שילוב נתונים ממקורות נתונים מרובים (Power Query)

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

הערה: Power Query ידוע בשם קבלה והמרה ב- Excel 2016. המידע המסופק כאן חל על שניהם. לקבלת מידע נוסף, ראה קבלה והמרה ב- Excel 2016.

בערכת לימוד זו, יהיה עליך להשתמש בעורך השאילתות של Power Query כדי לייבא נתונים מקובץ Excel מקומי המכיל מידע אודות המוצר ולאחר אשר מהזנת odata מכיל מידע אודות הזמנת המוצר. בצע את השלבים צבירה והמרה ולאחר שילוב נתונים ממקורות שני להפקת דוח Total Sales per Product ו- Year.

כדי לבצע ערכת לימוד זו, אתה זקוק לחוברת העבודה Products and Orders. בתיבת הדו-שיח שמירה בשם, תן לקובץ את השם Products and Orders.xlsx.

בערכת לימוד זו

משימה 1: ייבוא מוצרים לחוברת עבודה של Excel

שלב 1: חיבור לחוברת עבודה של Excel

שלב 2: קידום השורה הראשונה לכותרות העמודות בטבלה

שלב 3: הסרת עמודות אחרות כדי להציג רק עמודות רצויות

שלבי Power Query שנוצרו

שלב 4: ייבוא שאילתת מוצרים

משימה 2: ייבוא נתוני הזמנות מהזנת OData

שלב 1: חיבור להזנת OData

שלב 2: הרחבת הטבלה Order_Details

קישור להרחבת הטבלה Order_Details

שלב 3: הסרת עמודות אחרות כדי להציג רק עמודות רצויות

הסרת עמודות שנבחרו

שלב 4: חישוב סכום השורה עבור כל שורה של Order_Details

חישוב סכום השורה עבור כל שורה של Order_Details

שלב 5: המרת עמודת השנה OrderDate

שלב 6: קיבוץ שורות לפי ProductID ו- Year

שלב 7: שינוי שם של שאילתה

תוצאות סופיות של שאילתה

שלבי Power Query שנוצרו

שלב 8: השבתה של הורדת שאילתה לחוברת עבודה של Excel

השבתה של הורדת שאילתה

משימה 3: שילוב השאילתות Products ו- Total Sales

שלב 1: מיזוג ProductID בשאילתת Total Sales

שלב 2: הרחבת עמודת מיזוג

קישור להרחבת הטבלה NewColumn

שלבי Power Query שנוצרו

שלב 3: טעינת שאילתת Total Sales per Product במודל נתונים של Excel

טעינת השאילתה Total Sales per Product במודל הנתונים של Excel

שאילתת Total Sales per Product הסופית

משימה 1: ייבוא מוצרים לחוברת עבודה של Excel

במשימה זו תייבא מוצרים מהקובץ Products and Orders.xlsx לחוברת עבודה של Excel.

שלב 1: חיבור לחוברת עבודה של Excel

  1. צור חוברת עבודה של Excel.

  2. בכרטיסיה POWER QUERY ברצועת הכלים, לחץ על מקובץ > מ- Excel.

  3. בתיבת הדו-שיח לעיון Excel, אתר את Products and Orders.xlsx או הקלד את הנתיב שלו כדי לייבא אותו או לקשר אותו לקובץ.

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

    הערה: לצפייה בסרטון קצרצר שעוסק בהצגת עורך השאילתות, עבור לסוף המאמר.

שלב 2: קידום השורה הראשונה לכותרות העמודות בטבלה

ברשת תצוגה מקדימה של שאילתה, השורה הראשונה של הטבלה אינה מכילה את שמות העמודות של הטבלה. כדי לקדם את השורה הראשונה לכותרות העמודות של הטבלה:

  1. לחץ על סמל הטבלה ( סמל הטבלה ) בפינה הימנית העליונה של התצוגה המקדימה של הנתונים.

  2. לחץ על השתמש בשורה ראשונה ככותרות.

קידום השורה הראשונה לכותרות העמודות של טבלה

שלב 3: הסרת עמודות אחרות כדי להציג רק עמודות רצויות

בשלב זה תסיר את כל העמודות למעט ProductID,‏ ProductName,‏ CategoryID ו- QuantityPerUnit.

  1. ברשת תצוגה מקדימה של שאילתה, בחר בעמודות ProductID,‏ ProductName,‏ CategoryID ו- QuantityPerUnit (השתמש ב- Ctrl+לחיצה או ב- Shift+לחיצה).

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

    הסתרת עמודות אחרות

שלבי Power Query שנוצרו

בעת ביצוע פעולות שאילתה ב- Power Query, שלבי שאילתה נוצרים ומופיעים בחלונית הגדרות שאילתה, ברשימה שלבים שהוחלו. לכל שלב בשאילתה יש נוסחת Power Query תואמת, הנקראת גם שפת "M". לקבלת מידע נוסף אודות שפת הנוסחאות של Power Query, ראה למד אודות נוסחאות Power Query.

משימה

שלב בשאילתה

נוסחה

חיבור לחוברת עבודה של Excel

מקור

Source{[Name="Products"]}[Data]‎

קידום השורה הראשונה לכותרות העמודות של טבלה

FirstRowAsHeader

Table.PromoteHeaders

(Products)

הסרת עמודות אחרות כדי להציג רק עמודות רצויות

RemovedOtherColumns

Table.SelectColumns

‎(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})‎

שלב 4: ייבוא שאילתת מוצרים

בשלב זה, אתה מייבא את השאילתה Products לתוך חוברת העבודה של Excel.

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

לראש הדף

משימה 2: ייבוא נתוני הזמנות מהזנת OData

במשימה זו תייבא נתונים אל חוברת העבודה של Excel מהזנת ה- OData לדוגמה Northwind, שנמצאת בכתובת http://services.odata.org/Northwind/Northwind.svc.

שלב 1: חיבור להזנת OData

  1. בכרטיסיה POWER QUERY ברצועת הכלים, לחץ על ממקורות אחרים > מהזנת OData.

  2. בתיבת הדו-שיח הזנת OData, הזן את כתובת ה- URL של הזנת OData בשם Northwind.

  3. לחץ על אישור.

  4. בחלונית נווט, לחץ פעמיים על הטבלה Orders או לחץ על Orders ולחץ על ערוך.

הערה: כשתרחף עם העכבר מעל טבלה, תיפתח תצוגה מקדימה של טבלה.

ריחוף מעל מקור נתונים

שלב 2: הרחבת הטבלה Order_Details

בשלב זה תרחיב את הטבלה Order_Details הקשורה לטבלה Orders, כדי לשלב את העמודות ProductID,‏ UnitPrice ו- Quantity מ- Order_Details בטבלה Orders. הפעולה הרחב משלבת עמודות מטבלה קשורה לטבלת נושא. כאשר השאילתה פועלת, שורות מהטבלה הקשורה (Order_Details) משולבות בשורות מטבלת הנושא (Orders).

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

הרחבת קישור הטבלה Order_Details

לאחר הרחבת הטבלה Order_Details, שלוש עמודות חדשות ושורות נוספות מתווספות לטבלה Orders, אחת לכל שורה בטבלה המקוננת או הקשורה.

  1. בחלונית תצוגה מקדימה של שאילתה, גלול אל העמודה Order_Details.

  2. בעמודה Order_Details, לחץ על סמל ההרחבה ( הרחב ).

  3. בתפריט הנפתח הרחבה:

    1. לחץ על (בחר את כל העמודות) כדי לנקות את כל העמודות.

    2. לחץ על ProductID‏, UnitPrice ו- Quantity.

    3. לחץ על אישור.

      הרחבת קישור הטבלה Order_Details

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

שלב 3: הסרת עמודות אחרות כדי להציג רק עמודות רצויות

בשלב זה תסיר את כל העמודות למעט OrderDate,‏ ProductID,‏ UnitPrice ו- Quantity. במשימה הקודמת, השתמשת באפשרות הסר עמודות אחרות. במשימה זו, תסיר את העמודות שנבחרו.

הסרת עמודות שנבחרו

  1. בחלונית תצוגה מקדימה של שאילתה, בחר את כל העמודות:

    1. לחץ על העמודה הראשונה (OrderID).

    2. לחץ על Shift+העמודה האחרונה (Shipper).

    3. לחץ על Ctrl+העמודות OrderDate,‏ Order_Details.ProductID,‏ Order_Details.UnitPrice ו- Order_Details.Quantity.

  2. לחץ באמצעות לחצן העכבר הימני על כותרת עמודה ולחץ על הסר עמודות.

שלב 4: חישוב סכום השורה עבור כל שורה של Order_Details

בשלב זה תיצור עמודה מותאמת אישית כדי לחשב את סכום השורה עבור כל שורה של Order_Details.

חישוב סכום השורה עבור כל שורה של Order_Details

  1. בחלונית תצוגה מקדימה של שאילתה, לחץ על סמל הטבלה ( סמל הטבלה ) בפינה הימנית העליונה של התצוגה המקדימה.

  2. לחץ על הוסף עמודה > התאמה אישית.

  3. בתיבת הדו-שיח הוספת עמודה מותאמת אישית, בתיבת הטקסט נוסחת עמודה מותאמת אישית, הזן ‎[Order_Details.UnitPrice] * [Order_Details.Quantity]‎.

  4. בתיבת הטקסט שם עמודה חדשה, הזן Line Total.

  5. לחץ על אישור.

חישוב סכום השורה עבור כל שורה של Order_Details

שלב 5: המרת עמודת השנה OrderDate

בשלב זה תמיר את העמודה OrderDate כדי להציג את השנה של תאריך ההזמנה.

  1. ברשת תצוגה מקדימה, לחץ באמצעות לחצן העכבר הימני על OrderDate, ולחץ על המר > שנה.

  2. שנה את שם העמודה OrderDate ל- Year:

    1. לחץ פעמיים על העמודה OrderDate והזן Year או

    2. לחץ באמצעות לחצן העכבר הימני על העמודה OrderDate, לחץ על שנה שם והזן Year.

שלב 6: קיבוץ שורות לפי ProductID ו- Year

  1. ברשת תצוגה מקדימה של שאילתה, בחר ב- Year וב- Order_Details.ProductID.

  2. לחץ באמצעות לחצן העכבר הימני על הכותרות ולחץ על קבץ לפי.

  3. בתיבת הדו-שיח קיבוץ לפי:

    1. בתיבת הטקסט שם עמודה חדשה, הזן Total Sales.

    2. בתפריט הנפתח פעולה, בחר באפשרות סכום.

    3. בתפריט הנפתח עמודה, בחר באפשרות Line Total.

  4. לחץ על אישור.

    תיבת הדו-שיח 'קיבוץ לפי' עבור פעולות צבירה

שלב 7: שינוי שם של שאילתה

לפני שתייבא את נתוני המכירות ל- Excel, תן לשאילתה את השם Total Sales:

  1. בחלונית הגדרות שאילתה, בתיבת הטקסט שם, הזן Total Sales.

תוצאות סופיות של שאילתה

לאחר שתבצע את כל השלבים, תהיה לך שאילתת Total Sales על הזנת ה- OData בשם Northwind.

סך כל המכירות

שלבי Power Query שנוצרו

בעת ביצוע פעולות שאילתה ב- Power Query, שלבי שאילתה נוצרים ומופיעים בחלונית הגדרות שאילתה, ברשימה שלבים שהוחלו. לכל שלב בשאילתה יש נוסחת Power Query תואמת, הנקראת גם שפת "M". לקבלת מידע נוסף אודות שפת הנוסחאות של Power Query, ראה למד אודות נוסחאות Power Query.

משימה

שלב בשאילתה

נוסחה

התחברות להזנת OData

מקור

Source{[Name="Orders"]}[Data]‎

הרחבת הטבלה Order_Details

הרחבת Order_Details

Table.ExpandTableColumn

‎(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})‎

הסרת עמודות אחרות כדי להציג רק עמודות רצויות

RemovedColumns

Table.RemoveColumns

‎(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})‎

חישוב סכום השורה עבור כל שורה של Order_Details

InsertedColumns

Table.AddColumn

‎(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])‎

המרת העמודה OrderDate להצגת השנה

RenamedColumns

Table.RenameColumns

(InsertedCustom,{{"Custom", "Line Total"}}‎)

TransformedColumn

Table.TransformColumns

(RenamedColumns,{{"OrderDate", Date.Year}}‎)

RenamedColumns1

Table.RenameColumns

(TransformedColumn,{{"OrderDate", "Year"}}‎)

קיבוץ שורות לפי ProductID ו- Year

GroupedRows

Table.Group
(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})‎

שלב 8: השבתה של הורדת שאילתה לחוברת עבודה של Excel

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

הפיכת הורדה של שאילתה ללא זמינה

  1. בחלונית הגדרות שאילתה, בטל את הסימון של טען לגליון עבודה.

  2. ברצועת הכלים של עורך השאילתות, לחץ על החל וסגור. בחלונית שאילתות חוברת עבודה, השאילתה Total Sales מציגה טעינה לא זמינה.

    הפיכת הורדה של שאילתה ללא זמינה

לראש הדף

משימה 3: שילוב השאילתות Products ו- Total Sales

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

במשימה זו תשלב את השאילתות Products ו- Total Sales על-ידי שימוש בשלב שאילתה של מיזוג והרחבה.

שלב 1: מיזוג ProductID בשאילתת Total Sales

  1. בחוברת העבודה של Excel, נווט אל השאילתה Products ב- Sheet2.

  2. בכרטיסיה שאילתה ברצועת הכלים, לחץ על מזג.

  3. בתיבת הדו-שיח מיזוג, בחר ב- Products כטבלה הראשית, ובחר ב- Total Sales כשאילתה השניה או כשאילתה הקשורה למיזוג. העמודה Total Sales תהפוך לעמודה חדשה הניתנת להרחבה.

  4. כדי להתאים את Total Sales ל- Products לפי ProductID, בחר בעמודה ProductID מהטבלה Products, ובעמודה Order_Details.ProductID מהטבלה Total Sales.

  5. בתיבת הדו-שיח רמות פרטיות:

    1. בחר באפשרות ארגוני עבור רמת בידוד הפרטיות עבור שני מקורות הנתונים.

    2. לחץ על שמור.

  6. לחץ על אישור.

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

    תיבת הדו-שיח 'מיזוג'

לאחר הלחיצה על אישור, הפעולה מיזוג יוצרת שאילתה. תוצאת השאילתה מכילה את כל העמודות מהטבלה הראשית (Products), ועמודה בודדת המכילה קישור ניווט לטבלה הקשורה (Total Sales). פעולת הרחבה מוסיפה עמודות חדשות לטבלה הראשית או לטבלת הנושא מהטבלה הקשורה.

מיזוג סופי

שלב 2: הרחבת עמודת מיזוג

בשלב זה תרחיב את עמודת המיזוג בשם NewColumn כדי ליצור שתי עמודות חדשות בשאילתה Products‏: Year ו- Total Sales.

הרחבת קישור הטבלה NewColumn

  1. ברשת תצוגה מקדימה של שאילתה, לחץ על סמל ההרחבה של NewColumn ( הרחב ).

  2. בתפריט הנפתח הרחבה:

    1. לחץ על (בחר את כל העמודות) כדי לנקות את כל העמודות.

    2. לחץ על Year ו- Total Sales.

    3. לחץ על אישור.

  3. שנה את השם של שתי עמודות אלה ל- Year ול- Total Sales.

  4. מיין בסדר יורד לפי Total Sales כדי לגלות אילו מוצרים קיבלו את נפח המכירות הגבוה ביותר ובאילו שנים.

  5. שנה את השם של השאילתה ל- Total Sales per Product.

הרחבת קישור טבלה

שלבי Power Query שנוצרו

בעת ביצוע פעולות של מיזוג שאילתה ב- Power Query, שלבי שאילתה נוצרים ומופיעים בחלונית הגדרות שאילתה, ברשימה שלבים שהוחלו. לכל שלב בשאילתה יש נוסחת Power Query תואמת, הנקראת גם שפת "M". לקבלת מידע נוסף אודות שפת הנוסחאות של Power Query, ראה למד אודות נוסחאות Power Query.

משימה

שלב בשאילתה

נוסחה

מיזוג ProductID בשאילתת Total Sales

מקור (מקור נתונים עבור פעולת המיזוג)

Table.NestedJoin

‎(Products,{"ProductID"},#"Total Sales",{"Order_Details.ProductID"},"NewColumn")‎

הרחבת עמודת מיזוג

ExpandNewColumn

Table.ExpandTableColumn

‎(Source, "NewColumn", {"Year", "Total Sales"}, {"NewColumn.Year", "NewColumn.Total Sales"})‎

RenamedColumns

Table.RenameColumns

‎(#"Expand NewColumn",{{"NewColumn.Year", "Year"}, {"NewColumn.Total Sales", "Total Sales"}})‎

SortedRows

Table.Sort

(RenamedColumns,{{"Total Sales", Order.Descending}}‎)

שלב 3: טעינת שאילתת Total Sales per Product במודל נתונים של Excel

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

טעינת השאילתה Total Sales per Product במודל הנתונים של Excel

  1. בחלונית הגדרות שאילתה, בטל את סימון התיבה טען לגליון עבודה וסמן את טען במודל נתונים.

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

טעינת מודל נתונים של Excel

שאילתת Total Sales per Product הסופית

לאחר שתבצע את כל השלבים, תהיה לך שאילתת Total Sales per Product המשלבת נתונים מהקובץ Products and Orders.xlsx ומהזנת ה- OData בשם Northwind. ניתן להחיל שאילתה זו על מודל Power Pivot. בנוסף, שינויים בשאילתה ב- Power Query יגרמו לשינוי ולרענון הטבלה שתתקבל כתוצאה מכך במודל Power Pivot.

לראש הדף

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

כיצד לראות עורך שאילתות ב- Excel

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

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×