בנו טבלאות עם כותרות שונות ממספר ספרים

ניסוח הבעיה

יש לנו כמה קבצים (בדוגמה שלנו - 4 חלקים, במקרה הכללי - כמה שתרצה) בתיקייה אחת דוחות לדוגמא:

בפנים, הקבצים האלה נראים כך:

היכן:

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

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

שלב 1. הכנת ספרייה של שמות עמודות

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

אנו ממירים רשימה זו לטבלה "חכמה" דינמית באמצעות הלחצן עיצוב כטבלה בכרטיסייה עמוד הבית (בית - עיצוב כטבלה) או קיצור מקלדת Ctrl+T וטען אותו לתוך Power Query עם הפקודה נתונים - מטבלה/טווח (נתונים - מטבלה/טווח). בגירסאות האחרונות של Excel, שמו שונה ל- עם עלים (מהגיליון).

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

=Table.ToRows(מקור)

פקודה זו תמיר את זה שנטען בשלב הקודם מָקוֹר טבלת הפניות לרשימה המורכבת מרשימות מקוננות (List), שכל אחת מהן, בתורה, היא זוג ערכים זה היה-הפך משורה אחת:

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

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

שלב 2. אנו טוענים הכל מכל הקבצים כפי שהם

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

בחלון התצוגה המקדימה, לחץ המרת (שינוי צורה) or שינוי (לַעֲרוֹך):

ולאחר מכן הרחב את התוכן של כל הקבצים שהורדת (בינארי) לחצן עם חיצים כפולים בכותרת העמודה תוֹכֶן:

Power Query בדוגמה של הקובץ הראשון (Vostok.xlsx) ישאל אותנו את שם הגיליון שאנו רוצים לקחת מכל חוברת עבודה - בחר תמונות ולחץ על אישור:

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

1. Power Query ייקח את הקובץ הראשון מהתיקיה (זה יהיה לנו Vostok.xlsx — לִרְאוֹת דוגמה לקובץ) כדוגמה ומייבא את התוכן שלו על ידי יצירת שאילתה המר קובץ לדוגמה. לשאילתה זו יהיו כמה שלבים פשוטים כמו מָקוֹר (גישה לקובץ) ניווט (בחירת גיליון) ואולי העלאת הכותרות. בקשה זו יכולה לטעון נתונים מקובץ ספציפי אחד בלבד Vostok.xlsx.
   
2. על סמך בקשה זו, תיווצר הפונקציה המשויכת אליה המרת קובץ (מסומן על ידי סמל אופייני f_x), שבו קובץ המקור כבר לא יהיה קבוע, אלא ערך משתנה - פרמטר. לפיכך, פונקציה זו יכולה לחלץ נתונים מכל ספר שאנו מחליקים אליו כארגומנט.
   
3. הפונקציה תיושם בתורה על כל קובץ (בינארי) מהעמודה תוֹכֶן – step אחראי לכך התקשר לפונקציה מותאמת אישית בשאילתה שלנו שמוסיפה עמודה לרשימת הקבצים המרת קובץ עם תוצאות ייבוא ​​מכל חוברת עבודה:

   

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

   
   

שלב 3. שיוף

צילום המסך הקודם מראה בבירור שההרכבה הישירה "כמו שהיא" התבררה כאיכות ירודה:

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

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

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

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

= Table.RenameColumns(#"Elevated Headers", Headers, MissingField.Ignore)

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

בעצם, זה הכל.

חוזר לבקשה דוחות לדוגמא נראה תמונה שונה לחלוטין - הרבה יותר יפה מהקודמת:

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