טבלת ציר באקסל (Pivot Table): איך יוצרים, צעד אחר צעד

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

נוסחאותאיפוס

F2fx

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Sales	% of total
2	North	Apple	120		North	455	49%
3	South	Pear	85		South	305	33%
4	North	Pear	240		East	170	18%
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

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

UNIQUE מציגה כל אזור פעם אחת ו-SUMIF מסכמת אותו: North 455, South 305 ו-East 170, שהם 49%, 33% ו-18% מתוך 930 בסך הכל. שנו את C3 ל-185 והסכום של South וכל שלושת האחוזים מתעדכנים מיד. טבלת ציר הייתה מציגה את אותם מספרים, אבל רק אחרי שמרעננים אותה.

איך יוצרים טבלת ציר

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

1. לחצו על תא כלשהו בנתונים.
2. עברו ל-הוספה > PivotTable (בחלק מהגרסאות הוספה > PivotTable > מטבלה/טווח).
3. אקסל ממלא את הטווח. בחרו גליון עבודה חדש ולחצו על אישור.
4. טבלת ציר ריקה מופיעה עם החלונית שדות PivotTable בצד, שמציגה את כותרות העמודות שלכם.
5. גררו את Region לתיבה שורות ואת Sales לתיבה ערכים. טבלת הציר מציגה כל אזור פעם אחת עם Sum of Sales לידו, ושורת Grand Total.
6. כדי לשנות את מה שמוצג, גררו שדות בין התיבות או אל מחוץ לחלונית.

אם אתם לא בטוחים מאיפה להתחיל, הוספה > טבלאות PivotTable מומלצות מציג כמה פריסות מוכנות לנתונים שלכם. ב-Mac התפריט זהה: הוספה > PivotTable.

שורות, עמודות, ערכים ומסננים

בחלונית שדות PivotTable יש ארבע תיבות, וכל טבלת ציר היא בחירה של איזו עמודה הולכת לאיזו תיבה:

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

כש-Region בשורות, Product בעמודות ו-Sales בערכים, טבלת הציר של הנתונים שלמעלה נראית כך:

Sum of Sales   Column Labels
Row Labels     Apple   Pear   Grand Total
East              60    110           170
North            215    240           455
South            150    155           305
Grand Total      425    505           930

גרסת הנוסחאות של הפריסה הזו מציגה את האזורים לאורך עם UNIQUE, את המוצרים לרוחב עם TRANSPOSE(UNIQUE()), ומחשבת כל תא ברשת עם SUMIFS אחת שמקבלת את שתי הרשימות כקריטריונים שלה:

נוסחאותאיפוס

F2fx

=SUMIFS(C2:C9,A2:A9,E2:E4,B2:B9,F1:G1)

	A	B	C	D	E	F	G
1	Region	Product	Sales			Apple	Pear
2	North	Apple	120		North	215	240
3	South	Pear	85		South	150	155
4	North	Pear	240		East	60	110
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

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

E2 שופך את North, South ו-East למטה, F1 שופך את Apple ו-Pear לרוחב, וה-SUMIFS ב-F2 ממלאת את הרשת של 3 על 2 שביניהם: סכום אחד לכל זוג של אזור ומוצר. שנו את B5 מ-Apple ל-Pear ושני התאים של East משתנים. הסדר כאן הוא הסדר שבו הערכים מופיעים לראשונה; טבלת ציר ממיינת את התוויות שלה לפי הא"ב.

ספירה, ממוצע או אחוז במקום סכום

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

נוסחאותאיפוס

E2fx

=UNIQUE(A2:A9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Orders	Average
2	North	Apple	120		North	3	151.7
3	South	Pear	85		South	3	101.7
4	North	Pear	240		East	2	85.0
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

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

ל-North יש 3 הזמנות עם ממוצע 151.7, ל-South 3 עם ממוצע 101.7, ול-East 2 עם ממוצע 85.0. עמודת האחוז מהסכום הכולל נמצאת בגיליון הראשון בעמוד הזה.

סינון הסיכום לפי מוצר אחד

התיבה מסננים שמה רשימה נפתחת מעל טבלת הציר. גרסת הנוסחה היא תא עם רשימה נפתחת ו-SUMIFS, שמוסיפה עוד תנאי ל-SUMIF. בחרו מוצר ב-F1:

נוסחאותאיפוס

F1fx

	A	B	C	D	E	F
1	Region	Product	Sales		Product:	Apple▾ApplePear
2	North	Apple	120
3	South	Pear	85		Region	Sales
4	North	Pear	240		North	215
5	East	Apple	60		South	150
6	South	Apple	150		East	60
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

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

כש-Apple נבחר, North מציג 215, South 150 ו-East 60. בחרו Pear והם משתנים ל-240, 155 ו-110. ראו SUMIFS בשביל עוד תנאים ואת העמוד על רשימה נפתחת בשביל איך מוסיפים את הרשימה באקסל.

רענון טבלת ציר

טבלת ציר שומרת עותק של נתוני המקור (מטמון ה-Pivot) ולא מחושבת מחדש כשתא במקור משתנה. אחרי עריכת הנתונים:

• לחצו לחיצה ימנית במקום כלשהו בטבלת הציר ובחרו רענן, או הקישו Alt+F5 ב-Windows.
• נתונים > רענן הכל (Ctrl+Alt+F5) מרענן כל טבלת ציר בחוברת העבודה.
• כדי לרענן בכל פעם שהקובץ נפתח, לחצו לחיצה ימנית על טבלת הציר, בחרו אפשרויות PivotTable, ובכרטיסיה נתונים סמנו רענן נתונים בעת פתיחת הקובץ.

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

סיכום של כל האזורים בנוסחה אחת

נוסחאותאיפוס

F2fx

	A	B	C	D	E	F
1	Region	Product	Sales		Region	Sales
2	North	Apple	120		North
3	South	Pear	85		South
4	North	Pear	240		East
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

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

תורכם: ב-F2, סכמו בנוסחה אחת את המכירות של כל אזור שמופיע ב-E2:E4.

בדיקההצגת התשובה

התשובה שופכת 455, 305 ו-170. כשנותנים ל-SUMIF את כל הרשימה E2:E4 כקריטריונים, היא מחזירה סכום אחד לכל אזור, ולכן אין מה למלא למטה. ב-Excel 2019 ובגרסאות ישנות יותר אין UNIQUE וגם אין שפיכה: הקלידו את האזורים ב-E2:E4 ומלאו את =SUMIF($A$2:$A$9,E2,$C$2:$C$9) למטה. בלי סימני ה-$ הטווחים זזים למטה עם כל שורה והסכומים יוצאים שגויים.

GROUPBY ו-PIVOTBY: טבלת ציר בנוסחה אחת

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

=GROUPBY(A2:A9,C2:C9,SUM)

East      170
North     455
South     305
Total     930

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM)

          Apple   Pear   Total
East         60    110     170
North       215    240     455
South       150    155     305
Total       425    505     930

GROUPBY מקבלת את שדה השורות, את הערכים ואת הפונקציה (SUM, COUNTA, AVERAGE, MAX, PERCENTOF). PIVOTBY מוסיפה ביניהם שדה עמודות. שתיהן ממיינות את התוויות ומוסיפות שורות סכום, כמו טבלת ציר.

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

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