דוחות במהירות עם PIVOT 2012-06-01T09:07:12+00:00

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

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

אז מה זה בעצם PIVOT ?

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

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

אז בא נראה דוגמא פשוטה ואחרי כן הכול יהיה ברור יותר.

יצירת טבלת ציר – PIVOT

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

לצורך הפשטות של הדוגמא הטבלה תהיה קצרה :

פיבוט באקסל 1

כדי ליצור טבלת ציר ( Pivot ) :

1. נסמן את הטבלה המיועדת המכילה את הנתונים (שלב זה אינו חובה)

2. בכרטיסיה הוספה נבחר בכפתור Pivot Table

פיבוט אקסל 2

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

אקסל פיבוט 3

4. לאחר לחיצה על אישור, אקסל יפתח גיליון חדש ובו טבלה כזו בצד :

פיבוט אקסל 4

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

סמן ב- V את השורה "מוצר", ולאחר מכן סמן ב-V את השורה "מחיר"

תוך כדי הלחיצות אקסל יצר דוח שמסכם את סך הפדיון מכל מוצר ומוצר.

אקסל פיבוט 5

עתה נסה לשחק מעט עם סימוני ה- V, וראה כי האקסל מנסה כל פעם "לפלח" עבורך את הדוח לפי השדות (=עמודות) שאתה בוחר לסמן לו ב-V.

הסימון של השדות השונים ב- V מתאים לדוחות מאוד פשוטים, וזוהי רק דרך אחת להשתמש ב-PIVOT.

אך ישנה עוד דרך : במקום לסמן שדות ב- V, גרור את השדות אל תוך התיבה המתאימה בתחתית.
תוכל לגרור כל שדה לאחד מ-4 התיבות הבאות :

אקסל פיבוט 6

מכיוון שמעט קשה להסביר זאת במדריך כתוב (ואני ממליץ בחום לראות את מדריך הוידאו שלי בנושא Pivot )

נדגים את 4 התיבות השונות באמצעות הדוגמא הבאה :

אנחנו מעוניינים :

– לסכם את סך הפדיון רק של מגבות החוף.

– לפי לקוחות – כלומר, כל לקוח בשורה נפרדת

– ולפי צורת התשלום, כלומר כל צורת תשלום בעמודה נפרדת

כדי לבצע דוח כזה נפעל לפי ההוראות הבאות :

-ראשית, נרצה לנקות את הדוח הקודם שעשינו.
לצורך כך ,

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

– חזור על אותו תהליך עם השדה סכום של מחיר

פיבוט אקסל 7

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

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

2. עתה, בגיליון עצמו, לחץ על החץ הקטן שליד המילה מוצר – ובחר ב"מגבת חוף" ולאחר מכן לחץ " אישור"

אקסל פיבוט 8

כעת נמשיך לשלב הבא –

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

כדי לעשות זאת – נגרור את השדה מחיר אל תוך התיבה ערכים

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

לצורך השלב הזה, האחרון, נגרור את השדה לקוח לתיבה תויות שורה

ואת השדה צורת תשלום נגרור אל התיבה תויות עמודה

.

הדוח שנקבל נראה כך :

פיבוט 9

לסיכום מה שלמדנו עד כה :

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

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

מבט נוסף על סיכום נתונים באמצעות Pivot

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

אך Pivot מסוגל לסכם נתונים גם באמצעות פונקציות אחרות כמו : ממוצע (Avarage ) , ספירה (Count ) ועוד.

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

– בחר בתא אחד בתוך עמודת הסיכום

– מקש ימני בעכבר > סכם נתונים לפי > ובחר בצורת הסיכום המועדפת עליך.

פביוט 10 פיבוט

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

במקרה כזה, ברירת המחדל של ה- PIVOT היא למנות/לספור אותם (Count ) , כי הרי לא ניתן לסכם נתונים מילולים.

למשל – ראו את הדוגמא הזו :

– גרור החוצה את השדה צורת תשלום ואת השדה מחיר

– גרור את השדה לקוח אל תוך תיבת ערכים

– יתקבל דוח PIVOT כזה :

פיבוט 11

אם תשים לב – תבחין בכך שה PIVOT ספר את כמות הלקוחות – והוא גם מציג זאת בכותרת השדה : "ספירה של לקוח", כלומר ספירה של שדה שנקרא לקוח

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

איך מוחקים PIVOT ?

יש 2 צורות לעשות זאת :

1. סמן את כל איזור הטבלה, כולל איזור הפילטר למעלה, ופשוט לחץ על מקש DEL

2. בסרגל העליון כלי Pivot Table (שמופיע על המסך רק אם תבחר תא בתוך ה-PIVOT )

לחץ על כפתור נקה ובחר באפשרות נקה הכול.

פיבוט 12 pivot

איך מוסיפים סיכומי ביניים לטבלת PIVOT ?

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

אז איך עושים זאת – סיכומי ביניים ב- PIVOT :

– בחר עם העכבר באחד הנתונים בעמודה שאתה מעוניין להוסיף לה סיכום ביניים

– לחץ מקש ימני > וסמן ב- V את האפשרות סיכום ביניים של [שם השדה שלך]

(את סיכום הביניים שהדגמתי כרגע, כמובן שניתן לעשות זאת גם באמצעות רצועת הכלים)

pivot 13 פביוט פיבוט

וקצת על עיצוב של ה- PIVOT

להציג PIVOT בעיצוב מדורג או טבלאי ?

את דוח ה- PIVOT אתה יכול לבחור לעצב באחת מ-2 צורות : צורה טבלאית וצורה מדורגת.

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

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

בצורה מדורגת – טבלת ה- PIVOT תיראה כך :

פיבוט 14 pivot

לעומת זאת בצורה טבלאית, , דוח ה- PIVOT יראה כך :

פיבוט 15

הבדל קטן אך משמעותי לדעתי.

איך קובעים איך יוצג דוח ה- PIVOT ? באמצעות הלחצן הגדרות שדה (ברצועת הכלים > כלי PivotTable , או לחלופין לחיצת מקש ימני על כל עמודה בטבלת ה-PIVOT ובחירה באפשרויות שדה)

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

פיבוט 16

ועוד שתי דברים קטנים על PIVOT :

רענון טבלת PIVOT

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

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

פיבוט 17

שינוי מקור המידע של טבלת PIVOT

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

טבלת ה- PIVOT המקורית שלנו אינה כוללת את העמודות או השורות החדשים,

כדי להגדיר מחדש את מקור הנתונים של ה- PIVOT יש ללחוץ על שינוי מקור נתונים

פיבוט אחרון

בהצלחה!

[do_widget_area widget_area_class=none]

page