הרכבת טבלאות מקובצי Excel שונים עם Power Query

ניסוח הבעיה

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

נניח שיש לנו את התיקיה הבאה, המכילה מספר קבצים עם נתונים מערי סניפים:

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

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

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

אנחנו בוחרים כלי נשק

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

אם אין לך אקסל 2013 או 2016, אז אתה לא יכול לקרוא עוד (רק בצחוק). בגירסאות ישנות יותר של אקסל, משימה כזו יכולה להתבצע רק על ידי תכנות מאקרו ב-Visual Basic (שקשה מאוד למתחילים) או על ידי העתקה ידנית מונוטונית (שלוקחת זמן רב ויוצרת שגיאות).

שלב 1. ייבא קובץ אחד כדוגמה

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

• אם יש לך Excel 2016, פתח את הכרטיסייה נתונים ולאחר מכן צור שאילתה - מקובץ - מהספר (נתונים - שאילתה חדשה- מקובץ - מ-Excel)
• אם יש לך Excel 2010-2013 עם התוסף Power Query מותקן, ואז פתח את הכרטיסייה שאילתת כוח ובחר בו מתוך קובץ - מתוך ספר (מתוך קובץ - מאקסל)

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

אם תלחץ על הכפתור בפינה הימנית התחתונה של חלון זה הורדה (לִטעוֹן), אז הטבלה תיובא מיד לגיליון בצורתה המקורית. עבור קובץ בודד זה טוב, אבל אנחנו צריכים לטעון הרבה קבצים כאלה, אז נלך קצת אחרת ונלחץ על הכפתור תיקון (לַעֲרוֹך). לאחר מכן, עורך השאילתות Power Query אמור להיות מוצג בחלון נפרד עם הנתונים שלנו מהספר:

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

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

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

יש ליצור עמודה חדשה עם שמות הטקסט של החודש עבור כל שורה. על ידי לחיצה כפולה על כותרת עמודה, אתה יכול לשנות את שמה העתק תאריך לנוח יותר חוֹדֶשׁ, למשל.

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

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

יתר על כן, כל הטרנספורמציות שבוצעו קבועות בלוח הימני, שם תמיד ניתן להחזיר אותן לאחור (לחצות) או לשנות את הפרמטרים שלהן (הילוך):

קליל ואלגנטי, לא?

שלב 2. בואו נהפוך את הבקשה שלנו לפונקציה

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

בעורך השאילתות, עבור ללשונית תצוגה ולחץ על הכפתור עורך מתקדם (תצוגה - עורך מתקדם). אמור להיפתח חלון שבו כל הפעולות הקודמות שלנו ייכתבו בצורה של קוד בשפת M. שימו לב שהנתיב לקובץ שייבאנו עבור הדוגמה מקודד בקוד קשיח:

עכשיו בואו נעשה כמה התאמות:

המשמעות שלהם פשוטה: השורה הראשונה (filepath)=> הופך את הפרוצדורה שלנו לפונקציה עם ארגומנט נתיב, ולמטה נשנה את הנתיב הקבוע לערך של משתנה זה. 

את כל. לחץ על סיום וצריך לראות את זה:

אל תפחדו שהנתונים נעלמו – למעשה הכל בסדר, הכל אמור להיראות כך 🙂 יצרנו בהצלחה את הפונקציה המותאמת אישית שלנו, שבה כל האלגוריתם לייבוא ​​ועיבוד נתונים נזכר מבלי להיות קשור לקובץ ספציפי . נותר לתת לו שם מובן יותר (למשל getData) בחלונית מימין בשדה שם פרטי ואתה יכול לקצור דף הבית — סגור והורד (בית - סגור וטען). שימו לב שהנתיב לקובץ שייבאנו עבור הדוגמה מקודד בקוד קשה. אתה תחזור לחלון הראשי של Microsoft Excel, אבל לוח עם החיבור שנוצר לפונקציה שלנו אמור להופיע בצד ימין:

שלב 3. איסוף כל הקבצים

כל החלק הכי קשה מאחוריו, החלק הנעים והקל נשאר. עבור אל הכרטיסייה נתונים - צור שאילתה - מקובץ - מתיקיה (נתונים - שאילתה חדשה - מקובץ - מתיקיה) או, אם יש לך Excel 2010-2013, בדומה לכרטיסייה שאילתת כוח. בחלון שמופיע, ציין את התיקיה שבה נמצאים כל קבצי עיר המקור שלנו ולחץ OK. השלב הבא אמור לפתוח חלון שבו יופיעו כל קבצי האקסל שנמצאים בתיקייה זו (ותיקיות המשנה שלה) ופרטים עבור כל אחת מהן:

נְקִישָׁה שינוי (לַעֲרוֹך) ושוב אנחנו נכנסים לחלון עורך השאילתות המוכר.

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

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

עכשיו בואו נמחק את כל העמודות המיותרות (כמו באקסל, באמצעות לחצן העכבר הימני – להסיר), להשאיר רק את העמודה שנוספה ואת העמודה עם שם הקובץ, מכיוון ששם זה (ליתר דיוק, העיר) יהיה שימושי בנתונים הכוללים של כל שורה.

ועכשיו "רגע הוואו" - לחץ על הסמל עם החצים שלו בפינה הימנית העליונה של העמודה שנוספה עם הפונקציה שלנו:

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

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

את כל! לחץ על בית - סגור וטען (בית - סגור וטען). כל הנתונים שנאספו על ידי השאילתה עבור כל הערים יועלו לגיליון האקסל הנוכחי בפורמט "טבלה חכמה":

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

בעתיד, עם כל שינוי בתיקייה (הוספה או הסרה של ערים) או בקבצים (שינוי מספר השורות), יספיק ללחוץ לחיצה ימנית ישירות על הטבלה או על השאילתה בחלונית הימנית ולבחור את פקודה עדכן ושמור (לְרַעֲנֵן) - Power Query "יבנה מחדש" את כל הנתונים שוב תוך מספר שניות.

PS

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

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

וזה הכל! שִׁיר!

• עיצוב מחדש של הלוח הצלב לכזה שטוח המתאים לבניית טבלאות ציר
• בניית תרשים בועות מונפש ב-Power View
• מאקרו להרכבת גיליונות מקבצי Excel שונים לאחד