החלפת חישובים בטבלת PivotTable עם פריסה

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

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

שלב 1. חיבור התוספת Power Pivot

אם הכרטיסיות של תוסף Power Pivot אינן גלויות באקסל שלך, תחילה יהיה עליך להפעיל אותה. ישנן שתי אפשרויות לכך:

• Tab מפתחים - כפתור תוספות COM (מפתח - תוספות COM)
• קובץ – אפשרויות – תוספות – תוספות COM – עבור (קובץ — אפשרויות — תוספות — COM-תוספות — עבור אל)

אם זה לא עוזר, נסה להפעיל מחדש את Microsoft Excel.

שלב 2: טען נתונים למודל הנתונים של Power Pivot

יהיו לנו שתי טבלאות כנתונים ראשוניים:

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

יש להמיר את שתי הטבלאות הללו ל"חכם" (דינמי) עם קיצור מקלדת Ctrl+T או צוות בית - עיצוב כטבלה (בית - עיצוב כטבלה) ורצוי לתת להם שמות שפויים בלשונית בנאי (לְעַצֵב). תן לזה להיות, למשל, מבצעים и שירותים.

לאחר מכן, כל טבלה בתורה צריכה להיטען למודל הנתונים - לשם כך אנו משתמשים בכרטיסייה powerpivot לַחְצָן הוסף למודל נתונים (הוסף למודל נתונים).

שלב 3. צור מידה כדי לקבוע את הכפתור שנלחץ על הפרוסה

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

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

אם אתה חוזר על זה לא ב-Power Pivot, אלא ב-Power BI, אין צורך בנקודתיים ובמקום זאת ערכים אתה יכול להשתמש במקבילו המודרני יותר - הפונקציה SELECTEDVALUE.

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

שלב 4. צור מידה לחישוב על הכפתור לחוץ

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

בואו נפרק את זה חלק אחר חלק:

1. פונקציה SWITCH - אנלוגי של IF מקונן - בודק את מילוי התנאים שצוינו ומחזיר ערכים שונים בהתאם למילוי חלק מהם.
2. פונקציה נָכוֹן() – נותן "אמת" לוגי כך שהתנאים שנבדקו מאוחר יותר על ידי הפונקציה SWITCH פועלים רק אם הם מתקיימים, כלומר אמת.
3. לאחר מכן אנו בודקים את ערך המדד שלוחצים על הכפתור ומחשבים את התוצאה הסופית עבור שלוש אפשרויות שונות - כסכום העלות, הבדיקה הממוצעת ומספר המשתמשים הייחודיים. כדי לספור ערכים ייחודיים, השתמש בפונקציה DISTINCTCOUNT, ולעיגול - ROUND.
4. אם אף אחד משלושת התנאים לעיל לא מתקיים, אזי מוצג הארגומנט האחרון של הפונקציה SWITCH - אנו מגדירים אותו כדמה באמצעות הפונקציה רֵיק().

שלב 5. בניית סיכום והוספת פרוסה

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

לאחר מכן:

1. אנחנו זורקים את המגרש המוצר מהשולחן מבצעים לאזור שורות (שורות).
2. זורקים לשם שדה תוֹצָאָה מהשולחן שירותים.
3. קליק ימני על השדה תוֹצָאָהולבחור צוות להוסיף כפרוסה (הוסף כ-Slicer).
4. זורקים את המידה השנייה מסקנה מהשולחן שירותים לאזור ערכים (ערכים).

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

יופי 🙂

• היתרונות של Pivot לפי מודל נתונים
• ניתוח תוכנית-עובדות בטבלת ציר ב-Power Pivot
• צור מסד נתונים ב-Excel באמצעות תוסף Power Pivot