מתי להשתמש בעמודות מחושבות ובשדות מחושבים

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

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

הנוסחה החדשה שלנו קטגוריית מוצר משתמשת בפונקציה DAX הקשורה כדי לקבל ערכים מהעמודה ProductCategoryName בטבלה הקשורה Product Category ולאחר מכן מזין ערכים אלה עבור כל מוצר (כל שורה) בטבלה Product.

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

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

ניתן ליצור עמודה מחושבת המחשבת סכום רווח עבור כל שורה על-ידי חיסור ערכים בעמודה COGS מערכים בעמודה SalesAmount, באופן הבא:

כעת, ניתן ליצור PivotTable ולגרור את השדה 'קטגוריית מוצר' לעמודות, ואת השדה 'רווח' החדש שלנו לאזור 'ערכים' (עמודה בטבלה ב- PowerPivot היא שדה ברשימת השדות של PivotTable). התוצאה היא מידה משתמעת בשם Sum of Profit. זהו כמות צבורה של ערכים מעמודת הרווח עבור כל אחת מקטגוריות המוצר השונות. התוצאה שלנו נראית כך:

במקרה זה, הרווח הגיוני רק כשדה ב- VALUES. אם היינו מקם את 'רווח' באזור 'עמודות', ה- PivotTable שלנו היה נראה כך:

השדה רווח אינו מספק מידע שימושי כאשר הוא ממוקם באזורים COLUMNS, ROWS או FILTERS. הגיוני רק כערך צבור באזור VALUES.

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

רווח, יחושב טוב יותר כמדד מפורש.

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

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

תחילה, בטבלה Sales, אנו בוחרים את העמודה SalesAmount ולאחר מכן לוחץ על סכום אוטומטי כדי ליצור סכום מפורש של SalesAmountmeasure. זכור, מידה מפורשת היא אמצעי שאנו יוצרים באזור החישוב של טבלה ב- Power Pivot. אנו עושים את אותו הדבר עבור עמודת COGS. אנו נשנה את השם של Total SalesAmount ו- Total COGS כדי להקל על הזיהוי שלהם.

לאחר מכן ניצור מידה נוספת באמצעות נוסחה זו:

Total Profit:=[ Total SalesAmount] - [Total COGS]

לאחר שינוי התבנית החדשה של Total Profit measure למטבע, נוכל להוסיף אותה ל- PivotTable שלנו.

באפשרותך לראות את המידה החדשה של Total Profit מחזירה את אותן תוצאות כמו יצירת עמודה מחושבת של רווח ולאחר מכן הצבתה ב- VALUES. ההבדל הוא שמדד Total Profit שלנו יעיל הרבה יותר והופכת את מודל הנתונים שלנו לנעיל ורז יותר מכיוון שאנו מחושבים באותו הזמן ורק עבור השדות שאנו בוחרים עבור ה- PivotTable שלנו. אחרי הכל, אנחנו לא זקוקים לעמודה המחושבת 'רווח'.

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

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

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

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

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

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

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

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

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

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

אנו יוצרים מידה חדשה עם הנוסחה הבאה:

% of Total Sales:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())

נוסחה זו מציינת: חלק את התוצאה מ- Total SalesAmount סכום של SalesAmount ללא מסנני עמודה או שורה שאינם המסננים המוגדרים ב- PivotTable.

כעת, אם נוסיף את % מסכום המכירות החדש ל - PivotTable, אנחנו יקבלו:

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

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

שימוש בעמודות מחושבות

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

• אם ברצונך שהנתונים החדשים שלך יהיו ערך קבוע עבור השורה. לדוגמה, יש לך טבלת תאריכים עם עמודה של תאריכים, ואתה מעוניין בעמודה אחרת המכילה רק את מספר החודש. באפשרותך ליצור עמודה מחושבת המחשבת רק את מספר החודש מהתאריכים בעמודה תאריך. לדוגמה, =MONTH('Date'[Date]).

• אם ברצונך להוסיף ערך טקסט עבור כל שורה לטבלה, השתמש בעמודה מחושבת. לא ניתן לצבור שדות עם ערכי טקסט ב- VALUES. לדוגמה, הנוסחה =FORMAT('Date'[Date],"mmmm") מספקת לנו את שם החודש עבור כל תאריך בעמודה 'תאריך' בטבלה 'תאריך'.

השתמש באמצעי מדידים

• אם תוצאת החישוב תהיה תלויה תמיד בשדות האחרים שתבחר ב- PivotTable.

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

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

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