ערכת לימוד: ניתוח נתוני PivotTable באמצעות מודל נתונים ב- Excel 2013

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

בוא נתחיל בייבוא נתונים מסוימים.

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

  2. ב- Excel, פתח חוברת עבודה ריקה.

  3. לחץ על נתונים > קבל נתונים חיצוניים > מ- Access.

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

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

    תיבת הדו-שיח 'בחירת טבלה'

  6. ב'בחירת טבלה', סמן הפוך בחירה של טבלאות מרובות לזמינה.

  7. בחר את כל הטבלאות ולחץ על אישור.

  8. ב'ייבוא נתונים', לחץ על דוח PivotTable ולחץ על אישור.

    הערות: 

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

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

סקירת נתונים באמצעות PivotTable

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

  1. ברשימת השדות, גלול למטה עד שתמצא את הטבלה FactSales.

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

  3. ב- DimDate, גרור את CalendarYear ל'עמודות'.

  4. ב- DimProductSubcategory, גרור את ProductSubcategoryName ל'שורות'.

  5. בתיבה DimProduct, גרור את BrandName ל'שורות', והצב אותו מתחת לקטגוריית משנה.

ה- PivotTable שלך אמור להיות דומה למסך הבא.

PivotTable המציג נתונים לדוגמה

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

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

הוספת טבלאות נוספות

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

הוספת קטגוריות מוצרים

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

  2. לחץ על נתונים > קבל נתונים חיצוניים > מ- Access.

  3. עבור אל התיקיה המכילה את קבצי הנתונים לדוגמה ובחר ProductCategories. לחץ על פתח.

  4. בתיבה 'ייבוא נתונים', בחר טבלה ולחץ על אישור.

הוספת נתוני מיקום גיאוגרפי

  1. הוסף גיליון אחר.

  2. מקבצי הנתונים לדוגמה, פתח את Geography.xlsx, מקם את הסמן בתא A1 ולאחר מכן הקש Ctrl-Shift-End כדי לבחור את כל הנתונים.

  3. העתק את הנתונים ללוח.

  4. הדבק את הנתונים בתוך הגיליון הריק שהוספת זה עתה.

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

  6. ב'עצב כטבלה', ודא שהאפשרות לטבלה שלי יש כותרות נבחרה. לחץ על אישור.

  7. הענק לטבלה את השם Geography. בכלי טבלאות > עיצוב, הקלד Geography ב'שם הטבלה'.

  8. סגור את Geography.xlsx כדי למחוק את הקובץ מסביבת העבודה שלך.

הוספת נתוני חנות

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

כעת אמורים להיות לך ארבעה גליונות. גיליון1 מכיל את PivotTable, גיליון2 מכיל את ProductCategories, גיליון3 מכיל את Geography וגליון4 מכיל את Stores. מאחר שהקדשת זמן כדי להעניק שם לכל טבלה, השלב הבא, יצירת קשרי גומלין, יהיה פשוט יותר.

שימוש בשדות מהטבלאות המיובאות החדשות

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

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

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

  3. הרחב את Stores.

  4. גרור את StoreName אל האזור 'מסננים'.

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

  6. לחץ על צור כדי לפתוח את תיבת הדו-שיח 'יצירת קשר גומלין'.

  7. ב'טבלה', בחר FactSales. בנתונים לדוגמה שבהם אתה משתמש, FactSales מכיל מידע מכירות ועלויות מפורט אודות העסקים של Contoso וכן מפתחות לטבלאות אחרות, לרבות קודי חנויות שקיימים גם בקובץ Stores.xlsx שייבאת בשלב הקודם.

  8. ב'עמודה (חיצונית)', בחר StoreKey.

  9. ב'טבלה קשורה', בחר Stores.

  10. ב'עמודה קשורה (ראשית)', בחר StoreKey.

  11. לחץ על אישור.

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

הוספת קשרי גומלין

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

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

הדפדפן אינו תומך בווידאו. התקן את Microsoft Silverlight,‏ Adobe Flash Player או Internet Explorer 9.

קישור ProductSubcategory ל- ProductCategory

  1. ב- Excel, לחץ על נתונים > קשרי גומלין > חדש.

  2. ב'טבלה', בחר DimProductSubcategory.

  3. ב'עמודה (חיצונית)', בחר ProductCategoryKey.

  4. ב'טבלה קשורה', בחר Table_ProductCategory.accdb.

  5. ב'עמודה קשורה (ראשית)', בחר ProductCategoryKey.

  6. לחץ על אישור.

  7. סגור את תיבת הדו-שיח ניהול קשרי גומלין.

הוספת קטגוריות ל- PivotTable

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

  1. ב'שדות PivotTable', לחץ על הכל כדי להציג טבלאות שקיימות במודל הנתונים.

  2. גלול לתחתית הרשימה.

  3. באזור 'שורות', הסר את BrandName.

  4. הרחב את Table_DimProductCategories.accdb.

  5. גרור את ProductCategoryName אל האזור 'שורות' והצב אותו מעל ProductSubcategory.

  6. ב'שדות PivotTable', לחץ על פעיל כדי לוודא שהטבלאות שהשתמשת בהן נמצאות כעת בשימוש פעיל ב- PivotTable.

נקודת ביקורת: סקירת מה שלמדת

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

למרות שה- PivotTable שימושי, ודאי שמת לב שיש כמה דברים שניתן לשפר. נראה שרשימת שדות PivotTable כוללת טבלאות מיותרות (DimEntity) ועמודות מיותרות (ETLLoadID) שאינן קשורות לעסק של Contoso. בנוסף, עדיין לא שילבנו את נתוני Geography.

הנושא הבא: הצגה והרחבה של המודל שלך באמצעות Power Pivot

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

הערה:  התוספת Power Pivot ב- Microsoft Excel 2013 זמינה ב- Office Professional Plus. ראה התוספת Power Pivot ב- Microsoft Excel 2013 לקבלת מידע נוסף.

הוסף את Power Pivot לרצועת הכלים של Excel על-ידי הפיכת התוספת Power Pivot לזמינה.

  1. עבור אל קובץ >‏ אפשרויות >‏ תוספות.

  2. בתיבה נהל, לחץ על תוספות COM> עבור אל.

  1. סמן את התיבה Microsoft Office Power Pivot ב- Microsoft Excel 2013 ולאחר מכן לחץ על אישור.

רצועת הכלים כוללת כעת את הכרטיסיה Power Pivot.

הוספת קשר גומלין באמצעות תצוגת הדיאגרמה ב- Power Pivot

  1. ב- Excel, לחץ על גיליון3 כדי להפוך אותו לגיליון הפעיל. גיליון3 מכיל את הטבלה Geography שייבאת קודם לכן.

  2. ברצועת הכלים, לחץ על Power Pivot > הוסף למודל הנתונים. שלב זה מוסיף את הטבלה Geography למודל. הוא גם פותח את התוספת Power Pivot, שבה תשתמש כדי לבצע את השלבים הנותרים במשימה זו.

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

  4. בחלון Power Pivot, במקטע 'תצוגה', לחץ על תצוגת דיאגרמה.

  5. השתמש בסרגל המחוון כדי לשנות את גודל הדיאגרמה כדי שתוכל לראות כל האובייקטים בדיאגרמה. שים לב ששתי טבלאות אינן קשורות לשאר הדיאגרמה: DimEntity ו- Geography.

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

  7. הגדל את התצוגה ב- Geography כדי להציג את כל השדות של הטבלה. באפשרותך להשתמש במחוון כדי להגדיל את דיאגרמת הטבלה.

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

  9. לחץ על חיפוש.

  10. ב'חיפוש מטה-נתונים', הקלד GeographyKey.

  11. לחץ על חפש את הבא כמה פעמים. תבחין בכך שהעמודה GeographyKey מופיעה בטבלה Geography ובטבלה Stores.

  12. מקם מחדש את הטבלה Geography כך שתהיה סמוכה לטבלה Stores.

  13. גרור את העמודה GeographyKey בטבלה Stores אל העמודה GeographyKey בטבלה Geography. Power Pivot מותח קו בין שתי העמודות ומציין את קשר הגומלין ביניהן.

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

עצה:  ב'תצוגת דיאגרמה', חלק מהטבלאות מורחבות לגמרי ומציגות עמודות כגון ETLLoadID‏, LoadDate ו- UpdateDate. שדות מסוימים אלה הם ממצאים ממחסן הנתונים המקורי של Contoso, אשר התווספו כדי לתמוך בפעולות חילוץ נתונים וטעינה. אינך זקוק להם במודל שלך. כדי להיפטר מהם, סמן ולחץ באמצעות לחצן העכבר הימני על השדה ולאחר מכן לחץ על מחק .

יצירת עמודה מחושבת

ב- Power Pivot, באפשרותך להשתמש ב- Data Analysis Expressions‏ (DAX) כדי להוסיף חישובים. במשימה זו, תחשב רווח כולל, ותוסיף עמודה מחושבת שמפנה לערכי נתונים מטבלאות אחרות. בהמשך תראה כיצד להשתמש בעמודות שיש הפניה אליהן כדי לפשט את המודל שלך.

  1. בחלון Power Pivot, חזור לתצוגת הנתונים.

  2. שנה את שם הטבלה Table_ProductCategories accdb לשם ידידותי יותר. אתה תפנה אל טבלה זו בשלבים הבאים ושם קצר יותר יהפוך את החישובים לקלים יותר לקריאה. לחץ באמצעות לחצן העכבר הימני על שם הטבלה, לחץ על שנה שם, הקלד ProductCategories, ולאחר מכן הקש ENTER.

  3. בחר את הטבלה FactSales.

  4. לחץ על עיצוב > עמודות > הוסף.

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

    ‎= [SalesAmount] - [TotalCost] - [ReturnAmount]‎

  6. לאחר שסיימת ליצור את הנוסחה, הקש Enter כדי לקבל את הנוסחה.

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

  7. שנה את שם העמודה על-ידי לחיצה באמצעות לחצן העכבר הימני על CalculatedColumn1 ובחירת שנה שם עמודה. הקלד Profit והקש Enter.

  8. כעת בחר את הטבלה DimProduct.

  9. לחץ על עיצוב > עמודות > הוסף.

  10. בשורת הנוסחאות מעל הטבלה, הקלד את הנוסחה הבאה.

    ‎= RELATED(ProductCategories[ProductCategoryName])‎

    הפונקציה RELATED מחזירה ערך מטבלה קשורה. במקרה זה, הטבלה ProductCategories כוללת את שמות קטגוריות המוצרים, שיהיו שימושיים בטבלה DimProduct כאשר תבנה הירארכיה שכוללת מידע קטגוריה. לקבלת מידע נוסף אודות פונקציה זו, ראה הפונקציה RELATED‏‏ ‎(DAX).

  11. לאחר שסיימת ליצור את הנוסחה, הקש Enter כדי לקבל את הנוסחה.

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

  12. שנה את שם העמודה על-ידי לחיצה באמצעות לחצן העכבר הימני על CalculatedColumn1 ובחירת שנה שם עמודה. הקלד ProductCategory ולאחר מכן הקש Enter.

  13. לחץ על עיצוב > עמודות > הוסף.

  14. בשורת הנוסחאות מעל הטבלה, הקלד את הנוסחה הבאה ולאחר מכן הקש Enter כדי לקבל את הנוסחה.

    ‎= RELATED(DimProductSubcategory[ProductSubcategoryName])‎

  15. שנה את שם העמודה על-ידי לחיצה באמצעות לחצן העכבר הימני על CalculatedColumn1 ובחירת שנה שם עמודה. הקלד ProductSubcategory ולאחר מכן הקש Enter.

יצירת הירארכיה

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

  1. ב- Power Pivot, עבור לתצוגת דיאגרמה. הרחב את הטבלה DimDate כדי שתוכל לראות את כל השדות שלה בקלות רבה יותר.

  2. החזק את מקש Ctrl לחוץ ולחץ על העמודות CalendarYear‏, CalendarQuarter ו- CalendarMonth (יהיה עליך לגלול למטה בטבלה).

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

  4. הקלד Dates כשם עבור ההירארכיה החדשה שלך.

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

  6. בתצוגת הדיאגרמה, הצבע על הטבלה DimProduct ולחץ על לחצן צור הירארכיה בכותרת הטבלה. צומת הירארכיית אב ריק מופיע בתחתית הטבלה.

  7. הקלד Product Categories כשם ההירארכיה החדשה שלך.

  8. כדי ליצור צמתי הירארכיית צאצא, גרור את ProductCategory ואת ProductSubcategory אל ההירארכיה.

  9. לחץ באמצעות לחצן העכבר הימני על ProductName ובחר הוסף להירארכיה. בחר קטגוריות מוצרים.

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

  1. חזור ל- Excel.

  2. בגיליון1 (הגיליון המכיל את PivotTable), הסר את השדות באזור 'שורות'.

  3. החלף אותם בהירארכיית קטגוריות מוצרים החדשה ב- DimProduct.

  4. באופן דומה, החלף את CalendarYear באזור 'עמודות' עם הירארכיית Dates ב- DimDate.

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

הסתרת עמודות

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

הדפדפן אינו תומך בווידאו. התקן את Microsoft Silverlight,‏ Adobe Flash Player או Internet Explorer 9.

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

  1. ב- Power Pivot, ודא שתצוגת נתונים נבחרה.

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

  3. חזור על תהליך זה עבור ProductCategories.

  4. פתח את DimProduct.

  5. לחץ באמצעות לחצן העכבר הימני על העמודות הבאות ולחץ על הסתר מכלי לקוח:

    • ProductKey

    • ProductLabel

    • ProductSubcategory

  6. בחר בחירה מרובה בעמודות סמוכות. התחל עם ClassID והמשך אל ProductSubcategory בסוף. לחץ באמצעות לחצן העכבר הימני כדי להסתיר אותן.

  7. חזור על תהליך זה עבור טבלאות אחרות, והסר מזהים, מפתחות או פרטים אחרים שלא תשתמש בהם בדוח.

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

יצירת דוח Power View

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

  1. ב- Excel, לחץ על הוסף >‏ Power View.

    הערה:  אם זו הפעם הראשונה שבה אתה משתמש ב- Power View במחשב זה, תתבקש להפוך את התוספת לזמינה ולהתקין תחילה את Silverlight.

  2. ב'שדות Power View', לחץ על החץ לצד הטבלה FactSales ולחץ על SalesAmount.

  3. הרחב את הטבלה Geography ולחץ על RegionCountryName.

  4. ברצועת הכלים, לחץ על מפה.

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

מיטוב לצורך דיווח Power View

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

בשלב הראשון, הוסף כתובות URL לחוברת העבודה שלך.

  1. ב- Excel, פתח גיליון חדש והעתק ערכים אלה:

ManufacturerURL

ManufacturerID

http://www.contoso.com

קונטוסו בע"מ

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

פבריקם בע"מ

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

  2. צור קשר גומלין בין URL ובין הטבלה שמכילה את שמות היצרנים, DimProduct:

    1. לחץ על נתונים > קשרי גומלין. תיבת הדו-שיח 'יצירת קשר גומלין' מופיעה.

    2. לחץ על חדש.

    3. ב'טבלה', בחר DimProduct.

    4. ב'עמודה', בחר Manufacturer.

    5. ב'טבלה קשורה', בחר URL.

    6. ב'עמודה קשורה (ראשית)', בחר ManufacturerID.

כדי להשוות בין התוצאות לפני ואחרי, התחל דוח Power View חדש והוסף את FactSales | SalesAmount‏, dimProduct | Manufacturer ו- URL | ManufacturerURL לדוח. שים לב שכתובות ה- URL מוצגות כטקסט סטטי.

עיבוד כתובת URL כהיפר-קישור פעיל דורש סיווג לקטגוריות. כדי לסווג עמודה, יהיה עליך להשתמש ב- Power Pivot.

  1. ב- Power Pivot, פתח את כתובת URL.

  2. בחר ManufacturerURL.

  3. לחץ על מתקדם > מאפייני דיווח> קטגוריית נתונים: לא סווגו לקטגוריות.

  4. לחץ על החץ למטה.

  5. בחר כתובת URL של אתר אינטרנט.

  6. ב- Excel, לחץ על הוסף >‏ Power View.

  7. ב'שדות Power View', בחר FactSales | SalesAmount‏, dimProduct | Manufacturer ו- URL | ManufacturerURL. הפעם, כתובות ה- URL מופיעות כהיפר-קישורים ממשיים.

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

יצירת שדות מחושבים

במשימה השניה, סקירת נתונים באמצעות PivotTable, לחצת על השדה SalesAmount ברשימת שדות PivotTable. מאחר ש- SalesAmount הוא עמודה מספרית, היא הוצבה באופן אוטומטי באזור 'ערכים' של ה- PivotTable. הסכום של SalesAmount היה מוכן לחישוב סכומי מכירות עבור כל מסנן שהוחל. במקרה זה, אין מסננים בתחילה, אבל לאחר מכן מופיעים CalendarYear‏, ProductSubcategoryName ו- BrandName.

מה שבאמת עשית היה יצירת שדה מחושב משתמעים, והפכת את ניתוח סכומי המכירות מהטבלה FactSales מול שדות אחרים, כגון קטגוריית מוצרים, אזור ותאריכים, לפשוט יותר. שדות מחושבים משתמעים נוצרים על-ידי Excel כאשר אתה גורר שדה לאזור 'ערכים' או בעת לחיצה על שדה מספרי, כפי שעשית עם SalesAmount. שדות מחושבים משתמעים הם נוסחאות המשתמשות בפונקציות מצבור רגילות, כגון SUM‏, COUNT ו- AVERAGE, הנוצרות באופן אוטומטי עבורך.

קיימים גם סוגים אחרים של שדות מחושבים. באפשרותך ליצור שדות מחושבים מפורשים ב- Power Pivot. בשונה משדה מחושב משתמע, אשר יכול לשמש רק ב- PivotTable שבו נוצר, שדות מחושבים מפורשים ניתנים לשימוש בכל PivotTable בחוברת העבודה, או על-ידי כל דוח המשתמש במודל הנתונים כמקור נתונים. באמצעות שדות מחושבים מפורשים, שנוצרים ב- Power Pivot, תוכל להשתמש ב'סכום אוטומטי' כדי ליצור באופן אוטומטי שדות מחושבים באמצעות צבירות רגילות או ליצור צבירות משלך בעזרת נוסחה שנוצרה באמצעות Data Analysis Expressions (DAX)‎.

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

יצירת שדות מחושבים ב- Power Pivot היא פשוטה כאשר אתה משתמש בסכום אוטומטי.

  1. בטבלה FactSales, לחץ על העמודה Profit.

  2. לחץ על חישובים > סכום אוטומטי. שים לב ששדה מחושב חדש שנקרא Sum of Profit נוצר באופן אוטומטי בתא ב'אזור חישוב' ישירות מתחת לעמודה Profit.

  3. ב- Excel, בגיליון1, ברשימת השדות, ב- FactSales, לחץ על Sum of Profit.

זהו! זה כל מה שנדרש כדי ליצור שדה מחושב באמצעות צבירה רגילה ב- Power Pivot. כפי שתוכל לראות, בכמה דקות ספורות יצרת שדה מחושב של SUM of Profit והוספת אותו ל- PivotTable ובכך אפשרת לנתח בקלות רווחים בהתאם למסננים שהוחלו. במקרה זה, תראה את השדה Sum of Profit מסונן לפי ההירארכיות 'קטגוריות מוצר' ו'תאריכים'.

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

  1. בטבלה FactSales, לחץ על העמודה SalesKey.

  2. בחישובים, לחץ על החץ למטה בסכום אוטומטי > ספירה.

  3. שנה את שם השדה המחושב החדש על-ידי לחיצה באמצעות לחצן העכבר הימני על Count of SalesKey באזור החישוב ולאחר מכן בחירה באפשרות שנה שם. הקלד Count, ולאחר מכן הקש Enter.

  4. ב- Excel, בגיליון1, ברשימת השדות, ב- FactSales, לחץ על Count.

שים לב שעמודה חדשה, Count נוספה ל- PivotTable ומציגה את מספרי המכירות בהתאם למסננים שהוחלו. בדיוק כמו השדה המחושב Sum of Profit, אתה רואה את השדה Count מסונן לפי ההירארכיות 'קטגוריית מוצר' ו'תאריכים'.

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

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

  2. בשורת הנוסחאות, הקלד והשתמש ב- IntelliSense כדי ליצור את הנוסחה הבאה: Percentage of All Products:=[Count]/CALCULATE([Count], ALL(DimProduct))

  3. הקש Enter כדי לקבל את הנוסחה.

  4. ב- Excel, בגיליון1, ברשימת השדות, ב- FactSales, לחץ על Percentage of All Products.

  5. ב- PivotTable, בחר בחירה מרובה את העמודות Percentage of All Products.

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

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

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

שמירת עבודתך

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

השלבים הבאים

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

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

נסה לשפר את מודל הנתונים שלך כדי לקבל דוחות Power View טובים יותר על-ידי ביצוע השלבים בערכת לימוד: מיטוב מודל הנתונים שלך עבור דיווח Power View

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×