עבודה עם טבלאות ציר ב- Microsoft Excel

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

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

היסטוריה קטנה

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

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

הבנת PivotTables

אז מה זה PivotTable, בדיוק?

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

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

בדוק את הנתונים המוצגים להלן:

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

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

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

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

כיצד ליצור PivotTable

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

אז אנחנו מתחילים את Excel ... ואנחנו לטעון רשימה כזו ...

ברגע שיש לנו את הרשימה פתוחה ב- Excel, אנחנו מוכנים להתחיל ליצור את PivotTable.

לחץ על כל תא בודד בתוך הרשימה:

ואז, מן הוסף לחץ על הכרטיסייה טבלת ציר סמל you

ה צור PivotTable מופיע, שואל אותך שתי שאלות: אילו נתונים צריך PivotTable החדש שלך להיות מבוסס על, ואיפה זה צריך להיות נוצר? מכיוון שכבר הקלקנו על תא בתוך הרשימה (בשלב הבא), הרשימה כולה המקיפה את התא כבר נבחרה עבורנו ($ A $ 1: $ G $ 88 על תשלומים גיליון זה, בדוגמה זו). שים לב שאנו יכולים לבחור רשימה בכל אזור אחר של גליון עבודה אחר, או אפילו מקור נתונים חיצוני, כגון טבלת מסד נתונים של Access או אפילו טבלת מסד נתונים של MS-SQL Server. אנחנו גם צריכים לבחור אם אנחנו רוצים PivotTable החדש שלנו כדי להיווצר על חדש גליון עבודה, או על קיים אחד. בדוגמה זו אנו בוחר חדש אחד:

גיליון העבודה החדש נוצר עבורנו, ו- PivotTable ריק נוצר בגליון העבודה:

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

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

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

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

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

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

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

עכשיו, סוף סוף, דברים מתחילים להיות מעניינים! PivotTable שלנו מתחיל להתלבש ... .

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

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

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

מה שנראה כך:

מתחיל לקבל מאוד מגניב!

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

גרור את העמודה "חבילה" / כותרת אל דוח מסנן קופסא:

שימו לב לאן זה נגמר ... .

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

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

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

בואו נראה איך זה נראה ... כפי שאתם יכולים לראות, "מזומן" נעלם.

עיצוב

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

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

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

ה הגדרות שדה ערך מופיע.

לחץ על פורמט מספר כפתור, ואת תקן תבנית תיבת תאים מופיע you

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

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

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

לחץ על PivotTable כלים / עיצוב tab you

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

בחר כל אחד שפונה, והסתכל על התוצאה ב- PivotTable שלך:

אפשרויות אחרות

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

ראשית, נסיר את העמודה "שיטת תשלום" תוויות עמודות (פשוט גרור אותו בחזרה לרשימת השדות) והחלף אותו בעמודה "תאריך ההזמנה":

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

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

תיבת הקיבול מופיעה. אנו בוחרים חודשים ולחץ על אישור:

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

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

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

... שנראית ככה ... .

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

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

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

בתוך ה לסכם את שדה הערך לפי ברשימה הגדרות שדה ערך בחר, בחר ממוצעYou

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

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

אם אנחנו אוהבים, אנחנו יכולים אפילו יש סכומים, ממוצעים וספירות (סופרת = כמה המכירות היו) על כל PivotTable זהה!

הנה השלבים כדי לקבל משהו כזה במקום (החל מ PivotTable ריק):

1. גרור את "איש המכירות" לתוך תוויות עמודות
2. גרור את השדה "סכום" למטה ערכים תיבה שלוש פעמים
3. עבור השדה 'סכום' הראשון, שנה את השם המותאם אישית שלו ל'סך הכל 'ואת פורמט המספר שלו חשבונאות (0 מקומות אחרי הנקודה העשרונית)
4. עבור השדה "סכום" השני, שנה את השם המותאם אישית שלו ל"ממוצע ", הפונקציה שלו ממוצע וזה מספר פורמט חשבונאות (0 מקומות אחרי הנקודה העשרונית)
5. עבור השדה "סכום" השלישי, לשנות את שמו "ספירה" ואת הפונקציה שלה לספור
6. גרור את הפריטים שנוצרו באופן אוטומטי שדה מ תוויות עמודות ל תוויות שורה

הנה מה שאנחנו בסופו של דבר עם:

סך הכל, הממוצע ולספור על PivotTable אותו!

סיכום

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

אם אתה עובד ב- Office 2007, ייתכן שתרצה לבדוק את המאמר שלנו כיצד ליצור PivotTable ב- Excel 2007.

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

הורד תרגול בפועל שלנו