חישוב ערכים ב- PivotTable

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

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

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

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

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

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

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

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

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

  פונקציה	תוצאה
  ללא חישוב	הצגת הערך שהוזן בשדה.
  % מסכום כולל	הצגת ערכים כאחוז מתוך הסכום הכולל של כל הערכים או של כל נקודות הנתונים בדוח.
  % מסכום עמודה	הצגת כל הערכים בכל עמודה או בסידרה כאחוז מתוך הסכום של העמודה או של הסידרה.
  % מסכום שורה	הצגת הערך בכל שורה או קטגוריה כאחוז מתוך הסכום של השורה או של הקטגוריה.
  % מ-	הצגת ערכים כאחוז מתוך הערך של פריט הבסיס בשדה הבסיס.
  % מסכום שורת אב	חישוב ערכים באופן הבא: (ערך פריט האב בשורות) / (ערך הפריט)
  % מסכום עמודת אב	חישוב ערכים באופן הבא: (ערך פריט האב בעמודות) / (ערך הפריט)
  % מסכום אב	חישוב ערכים באופן הבא: (ערך פריט האב של שדה הבסיס הנבחר) / (ערך הפריט)
  הפרש מ-	הצגת ערכים כהפרש מתוך הערך של פריט הבסיס בשדה הבסיס.
  % הפרש מ-	הצגת ערכים כהפרש באחוזים מתוך הערך של פריט הבסיס בשדה הבסיס.
  סכום מצטבר ב-	הצגת הערך עבור פריטים עוקבים הנמצאים בשדה הבסיס כסכום מצטבר.
  % סכום מצטבר ב-	חישוב הערך עבור פריטים עוקבים בשדה הבסיס המוצגים כאחוז סכום מצטבר.
  דירוג מהקטן ביותר לגדול ביותר	הצגת הדירוג של הערכים הנבחרים בשדה ספציפי, כאשר הפריט הקטן ביותר מופיע בשדה כ- 1, ולכל ערך גדול יותר יהיה ערך דירוג גבוה יותר.
  דירוג מהגדול ביותר לקטן ביותר	הצגת הדירוג של הערכים הנבחרים בשדה ספציפי, כאשר הפריט הגדול ביותר מופיע בשדה כ- 1, ולכל ערך קטן יותר יהיה ערך דירוג גבוה יותר.
  אינדקס	חישוב ערכים באופן הבא: ((סכום כולל של עמודה) X (סכום כולל של שורה))‎ / ((סכום כולל מקיף) X (ערך בתא))‎

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

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

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

  כמו כן, אם שרת OLAP מספק שדות מחושבים, שנקראים חברים מחושבים, תראה שדות אלה ברשימת השדות של PivotTable. תראה גם שדות מחושבים ופריטים מחושבים שנוצרו על-ידי פקודות מאקרו שנכתבו ב- Visual Basic for Applications ‏(VBA) ומאוחסנות בחוברת העבודה, אך לא תוכל לשנות את השדות או הפריטים הללו. אם דרושים לך סוגים נוספים של חישובים, צור קשר עם מנהל מסד הנתונים של OLAP.

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

• חישובים המבוססים על נתוני מקור שאינם OLAP    בטבלאות PivotTable המבוססים על סוגים אחרים של נתונים חיצוניים או על נתוני גליון עבודה, Excel משתמש בפונקציית הסיכום Sum כדי לחשב שדות ערכים שמכילים נתונים מספריים, ובפונקציית הסיכום Count כדי לחשב שדות נתונים שמכילים טקסט. באפשרותך לבחור פונקציית סיכום אחרת, כגון Average‏, Max או Min, כדי להמשיך בניתוח ולהתאים אישית את הנתונים שלך. באפשרותך גם ליצור נוסחאות משלך שמשתמשות ברכיבים של הדוח או בנתוני גליון עבודה אחרים על-ידי יצירת שדה מחושב או פריט מחושב בתוך שדה.

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

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

• שמות השדות והפריטים    Excel משתמש בשמות השדות והפריטים כדי לזהות את רכיבי הדוח הללו בנוסחאות שלך. בדוגמה הבאה, הנתונים בטווח C3:C9 משתמשים בשם השדה חלב. פריט מחושב בשדה סוג שמעריך את המכירות עבור מוצר חדש בהתבסס על מכירות החלב עשוי להשתמש בנוסחה כגון ‎=Dairy * 115%‎.

  

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

• נוסחאות פועלות על סכומים כוללים, ולא על רשומות בודדות    הנוסחאות עבור שדות מחושבים פועלות על סכום הנתונים המשמשים כבסיס עבור כל השדות בנוסחה. לדוגמה, הנוסחה בשדה המחושב ‎= Sales * 1.2‎ מכפילה את סכום המכירות עבור כל סוג ואזור פי 1.2; היא לא מכפילה כל מכירה בודדת פי 1.2 ולאחר מכן מסכמת את סכומי המכפלות.

  נוסחאות עבור פריטים מחושבים פועלות על רשומות בודדות. לדוגמה, נוסחת הפריט המחושב ‎=Dairy *115%‎ מכפילה כל מכירה בודדת של Dairy ב- 115%, ולאחר מכן סכומי המכפלות מסוכמים באזור הערכים.

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

• סכומים    נוסחאות לא יכולות להפנות לסכומים (כגון סה"כ מרץ, סה"כ אפריל וסכום כולל בדוגמה).

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

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

  באפשרותך להשתמש במיקומים יחסיים כדי להפנות לפריטים. המיקומים נקבעים יחסית לפריט המחושב שמכיל את הנוסחה. אם דרום הוא האזור הנוכחי, Region[-1]‎ הוא צפון; אם צפון הוא האזור הנוכחי, Region[+1]‎ הוא דרום. לדוגמה, פריט מחושב עשוי להשתמש בנוסחה ‎=Region[-1] * 3%‎. אם המיקום שציינת הוא לפני הפריט הראשון או אחרי הפריט האחרון בשדה, הנוסחה תחזיר שגיאת ‎#REF!‎.

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

לדוגמה, ה- PivotChart הבא מציג את המכירות עבור כל איש מכירות בכל אזור:

כדי לראות כיצד ייראו המכירות אם הן יעלו ב- 10 אחוזים, באפשרותך ליצור שדה מחושב ב- PivotTable המשויך, שישתמש בנוסחה כמו ‎‏=Sales * 110%‎.

התוצאה מופיע באופן מיידי ב- PivotChart, כפי שמוצג בתרשים הבא:

כדי לראות סמן נתונים נפרד עבור מכירות באזור צפון ולהפחית ממנו את עלות ההובלה של 8 אחוזים, באפשרותך ליצור פריט מחושב בשדה 'אזור' באמצעות נוסחה כגון ‎=North – (North * 8%)‎.

התרשים שנוצר ייראה כך:

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