מטמון ציר ב- Excel - מה זה וכיצד ניתן להשתמש בו בצורה הטובה ביותר

אם אתה עובד עם טבלאות Pivot של Excel, מטמון Pivot הוא משהו שאתה בהחלט צריך לדעת עליו.

מהו מטמון Pivot?

מטמון ציר הוא דבר שנוצר באופן אוטומטי בעת יצירת טבלת צירים.

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

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

מטמון הציר מאפשר תפקוד מהיר זה של טבלת צירים.

למרות שאתה חושב שאתה מקושר ישירות לנתוני המקור, במציאות אתה ניגש למטמון הציר (ולא לנתוני המקור) כאשר אתה מבצע שינויים בטבלת הצירים.

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

תופעות לוואי של Civot Cache

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

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

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

שיתוף מטמון הציר

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

מגבלות של מטמון ציר משותף

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

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

הדרך לעקוף מגבלות אלה היא לאלץ את Excel ליצור מטמון ציר נפרד עבור טבלאות ציר שונות (תוך שימוש באותו מקור נתונים).

הערה: אם אתה משתמש במקורות נתונים שונים עבור טבלאות ציר שונות, Excel ייצר עבורו אוטומטית מטמון ציר נפרד.

יצירת מטמון פיבוט כפול (עם אותו מקור נתונים)

להלן 3 דרכים ליצור מטמון ציר כפול תוך יצירת טבלאות ציר מאותו מקור נתונים:

#1 שימוש בשמות טבלאות שונים

• לחץ במקום כלשהו במקור הנתונים ועבור אל הוספה -> טבלה (או שאתה יכול להשתמש בקיצור המקשים - Control + T).
• בתיבת הדו -שיח צור טבלה, לחץ על אישור. הוא ייצור טבלה בשם Table1.
• כאשר כל תא נבחר בטבלה, עבור אל הוספה -> טבלת צירים.
• בתיבת הדו -שיח צור טבלת צירים, תבחין כי בשדה טבלה/טווח יש את שם הטבלה. לחץ על אישור.
  • פעולה זו תיצור את טבלת הציר הראשונה.
• עבור למקור הנתונים (טבלה), בחר תא כלשהו ועבור לעיצוב כלי טבלה -> כלים -> המר לטווח. הוא יראה בקשה לשאול אם ברצונך להמיר טבלה לטווח רגיל. לחץ על כן. זה יהפוך את הטבלה לנתונים לוחיים רגילים.

כעת חזור על השלבים לעיל, ופשוט שנה את שם הטבלה (מטבלה 1 לטבלה 2 או מה שתרצה). אתה יכול לשנות אותו על ידי הזנת השם בשדה שמתחת לשם הטבלה בכרטיסיה עיצוב כלי טבלה.

למרות ששתי הטבלאות (טבלה 1 ו טבלה 2) מתייחסות לאותו מקור נתונים, שיטה זו מבטיחה כי שני מטמוני ציר נפרדים נוצרים לכל טבלה.

#2 באמצעות אשף טבלת הצירים הישן

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

• בחר תא כלשהו בנתונים והקש ALT + D + P.
  • פעולה זו תפתח את אשף טבלת הצירים ואת תרשים הציר.
• בשלב 1 מתוך 3, לחץ על הבא.
• בשלב 2 מתוך 3, ודא שטווח הנתונים נכון ולחץ על הבא.
• Excel מציג הודעה שאומרת בעצם לחיצה על כן ליצירת מטמון ציר משותף ולא כדי ליצור מטמון ציר נפרד.
• לחץ על לא.
• בשלב 3 של האשף, בחר אם אתה רוצה את טבלת הציר בגליון עבודה חדש או באותו גליון עבודה ולאחר מכן לחץ על סיום.

הערה: ודא שהנתונים אינם טבלת Excel.

ספרו את מספר מטמון הצירים

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

להלן דרך מהירה לספור זאת:

• הקש ALT + F11 כדי לפתוח את עורך VB (או עבור לכרטיסייה מפתחים -> Visual Basic).
• בתפריט עורך Visual Basic, לחץ על תצוגה ובחר חלון מיידי (או הקש Control + G). זה יהפוך את החלון המיידי לגלוי.
• בחלון המיידי, הדבק את הקוד הבא והקש Enter:

  ? ActiveWorkbook.PivotCaches.Count

הוא יראה באופן מיידי את מספר מטמון הצירים בחוברת העבודה.

שיפור הביצועים בעבודה עם טבלאות ציר

יש כמה דברים שאתה יכול לעשות כדי לשפר את הביצועים של חוברות עבודה (גודל קובץ ושימוש בזיכרון) בזמן שאתה עובד עם טבלאות ציר:

#1 מחק את נתוני המקור

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

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

#2 אל תשמור את הנתונים במטמון Pivot

כשאתה שומר קובץ עם טבלת צירים ונתוני מקור, הוא גם שומר את מטמון הציר הכולל עותק של נתוני המקור. המשמעות היא שאתה שומר את נתוני המקור בשני מקומות: בגליון העבודה המכיל את הנתונים ובמטמון הציר.

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

כדי לעשות זאת:

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

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

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

ראה גם: שמירת נתוני מקור באמצעות טבלת צירים.

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

#3 שיתוף מטמון ה- Pivot לביצועים טובים יותר

אם במקרה (או בכוונה) אתה מגיע למצב שיש לך מטמון ציר כפול ואתה רוצה למחוק את הכפיל ולשתף את מטמון הציר, להלן השלבים לביצוע הפעולה:

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

• עזרה של Microsoft - ביטול שיתוף של מטמון נתונים בין דוחות PivotTable.

שיעורי שולחן ציר אחרים שאתה עשוי לאהוב:

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