קורס אקסל מתקדם פתרון תרגילים בנושא טבלאות ציר (Pivot Table)
תוכן הדף
- טבלאות ציר הסבר כללי
- פתרונות לדוגמה תרגילים נבחרים
פתרונות תרגילים בנושאים נוספים | קורס אקסל מתקדם
טבלאות ציר (Pivot Table)
2. הכרת חלון העבודה -רשימת השדות ואזורי הפריסה
4. הקשר בין הטבלה לדוח ושינוי מקור הנתונים
5. יצירת טבלה חדשה על כל נתון ונתון בדוח
הוספת טבלת ציר (Pivot Table)
סדר פעולות:
נסמן את הטבלה או במידה ואנו יודעים שטבלת הנתונים שלנו אינה מכילה שורות ריקות ניתן פשוט לעמוד על אחד התאים בטבלה ← לשונית הוספה ← PivotTable
← תיפתח חלונית בה יסומן טווח נתוני הטבלה שלנו, ונתבקש לבחור האם ברצוננו למקם את הדוח בגיליון חדש או בגיליון קיים (ברירית המחדל הינה בגיליון חדש – וגם הדוגמאות בחוברת זו יהיו בגיליון חדש).
לפני הלחיצה על אישור יש לוודא כי טווח הנתונים המעודכן הינו הטווח הרצוי, במקרה זה הטווח המסומן הוא A1 עד F1091 וזה אכן טווח הנתונים שלנו ← אישור.
לאחר האישור יפתח גיליון חדש ובו האזור בו נבנה את הדוח. ותיפתח חלונית העבודה (חלונית רשימת השדות).
_________________________________________________________________________________________________________________________
הכרת חלונית העבודה
חלונית העבודה מורכבת משני חלקים:
1.ברשימת השדות, כל עמודה בטבלת הנתונים המקורית מקבלת שדה בחלונית העבודה.
2. ארבעה אזורי יעד: עמודות, שורות, ערכים, מסננים
בניית הדוח נעשית ע"י מיקום השדות הרצויים באזור הפריסה הרצוי, כך שאת השדה שנרצה למקם בשורות נמקם בשורות ואותו הדבר נכון גם לעמודות.
∗ שימו לב שחלונית העבודה נעלמת כאשר אנו עומדים מחוץ לדוח ומוצגת שוב כאשר אנו עומדים בתוך הדוח.
∗ במידה וסגרנו במכוון את החלונית היא לא תיפתח כאשר נעמוד בתוך הדוח. במידה ונתקלנו במצב בו אנו לא מצלחים להחזיר את החלונית יש לעמוד בתוך הדוח ← קליק ימני ← "הצג רשימת שדות" (נמצא בתחתית הרשימה)
בניית הדוח
עכשיו אנו יכולים לבנות דוח המציג את סה"כ המכירות עבור כל נציג ונציג. כל שיש לעשות הוא לגרור את השדה "שם נציג" לשורות ואת השדה "מחיר " לערכים.
בכך ייצרנו דוח המציג בשורות את שם הנציג ואת סה"כ המכירות שלו.
ניתן להוסיף את השדה "מוצר" לעמודות ונקבל דוח המציג את סה"כ המכירות עבור כל נציג עבור כל מוצר.
הקשר בין נתוני הטבלה לדוח
הדוח קשור לטבלת הנתונים ולכן כל שינוי בנתוני הטבלה יתעדכן בדוח, אך לא באופן אוטומטי יש לבצע רענון לדוח.
סדר פעולות:
כאשר אנו עומדים בתוך הדוח ניתן לראות שנוספו לשוניות תחת השם "כלי PivotTable" ← לשונית ניתוח ← רענון (או קליק ימני ← רענון).
לאחר הרענון כל שינוי שנעשה בטבלה המקורית יתעדכן בדוח שלנו.
∗ כאשר ייצרנו לראשונה את דוח התבקשנו להגדיר את טווח הנתונים, בדוגמה זו הגדרנו את השטח המתחיל ב A1 ועד F1091זהו טווח נתונים סטטי (סעיף 1). כל שינוי שיעשה בשורות/עמודות מחוץ לטווח נתונים זה לא יתעדכן כאשר נרענן את הנתונים. כלומר במידה ונוסיף שורות או עמודות לטבלה המקורית – נתונים אלו לא יתעדכנו בדוח לאחר רענון.
לכן במצב זה יש לעדכן מחדש את מקור הנתנים:
סדר פעולות:
כלי PivotTable" ← לשונית ניתוח ← שינוי מקור נתונים ← עדכון מקור הנתונים הרלוונטי ← אישור.
∗ בפרק עצב כטבלה (Format as Table) ראינו שניתן להגדיר טבלה, לתת לה שם ושטווח הטבלה הנו טווח דינאמי המתעדכן אוטו'. ושכל שורה ועמודה שאנו מוסיפים, מתעדכנים באופן אוטו' תחת שם הטבלה. מאפיין זה של טבלה מוגדרת יכול להיות מצויין בשילוב עם דוח Pivot. כאשר אנו מגדירים טבלה כטבלה ומייצרים דוח - הדוח יהיה קשור לטבלה (לטווח נתונים דינאמי) ולכן כל שינוי שנעשה בטבלה המקורית יתעדכן גם בדוח באופן אוטו' לאחר רענון.
∗ כאשר נשייך את הדוח לטבלה המוגדרת/מעוצבת כטבלה כל שינוי שיעשה בשורות/עמודות מחוץ לטווח נתונים יתעדכן כחלק מהטבלה ולכן יתעדכן כאשר נרענן את הנתונים. כלומר במידה ונוסיף שורות או עמודות לטבלה המקורית – נתונים אלו יתעדכנו בדוח לאחר רענון.
להלן מוצג חיבור בין אותה הטבלה לאחר שהוגדרה/עוצבה כטבלה וניתן לה השם "מכירות_מוקד"
ניתן לראות שטווח הנתונים המעודכן הוא שם הטבלה (טווח דינאמי) ולא A1:F1091 (טווח סטטי)
יצירת טבלה חדשה עבור כל נתון ונתון בדוח
נניח ואנו מעוניינים לקבל טבלת נתונים מהם בנוי ערך מסויים בדוח שלנו.
כך לדוגמה נניח וערך המכירות של ערן לוי עבור המוצר ביטוח חיים אינו מסתדר לנו, כלומר אנו מאמינים שיתכן וישנה טעות. כל שעלנו לעשות הוא ללחוץ דאבל קליק על התא המכיל את הערך ונקבל טבלה המסוננת עבור הנתונים מהם מורכב הערך.
להלן הטבלה שתתקבל:
שימו לב שטבלה זו מכילה נתונים רק עבור הנציג ערן לוי והמוצר ביטוח חיים.
קורס אקסל מתקדם | פתרונות תרגילים נבחרים בנושא טבלאות ציר (Pivot Tables)
קובץ Pivot tables 1 - תרגילים מס' 3,4