קורס אקסל – PivotChart ו- PivotTable

הצורך ב PivotChart ו- PivotTable

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

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

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

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

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

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

כעת נוסיף את טבלת הציר לנתונים:

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

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

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

לאחר הלחיצה על האישור נפתח לנו גיליון עם הצגת שדות חדשים:

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

תזכורת: רצינו לחשב כמה שעונים נמכרו במהלך הרבעון הראשון של שנת 2010

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

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

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

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

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

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

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

כדי להוסיף את כלי הפריסה, ניגש ל: כרטיסיית כלי PivotTable > כרטיסיית ניתוח > קבוצת סינון > הוסף כלי פריסה.

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

נבחר בשעונים ובשנת 2010, ונראה כיצד התכווצו הנתונים:

ניתן לראות שבכל שנת 2010 נמכרו 9 שעונים –

כעת נסיים לכווץ את השנה לרבעונים כדי לדעת כמה שעונים נמכרו במהלך הרבעון הראשון של שנת 2010

נעמוד על תא B4 > לחצן ימני > קיבוץ > ונסמן רבעונים,
ונלחץ על אישור:

נקבל את התוצאה הבאה:

ניתן לראות שהתוצאה לשאלה שלנו היא: 4.

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

כעת ניתן לבטל את המסננים

הערה: כדי לנקות מסנן מכלי פריסה: נלחץ על סמל נקה מסנן:

הערה נוספת: כדי להסיר טבלת ציר, נסמן את כל ה Pivot Table  ונלחץ על מקש ה- Delete.
ניתן לסמן את כל טבלת הציר מתוך הסרגל שלה, ע"י לחיצה על מקש בחר.

הידעת??

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

הוספת ציר זמן

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

נקבל שוב חלון בחירה בו ניתן לבחור רק את האפשרות תאריך

נסמן אותו ונקבל את החלון הבא:

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

לשם הדוגמא – ראינו לעיל שברבע השני של שנת 2010 נמכרו 3 שעונים – פירוק הקבוצה לחודשים ורבעונים יראה לנו ששלוש אלו מורכבים מחודשים: מאי * 1 ויוני * 2:

אם ניגש למסנן ציר הזמן ונגדיר רק את חודש יוני:

נקבל את הטבלה הבאה:

אחד הכלים המרשימים ביותר ב PivotTable  הוא היכול לראות אלו נתונים סימנו. אם נלחץ פעמיים בעזרת הלחצן השמאלי של העכבר על הסינון 2        נקבל את שתי הרשומות מהנתונים בטבלת המקור:

כעת ניתן לבטל את המסננים

הערות:

* כדי לנקות מסנן מכלי פריסה: נלחץ על סמל נקה מסנן: .

** כדי להסיר טבלת ציר, נסמן את כל ה Pivot Table  ונלחץ על מקש ה- Delete.
ניתן לסמן את כל טבלת הציר מתוך הסרגל שלה, ע"י לחיצה על מקש בחר.

הידעת??

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

תרשימי ציר

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

1. תרשימים מומלצים – כלי חזק מאוד שנוסף ב 2013, שמזהה אלו נתונים יש לנו בטבלה וממליץ על התרשים שלדעתו הכי מתאים – אם הנתונים בנויים כמו בדוגמא לעיל, אזי האקסל ימליץ לנו על תרשים ציר, ואפילו יבחר את השדות עברנו.
2. בחירה באפשרות: PivotChart > ואז להמשיך כמו בטבלת ציר לפי השלבים.
   שתי אפשרויות אלו נמצאות בסרגל הכלים של כרטיסיית הוספה > קבוצת תרשימים.
3. כשיצרנו טבלת ציר נוכל לגשת ל: כרטיסיית כלי PivotTable -> כרטיסיית ניתוח -> קבוצת כלים -> Pivot Chart

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

• ריענון טבלת ציר בודדת: כלי PivotTable -> אפשרויות -> נתונים -> רענן.
• ריענון טבלאות ציר מרובות: כלי PivotTable -> אפשרויות -> נתונים -> רענן -> רענן את הכל.

אדגים בקצרה, ע"ג טבלת הציר שסיננו את יצירת תרשים הציר (כמפורט באפשרות 3):

ראשית נסיר שני מסננים, מסנן חודש יוני ומסנן מוצר. נשאיר מסנן אחד בלבד: שנת 2010.

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

2. נלחץ על סרגל הכלים שהופיע: כלי PivotTable – > כרטיסיית ניתוח –

> קבוצת כלים -> Pivot Chart

קיבלנו את תרשים הציר:

תרשימים מומלצים

אדגים את אפשרות 1 שציינתי ליצירת תרשים ציר – תרשימים מומלצים.

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

* בסרגל הכלים הוספה > נלחץ על תרשימים מומלצים

* האקסל יציע לנו מגוון אפשרויות לתרשימים,

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

ניתן לראות שיש מגוון אפשרויות לפי הציר סביבו נרצה לבנות את הנתונים

לדוגמא:

סכום של שנה על מוצר – כלומר הציר שלנו הוא המוצר והערכים הם סכום של השנה.

ספירה לפי הזמנת מוצר.

סכום בחישוב עמודת הדולרים.

סכום בחישוב השקלים ללא מע"מ

סכום חישוב השקלים כולל מע"מ

ועוד ועוד..

נלחץ על התרשים המבוקש, ונקבל את התרשים: