טבלת ציר (באנגלית – Pivot Table, או פשוט: פיבוט) היא כלי שמאפשר לסכם ולנתח טבלאות נתונים גדולות בצורה נוחה ויעילה.

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

רוצים לתרגל טבלאות ציר? היכנסו לדף התרגילים שלנו!

איך משתמשים בטבלת ציר?

בואו נתבונן בדוגמא הבאה, על מנת להבין כיצד הכלי עובד:

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

אנחנו רוצים לדעת:

1. מהו הממוצע של כל סטודנטית
2. כמה מקצועות למדה כל סטודנטית
3. מה הממוצע הכללי עבור כל מקצוע?

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

1. סמנו את טווח הנתונים (כולל שורת הכותרת!) אותו תרצו לנתח באמצעות כלי טבלאות הציר.
2. בסרגל הכלים, לכו ללשונית Insert (הוסף) ולחצו Pivot Table
3. בידקו שהטווח שסימנתם הוא אכן הטווח אליו כלי הטבלאות מתייחס.

זה אמור להיראות כך:

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

4. כעת, סמנו אם ברצונכם ליצור את טבלת הציר בגיליון חדש (New Worksheet) או בגיליון קיים (Existing Worksheet). אם בחרתם בגיליון קיים, לחצו על תא ריק בגיליון הנבחר, וודאו שיש מספיק מקום בתאים הסמוכים כדי להכיל את טבלת הציר. לחצו OK

5. כעת נפתח גיליון חדש שנראה כך:

 

מה אנחנו רואים כאן?

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

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

מה כל אזור מייצג?

ROWS – כאן נציין את את השדה שלפיו נרצה ליצור את השורות בטבלה שלנו.

COLUMNS – כאן נציין את השדה שלפיו נרצה ליצור את העמודות בטבלה שלנו.

FILTERS – לכאן נכניס את שמות השדות שנרצה לסנן על פי הנתונים שבהם את הטבלה. לדוגמא – להכניס את שדה “תאריך” ולסנן נתונים לפי חודש / שנה מסוימת.

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

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

נחזור כעת לפיבוט שרצינו להרכיב:

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

1. נגרור את שדה “שם” ל- ROWS – זה בעצם אומר לאקסל שאנו מעוניינים שהשורות בטבלת הציר יורכבו לפי הערכים שמופיעים בעמודה “שם”.
2. נגרור את שדה “ציון” ל- VALUES – זה אומר שאנו רוצים לבצע את החישובים שלנו עבור עמודת הציון.
3. נלחץ על שדה “ציון” שזה עתה גררנו ל- VALUES, בתפריט שנפתח נלחץ על VALUE FIELD SETTINGS (הגדרות שדה ערכים), ונלחץ על AVERAGE (ממוצע).

בעצם, ביקשנו מטבלת הציר לבצע ממוצע של הציונים לפי השמות השונים.

 

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

 

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

אם נרצה לדעת כמה מקצועות למדה כל סטודנטית, פשוט נשנה את VALUE FIELD SETTINGS של ציון מ”AVERAGE” ל”COUNT”, ונקבל את הטבלה הבאה:

 

חישוב הממוצע עבור כל מקצוע – 

על מנת לחשב את הממוצע עבור כל מקצוע, נגרור החוצה מה- ROWS את שדה השמות ונגרור פנימה את שדה המקצוע. בשדה ה- VALUES נבצע שינוי מ”COUNT OF” ל- “AVERAGE”. זו התוצאה שנקבל:

 

הוספת עמודות בטבלת ציר

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

במקרה הזה, נשתמש באזור ה- COLUMNS (עמודות), באופן הבא:

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

שימוש בפילטר

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

כל מה שצריך לעשות הוא ללחוץ על המשולש שליד Row Labels, והוא יאפשר לנו לבצע פילטור על שדה השמות, באופן הבא:

 

כפי שאולי שמתם לב, איזור ה- “FILTERS” שמופיע מצד ימין בתמונה שלעיל, נותר ריק. לא הזננו לתוכו שום שדה – משום שלא היה צורך.

אנו נשתמש ברובריקה של ה- FILTER כשנרצה לבצע פילטור לפי שדה נוסף ושונה מהשדה של השורות.

בואו נראה דוגמא:

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

 

כשלב ראשון, בואו נסכם את טבלת המכירות לפי לקוח –

כפי שאתם רואים, גררנו את עמודת “שם לקוח” לאיזור השורות, ואת עמודת “סך קניות” לאזור הערכים, וביקשנו שיבצע סיכום (SUM).

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

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

 

דגשים חשובים:

1. בכל פעם שאתם מעדכנים מידע מסוים בטבלת המקור שעליה מתבססת טבלת הציר, עליכם לבצע רענון לטבלת הציר. איך עושים רענון? לכו לטבלת הציר, לחצו קליק ימני ואז “רענון”. ניתן גם על ידי לחיצה על קיצור המקלדת ALT+F5.
2. הוספת שורות/עמודות לטבלת ציר – אך ורק מאמצע הטבלה (ולא לשכוח ללחוץ “רענון” מיד אחר כך). אם לא תעשו זאת ותחליטו להוסיף בסוף הטבלה או בהתחלתה – הטבלה לא תזהה שביצעתם עדכון. אז נא להקפיד – להוסיף רק מהאמצע!
3. יש לוודא שלא השארתם אף תא ריק בשורת הכותרת של טבלת המקור. טבלת הציר זקוקה שכל שורת הכותרת תהיה מלאה, משום שעל סמך שמות אלו היא יוצרת את השדות שעמם אנו עובדים לאחר מכן בטבלת הציר עצמה (שם לקוח, ציון, מכירות וכו’).
4. נסו להימנע משמות כפולים בשורת הכותרת – משום שאז טבלת הציר בוחרת את אחד השמות, ומוסיפה לו את הספרה 2, 3 וכן הלאה וזה עשוי להקשות על זיהוי השדה בהמשך (לדוגמא, אילו היו שתי עמודות בכותרת “לקוח”, השדות היו “לקוח” ו”לקוח2″).

דברים נוספים שכדאי להכיר:

1. לפעמים יהיו מצבים שיהיה לכם נוח יותר להפוך את הפיבוט לטבלה רגילה. על מנת לעשות זאת, בחרו את טבלת הציר, העתיקו אותה והדביקו אותה במיקום מסוים. לאחר שהדבקתם, גשו לתפריט ההדבקה ובצעו הדבקה כערכים (VALUES) או של ערכים ופורמט (VALUE & SOURCES FORMATTING).
2. גילוי/שינוי מקור הנתונים של טבלת הציר – מעוניינים לשנות את מקור הנתונים של טבלת הציר שלכם, או לחילופין פשוט להבין מהיכן מגיעים הנתונים שעליהם מתבססת הטבלה? ליחצו על טבלת הציר שלכם, ולאחר מכן גשו לסרגל העזר שנפתח למעלה תחת לשונית “ANALYZE” ולחצו על “CHANGE DATA SOURCE”:
3. שכפול טבלת ציר – נניח שאתם מעוניינים לשכפל את טבלת הציר שלכם כך שהיא תשמור על תצורת השדות, אך תפעל על מקור נתונים אחר? אין בעיה – בחרו את טבלת הציר כולה, העתיקו אותה, הדביקו במקום אחר ולחצו CHANGE DATA SOURCE על מנת להזין את מקור הנתונים החדש.

עיצוב טבלאות ציר

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

הדבר החשוב ביותר שצריך להכיר בסרגל הזה הוא ה- Report Layout:

 

COMPACT FORM – מרכז את כל תוויות השורה בעמודה אחת. זאת אומרת, אם יצרתם טבלת ציר שבה השורות מוגדרות על יותר משדה אחד (כמו בדוגמא בהמשך) – כל השדות יוצגו בעמודה אחת.

SHOW IN TABULAR FORM – פורס כל תווית שורה בעמודה בפני עצמה. לחצו REPEAT ALL ITEM LABELS במידה ואתם רוצים שכל שורה בטבלת הציר תכיל מידע לגבי כל שדה, גם אם הוא חוזר על עצמו.

להלן המחשה של ההבדל בין COMPACT FORM (מימין) ל- TABULAR FORM עם REPEAT ALL ITEM LABELS פועל (משמאל). מה עדיף? 🙂

 

כפתורי GRAND TOTALS וSUBTOTALS – הם מופיעים ליד כפתור REPORT LAYOUT, ודרכם תוכלו להגדיר אם אתם רוצים להציג סך הכל כולל (בסוף הטבלה) וכן סיכומי ביניים למיניהם.

כלי ה- SLICER

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

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

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

נלחץ על כלי ה- SLICER, נסמן ב- V “חודש”, וכעת יופיע בפנינו הדבר הבא:

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

 

ניתן כמובן להפעיל סינון על כמה חודשים במכה על ידי החזקת מקש CTRL (בחירה של חודשים בודדים כל פעם), או על ידי החזקת מקש SHIFT (בחירה רציפה). אם אתם רוצים דרך קלה ומהירה לראות את כל הערכים (במקרה שלנו – כל החודשים) – פשוט לחצו על סימן המשפך עם האיקס לצדו:  

אם אתם רוצים לשחק קצת עם מראה ה- SLICER, נניח – להציג את החודשים בשתי עמודות כדי שיהיה נוח יותר, לחצו על ה- SLICER, כעת יפתח לכם סרגל כלים של OPTIONS (מעליו יהיה כתוב “SLICER TOOLS”) – כעת שנו את מספר העמודות ל- 2. זו התוצאה:

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

עד כאן מדריך טבלאות הציר. מקווים שהחכמתם ושעשו שימוש נבון ומוצלח בכלי הנהדר הזה! 🙂