מיזוג טבלאות ב- Excel באמצעות שאילתת כוח (מדריך פשוט שלב אחר שלב)

עם Power Query, העבודה עם נתונים המפוזרים בין דפי עבודה או אפילו חוברות עבודה הפכה לקלה יותר.

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

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

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

נניח שיש לך טבלה כפי שמוצג להלן:

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

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

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

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

יהיה עליך למפות את הרשומות הרלוונטיות מטבלה 1 עם נתונים מטבלה 2 ו -3.

עכשיו אתה יכול להסתמך על VLOOKUP או INDEX/MATCH כדי לעשות זאת.

או שאם אתה משתוקק VBA, תוכל לכתוב קוד לשם כך.

אך האפשרויות הללו גוזלות זמן ומורכבות בהשוואה ל- Power Query.

במדריך זה אראה לך כיצד למזג שלוש טבלאות אקסל אלה לאחת.

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

הערה: ניתן להשתמש ב- Power Query כתוסף ב- Excel 2010 ו- 2013, והיא תכונה מובנית מ- Excel 2016 ואילך. בהתבסס על הגרסה שלך, תמונות מסוימות עשויות להיראות שונות (צילומי תמונות המשמשים במדריך זה הם מ- Excel 2016).

מיזוג טבלאות באמצעות שאילתת חשמל

שמתי את הטבלאות האלה כפי שמוצג להלן:

1. לוח 1 - תאריך מכירות
2. שולחן 2 - Pdt_Id
3. שולחן 3 - אזור

אין חובה לשנות את שם הטבלאות הללו, אך עדיף לתת שמות המתארים על מה הטבלה.

בבת אחת אתה יכול למזג רק שתי טבלאות ב- Power Query.

אז קודם כל נצטרך למזג את טבלה 1 וטבלה 2 ולאחר מכן למזג לתוכה את הטבלה 3 בשלב הבא.

מיזוג טבלה 1 וטבלה 2

כדי למזג טבלאות, תחילה עליך להמיר טבלאות אלה לחיבורים ב- Power Query. ברגע שיש לך את החיבורים, תוכל למזג אותם בקלות.

להלן השלבים לשמירת טבלת Excel כחיבור ב- Power Query:

1. בחר תא כלשהו בטבלת Sales_Data.
2. לחץ על הכרטיסייה נתונים.
3. בקבוצה Get & Transform, לחץ על 'מטבלה/טווח'. פעולה זו תפתח את עורך השאילתות.
4. בעורך השאילתות, לחץ על הכרטיסייה 'קובץ'.
5. לחץ על האפשרות 'סגור וטען אל'.
6. בתיבת הדו -שיח 'ייבוא ​​נתונים', בחר 'צור רק חיבור'.
7. לחץ על אישור.

השלבים שלעיל ייצרו חיבור עם השם Sales_Data (או כל שם שנתת לטבלת Excel).

חזור על השלבים לעיל עבור טבלה 2 וטבלה 3.

אז כשתסיים, יהיו לך שלושה חיבורים (עם השם Sales_Data, Pdt_Id ואזור).

כעת נראה כיצד למזג את הטבלה Sales_Data ו- Pdt_Id.

1. לחץ על הכרטיסייה נתונים.
2. בקבוצה Get & Transform Data, לחץ על קבל נתונים.
3. בתפריט הנפתח, לחץ על שלב שאילתות.
4. לחץ על מיזוג. פעולה זו תפתח את תיבת הדו -שיח מיזוג.
5. בתיבת הדו -שיח מיזוג, בחר 'Sales_Data' מהתפריט הנפתח הראשון.
6. בחר 'Pdt_Id' מהתפריט הנפתח השני.
7. בתצוגה המקדימה של 'Sales_Data', לחץ על העמודה 'פריט'. פעולה זו תבחר את העמודה כולה.
8. בתצוגה המקדימה של 'Pdt_Id', לחץ על העמודה 'פריט'. פעולה זו תבחר את העמודה כולה.
9. בתפריט הנפתח 'הצטרף לסוג', בחר 'שמאל חיצוני (הכל מהראשון, תואם מהשני)'.
10. לחץ על אישור.

השלבים שלעיל יפתחו את עורך השאילתות ויציגו לך את הנתונים מתוך Sales_Data עם עמודה נוספת אחת (של Pdt_Id).

מיזוג טבלאות האקסל (טבלאות 1 ו -2)

כעת תהליך מיזוג הטבלאות יתרחש בתוך עורך השאילתות בשלבים הבאים:

1. בעמודה הנוספת (Pdt_Id), לחץ על החץ הכפול בכותרת.
2. מתיבת האפשרויות שנפתחת, בטל את הסימון של כל שמות העמודות ובחר רק פריט. הסיבה לכך היא שכבר יש לנו את עמודת שם המוצר בטבלה הקיימת, ואנו רוצים רק את מזהה המוצר עבור כל מוצר.
3. בטל את הסימון של האפשרות 'השתמש בשם העמודה המקורית כתחילית'.
4. לחץ על אישור.

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

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

אבל יש לנו שלושה טבלאות למיזוג, כך שיש עוד עבודה.

עליך לשמור את הטבלה המתקבלת כחיבור (כדי שנוכל למזג אותה עם טבלה 3).

להלן השלבים לשמירת הטבלה הממוזגת הזו (עם נתונים מטבלת Sales_Data ו- Pdt_Id) כחיבור:

1. לחץ על הכרטיסייה קובץ
2. לחץ על האפשרות 'סגור וטען ל'.
3. בתיבת הדו -שיח 'ייבוא ​​נתונים', בחר 'צור רק חיבור'.
4. לחץ על אישור.

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

מיזוג טבלה 3 עם הטבלה המתקבלת

תהליך מיזוג הטבלה השלישית עם הטבלה שהתקבלה (שקיבלנו על ידי מיזוג טבלה 1 וטבלה 2) זהה לחלוטין.

להלן השלבים למיזוג הטבלאות הללו:

1. לחץ על הכרטיסייה נתונים.
2. בקבוצה Get & Transform Data, לחץ על 'קבל נתונים'.
3. בתפריט הנפתח, לחץ על 'שלב שאילתות'.
4. לחץ על 'מיזוג'. פעולה זו תפתח את תיבת הדו -שיח מיזוג.
5. בתיבת הדו -שיח מיזוג, בחר 'מיזוג 1' מהתפריט הנפתח הראשון.
6. בחר 'אזור' מהתפריט הנפתח השני.
7. בתצוגה המקדימה של 'מיזוג 1', לחץ על העמודה 'נציג מכירות'. פעולה זו תבחר את העמודה כולה.
8. בתצוגה מקדימה של אזור, לחץ על העמודה 'נציג מכירות'. פעולה זו תבחר את העמודה כולה.
9. בתפריט הנפתח 'הצטרף לסוג', בחר שמאל חיצוני (הכל מהראשון, התאמה מהשני).
10. לחץ על אישור.

השלבים שלעיל יפתחו את עורך השאילתות ויציגו לך את הנתונים ממיזוג 1 עם עמודה אחת נוספת (אזור).

כעת תהליך מיזוג הטבלאות יתרחש בתוך עורך השאילתות בשלבים הבאים:

1. בעמודה הנוספת (אזור), לחץ על החץ הכפול בכותרת.
2. מתיבת האפשרויות שנפתחת, בטל את הסימון של כל שמות העמודות ובחר רק אזור.
3. בטל את הסימון של האפשרות 'השתמש בשם העמודה המקורית כתחילית'.
4. לחץ על אישור.

השלבים שלעיל יתנו לך טבלה שכל מיזוג שלוש הטבלאות (טבלת Sales_Data עם עמודה אחת עבור Pdt_Id ואחת עבור אזור).

להלן השלבים לטעון טבלה זו ב- Excel:

1. לחץ על הכרטיסייה קובץ.
2. לחץ על 'סגור וטען ל'.
3. בתיבת הדו -שיח 'ייבוא ​​נתונים', בחר באפשרות טבלה ודפי עבודה חדשים.
4. לחץ על אישור.

זה ייתן לך את הטבלה הממוזגת שהתקבלה בדף עבודה חדש.

אחד הדברים הטובים ביותר ב- Power Query הוא שתוכל להכיל בקלות כל שינוי בנתונים הבסיסיים (טבלה 1, 2 ו -3) פשוט על ידי רענוןם.

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

תוך שניות תקבל את הטבלה הממוזגת החדשה.

אולי תאהב גם את שיעורי שאילתת החשמל הבאים:

• שלב נתונים מחוברות עבודה מרובות ב- Excel (באמצעות Power Query).
• שלב נתונים מגליונות עבודה מרובים לגליון עבודה יחיד ב- Excel.
• כיצד לבטל סיבוב נתונים ב- Excel באמצעות שאילתת Power (aka Get & Transform)
• קבל רשימה של שמות קבצים מתיקיות ותיקיות משנה (באמצעות Power Query)