האם השתמשת אי פעם בפונקציה VLOOKUP כדי להביא עמודה מטבלה אחת לטבלה אחרת? כעת, כש- Excel מכיל מודל נתונים מוכלל, הפונקציה VLOOKUP היא מיושנת. באפשרותך ליצור קשר גומלין בין שתי טבלאות של נתונים, בהתבסס על התאמת נתונים בכל טבלה. לאחר מכן תוכל ליצור גליונות Power View ולבנות טבלאות PivotTable ודוחות אחרים עם שדות מכל טבלה, גם כאשר הטבלאות הן ממקורות שונים. לדוגמה, אם יש לך נתוני מכירות של לקוחות, ייתכן שתרצה לייבא ולקשר נתוני בינת זמן כדי לנתח דפוסי מכירות לפי שנה ולפי חודש.
כל הטבלאות בחוברת עבודה מפורטות ברשימות השדות של Power View ו- PivotTable.
בעת ייבוא טבלאות קשורות ממסד נתונים יחסי, לעתים קרובות Excel יכול ליצור קשרי גומלין אלה במודל הנתונים שהוא בונה מאחורי הקלעים. בכל המקרים האחרים, יהיה עליך ליצור קשרי גומלין באופן ידני.
-
ודא שחוברת העבודה מכילה לפחות שתי טבלאות ושבכל טבלה יש עמודה שניתן למפות לעמודה בטבלה אחרת.
-
בצע אחת מהפעולות הבאות: עיצוב הנתונים כטבלהאו ייבוא נתונים חיצוניים כטבלה בגליון עבודה חדש.
-
תן לכל טבלה שם בעל משמעות: בכלי טבלאות, לחץ על עיצוב > שם טבלה > הזן שם.
-
ודא שהעמודה באחת מהטבלאות מכילה ערכי נתונים ייחודיים ללא כפילויות. Excel יכול ליצור את קשר הגומלין רק אם עמודה אחת מכילה ערכים ייחודיים.
לדוגמה, כדי לקשר מכירות של לקוחות לבינת זמן, על שתי הטבלאות לכלול תאריכים באותה תבנית (לדוגמה, 1/1/2012), וכל תאריך יופיע בטבלה אחת לפחות (בינת זמן) פעם אחת בלבד בתוך העמודה.
-
לחץ על נתונים > קשרי גומלין.
אם קשרי גומלין מופיע באפור, הסיבה לכך היא שחוברת העבודה שלך מכילה טבלה אחת בלבד.
-
בתיבת הדו-שיח ניהול קשרי גומלין לחץ על חדש.
-
בתיבת הדו-שיח יצירת קשר גומלין, לחץ על החץ עבור טבלה ובחר טבלה מהרשימה. בקשר גומלין מסוג יחיד לרבים, טבלה זו אמורה להיות בצד הרבים. אם נחזור לדוגמה של הלקוחות ובינת זמן, יהיה עליך לבחור תחילה את טבלת המכירות של הלקוחות, מאחר שסביר להניח שמכירות רבות יתרחשו בכל יום נתון.
-
עבור עמודה (זרה), בחר את העמודה המכילה את הנתונים הקשורים לעמודה קשורה (ראשית). לדוגמה, במקרה של עמודת תאריך בשתי הטבלאות, תבחר עמודה זו כעת.
-
עבור טבלה קשורה, בחר טבלה בעלת עמודת נתונים אחת לפחות הקשורה לטבלה שזה עתה בחרת עבור טבלה.
-
עבור עמודה קשורה (ראשית), בחר עמודה בעלת ערכים ייחודיים התואמים לערכים בעמודה שבחרת עבור עמודה.
-
לחץ על אישור.
מידע נוסף אודות קשרי גומלין בין טבלאות ב- Excel
הערות אודות קשרי גומלין
-
תוכל לדעת אם קיים קשר גומלין כאשר תגרור שדות מטבלאות שונות לרשימת השדות של PivotTable. אם אינך מתבקש ליצור קשר גומלין, המשמעות היא ש- Excel כבר קיבל את נתוני קשרי הגומלין שהוא זקוק להם כדי לקשר את הנתונים.
-
יצירת קשרי גומלין דומה לשימוש ב- VLOOKUP: אתה זקוק לעמודות המכילות נתונים תואמים כך ש- Excel יוכל ליצור הפניה מקושרת של שורות בטבלה אחת לשורות בטבלה אחרת. בדוגמה של בינת הזמן, טבלת הלקוחות תצטרך לכלול ערכי תאריך הקיימים גם בטבלת בינת זמן.
-
במודל נתונים, קשרי גומלין בין טבלאות יכולים להיות מסוג יחיד ליחיד (לכל נוסע יש כרטיס עלייה למטוס אחד) או מסוג יחיד לרבים (בכל טיסה יש נוסעים רבים), אך לא מסוג רבים לרבים. קשרי גומלין מסוג רבים לרבים גורמים לשגיאות של תלות מעגלית, כגון "זוהתה תלות מעגלית". שגיאה זו תתרחש אם תבצע חיבור ישיר בין שתי טבלאות עם סוג קשר גומלין של רבים לרבים, או עם חיבורים עקיפים (שרשרת של קשרי גומלין בין טבלאות מסוג יחיד לרבים בכל קשר גומלין, אך רבים לרבים בעת הצגה מקצה לקצה). קרא עוד אודות קשרי גומלין בין טבלאות במודל נתונים.
-
סוגי הנתונים בשתי העמודות חייבים להיות תואמים. ראה סוגי נתונים במודלי נתונים של Excel לקבלת פרטים.
-
קיימות דרכים אחרות ליצירת קשרי גומלין שעשויות להיות אינטואיטיביות יותר, במיוחד אם אינך בטוח באילו עמודות להשתמש. ראה יצירת קשר גומלין בתצוגת דיאגרמה ב- Power Pivot.
דוגמה: קישור נתוני בינת זמן לנתוני טיסות של חברת תעופה
באפשרותך ללמוד אודות קשרי גומלין בין טבלאות וכן אודות בינת זמן באמצעות נתונים המסופקים ללא תשלום ב- Microsoft Azure Marketplace. חלק מערכות נתונים אלה גדולות מאוד, ונדרש עבורן חיבור אינטרנט מהיר להשלמת הורדת הנתונים בפרק זמן סביר.
-
הפעל את התוספת Power Pivot ב- Microsoft Excel 2013 ופתח את חלון Power Pivot.
-
לחץ על קבל נתונים חיצוניים > משירות נתונים > מ- Microsoft Azure Marketplace. דף הבית של Microsoft Azure Marketplace ייפתח באשף ייבוא טבלאות.
-
תחת מחיר, לחץ על ללא תשלום.
-
תחת קטגוריה, לחץ על מדע וסטטיסטיקה.
-
אתר את DateStream ולחץ על הירשם כמנוי.
-
הזן את חשבון Microsoft שלך ולחץ על היכנס. תצוגה מקדימה של הנתונים אמורה להופיע בחלון.
-
גלול לחלק התחתון ולחץ על שאילתת בחירה.
-
לחץ על הבא.
-
בחר BasicCalendarUS ולאחר מכן לחץ על סיום כדי לייבא את הנתונים. עם חיבור אינטרנט מהיר, הייבוא אמור להימשך כדקה. בסיום, אתה אמור לראות דוח מצב של 73,414 שורות שהועברו. לחץ על סגור.
-
לחץ על קבל נתונים חיצוניים > משירות נתונים > מ- Microsoft Azure Marketplace כדי לייבא ערכת נתונים נוספת.
-
תחת סוג, לחץ על נתונים.
-
תחת מחיר, לחץ על ללא תשלום.
-
אתר את עיכובי טיסות של חברות תעופה אמריקאיות ולחץ על בחר.
-
גלול לחלק התחתון ולחץ על שאילתת בחירה.
-
לחץ על הבא.
-
לחץ על סיום כדי לייבא את הנתונים. עם חיבור אינטרנט מהיר, הייבוא עשוי להימשך 15 דקות. בסיום, אתה אמור לראות דוח מצב של 2,427,284 שורות שהועברו. לחץ על סגור. כעת אמורות להופיע שתי טבלאות במודל הנתונים. כדי לקשר אותן, אנו זקוקים לעמודות תואמות בכל טבלה.
-
שים לב ש- DateKey ב- BasicCalendarUS הוא בתבנית 1/1/2012 12:00:00. הטבלה On_Time_Performance מכילה גם עמודת תאריך ושעה, FlightDate, שהערכים שלה מצוינים באותה תבנית: 1/1/2012 12:00:00. שתי העמודות מכילות נתונים תואמים, של סוג נתונים זהה, ולפחות אחת מהעמודות (datekey) מכילה ערכים ייחודיים בלבד. בשלבים הבאים, תשתמש בעמודות אלה כדי לקשר את הטבלאות.
-
בחלון Power Pivot, לחץ על PivotTable כדי ליצור PivotTable בגליון עבודה חדש או קיים.
-
ברשימת השדות, הרחב את On_Time_Performance ולחץ על ArrDelayMinutes להוספה לאזור הערכים. ב- PivotTable, אתה אמור לראות את הסכום הכולל של זמן העיכוב של הטיסות, כפי שנמדד בדקות.
-
הרחב את BasicCalendarUS ולחץ על MonthInCalendar להוספה לאזור השורות.
-
שים לב שב- PivotTable מופיעים כעת חודשים, אך הסכום הכולל של הדקות זהה עבור כל חודש. ערכים חוזרים וזהים מעידים על צורך בקשר גומלין.
-
ברשימת השדות, ב“ייתכן שנחוצים קשרי גומלין בין טבלאות”, לחץ על צור.
-
ב'טבלה קשורה', בחר On_Time_Performance וב'עמודה קשורה (ראשית)' בחר FlightDate.
-
בטבלה, בחר BasicCalendarUS וב'עמודה (זרה)' בחר DateKey. לחץ על אישור כדי ליצור את קשר הגומלין.
-
שים לב שסכום הדקות של העיכוב משתנה כעת עבור כל חודש.
-
ב- BasicCalendarUS גרור את YearKey לאזור השורות, מעל MonthInCalendar.
כעת באפשרותך לפרוס עיכובים של טיסות נכנסות לפי שנה ולפי חודש, או לפי ערכים אחרים בלוח השנה.
עצות: כברירת מחדל, חודשים מפורטים בסדר אלפביתי. באמצעות התוספת Power Pivot, באפשרותך לשנות את המיון כך שהחודשים יופיעו בסדר כרונולוגי.
-
ודא שהטבלה BasicCalendarUS פתוחה בחלון Power Pivot.
-
בטבלה 'בית', לחץ על מיין לפי עמודה.
-
ב'מיין', בחר MonthInCalendar
-
ב'לפי', בחר MonthOfYear.
ה- PivotTable כעת ממיין כל שילוב של חודש ושנה (אוקטובר 2011, נובמבר 2011) לפי מספר החודש בשנה (10, 11). שינוי סדר המיון קל מאחר שההזנה DateStream מספקת את כל העמודות הדרושות על מנת שתרחיש זה יפעל. אם אתה משתמש בטבלה שונה של בינת זמן, השלב שתבצע יהיה שונה.
"ייתכן שיהיה צורך בקשרי גומלין בין טבלאות"
כאשר תוסיף שדות ל- PivotTable, המערכת תיידע אותך אם נדרש קשר גומלין בין טבלאות כדי להבין את השדות שבחרת ב- PivotTable.
למרות שבאפשרות Excel להודיע לך כאשר קשר גומלין נחוץ, אין לו אפשרות לומר לך באילו טבלאות ועמודות להשתמש, או אם קשר גומלין בין טבלאות אפשרי בכלל. נסה לבצע את השלבים הבאים כדי לקבל את התשובות שאתה זקוק להן.
שלב 1: קביעה אילו טבלאות לציין בקשר הגומלין
אם המודל שלך מכיל רק כמה טבלאות, ייתכן שיהיה ברור לך מיד באילו מהן עליך להשתמש. אך במודלים גדולים יותר, סביר להניח שתזדקק לעזרה. גישה אחת היא להשתמש בתצוגת דיאגרמה בתוספת Power Pivot. תצוגת דיאגרמה מספקת ייצוג חזותי של כל הטבלאות במודל הנתונים. באמצעות תצוגת דיאגרמה, תוכל לקבוע במהירות אילו טבלאות נפרדות משאר המודל.
הערה: ניתן ליצור קשרי גומלין שאינם חד-משמעיים שהם בלתי חוקיים בעת שימוש בדוח PivotTable או Power View. נניח שכל הטבלאות קשורות בדרך כלשהי לטבלאות אחרות במודל, אך בעת ניסיון לשלב שדות מטבלאות שונות, אתה מקבל את ההודעה "ייתכן שנחוצים קשרי גומלין בין טבלאות". סביר להניח שנתקלת בקשר גומלין של רבים לרבים. אם תעקוב אחר שרשרת של קשרי גומלין בין טבלאות המחברים את הטבלאות שברצונך להשתמש בהן, סביר להניח שתגלה שיש לך שני קשרי גומלין בין טבלאות מסוג יחיד לרבים או יותר. אין פתרון קל שפועל בכל מצב, אך ניתן לנסות יצירת עמודות מחושבות לאיחוד העמודות שברצונך להשתמש בהן לטבלה אחת.
שלב 2: איתור עמודות שניתן להשתמש בהן כדי ליצור נתיב מטבלה אחת לטבלה הבאה
לאחר שתזהה איזו טבלה מנותקת משאר המודל, סקור את העמודות שלה כדי לקבוע אם עמודה אחרת, במקום אחר במודל, מכילה ערכים תואמים.
לדוגמה, נניח שהמודל שלך מכיל מכירות של מוצרים לפי אזור, ושלאחר מכן אתה מייבא נתונים דמוגרפיים כדי לברר אם קיים מתאם בין מגמות מכירות למגמות דמוגרפיות בכל אזור. מאחר שהנתונים הדמוגרפיים מגיעים ממקור נתונים אחר, הטבלאות שלהם תחילה מבודדות משאר המודל. כדי לשלב את הנתונים הדמוגרפיים עם שאר המודל, יהיה עליך למצוא עמודה באחת מהטבלאות של הנתונים הדמוגרפיים המתאימה לזו שאתה כבר משתמש בה. לדוגמה, אם הנתונים הדמוגרפיים מסודרים לפי אזור, ונתוני המכירות שלך מציינים את האזור שבו המכירה התבצעה, ניתן לקשר את שתי ערכות הנתונים על-ידי מציאת עמודה משותפת, כגון 'מדינה', 'מיקוד' או 'אזור', כדי לספק את בדיקת המידע.
מלבד ערכים תואמים, קיימות כמה דרישות נוספות ליצירת קשר גומלין:
-
ערכי נתונים בעמודת בדיקת המידע חייבים להיות ייחודיים. במילים אחרות, העמודה אינה יכולה להכיל ערכים כפולים. במודל נתונים, מחרוזות ריקות וערכי Null הם שווי ערך לרווח, שהוא ערך נתונים ייחודי. משמעות הדבר היא שלא ייתכנו ערכי Null מרובים בעמודת בדיקת המידע.
-
על סוגי הנתונים בעמודת המקור וכן בעמודת בדיקת המידע להיות תואמים. לקבלת מידע נוסף אודות סוגי נתונים, ראה סוגי נתונים במודלי נתונים.
כדי ללמוד עוד אודות קשרי גומלין בין טבלאות, ראה קשרי גומלין בין טבלאות במודל נתונים.
