מודל נתונים באקסל | כיצד ליצור מודל נתונים? (עם דוגמאות)

מהו מודל הנתונים ב- Excel?

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

הֶסבֵּר

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

דוגמאות

אתה יכול להוריד תבנית Excel זו של דגם נתונים כאן - תבנית Excel של דגם נתונים

דוגמה מס '1

אם יש לנו שלושה מערכי נתונים הקשורים לאיש המכירות: הראשון המכיל מידע על הכנסות, שני המכיל הכנסות של איש מכירות, ושלישי המכיל הוצאות של איש המכירות.

כדי לחבר את שלושת מערכי הנתונים הללו וליצור קשר עם אלה, אנו יוצרים מודל נתונים עם השלבים הבאים:

• המרת מערכי הנתונים לאובייקטים בטבלה:

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

• שלב 1 - לחץ במקום כלשהו בתוך מערך הנתונים ואז לחץ על הכרטיסייה 'הוסף' ואז לחץ על 'טבלה' בקבוצה 'טבלאות'.

• שלב 2 - סמן או בטל את הסימון של האפשרות: 'לטבלה שלי יש כותרות' ולחץ על אישור.

• שלב 3 - כאשר הטבלה החדשה נבחרה, הזן את שם הטבלה ב'שם הטבלה 'בקבוצה' כלים '.

• שלב 4 - כעת אנו יכולים לראות כי מערך הנתונים הראשון מומר לאובייקט 'Table'. לאחר חזרה על שלבים אלה עבור שני מערכי הנתונים האחרים, אנו רואים שהם מומרים גם לאובייקטים של 'טבלה' כמפורט להלן:

הוספת אובייקטים 'טבלה' למודל הנתונים: באמצעות חיבורים או קשרים.

דרך חיבורים

• בחר טבלה אחת ולחץ על הכרטיסייה 'נתונים' ואז לחץ על 'חיבורים'.

• בתיבת הדו-שיח המתקבלת, יש סמל של 'הוסף'. הרחב את התפריט הנפתח של 'הוסף' ולחץ על 'הוסף למודל הנתונים'.

• לחץ על 'טבלאות' בתיבת הדו-שיח המתקבלת ואז בחר באחת מהטבלאות ולחץ על 'פתח'.

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

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

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

דרך מערכות יחסים

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

• לחץ על הכרטיסייה 'נתונים' ואז לחץ על 'יחסים'.

• נראה תיבת דו-שיח ריקה מכיוון שאין חיבורים שוטפים.

• לחץ על 'חדש' ותיבת דו-שיח נוספת מופיעה.

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

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

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

כעת Excel יוצר את הקשר מאחורי הקלעים על ידי שילוב נתונים במודל הנתונים המבוסס על עמודה נפוצה: מזהה איש מכירות (במקרה זה).

דוגמה מס '2

עכשיו, בואו נגיד בדוגמה שלעיל נרצה ליצור טבלת ציר שמעריכה או מנתחת את אובייקטי הטבלה:

• לחץ על 'הוסף' -> 'טבלת ציר'.

• בתיבת הדו-שיח המתקבלת, לחץ על האפשרות המציינת: 'השתמש במקור נתונים חיצוני' ואז לחץ על 'בחר חיבור'.

• לחץ על 'טבלאות' בתיבת הדו-שיח המתקבלת ובחר במודל הנתונים של חוברת העבודה המכיל שלוש טבלאות ולחץ על 'פתח'.

• בחר באפשרות 'גליון עבודה חדש' במיקום ולחץ על 'אישור'.

• החלונית שדות טבלת ציר תציג אובייקטים בטבלה.

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

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

זוהי עזרה עצומה במקרה של מודל / טבלה המכילה מספר רב של תצפיות.

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

דברים שיש לזכור

• באמצעות מודל הנתונים אנו יכולים לנתח נתונים ממספר טבלאות בו זמנית.
• על ידי יצירת קשרים עם מודל הנתונים, אנו עולים על הצורך בשימוש בנוסחאות VLOOKUP, SUMIF, INDEX ו- MATCH מכיוון שאיננו צריכים להכניס את כל העמודות לטבלה אחת.
• כאשר מערכי נתונים מיובאים ב- Excel ממקורות חיצוניים, המודלים נוצרים באופן מרומז.
• ניתן ליצור קשרי טבלה באופן אוטומטי אם אנו מייבאים טבלאות קשורות הכוללות קשרי מפתח ראשוניים וזרים.
• בעת יצירת קשרים, העמודות שאנו מחברים בטבלאות צריכות להיות מאותו סוג נתונים.
• בעזרת טבלאות הציר שנוצרו באמצעות מודל הנתונים, אנו יכולים להוסיף גם פרוסות ולחתוך את טבלאות הציר בכל שדה שנרצה.
• היתרון של מודל הנתונים על פני פונקציות LOOKUP () הוא שהוא דורש פחות זיכרון באופן משמעותי.
• Excel 2013 תומך רק בקשר אחד לאחד או אחד לרבים, כלומר באחת הטבלאות אסור שיהיו ערכים כפולים בעמודה אליה אנו מקשרים.