יצירת מודל נתונים ב- Excel

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

כדי שתוכל להתחיל לעבוד עם מודל הנתונים, עליך לקבל נתונים מסוימים. לשם כך נשתמש בחוויה קבל את & Transform (Power Query), כך שייתכן שתרצה לקחת צעד אחורה ולצפות בסרטון וידאו, או פעל בהתאם למדריך הלמידה שלנו בנושא קבל את & Transform ו- Power Pivot.

היכן נמצא Power Pivot?

Excel 2016 & Excel עבור Microsoft 365 - Power Pivot כלול ברצועת הכלים.

היכן נמצא Get & Transform (Power Query)?

Excel 2016 & Excel עבור Microsoft 365 - קבל & המרה (Power Query) משולב עם Excel בכרטיסיה נתונים.

תחילת העבודה

תחילה, עליך לקבל נתונים מסוימים.

ב- Excel 2016 וב- Excel עבור Microsoft 365, השתמש בנתונים > קבל נתוני המרה של & > קבל נתונים כדי לייבא נתונים ממספר כלשהו של מקורות נתונים חיצוניים, כגון קובץ טקסט, חוברת עבודה של Excel, אתר אינטרנט, Microsoft Access, SQL Server או מסד נתונים יחסי אחר המכיל טבלאות קשורות מרובות.
Excel מבקש ממך לבחור טבלה. אם ברצונך לקבל טבלאות מרובות מאותו מקור נתונים, סמן את האפשרות אפשר בחירה של טבלאות מרובות . בעת בחירת טבלאות מרובות, Excel יוצר עבורך מודל נתונים באופן אוטומטי.

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

אם עליך לערוך את נתוני המקור, באפשרותך לבחור את האפשרות ערוך. לקבלת פרטים נוספים, ראה: יצירה, טעינה או עריכה של שאילתה ב- Excel (Power Query).

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

הערות:

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

עצה: כיצד ניתן לדעת אם חוברת העבודה שלך כוללת מודל נתונים? עבור אל ניהולPower Pivot>. אם אתה רואה נתונים כגון גליון עבודה, קיים מודל. ראה: גלה אילו מקורות נתונים נמצאים בשימוש במודל נתונים של חוברת עבודה לקבלת מידע נוסף.

יצירת קשרי גומלין בין הטבלאות שלך

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

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

יצרנו את הקישורים הבאים:
- tbl_Students | מזהה תלמיד > tbl_Grades | מזהה תלמיד
  
  במילים אחרות, גרור את השדה מזהה סטודנט מהטבלה תלמידים לשדה מזהה סטודנט בטבלה ציונים.
- tbl_Semesters | מזהה סמסטר > tbl_Grades | הסמסטר
- tbl_Classes | מספר כיתה > tbl_Grades | מספר כיתה
הערות:
- שמות שדות אינם חייבים להיות זהים כדי ליצור קשר גומלין, אך עליהם להיות בעלי סוג נתונים זהה.
- המחברים בתצוגת הדיאגרמה כוללים "1" בצד אחד ו- "*" בצד השני. משמעות הדבר היא שקיים קשר גומלין של אחד לרבים בין הטבלאות, וקובע כיצד נעשה שימוש בנתונים בטבלאות ה- PivotTable שלך. ראה: קשרי גומלין בין טבלאות במודל נתונים לקבלת מידע נוסף.
- המחברים מציינים רק שקיים קשר גומלין בין טבלאות. הם לא באמת יציגו לך אילו שדות מקושרים זה לזה. כדי לראות את הקישורים, עבור אל Power Pivot כדי > עיצוב >קשרי גומלין > קשרי > קשרי גומלין. ב- Excel, באפשרותך לעבור אל קשרי גומלין בין > נתונים.

שימוש במודל נתונים ליצירת PivotTable או PivotChart

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

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

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

הוספת נתונים קיימים שאינם קשורים למודל נתונים

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

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

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

הוספת נתונים לטבלה Power Pivot אישית

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