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