טבלת ציר באקסל

PIVOT TABLE טבלת ציר באקסל

מהי טבלת ציר

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

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

 

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

 

יצירת טבלת ציר

1. יש לסמן את מסד הנתונים (בעזרת מקשי מקלדת CTRL A),
2. מלשונית "הוספה" INSERT ולבחור בפקודה " טבלת ציר" (Pivot Table)
3. יופיע אשף עבור טבלת ציר המכיל שאלות:
4. נוודא שהוא אכן רואה את גבולות הטבלה הרצויים
5. נוודא שהדוח יווצר במיקום שאנו רוצים (בד"כ בגיליון חדש) או בגיליון זה, ואז יש לסמן לו את התא ששם תתחיל טבלת הציר) ונאשר
6. כאשר נוצרת טבלת ציר, נוצרת תמונת טבלה בתוך תאי הגיליון ובצד נוצר חלון חדש המכיל את כותרות הטבלה המקורית – הכותרות נקראות שדות PIVOT TABLE

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

איזורי הטבלה הם:

1. עמודה COLUMN
2. שורה ROW
3. נתונים VALUE
4. מסנן FILTER.

זוהי טבלה אשר בשורות יש תאריכים

בעמודות שם הלקוח

בערכים הסכומים

ובאיזור הפילטר שם המוצר

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

פתיחת גיליון פירוט

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

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

גרירת הכותרת גם לשורה וגם לערכים

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

ניתן לגרור אותו לשורה וכמו כן נגרור אותו לערכים

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

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

ביצוע פעולות סיכום נוספות

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

נוכל לשנות את הפעולה ל: ספירה, ממוצע, הערך הגבוה/הנמוך ביותר

יש ללחוץ לחצן ימני על אחד המספרים, ולבחור "סכם ערכים כ.." Summarize Values By

לבחור את הפעולה הרצויה

שינוי מבנה טבלת הציר

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

לדוגמא

שמות הסוכנים התווספו ראשונים לאיזור השורות

ושמות המוצרים אחריהם,

ואז יש היררכיה, שם סוכן וכל המוצרים שמכר וכן הלאה

הכל נמצא בטור A

ולכן גם הכותרת היא "תוויות שורה" ולא שם השדה בפועל

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

מתפריט כלי PIVOT

כרטסת עיצוב לבחור פריסת דוח– הצג בצורת טבלה

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

במידה ונרצה לבטל את סיכומי הביניים

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

 

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

מתפריט כלי PIVOT , כרטסת עיצוב, פריסת דוח, לבחור חזור על תוויות הפריטים

הוספת כלי פריסה

כלי הפריסה משמשים כמו מסנני הדוחות, מאפשרים סינון של הפריטים שנרצה/לא נרצה לראות

יצירת כלי פריסה (סלייסר)

נעמוד על טבלת הציר, מתפריט כלי PIVOT נבחר ניתוח – הוסף כלי פריסה

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

כאן בחרנו לסנן לפי התאור (שם המוצר)

סרגל כלי הפריסה

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

1. ניתן לתת שם לכלי הפריסה ולהכנס למאפיינים
2. ניתן לחבר את כלי הפריסה לכמה טבלאות ציר
3. ניתן לעצב בצבעים שונים את כלי הפריסה
4. ניתן להעביר אחורה/קדימה מבחינת תצוגה
5. ניתן לשנות את מספר עמודות כלי הפריסה – כך שיהיה אופקי
6. ניתן לשנות את הגובה, הרוחב והגודל של כלי הפריסה

הוספת ציר זמן

ניתן לסנן בתוך טבלת ציר את הטבלה לפי זמנים בעזרת סרגל ציר זמן

יש לוודא שהתאריכים בטבלה המזינה את טבלת הציר כתובים כהלכה (כלומר יום/חודש/שנה)

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

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

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

ניתן לגרור את הפס הכחול למקומות שונים ולראות חתכים של סכומים לפי החודשים/שנים

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

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

לדוגמא:

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

נרצה לבצע נוסחת חילוק בין שני שדות: שדה המכירות בפועל לבין שדה תחזית המכירות

1. מתפריט כלי טבלת ציר, נבחר ניתוח, שדות פרטים וערכות, נבחר שדה מחושב
2. נקליד שם לשדה המחושב, ונעזר ברשימת הכותרות כדי להוסיף שדה ולבצע את הפונקציה הנדרשת.
3. נשתמש בסוגי הפעולה * , – , * , / וכד' ונאשר

לטבלת הציר יתווסף טור חדש, ולשדות טבלת הציר, תתווסף כותרת חדשה.

נעצב את הטור החדש כאחוזים.

במידה ונרצה לשנות את הטור המחושב (למשל להוסיף מינוס 1 לחישוב באחוזים)

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

נפתח את שם השדה בעזרת לחצן החץ למטה

נבחר את השדה שיצרנו קודם

ונשנה בו את הנוסחה

יצירת גרף מנתוני טבלת הציר:

עמוד בתוך טבלת הציר – בכרטיסיה ניתוח, בחר PIVOT CHART

בתיבת הדו-שיח הוספת תרשים, לחץ על סוג התרשים וסוג המשנה של התרשים הרצוי.

(ניתן להשתמש בכל סוג תרשים פרט לתרשימי: xy פיזור , בועות או מניות, קרני שמש, היסטוגרמה)

דוח PivotChart ודוח טבלת הציר חייבים להיות תמיד באותה חוברת עבודה.

ליתר פירוט על גרפים בטבלאות ציר ראה.