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

תוכן העניינים

מהו עיצוב טוב לנתוני המקור לטבלת צירים?
מלכודות נפוצות שיש להימנע מהן בעת הכנת נתוני המקור
דוגמאות לעיצובים של נתוני מקור רע
[מקרה מקרה] המרת נתונים בפורמט גרוע לנתוני מקור מוכן של טבלת ציר

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

מהו עיצוב טוב לנתוני המקור לטבלת צירים?

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

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

השורה הראשונה מכילה כותרות המתארות את הנתונים בעמודות.
כל עמודה מייצגת קטגוריית נתונים ייחודית. לדוגמה, בעמודה C יש נתוני מוצר בלבד ועמודה D ונתוני החודש בלבד.
כל שורה היא רשומה שתייצג מופע אחד של העסקה או המכירה.
כותרות הנתונים ייחודיות ואינן חוזרות על עצמן בשום מקום במערך הנתונים. לדוגמה, אם יש לך מספרי מכירות במשך ארבעה רבעונים בשנה, אסור לך לקרוא לכל אלה כמכירות. במקום זאת, העניקו לכותרות העמודות שמות ייחודיים כגון מכירות Q1, מכירות Q2 וכן הלאה …
- אם אין לך כותרות ייחודיות, אתה עדיין יכול להמשיך ליצור טבלת ציר ו- Excel יהפוך אותן לייחודיות באופן אוטומטי על ידי הוספת סיומת (כגון Sales, Sales2, Sales3). עם זאת, זו תהיה דרך איומה להכין ולהשתמש בטבלת צירים.

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

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

דוגמאות לעיצובים של נתוני מקור רע

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

עיצוב נתוני מקור רע - דוגמה 1

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

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

עיצוב נתוני מקור רע - דוגמה 2

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

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

עיצוב נתוני מקור רע - דוגמה 3

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

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

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

[מקרה מקרה] המרת נתונים בפורמט גרוע לנתוני מקור מוכן של טבלת ציר

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

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

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

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

שיטה 1: שימוש בנוסחאות Excel

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

צור כותרת עמודה ייחודית לכל הקטגוריות במערך הנתונים המקורי. בדוגמה זו, זה יהיה אזור, רבעון ומכירות.
בתא שמתחת לכותרת האזור, השתמש בנוסחה הבאה: = INDEX ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
- גרור את הנוסחה כלפי מטה והיא תחזור על כל האזורים.
בתא שמתחת לכותרת הרבעון, השתמש בנוסחה הבאה: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1) +0.1) , 0))
- גרור את הנוסחה כלפי מטה והיא תחזור על כל הרבעים.
בכותרת מתחת למכירות, השתמש בנוסחה הבאה: = INDEX ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 ))
- גרור אותו כלפי מטה כדי לקבל את כל הערכים. נוסחה זו משתמשת בנתוני האזור והרבעון כערכי החיפוש ומחזירה את ערך המכירה ממערך הנתונים המקורי.

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

לחץ כאן להורדת קובץ הדוגמה.

שיטה 2: שימוש ב- Power Query

ל- Power Query יש תכונה שיכולה בקלות להמיר נתונים מסוג זה לפורמט נתונים מוכן ל- Pivot.

אם אתה משתמש ב- Excel 2016, תכונות Power Query יהיו זמינות בכרטיסיה נתונים בקבוצה Get & Transform. אם אתה משתמש ב- Excel 2013 או בגרסאות קודמות, תוכל להשתמש בו כתוסף.

להלן מדריך מצוין להתקנת שאילתת כוח מאת ג'ון מקמפוס Excel.

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

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

המרת הנתונים לטבלת אקסל. בחר את מערך הנתונים ועבור אל הוספה -> טבלאות -> טבלה.
בתיבת הדו -שיח הוספת טבלה, ודא שהטווח הנכון נבחר ולחץ על אישור. פעולה זו תמיר את הנתונים הטבליים לטבלת Excel.
ב- Excel 2016, עבור אל Data -> Get & Transform -> From Table.
- אם אתה משתמש בתוסף Power Query בגרסה קודמת, עבור אל Power Query -> נתונים חיצוניים -> מהטבלה.
בעורך השאילתות, בחר את העמודות שברצונך לבטל. במקרה זה, אלה הם אלה לארבעת הרבעים. כדי לבחור את כל העמודות, החזק את מקש Shift ולאחר מכן בחר בעמודה הראשונה ולאחר מכן בעמודה האחרונה.
בתוך עורך השאילתות, עבור אל טרנספורמציה -> כל עמודה -> בטל עמודות. פעולה זו תמיר את נתוני העמודה לפורמט ידידותי של טבלת צירים.
Power Query נותן שמות כלליים לעמודות. שנה את השמות האלה לזה שאתה רוצה. במקרה זה, שנה את המאפיין לרבעון ואת הערך למכירות.
בעורך השאילתות, עבור אל קובץ -> סגור וטען. פעולה זו תסגור את תיבת הדו -שיח עורך השאילתות Power ותיצור גליון עבודה נפרד שיכלול את הנתונים עם עמודות לא מסתובבות.