היתרונות של Pivot לפי מודל נתונים

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

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

עם זאת, לפני שנבחן את ה"לחמניות" הללו מקרוב, בואו נבין תחילה מהו, למעשה, מודל הנתונים הזה?

מהו מודל נתונים

מודל נתונים (בקיצור MD או DM = Data Model) הוא אזור מיוחד בתוך קובץ אקסל שבו ניתן לאחסן נתונים טבלאיים - טבלה אחת או יותר המקושרת, אם תרצה, זו לזו. למעשה, זהו מסד נתונים קטן (קוביית OLAP) המוטבע בתוך חוברת עבודה של Excel. בהשוואה לאחסון הקלאסי של נתונים בצורה של טבלאות רגילות (או חכמות) על גיליונות של Excel עצמו, למודל הנתונים מספר יתרונות משמעותיים:

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

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

אם כרטיסיות מפתחים (מפתח)אתה לא יכול לראות את זה על הסרט, אתה יכול להפעיל אותו דרך קובץ – אפשרויות – הגדרת סרט (קובץ - אפשרויות - התאמה אישית של סרט). אם בחלון המוצג למעלה ברשימת תוספות ה-COM אין לך Power Pivot, אז זה לא כלול בגרסה שלך של Microsoft Office 🙁

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

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

טען טבלאות למודל הנתונים

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

לאחר מכן תוכל להשתמש בכל אחת משלוש השיטות לבחירה:

• לחץ על לחצן הוסף לדגם (הוסף למודל נתונים) כרטיסייה powerpivot כרטיסייה עמוד הבית (בית).
  
• בחירת צוותים הוספה - PivotTable (הוסף - טבלת ציר) והפעל את תיבת הסימון הוסף נתונים אלה למודל הנתונים (הוסף את הנתונים האלה למודל הנתונים). במקרה זה, על פי הנתונים שנטענו במודל, נבנית מיד גם טבלת ציר.
  
• בכרטיסיה מתקדם נתונים (תַאֲרִיך) לחץ על הכפתור מתוך טבלה/טווח (מטבלה/טווח)כדי לטעון את הטבלה שלנו לעורך Power Query. הנתיב הזה הוא הארוך ביותר, אבל אם תרצה, כאן אתה יכול לבצע ניקוי נתונים נוספים, עריכה וכל מיני טרנספורמציות, שבהם Power Query חזק מאוד.

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

אנו בונים סיכום של מודל הנתונים

כדי לבנות מודל נתונים מסכם, אתה יכול להשתמש בכל אחת משלוש גישות:

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

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

יתרון 1: קשרים בין טבלאות ללא שימוש בנוסחאות

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

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

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

יתרון 2: ספירת ערכים ייחודיים

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

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

יתרון 3: נוסחאות DAX מותאמות אישית

לפעמים צריך לבצע חישובים נוספים שונים בטבלאות ציר. בסיכומים רגילים זה נעשה באמצעות שדות ואובייקטים מחושבים, בעוד שסיכום מודל הנתונים משתמש במדדים בשפת DAX מיוחדת (DAX = Data Analysis Expressions).

כדי ליצור מידה, בחר בכרטיסייה powerpivot פיקוד מידות - צור מידה (מידות - מידה חדשה) או פשוט לחץ לחיצה ימנית על הטבלה ברשימה Pivot Fields ובחר הוסף מידה (הוסף מידה) בתפריט ההקשר:

בחלון שנפתח, הגדר:

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

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

יתרון 4: היררכיות שדות מותאמות אישית

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

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

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

יתרון 5: שבלונות מותאמות אישית

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

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

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

כל הקבוצות שנוצרו יוצגו בחלונית PivotTable Fields בתיקייה נפרדת, ממנה ניתן לגרור אותן בחופשיות לאזורי השורות והעמודות של כל PivotTable חדש:

יתרון 6: הסתר טבלאות ועמודות באופן סלקטיבי

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

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

יתרון 7. קידוח מתקדם

If you double-click on any cell in the value area in a regular pivot table, then Excel displays on a separate sheet a copy of the source data fragment that was involved in the calculation of this cell. This is a very handy thing, officially called Drill-down (in they usually say “fail”).

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

לאחר מכן, הערך הנוכחי (Model = Explorer) ייכנס לאזור הסינון, והסיכום ייבנה על ידי משרדים:

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

יתרון 8: המרת פונקציות Pivot לקובייה

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

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

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

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