לְהִצטַיֵן

טבלאות ציר של Excel

Excel Pivot Tables

סקירה כללית | למה Pivot? | טיפים | דוגמאות | הַדְרָכָה

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



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

מהו שולחן ציר?

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





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

תוכן

הדרכה שלב אחר שלב

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



נתונים לדוגמא

ה נתונים לדוגמא מכיל 452 רשומות עם 5 שדות מידע: תאריך, צבע, יחידות, מכירות ואזור. נתונים אלה מושלמים לטבלת ציר.

נתוני מכירות לדוגמא כבר בטבלת Excel

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

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

הכנס טבלת ציר

1. כדי להתחיל, בחר כל תא בנתונים ולחץ על טבלת צירים בכרטיסיה הוספה של הסרט:

Click the button at Insert>טבלת צירים טבלת צירים' />

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

2. עקוף את מיקום ברירת המחדל והזן H4 כדי למקם את טבלת הצירים בגליון העבודה הנוכחי:

צור חלון טבלת צירים

3. לחץ על אישור, ו- Excel בונה טבלת ציר ריקה המתחילה בתא H4.

טבלת ציר חדשה ריקה בוהה בתא H4

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

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

חלונית שדות לטבלת ציר ריקה חדשה

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

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

הוסף שדות

1. גרור את שדה המכירות לאזור הערכים.

Excel מחשב סך כולל, 26356. זהו סכום כל ערכי המכירה במכלול הנתונים כולו:

סך כל הנתונים במערך הנתונים

2. גרור את שדה הצבע לאזור שורות.

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

פירוט לפי צבע

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

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

חלונית שדות טבלת צירים - מכירות לפי צבע

עיצוב מספרים

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

1. לחץ לחיצה ימנית על כל מספר מכירות ובחר בפורמט מספר:

לחץ לחיצה ימנית ובחר בפורמט מספר

2. החל עיצוב מטבע עם אפס מקומות עשרוניים, לחץ על אישור:

פורמט מספר מטבע עם אפס מקומות עשרוניים

בטבלת הציר המתקבלת, כל ערכי המכירות החלים פורמט מטבע:

טבלת ציר עם פורמט מטבע הוחל

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

מיון לפי ערך

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

Right-click and select Sort>מהגדול עד הקטן מהגדול ביותר' />

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

פירוט לפי צבע, הצבעים הנמכרים ביותר

רענן נתונים

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

1. בחר תא F5 ושנה $ 11.00 ל $ 2000.

2. לחץ באמצעות לחצן העכבר הימני בכל מקום בטבלת הצירים ובחר 'רענן'.

כדי לעדכן נתונים, לחץ באמצעות לחצן העכבר הימני ובחר

שימו לב ש'אדום 'הוא כעת הצבע הנמכר ביותר, ועובר אוטומטית לראש:

טבלת ציר לאחר רענון נתונים

3. שנה את F5 חזרה ל- $ 11.00 ורענן את הציר שוב.

להצטיין למצוא את המספר הראשון במחרוזת

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

שדה ערך שני

תוכל להוסיף יותר משדה אחד כשדה ערך.

1. גרור יחידות לאזור ערך כדי לראות מכירות ויחידות יחד:

חלוקה לפי צבע עם מכירות ויחידות

אחוז מהסך הכל

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

1. הסר את היחידות מאזור הערכים

2. הוסף את שדה המכירות (שוב) לאזור הערכים.

3. לחץ לחיצה ימנית על המופע השני ובחר '% מהסכום הכולל':

Right-click select Show values as>אחוזים מסך אחוזים מסך' />

התוצאה היא פירוט לפי צבע יחד עם אחוזים מהסך הכל:

טבלת ציר - פירוט לפי צבע עם אחוזים

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

להלן חלונית השדות בשלב זה:

חלונית שדות טבלת צירים - מכירות לפי צבע עם אחוזים

קבוצה לפי תאריך

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

1. הסר את שדה המכירות השני (Sales2).

2. גרור את שדה התאריך לאזור עמודות.

3. לחץ לחיצה ימנית על תאריך באזור הכותרת ובחר 'קבוצה':

לחץ לחיצה ימנית על תאריך ובחר קבוצה

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

הגדרות קיבוץ תאריכים - קבוצה לפי שנים בלבד

כעת יש לנו טבלת צירים המקבצת מכירות לפי צבע ושנה:

שולחן ציר דו כיווני - מכירות לפי צבע ושנה

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

להלן חלונית השדות בשלב זה:

חלונית שדות שולחן ציר - מכירות לפי צבע ולפי שנה

ציר דו כיווני

טבלאות ציר יכולות לשרטט נתונים בסידורים דו-ממדיים שונים.

1. גרור את שדה התאריך מחוץ לאזור העמודות

2. גרור אזור לאזור עמודות.

Excel בונה טבלת ציר דו כיוונית המחלקת את המכירות לפי צבע ואזור:

שולחן ציר דו כיווני - מכירות לפי צבע ואזור

3. החלף אזור וצבע (כלומר גרור אזור לאזור השורות וצבע לאזור עמודות).

Excel בונה טבלת ציר דו-ממדית נוספת:

שולחן ציר דו כיווני - מכירות לפי אזור וצבע

שוב שים לב שסך המכירות (26,356 $) זהה כל טבלאות הציר למעלה . כל שולחן מציג מבט שונה על אותם נתונים , כך שכולם מסתכמים ב- אותו סך הכל .

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

יתרונות טבלת הצירים המרכזיים

פַּשְׁטוּת . טבלאות ציר בסיסיות מאוד פשוטות להתקנה והתאמה אישית. אין צורך ללמוד נוסחאות מסובכות.

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

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

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

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

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

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

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

משאבי טבלת ציר נוספים

מחבר דייב בראנס קבצים מצורפים קוֹבֶץ נתוני לדוגמא עבור pivot table.xlsx


^