טבלת ציר על פני מספר טווחי נתונים

ניסוח הבעיה

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

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

• לטבלאות יכולות להיות כל מספר של שורות עם כל נתונים, אבל עליהן להיות אותה כותרת.
• לא אמורים להיות נתונים נוספים בגיליונות עם טבלאות מקור. סדין אחד - שולחן אחד. כדי לשלוט, אני ממליץ לך להשתמש בקיצור מקלדת Ctrl+סוֹף, אשר מעביר אותך לתא בשימוש האחרון בגליון העבודה. באופן אידיאלי, זה צריך להיות התא האחרון בטבלת הנתונים. אם כשאתה לוחץ על Ctrl+סוֹף כל תא ריק מימין או מתחת לטבלה מסומן - מחק את העמודות הריקות הללו מימין או שורות מתחת לטבלה אחרי הטבלה ושמור את הקובץ.

שיטה 1: בניית טבלאות עבור ציר באמצעות Power Query

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

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

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

בחלון שיופיע בחרו כל גיליון (לא משנה איזה) ולחצו על הכפתור למטה שינוי (לַעֲרוֹך):

חלון Power Query Query Editor אמור להיפתח על גבי Excel. בצד ימין של החלון בלוח פרמטרים בקשה מחק את כל השלבים שנוצרו אוטומטית מלבד הראשון - מָקוֹר (מָקוֹר):

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

מחק את כל העמודות מלבד העמודות נתוניםעל ידי לחיצה ימנית על כותרת עמודה ובחירה מחק עמודות אחרות (לְהַסִיר עמודות אחרות):

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

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

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

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

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

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

שיטה 2. אנו מאחדים טבלאות עם פקודת UNION SQL במאקרו

פתרון נוסף לבעיה שלנו מיוצג על ידי מאקרו זה, שיוצר מערך נתונים (מטמון) עבור טבלת הציר באמצעות הפקודה אַחְדוּת שפת שאילתות SQL. פקודה זו משלבת טבלאות מכל מה שצוינו במערך שמות גיליונות גיליונות של הספר לטבלת נתונים אחת. כלומר, במקום להעתיק פיזית ולהדביק טווחים בין גיליונות שונים לאחד, אנחנו עושים את אותו הדבר ב-RAM של המחשב. לאחר מכן המאקרו מוסיף גיליון חדש עם השם הנתון (משתנה ResultSheetName) ויוצר עליו סיכום מלא (!) על סמך המטמון שנאסף.

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

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant שם הגיליון שבו הציר המתקבל יוצג ResultSheetName = "Pivot of sheet" שמות עם טבלאות מקור SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'אנו יוצרים מטמון עבור טבלאות מגיליונות מ-SheetsNames עם ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) עבור i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" הבא i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) סיים עם 'צור מחדש את הגיליון כדי להציג את טבלת הציר שהתקבלה בשגיאה המשך יישום הבא.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo ט. Name = ResultSheetName 'הצג את תקציר המטמון שנוצר בגיליון זה Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotCache.CreatePstinivT"(3) Set objPivotCaches. objPivotCache = Nothing Range("A3"). בחר ב- End With End Sub    

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

חסרונות של גישה זו:

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

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

וואלה!

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

	 ספק = Microsoft.Jet.OLEDB.4.0;  

ל:

	ספק=Microsoft.ACE.OLEDB.12.0;  

והורד והתקן את מנוע עיבוד הנתונים החינמי מ-Access מאתר Microsoft - Microsoft Access Database Engine 2010 Redistributable

שיטה 3: איחוד את אשף PivotTable מגרסאות ישנות של Excel

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

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

לאחר לחיצה על כפתור התוספת, עליך לבחור את האפשרות המתאימה בשלב הראשון של האשף:

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

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

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