טבלאות ציר באקסל - PIVOT TABLES

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

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

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

לצורך ההדגמה נשתמש בטבלה הבאה:

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

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

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

לפני שמתחילים לבנות טבלת ציר, חייבים לוודא שלושה דברים:

1. כותרות מסודרות - לכל עמודה קיים שם כותרת, ואין כותרות כפולות או תאים מאוחדים.

2. אין הפרדות או פערים בטבלה – לדוגמה, שורות ריקות. במקרה של שורה ריקה, אם ננסה לבחור את הטבלה, "נתפוס" רק חלק קטן ממנה.

3. הטבלה מכילה נתונים בלבד - ללא הערות, שורות סיכום, sub totals, grand totals וכו', כל אלו אינם מספרים אשר מתארים עוד מכירות, ולכן הם רק מכפילים את המספרים, וזה יהרוס את החישובים.

אלו שלושה תנאים שחייבים להתקיים.

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

השלב הבא פשוט – יש לדאוג שהסמן ממוקם איפשהו בטבלה.

נלחץ על לשונית ה-INSERT, ומכאן נבחר את הכפתור הראשון (השמאלי ביותר) - PIVOT TABLE.

אקסל יזהה באופן אוטומטי את טווח הנתונים שלנו.

נלחץ על OK.

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

בפאנל הימני ישנה רשימה של כל העמודות מהטבלה המקורית.

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

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

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

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

במקרה הזה ניקח את PROFIT ונשים ב- VALUES.

ניתן לראות שאנחנו כבר מקבלים את הסכום הכולל - זה בעצם סכום כל הרווחים בטבלה.

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

לעמודות הקטגוריות הללו אנחנו קוראים Dimensions, והן עוזרות לענות על שאלות כמו מי, מה, מתי, למה, איך וכו'.

במקרה זה, ניקח את שורת המדינה ונגרור אותה ל-ROWS.

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

בשורה התחתונה עדיין רואים את הסכום הכולל (Grand Total) שראינו קודם לכן. סכום זה בעצם מייצג את הסכום של כל הרווחים מכל המדינות.

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

כעת כבר ממש אפשר לראות "טבלת ציר".

ציר ה-Y מורכב משמות המדינות:

וציר ה-X מורכב משמות המוצרים:

בשוליים ניתן לראות את הסכום הכולל לכל מוצר על פני כלל המדינות:

ואת הסכום הכולל לכל מדינה על פני כלל המוצרים:

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

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

ניתן ממש לראות את החלוקה לכל סוג לקוח, בכל אחת מהמדינות:

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

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

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

כעת הפכנו את הסדר:

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

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

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

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

מקווים שהטיפ הזה יהיה שימושי עבורכם, בהצלחה!