כיצד להוסיף ולהשתמש בשדה מחושב של טבלת צירים של Excel

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

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

הורד את מערך הנתונים והמשך.

מהו שדה מחושב של טבלת צירים?

נתחיל בדוגמה בסיסית של טבלת צירים.

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

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

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

ישנן מספר דרכים לעשות זאת:

1. חזור למערך הנתונים המקורי והוסף את נקודת הנתונים החדשה הזו. כך שתוכל להכניס עמודה חדשה לנתוני המקור ולחשב את שולי הרווח בה. לאחר שתעשה זאת, עליך לעדכן את נתוני המקור של טבלת הצירים כדי לקבל את העמודה החדשה הזו כחלק ממנה.
   • למרות ששיטה זו היא אפשרות, יהיה עליך לחזור באופן ידני למערך הנתונים ולבצע את החישובים. לדוגמה, ייתכן שיהיה עליך להוסיף עמודה נוספת לחישוב המכירה הממוצעת ליחידה (מכירות/כמות). שוב יהיה עליך להוסיף עמודה זו לנתוני המקור שלך ולאחר מכן לעדכן את טבלת הציר.
   • שיטה זו גם מעלה את טבלת הצירים שלך כשאתה מוסיף לה נתונים חדשים.
2. הוסף חישובים מחוץ לטבלת הציר. זו יכולה להיות אופציה אם מבנה טבלת הצירים שלך לא ישתנה. אך אם תשנה את טבלת ה- Pivot, ייתכן שהחישוב לא יתעדכן בהתאם ויכול לתת לך את התוצאות או השגיאות הלא נכונות. כפי שמוצג להלן, חישבתי את שולי הרווח כאשר היו קמעונאים בשורה. אבל כששיניתי את זה מלקוחות לאזורים, הנוסחה נתנה שגיאה.
3. שימוש בשדה מחושב בטבלת ציר. זה הדרך היעילה ביותר להשתמש בנתוני Pivot Table הקיימים ולחשב את המדד הרצוי. שקול את השדה המחושב כעמודה וירטואלית שהוספת באמצעות העמודות הקיימות מתוך טבלת הצירים. יש הרבה יתרונות בשימוש בשדה מחושב טבלת צירים (כפי שנראה בעוד דקה):
   • זה לא דורש ממך לטפל בנוסחאות או לעדכן נתוני מקור.
   • הוא ניתן להרחבה מכיוון שהוא יתייחס אוטומטית לכל הנתונים החדשים שתוכל להוסיף לטבלת הצירים שלך. לאחר הוספת שדה חישוב, תוכל להשתמש בו כמו כל שדה אחר בטבלת הצירים שלך.
   • קל לעדכן ולנהל. לדוגמה, אם המדדים משתנים או שאתה צריך לשנות את החישוב, תוכל לעשות זאת בקלות מתוך טבלת הצירים עצמה.

הוספת שדה מחושב לטבלת הצירים

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

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

להלן השלבים להוספת שדה מחושב של טבלת צירים:

• בחר תא כלשהו בטבלת הציר.
• עבור אל כלי טבלאות ציר -> ניתוח -> חישובים -> שדות, פריטים וערכות.
• מהתפריט הנפתח, בחר שדה מחושב.
• בתיבת הדו -שיח הוסף קובץ מחושב:
  • תן לו שם על ידי הזנתו בשדה שם.
  • בשדה נוסחה, צור את הנוסחה הרצויה עבור השדה המחושב. שים לב שאתה יכול לבחור מבין שמות השדות המפורטים מתחתיו. במקרה זה, הנוסחה היא '= רווח/ מכירות'. תוכל להזין ידנית את שמות השדות או ללחוץ פעמיים על שם השדה המופיע בתיבה שדות.
• לחץ על הוסף וסגור את תיבת הדו -שיח.

ברגע שתוסיף את השדה המחושב, הוא יופיע כאחד השדות ברשימה שדות PivotTable.

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

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

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

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

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

• אינך יכול להשתמש בהפניות או בטווחי שם בעת יצירת שדה מחושב טבלת צירים. זה יפסול הרבה נוסחאות כגון VLOOKUP, INDEX, OFFSET וכן הלאה. עם זאת, אתה יכול להשתמש בנוסחאות שיכולות לפעול ללא הפניות (כגון SUM, IF, COUNT, וכן הלאה …).
• אתה יכול להשתמש בקבוע בנוסחה. לדוגמה, אם אתה רוצה לדעת את המכירות החזויות שבהן התחזית שלהן תגדל ב -10%, תוכל להשתמש בנוסחה = מכירות*1.1 (כאשר 1.1 הוא קבוע).
• סדר העדיפויות מתקיים בנוסחה שעושה את השדה המחושב. כשיטה מומלצת, השתמש בסוגריים כדי לוודא שאינך צריך לזכור את סדר העדיפויות.

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

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

עבור ערך צפוי, עליך להשתמש בעליית מכירות של 5% לקמעונאים גדולים (מכירות מעל 3 מיליון) ועלייה של 10% במכירות של קמעונאים קטנים ובינוניים (מכירות מתחת ל -3 מיליון).

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

הנה איך לעשות זאת:

• בחר תא כלשהו בטבלת הציר.
• עבור אל כלי טבלאות ציר -> ניתוח -> חישובים -> שדות, פריטים וערכות.
• מהתפריט הנפתח, בחר שדה מחושב.
• בתיבת הדו -שיח הוסף קובץ מחושב:
  • תן לו שם על ידי הזנתו בשדה שם.
  • בשדה הנוסחה, השתמש בנוסחה הבאה: = IF (אזור = "דרום", מכירות *1.05, מכירות *1.1)
• לחץ על הוסף וסגור את תיבת הדו -שיח.

זה מוסיף עמודה חדשה לטבלת הציר עם ערך תחזית המכירות.

לחץ כאן להורדת מערך הנתונים.

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

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

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

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

שים לב כי סכומי הביניים והסיכומים הגדולים אינם נכונים.

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

אז עבור דרום סה"כ, בעוד שהערך אמור להיות 22,824,000, הדרום טוטאל מדווח על כך שגוי כ -22,287,000. זה קורה כשהוא משתמש בנוסחה 21,225,800*1.05 כדי לקבל את הערך.

למרבה הצער, אין דרך לתקן זאת.

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

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

כיצד לשנות או למחוק שדה מחושב של טבלת צירים?

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

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

כיצד ניתן לקבל רשימה של כל נוסחאות השדה המחושבות?

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

Excel מאפשר לך ליצור במהירות רשימה של כל הנוסחאות המשמשות ביצירת שדות מחושבים.

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

• בחר תא כלשהו בטבלת הציר.
• עבור אל כלי טבלאות ציר -> ניתוח -> שדות, פריטים וערכות -> נוסחאות רשימה.

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

זה יכול להיות כלי שימושי באמת אם אתה צריך לשלוח את העבודה שלך ללקוח או לשתף אותה עם הצוות שלך.

ייתכן שתמצא גם את הדרכות טבלת הציר הבאות שימושיות:

• הכנת נתוני מקור לטבלת צירים.
• שימוש בפרוסות בטבלת ציר Excel: מדריך למתחילים.
• כיצד לקבץ תאריכים בטבלאות ציר ב- Excel.
• כיצד לקבץ מספרים בטבלת צירים ב- Excel.
• כיצד לסנן נתונים בטבלת צירים ב- Excel.
• כיצד להחליף תאים ריקים באפס בטבלאות ציר של Excel.
• כיצד ליישם עיצוב מותנה בטבלת צירים ב- Excel.
• מטמון ציר ב- Excel - מה זה וכיצד ניתן להשתמש בו בצורה הטובה ביותר?