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

להלן הסעיפים בערכת לימוד זו:

• ייבוא נתונים ממסד נתונים

• ייבוא נתונים מגיליון אלקטרוני

• ייבוא נתונים באמצעות העתקה והדבקה

• יצירת קשר גומלין בין נתונים מיובאים

• נקודת ביקורת ובוחן

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

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

ייבוא נתונים ממסד נתונים

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

תחילה, נוריד נתונים מהאינטרנט. הנתונים מתארים מדליות אולימפיות, ומהווים מסד נתונים של Microsoft Access.

1. לחץ על הקישורים הבאים כדי להוריד קבצים שבהם נשתמש במהלך סדרת ערכות לימוד זו. הורד כל אחד מארבעת הקבצים למיקום נגיש, כגון הורדות אוהמסמכים שלי, או לתיקיה חדשה שאתה יוצר:
   > OlympicMedals.accdb Access הנתונים
   >OlympicSports.xlsx Excel העבודה
   > Population.xlsx Excel העבודה
   >DiscImage_table.xlsx Excel העבודה

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

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

4. בחר את הקובץ OlympicMedals.accdb שהורדת ולחץ על פתח. מופיע החלון 'בחירת טבלה' הבא, המציג את הטבלאות שנמצאו במסד הנתונים. טבלאות במסד נתונים דומות לגליונות עבודה או טבלאות ב- Excel. סמן את התיבה אפשר בחירה של טבלאות מרובות ובחר את כל הטבלאות. לאחר מכן לחץ על אישור.
   
   

5. החלון 'ייבוא נתונים' מופיע.

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

   
   בחר את האפשרות דוח PivotTable, המייבאת את הטבלאות Excel ומכין PivotTable לניתוח הטבלאות המיובאות ולחץ על אישור.
   
   

6. לאחר ייבוא הנתונים, נוצר PivotTable באמצעות הטבלאות המיובאות.
   
   

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

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

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

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

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

1. בתיבה שדות PivotTable, הרחב את הטבלה Medals על-ידי לחיצה על החץ שלצדה. מצא את השדה NOC_CountryRegion בטבלה מדליות המורחבת, וגרור אותו לאזור עמודות. NOC הוא ראשי תיבות של National Olympic Committees (ועדים אולימפיים לאומיים) ומהווה את היחידה הארגונית של מדינה או אזור.

2. לאחר מכן, מהטבלה Disciplines, גרור את Discipline לאזור שורות.

3. בוא נסנן את Disciplines כדי להציג רק חמישה ענפי ספורט: Archery,‏ Diving,‏ Fencing,‏ Figure Skating ו- Speed Skating. באפשרותך לעשות זאת מתוך האזור שדות PivotTable או מתוך המסנן תוויות שורה ב- PivotTable עצמו.

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

   2. לחלופין, במקטע תוויות שורה של ה- PivotTable, לחץ על הרשימה הנפתחת ליד תוויות שורה ב- PivotTable, לחץ על (בחר הכל) כדי להסיר את כל הבחירות ולאחר מכן גלול מטה ובחר ב- Archery,‏ Diving,‏ Fencing,‏ Figure Skating ו- Speed Skating. לחץ על אישור.

4. בשדות PivotTable, מהטבלה Medals, גרור את Medal לאזור ערכים. מאחר שרשומת 'ערכים' חייבת להיות מספרית, Excel משנה את Medal באופן אוטומטי לספירה של Medal.

5. מהטבלה Medals, בחר שוב את Medal וגרור אותו לאזור מסננים.

6. בוא נסנן את ה- PivotTable כך שיציג רק את המדינות או האזורים בעלי יותר מ- 90 מדליות בסה"כ. כך תעשה זאת.

   1. ב- PivotTable, לחץ על הרשימה הנפתחת משמאל לתוויות עמודה.

   2. בחר מסנני ערכים ובחר גדול מ….

   3. הקלד 90 בשדה האחרון (משמאל). לחץ על אישור.
      

ה- PivotTable שלך נראה כמו המסך הבא.

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

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

ייבוא נתונים מגיליון אלקטרוני

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

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

1. הוסף גליון עבודה חדש של Excel ותן לו את השם Sports.

2. אתר את התיקיה המכילה את קבצי הנתונים לדוגמה שהורדו ופתח את OlympicSports.xlsx.

3. בחר והעתק את הנתונים בגיליון1. אם אתה בוחר תא עם נתונים, כגון תא A1, באפשרותך להקיש Ctrl + A כדי לבחור את כל הנתונים הסמוכים. סגור את חוברת העבודה OlympicSports.xlsx.

4. בגליון העבודה Sports, מקם את הסמן בתא A1 והדבק את הנתונים.

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

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

7. שמור את חוברת העבודה.

ייבוא נתונים באמצעות העתקה והדבקה

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

1. הוסף גליון עבודה חדש של Excel ותן לו את השם Hosts.

2. בחר והעתק את הטבלה הבאה, לרבות כותרות הטבלה.

1. ב- Excel, מקם את הסמן בתא A1 של גליון העבודה Hosts והדבק את הנתונים.

2. עצב את הנתונים כטבלה. כפי שתואר קודם בערכת לימוד זו, הקש Ctrl + T כדי לעצב את הנתונים כטבלה, או עבור אל בית > עצב כטבלה. מאחר שלנתונים יש כותרות, בחר לטבלה שלי יש כותרות בחלון יצירת טבלה שמופיע.

3. תן לטבלה שם. בכלי טבלאות > עיצוב > מאפיינים, אתר את השדה שם טבלה והקלד Hosts.

4. בחר את העמודה Edition, ומהכרטיסיה בית, עצב אותה כמספר עם 0 מקומות עשרוניים.

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

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

יצירת קשר גומלין בין נתונים מיובאים

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

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

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

3. הרחב את Sports ובחר ב- Sport כדי להוסיף אותו ל- PivotTable. שים לב ש- Excel יבקש ממך ליצור קשר גומלין, כפי שניתן לראות במסך הבא.
   
    

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

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

5. בטבלה, בחר Disciplines מהרשימה הנפתחת.

6. בעמודה (זרה), בחר SportID.

7. בטבלה קשורה, בחר Sports.

8. בעמודה קשורה (ראשית), בחר SportID.

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

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

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

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

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

נקודת ביקורת ובוחן

עיון בחומר הנלמד

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

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

אתה מוכן לעבור לערכת הלימוד הבאה בסדרה זו. הנה קישור:

הרחבת קשרי הגומלין של מודל נתונים באמצעות Excel 2013,‏ Power Pivot ו- DAX

בוחן

ברצונך לבחון באיזו מידה אתה זוכר את מה שלמדת? זו ההזדמנות שלך. הבוחן הבא מתמקד בתכונות, יכולות או דרישות שלמדת עליהן בערכת לימוד זו. בתחתית הדף, תמצא את התשובות. בהצלחה!

שאלה 1: מדוע חשוב להמיר נתונים מיובאים לטבלאות?

א: אינך צריך להמיר אותם לטבלאות, מכיוון שכל הנתונים המיובאים הופכים באופן אוטומטי לטבלאות.

ב: אם תמיר נתונים מיובאים לטבלאות, הם לא ייכללו במודל הנתונים. רק כאשר הם אינם נכללים במודל הנתונים, הם הופכים לזמינים בטבלאות PivotTable‏, Power Pivot ו- Power View.

ג: אם תמיר הנתונים מיובאים לטבלאות, ניתן יהיה לכלול אותם במודל הנתונים ולהפוך אותם לזמינים בטבלאות PivotTable,‏ Power Pivot ו- Power View.

ד: לא ניתן להמיר נתונים מיובאים לטבלאות.

שאלה 2: אילו ממקורות הנתונים הבאים ניתן לייבא אל Excel ולכלול אותם במודל הנתונים?

א: מסדי נתונים של Access ומסדי נתונים רבים אחרים.

ב: קבצים קיימים של Excel.

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

ד: כל התשובות נכונות.

שאלה 3: ב- PivotTable, מה קורה בעת סידור מחדש של שדות בארבעת אזורי שדות PivotTable?

א: שום דבר - לא ניתן לסדר מחדש שדות לאחר שאתה ממקם אותם באזורי 'שדות PivotTable'.

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

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

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

שאלה 4: בעת יצירת קשר גומלין בין טבלאות, מה נדרש?

א: אף טבלה אינה יכולה לכלול עמודה כלשהי המכילה ערכים ייחודים שאינם חוזרים.

ב: אחת הטבלאות אינה יכולה להוות חלק מחוברת העבודה של Excel.

ג: אין להמיר את העמודות לטבלאות.

ד: אף תשובה אינה נכונה.

תשובות הבוחן

1. התשובה הנכונה: ג

2. התשובה הנכונה: ד

3. התשובה הנכונה: ב

4. התשובה הנכונה: ד