בדרך כלל, לאחר שיצרת טבלת ציר, עליך להרחיב את הניתוח שלך ולכלול בה נתונים/חישובים נוספים.
אם אתה צריך נקודת נתונים חדשה שניתן להשיג באמצעות נקודת נתונים קיימת בטבלת הציר, אין צורך לחזור ולהוסיף אותה לנתוני המקור.במקום זאת, אתה יכול להשתמששדות מחושבים של PivotTableכדי לעשות זאת.
תוכן
מהו שדה מחושב PivotTable?
נתחיל עם דוגמה בסיסית של טבלת ציר.
נניח שיש לך מערך נתונים של קמעונאים ואתה יוצר טבלת ציר כמו זה:
טבלת הציר למעלה מסכמת את ערכי המכירות והרווח של הקמעונאי.
עכשיו, מה אם אתה גם רוצה לדעת מה הם שולי הרווח של הקמעונאים האלה (מתח הרווח הוא "רווח" חלקי "מכירות")?
ישנן מספר דרכים לעשות זאת:
- חזור למערך הנתונים המקורי והוסף את נקודת הנתונים החדשה הזו.אז אתה יכול להכניס עמודה חדשה בנתוני המקור ולחשב את שולי הרווח בה.לאחר שהדבר נעשה, עליך לעדכן את נתוני המקור של טבלת הציר כדי לקבל את העמודה החדשה הזו כחלק ממנה.
- בעוד שגישה זו עובדת, עליך לחזור ידנית למערך הנתונים ולבצע את החישובים.לדוגמה, ייתכן שתרצה להוסיף עמודה נוספת כדי לחשב את ממוצע המכירות ליחידה (מכירות/כמות).שוב, עליך להוסיף עמודה זו לנתוני המקור ולאחר מכן לעדכן את טבלת הציר.
- שיטה זו גם מנפחת את טבלת הציר שלך כשאתה מוסיף לה נתונים חדשים.
- הוסף חישובים מחוץ לטבלאות ציר.בחר באפשרות זו אם מבנה טבלת הציר שלך לא צפוי להשתנות.עם זאת, אם תשנה את טבלת הציר, ייתכן שהחישובים לא יתעדכנו בהתאם ועלולים לתת לך תוצאות שגויות או שגיאות.כפי שמוצג להלן, חישבתי את שולי הרווח כאשר יש קמעונאים בשורה.אבל כשאני משנה את זה מלקוח לאזור, הנוסחה משתבשת.
- חשב שדות באמצעות טבלאות ציר.זההשתמש בנתוני PivotTable קיימים וחשב את ההדרך היעילה ביותר.חשוב על שדות מחושבים כעל עמודות וירטואליות שאתה מוסיף באמצעות עמודות קיימות בטבלת הציר שלך.לשימוש בשדות מחושבים בטבלת ציר יש יתרונות רבים (כפי שנראה בהמשך):
- זה לא מחייב אותך לעבוד עם נוסחאות או לעדכן נתוני מקור.
- זה ניתן להרחבה מכיוון שהוא לוקח בחשבון אוטומטית כל מידע חדש שאתה עשוי להוסיף לטבלת הצירים.לאחר הוספת שדה מחושב, ניתן להשתמש בו כמו כל שדה אחר בטבלת ציר.
- קל לעדכן ולנהל.לדוגמה, אם מדד משתנה או שאתה צריך לשנות חישוב, אתה יכול לעשות זאת בקלות מטבלת הצירים עצמה.
הוסף שדות מחושבים לטבלת ציר
בוא נראה כיצד להוסיף שדה מחושב של טבלת ציר לטבלת ציר קיימת.
נניח שיש לך טבלת ציר שנראית כך, ואתה רוצה לחשב את שולי הרווח עבור כל קמעונאי:
להלן השלבים להוספת שדה מחושב PivotTable:
לאחר הוספת השדה המחושב, הוא יופיע כאחד מהשדות ברשימת שדות PivotTable.
כעת אתה יכול להשתמש בשדה מחושב זה כמו כל שדה אחר של טבלת ציר (שים לב שאינך יכול להשתמש בשדה מחושב של טבלת ציר כמסנן דוח או כפורס).
כפי שציינתי קודם, היתרון בשימוש בשדה מחושב של טבלת ציר הוא שניתן לשנות את המבנה של טבלת הציר והיא תתכוונן אוטומטית.
לדוגמה, אם אני גרור ושחרר אזור לתוך אזור השורה, תקבל תוצאות כמו הבאות, שבהן מדווחים ערכי שולי הרווח עבור הקמעונאי והאזור.
בדוגמה למעלה השתמשתי בנוסחה פשוטה (=רווח/מכירות) כדי להכניס את השדה המחושב.עם זאת, אתה יכול גם להשתמש בכמה נוסחאות מתקדמות.
לפני שאראה לך דוגמה לשימוש בנוסחה מתקדמת ליצירת שדה מחושב PivotTable, הנה כמה דברים שאתה חייב לדעת:
- לא ניתן להשתמש בהפניות או בטווחים בעלי שם בעת יצירת שדות מחושבים של PivotTable.זה לא יכלול נוסחאות רבות כגון VLOOKUP, INDEX, OFFSET וכו'.עם זאת, ניתן להשתמש בנוסחאות שעובדות ללא הפניות (למשל SUM, IF, COUNT וכו'...).
- אתה יכול להשתמש בקבועים בנוסחאות.לדוגמה, אם תרצה לדעת את צמיחת המכירות החזויה שצפויה לעלות ב-10%, תשתמש בנוסחה =מכירות*1.1 (כאשר 1.1 הוא קבוע).
- עקוב אחר סדר העדיפויות בנוסחאות שיוצרות שדות מחושבים.כתרגול מומלץ, השתמש בסוגריים כדי להבטיח שלא תצטרך לזכור קדימות.
כעת נסתכל על דוגמה ליצירת שדה מחושב באמצעות נוסחה מתקדמת.
נניח שיש לך את מערך הנתונים המוצג למטה ואתה צריך להציג את המכירות החזויות בטבלת ציר.
עבור ערכי תחזית, תשתמש בעליית מכירות של 300% לקמעונאים גדולים (מעל 5 מיליון מכירות) ועלייה של 300% עבור קמעונאים קטנים ובינוניים (פחות מ-10 מיליון מכירות).
הערה: נתוני המכירות כאן מזויפים ושימשו להמחשת הדוגמאות במדריך זה.
הנה איך לעשות את זה:
פעולה זו תוסיף עמודה חדשה ל- PivotTable עם ערכי תחזית מכירות.
בעיית שדה מחושבת בטבלת ציר
שדות מחושבים הם תכונה מדהימה שבאמת מעלה את הערך של טבלאות ציר עם חישובי שדות, תוך שמירה על הכל ניתן להרחבה ולניהול.
עם זאת, יש בעיה עם שדות מחושבים PivotTable שעליך להבין לפני השימוש בהם.
נניח שיש לי טבלת ציר כמו להלן ואני משתמש בשדה מחושב כדי לקבל תחזית מספרי מכירות.
שימו לב שסיכומי הביניים והסיכומים אינם נכונים.
בעוד שאלו אמורים להוסיף ערכי תחזית מכירות נפרדים לכל קמעונאי, במציאות, הוא עוקב אחר אותה נוסחת שדה מחושבת שיצרנו.
אז עבור South Total, בעוד שהערך צריך להיות 22,824,000, South Total מדווחת אותו באופן שגוי כ-22,287,000.זה קורה מכיוון שהוא משתמש בנוסחה 21,225,800*1.05 כדי לקבל את הערך.
למרבה הצער, אתה לא יכול לתקן בעיה זו.
הדרך הטובה ביותר לטפל בזה היא להסיר את סכומי הביניים והסיכומים הגדולים מטבלת הציר.
אפשר גם לעבור מוגש על ידי דברהכמה דרכים לעקיפת הבעיה כדי להתמודד עם בעיה זו.
כיצד לשנות או למחוק שדה מחושב של טבלת ציר?
לאחר יצירת שדה מחושב PivotTable, תוכל לשנות את הנוסחה או למחוק אותה באמצעות השלבים הבאים:
כיצד לקבל רשימה של כל נוסחאות השדה המחושבות?
אם אתה יוצר הרבה שדות מחושבים PivotTable, אתה לא צריך לדאוג לעקוב אחר הנוסחאות המשמשות בכל שדה.
Excel מאפשר לך ליצור במהירות רשימה של כל הנוסחאות המשמשות ליצירת שדות מחושבים.
להלן השלבים כדי לקבל במהירות רשימה של כל נוסחאות השדה המחושבות:
- בחר תא כלשהו בטבלת הציר.
- עבור אל PivotTable Tools -> ניתוח -> שדות, פריטים וקבוצות -> רשימת נוסחאות.
ברגע שתלחץ על נוסחת הרשימה, Excel יוסיף אוטומטית גליון עבודה חדש עם פרטים של כל השדות/פריטים המחושבים שבהם השתמשת בטבלת הציר.
זה יכול להיות כלי שימושי מאוד אם אתה צריך לשלוח עבודה ללקוח או לשתף אותה עם הצוות שלך.
