Excel הוא כלי רב עוצמה שיש לו כמה פונקציות ופונקציות מדהימות בעת עבודה עם נתונים סטטיסטיים.
מציאת מתאם בין שתי סדרות נתונים היא אחד החישובים הסטטיסטיים הנפוצים ביותר בעבודה עם מערכי נתונים גדולים,
עבדתי כאנליסט פיננסי לפני כמה שנים, ולמרות שלא היינו מעורבים במידה רבה בנתונים סטטיסטיים, מציאת מתאם הייתה דבר שעוד היה עלינו לעשות לעתים קרובות למדי.
במדריך זה אראה לכם שתי דרכים פשוטות באמת לחשב את מקדם המתאם ב- Excel. יש כבר פונקציה מובנית לשם כך, ותוכל גם להשתמש בחבילת ניתוח הנתונים.
אז בואו נתחיל!
מהו מקדם המתאם?
מכיוון שלא מדובר בשיעור סטטיסטיקה, הרשה לי להסביר בקצרה מהו מקדם המתאם ולאחר מכן נעבור לסעיף בו אנו מחשבים את מקדם המתאם ב- Excel.
מקדם מתאם הוא ערך שאומר לך עד כמה שתי סדרות נתונים קשורות.
דוגמה נפוצה היא המשקל והגובה של 10 אנשים בקבוצה. אם נחשב את מקדם המתאם לנתוני הגובה והמשקל עבור אנשים אלה, נקבל ערך בין -1 ל -1.
ערך פחות מאפס מציין מתאם שלילי, מה שאומר שאם הגובה עולה אז המשקל יורד, או אם המשקל עולה אז הגובה יורד.
וערך יותר מאפס מציין מתאם חיובי, מה שאומר שאם הגובה עולה אז המשקל עולה, ואם הגובה יורד המשקל יורד.
ככל שהערך קרוב יותר ל -1, כך המתאם החיובי חזק יותר. אז ערך של .8 יצביע על כך שנתוני הגובה והמשקל מתואמים מאוד.
הערה: ישנם סוגים שונים של מקדמי מתאם וסטטיסטיקה, אך במדריך זה נבחן את המקובל ביותר שהוא מקדם המתאם של פירסון.כעת, בואו נראה כיצד לחשב את מקדם המתאם הזה ב- Excel.
חישוב מקדם המתאם ב- Excel
כפי שציינתי, ישנן מספר דרכים שבהן ניתן לחשב את מקדם המתאם ב- Excel.
שימוש בפורמולה של CORREL
CORREL היא פונקציית סטטיסטיקה שהוצגה ב- Excel 2007.
נניח שיש לך מערך נתונים כפי שמוצג להלן שבו אתה רוצה לחשב את מקדם המתאם בין הגובה למשקל של 10 אנשים.
להלן הנוסחה שתעשה זאת:
= CORREL (B2: B12, C2: C12)
הפונקציה לעיל CORREL לוקחת שני ארגומנטים - הסדרה עם נקודות נתוני הגובה והסדרה עם נקודות נתוני המשקל.
וזה הכל!
ברגע שאתה לוחץ על enter, Excel מבצע את כל החישובים בקצה האחורי, והוא נותן לך מספר מקדם מתאם אחד של פירסון.
בדוגמה שלנו, ערך זה הוא מעט מעל .5, מה שמעיד על קיום מתאם חיובי חזק למדי.
שיטה זו מומלצת ביותר אם יש לך שתי סדרות וכל מה שאתה רוצה הוא מקדם המתאם.
אך אם יש לך מספר סדרות וברצונך לברר את מקדם המתאם של כל הסדרות הללו, תוכל גם לשקול שימוש בחבילת הכלים לניתוח נתונים ב- Excel (מכוסה בהמשך)
שימוש בחבילת ניתוח הנתונים
ל- Excel יש כלי חבילת ניתוח נתונים שניתן להשתמש בו לחישוב מהיר של ערכי סטטיסטיקה שונים (כולל קבלת מקדם המתאם).
אך כלי חבילת ניתוח הנתונים מושבת כברירת מחדל ב- Excel. אז השלב הראשון יהיה לאפשר את כלי ניתוח הנתונים בחזרה ולאחר מכן להשתמש בזה לחישוב מקדם המתאם של פירסון ב- Excel.
הפעלת חבילת ניתוח הנתונים
להלן השלבים להפעלת Toolpak Analysis Data ב- Excel:
- לחץ על הכרטיסייה קובץ
- לחץ על אפשרויות
- בתיבת הדו-שיח אפשרויות Excel שנפתחת, לחץ על האפשרות תוספות בחלונית סרגל הצד
- בתפריט הנפתח ניהול, בחר תוספות Excel
- לחץ על עבור. פעולה זו תפתח את תיבת הדו-שיח התוספות
- בדוק את האפשרות Analysis Toolpak
- לחץ על אישור
השלבים שלעיל יוסיפו קבוצה חדשה בכרטיסיה נתונים ברצועת הכלים של Excel בשם Analysis. בתוך קבוצה זו תהיה לך האפשרות ניתוח נתונים
חישוב מקדם המתאם באמצעות כלי חבילת ניתוח נתונים
כעת, כאשר יש לך את כלי הניתוח זמין בסרט, בואו נראה כיצד לחשב את מקדם המתאם באמצעותו.
נניח שיש לך מערך נתונים כפי שמוצג להלן ואתה רוצה לברר את המתאם בין שלוש הסדרות (גובה ומשקל, גובה והכנסה, ומשקל והכנסה)
להלן השלבים לביצוע פעולה זו:
- לחץ על הכרטיסייה נתונים
- בקבוצת הניתוח, לחץ על האפשרות ניתוח נתונים
- בתיבת הדו -שיח ניתוח נתונים שנפתחת, לחץ על 'מתאם'
- לחץ על אישור. פעולה זו תפתח את תיבת הדו -שיח מתאם
- לטווח קלט, בחר את שלוש הסדרות - כולל הכותרות
- עבור 'מקובץ לפי', ודא כי 'עמודות' נבחרה
- בחר באפשרות - 'תווית בשורה הראשונה'. זה יוודא שבנתונים המתקבלים יהיו אותן כותרות ויהיה הרבה יותר קל להבין את התוצאות
- באפשרויות הפלט, בחר היכן אתה רוצה את הטבלה המתקבלת. אני הולך עם תא G1 על אותו דף עבודה. תוכל גם לבחור לקבל את התוצאות בגליון עבודה חדש או בחוברת עבודה חדשה
- לחץ על אישור
ברגע שתעשה זאת, Excel היה מחשב את מקדם המתאם לכל הסדרות ונותן לך טבלה כפי שמוצג להלן:
שים לב שהטבלה המתקבלת היא סטטית ולא תתעדכן במקרה שאחת מנקודות הנתונים בטבלה שלך תשתנה. במקרה של שינוי כלשהו, יהיה עליך לחזור על השלבים שלעיל כדי ליצור טבלה חדשה של מקדמי מתאם.
אז אלו שתי שיטות מהירות וקלות לחישוב מקדם המתאם ב- Excel.
אני מקווה שמצאת הדרכה שימושית!
