כיצד לחשב 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% עשה בהשוואה לשאר הכיתה שלהם.

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

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

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

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

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

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

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

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

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

= ממוצע (E2: E13)

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

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

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

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

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

STDEV.S = (E3: E12)

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

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

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

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

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

= STDEV.P (E3: E12)

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

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

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

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

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

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

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

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

לחץ על התא F3 והקלד 49
בחר בתא E3. (ניתן ללחוץ על מקש חץ שמאלי פעם או להשתמש בעכבר)
הקלד את סימן החיסור -
בחר בתא G3 ולאחר מכן לחץ על F4 כדי להוסיף את "$" תווים כדי להפוך את "מוחלט" התייחסות לתא (זה יהיה מחזור דרך "G3"> "$ז$3 ">" G$3 ">"$G3 ">" G3 "אם תמשיך ללחוץ F4)
הקלד )
בחר בתא H3 (או I3 אם אתה משתמש "STDEV.P") ולחץ על F4 כדי להוסיף את שתי התווים "$".
לחץ אנטר

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

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

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

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

חישוב Z- ציון ללא שימוש "עוזר" תאים

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

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

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

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

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

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

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

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