שימוש ב- Microsoft Query לאחזור נתונים חיצוניים

באמצעות Microsoft Query, באפשרותך להתחבר למקורות נתונים חיצוניים, לבחור נתונים ממקורות חיצוניים אלה, לייבא נתונים אלה לגליון העבודה שלך וארענן את הנתונים לפי הצורך כדי לסנכרן את נתוני גליון העבודה עם הנתונים במקורות החיצוניים.

סוגי מסדי נתונים שניתן לגשת אליהם     באפשרותך לאחזר נתונים ממספר סוגים של מסדי נתונים, כולל Microsoft Office Access, Microsoft SQL Server ו- Microsoft SQL Server OLAP Services. באפשרותך גם לאחזר נתונים מתוך חוברות עבודה של Excel ומקובצי טקסט.

Microsoft Office מספק מנהלי התקנים שבהם באפשרותך להשתמש כדי לאחזר נתונים ממקורות הנתונים הבאים:

• Microsoft SQL Server Analysis Services (ספק OLAP )

• Microsoft Office Access

• dBASE

• Microsoft FoxPro

• Microsoft Office Excel

• Oracle

• פרדוקס

• מסדי נתונים של קבצי טקסט

באפשרותך גם להשתמש במנהלי התקנים של ODBC או במנהלי התקנים של מקורות נתונים של יצרנים אחרים כדי לאחזר מידע ממקורות נתונים שאינם מפורטים כאן, כולל סוגים אחרים של מסדי נתונים של OLAP. לקבלת מידע אודות התקנת מנהל התקן של ODBC או מנהל התקן של מקור נתונים שאינו מופיע כאן, עיין בתיעוד של מסד הנתונים או פנה לספק מסד הנתונים.

בחירת נתונים ממסד נתונים     ניתן לאחזר נתונים ממסד נתונים על-ידי יצירת שאילתה, שהיא שאלה שאתה שואל אודות נתונים המאוחסנים במסד נתונים חיצוני. לדוגמה, אם הנתונים שלך מאוחסנים במסד נתונים של Access, ייתכן שתרצה לדעת את נתוני המכירות של מוצר מסוים לפי אזור. באפשרותך לאחזר חלק מהנתונים על-ידי בחירת הנתונים עבור המוצר והאזור שברצונך לנתח בלבד.

באמצעות Microsoft Query, באפשרותך לבחור את עמודות הנתונים הרצויות ולייבא רק את הנתונים לתוך Excel.

עדכון גליון העבודה בפעולה אחת     לאחר שיש לך נתונים חיצוניים בחוברת עבודה של Excel, בכל פעם שמסד הנתונים משתנה, באפשרותך רענון את הנתונים כדי לעדכן את הניתוח - מבלי שתצטרך ליצור מחדש את דוחות הסיכום ותרשימים. לדוגמה, באפשרותך ליצור סיכום מכירות חודשי רועענן אותו מדי חודש כאשר נתוני המכירות החדשים מגיעים.

כיצד Microsoft Query משתמש במקורות נתונים     לאחר הגדרת מקור נתונים עבור מסד נתונים מסוים, באפשרותך להשתמש בו בכל פעם שתרצה ליצור שאילתה כדי לבחור ולאחזר נתונים ממסד נתונים זה - מבלי שתצטרך להקליד מחדש את כל פרטי החיבור. Microsoft Query משתמש במקור הנתונים כדי להתחבר למסד הנתונים החיצוני ולהציג לך אילו נתונים זמינים. לאחר יצירת השאילתה והחזרת הנתונים ל- Excel, Microsoft Query מספק את חוברת העבודה של Excel עם השאילתה ומידע מקור הנתונים, כך שתוכל להתחבר מחדש למסד הנתונים כאשר ברצונך לרענן את הנתונים.

שימוש ב- Microsoft Query לייבוא נתונים     כדי לייבא נתונים חיצוניים ל- Excel באמצעות Microsoft Query, בצע שלבים בסיסיים אלה, שכל אחד מהם מתואר בפירוט רב יותר בסעיפים הבאים.

מהו מקור נתונים?     מקור נתונים הוא ערכת מידע מאוחסנת המאפשרת ל- Excel ול- Microsoft Query להתחבר למסד נתונים חיצוני. בעת שימוש ב- Microsoft Query להגדרת מקור נתונים, אתה מעניק למקור הנתונים שם ולאחר מכן מספק את השם והמיקום של מסד הנתונים או השרת, סוג מסד הנתונים ופרטי הכניסה והסיסמה שלך. המידע כולל גם את השם של מנהל התקן OBDC או מנהל התקן של מקור נתונים, תוכנית ההופכת חיבורים לסוג ספציפי של מסד נתונים.

כדי להגדיר מקור נתונים באמצעות Microsoft Query:

1. בכרטיסיה נתונים, בקבוצה קבל נתונים חיצוניים , לחץ על ממקורות אחרים ולאחר מכן לחץ על מתוך Microsoft Query.

   הערה: Excel 365 העביר את Microsoft Query לקבוצת התפריטים אשפים מדור קודם.  תפריט זה אינו מוצג כברירת מחדל.  כדי להפוך לזמין, עבור אל קובץ,אפשרויות, נתונים והפעל במקטע הצג אשפי ייבוא נתונים מדור קודם.

2. בצע אחת מהפעולות הבאות:

   • כדי לציין מקור נתונים עבור מסד נתונים, קובץ טקסט או חוברת עבודה של Excel, לחץ על הכרטיסיה מסדי נתונים.

   • כדי לציין מקור נתונים של קוביית OLAP, לחץ על הכרטיסיה קוביות OLAP . כרטיסיה זו זמינה רק אם הפעלת את Microsoft Query מ- Excel.

3. לחץ פעמיים על <מקור נתונים חדש>.

   -לחלופין-

   לחץ <מקור נתונים חדש>ולאחר מכן לחץ על אישור.

   תיבת הדו-שיח יצירת מקור נתונים חדש מוצגת.

4. בשלב 1, הקלד שם כדי לזהות את מקור הנתונים.

5. בשלב 2, לחץ על מנהל התקן עבור סוג מסד הנתונים שבו אתה משתמש כמקור הנתונים שלך.

   הערות: 

   • אם מסד הנתונים החיצוני שאליו ברצונך לגשת אינו נתמך על-ידי מנהלי ההתקנים של ODBC המותקנים עם Microsoft Query, עליך להשיג ולהתקין מנהל התקן ODBC התואם ל- Microsoft Office מספק חיצוני, כגון יצרן מסד הנתונים. פנה לספק מסד הנתונים לקבלת הוראות התקנה.

   • מסדי נתונים של OLAP אינם דורשים מנהלי התקנים של ODBC. בעת התקנת Microsoft Query, מנהלי התקנים מותקנים עבור מסדי נתונים שנוצרו באמצעות Microsoft SQL Server Analysis Services. כדי להתחבר למסדי נתונים אחרים של OLAP, עליך להתקין מנהל התקן של מקור נתונים ותוכנות לקוח.

6. לחץ על התחבר ולאחר מכן ספק את המידע הדרוש כדי להתחבר למקור הנתונים שלך. עבור מסדי נתונים, חוברות עבודה של Excel וקובצי טקסט, המידע שתספק תלוי בסוג מקור הנתונים שבחרת. ייתכן שתתבקש לספק שם כניסה, סיסמה, את גירסת מסד הנתונים שבה אתה משתמש, את מיקום מסד הנתונים או מידע אחר הספציפי לסוג מסד הנתונים.

   חשוב: 

   • השתמש בסיסמאות חזקות המשלבות אותיות רישיות ואותיות קטנות, מספרים וסמלים. סיסמאות חלשות אינן מערבבות רכיבים אלה. סיסמה חזקה: Y6dh!et5. סיסמה חלשה: House27. סיסמאות צריכות להכיל 8 תווים או יותר. ביטוי סיסמה המשתמש ב- 14 תווים או יותר עדיף.

   • חיוני שתזכור את הסיסמה שלך. אם תשכח את הסיסמה שלך, Microsoft לא תוכל לאחזר אותה. שמור את הסיסמאות שאתה כותב במקום בטוח הרחק מהמידע המוגן באמצעות סיסמאות אלה.

7. לאחר שתזין את המידע הנדרש, לחץ על אישור אועל סיום כדי לחזור לתיבת הדו-שיח יצירת מקור נתונים חדש.

8. אם מסד הנתונים שלך כולל טבלאות וברצונך שטבלה מסוימת תוצג באופן אוטומטי באשף השאילתות, לחץ על התיבה עבור שלב 4 ולאחר מכן לחץ על הטבלה הרצויה.

9. אם אינך מעוניין להקליד את שם הכניסה והסיסמה שלך בעת השימוש במקור הנתונים, בחר את תיבת הסימון שמור את מזהה המשתמש והסיסמה שלי בהגדרת מקור הנתונים. הסיסמה שנשמרה אינה מוצפנת. אם תיבת הסימון אינה זמינה, פנה למנהל מסד הנתונים כדי לקבוע אם ניתן להפוך אפשרות זו לזמינה.

   הערת אבטחה: הימנע משמירה של פרטי כניסה בעת התחברות למקורות נתונים. מידע זה עשוי להיות מאוחסן כטקסט רגיל, ומשתמש זדוני יכול לגשת למידע כדי לסכן את אבטחת מקור הנתונים.

לאחר השלמת שלבים אלה, השם של מקור הנתונים שלך מופיע בתיבת הדו-שיח בחירת מקור נתונים.

שימוש באשף השאילתות עבור רוב השאילתות     אשף השאילתות מקל עליך לבחור ולכנס נתונים מטבלאות ושדות שונים במסד הנתונים שלך. באמצעות אשף השאילתות, באפשרותך לבחור את הטבלאות והשדות שברצונך לכלול. צירוף פנימי (פעולת שאילתה המציינת ששורות משתי טבלאות משולבות בהתבסס על ערכי שדה זהים) נוצר באופן אוטומטי כאשר האשף מזהה שדה מפתח ראשי בטבלה אחת ושדה עם שם זהה בטבלה שניה.

באפשרותך גם להשתמש באשף כדי למיין את ערכת התוצאות ולבצע סינון פשוט. בשלב הסופי של האשף, באפשרותך לבחור להחזיר את הנתונים ל- Excel, או למקד את השאילתה ב- Microsoft Query. לאחר יצירת השאילתה, באפשרותך להפעיל אותה ב- Excel או ב- Microsoft Query.

כדי להפעיל את אשף השאילתות, בצע את השלבים הבאים.

1. בכרטיסיה נתונים, בקבוצה קבל נתונים חיצוניים , לחץ על ממקורות אחרים ולאחר מכן לחץ על מתוך Microsoft Query.

2. בתיבת הדו-שיח בחירת מקור נתונים, ודא שתיבת הסימון השתמש באשף השאילתות כדי ליצור / לערוך שאילתות נבחרה.

3. לחץ פעמיים על מקור הנתונים שבו ברצונך להשתמש.

   -לחלופין-

   לחץ על מקור הנתונים שבו ברצונך להשתמש ולאחר מכן לחץ על אישור.

עבודה ישירות ב- Microsoft Query עבור סוגים אחרים של שאילתות     אם ברצונך ליצור שאילתה מורכבת יותר מכפי שתאפשר אשף השאילתות, באפשרותך לעבוד ישירות ב- Microsoft Query. באפשרותך להשתמש ב- Microsoft Query כדי להציג ולשנות שאילתות שאתה מתחיל ליצור באשף השאילתות, או ליצור שאילתות חדשות מבלי להשתמש באשף. עבוד ישירות ב- Microsoft Query כאשר ברצונך ליצור שאילתות שבצעו את הפעולות הבאות:

• בחירת נתונים ספציפיים מתוך שדה     במסד נתונים גדול, ייתכן שתרצה לבחור חלק מהנתונים בשדה ולהשממיט נתונים שאינך זקוק להם. לדוגמה, אם אתה זקוק לנתונים עבור שניים מהתוצרים בשדה המכיל מידע עבור מוצרים רבים, באפשרותך להשתמש ב- קריטריונים כדי לבחור נתונים עבור שני המוצרים הרצויים בלבד.

• אחזור נתונים בהתבסס על קריטריונים שונים בכל פעם שאתה מפעיל את השאילתה     אם עליך ליצור את אותו דוח או סיכום של Excel עבור כמה אזורים באותם נתונים חיצוניים - כגון דוח מכירות נפרד עבור כל אזור - באפשרותך ליצור שאילתת פרמטר. בעת הפעלת שאילתת פרמטר, אתה מתבקש לספק ערך שיש להשתמש בו כקריטריון כאשר השאילתה בוחרת רשומות. לדוגמה, שאילתת פרמטר עשויה לבקש ממך להזין אזור ספציפי, וייתכן שתרצה לעשות שימוש חוזר בשאילתה זו כדי ליצור כל אחד מדוחות המכירות האזוריים שלך.

• צירוף נתונים בדרכים שונות     צירופים פנימיים ש'אשף השאילתות' יוצר הם סוג הצירוף הנפוץ ביותר הנמצא בשימוש ביצירת שאילתות. עם זאת, לעתים תרצה להשתמש בסוג אחר של צירוף. לדוגמה, אם יש לך טבלה של פרטי מכירות של מוצרים וטבלת פרטי לקוח, צירוף פנימי (הסוג שנוצר על-ידי אשף השאילתות) ימנע את אחזור רשומות הלקוח עבור לקוחות שלא ביצעו רכישה. באמצעות Microsoft Query, באפשרותך לצרף טבלאות אלה כך שכל רשומות הלקוחות יאוחזרו, יחד עם נתוני מכירות עבור לקוחות שביצעו רכישות.

כדי להפעיל את Microsoft Query, בצע את השלבים הבאים.

1. בכרטיסיה נתונים, בקבוצה קבל נתונים חיצוניים , לחץ על ממקורות אחרים ולאחר מכן לחץ על מתוך Microsoft Query.

2. בתיבת הדו-שיח בחירת מקור נתונים, ודא שתיבת הסימון השתמש באשף השאילתות כדי ליצור/ לערוך שאילתות אינה מסומנת.

3. לחץ פעמיים על מקור הנתונים שבו ברצונך להשתמש.

   -לחלופין-

   לחץ על מקור הנתונים שבו ברצונך להשתמש ולאחר מכן לחץ על אישור.

שימוש חוזר בשאילתות ושיתוף     הן באשף השאילתות והן ב- Microsoft Query, באפשרותך לשמור את השאילתות כקובץ .dqy שניתן לשנות, לעשות בו שימוש חוזר ולשתף. Excel יכול לפתוח קבצי .dqy ישירות, דבר המאפשר לך או למשתמשים אחרים ליצור טווחי נתונים חיצוניים נוספים מאותה שאילתה.

כדי לפתוח שאילתה שמורה מ- Excel:

1. בכרטיסיה נתונים, בקבוצה קבל נתונים חיצוניים , לחץ על ממקורות אחרים ולאחר מכן לחץ על מתוך Microsoft Query. תיבת הדו-שיח בחירת מקור נתונים מוצגת.

2. בתיבת הדו-שיח בחירת מקור נתונים, לחץ על הכרטיסיה שאילתות .

3. לחץ פעמיים על השאילתה השמורה שברצונך לפתוח. השאילתה מוצגת ב- Microsoft Query.

אם ברצונך לפתוח שאילתה שמורה ו- Microsoft Query כבר פתוח, לחץ על תפריט קובץ של Microsoft Query ולאחר מכן לחץ על פתח.

אם תלחץ פעמיים על קובץ .dqy, Excel ייפתח, י מפעיל את השאילתה ולאחר מכן מוסיף את התוצאות לגליון עבודה חדש.

אם ברצונך לשתף סיכום או דוח של Excel המבוססים על נתונים חיצוניים, באפשרותך להעניק למשתמשים אחרים חוברת עבודה המכילה טווח נתונים חיצוני, או ליצור תבנית. תבנית מאפשרת לך לשמור את הסיכום או הדוח מבלי לשמור את הנתונים החיצוניים כך שהקובץ יהיה קטן יותר. הנתונים החיצוניים מאוחזרים כאשר משתמש פותח את תבנית הדוח.

לאחר יצירת שאילתה באשף השאילתות או ב- Microsoft Query, באפשרותך להחזיר את הנתונים לגליון עבודה של Excel. לאחר מכן, הנתונים טווח נתונים חיצוני או דוח PivotTable שבאפשרותך לעצב ותרענן.

עיצוב נתונים שאוחזרו     ב- Excel, באפשרותך להשתמש בכלים, כגון תרשימים או סכומי ביניים אוטומטיים, כדי להציג ולסכם את הנתונים שאוחזרו על-ידי Microsoft Query. באפשרותך לעצב את הנתונים, והעיצוב שלך יישמר בעת רענון הנתונים החיצוניים. באפשרותך להשתמש בתוויות עמודה משלך במקום בשמות השדות, ולהוסיף מספרי שורות באופן אוטומטי.

Excel יכול לעצב באופן אוטומטי נתונים חדשים שאתה מקליד בסוף טווח כך שיתאים לשורות הקדימות. Excel יכול גם להעתיק באופן אוטומטי נוסחאות שחוזרות על אותן שורות קודמות ולהרחיב אותן לשורות נוספות.

באפשרותך להפעיל אפשרות זו (או לבטל שוב) בכל עת:

1. לחץ על קובץ >‏ אפשרויות >‏ מתקדם.

2. במקטע אפשרויות עריכה, בחר בתיבת הסימון הרחב תבניות ונוסחאות של טווח נתונים. כדי לבטל שוב את עיצוב טווח הנתונים האוטומטי, נקה תיבת סימון זו.

רענון נתונים חיצוניים     בעת רענון נתונים חיצוניים, אתה מפעיל את השאילתה כדי לאחזר נתונים חדשים או נתונים שהשתנו התואמים למפרטים שלך. באפשרותך לרענן שאילתה הן ב- Microsoft Query והן ב- Excel. Excel מספק כמה אפשרויות לרענון שאילתות, כולל רענון הנתונים בכל פעם שאתה פותח את חוברת העבודה ומרענן אותם באופן אוטומטי במרווחי זמן קבועים. באפשרותך להמשיך לעבוד ב- Excel בזמן רענון נתונים, ובאפשרותך גם לבדוק את המצב בעת רענון הנתונים. לקבלת מידע נוסף, ראה רענון חיבור נתונים חיצוני ב- Excel.

לראש הדף