בין אם אתה בעל עסק קטן או מנהל מחלקה בחברה גדולה, המומחים שלנו לאקסל שלנו יכולים לשנות את האופן שבו אתה עובד.
עם ידע עמוק וניסיון רב שנים באקסל, המומחים שלנו יסייע לך למקסם את הפוטנציאל של הנתונים שלך.
חסוך זמן ומשאבים בעזרת קבצי אקסל משוכללים, דוחות אוטומטיים וממשקים חכמים.
צא לדרך לעסק יעיל וממוקד נתונים עוד היום - צור קשר עכשיו!
תיאור הבעיה - למה לא להשתמש בהפניות רגילות עבור מידע מתוך טבלת פיבוט ?
שלום וברכה,
הנוסחה שאנחנו הולכים ללמוד היום, קלה מאוד לשימוש, והתועלת שלה מרובה. קורה הרבה למשתמשי אקסל, שכאשר צריכים לשלוף מידע מתוך טבלת פיבוט (Pivot table), אז משתמשים בהפניה פשוטה לתא מסויים בטבלה. נניח, אם התא הרצוי הוא d4, אז נרשום פשוט
=D4
אבל, בשיטה הזאת יש חיסרון גדול. השיטה הזו, מפנה אל תא ספציפי, ולא מתייחסת לעמודה ולשורה הרלוונטים בתוך טבלת הפיבוט. ואז, כעבור חודש, חודשיים… טבלת הפיבוט עשויה להתעדכן, והמיקום של החודש הרלוונטי עשוי לרדת \ לעלות, ואז ההפניה שכתבנו לתא ספציפי, הופכת להיות לא רלוונטית.
והפתרון - נוסחת GETPIVOTDATA
כמובן, שיש לזה פתרון אלגנטי, ודי פשוט. אם נשתמש בנוסחת GETPIVOTDATA, נוכל לציין בקלות, שבעצם אנחנו שולפים מידע לפי הצלבה בין עמודה\שורה מסויימים. בצורה הזו, גם אם הטבלה תשתנה בהמשך, עדין, הנוסחה תשלוף עבורנו את התא הנכון.
כמה פשוט להשתמש בנוסחת GETPIVOTDATA ?
נוסחת GETPIVOTDATA היא ברירת המחדל כאשר באים לשלוף מידע מטבלת פיבוט. זאת אומרת, שאם נלחץ בתא כלשהוא על סימן = ואז אחרי כן נלחץ על תא שנמצא בתוך טבלת הפיבוט, אז אקסל באופן אוטומטי ירכיב עבורנו נוסחת GETPIVOTDATA מתאימה, וכל שיוותר לנו הוא רק לאשר באמצעות לחיצה על ENTER.
כלומר, למעשה אקסל נוקט עבורנו בדרך החכמה יותר ומונע מאיתנו לעשות טעויות באמצעות הפניות ישירות, ובמקום זאת, אקסל מציע לנו כברירת מחדל, שימוש בנוסחה GETPIVOTDATA.
עד עכשיו ראינו דוגמה לשימוש פשוט ב-GETPIVOTDATA. כעת, בואו נתקדם ל-2 מקרים קצת יותר מתקדמים. באמצעות המקרים האלו, נלמד על 2 אלמנטים שיש לשים לב אליהם, כאשר משתמשים בנוסחת GETOPIVOTDATA.
שימוש ב-GETPIVOTDATA לצורך שליפה באמצעות בחירה מרשימה
פעמים רבות, נרצה לשלוף מתוך טבלת הפיבוט (Pivot table), נתונים על פי הצלבה (חתך) בין מספר פרמטרים. למשל :
- משכורת של עובד מסוים עבור חודש מסוים.
- מחיר של מוצר מסוים ב חודש מסוים.
- מכירות לפי סניף מסוים עבור קטגורית מוצרים מסויימת.
- וכמובן, ניתן לחשוב על דוגמא הקרובה לעולמכם, גם בחתך של יותר מ-2 פרמטרים יחד.
רוצה ליצור דוחות ותרשימים מתקדמים בעזרת Excel?
שירות של מומחה Excel יכול להציע לך פתרונות מותאמים לצרכים שלך.
צור קשר עכשיו
במקרים האלו, נרצה לתת למשתמש תיבות בחירה, שבהן הוא יבחר את הערכים הרלוונטים, ולפי זה, אנחנו נשלוף עבורו את הפרטים מתוך הפיבוט.
באמצעות נוסחת GETPIVOTDATA זה הופך להיות פשוט. בואו נראה כיצד.
נתחיל בכך שניצור 2 רשימות של ערכים בצד. לצורך הדוגמה, כתבתי בצד רשימה אחת של מגדר, ורשימה שניה של מספרי חודשים. ואז על מנת ליצור את תיבות הבחירה, לחצתי על התא המתאים, ובחרתי בתפריט נתונים ואז אימות נתונים > מתוך רשימה. כאן בחרתי את הרשימה המתאימה, כלומר את התאים שמרכיבים אותה. ואז חזרתי שוב על התהליך, בתא השני.
כעת, בדוגמא שלי, המשתמש יכול לבחור את 2 הערכים שמרכיבים את החיתוך הרצוי.
כעת - נרכיב נוסחת GETPIVOTDATA, ונשנה אותה, כך שתשלוף את הערך המתאים לחתך שהמשתמש בחר.
לצורך כך, נבין ראשית, איזה פרמטרים מקבלת נוסחת GETPIVOTDATA.
איזה פרמטרים מקבלת נוסחת GETPIVOTDATA ?
נוסחת GETPIVOTDATA מקבלת
- פרמטר ראשון - את השדה הרצוי מתוך הערכים בטבלת הפיבוט.
- פרמטר שני - את המיקום של התא הראשון של טבלת הפיבוט.
- פרמטר שלישי, רביעי, חמישי ואילך - זוגות של פרמטרים. כלומר תמיד מקלידים בהתחלה את השם של העמודה\השורה, ואחרי כן סימן פסיק, ואז את הערך הרצוי.
לדוגמא - בנוסחה הבאה :
=GETPIVOTDATA("סכום רכישה",$A$3,"מגדר הקונה",G21,"תאריך רכישה",G22)
- הפרמטר הראשון, הוא “סכום רכישה” - כלומר השדה בערכי טבלת הפיבוט שנקרא “סכום רכישה”.
- פרמטר שני - המיקום A3 זהו התא שבו מתחילה טבלת הפיבוט (Pivot Table).
- ואחרי כן יש לנו זוגות של פרמטרים :
- מגדר הקונה - ניקח אותו מתוך תיבת הבחירה הראשונה שהגדרנו.
- ו- תאריך הרכישה (יותר נכון, חודש הרכישה) - ניקח אותו מתוך תיבת הבחירה השנייה שהגדרנו.
רוצה להפוך את עבודתך ב-Excel לפחות מסובכת ויותר יעילה?
מומחה Excel יכול להפוך תהליכים לאוטומטים ולבנות עבורך אקסלים יעילים.
צור קשר עכשיו
מה יצא לנו ?
נוכל לראות, שבשיטה הזאת, בעצם אנחנו מאפשרים למי שמשתמש באקסל שלנו, לשלוף את הערך בחתך הרצוי.
ועבורנו, העבודה שנדרשה כדי לבצע זאת, הייתה קלה מאוד.
וכעת, לנקודה הבאה שצריך לשים לב אליה, בעבודה עם נוסחת GETPIVOTDATA.
איך מתייחסים לחודשים בנוסחת GETPIVOTDATA ?
חודשים, למרות שהם מוצגים במילים : יולי, אוגוסט וכדומה, נתייחס אליהם בתור מספרים. זאת אומרת, שבדוגמא שהצגתי למעלה, בוודאי שמתם לב שתיבת הבחירה של החודשים, התבססה על מספרי החודשים, ולא על השמות שלהם. זאת כיוון שככה זה מוגדר מתחת למכסה המנוע של טבלת פיבוט.
אז בדוגמא הבאה - רצינו לשלוף את כל הערכים לפי חודשים. רשמנו לעצמנו את המספרים 7,8 וגררנו מטה.
ואז לחצנו לצד המספר 7, והקלדנו סימן = ואז לחצנו על תא מסויים בתוך טבלת הפיבוט.
אקסל נתן לנו מייד נוסחת GETPIVOTDATA.
אנחנו צריכים להתאים את נוסחת ה-GETPIVOTDATA שהתקבלה לצרכינו.
- ראשית - נחליף את התא שמציין את החודש, בתא שמכיל את המספר 7.
- ושנית - נוריד את זוג הפרמטרים האחרון שעוסק ברבעון.
וכך בעצם, יצרנו מצב שאפשרי לגרור את הנוסחה הזאת כלפי מטה, וכל הערכים ישלפו בצורה נכונה מתוך טבלת הפיבוט.
סיכום - שליפה בצורה נכונה של ערכים מתוך טבלת ציר
במדריך זה, הבנו שיש חיסרון מסויים בשליפה מתוך טבלת פיבוט (Pivot table) באמצעות הפניה ישירה. למדנו כיצד להשתמש בנוסחת GETPIVOTDATA על מנת לשלוף מידע מטבלת פיבוט. בנוסף לכך, ראינו 2 דגשים לגבי השימוש בנוסחה זאת : חודשים יש לציין במספרים, ושנית, יש להתאים את זוגות הפרמטרים לצרכינו. הבנו גם את מבנה הפרמטרים שמקבלת נוסחת GETPIVOTDATA, וכך נוכל להתאים את הנוסחה לצורך שלנו.
👉 החלקים הבאים הם חזרה על החומר בצורות שונות למעוניינים.
מחפש פתרון מתקדם ואפקטיבי ב-Excel ?
צוות המומחים שלנו ב-"תותח אקסל" מסוגל להתמודד עם כל אתגר, גדול כקטן , בתחום תוכנת אקסל.
בין אם מדובר בניתוח נתונים מורכב, פיתוח דוחות אוטומטיים או שילוב עם תוכנות אחרות לשימוש יעיל באקסל.
אנחנו בתותח אקסל מציעים פתרונות המותאמים באופן מדויק לצרכים שלכם.
עם ידע רחב וניסיון עשיר, נהפוך עבורך כל פרויקט או בעיה להזדמנות לשיפור והצלחה.
תותח אקסל - הבחירה המושלמת למי שרוצה להשתמש באקסל לא רק ככלי, אלא כנשק עסקי חד וחזק.
צור קשר עכשיו
שאלות נפוצות על שליפת נתונים מפיבוט
איך לשלוף נתונים מטבלת פיבוט ?
הדרך הנכונה לשלוף נתונים מטבלת PIVOT היא להשתמש בנוסחה GETPIVOTDATA. נוסחה זו מאפשרת לך לשלוף נתון מפיבוט, לפי החיתוך של העמודה והשורה המתאימים. בצורה הזו, גם אם טבלת הפיבוט תשתנה בהמשך ותכיל יותר או פחות ערכים, עדין הערך שלך ישלף בצורה נכונה, כיוון שהנוסחה תשלוף אותו על פי החיתוך בין העמודה והשורה שהגדרת.
האם נכון לשלוף נתונים מטבלת פיבוט באמצעות הפניה ישירה ?
לא. כיוון שהפניה ישירה לתא, תגרום לכך שבמידה וטבלת הפיבוט תשתנה בעתיד, אז הנוסחה תפנה אל תא לא נכון. הדרך הנכונה לשלוף נתונים מטבלת פיבוט, היא באמצעות נוסחת GETPIVOTDATA.
איך לשלוף נתון מטבלת PIVOT ? - מדריך שלב אחר שלב
- עבור ללשונית באקסל בה נמצא הפיבוט
- בחר תא כלשהוא, שאליו יגיע המידע
- הקלד את הסימן = (שווה ) כדי להתחיל נוסחה
- לחץ על התא שמכיל את המידע הרלוונטי בתוך טבלת הפיבוט
- אקסל ירכיב עבורך נוסחת GETPIVOTDATA - התאם אותה לצרכיך (אם צריך).
בהצלחה!
אם אתם מחפשים לשדרג את ניתוח הנתונים והדו"חות שלכם באופן מקצועי ?
אני ממליץ בחום לשקול את השירות שלנו של תותח Excel.
המומחיות שלנו באקסל תורמת להגשמת היעדים ולפתרון של בעיות מורכבות בצורה יעילה ומהירה.
עם מומחה Excel, אתם יכולים להיות בטוחים שתהליך העבודה שלכם יתנהל בצורה מקצועית.
ליצירת קשר לחצו כאן.
