פונקציית CUBEVALUE – להפוך כותרות למספרים
תקציר המאמר:המאמר מסביר כיצד להשתמש בפונקציית CUBEVALUE באקסל כדי לשלוף נתונים מספריים ממודל הנתונים על בסיס חיתוך בין מדדים לקטגוריות.
השימוש בפונקציה, בשילוב עם CUBEMEMBER, מאפשר בניית דוחות ולוחות מחוונים גמישים ומעוצבים ללא תלות במבנה הקשיח של טבלאות ציר מסורתיות. |
במאמר הקודם למדנו כיצד להשתמש בפונקציית CUBEMEMBER.
ראינו שהפונקציה הזו משמשת כמעין מתרגם: היא לוקחת טקסט פשוט (כמו "שיווק") וממירה אותו לאובייקט שמודל הנתונים יודע לזהות.
אבל הזיהוי הוא רק הצעד הראשון. בשלב השני, אנחנו רוצים לראות תוצאות, מספרים, ונתונים כספיים.
כאן בדיוק נכנסת לתמונה הפונקציה השנייה בצמד: CUBEVALUE.
אם CUBEMEMBER יצרה את הזיהוי, CUBEVALUE היא הפועלת שמבצעת את העבודה ומושכת את המספרים בפועל.
איך הפונקציה עובדת? עקרון החיתוך
לפני שצוללים לנוסחה, חשוב להבין את ההיגיון.
פונקציית CUBEVALUE עובדת בשיטת החיתוך (Intersection):
אנחנו שולחים לה רשימה של אובייקטים (Members), והיא מחזירה את המספר שנמצא בחיתוך של כולם.
כדי ללמוד את הפונקציה, נשתמש בטבלת הנתונים הבאה:
התחביר (Syntax)
מבנה הפונקציה פשוט למדי, והוא מורכב מחיבור למודל ואחריו רשימה של איברים לחיתוך:
=CUBEVALUE(Connection, Member_Expression1, [Member_Expression2], …)
עריכת החישובים
אנחנו רוצים לשלוף את סכום המשכורות עבור המחלקות השונות.
לפני שנוכל לערוך חישובים אנחנו צריכים:
- להוסיף את הטבלה ל Data Model
- ליצור מדד (Measure) של סכום המשכורות
- להכין את הפילטרים הרצויים באמצעות הפונקציה CUBEMEMBER, עליה למדנו במאמר הזה
שלב 1: החיבור ל- Data Model (Power Pivot)
נעמוד בתוך טבלת הנתונים, נעבור ללשונית Power Pivot ונלחץ על הוסף למודל נתונים.
שלב 2: בניית המדד
מכיוון שאנחנו רוצים לסכום את המשכורות, ניצור את המדד הבא:
TotalSalary:=sum([משכורת])
שלב 3: הגדרת האיברים לחיתוך (MEMBERS)
כדי שפונקציית CUBEVALUE תחזיר תוצאה, עלינו לספק לה את ה"קואורדינטות" של החיתוך.
לצורך כך, אנחנו זקוקים לשני סוגי איברים:
- איבר המדידה (MEASURE): זהו המדד שיוצר את החישוב המספרי (במקרה שלנו – סכום המשכורות).
עבור המדד הזה יצרתי מתרגם באמצעות הפונקציה CUBEMEMBER, בתא G1, שמפנה למדד TotalSalary שכתבתי ב Power Pivot באמצעות DAX
- איברי הסינון (Dimension Members): אלו הקטגוריות לפיהן נחתוך את הנתונים (במקרה שלנו – שמות המחלקות).
בתאים C2:C8 יצרתי את הפונקציות המתרגמות את שמות המחלקות, מהטקסט הגולמי שנמצא בטווח B2:B8, לאובייקטים שהמודל מבין.
שלב 4: שליפת הערכים
מכיוון שכבר הוספתי לגיליון עמודת עזר עם פונקציות תרגום (CUBEMEMBER),
כל מה שאני צריכה לעשות עכשיו זה להפנות אל התאים האלה, כדי לקבל את החישוב הנכון:
שימו לב שקיבעתי את תא G1 שמפנה למדד, כך שאוכל לגרור את הנוסחה.
חוקים חשובים שכדאי לזכור
- אין טקסט חופשי: אי אפשר לכתוב את המילה "שיווק" סתם כך בתוך הנוסחה (למשל "שיווק"). הפונקציה לא תדע לקרוא את זה.
חובה להשתמש בנתיב המלא של האובייקט (כפי שלמדנו ב-CUBEMEMBER) או להפנות לתא שכבר מכיל אובייקט תקין. - ריבוי מסננים: מכיוון שהפונקציה עובדת על חיתוכים, אפשר להוסיף עשרות חיתוכים שונים, מופרדים בפסיקים. הפונקציה תחזיר את החיתוך של כל האובייקטים יחד.
לסיכום,
השילוב בין CUBEMEMBER (המזהה) ל-CUBEVALUE (השולפת) מאפשר לנתק את הנתונים מכבלי הפיבוט.
כעת ניתן למקם כל נתון בכל תא בגיליון, ליצור דוחות בתבנית אישית (כגון דוחות כספיים – P&L) ולבנות לוחות מחוונים (Dashboards) ומערכות תומכות החלטה,
ללא התלות במבנה הנוקשה של טבלאות ציר רגילות.
שאלות ותשובות בנושא פונקציית CUBEVALUE
שאלה:
מהו העיקרון המנחה את פעולתה של פונקציית CUBEVALUE בשליפת נתונים?
תשובה:
הפונקציה פועלת בשיטת החיתוך (Intersection). היא מקבלת רשימה של אובייקטים או איברים (Members) ומחזירה את הערך המספרי שנמצא בנקודת החיתוך של כולם בתוך מודל הנתונים.
שאלה:
אילו סוגי איברים נדרשים כדי לקבל תוצאה תקינה מהפונקציה?
תשובה:
כדי לקבל תוצאה, יש לספק לפונקציה שני סוגי "קואורדינטות": איבר מדידה (Measure), שהוא המדד המבצע את החישוב המספרי כמו סכום משכורות,
ואיברי סינון (Dimension Members), שהם הקטגוריות לפיהן נחתך המידע, כגון שמות מחלקות או שנים.
שאלה:
מהם היתרונות המרכזיים בשימוש בשילוב הפונקציות CUBEMEMBER ו-CUBEVALUE לעומת פיבוטים רגילים?
תשובה:
השילוב בין הפונקציות מאפשר לנתק את הנתונים מהמבנה הקשיח של טבלת הציר.
הדבר מאפשר למקם כל נתון בכל תא בגיליון האקסל, ובכך לבנות דוחות כספיים בתבנית אישית ולוחות מחוונים (Dashboards) גמישים ומעוצבים באופן חופשי