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

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

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

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

העמודה 'קטגוריית מוצר מחושבת'

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

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

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

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

העמודה 'רווח' בטבלה Power Pivot

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

PivotTable פשוט

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

PivotTable ללא ערכים שימושיים

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

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

רווח, באמת טוב יותר תחושב כאמצעי מפורשות.

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

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

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

לחצן 'סכום אוטומטי' ב- Power Pivot

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

כולל Profit: = [ כולל SalesAmount]-[Total COGS]

הערה: אנו גם לכתוב נוסחה שלנו בתור Profit:=SUM([SalesAmount]) הכולל - SUM([COGS]), אך על-ידי יצירת מידות SalesAmount הכולל ו- COGS הכולל נפרד, נוכל להשתמש בהם ב- PivotTable שלנו מדי ולאחר נוכל להשתמש בהם כארגומנטים במסוגים אחרים נוסחאות מידה.

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

טבלאות PivotTable

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

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

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

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

העמודה 'אחוז מכירות מחושבות'

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

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

טבלת PivotTable המציגה סכום של אחוז מכירות עבור קטגוריות מוצרים

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

תוצאה שגויה של סכום אחוזי מכירות ב- PivotTable

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

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

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

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

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

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

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

% של סך כל המכירות: =([Total SalesAmount]) / לחשב ([סיכום SalesAmount], ALLSELECTED())

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

עצה: הקפד קרא אודות הפונקציות CALCULATE ו- ALLSELECTED בהפניה DAX.

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

תוצאה נכונה של סכום אחוזי מכירות ב- PivotTable

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

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

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

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

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

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

שימוש במידות

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

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

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

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

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

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×