אתר זה הוקם ע"י
ספקטרא הדרכת אקסל - 25 שנות ניסיון בהדרכה מקצועית בעסקים וארגונים
- הדרכת אקסל בכל הרמות
- הדרכת Google Sheets
- הדרכת Power BI
- הדרכות באנגלית
- לאתר ספקטרא
- קבל הצעת מחיר
-
הפונקציה GETPIVOTDATA
הסבר
החזרת נתונים המאוחסנים בטבלאות ציר Pivot Table. ניתן להזין נוסחה פשוטה GETPIVOTDATA במהירות על-ידי הקלדת = (סימן שוויון) בתא שברצונך להחזיר את הערך ולאחר מכן לחיצה על התא ב- PivotTable המכיל את הנתונים שברצונך להחזיר.
כדי שהפונקציה תהיה פעילה, יש לוודא שיש סימון בתוך אפשריות כלי PIVOT – צור רכיב GetPivotData
אם נרצה לבטל את האפשרות לנוסחת GetPivotData , כלומר נוכל לסמן תא בתוך טבלת ציר ולא לקבל את הנוסחה, יש לבטל את ה בתפריט
ניתן להזין נוסחה פשוטה של GETPIVOTDATA, על-ידי הקלדת = (סימן שוויון) בתא חיצוני לטבלת הציר, ולאחר מכן לבחור את התא בתוך טבלת הציר המכיל את הנתונים שברצונך לקבל, הפונקציה תיווצר באופן אוטומטי ותכיל בתוכה באופן מדויק את ההפניה לאותו תא מסויים בטבלת הציר המושפע (המסונן) מתוך השדות (מכותרות השורה וכותרות העמודה) המרכיבים את טבלת הציר.
לדוגמא אם נעמוד מחוץ לטבלת ציר קיימת, נקיש = ונבחר תא בתוך טבלת ציר, נקבל את שם הפונקציה ואת כתובת התא שבחרנו המקובעת($) קיבוע מלא ואת המסננים שיצרו אותה.
בדוגמא זו בחרנו את הסה"כ של המכירות של אבי עם המחשב הנייד
בתוך הפונקציה נרשמו הדברים כך:
בתוך הפונקציה נמצאת כותרת הסיכום של המספרים = 3A
כותרת השדה שממנו לקחנו את השם אבי = "שם לקוח" ואת התא שבו נמצא אבי שהוא 15B
כותרת השדה שממנו לקחנו את המחשב הנייד = "מוצר" ואת התא של המחשב הנייד 16A
והנתונים עם קיבוע מלא.אם נרצה לגרור את פונקציית GetPivotData, יש לבטל את הקיבועים, כך שהשורה לא תהיה מקובעת וכך יהיה ניתן לגרור את הפונקציה. בד"כ משתמשים בפונקציה זו, כאשר רוצים ליצור טבלה חיצונית (רצוי עם רשימה נפתחת) לטבלת הציר ולהשתמש בתוצאות שהניבה טבלת הציר.
מגבלת הפונקציה: הנתון שהיא מחזירה מאוד מדוייק וזה מקשה על גרירת הנוסחה לכיוונים שונים.
יתרונות הפונקציה: במידה וטבלת הציר משתנה, כלומר מתווספים נתונים, או אם טבלת הציר עוברת מיון כלשהו ואז השורות משנות את הסדר – הפונקציה עדין תמיד תחפש את אותו נתון בכל מקום שבו יהיה בטבלת הציר .תחביר
GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)
ארגומנטים נדרשים
- Data_field השם במרכאות של שדה הנתונים המכיל את הנתונים שברצונך לאחזר.
- Pivot_table הפניה לכל תא, טווח של תאים או טווח בעל שם של תאים ב- PivotTable. מידע זה משמש כדי לקבוע אילו PivotTable מכיל את הנתונים שברצונך לאחזר.
ארגומנטים אופציונליים
Field1, Item1, Field2, Item2 1 עד 126 זוגות של שמות שדות ושמות פריטים המתארים הנתונים שברצונך לאחזר. הזוגות יכול להיות בכל סדר. שמות שדות ושמות עבור פריטים שאינם תאריכים ומספרים המוקפים במרכאות. עבור טבלאות Pivottable של OLAP, פריטים עשויים להכיל את שם המקור של הממד וכן את שם מקור של הפריט. זוג השדה והפריט עבור PivotTable של OLAP עשוי להיראות כך:"[Product]","[Product].[All Products].[Foods].[Baked Goods]" דוגמה
הערות*
- החישובים במסגרת הפונקציה GETPIVOTDATA כוללים שדות או פריטים מחושבים וכן חישובים מותאמים אישית.
- אם pivot_table הוא טווח הכולל שני או יותר טבלאות Pivottable, הנתונים מאוחזרים מהדוח העדכני ביותר שנוצר בטווח.
- אם הארגומנטים של השדה והפריט מתארים תא יחיד, הערך של אותו תא מוחזר בכל מקרה, בין אם מדובר במחרוזת, מספר, שגיאה וכן הלאה.
- אם פריט כולל תאריך, יש לבטא את הערך כמספר סידורי או כמספר מאוכלס באמצעות הפונקציה DATE, כך שהערך יישמר גם בעת פתיחה של גליון העבודה באזור אחר. לדוגמה, ניתן להזין פריט המתייחס לתאריך 5 במרץ 1999 כ- 36224 או כ- DATE(1999,3,5). ניתן להזין שעות כערכים עשרוניים, או באמצעות הפונקציה TIME.
- אם pivot_table אינו טווח שבו נמצא PivotTable, הפונקציה GETPIVOTDATA מחזירה #REF!.
- אם הארגומנטים אינם מתארים שדה גלוי, או אם הם כוללים מסנן דוחות שבו הנתונים המסוננים אינם מוצגים, הפונקציה GETPIVOTDATA מחזירה את ערך השגיאה #REF!.
*כפי שמופיעות באתר התמיכה לאופיס של מיקרוסופט