חישוב מדדים סטטיסטיים באקסל

טווח (Range)

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

חישוב באקסל:

= MAX(A2:A13) - MIN(A2:A13)

דוגמה: עבור קבוצת הנתונים 2, 5, 7, 9, 8, 2, 5, 7, 7, 7, 6, 5, 4 יש לחשב את הערך המקסימלי (9) פחות הערך המינימלי (2), כלומר טווח = 7.

ממוצע (Mean)

הממוצע הוא סכום כל הערכים חלקי מספר הערכים.

חישוב באקסל:

= AVERAGE(A2:A13)

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

הפרש מהממוצע

ההפרש מהממוצע הוא ההפרש בין כל ערך לבין הממוצע. כדי לחשב זאת באקסל, יש לקבע את התא המכיל את הממוצע באמצעות סימן דולר ($):

= A2 - $C$3

כאשר A2 הוא ערך במדגם ו-C3 מכיל את הממוצע.

ריבוע ההפרש מהממוצע

ריבוע ההפרש מהממוצע הוא הריבוע של ההפרש בין כל ערך לבין הממוצע:

= (A2 - $C$3)^2

שונות (Variance)

השונות היא ממוצע של ריבועי ההפרשים מהממוצע, כאשר מחלקים ב-(n-1):

= SUM(D2:D13) / 11

או באמצעות הפונקציה המובנית:

= VAR.S(A2:A13)

בקורס שלנו משתמשים בחלוקה ב-n-1. החלוקה ב-n-1 במקום ב-n נקראת תיקון בסל (Bessel’s correction) ומשמשת לקבלת אומדן הטיה לשונות האוכלוסייה.

סטיית תקן (Standard Deviation)

סטיית התקן היא השורש הריבועי של השונות:

= SQRT(F3)

או באמצעות הפונקציה המובנית:

= STDEV.S(A2:A13)

השפעת טרנספורמציות על מדדים סטטיסטיים

הוספת קבוע

אם נוסיף קבוע (למשל 10) לכל הנתונים:

  • הממוצע יגדל ב-10
  • השונות וסטיית התקן לא ישתנו

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

הכפלה בקבוע

אם נכפיל את כל הנתונים בקבוע (למשל 5):

  • הממוצע יגדל פי 5
  • השונות תגדל פי 25 (5 בריבוע)
  • סטיית התקן תגדל פי 5

דוגמה: אם הממוצע המקורי היה 85 וסטיית התקן 10:

  • הממוצע החדש: 85 × 5 = 425
  • סטיית התקן החדשה: 10 × 5 = 50
  • השונות החדשה: 100 × 25 = 2,500

מקדמי מתאם והשוואת קבוצות

דוגמה: השוואה בין חוגים לפי ציונים באנגלית

נתונים על שני חוגים

מדד חוג א’ חוג ב’
ממוצע 78 70
חציון 65 70
שכיח < 65 70
סטיית תקן גבוהה נמוכה (6)

ניתוח ההבדלים

בחוג א’:

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

בחוג ב’:

  • הממוצע, החציון והשכיח דומים (כולם בסביבות 70)
  • סטיית תקן נמוכה (6)
  • המבנה מעיד על התפלגות סימטרית יותר - רוב הסטודנטים מקבלים ציונים דומים בסביבות 70

מסקנות

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

מעבר בין סולמות ציונים

כאשר עוברים מסולם ציונים של 1-100 לסולם של 1-10 (כלומר, מחלקים כל ציון ב-10):

  • הממוצע יקטן פי 10
  • סטיית התקן תקטן פי 10
  • השונות תקטן פי 100

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

מקדם המתאם של פירסון מודד את הקשר הלינארי בין שני משתנים. המקדם נע בין -1 ל-1:

  • 1 מייצג קשר לינארי חיובי מושלם
  • -1 מייצג קשר לינארי שלילי מושלם
  • 0 מייצג היעדר קשר לינארי

חישוב באקסל

ניתן לחשב את מקדם המתאם באקסל בשתי דרכים:

  1. באמצעות הפונקציה המובנית:

    = PEARSON(array1, array2)

  2. באמצעות הנוסחה:

    = COVAR(array1, array2) / (STDEV.S(array1) * STDEV.S(array2))

יצירת גרף פיזור באקסל

  1. בחר את הנתונים של שני המשתנים
  2. לחץ על “הוספה” (Insert) ואז “גרף פיזור” (Scatter)
  3. ניתן להוסיף כותרות לצירים ולגרף באמצעות ה”פלוס” שליד הגרף

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

  • הוספת קבוע (למשל, פקטור של 5 נקודות) לכל הערכים של אחד המשתנים לא משנה את ערך מקדם המתאם
  • הכפלה בקבוע (למשל, הכפלת כל הציונים ב-1.5) לא משנה את ערך מקדם המתאם

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

מקדם המתאם בטרנספורמציות לינאריות

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

מקדם המתאם של פירסון מוגדר כך:

\[r = \frac{\sum_{i=1}^{n}(x_i - \bar{x})(y_i - \bar{y})}{\sqrt{\sum_{i=1}^{n}(x_i - \bar{x})^2} \cdot \sqrt{\sum_{i=1}^{n}(y_i - \bar{y})^2}}\]

כאשר נכפיל את המשתנה $x$ בקבוע $a$, נקבל:

\[r = \frac{\sum_{i=1}^{n}(a \cdot x_i - a \cdot \bar{x})(y_i - \bar{y})}{\sqrt{\sum_{i=1}^{n}(a \cdot x_i - a \cdot \bar{x})^2} \cdot \sqrt{\sum_{i=1}^{n}(y_i - \bar{y})^2}}\]

ניתן להוציא את $a$ מחוץ לסכום במונה:

\[r = \frac{a \cdot \sum_{i=1}^{n}(x_i - \bar{x})(y_i - \bar{y})}{\sqrt{a^2 \cdot \sum_{i=1}^{n}(x_i - \bar{x})^2} \cdot \sqrt{\sum_{i=1}^{n}(y_i - \bar{y})^2}}\]

כעת ניתן להוציא את $a$ מהשורש במכנה:

\[r = \frac{a \cdot \sum_{i=1}^{n}(x_i - \bar{x})(y_i - \bar{y})}{a \cdot \sqrt{\sum_{i=1}^{n}(x_i - \bar{x})^2} \cdot \sqrt{\sum_{i=1}^{n}(y_i - \bar{y})^2}}\]

ה-$a$ מתקזז, ואנו נשארים עם אותו ערך של $r$ כמו במקרה המקורי. זאת אומרת, הכפלה של אחד המשתנים בקבוע אינה משנה את מקדם המתאם.

באופן דומה, אם נוסיף קבוע $b$ למשתנה $x$, מקדם המתאם לא ישתנה מכיוון שהקבוע מתקזז בעת חישוב ההפרשים מהממוצע.

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

כאשר קיים קשר לינארי מושלם בין שני משתנים, כלומר $y = ax + b$ (כאשר $a > 0$), נוכיח שמקדם המתאם שווה ל-1.

נציב את $y_i = a \cdot x_i + b$ בנוסחת מקדם המתאם:

\[r = \frac{\sum_{i=1}^{n}(x_i - \bar{x})((a \cdot x_i + b) - (a \cdot \bar{x} + b))}{\sqrt{\sum_{i=1}^{n}(x_i - \bar{x})^2} \cdot \sqrt{\sum_{i=1}^{n}((a \cdot x_i + b) - (a \cdot \bar{x} + b))^2}}\]

נפשט את הביטוי במונה:

\[(a \cdot x_i + b) - (a \cdot \bar{x} + b) = a \cdot (x_i - \bar{x})\]

ואת הביטוי במכנה:

\[((a \cdot x_i + b) - (a \cdot \bar{x} + b))^2 = (a \cdot (x_i - \bar{x}))^2 = a^2 \cdot (x_i - \bar{x})^2\]

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

\[r = \frac{\sum_{i=1}^{n}(x_i - \bar{x}) \cdot a \cdot (x_i - \bar{x})}{\sqrt{\sum_{i=1}^{n}(x_i - \bar{x})^2} \cdot \sqrt{\sum_{i=1}^{n}a^2 \cdot (x_i - \bar{x})^2}}\]

נוציא את $a$ מחוץ לסכום במונה:

\[r = \frac{a \cdot \sum_{i=1}^{n}(x_i - \bar{x})^2}{\sqrt{\sum_{i=1}^{n}(x_i - \bar{x})^2} \cdot \sqrt{a^2 \cdot \sum_{i=1}^{n}(x_i - \bar{x})^2}}\]

נוציא את $a$ מהשורש במכנה:

\[r = \frac{a \cdot \sum_{i=1}^{n}(x_i - \bar{x})^2}{\sqrt{\sum_{i=1}^{n}(x_i - \bar{x})^2} \cdot a \cdot \sqrt{\sum_{i=1}^{n}(x_i - \bar{x})^2}}\]

המונה והמכנה זהים (פרט לסדר הגורמים), ולכן:

\[r = \frac{\sum_{i=1}^{n}(x_i - \bar{x})^2}{\sum_{i=1}^{n}(x_i - \bar{x})^2} = 1\]

זה מוכיח שכאשר יש קשר לינארי מושלם בין שני משתנים (כאשר המקדם חיובי), מקדם המתאם של פירסון שווה ל-1.

במקרה של $a < 0$ (קשר לינארי שלילי), נקבל $r = -1$.

דוגמה מספרית: קשר מושלם בין שני מקצועות לימוד

נשאלנו: האם ייתכן מקדם מתאם 1 בין שני מקצועות, כאשר במקצוע אחד הממוצע הוא 50 וסטיית התקן 15, ובמקצוע השני הממוצע הוא 80 וסטיית התקן 15?

התשובה היא כן. אם נגדיר $y = x + 30$, כאשר $x$ מייצג את הציון במקצוע הראשון ו-$y$ מייצג את הציון במקצוע השני, אז:

  1. הממוצע של $x$ הוא 50, ולכן הממוצע של $y$ יהיה $50 + 30 = 80$
  2. סטיית התקן של $x$ היא 15, וסטיית התקן של $y$ תישאר 15 (הוספת קבוע לא משנה את סטיית התקן)
  3. מקדם המתאם יהיה 1 מכיוון שיש קשר לינארי מושלם בין שני המשתנים

זוהי דוגמה מציאותית לתופעה שיכולה לקרות כאשר מורה מוסיף “פקטור” קבוע לכל הציונים במקצוע מסוים.

משמעות ותובנות

חשוב להבין שמקדם מתאם של 1 מצביע רק על קשר לינארי מושלם בין שני משתנים. הוא אינו אומר דבר על הפיזור של הנתונים עצמם. למשל, שני משתנים יכולים להיות מאוד “מפוזרים” (בעלי סטיית תקן גבוהה), אך עדיין לקיים ביניהם קשר לינארי מושלם.

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

לסיכום, מקדם המתאם מודד רק את היחס בין המשתנים ולא את המאפיינים האינדיבידואליים של כל משתנה בנפרד.

ניתוח גרפי פיזור ומקדמי המתאם שלהם

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

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

תובנות מהגרפים השונים

  • גרף מספר 7 נראה כקו ישר כמעט מושלם, אך יש בו נקודה חריגה (אאוטלייר) שמשפיעה משמעותית על חישוב מקדם המתאם
  • גרף מספר 8 נראה בעל קורלציה גבוהה
  • גרף מספר 2 נראה מבטיח ועשוי להציג קורלציה משמעותית

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

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

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

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

הבהרה על גרף פיזור

בגרף פיזור, כל נקודה מייצגת תצפית אחת, כאשר:

  • הקואורדינטה על ציר ה-X מייצגת את ערך המשתנה הראשון (X) בתצפית זו
  • הקואורדינטה על ציר ה-Y מייצגת את ערך המשתנה השני (Y) באותה תצפית

זאת בניגוד להיסטוגרמה של משתנה בודד, שבה ציר ה-X מייצג את ערך המשתנה וציר ה-Y מייצג את תדירות הופעתו של ערך זה.

המידע שכן ניתן להסיק מגרף פיזור

למרות המגבלות, יש מידע שאפשר להסיק מגרף פיזור:

  1. הטווח של כל משתנה:
    • הטווח של X הוא ההפרש בין הערך המינימלי לערך המקסימלי על ציר ה-X
    • הטווח של Y הוא ההפרש בין הערך המינימלי לערך המקסימלי על ציר ה-Y
  2. ניתן לשחזר באופן גס את ההיסטוגרמות של X ו-Y על ידי “דחיסת” הנקודות לציר הרלוונטי:
    • אם יש אזור עם ריכוז גבוה של נקודות באותו ערך X, זה מצביע על תדירות גבוהה של ערך זה
    • כך למשל, אם באזור X=2 יש 2 נקודות, באזור X=3 יש 3 נקודות, וכן הלאה, ניתן לשחזר באופן גס את התפלגות X

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

קשרים לינאריים מושלמים וסטיית התקן

במקרה של קשר לינארי מושלם בין שני משתנים (Y = aX + b), התפלגות הנקודות בגרף הפיזור תהיה על קו ישר. במקרה זה:

  1. אם a > 0, מקדם המתאם יהיה 1
  2. אם a < 0, מקדם המתאם יהיה -1

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

דוגמה מספרית

אם משתנה X מתפלג עם ממוצע 50 וסטיית תקן 15, ומשתנה Y מתפלג עם ממוצע 80 וסטיית תקן 15, יכול להיות ביניהם מקדם מתאם של 1 אם Y = X + 30.

ניתן להדגים זאת על ידי המחשה של הקשר הלינארי:

  • הממוצע של Y יהיה הממוצע של X בתוספת 30, כלומר 80
  • סטיית התקן של Y תישאר זהה לזו של X (15), מכיוון שהוספת קבוע לא משנה את סטיית התקן

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

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

  • כיוון הקשר בין המשתנים (חיובי או שלילי)
  • מידת הלינאריות של הקשר
  • עוצמת הקשר הלינארי (בין -1 ל-1)

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

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

הערה לגבי חישוב שונות מדגמית ומדוע מחלקים ב-(n-1)

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

כאשר מחשבים שונות מדגמית כדי להעריך את שונות האוכלוסייה, מחלקים ב-(n-1) ולא ב-n מכיוון שכבר “השתמשנו” בדרגת חופש אחת כאשר אמדנו את ממוצע האוכלוסייה באמצעות ממוצע המדגם.

אינטואיטיבית, כאשר מחשבים את הסכום $\sum_{i=1}^{n}(x_i - \bar{x})^2$, הערך $\bar{x}$ הוא כבר פונקציה של ערכי המדגם עצמם. כלומר, מתוך n נקודות המדגם, רק n-1 הן “חופשיות” באופן מלא לאחר שקבענו את הממוצע. חלוקה ב-(n-1) במקום ב-n מתקנת הטיה זו ומספקת אומדן טוב יותר לשונות האוכלוסייה.

דור פסקל
חזרה לעמוד הראשי