בניית טבלאות ריבוי פורמטים מגיליון אחד ב- Power Query

ניסוח הבעיה

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

שים לב ש:

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

שתי הנחות חשובות. ההנחה היא ש:

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

המטרה הסופית היא לאסוף נתונים מכל הטבלאות לטבלה אחת מנורמלת שטוחה, נוחה לניתוח שלאחר מכן ולבניית סיכום, כלומר בטבלה זו:

שלב 1. התחבר לקובץ

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

כתוצאה מכך, יש לטעון את כל הנתונים ממנו לתוך עורך Power Query:

שלב 2. נקה את האשפה

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

שלב 3. הוספת מנהלים

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

1. בואו נוסיף עמודת עזר עם מספרי שורות באמצעות הפקודה הוסף עמודה - עמודת אינדקס - מ-0 (הוסף עמודה - עמודת אינדקס - מ-0).

2. הוסף עמודה עם נוסחה עם הפקודה הוספת עמודה - עמודה מותאמת אישית (הוסף עמודה - עמודה מותאמת אישית) והצג שם את הבנייה הבאה:

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

כדי לקבל את תא האב עם שם המשפחה, אנו מתייחסים תחילה לטבלה מהשלב הקודם #"אינדקס נוסף", ולאחר מכן ציין את שם העמודה שאנו צריכים [עמודה1] בסוגריים מרובעים ומספר התא בעמודה זו בסוגריים מסולסלים. מספר התא יהיה אחד פחות מהנוכחי, אותו אנו לוקחים מהעמודה מדד, בהתאמה.

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

שלב 4. קיבוץ לטבלאות נפרדות לפי מנהלים

השלב הבא הוא לקבץ את השורות עבור כל מנהל לטבלאות נפרדות. לשם כך, בלשונית טרנספורמציה, השתמשו בפקודה Group by (Transform – Group By) ובחלון שנפתח בחרו בעמודה מנהל ובפעולה All rows (All rows) פשוט לאסוף נתונים מבלי להחיל פונקציית צבירה כלשהי על אותם (סכום, ממוצע וכו'). פ.):

כתוצאה מכך, אנו מקבלים טבלאות נפרדות עבור כל מנהל:

שלב 5: שינוי טבלאות מקוננות

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

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

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

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

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

כדי להיפטר מעמודות ביניים מיותרות, יש לנו:

שלב 6 הרחב את הטבלאות המקוננות

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

... וסוף סוף קיבלנו את מה שרצינו:

אתה יכול לייצא את הטבלה שהתקבלה בחזרה לאקסל באמצעות הפקודה בית - סגור וטען - סגור וטען פנימה... (בית — סגור&טען — סגור&טען אל...).

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