מי מפחד מ GetPivotData?
אם ניסיתם לכתוב פונקציה שאחד הפרמטרים שלה נלקח מתוך טבלת ציר,
בוודאי שמתם לב לכך שאקסל משתמש בפונקציה GetPivotData.
זוהי אחת הפונקציות השנויות ביותר במחלוקת באקסל. למה?
– כי היא נכתבת בלי שנבקש
– כי התחביר שלה מוזר
– כי היא פועלת בצורה מוחלטת, בניגוד לברירת המחדל היחסית של אקסל
– כי אנחנו לא מבינים מה היא עושה
אבל… אם רק תתנו לה צ'אנס, תראו שהפונקציה הזאת יכולה להיות מועילה ויעילה
ואם אתם בונים דשבורדים שמבוססים על טבלאות ציר
(וזה אחד מהטיפים החשובים שאני מלמדת בקורס בניית דשבורדים באמצעות טבלאות ציר), אני מבטיחה לכם שהפונקציה הזאת תהפוך להיות החברה הכי טובה שלכם.
מה מטרתה של הפונקציה הזו?
אז קודם כל, בואו נבין מה הפונקציה הזאת עושה:
מטרתה לאחזר נתון שנוצר מחיתוך של שורה ועמודה.
בואו נביט בדוגמה הבאה, שמציגה את ממוצע המשכורות במפעל, בחיתוך של מחלקה ומין:
כנקודת התייחסות לעבודתנו, אנו מעוניינים לשמור בתא בגיליון את ממוצע משכורות הנשים במטה האגף.
לצורך כך, נעמוד בתא מחוץ לטבלת הציר, ונכתוב בו את הנוסחה =C7 (זהו התא שבו נמצא השכר הממוצע של נשות מטה האגף)
אם נביט בשורת הנוסחאות, נראה שאקסל יצר את הנוסחה הבאה:
=GETPIVOTDATA("משכורת",$A$3,"מחלקה","מטה האגף","מין","נקבה")
משמעות הנוסחה היא שבמקום לשמור על המיקום, כלומר על תא C7,
אקסל שמר על נקודת החיתוך של הערך הממוצע של המשכורות,
כאשר הפריט הנבחר של שדה המין הוא נקבה והפריט הנבחר של שדה המחלקה הוא מטה האגף!
היתרון הגדול בנוסחה הוא שגם אם ישתנו נתוני הטבלה המקורית,
כלומר – יתווספו עוד אגפים, וייתכן שכתובת התא שמאחסן כרגע את ממוצע נשות מטה האגף ישתנה, ולא יהיה עוד C7,
החיתוך תמיד ישמר, ותמיד נקבל את ממוצע משכורות הנשים במטה האגף!
אחד השימושים החשובים באפשרות העוצמתית הזאת בבניית דשבורדים,
שבהם אנו רוצים להציג תמיד נתונים מסוימים,
כמו מספר העובדים בחברה, ממוצע המשכורות או כל נתון רלוונטי אחר לדשבורד.
מכיוון שטבלאות הציר שלנו מתעדכנות עם כל שינוי בנתונים,
אנו זקוקים לדרך שבה נוכל תמיד לקבל את הנתון הרצוי,
ואת זה משיגים באמצעות השימוש ב GetPivotData!
אבל, אם נרצה לגרור את הנוסחה, נגלה שברירת המחדל בעת שימוש בנוסחאות המתייחסות לנתונים בטבלת הציר היא הפניה מוחלטת, בניגוד לטבלת אקסל שבה ברירת המחדל היא הפניה יחסית.
בשל הקיבוע, לא נוכל לדוגמה לגרור את הנוסחה לאורך כל הטבלה, ולקבל תוצאות יחסיות.
עם זאת, ישנו טריק שיאפשר לנו הן להשתמש בפונקציה GetPivotData והן לשמור על היחסיות,
וזאת על ידי החלפת הערכים הקבועים המתקבלים בפונקציה בהפניה אל תאים.
ואם בכל זאת נרצה לבטל את היצירה האוטומטית של הפונקציה GetPivotData
נלחץ על 'כלי Pivot Table', לשונית 'ניתוח', לחצן 'אפשרויות' ונסיר את הסימון מ'צור רכיב GetPivotData'
לחילופין, אם נבחר להשאיר את השימוש בפונקציה GetPivotData, נוכל להקליד את הנוסחה בתא
(במקום להצביע על התאים עם העכבר), כך שתתקבל הפניה יחסית רגילה.
מעוניינים ללמוד עוד על טבלאות ציר?
לחצו על הלינק לרכישת ספר מקיף, הכולל את מודל הנתונים.
הספר קיים בגרסה מודפסת (בשורה הראשונה) ובגרסה דיגיטלית להורדה מיידית (בשורה השנייה)
מעוניינים בקורס מקוון ללימוד טבלאות ציר? לחצו כאן