=STDEV.S(B2:B9) מחזירה את סטיית התקן של הערכים ב-B2:B9 כשמתייחסים אליהם כמדגם, ו-=STDEV.P(B2:B9) מתייחסת אליהם כאוכלוסייה כולה. סטיית התקן אומרת כמה רחוק ערכים נמצאים בדרך כלל מהממוצע שלהם: סטיית תקן קטנה פירושה שהערכים קרובים זה לזה.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Measure | Result | |
| 2 | Ana | 72 | STDEV.S | 12.82853961 | |
| 3 | Ben | 84 | STDEV.P | 12 | |
| 4 | Cleo | 84 | Average | 90 | |
| 5 | Dan | 84 | |||
| 6 | Eve | 90 | |||
| 7 | Finn | 90 | |||
| 8 | Gia | 102 | |||
| 9 | Hal | 114 |
ממוצע הציונים הוא 90. STDEV.P נותנת בדיוק 12 ו-STDEV.S נותנת בערך 12.83. שנו את הציון של Hal ל-90 ושתיהן יורדות בחדות: ערך אחד שרחוק מהשאר מזיז את סטיית התקן הרבה.
STDEV.S מול STDEV.P: באיזו להשתמש
השתיים נבדלות בצעד אחד. STDEV.P מחלקת את סכום ריבועי ההפרשים במספר הערכים, n. STDEV.S מחלקת ב-n פחות 1, וזה הופך את התוצאה לקצת גדולה יותר. הסיבה: הפיזור של מדגם נמדד סביב הממוצע של המדגם עצמו, שקרוב יותר לערכים שלו מאשר הממוצע האמיתי, ולכן חלוקה ב-n הייתה מקטינה את הפיזור של הקבוצה כולה.
- STDEV.P (אוכלוסייה): הטווח מכיל כל ערך שאתם רוצים לתאר. הציונים של כל 8 התלמידים בכיתה הזו, כשהשאלה היא על הכיתה הזו.
- STDEV.S (מדגם): הטווח הוא חלק ממשהו גדול יותר. 8 תלמידים שנבחרו מבית ספר של 600, שמשמשים להערכת הפיזור של כל בית הספר.
כשיש ספק, השתמשו ב-STDEV.S. רוב הנתונים בגיליון הם מדגם, וכלים סטטיסטיים (מבחני t, רווחי סמך) מצפים לגרסת המדגם. עם מאות ערכים שתי התוצאות כמעט זהות; עם 8 ערכים הפער הוא בערך 7%.
הפונקציות הישנות STDEV ו-STDEVP נותנות את אותן תוצאות כמו STDEV.S ו-STDEV.P ועדיין עובדות בכל גרסה של אקסל. STDEVA ו-STDEVPA סופרות גם טקסט כ-0 ו-TRUE כ-1, ולרוב זה לא מה שרוצים.
איך אקסל מחשב את זה, צעד אחר צעד
הגיליון הזה עושה ידנית את מה ש-STDEV.S עושה בקריאה אחת: מחסירים את הממוצע מכל ערך, מעלים את ההפרשים בריבוע, מחברים אותם, מחלקים ב-n פחות 1, ומוציאים שורש.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Value | Difference | Squared | Result | ||
| 2 | 4 | -2 | 4 | Sum of squares | 34 | |
| 3 | 8 | 2 | 4 | n | 6 | |
| 4 | 6 | 0 | 0 | Variance (sample) | 6.8 | |
| 5 | 5 | -1 | 1 | Std dev (sample) | 2.607680962 | |
| 6 | 3 | -3 | 9 | STDEV.S | 2.607680962 | |
| 7 | 10 | 4 | 16 |
סכום הריבועים הוא 34, השונות של המדגם היא 6.8, והשורש שלה (בערך 2.61) תואם את STDEV.S ב-F6. שנו את F4 ל-=F2/F3 ותקבלו את השונות של האוכלוסייה; השורש שלה הוא מה ש-STDEV.P מחזירה.
שונות: VAR.S ו-VAR.P
השונות היא סטיית התקן לפני השורש: =VAR.S(A2:A7) נותנת 6.8 לנתונים שלמעלה, ו-=VAR.P(A2:A7) מחלקת ב-n במקום ב-n פחות 1. השונות היא ביחידות בריבוע (נקודות בריבוע, דולרים בריבוע), ולכן לדיווח, סטיית התקן קלה יותר לקריאה. VAR ו-VARP הם השמות הישנים.
ממוצע ועוד או פחות סטיית תקן אחת
דרך נפוצה לדווח על פיזור היא "ממוצע ± סטיית תקן", למשל 90 ± 12.8. שני הקצוות של הטווח הזה הם נוסחאות פשוטות, וכלל של עיצוב מותנה יכול לסמן את הערכים שמחוץ לו.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Measure | Value | |
| 2 | Ana | 72 | Mean | 90.0 | |
| 3 | Ben | 84 | SD | 12.8 | |
| 4 | Cleo | 84 | Low | 77.2 | |
| 5 | Dan | 84 | High | 102.8 | |
| 6 | Eve | 90 | |||
| 7 | Finn | 90 | |||
| 8 | Gia | 102 | |||
| 9 | Hal | 114 |
הכלל מדגיש את Ana ואת Hal, שני הציונים שמחוץ לטווח של בערך 77.2 עד 102.8. בנתונים שמתפלגים נורמלית בערך שני שלישים מהערכים נמצאים בטווח של סטיית תקן אחת מהממוצע, ובערך 95% בטווח של שתיים. כדי לעצב את הטקסט בתא כ-"90.0 ± 12.8", השתמשו ב-=TEXT(E2,"0.0")&" ± "&TEXT(E3,"0.0").
סטיית תקן עם תנאי
אין פונקציה STDEVIF. שימו IF בתוך STDEV.S: IF מחזירה את הציון כשהאזור מתאים ו-FALSE בכל מקום אחר, ו-STDEV.S מדלגת על ערכי ה-FALSE.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Region | STDEV.S | |
| 2 | North | 120 | North | 25.61737691 | |
| 3 | South | 95 | South | 3.872983346 | |
| 4 | North | 150 | |||
| 5 | South | 101 | |||
| 6 | North | 90 | |||
| 7 | South | 98 | |||
| 8 | North | 135 | |||
| 9 | South | 104 |
המכירות של North משתנות הרבה יותר מאלה של South. ב-Excel 365 וב-2021 הנוסחה הזו עובדת כמו שהיא. ב-Excel 2019 ובגרסאות קודמות, סיימו אותה עם Ctrl+Shift+Enter (Cmd+Shift+Enter ב-Mac), אחרת היא מחזירה תוצאה שגויה או #VALUE!. ב-Excel 365 אפשר גם לכתוב =STDEV.S(FILTER(B2:B9,A2:A9=D2)).
נסו בעצמכם: הפיזור של זמני משלוח
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Days | Std dev | ||
| 2 | A1 | 3 | |||
| 3 | A2 | 5 | |||
| 4 | A3 | 4 | |||
| 5 | A4 | 9 | |||
| 6 | A5 | 3 | |||
| 7 | A6 | 4 | |||
| 8 | A7 | 6 |
תורכם: ההזמנות ב-B2:B8 הן מדגם מכל ההזמנות. ב-E2, חשבו את סטיית התקן שלהן.
רמז: מדגם פירושו הפונקציה שנגמרת ב-.S.
טעות נפוצה: שורת הסכום בתוך הטווח
טווח כמו B2:B10 שכולל גם סכום או ממוצע בתחתית העמודה מתייחס לסיכום הזה כעוד נקודת נתונים, וסטיית התקן יוצאת גדולה מדי בהרבה. בחרו רק את שורות הנתונים, או שמרו סיכומים בעמודה אחרת, כמו בגיליונות שבעמוד הזה. תאים ריקים וטקסט בטווח מתעלמים מהם, אבל 0 הוא ערך והוא נספר: ציון חסר שהוקלד כ-0 מרחיב את הפיזור בדיוק כמו 0 אמיתי. כדי לבדוק כמה ערכים שימשו, שימו =COUNT(B2:B9) ליד התוצאה.
שאלות נפוצות
מה הנוסחה לסטיית תקן באקסל?
=STDEV.S(B2:B9) למדגם ו-=STDEV.P(B2:B9) לאוכלוסייה מלאה. שתיהן מתעלמות מטקסט ומתאים ריקים בטווח.
האם להשתמש ב-STDEV.S או ב-STDEV.P?
השתמשו ב-STDEV.P רק כשהטווח מכיל כל חבר בקבוצה שאתם מתארים, למשל כל 8 האנשים בצוות. כשהנתונים הם מדגם שמשמש לתיאור משהו גדול יותר (חלק מהלקוחות, חלק מהריצות), השתמשו ב-STDEV.S. עם הרבה ערכים השתיים קרובות; עם מעט ערכים STDEV.S גדולה יותר באופן ניכר.
מה ההבדל בין STDEV ל-STDEV.S?
אין הבדל בתוצאה. STDEV ו-STDEVP הם השמות מלפני 2010, שנשמרו לצורך תאימות; STDEV.S ו-STDEV.P הם השמות הנוכחיים. Google Sheets מקבלת את שתי קבוצות השמות.
איך מחשבים שונות באקסל?
השתמשו ב-=VAR.S(B2:B9) למדגם וב-=VAR.P(B2:B9) לאוכלוסייה. השונות היא סטיית התקן בריבוע, ולכן =STDEV.S(B2:B9)^2 נותנת את אותו מספר כמו VAR.S.
איך מחשבים טעות תקן באקסל?
לאקסל אין פונקציה לטעות התקן של הממוצע. חלקו את סטיית התקן של המדגם בשורש של מספר הערכים: =STDEV.S(B2:B9)/SQRT(COUNT(B2:B9)).
