סינון מספר טבלאות PivotTable בו זמנית

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

שיטה 1: General Slicer לסינון pivots באותו מקור נתונים

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

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

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

שיטה 2. פרוסה כללית לסינון סיכומים על מקורות שונים

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

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

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

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

אנו עושים את הפעולות הבאות:

1. הפיכת השולחנות המקוריים שלנו לשולחנות חכמים דינמיים עם קיצור מקלדת Ctrl+T או פקודות בית - עיצוב כטבלה (בית - עיצוב כטבלה) ולתת להם שמות tablProdaji и tabTransport כרטיסייה בנאי (לְעַצֵב).

2. טען את שתי הטבלאות בתורן לדגם באמצעות הכפתור הוסף למודל נתונים בכרטיסייה Power Pivot.

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

3. לאחר שבחרנו תא כלשהו בתוך הטבלה "החכמה", אנו טוענים אותם אחד אחד ב- Power Query עם הכפתור משולחן/טווח כרטיסייה נתונים (נתונים - מטבלה/טווח) ולאחר מכן בחלון Power Query בחר על העיקרי צוותי סגור וטען - סגור וטען פנימה (בית — סגור&טען — סגור&טען אל...) ואפשרות ייבוא פשוט צור קשר (יצירת חיבור בלבד):

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

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

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

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

8. כעת תוכל ליצור את כל טבלאות הציר הנדרשות עבור המודל שנוצר באמצעות הכפתור טבלת סיכום (טבלת ציר) on העיקרי (בית) הכרטיסייה בחלון Power Pivot, ועל ידי בחירת תא כלשהו בכל ציר, בכרטיסייה אָנָלִיזָה כפתור הוסף פרוסה הדבק פרוסה (נתח - הכנס פורס) ובחר לפרוס בתיבת הרשימה עִיר בספרייה שנוספה:

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