כיצד לחשב ציון Z באמצעות Microsoft Excel

ציון Z הוא ערך סטטיסטי המספר לך כמה סטיות תקן בערך מסוים מממוצע מערך הנתונים כולו. אתה יכול להשתמש בנוסחאות AVERAGE ו- STDEV.S או STDEV.P כדי לחשב את סטיית הממוצע והסטנדרט של הנתונים שלך ואז להשתמש בתוצאות אלה כדי לקבוע את ציון ה- Z של כל ערך.

מהי ציון Z ומה הפונקציות AVERAGE, STDEV.S ו- STDEV.P עושות?

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

= (DataPoint-AVERAGE (DataSet)) / STDEV (DataSet)

להלן דוגמה העוזרת להבהיר. נניח שרצית להשוות את תוצאות המבחנים של שני תלמידי אלגברה שלימדו מורים שונים. אתה יודע שהתלמיד הראשון קיבל 95% בבחינה האחרונה בכיתה אחת, והתלמיד בכיתה השנייה השיג 87%.

במבט ראשון הציון 95% מרשים יותר, אבל מה אם המורה בכיתה ב 'יעמוד לבחינה קשה יותר? תוכלו לחשב את ציון ה- Z של הציון של כל תלמיד על סמך הציונים הממוצעים בכל כיתה וסטיית התקן של הציונים בכל כיתה. השוואת ציוני ה- Z של שני התלמידים יכולה לגלות שהתלמיד עם ציון 87% הצליח טוב יותר בהשוואה לשאר כיתתו מאשר התלמיד עם ציון 98% בהשוואה לשאר כיתתו.

הערך הסטטיסטי הראשון שאתה צריך הוא 'הממוצע' ופונקציית "AVERAGE" של Excel מחשבת את הערך הזה. זה פשוט מוסיף את כל הערכים בטווח תאים ומחלק את המסכם במספר התאים המכילים ערכים מספריים (הוא מתעלם מתאים ריקים).

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

בגירסאות קודמות של Excel הייתה רק הפונקציה "STDEV", המחשבת את סטיית התקן תוך התייחסות לנתונים כאל "מדגם" של אוכלוסייה. Excel 2010 שבר את זה לשתי פונקציות המחושבות את סטיית התקן:

  • STDEV.S: פונקציה זו זהה לפונקציה הקודמת "STDEV". הוא מחשב את סטיית התקן תוך התייחסות לנתונים כאל 'מדגם' של אוכלוסייה. מדגם של אוכלוסייה יכול להיות משהו כמו היתושים המסוימים שנאספו לצורך פרויקט מחקר או מכוניות שהופרשו ושימשו לבדיקת בטיחות ריסוק.
  • STDEV.P: פונקציה זו מחשבת את סטיית התקן תוך התייחסות לנתונים כאל האוכלוסייה כולה. אוכלוסייה שלמה תהיה משהו כמו כל היתושים על פני כדור הארץ או כל מכונית בהפקה של דגם ספציפי.

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

בואו נסתכל על דוגמא

לדוגמא שלנו, יש לנו שתי עמודות ("ערכים" ו- "ציון Z") ושלושה תאים "עוזרים" לאחסון התוצאות של הפונקציות "ממוצע", ​​"STDEV.S" ו- "STDEV.P". העמודה "ערכים" מכילה עשרה מספרים אקראיים שבמרכזם 500, ובעמודה "ציון Z" נחשב את ציון ה- Z באמצעות התוצאות המאוחסנות בתאי 'העוזר'.

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

הקלד את הנוסחה הבאה ולחץ על Enter - או - השתמש בתפריט "נוסחאות".

= ממוצע (E2: E13)

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

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

כעת לחץ על "אישור".

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

לחישוב סטיית התקן באמצעות הפונקציה "STDEV.S", הקלד נוסחה זו ולחץ על Enter (או גש אליה דרך התפריט "נוסחאות").

= STDEV.S (E3: E12)

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

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

כעת לחץ על "אישור".

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

לחישוב סטיית התקן באמצעות הפונקציה "STDEV.P", הקלד נוסחה זו ולחץ על Enter (או גש אליה דרך תפריט "נוסחאות").

= STDEV.P (E3: E12)

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

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

כעת לחץ על "אישור".

עכשיו, לאחר שחישבנו את סטיית הממוצע והסטנדרט של הנתונים שלנו, יש לנו את כל מה שאנחנו צריכים כדי לחשב את ציון ה- Z. אנו יכולים להשתמש בנוסחה פשוטה המתייחסת לתאים המכילים את התוצאות של הפונקציות "AVERAGE" ו- "STDEV.S" או "STDEV.P".

בחר את התא הראשון בעמודה "Z-Score". אנו נשתמש בתוצאה של הפונקציה "STDEV.S" לדוגמא זו, אך תוכל גם להשתמש בתוצאה מ- "STDEV.P."

הקלד את הנוסחה הבאה ולחץ על Enter:

= (E3- $ G $ 3) / $ H $ 3

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

  1. לחץ על תא F3 והקלד =(
  2. בחר תא E3. (ניתן ללחוץ על מקש החץ השמאלי פעם אחת או להשתמש בעכבר)
  3. הקלד את סימן המינוס -
  4. בחר בתא G3 ואז לחץ על F4 כדי להוסיף את התווים "$" כדי ליצור התייחסות 'מוחלטת' לתא (זה יעבור דרך "G3"> " $ G $ 3 ″>" G $ 3 ″> " $ G3 ″> "G3" אם תמשיך ללחוץ על F4 )
  5. סוּג )/
  6. בחר בתא H3 (או I3 אם אתה משתמש ב- "STDEV.P") ולחץ על F4 כדי להוסיף את שתי התווים "$".
  7. לחץ אנטר

ציון ה- Z מחושב לערך הראשון. זה 0.15945 סטיות תקן מתחת לממוצע. לבדיקת התוצאות ניתן להכפיל את סטיית התקן בתוצאה זו (6.271629 * -0.15945) ולבדוק שהתוצאה שווה להפרש בין הערך לממוצע (499-500). שתי התוצאות שוות, כך שהערך הגיוני.

בואו נחשב את ציוני Z של שאר הערכים. הדגש את כל העמודה 'Z-Score' החל מהתא המכיל את הנוסחה.

לחץ על Ctrl + D, שמעתיק את הנוסחה בתא העליון למטה דרך כל שאר התאים שנבחרו.

כעת הנוסחה 'התמלאה' לכל התאים, וכל אחד מהם יתייחס תמיד לתאים "AVERAGE" ו- "STDEV.S" או "STDEV.P" הנכונים בגלל התווים "$". אם אתה מקבל שגיאות, חזור וודא שהתווים "$" כלולים בנוסחה שהזנת.

חישוב ציון Z ללא שימוש בתאי 'עוזר'

תאי עוזר מאחסנים תוצאה, כמו אלה המאחסנים את התוצאות של הפונקציות "AVERAGE", "STDEV.S" ו- "STDEV.P". הם יכולים להיות שימושיים אך לא תמיד נחוצים. ניתן לדלג עליהם לחלוטין בעת ​​חישוב ציון Z באמצעות הנוסחאות הכלליות הבאות, במקום זאת.

הנה אחת המשתמשת בפונקציה "STDEV.S":

= (ערך-ממוצע (ערכים)) / STDEV.S (ערכים)

ואחד המשתמש בפונקציה "STEV.P":

= (ערך-ממוצע (ערכים)) / STDEV.P (ערכים)

כשנכנסים לטווחי התאים עבור "הערכים" בפונקציות, הקפד להוסיף הפניות מוחלטות ("$" באמצעות F4) כך שכאשר אתה 'ממלא' אינך מחשב את סטיית הממוצע או הסטנדרט של טווח אחר של תאים בכל נוסחה.

אם יש לך מערך נתונים גדול, יתכן ויעיל יותר להשתמש בתאי עוזר מכיוון שהוא אינו מחשב את התוצאה של הפונקציות "AVERAGE" ו- "STDEV.S" או "STDEV.P" בכל פעם, תוך חיסכון במשאבי המעבד ו מאיץ את הזמן שנדרש לחישוב התוצאות.

כמו כן, "$ G $ 3" ​​לוקח פחות בתים לאחסון ופחות זיכרון RAM לטעינה מאשר "ממוצע ($ E $ 3: $ E $ 12).". זה חשוב מכיוון שגרסת ה 32 סיביות הרגילה של Excel מוגבלת ל -2 ג'יגה זיכרון RAM (בגרסת 64 סיביות אין מגבלות על כמות RAM ניתן להשתמש).