צפה בוידאו - הדרך המהירה ביותר לביטול סיבוב נתונים ב- Excel
טבלאות ציר נהדרות כאשר רוצים לנתח כמות עצומה של נתונים תוך שניות. זה גם מאפשר לך ליצור תצוגות שונות של נתונים במהירות על ידי גרירה ושחרור.
וכדי ליצור טבלת ציר, עליך לקבל את הנתונים בפורמט ספציפי של טבלת צירים.
במקרים רבים, סביר שתקבל את הנתונים בפורמטים שאינם מוכנים לטבלת ציר.
לעתים קרובות זה קורה כאשר מישהו אוסף נתונים באופן ידני ויוצר פורמט שקריא יותר על ידי בני אדם (לא טבלאות ציר).
משהו כפי שמוצג להלן:
פורמט הנתונים לעיל הוא משהו שאתה מצפה לקבל כתוצאה של ניתוח טבלת צירים.
עכשיו, מה אם אתה רוצה לנתח את אותם נתונים ולראות מה היו סך המכירות לפי כל אזור או לפי כל חודש.
למרות שניתן בקלות לעשות זאת באמצעות טבלאות ציר, אך למרבה הצער, לא ניתן להזין את הנתונים לעיל לטבלת ציר.
אז אתה צריך לבטל את הסיבוב של הנתונים ולהפוך אותם לידידותיים ב- Pivot Table.
למרות שיש כמה דרכים לעשות זאת באמצעות נוסחת Excel או VBA, Power Query (Get & Transform in Excel 2016) הוא הכלי הטוב ביותר לביטול סיבוב הנתונים.
בטל נתונים באמצעות Power Query
להלן השלבים לביטול סיבוב הנתונים באמצעות Power Query:
(אם הנתונים שלך כבר נמצאים בטבלת Excel, התחל משלב 6 ואילך)
- בחר תא כלשהו במערך הנתונים.
- עבור לכרטיסייה הוספה.
- לחץ על סמל הטבלה.
- בתיבת הדו -שיח 'צור טבלה', ודא שהטווח נכון. ניתן לשנות את הטווח במידת הצורך.
- לחץ על אישור. פעולה זו תמיר את הנתונים הטבליים שלך לטבלת Excel.
- כאשר כל תא נבחר בטבלת Excel, לחץ על הכרטיסייה נתונים.
- בקבוצת הנתונים Get & Transform, לחץ על הסמל 'מתוך טבלה/טווח'.
- בתיבת הדו -שיח צור טבלה שנפתחת (אם היא נפתחת), לחץ על אישור. פעולה זו תפתח את עורך השאילתות באמצעות נתוני טבלת Excel.
- בעורך השאילתות, לחץ באמצעות לחצן העכבר הימני על העמודה אזור.
- לחץ על האפשרות 'הסר עמודות אחרות'. פעולה זו תבטל באופן מיידי את סיבוב הנתונים שלך.
- שנה את שם העמודה 'תכונה' לשם בעל משמעות יותר, כגון 'חודשים'.
- ברגע שיש לך את הנתונים ללא סיבוב, נוהג טוב לוודא שסוגי הנתונים נכונים. בדוגמה זו, לחץ על תא אחד לכל עמודה וראה את סוג הנתונים בכרטיסייה טרנספורמציה. במידת הצורך, תוכל גם לשנות את סוג הנתונים.
- (אופציונלי) שנה את שם השאילתה שלך ל'מכירות '.
- עבור לכרטיסייה דף הבית (בעורך השאילתות).
- לחץ על סגור וטען.
השלבים שלעיל יבטלו את סיבוב מערך הנתונים שלך באמצעות Power Query ויוחזרו ל- Excel כטבלה בגליון עבודה חדש.
כעת תוכל להשתמש בנתונים אלה כדי ליצור תצוגות שונות באמצעות טבלת ציר. לדוגמה, תוכל לבדוק את ערך המכירה הכולל לפי חודש או לפי אזור.
רענון השאילתה בעת הוספת נתונים חדשים
כל זה עובד בסדר.
אבל מה קורה כאשר נתונים חדשים מתווספים למערך הנתונים המקורי שלנו.
נניח שאתה מקבל נתונים ליולי שהם בפורמט זהה לזה שבו התחלנו.
האם עלי לחזור על כל השלבים שוב כדי לכלול נתונים אלה במערך הנתונים הבלתי ציר שלי?
התשובה היא לא.
וזה מה שכל כך מדהים ב- Power Query. תוכל להמשיך להוסיף נתונים חדשים (או לשנות נתונים קיימים), ו- Power Query תעדכן אותם באופן מיידי ברגע שתרענן אותם.
תן לי להראות לך כיצד.
נניח להלן מערך הנתונים החדש שאני מקבל (הכולל נתונים נוספים ליולי):
להלן השלבים לרענון השאילתה שכבר נוצרה וביטול סיבוב הנתונים:
- הוסף נתונים חדשים אלה לנתונים המקוריים שבהם השתמשת כדי ליצור את השאילתה.
- מכיוון שאתה מוסיף נתונים לעמודה הסמוכה בטבלת Excel, טבלת Excel תתרחב ותכלול בתוכה נתונים אלה. אם זה לא במקרה, עשה זאת באופן ידני על ידי גרירת סמל ה- L הפוך הקטן בפינה השמאלית התחתונה של טבלת Excel.
- עבור לכרטיסייה נתונים ולחץ על שאילתות וחיבורים. זה יציג חלונית עם כל השאילתות הקיימות.
- לחץ לחיצה ימנית על שאילתת המכירות בחלונית שאילתות.
- לחץ על רענן.
זהו זה! הנתונים החדשים שלך מנותקים באופן מיידי ומתווספים לנתונים הקיימים.
תבחין שמספר השורות המוצגות בעדכון השאילתה מעדכן את המספרים החדשים. בדוגמה זו, זה היה 24 לפני הרענון והפך ל- 28 לאחר הרענון.
פירוש הדבר גם שאם יצרת טבלאות ציר כלשהן באמצעות הנתונים שקיבלת מ- Power Query, טבלאות ציר אלה גם ירעננו כדי להראות לך את התוצאות המעודכנות.
