חישוב ערכים ב- 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%)‎.

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

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

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

עבור פריטים מחושבים, באפשרותך להזין נוסחאות שונות בכל תא ותא. לדוגמה, אם פריט מחושב בשם OrangeCounty כולל את הנוסחה ‎=Oranges * .25‎ בכל החודשים, תוכל לשנות את הנוסחה ל- ‎=Oranges *.5‎ עבור יוני, יולי ואוגוסט.

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

הוספת שדה מחושב

1. לחץ על ה- PivotTable.

   פעולה זו מציגה את 'כלי PivotTable' ומוסיפה את הכרטיסיות ניתוח ועיצוב.

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

   

3. בתיבה שם, הקלד שם עבור השדה.

4. בתיבה נוסחה, הזן את הנוסחה עבור השדה.

   כדי להשתמש בנתונים משדה אחר בנוסחה, לחץ על השדה בתיבה שדות ולאחר מכן לחץ על הוסף שדה. לדוגמה, כדי לחשב עמלה של 15% לכל ערך בשדה 'מכירות', באפשרותך להזין ‎= Sales * 15%‎.

5. לחץ על הוסף.

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

1. לחץ על ה- PivotTable.

   פעולה זו מציגה את 'כלי PivotTable' ומוסיפה את הכרטיסיות ניתוח ועיצוב.

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

   

3. לחץ על השדה שאליו ברצונך להוסיף את הפריט המחושב.

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

   

5. בתיבה שם, הקלד שם עבור הפריט המחושב.

6. בתיבה נוסחה, הזן את הנוסחה עבור הפריט.

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

7. לחץ על הוסף.

הזנת נוסחאות שונות, תא אחר תא, עבור פריטים מחושבים

1. לחץ על תא שעבורו ברצונך לשנות את הנוסחה.

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

2. ב- שורת הנוסחאות, הקלד את השינויים בנוסחה.

קביעת סדר החישוב עבור מספר רב של פריטים מחושבים או נוסחאות

1. לחץ על ה- PivotTable.

   פעולה זו מציגה את 'כלי PivotTable' ומוסיפה את הכרטיסיות ניתוח ועיצוב.

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

   

3. לחץ על נוסחה ולאחר מכן לחץ על הזז למעלה או הזז למטה.

4. המשך עד שהנוסחאות יוצגו בסדר שבו ברצונך לחשב.

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

1. לחץ על ה- PivotTable.

   פעולה זו מציגה את 'כלי PivotTable' ומוסיפה את הכרטיסיות ניתוח ועיצוב.

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

   

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

לקבלת פריטים מחושבים, באפשרותך לערוך נוסחאות בודדות עבור תאים ספציפיים של פריט מחושב. לדוגמה, אם פריט מחושב בשם OrangeCalc כולל את הנוסחה ‎=Oranges *.25‎ בכל החודשים, באפשרותך לשנות את הנוסחה ל- ‎=Oranges *.5 עבור יוני, יולי ואוגוסט.

קבע אם נוסחה נמצאת בשדה מחושב או בפריט מחושב

1. לחץ על ה- PivotTable.

   פעולה זו מציגה את 'כלי PivotTable' ומוסיפה את הכרטיסיות ניתוח ועיצוב.

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

   

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

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

4. המשך באמצעות אחת מהשיטות הבאות:

עריכת נוסחה של שדה מחושב

1. לחץ על ה- PivotTable.

   פעולה זו מציגה את 'כלי PivotTable' ומוסיפה את הכרטיסיות ניתוח ועיצוב.

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

   

3. בתיבה שם , בחר את השדה המחושב שעבורו ברצונך לשנות את הנוסחה.

4. בתיבה נוסחה, ערוך את הנוסחה.

5. לחץ על שינוי.

עריכת נוסחה בודדת עבור פריט מחושב

1. לחץ על השדה שמכיל את הפריט המחושב.

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

   

3. בתיבה שם, בחר את הפריט המחושב.

4. בתיבה נוסחה, ערוך את הנוסחה.

5. לחץ על שינוי.

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

1. לחץ על תא שעבורו ברצונך לשנות את הנוסחה.

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

2. ב- שורת הנוסחאות, הקלד את השינויים בנוסחה.

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

1. קבע אם הנוסחה נמצאת בשדה מחושב או בפריט מחושב

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

2. בצע אחת מהפעולות הבאות:

   • כדי למחוק שדה מחושב, לחץ במקום כלשהו ב- PivotTable.

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

     פעולה זו מציגה את 'כלי PivotTable' ומוסיפה את הכרטיסיות ניתוח ועיצוב.

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

   

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

5. לחץ על מחק.