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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

לאחר מכן אנו יוצרים מדד נוסף עם הנוסחה הבאה:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

% מסך המכירות:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())

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

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

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

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

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

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

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

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

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