המרת תאי PivotTable לנוסחאות גליון עבודה

PivotTable כולל כמה פריסות המספקות מבנה מוגדר מראש לדוח, אך לא ניתן להתאים אישית פריסות אלה. אם אתה זקוק לגמישות רבה יותר בעיצוב הפריסה של דוח PivotTable, באפשרותך להמיר את התאים לנוסחאות של גליון עבודה ולאחר מכן לשנות את הפריסה של תאים אלה על-ידי ניצול מלא של כל התכונות הזמינות בגליון עבודה. באפשרותך להמיר את התאים לנוסחאות המשתמשות בפונקציות קוביה או להשתמש בפונקציה GETPIVOTDATA. המרת תאים לנוסחאות מפשטת במידה רבה את תהליך היצירה, העדכון והתחזוקה של טבלאות PivotTable מותאמות אישית אלה.

בעת המרת תאים לנוסחאות, נוסחאות אלה ניגשות לנתונים זהים לאלה של ה- PivotTable וניתן לרענן אותן כדי לראות תוצאות מעודכנות. עם זאת, למעט מסנני דוחות, אין לך עוד גישה לתכונות האינטראקטיביות של PivotTable, כגון סינון, מיון, או הרחבה וכיווץ של רמות.

למד אודות תרחישים נפוצים של המרת טבלאות PivotTable לנוסחאות גליון עבודה

להלן דוגמאות אופייניות לפעולות שבאפשרותך לבצע לאחר המרת תאי PivotTable לנוסחאות גליון עבודה כדי להתאים אישית את הפריסה של התאים המומרים.

סידור מחדש ומחיקה של תאים 

נניח שיש לך דוח תקופתי שעליך ליצור מדי חודש עבור הצוות שלך. אתה זקוק רק לקבוצת משנה של מידע הדוח, ואתה מעדיף לפרוס את הנתונים באופן מותאם אישית. באפשרותך פשוט להעביר ולסדר תאים בפריסת העיצוב הרצויה, למחוק את התאים שאינם נחוצים לדוח הצוות החודשי ולאחר מכן לעצב את התאים ואת גליון העבודה בהתאם להעדפתך.

הוספת שורות ועמודות 

נניח שברצונך להציג מידע מכירות עבור השנתיים האחרונות בחלוקה לפי אזור וקבוצת מוצרים, ושברצונך להוסיף פרשנות מורחבת בשורות נוספות. פשוט הוסף שורה והזן את הטקסט. בנוסף, ברצונך להוסיף עמודה המציגה מכירות לפי אזור ולפי קבוצת מוצרים שאינם ב- PivotTable המקורי. פשוט הוסף עמודה, הוסף נוסחה כדי לקבל את התוצאות הרצויות ולאחר מכן מלא את העמודה כלפי מטה כדי לקבל את התוצאות עבור כל שורה.

שימוש במקורות נתונים מרובים 

נניח שברצונך להשוות את התוצאות בין מסד נתונים של ייצור למסד נתונים של בדיקות כדי לוודא שמסד הנתונים של הבדיקות מניב את התוצאות הצפויות. באפשרותך להעתיק בקלות נוסחאות בתאים ולאחר מכן לשנות את ארגומנט החיבור כך שיצביע על מסד הנתונים של הבדיקה כדי להשוות בין שתי תוצאות אלה.

שימוש בהפניות לתאים כדי לגוון קלט משתמש 

נניח שברצונך שהדוח כולו ישתנה בהתבסס על קלט משתמש. באפשרותך לשנות ארגומנטים בנוסחאות הקוביה להפניות לתאים בגליון העבודה ולאחר מכן להזין ערכים שונים בתאים אלה כדי להפיק תוצאות שונות.

יצירת פריסה לא אחידה של שורות או עמודות (הנקראת גם דיווח אסימטרי) 

נניח שעליך ליצור דוח המכיל עמודה בשם 2008 הנקראת 'מכירות בפועל', עמודה בשם 2009 הנקראת 'מכירות חזויות', אך אינך מעוניין בעמודות אחרות. באפשרותך ליצור דוח המכיל רק את העמודות הללו, בניגוד ל- PivotTable, הדורש דיווח סימטרי.

יצירת נוסחאות קוביה וביטויי MDX משלך 

נניח שברצונך ליצור דוח המציג מכירות עבור מוצר מסוים על-ידי שלושה אנשי מכירות ספציפיים עבור חודש יולי. אם אתה בקיא בביטויי MDX ובשאילתות OLAP, באפשרותך להזין את נוסחאות הקוביה בעצמך. אף על פי שנוסחאות אלה יכולות להיות מורכבות למדי, באפשרותך לפשט את היצירה ולשפר את הדיוק של נוסחאות אלה באמצעות השלמה אוטומטית של נוסחה. לקבלת מידע נוסף, ראה שימוש בהשלמה אוטומטית של נוסחה.

המרת תאים לנוסחאות המשתמשות בפונקציות קוביה

1. כדי לשמור את ה- PivotTable לשימוש עתידי, מומלץ ליצור עותק של חוברת העבודה לפני שתמיר את ה- PivotTable על-ידי לחיצה על 'קובץ>שמירה בשם'. לקבלת מידע נוסף, ראה שמירת קובץ.

2. הכן את ה- PivotTable כך שתוכל למזער את הסידור מחדש של התאים לאחר ההמרה על-ידי ביצוע הפעולות הבאות:

   • שנה לפריסה הדומה ביותר לפריסה הרצויה.
   • בצע אינטראקציה עם הדוח, כגון סינון, מיון ועיצוב מחדש של הדוח, כדי להשיג את התוצאות הרצויות.

3. לחץ על ה- PivotTable.

4. בכרטיסיה אפשרויות , בקבוצה כלים , לחץ על כלי OLAP ולאחר מכן לחץ על המר לנוסחאות.
   אם אין מסנני דוחות, פעולת ההמרה מסתיימת. אם קיים מסנן דוחות אחד או יותר, תיבת הדו-שיח 'המרה לנוסחאות ' מוצגת.

5. החלט כיצד ברצונך להמיר את ה- PivotTable:
   המרת ה- PivotTable כולו 

   • בחר בתיבת הסימון 'המר מסנני דוחות '.
     פעולה זו ממירה את כל התאים לנוסחאות גליון עבודה ומוחקת את ה- PivotTable כולו.
     המר רק את תוויות השורה, תוויות העמודה ואזור הערכים של PivotTable, אך שמור על מסנני הדוחות 

   • ודא שתיבת הסימון 'המרת מסנני דוחות ' אינה מסומנת. (זוהי ברירת המחדל.)
     פעולה זו ממירה את כל התאים של תוויות השורה, תוויות העמודה ואזור הערכים לנוסחאות של גליון עבודה, ומשאירה את ה- PivotTable המקורי, אך עם מסנני הדוחות בלבד, כדי שתוכל להמשיך לסנן באמצעות מסנני הדוחות.

     הערה

     אם תבנית PivotTable היא מגירסה 2000-2003 או גירסה קודמת, ניתן להמיר רק את ה- PivotTable כולו.

6. לחץ על המר.
   פעולת ההמרה מרעננת תחילה את ה- PivotTable כדי לוודא שנעשה שימוש בנתונים עדכניים.
   הודעה מוצגת בשורת המצב בזמן שפעולת ההמרה מתבצעת. אם הפעולה נמשכת זמן רב ואתה מעדיף להמיר במועד אחר, הקש ESC כדי לבטל את הפעולה.

   הערה

   • לא ניתן להמיר תאים עם מסננים שהוחלו עליהם לרמות מוסתרות.
   • לא ניתן להמיר תאים שבהם לשדות יש חישוב מותאם אישית שנוצרו באמצעות הכרטיסיה 'הצג ערכים כ ' בתיבת הדו-שיח 'הגדרות שדה ערכים '. (בכרטיסיה אפשרויות , בקבוצה שדה פעיל , לחץ על שדה פעיל ולאחר מכן לחץ על הגדרות שדה ערכים.)
   • עבור תאים עוברים המרה, עיצוב התא נשמר, אך סגנונות PivotTable מוסרים מכיוון שסגנונות אלה יכולים לחול על טבלאות PivotTable בלבד.

המרת תאים באמצעות הפונקציה GETPIVOTDATA

באפשרותך להשתמש בפונקציה GETPIVOTDATA בנוסחה כדי להמיר תאי PivotTable לנוסחאות גליון עבודה כאשר ברצונך לעבוד עם מקורות נתונים שאינם OLAP, כאשר אתה מעדיף לא לשדרג מיד לתבנית החדשה של PivotTable גירסה 2007 או כאשר ברצונך להימנע מהמורכבות של השימוש בפונקציות הקוביה.

1. ודא שהפקודה 'צור GETPIVOTDATA ' בקבוצה PivotTable בכרטיסיה 'אפשרויות ' מופעלת.

   הערה

   הפקודה 'צור GETPIVOTDATA ' מגדירה או מנקה את האפשרות השתמש בפונקציות GETPIVOTTABLE עבור הפניות PivotTable בקטגוריה 'נוסחאות' של המקטע 'עבודה עם נוסחאות ' בתיבת הדו-שיח 'אפשרויות Excel '.

2. ב- PivotTable, ודא שהתא שבו ברצונך להשתמש בכל נוסחה גלוי.

3. בתא בגליון עבודה מחוץ ל- PivotTable, הקלד את הנוסחה הרצויה עד לנקודה שבה ברצונך לכלול נתונים מהדוח.

4. לחץ על התא ב- PivotTable שבו ברצונך להשתמש בנוסחה שלך ב- PivotTable. לנוסחה נוספת פונקציית גליון עבודה מסוג GETPIVOTDATA, אשר מאחזרת את הנתונים מה- PivotTable. פונקציה זו ממשיכה לאחזר את הנתונים הנכונים אם פריסת הדוח משתנה או אם אתה מרענן את הנתונים.

5. סיים להקליד את הנוסחה והקש ENTER.

בעיה: לא ניתן להמיר תאי PivotTable לנוסחאות של גליונות עבודה