אחד הדברים שאני הרשימה ביותר על PowerPivot עבור Excel היא היכולת להוסיף טבלאות בדיקה ערכות הנתונים שלך. רוב הזמן, הנתונים אתה עובד עם אין כל שדה שאתה צריך לניתוח שלך. לדוגמה, ייתכן שיהיה שדה תאריך אך עליך לקבץ את הנתונים שלך לפי רבעון. אתה יכול לכתוב נוסחה, אבל זה קל יותר ליצור טבלת בדיקה פשוטה בסביבת PowerPivot.
ניתן גם להשתמש בטבלת בדיקה זו עבור קיבוץ נוסף כגון שם חודש ומחצית ראשונה / שנייה של השנה. במונחים של אחסון נתונים, אתה בעצם יוצר טבלת ממדי תאריך. במאמר זה, אני הולך לתת לך כמה שולחנות טבלה למשל כדי לשפר את PowerPivot עבור פרויקט Excel.
מאפיין טקסט חדש (Lookup) טבלה
הבה נבחן טבלה עם נתוני הזמנה. נניח שלטבלה יש שדות עבור הלקוח, תאריך ההזמנה, סדר ההזמנה וסוג ההזמנה. אנחנו הולכים להתמקד בתחום סוג ההזמנה. נניח ששדה סוג ההזמנה כולל ערכים כגון:
- מחשבים ניידים
- מחשבים שולחניים
- מוניטורים
- מקרנים
- מדפסות
- סורקים
- מצלמות דיגיטליות
- מצלמות דיגיטליות SLR
- מצלמות קולנוע
- מצלמות וידיאו
- טלפונים
- טלפונים חכמים
- מחשבי כף יד
- פלאפון אביזרים
במציאות, היו לך קודים אלה אבל כדי לשמור על זה פשוט, נניח אלה הם הערכים בפועל בטבלה סדר. באמצעות PowerPivot עבור Excel, תוכל בקלות לקבץ את ההזמנות שלך לפי סוג ההזמנה.
מה אם אתה רוצה קיבוץ שונה? לדוגמה, נניח שאתה צריך קיבוץ קטגוריה כמו מחשבים, מצלמות וטלפונים. לטבלת ההזמנה אין שדה קטגוריה, אך ניתן ליצור אותו בקלות כשולחן בדיקה ב- PowerPivot עבור Excel. טבלת הדגימה המלאה נמצאת למטה שולחן 1 .
הנה השלבים הבאים:
- שלב 1: אתה צריך רשימה ברורה משדה סוג עבור שולחן החיפוש שלך. זה יהיה שדה החיפוש שלך. ממערכת הנתונים שלך, צור רשימת ערכים ברורה משדה סוג ההזמנה. הזן את רשימת הסוגים המובחרת לחוברת עבודה של Excel. תייג את העמודה הקלד .
- שלב 2: בעמודה שליד עמודת החיפוש (סוג), הוסף את השדה החדש שאליו ברצונך לקבץ. בדוגמה שלנו, הוסף עמודה עם תווית שנקראת קטגוריה .
- שלב 3: עבור כל ערך ברשימת הערכים הייחודית שלך (סוגים בדוגמה זו), הוסף את המקביל קטגוריה ערכים. בדוגמה הפשוטה שלנו, הזן אחת מהן מחשבים , מצלמות או טלפונים לתוך ה קטגוריה טור.
- שלב 4: העתק את הקלד ו קטגוריה טבלת נתונים ללוח שלך.
- שלב 5: פתח את חוברת העבודה של Excel עם נתוני ההזמנה ב- PowerPivot עבור Excel. הפעל את חלון PowerPivot. לחץ על הדבק שיביא לטבלת החיפוש החדשה שלך. תן לשולחן שם וודא שאתה בודק השתמש בשורה הראשונה ככותרות עמודות. לחץ על בסדר. יצרת שולחן בדיקה ב- PowerPivot.
- שלב 6: יצירת מערכת יחסים בין הקלד שדה ב להזמין השולחן קטגוריה שדה בשולחן החיפוש. הקלק על ה עיצוב סרט ובחר צור קשר. בצע את הבחירות ב צור קשר שיח ולחץ על צור.
בעת יצירת PivotTable ב- Excel בהתבסס על נתוני PowerPivot, תוכל לקבץ לפי החדש שלך קטגוריה שדה. זכור כי PowerPivot עבור Excel תומך רק Inner Joins. אם יש לך סוג הזמנה חסר בטבלת החיפוש שלך, כל הרשומות המתאימות עבור סוג זה יהיו חסרים מכל PivotTable מבוסס על נתוני PowerPivot. יהיה עליך לבדוק זאת מעת לעת.
מאפיין תאריך (Lookup) טבלה
סביר להניח שיהיה צורך בטבלת בדיקת תאריך ברוב הפרוייקטים של PowerPivot עבור Excel. לרוב ערכות הנתונים יש סוג כלשהו של שדות תאריך. ישנן פונקציות לחישוב השנה והחודש.
עם זאת, אם אתה צריך את הטקסט בחודש בפועל או ברבעון, אתה צריך לכתוב נוסחה מורכבת. קל יותר לכלול טבלה של תאריך תאריכים (חיפוש) ולהתאים אותה עם מספר החודש במערך הנתונים הראשי שלך. יהיה עליך להוסיף עמודה לטבלת ההזמנה כדי לייצג את מספר החודש משדה תאריך ההזמנה. נוסחת DAX עבור חודש בדוגמה שלנו היא חודש MONTH (תאריך הזמנה). פעולה זו תחזיר מספר בין 1 ל -12 עבור כל רשומה. טבלת המאפיינים שלנו תספק ערכים חלופיים, המקשרים למספר החודש. זה יספק לך גמישות בניתוח שלך. הטבלה המלאה של מאפיין תאריך המדגם נמצאת למטה שולחן 2 .
מאפיין התאריך או טבלת החיפוש יכלול 12 רשומות. בעמודה החודש יהיו הערכים 1 - 12. עמודות אחרות יכללו טקסט חודש מקוצר, טקסט חודש מלא, רבעון וכו '. להלן השלבים הבאים:
- שלב 1: העתק את הטבלה מטבלה 2 להלן והדבק לתוך PowerPivot. אתה יכול ליצור את הטבלה ב- Excel אבל אני חוסך לך זמן. אתה אמור להיות מסוגל להדביק ישירות מתוך הנתונים שנבחרו להלן אם אתה משתמש ב- Internet Explorer או Edge. PowerPivot בוחרת את עיצוב הטבלה בבדיקה שלי. אם אתה משתמש בדפדפן אחר, ייתכן שיהיה עליך להדביק לתוך Excel תחילה ולהעתיק אותו מ- Excel כדי להרים את עיצוב השולחן.
- שלב 2: פתח את חוברת העבודה של Excel עם נתוני ההזמנה ב- PowerPivot עבור Excel. הפעל את חלון PowerPivot. לחץ על הדבק אשר יביא טבלת החיפוש שלך מועתקים מהטבלה למטה או מ- Excel. תן לשולחן שם וודא שאתה בודק השתמש בשורה הראשונה ככותרות עמודות. לחץ על בסדר. יצרת טבלת בדיקת תאריך ב- PowerPivot.
- שלב 3ליצור קשר בין חודש שדה ב להזמין השולחן חודש שדה בשולחן החיפוש. הקלק על ה עיצוב סרט ובחר צור קשר. בצע את הבחירות ב צור קשר שיח ולחץ על צור.
שוב, עם הוספת מאפיין תאריכים, תוכל לקבץ את הנתונים ב- PivotTable שלך באמצעות כל אחד מהערכים השונים מטבלת בדיקת התאריכים. קיבוץ לפי רבעון או שם החודש יהיה הצמד.
מימד לדוגמא (טבלה) טבלאות
שולחן 1
| הקלד | קטגוריה |
| מחשבים ניידים | מחשב |
| מחשבים שולחניים | מחשב |
| מוניטורים | מחשב |
| מקרנים ומסכים | מחשב |
| מדפסות, סורקים ופקסים | מחשב |
| הגדרת המחשב ושירות | מחשב |
| אביזרי מחשב | מחשב |
| מצלמות דיגיטליות | מצלמה |
| מצלמות דיגיטליות SLR | מצלמה |
| מצלמות קולנוע | מצלמה |
| מצלמות וידיאו | מצלמה |
| מצלמות וידיאו ואביזרים | מצלמה |
| טלפונים לבית ולמשרד | מכשיר טלפון |
| מסך מגע טלפונים | מכשיר טלפון |
| טלפונים חכמים & מחשבי כף יד | מכשיר טלפון |
שולחן 2
| חודש | חודש | חודש | רובע | סמסטר |
| 1 | יאן | ינואר | Q1 | H1 |
| 2 | פברואר | פברואר | Q1 | H1 |
| 3 | מרץ | מרץ | Q1 | H1 |
| 4 | Apr | אפריל | Q2 | H1 |
| 5 | מאי | מאי | Q2 | H1 |
| 6 | יוני | יוני | Q2 | H1 |
| 7 | יול | יולי | ש 3 | H2 |
| 8 | אוגוסט | אוגוסט | ש 3 | H2 |
| 9 | ספטמבר | ספטמבר | ש 3 | H2 |
| 10 | אוקטובר | אוקטובר | ש 4 | H2 |
| 11 | נובמבר | נובמבר | ש 4 | H2 |
| 12 | דצמבר | דצמבר | ש 4 | H2 |
