באפשרותך להשתמש ב- Microsoft Query כדי לאחזר נתונים ממקורות חיצוניים. על-ידי שימוש ב- Microsoft Query כדי לאחזר נתונים ממסדי הנתונים ומהקבצים של הארגון שלך, אינך צריך להקליד מחדש את הנתונים שברצונך לנתח ב- Excel. באפשרותך גם לרענן דוחות וסיכומים של Excel באופן אוטומטי ממסד הנתונים המקורי בכל פעם שמסד הנתונים מתעדכן במידע חדש.
מידע נוסף על Microsoft Query
באמצעות Microsoft Query, באפשרותך להתחבר למקורות נתונים חיצוניים, לבחור נתונים ממקורות חיצוניים אלה, לייבא נתונים אלה לגליון העבודה שלך ולרענן את הנתונים לפי הצורך כדי שנתוני גליון העבודה שלך יישארו מסונכרנים עם הנתונים במקורות החיצוניים.
סוגי מסדי נתונים שבאפשרותך לגשת אליהם באפשרותך לאחזר נתונים מכמה סוגים של מסדי נתונים, כולל Microsoft Office Access, Microsoft SQL Server ושירותי OLAP של Microsoft SQL Server. באפשרותך גם לאחזר נתונים מחוברות עבודה של 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:
בכרטיסיה נתונים , בקבוצה קבל נתונים חיצוניים , לחץ על ממקורות אחרים ולאחר מכן לחץ על מ- Microsoft Query.
הערה
Excel 365 העביר את Microsoft Query לקבוצת התפריטים ' אשפים מדור קודם '. תפריט זה אינו מוצג כברירת מחדל. כדי להפוך לזמין, עבור אל 'קובץ', 'אפשרויות', 'נתונים' ו'הפעל' במקטע 'הצגת אשפי ייבוא נתונים מדור קודם '.
בצע אחת מהפעולות הבאות:
- כדי לציין מקור נתונים עבור מסד נתונים, קובץ טקסט או חוברת עבודה של Excel, לחץ על הכרטיסיה 'מסדי נתונים '.
- כדי לציין מקור נתונים של קוביית OLAP, לחץ על הכרטיסיה קוביות OLAP . כרטיסיה זו זמינה רק אם הפעלת את Microsoft Query מ- Excel.
לחץ פעמיים על <מקור> נתונים חדש.
-לחלופין-
לחץ על <מקור> נתונים חדש ולאחר מכן לחץ על אישור.
תיבת הדו-שיח 'יצירת מקור נתונים חדש ' מוצגת.בשלב 1, הקלד שם כדי לזהות את מקור הנתונים.
בשלב 2, לחץ על מנהל התקן עבור סוג מסד הנתונים שבו אתה משתמש כמקור הנתונים שלך.
הערה
- אם מסד הנתונים החיצוני שאליו ברצונך לגשת אינו נתמך על-ידי מנהלי ההתקנים של ODBC המותקנים עם Microsoft Query, עליך להשיג ולהתקין מנהל התקן ODBC התואם ל- Microsoft Office מספק חיצוני, כגון יצרן מסד הנתונים. פנה לספק מסד הנתונים לקבלת הוראות התקנה.
- מסדי נתונים של OLAP אינם דורשים מנהלי התקנים של ODBC. בעת התקנת Microsoft Query, מנהלי התקנים מותקנים עבור מסדי נתונים שנוצרו באמצעות Microsoft SQL Server Analysis Services. כדי להתחבר למסדי נתונים אחרים של OLAP, עליך להתקין מנהל התקן של מקור נתונים ותוכנת לקוח.
לחץ על 'התחבר' ולאחר מכן ספק את המידע הדרוש כדי להתחבר למקור הנתונים שלך. עבור מסדי נתונים, חוברות עבודה של Excel וקבצי טקסט, המידע שתספק תלוי בסוג מקור הנתונים שבחרת. ייתכן שתתבקש לספק שם כניסה, סיסמה, גירסת מסד הנתונים שבו אתה משתמש, מיקום מסד הנתונים או מידע ספציפי אחר לסוג מסד הנתונים.
חשוב
- השתמש בסיסמאות חזקות המשלבות אותיות רישיות ואותיות קטנות, מספרים וסמלים. סיסמאות חלשות אינן מערבבות רכיבים אלה. סיסמה חזקה: Y6dh!et5. סיסמה חלשה: House27. סיסמאות צריכות להכיל 8 תווים או יותר. ביטוי סיסמה המשתמש ב- 14 תווים או יותר עדיף.
- חיוני שתזכור את הסיסמה שלך. אם תשכח את הסיסמה שלך, Microsoft לא תוכל לאחזר אותה. שמור את הסיסמאות שאתה כותב במקום בטוח הרחק מהמידע המוגן באמצעות סיסמאות אלה.
לאחר שתזין את המידע הנדרש, לחץ על אישור או על סיום כדי לחזור לתיבת הדו-שיח יצירת מקור נתונים חדש .
אם מסד הנתונים שלך מכיל טבלאות וברצונך שטבלה מסוימת תוצג באופן אוטומטי באשף השאילתות, לחץ על התיבה של שלב 4 ולאחר מכן לחץ על הטבלה הרצויה.
אם אינך מעוניין להקליד את שם המשתמש והסיסמה שלך בעת השימוש במקור הנתונים, בחר בתיבת הסימון שמור את מזהה המשתמש והסיסמה שלי בהגדרת מקור הנתונים . הסיסמה השמורה אינה מוצפנת. אם תיבת הסימון אינה זמינה, פנה למנהל מסד הנתונים כדי לברר אם ניתן להפוך אפשרות זו לזמינה.
הערה
הימנע משמירת פרטי כניסה בעת התחברות למקורות נתונים. מידע זה עשוי להיות מאוחסן כטקסט רגיל, ומשתמש זדוני עלול לגשת למידע כדי לפגוע באבטחה של מקור הנתונים.
לאחר השלמת שלבים אלה, שם מקור הנתונים שלך מופיע בתיבת הדו-שיח בחירת מקור נתונים .
שימוש באשף השאילתות להגדרת שאילתה
שימוש באשף השאילתות עבור רוב השאילתות אשף השאילתות מקל עליך לבחור ולשלב נתונים מטבלאות ושדות שונים במסד הנתונים שלך. באמצעות אשף השאילתות, תוכל לבחור את הטבלאות ואת השדות שברצונך לכלול. צירוף פנימי (פעולת שאילתה המציינת ששורות משתי טבלאות משולבות על בסיס ערכי שדה זהים) נוצר באופן אוטומטי כאשר האשף מזהה שדה מפתח ראשי בטבלה אחת ושדה בעל שם זהה בטבלה שניה.
באפשרותך להשתמש באשף גם כדי למיין את ערכת התוצאות ולבצע סינון פשוט. בשלב הסופי של האשף, באפשרותך לבחור להחזיר את הנתונים ל- Excel, או להמשיך ולמקד את השאילתה ב- Microsoft Query. לאחר יצירת השאילתה, באפשרותך להפעיל אותה ב- Excel או ב- Microsoft Query.
כדי להפעיל את אשף השאילתות, בצע את השלבים הבאים.
- בכרטיסיה נתונים , בקבוצה קבל נתונים חיצוניים , לחץ על ממקורות אחרים ולאחר מכן לחץ על מ- Microsoft Query.
- בתיבת הדו-שיח בחירת מקור נתונים , ודא שתיבת הסימון השתמש באשף השאילתות כדי ליצור/לערוך שאילתות נבחרה.
- לחץ פעמיים על מקור הנתונים שבו ברצונך להשתמש.
-לחלופין-
לחץ על מקור הנתונים שבו ברצונך להשתמש ולאחר מכן לחץ על אישור.
עבודה ישירות ב- Microsoft Query עבור סוגים אחרים של שאילתות אם ברצונך ליצור שאילתה מורכבת יותר מזו שאשף השאילתות מאפשר, באפשרותך לעבוד ישירות ב- Microsoft Query. באפשרותך להשתמש ב- Microsoft Query כדי להציג ולשנות שאילתות שאתה מתחיל ליצור באשף השאילתות, או ליצור שאילתות חדשות מבלי להשתמש באשף. עבוד ישירות ב- Microsoft Query כאשר ברצונך ליצור שאילתות המבצעות את הפעולות הבאות:
- בחירת נתונים ספציפיים משדה במסד נתונים גדול, מומלץ לבחור חלק מהנתונים בשדה ולהשמיט נתונים שאינך זקוק להם. לדוגמה, אם אתה זקוק לנתונים עבור שניים מהמוצרים בשדה המכיל מידע עבור מוצרים רבים, באפשרותך להשתמש בקריטריונים כדי לבחור נתונים עבור שני המוצרים הרצויים בלבד.
- אחזר נתונים בהתבסס על קריטריונים שונים בכל פעם שתפעיל את השאילתה אם עליך ליצור את אותו דוח או סיכום של Excel עבור כמה אזורים באותם נתונים חיצוניים — כגון דוח מכירות נפרד עבור כל אזור — באפשרותך ליצור שאילתת פרמטר. בעת הפעלת שאילתת פרמטר, אתה מתבקש לציין ערך שישמש כקריטריון כאשר השאילתה בוחרת רשומות. לדוגמה, שאילתת פרמטר עשויה לבקש ממך להזין אזור ספציפי, ותוכל לעשות שימוש חוזר בשאילתה זו כדי ליצור כל אחד מדוחות המכירות האזוריים שלך.
- צירוף נתונים בדרכים שונות הצירופים הפנימיים שאשף השאילתות יוצר הם סוג הצירוף הנפוץ ביותר המשמש ליצירת שאילתות. עם זאת, לעתים תרצה להשתמש בסוג אחר של צירוף. לדוגמה, אם יש לך טבלה של נתוני מכירות של מוצרים וטבלה של פרטי לקוחות, צירוף פנימי (מהסוג שנוצר על-ידי אשף השאילתות) ימנע אחזור רשומות לקוח עבור לקוחות שלא ביצעו רכישה. באמצעות Microsoft Query, באפשרותך לצרף טבלאות אלה כך שכל רשומות הלקוח מאוחזרות, יחד עם נתוני מכירות של לקוחות שביצעו רכישות.
כדי להפעיל את Microsoft Query, בצע את השלבים הבאים.
- בכרטיסיה נתונים , בקבוצה קבל נתונים חיצוניים , לחץ על ממקורות אחרים ולאחר מכן לחץ על מ- Microsoft Query.
- בתיבת הדו-שיח בחירת מקור נתונים , ודא שתיבת הסימון השתמש באשף השאילתות כדי ליצור/לערוך שאילתות אינה מסומנת.
- לחץ פעמיים על מקור הנתונים שבו ברצונך להשתמש.
-לחלופין-
לחץ על מקור הנתונים שבו ברצונך להשתמש ולאחר מכן לחץ על אישור.
שימוש חוזר ושיתוף של שאילתות הן באשף השאילתות והן ב- Microsoft Query, באפשרותך לשמור את השאילתות כקובץ .dqy שניתן לשנות, לעשות בו שימוש חוזר ולשתף אותו. Excel יכול לפתוח קבצי .dqy ישירות, מה שמאפשר לך או למשתמשים אחרים ליצור טווחי נתונים חיצוניים נוספים מאותה שאילתה.
כדי לפתוח שאילתה שמורה מ- Excel:
- בכרטיסיה נתונים , בקבוצה קבל נתונים חיצוניים , לחץ על ממקורות אחרים ולאחר מכן לחץ על מ- Microsoft Query. תיבת הדו-שיח בחירת מקור נתונים מוצגת.
- בתיבת הדו-שיח בחירת מקור נתונים , לחץ על הכרטיסיה שאילתות .
- לחץ פעמיים על השאילתה השמורה שברצונך לפתוח. השאילתה מוצגת ב- Microsoft Query.
אם ברצונך לפתוח שאילתה שמורה ו- Microsoft Query כבר פתוח, לחץ על תפריט קובץ Microsoft Query ולאחר מכן לחץ על פתח.
אם תלחץ פעמיים על קובץ .dqy, Excel ייפתח, יפעיל את השאילתה ולאחר מכן יוסיף את התוצאות לגליון עבודה חדש.
אם ברצונך לשתף סיכום או דוח של Excel המבוססים על נתונים חיצוניים, באפשרותך לתת למשתמשים אחרים חוברת עבודה המכילה טווח נתונים חיצוניים, או ליצור תבנית. תבנית מאפשרת לך לשמור את הסיכום או הדוח מבלי לשמור את הנתונים החיצוניים, ובכך הקובץ יהיה קטן יותר. הנתונים החיצוניים מאוחזרים כאשר משתמש פותח את תבנית הדוח.
עבודה עם הנתונים ב- Excel
לאחר יצירת שאילתה באשף השאילתות או ב- Microsoft Query, באפשרותך להחזיר את הנתונים לגליון עבודה של Excel. לאחר מכן, הנתונים הופכים לטווח נתונים חיצוני או לדוח PivotTable שניתן לעצב ולרענן אותו.
עיצוב נתונים שאוחזרו ב- Excel, באפשרותך להשתמש בכלים, כגון תרשימים או סכומי ביניים אוטומטיים, כדי להציג ולסכם את הנתונים שאוחזרו על-ידי Microsoft Query. באפשרותך לעצב את הנתונים, והעיצוב שלך יישמר כאשר תרענן את הנתונים החיצוניים. באפשרותך להשתמש בתוויות עמודות משלך במקום בשמות השדות, ולהוסיף מספרי שורות באופן אוטומטי.
Excel יכול לעצב באופן אוטומטי נתונים חדשים שאתה מקליד בסוף טווח כך שיתאימו לשורות הקודמות. Excel יכול גם להעתיק באופן אוטומטי נוסחאות שחזרו על עצמן בשורות הקודמות ולהרחיב אותן לשורות נוספות.
הערה
כדי להתרחב לשורות חדשות בטווח, התבניות והנוסחאות חייבות להופיע לפחות בשלוש מתוך חמש השורות הקודמות.
באפשרותך להפעיל אפשרות זו (או לבטל שוב) בכל עת:
- לחץ על'אפשרויות>קובץ>' מתקדם.
- במקטע אפשרויות עריכה , בחר את הרחב את בדיקת העיצוב והנוסחאות של טווח הנתונים . כדי לבטל שוב את העיצוב האוטומטי של טווח הנתונים, נקה תיבת סימון זו.
רענון נתונים חיצוניים כאשר אתה מרענן נתונים חיצוניים, אתה מפעיל את השאילתה כדי לאחזר נתונים חדשים או נתונים שהשתנו התואמים למפרטים שלך. באפשרותך לרענן שאילתה הן ב- Microsoft Query והן ב- Excel. Excel מספק כמה אפשרויות לרענון שאילתות, כולל רענון הנתונים בכל פעם שאתה פותח את חוברת העבודה ורענון אוטומטי במרווחי זמן קצובים. באפשרותך להמשיך לעבוד ב- Excel בזמן רענון הנתונים ובאפשרותך גם לבדוק את המצב בזמן רענון הנתונים. לקבלת מידע נוסף, ראה רענון חיבור נתונים חיצוני ב- Excel.