מעבר לפיבוט: יצירת דוחות מעוצבים באמצעות פונקציות CUBE באקסל – חלק 1

אם אתם עובדים עם Power Pivot או עם מודל הנתונים (Data Model) של אקסל,
אתם בוודאי רגילים לצרוך את הנתונים שלכם באמצעות פיבוטים (טבלאות ציר/ Pivot Tables).
זהו הכלי המרכזי והנוח ביותר לניתוח נתונים, אך הוא מגיע עם מגבלה בולטת: התלות במבנה הטבלאי.
הפיבוט הוא אובייקט קשיח. אי אפשר להזיז בו תא בודד, קשה לשלב בתוכו שורות רווח או טקסט חופשי,
והוא נוטה לשנות את גודלה ולזוז בגיליון בכל פעם שמרעננים נתונים.
כאן בדיוק נכנסות לתמונה פונקציות ה-CUBE באקסל.
הפונקציות האלו מאפשרות לנו לדבר ישירות עם מודל הנתונים שמאחורי הקלעים, ולשלוף נתונים היישר לתוך תאים בודדים בגיליון,
ללא תלות בטבלת ציר כלשהי, כדי ליצור KPI או טבלאות במבנה קשיח (למשל – דוח רווח והפסד).

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

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

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

הבעיה: המגבלות של טבלאות ציר

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

נתוני המקור לדוגמה

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

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

שלב ראשון: יצירת טבלת ציר מבוססת Data Model

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

1. עמדו בתא כלשהו בתוך טבלת הנתונים
2. בחרו ב-הוספה > PivotTable
3. הקפידו לסמן את התיבה הוסף נתונים אלה לנתונים ולמודל
   
   שימו לב – אם לא תסמנו את התיבה, כפתור ההמרה לנוסחאות לא יהיה פעיל. פונקציות CUBE חייבות מודל נתונים כדי לעבוד.
4. כעת גררו את השדה 'מחלקה' לשורות, ואת השדה 'משכורת' לערכים.
   תתקבל טבלת ציר סטנדרטית שמציגה את סכום המשכורות בכל מחלקה:
   

שלב שני: המרת הטבלה לנוסחאות

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

1. לחצו על תא כלשהו בתוך טבלת הציר שיצרתם.
2. עברו לכרטיסייה ניתוח PivotTable
3. בקבוצת 'חישובים' לחצו על כלי OLAP > המר לנוסחאות
   
4. תתקבל הטבלה הבאה:
   
   שמכילה את כל המידע, אך ללא העיצוב של הפיבוט.

אם תעמדו על הכותרת 'סכום של משכורות' תראו נוסחה מסוג CUBEMEMBER
ואם תעמדו על תא של מחלקה מסוימת, תראו נוסחה מסוג CUBEVALUE

מה בעצם קרה כאן?

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

1. CUBEMEMBER בתאי הכותרת (למשל בתא שבו כתוב 'חריטה', 'חשבות' או 'סכום של משכורות').
   התפקיד של הפונקציה הוא להגדיר לאקסל מהו הפריט הספציפי שאנו מחפשים.
2. CUBEVALUE בתאים המחושבים (לדוגמה סכום המשכורת פר מחלקה).
   הן מבצעות את החישוב בפועל ושולפות את הסכום מהמודל, בהתבסס על הכותרות שהוגדרו.
   כלומר, ה-CUBEMEMBER היא הקטגוריה ('מטה האגף'), וה CUBEVALUE הוא הערך המספרי (113,083) שמשויך לקטגוריה

היתרונות בעבודה עם פונקציות CUBE באקסל

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

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

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

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

שאלות ותשובות בנושא פונקציות CUBE באקסל

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

שאלה:
למי מיועדת העבודה עם פונקציות CUBE ולמי היא אינה מתאימה?
תשובה:
השימוש בפונקציות אלו אינו מיועד למשתמשים מתחילים, אלא לאנליסטים, מנהלי כספים ומיישמי BI,
שיודעים לכתוב פונקציות DAX, אשר מתמודדים עם כמויות נתונים גדולות (Big Data) המעמיסות על הגיליון,
או לכאלו הנדרשים לייצר דוחות מורכבים וייצוגיים שלא ניתן להפיק באמצעים סטנדרטיים.

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