PivotTable כולל כמה פריסות המספקות מבנה מוגדר מראש לדוח, אך לא ניתן להתאים אישית פריסות אלה. אם דרושה לך גמישות רבה יותר בעיצוב הפריסה של דוח PivotTable, באפשרותך להמיר את התאים לנוסחאות גליון עבודה ולאחר מכן לשנות את הפריסה של תאים אלה על-ידי ניצול מלא של כל התכונות הזמינות בגליון עבודה. באפשרותך להמיר את התאים לנוסחאות המשתמשות בפונקציות קוביה או להשתמש בפונקציה GETPIVOTDATA. המרת תאים לנוסחאות מפשטת מאוד את תהליך היצירה, העדכון והתחזוקה של טבלאות PivotTable מותאמות אישית אלה.
בעת המרת תאים לנוסחאות, נוסחאות אלה ניגשות לאותם נתונים כמו ה- PivotTable ובאפשרותך לרענן אותן כדי לראות תוצאות עדכניות. עם זאת, למעט מסנני דוחות אפשריים, כבר אין לך גישה לתכונות האינטראקטיביות של PivotTable, כגון סינון, מיון או הרחבה לכיווץ של רמות.
הערה: בעת המרת PivotTable של עיבוד אנליטי מקוון (OLAP), באפשרותך להמשיך לרענן את הנתונים כדי לקבל ערכי מדידה עדכניים, אך לא ניתן לעדכן את החברים בפועל המוצגים בדוח.
למד אודות תרחישים נפוצים להמרת טבלאות PivotTable לנוסחאות גליון עבודה
להלן דוגמאות אופייניות לפעולות שניתן לבצע לאחר המרת תאי PivotTable לנוסחאות גליון עבודה כדי להתאים אישית את הפריסה של התאים שהומרו.
סידור מחדש ומחיקה של תאים
נניח שיש לך דוח תקופתי שעליך ליצור מדי חודש עבור הצוות שלך. דרושה לך רק קבוצת משנה של פרטי הדוח ואתה מעדיף לפרוס את הנתונים באופן מותאם אישית. באפשרותך פשוט להעביר ולסדר תאים בפריסת עיצוב הרצויה, למחוק את התאים שאינם דרושים עבור דוח הצוות החודשי ולאחר מכן לעצב את התאים ואת גליון העבודה בהתאם להעדפתך.
הוספת שורות ועמודות
נניח שברצונך להציג פרטי מכירות עבור השנתיים האחרונות המנותנות לפי אזור וקבוצת מוצרים, ושברצונך להוסיף הערות מורחבות בשורות נוספות. פשוט הוסף שורה והזן את הטקסט. בנוסף, ברצונך להוסיף עמודה המציגה מכירות לפי אזור וקבוצת מוצרים שאינה נמצאת ב- PivotTable המקורי. פשוט הוסף עמודה, הוסף נוסחה כדי לקבל את התוצאות הרצויות ולאחר מכן מלא את העמודה כלפי מטה כדי לקבל את התוצאות עבור כל שורה.
שימוש במקורות נתונים מרובים
נניח שברצונך להשוות בין תוצאות בין מסד נתונים של ייצור לבדיקה כדי לוודא שמסד הנתונים של הבדיקה מפיק את התוצאות הצפויות. באפשרותך להעתיק בקלות נוסחאות תא ולאחר מכן לשנות את ארגומנט החיבור כך יצביע על מסד הנתונים לבדיקה כדי להשוות בין שתי תוצאות אלה.
שימוש בהפניות לתאים כדי לשנות קלט משתמש
נניח שברצונך שהדוח כולו ישתנה בהתבסס על קלט המשתמש. באפשרותך לשנות ארגומנטים לנוסחאות הקוביה להפניות לתאים בגליון העבודה ולאחר מכן להזין ערכים שונים בתאים אלה כדי לגזור תוצאות שונות.
יצירת פריסת שורה או עמודה שאינה יחידה (נקראת גם דיווח אסימטרי)
נניח שעליך ליצור דוח המכיל עמודה של 2008 הנקראת 'מכירות בפועל', עמודה של 2009 שנקראת 'מכירות מופרוייקטות', אך אינך מעוניין בעמודות אחרות. באפשרותך ליצור דוח המכיל רק עמודות אלה, בניגוד ל- PivotTable, המחייב דיווח סימטרי.
יצירת נוסחאות קוביה וביטויים MDX משלך
נניח שברצונך ליצור דוח המציג מכירות עבור מוצר מסוים על-ידי שלושה אנשי מכירות ספציפיים לחודש יולי. אם יש לך ידע לגבי ביטויי MDX ושאילתות OLAP, באפשרותך להזין את נוסחאות הקוביה בעצמך. על אף שנוסחאות אלה יכולות להיות מורכבות למדי, באפשרותך לפשט את היצירה ולשפר את הדיוק של נוסחאות אלה באמצעות השלמה אוטומטית של נוסחה. לקבלת מידע נוסף, ראה שימוש בהשלמה אוטומטית של נוסחה.
הערה: באפשרותך להמיר PivotTable של עיבוד אנליטי מקוון (OLAP) בלבד באמצעות הליך זה.
-
כדי לשמור את ה- PivotTable לשימוש עתידי, מומלץ ליצור עותק של חוברת העבודה לפני המרת ה- PivotTable על-ידי לחיצה על קובץ > שמירה בשם. לקבלת מידע נוסף, ראה שמירת קובץ.
-
הכן את ה- PivotTable כך שתוכל למזער את סידור התאים מחדש לאחר ההמרה על-ידי ביצוע הפעולות הבאות:
-
שנה לפריסה הדומה ביותר לפריסה הרצויה.
-
קיים אינטראקציה עם הדוח, כגון סינון, מיון ועיצוב מחדש של הדוח, כדי לקבל את התוצאות הרצויות.
-
-
לחץ על ה- PivotTable.
-
בכרטיסיה אפשרויות , בקבוצה כלים, לחץ על כלי OLAP ולאחר מכן לחץ על המר לנוסחאות.
אם אין מסנני דוחות, פעולת ההמרה תושלם. אם קיימים מסנן דוחות אחד או יותר, תיבת הדו-שיח המרה לנוסחאות מוצגת.
-
החלט כיצד ברצונך להמיר את ה- PivotTable:
המרת ה- PivotTable כולו
-
בחר את תיבת הסימון המרת מסנני דוחות.
פעולה זו ממירה את כל התאים לנוסחאות גליון עבודה ומחקה את ה- PivotTable כולו.
המר רק את תוויות השורה, תוויות העמודות והערכים של PivotTable, אך שמור את מסנני הדוחות
-
ודא שתיבת הסימון המרת מסנני דוחות אינה מסומנת. (זוהי ברירת המחדל.)
פעולה זו ממירה את כל תאי האזור של תווית השורה, תווית העמודה והערכים לנוסחאות גליון עבודה, ושומרת על ה- PivotTable המקורי, אך רק עם מסנני הדוחות, כדי שתוכל להמשיך לסנן באמצעות מסנני הדוחות.
הערה: אם תבנית ה- PivotTable היא גירסה 2000-2003 או גירסה קודמת, באפשרותך להמיר את ה- PivotTable כולו בלבד.
-
-
לחץ על המר.
פעולת ההמרה מרענן תחילה את ה- PivotTable כדי להבטיח שנעשה שימוש בנתונים עדכניים.
הודעה מוצגת בשורת המצב בזמן פעולת ההמרה. אם הפעולה נמשכת זמן רב ואתה מעדיף לבצע המרה בזמן אחר, הקש ESC כדי לבטל את הפעולה.
הערות:
-
לא ניתן להמיר תאים עם מסננים המוחלים על רמות מוסתרות.
-
אין באפשרותך להמיר תאים שבהם לשדות יש חישוב מותאם אישית שנוצר באמצעות הכרטיסיה הצג ערכים כ בתיבת הדו-שיח הגדרות שדה ערכים. (בכרטיסיה אפשרויות , בקבוצה שדה פעיל , לחץ על שדה פעיל ולאחר מכן לחץ על הגדרות שדה ערכים.)
-
עבור תאים המומרים, עיצוב התא נשמר, אך סגנונות PivotTable מוסרים מכיוון שסגנונות אלה יכולים לחול על טבלאות PivotTable בלבד.
-
באפשרותך להשתמש בפונקציה GETPIVOTDATA בנוסחה כדי להמיר תאי PivotTable לנוסחאות גליון עבודה כאשר ברצונך לעבוד עם מקורות נתונים שאינם OLAP, כאשר אתה מעדיף שלא לשדרג לתבנית החדשה של PivotTable גירסה 2007 באופן מיידי, או כאשר ברצונך להימנע מה במורכבות השימוש בפונקציות הקוביה.
-
ודא שהפקודה צור GETPIVOTDATA בקבוצה PivotTableבכרטיסיה אפשרויות מופעלת.
הערה: הפקודה צור GETPIVOTDATA מגדירה או מנקה את האפשרות השתמש בפונקציות GETPIVOTTABLE עבור הפניות PivotTable בקטגוריה נוסחאות של המקטע עבודה עם נוסחאות בתיבת הדו-שיח אפשרויות Excel.
-
ב- PivotTable, ודא שהתא שבו ברצונך להשתמש בכל נוסחה גלוי.
-
בתא גליון עבודה מחוץ ל- PivotTable, הקלד את הנוסחה הרצויה עד לנקודה שבה ברצונך לכלול נתונים מהדוח.
-
לחץ על התא ב- PivotTable שבו ברצונך להשתמש בנוסחה ב- PivotTable. פונקציית גליון עבודה GETPIVOTDATA נוספת לנוסחה מאחזרת את הנתונים מה- PivotTable. פונקציה זו ממשיכה לאחזר את הנתונים הנכונים אם פריסת הדוח משתנה או אם אתה מרענן את הנתונים.
-
סיים להקליד את הנוסחה והקש ENTER.
הערה: אם תסיר אחד מהתאים שאליהם מתבצעת הפניה בנוסחת GETPIVOTDATA מהדוח, הנוסחה מחזירה #REF!.
