✅ שלב נתונים מגליונות עבודה מרובים לגליון עבודה יחיד ב- Excel

תוכן העניינים

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

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

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

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

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

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

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

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

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

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

כל אחד מדפי העבודה האלה מכיל את הנתונים בטבלת Excel, ומבנה הטבלה עקבי (כלומר הכותרות זהות).

לחץ כאן להורדת הנתונים והמשך.

קל לשלב סוג זה של נתונים באמצעות Power Query (שעובד ממש טוב עם נתונים בטבלת Excel).

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

נתתי לטבלאות את השמות הבאים: East_Data, West_Data, North_Data ו- South_Data.

להלן השלבים לשילוב גליונות עבודה מרובים עם טבלאות Excel באמצעות שאילתת Power:

עבור לכרטיסייה נתונים.
בקבוצה Get & Transform Data, לחץ על האפשרות 'קבל נתונים'.
עבור לאפשרות 'ממקורות אחרים'.
לחץ על האפשרות 'שאילתא ריקה'. פעולה זו תפתח את עורך Power Query.
בעורך השאילתות, הקלד את הנוסחה הבאה בשורת הנוסחאות: = Excel.CurrentWorkbook(). שים לב כי נוסחאות Power Query תלויות באותיות רישיות, כך שעליך להשתמש בנוסחה המדויקת כאמור (אחרת תקבל שגיאה).
הקש על מקש Enter. פעולה זו תציג בפניך את כל שמות הטבלאות בחוברת העבודה כולה (היא תראה לך גם את הטווחים ו/או החיבורים במקרה שהם קיימים בחוברת העבודה).
[שלב אופציונלי] בדוגמה זו, אני רוצה לשלב את כל הטבלאות. אם ברצונך לשלב טבלאות ספציפיות של Excel בלבד, תוכל ללחוץ על הסמל הנפתח בכותרת השם ולבחור את אלה שברצונך לשלב. באופן דומה, אם יש לך שמות של טווחים או חיבורים וברצונך לשלב רק טבלאות, תוכל להסיר גם את הטווחים ששמם.
בתא כותרת התוכן, לחץ על החץ הכפול.
בחר את העמודות שברצונך לשלב. אם ברצונך לשלב את כל העמודות, ודא (בחר כל העמודות) מסומן.
בטל את הסימון של האפשרות 'השתמש בשם העמודה המקורית כקידומת'.
לחץ על אישור.

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

אם תסתכל מקרוב, תמצא בעמודה האחרונה (הימנית ביותר) את שם טבלאות ה- Excel (East_Data, West_Data, North_Data ו- South_Data). זהו מזהה המספר לנו איזו רשומה הגיעה מאיזו טבלת Excel. זו גם הסיבה שאמרתי שעדיף שיהיו שמות תיאוריים לטבלאות האקסל.

להלן מספר שינויים שתוכל לבצע בנתונים המשולבים ב- Power Query עצמו:

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

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

כדי לעשות זאת. בצע את השלבים הבאים:

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

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

בעיה אחת שעליך לפתור בעת שימוש בשיטה זו

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

עיין במספר השורות של הנתונים המשולבים - 1304 (שזה נכון).

עכשיו, אם ארענן את השאילתה, מספר השורות ישתנה ל -2607. רענן שוב והוא ישתנה ל- 3910.

הנה הבעיה.

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

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

הבה נבין את הסיבה לבעיה זו וכיצד לתקן אותה.

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

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

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

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

זה נקרא רקורסיה.

להלן כיצד לפתור בעיה זו.

לאחר הכנסת = Excel.CurrentWorkbook () בשורת הנוסחאות של Power Query והקש Enter, תקבל רשימה של טבלאות Excel. כדי לוודא שאתה יכול לשלב רק את הטבלאות מגליון העבודה, עליך לסנן איכשהו רק את הטבלאות האלה שברצונך לשלב ולהסיר את כל השאר.

להלן השלבים לוודא שיש לך רק את הטבלאות הנדרשות:

לחץ על התפריט הנפתח והעבר את הסמן על מסנני טקסט.
לחץ על האפשרות מכיל.
בתיבת הדו -שיח שורות מסנן, הזן _Data בשדה שליד האפשרות 'מכיל'.
לחץ על אישור.

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

שים לב שבשלבים שלעיל השתמשנו ב- "_נתונים"כדי לסנן כפי ששמנו את הטבלאות כך. אבל מה אם הטבלאות שלך אינן נקראות בעקביות. מה אם כל שמות הטבלאות אקראיים ואין להם שום מכנה משותף.

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

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

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

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