עבודה עם PivotTables ב-Microsoft Excel

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

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

• קצת היסטוריה
• מהן טבלאות ציר?
• איך יוצרים טבלת ציר?
• הגדרת PivotTable
• עיצוב טבלת ציר
• הגדרות אחרות של טבלת ציר
• סיכום

קצת היסטוריה

בימים הראשונים של תוכנת הגיליון האלקטרוני, כדור השלטון Lotus 1-2-3. הדומיננטיות שלה הייתה כה מוחלטת עד שהמאמצים של מיקרוסופט לפתח תוכנה משלה (אקסל) כחלופה ללוטוס נראו כמו בזבוז זמן. עכשיו מהר קדימה לשנת 2010! Excel שולט בגיליונות אלקטרוניים יותר ממה שקוד לוטוס עשה אי פעם בהיסטוריה שלו, ומספר האנשים שעדיין משתמשים בלוטוס קרוב לאפס. איך זה יכל לקרות? מה הייתה הסיבה לתפנית כה דרמטית?

אנליסטים מזהים שני גורמים עיקריים:

• ראשית, לוטוס החליטה שפלטפורמת GUI חדשנית זו בשם Windows היא רק אופנה חולפת שלא תימשך זמן רב. הם סירבו לבנות גרסת Windows של Lotus 1-2-3 (אבל רק לכמה שנים), תוך שהם חזו שגרסת ה-DOS של התוכנה שלהם תהיה כל מה שהצרכנים יצטרכו אי פעם. מיקרוסופט פיתחה באופן טבעי את Excel במיוחד עבור Windows.
• שנית, מיקרוסופט הציגה כלי באקסל בשם PivotTables שלא היה זמין ב-Lotus 1-2-3. PivotTables, הבלעדיים ל-Excel, הוכיחו שהם שימושיים עד כדי כך שאנשים נטו להישאר עם חבילת התוכנה החדשה של Excel במקום להמשיך עם Lotus 1-2-3, שלא היה להם אותם.

PivotTables, יחד עם חוסר הערכת ההצלחה של Windows באופן כללי, שיחקו את צעדת המוות עבור Lotus 1-2-3 והובילו את הצלחת Microsoft Excel.

מהן טבלאות ציר?

אז מהי הדרך הטובה ביותר לאפיין מה הם PivotTables?

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

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

תסתכל על הנתונים המוצגים באיור שלהלן:

שימו לב שלא מדובר בנתונים גולמיים, שכן הם כבר סוכמו. בתא B3 אנו רואים $30000, וזו כנראה התוצאה הכוללת שעשה ג'יימס קוק בינואר. איפה הנתונים המקוריים אז? מאיפה הגיע הנתון של 30000$? איפה רשימת המכירות המקורית שממנה נגזר הסכום החודשי הזה? ברור שמישהו עשה עבודה מצוינת בארגון ובמיון של כל נתוני המכירות בחצי השנה האחרונה והפיכתם לטבלת הסכומים שאנו רואים. כמה זמן לדעתך לקח? שָׁעָה? השעה עשר?

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

אם נחזור לרשימת המכירות המקורית, היא תיראה בערך כך:

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

איך יוצרים טבלת ציר?

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

אז, אנחנו מתחילים את אקסל ... וטוענים רשימה כזו ...

לאחר שפתחנו רשימה זו באקסל, נוכל להתחיל ליצור טבלת ציר.

בחר כל תא מהרשימה הזו:

ואז בכרטיסייה הַכנָסָה (הוסף) פקודה בחר טבלת ציר (טבלת ציר):

תיבת דו-שיח תופיע צור PivotTable (יצירת טבלת ציר) עם שתי שאלות עבורך:

• באילו נתונים להשתמש כדי ליצור טבלת ציר חדשה?
• איפה לשים את טבלת הציר?

מכיוון שבשלב הקודם כבר בחרנו באחד מתאי הרשימה, הרשימה כולה תיבחר אוטומטית ליצירת טבלת ציר. שים לב שאנו יכולים לבחור טווח אחר, טבלה אחרת, ואפילו מקור נתונים חיצוני כלשהו כגון טבלת מסד נתונים של Access או MS-SQL. בנוסף, עלינו לבחור היכן למקם את טבלת הציר החדש: על גיליון חדש או על אחד מהקיימים. בדוגמה זו, נבחר באפשרות - גיליון עבודה חדש (לגיליון חדש):

Excel יצור גיליון חדש ויציב עליו טבלת ציר ריקה:

ברגע שנלחץ על תא כלשהו בטבלת הציר, תופיע תיבת דו-שיח נוספת: רשימת שדות PivotTable (שדות טבלת ציר).

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

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

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

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

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

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

זה נהיה יותר מעניין! PivotTable שלנו מתחיל להתגבש...

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

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

הגדרת PivotTable

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

אנחנו מקבלים את התוצאה:

נראה מאוד מגניב!

עכשיו בואו נעשה טבלה תלת מימדית. איך ייראה שולחן כזה? בוא נראה…

גרור את הכותרת חֲבִילָה (מורכב) לאזור מסנני דיווח (מסננים):

שימו לב איפה הוא...

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

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

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

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

עיצוב טבלאות PivotTables באקסל

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

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

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

תיבת דו-שיח תופיע הגדרות שדה ערך (אפשרויות שדה ערך).

לחץ על הכפתור עיצוב מספר (פורמט מספר), תיפתח תיבת דו-שיח. עיצוב תאים (פורמט תא):

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

כפי שאתה יכול לראות, המספרים מעוצבים כסכומי דולרים.

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

לחץ על כלי PivotTable: עיצוב (עבודה עם PivotTables: קונסטרוקטור):

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

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

הגדרות PivotTable אחרות ב-Excel

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

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

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

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

תיבת הדו-שיח של קיבוץ תופיע. אנחנו בוחרים חודשים (חודשים) ולחץ OK:

וואלה! הטבלה הזו שימושית הרבה יותר:

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

יש עוד נקודה חשובה מאוד שאתה צריך לדעת! אתה יכול ליצור לא אחת, אלא כמה רמות של כותרות שורה (או עמודות):

… וזה ייראה כך…

ניתן לעשות את אותו הדבר עם כותרות עמודות (או אפילו מסננים).

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

כדי להתחיל, לחץ על סכום הסכום ומהתפריט שמופיע בחר הגדרות שדה ערך (אפשרויות שדה ערך):

הרשימה סכם את שדה הערך לפי (פעולה) בתיבת הדו-שיח הגדרות שדה ערך (אפשרויות שדה ערך) בחר מְמוּצָע (מְמוּצָע):

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

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

אם תרצו, תוכלו לקבל מיד את הכמות, הממוצע והמספר (מכירות) בטבלת ציר אחת.

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

1. גרור את הכותרת איש מכירות (נציג מכירות) לאזור תוויות עמודות (עמודות).
2. גרור את הכותרת שלוש פעמים כמות (עלות) לאזור ערכים (ערכים).
3. לשדה הראשון כמות לשנות את הכותרת ל סה"כ (כמות), ופורמט המספר בשדה זה הוא חשבונאות (כַּספִּי). מספר המקומות העשרוניים הוא אפס.
4. שדה שני כמות שם ממוצעה, הגדר את הפעולה עבורו מְמוּצָע (ממוצע) ופורמט המספרים בשדה זה משתנים גם הם ל חשבונאות (פיננסי) עם אפס מקומות עשרוניים.
5. לתחום השלישי כמות להגדיר כותרת לִסְפּוֹר וניתוח עבורו - לִסְפּוֹר (כַּמוּת)
6. ב תוויות עמודות שדה (עמודות) נוצר באופן אוטומטי Σ ערכים (Σ ערכים) - גרור אותו לאזור תוויות שורה (שורות)

הנה מה נסיים עם:

סכום כולל, ערך ממוצע ומספר מכירות - הכל בטבלת ציר אחת!

סיכום

טבלאות ציר ב-Microsoft Excel מכילות הרבה תכונות והגדרות. במאמר כל כך קטן, הם אפילו לא קרובים לכסות את כולם. יידרש ספר קטן או אתר אינטרנט גדול כדי לתאר במלואו את כל האפשרויות של טבלאות ציר. קוראים נועזים וסקרנים יכולים להמשיך ולחקור את טבלאות הציר. כדי לעשות זאת, פשוט לחץ לחיצה ימנית על כמעט כל רכיב בטבלת הציר וראה אילו פונקציות והגדרות נפתחות. ברצועת הכלים תמצאו שתי לשוניות: כלי PivotTable: אפשרויות (ניתוח) ו עיצוב (בַּנַאִי). אל תפחד לטעות, אתה תמיד יכול למחוק את PivotTable ולהתחיל מחדש. יש לך הזדמנות שמעולם לא הייתה למשתמשי DOS ו-Lotus 1-2-3 מזמן.