שלב נתונים מחוברות עבודה מרובות ב- Excel (באמצעות Power Query)

Power Query יכול להיות לעזר רב כאשר אתה רוצה לשלב חוברות עבודה מרובות לחוברת עבודה אחת.

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

אם יש לך את חוברות העבודה במיקומים/תיקיות שונים, כדאי להעביר את כל אלה לתיקייה אחת (או ליצור עותק ולשים את העותק של חוברת העבודה באותה תיקיה).

אז ראשית, יש לי ארבעה חוברות עבודה בתיקייה (כפי שמוצג להלן).

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

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

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

כל חוברת עבודה מכילה את הנתונים בטבלת Excel עם אותו מבנה

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

מספר השורות בכל טבלה יכול להשתנות.

אל תדאג אם בחלק מלוחות Excel יש עמודות נוספות. אתה יכול לבחור אחת מהטבלאות כתבנית (או בתור 'המפתח' כפי ש- Power Query קורא לזה), ו- Power Query ישתמש בה כדי לשלב את כל שאר טבלאות האקסל.

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

עכשיו יש לי ארבעה חוברות עבודה בתיקייה שאני רוצה לשלב.

להלן תמונת מצב של הטבלה שיש לי באחד מחוברות העבודה.

להלן השלבים לשילוב הנתונים מחוברות העבודה הללו לחוברת עבודה אחת (כטבלה אחת).

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

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

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

אם ברצונך לערוך את הנתונים תחילה לפני טעינתם לאקסל, בשלב 6, בחר 'שלב וערוך'. פעולה זו תפתח את התוצאה הסופית בעורך Power Query, שם תוכל לערוך את הנתונים.

כמה דברים שכדאי לדעת:

• אם תבחר טבלת Excel כתבנית (בשלב 7), Power Query ישתמש בשמות העמודות בטבלה זו כדי לשלב את הנתונים מטבלאות אחרות. אם בטבלאות אחרות יש עמודות נוספות, התעלמות מהן תהיה. במקרה שלטבלאות אחרות אלה אין עמודה, המופיעה בטבלת התבניות שלך, Power Query פשוט היה שם 'null' עבורה.
• העמודות לא צריכות להיות באותו סדר שבו Power Query משתמשת בכותרות עמודות כדי למפות עמודות.
• מכיוון שבחרת בטבלה 1 כמפתח, Power Query יחפש את טבלה 1 בכל חוברות העבודה וישלב את כל אלה. במקרה שהוא לא מוצא טבלת Excel עם אותו שם (טבלה 1 בדוגמה זו), Power Query תיתן לך שגיאה.

הוספת קבצים חדשים לתיקייה

עכשיו בואו ניקח דקה ונבין מה עשינו עם השלבים שלעיל (שלקח לנו רק כמה שניות).

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

אבל זה לא הכל.

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

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

לדוגמה, בדוגמה שלמעלה, אם אוסיף חוברת עבודה חדשה - 'Mid-West.xlsx' לתיקייה ולרענן את השאילתה, הוא ייתן לי באופן מיידי את הנתונים המשולבים החדשים.

כך תוכל לרענן שאילתה:

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

בכל חוברת עבודה יש ​​את הנתונים עם אותו שם גליון עבודה

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

יש כמה דברים שאתה צריך להיות זהיר לגביהם כאשר מדובר רק בנתונים טבלאיים ולא בטבלת Excel.

• שמות גליונות העבודה צריכים להיות זהים. זה יעזור ל- Power Query לעבור על חוברות העבודה שלך ולשלב את הנתונים מדפי העבודה שיש להם אותו שם בכל חוברת עבודה.
• Power Query תלוי באותיות רישיות. המשמעות היא שגיליון עבודה בשם 'נתונים' ו'נתונים 'נחשבים שונים. באופן דומה, עמודה עם הכותרת 'חנות' ואחת עם 'חנות' נחשבות שונות.
• למרות שחשוב שיהיו אותן כותרות עמודות, לא חשוב שיהיה אותו סדר. אם עמודה 2 ב 'East.xlsx' היא עמודה 4 ב- 'West.xlsx', Power Query תתאים אותה כראוי על ידי מיפוי הכותרות.

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

בדוגמה זו, יש לי תיקייה עם ארבעה קבצים.

בכל חוברת עבודה יש ​​לי דף עבודה עם השם 'נתונים' המכיל את הנתונים בפורמט הבא (שים לב שזו אינה טבלת Excel).

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

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

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

אם ברצונך לערוך את הנתונים תחילה לפני טעינתם לאקסל, בשלב 6, בחר 'שלב וערוך'. פעולה זו תפתח את התוצאה הסופית בעורך Power Query, שם תוכל לערוך את הנתונים.

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

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

לדוגמה, נניח שאתה מקבל את הנתונים ממישהו שיצר מערכי נתונים אלה אך כינה את גליונות העבודה כ- Data East, Data West, Data North ו- South Data.

או שאולי האדם יצר טבלאות Excel, אך עם שמות שונים.

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

1. עבור לכרטיסייה נתונים.
2. בקבוצה Get & Transform, לחץ על התפריט הנפתח שאילתה חדשה.
3. העבר את הסמן על 'מתוך קובץ' ולחץ על 'מתיקייה'.
4. בתיבת הדו -שיח תיקייה, הזן את נתיב הקבצים של התיקייה המכילה את הקבצים, או לחץ על עיון ואתר את התיקיה.
5. לחץ על אישור.
6. בתיבת הדו -שיח שנפתחת, לחץ על הלחצן ערוך. פעולה זו תפתח את עורך Power Query שבו תראה את פרטי כל הקבצים בתיקייה.
7. החזק את מקש Ctrl ובחר בעמודות 'תוכן' ו'שם ', לחץ לחיצה ימנית ובחר' הסר עמודות אחרות '. פעולה זו תסיר את כל העמודות האחרות למעט העמודות שנבחרו.
8. בסרט עורך השאילתות, לחץ על 'הוסף עמודה' ולאחר מכן לחץ על 'עמודה מותאמת אישית'.
9. בתיבת הדו -שיח הוספת עמודה מותאמת אישית, קראו לעמודה החדשה בשם 'ייבוא ​​נתונים' והשתמשו בנוסחה הבאה = Excel.Workbook ([CONTENT]). שים לב שנוסחה זו תלויה באותיות רישיות ואתה צריך להזין אותה בדיוק כפי שהצגתי כאן.
10. כעת תראה טור חדש שכתובתו טבלה. עכשיו הרשה לי להסביר מה קרה כאן. סיפקת ל- Power Query את השמות של חוברות העבודה, ו- Power Query אחזר את האובייקטים כגון גליונות עבודה, טבלאות וטווחי שמות מכל חוברת עבודה (השוכנת בתא הטבלה נכון לעכשיו). תוכל ללחוץ על הרווח הלבן שליד טבלת הטקסט ותראה את המידע בתחתית. במקרה זה, מכיוון שיש לנו רק טבלה אחת ודף עבודה אחד בכל חוברת עבודה, תוכל לראות רק שתי שורות.
11. לחץ על סמל החץ הכפול בראש העמודה 'ייבוא ​​נתונים'.
12. בתיבת נתוני העמודות שנפתחת, בטל את הסימון של 'השתמש בעמודה המקורית כקידומת' ולאחר מכן לחץ על אישור.
13. כעת תראה טבלה מורחבת שבה תראה שורה אחת לכל אובייקט בטבלה. במקרה זה, עבור כל חוברת עבודה, אובייקט הגיליון ואובייקט הטבלה מפורטים בנפרד.
14. בעמודה סוג, סנן את הרשימה להצגת הטבלה בלבד.
15. החזק את מקש הבקרה ובחר בעמודה שם ונתונים. כעת, לחץ באמצעות לחצן העכבר הימני והסר את כל העמודות האחרות.
16. בעמודה נתונים, לחץ על סמל החץ הכפול בפינה השמאלית העליונה של כותרת הנתונים.
17. בתיבת נתוני העמודות שנפתחת, לחץ על אישור. פעולה זו תשלב את הנתונים בכל הטבלאות ותוצג ב- Power Query.
18. עכשיו אתה יכול לבצע כל שינוי שאתה צריך, ולאחר מכן עבור לכרטיסייה דף הבית ולחץ על סגור וטען.

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

בדוגמה זו, סיננו את הנתונים לשימוש בטבלאות Excel בלבד (בשלב 13). במקרה שאתה רוצה לשלב גיליונות ולא טבלאות, אתה יכול לסנן גיליונות.

שימו לב - טכניקה זו תעניק לכם את הנתונים המשולבים גם כאשר יש חוסר התאמה בשמות העמודות. לדוגמה, אם ב- East.xlsx, יש לך עמודה שגיאת כתיב שגויה, תקבל 5 עמודות. Power Query ימלא נתונים בעמודות אם היא תמצא אותם, ואם היא לא תוכל למצוא עמודה, היא תדווח על הערך 'null'.

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

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