=ODCH.STANDARD.PRÓBKI(B2:B9) (po angielsku =STDEV.S(B2:B9)) zwraca odchylenie standardowe wartości z B2:B9 traktowanych jako próba, a =ODCH.STAND.POPUL(B2:B9) (STDEV.P) traktuje je jako całą populację. Odchylenie standardowe mówi, jak daleko wartości zwykle leżą od swojej średniej: małe oznacza, że wartości są blisko siebie. Tabele pokazują formuły po angielsku, ale możesz w nich wpisywać formuły także po polsku, ze średnikami.
| 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 |
=ODCH.STANDARD.PRÓBKI(B2:B9)Średnia wyników to 90. ODCH.STAND.POPUL daje dokładnie 12, a ODCH.STANDARD.PRÓBKI około 12,83. Zmień wynik Hala na 90, a oba mocno spadną: jedna wartość daleko od reszty bardzo zmienia odchylenie standardowe.
ODCH.STANDARD.PRÓBKI a ODCH.STAND.POPUL: którą wybrać
Te dwie funkcje różnią się jednym krokiem. ODCH.STAND.POPUL dzieli sumę kwadratów różnic przez liczbę wartości, n. ODCH.STANDARD.PRÓBKI dzieli przez n minus 1, co daje trochę większy wynik. Powód: rozrzut próby mierzy się wokół jej własnej średniej, która leży bliżej jej wartości niż prawdziwa średnia, więc dzielenie przez n zaniżałoby rozrzut całej grupy.
- ODCH.STAND.POPUL (populacja): zakres zawiera każdą wartość, którą chcesz opisać. Wyniki wszystkich 8 uczniów w tej klasie, gdy pytanie dotyczy tej klasy.
- ODCH.STANDARD.PRÓBKI (próba): zakres to część czegoś większego. 8 uczniów wybranych ze szkoły liczącej 600 osób, użytych do oszacowania rozrzutu w całej szkole.
W razie wątpliwości użyj ODCH.STANDARD.PRÓBKI. Większość danych w arkuszu to próby, a narzędzia statystyczne (testy t, przedziały ufności) oczekują wersji dla próby. Przy setkach wartości oba wyniki są prawie takie same; przy 8 wartościach różnica wynosi około 7%.
Starsze funkcje ODCH.STANDARDOWE (STDEV) i STDEVP dają te same wyniki co ODCH.STANDARD.PRÓBKI i ODCH.STAND.POPUL i nadal działają w każdej wersji Excela. STDEVA i STDEVPA liczą też tekst jako 0, a PRAWDA jako 1, a rzadko o to chodzi.
Jak Excel to liczy, krok po kroku
Ta tabela robi ręcznie to, co ODCH.STANDARD.PRÓBKI robi jednym wywołaniem: odejmuje średnią od każdej wartości, podnosi różnice do kwadratu, sumuje je, dzieli przez n minus 1 i wyciąga pierwiastek.
| 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 |
=PIERWIASTEK(F4)Suma kwadratów wynosi 34, wariancja próby 6,8, a jej pierwiastek (około 2,61) zgadza się z ODCH.STANDARD.PRÓBKI w F6. Zmień F4 na =F2/F3, a dostaniesz wariancję populacji; jej pierwiastek to wynik ODCH.STAND.POPUL.
Wariancja: WARIANCJA.PRÓBKI i WARIANCJA.POP
Wariancja to odchylenie standardowe przed wyciągnięciem pierwiastka: =WARIANCJA.PRÓBKI(A2:A7) (VAR.S) daje 6,8 dla danych powyżej, a =WARIANCJA.POP(A2:A7) (VAR.P) dzieli przez n zamiast przez n minus 1. Wariancja jest w jednostkach do kwadratu (punkty do kwadratu, dolary do kwadratu), więc w raportach łatwiej czytać odchylenie standardowe. WARIANCJA (VAR) i VARP to stare nazwy.
Średnia plus minus jedno odchylenie standardowe
Popularny sposób podawania rozrzutu to „średnia ± SD”, na przykład 90 ± 12,8. Oba końce tego przedziału to proste formuły, a reguła formatowania warunkowego może oznaczyć wartości poza nim.
| 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 |
=ODCH.STANDARD.PRÓBKI(B2:B9)Reguła wyróżnia Anę i Hala, dwa wyniki poza przedziałem od około 77,2 do 102,8. W danych o rozkładzie normalnym około dwóch trzecich wartości mieści się w jednym odchyleniu standardowym od średniej, a około 95% w dwóch. Aby zapisać w komórce tekst „90.0 ± 12.8”, użyj =TEXT(E2,"0.0")&" ± "&TEXT(E3,"0.0") (postać angielska; w polskim Excelu funkcja TEKST z kodem 0,0).
Odchylenie standardowe z warunkiem
Nie ma funkcji STDEVIF. Wstaw JEŻELI do ODCH.STANDARD.PRÓBKI: JEŻELI zwraca wynik tam, gdzie region pasuje, i FAŁSZ w pozostałych wierszach, a ODCH.STANDARD.PRÓBKI pomija wartości FAŁSZ.
| 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 |
=ODCH.STANDARD.PRÓBKI(JEŻELI(A2:A9=D2;B2:B9))Sprzedaż North zmienia się znacznie bardziej niż South. W Excelu 365 i 2021 ta formuła działa tak, jak jest wpisana: =ODCH.STANDARD.PRÓBKI(JEŻELI(A2:A9=D2;B2:B9)). W Excelu 2019 i starszym zatwierdź ją klawiszami Ctrl+Shift+Enter (Cmd+Shift+Enter na Macu), inaczej zwróci zły wynik albo #ARG! (po angielsku #VALUE!). W Excelu 365 możesz też napisać =ODCH.STANDARD.PRÓBKI(FILTRUJ(B2:B9;A2:A9=D2)).
Spróbuj: rozrzut czasów dostawy
| 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 |
Twoja kolej: Zamówienia w B2:B8 to próba ze wszystkich zamówień. W E2 oblicz ich odchylenie standardowe.
Wskazówka: próba oznacza funkcję dla próby (w angielskim Excelu ta z końcówką .S).
Częsty błąd: wiersz sumy w zakresie
Zakres taki jak B2:B10, który obejmuje też sumę albo średnią na dole kolumny, traktuje to podsumowanie jako jeszcze jeden punkt danych, a odchylenie standardowe wychodzi o wiele za duże. Zaznaczaj tylko wiersze z danymi albo trzymaj podsumowania w innej kolumnie, jak w tabelach na tej stronie. Puste komórki i tekst w zakresie są pomijane, ale 0 to wartość i się liczy: brakujący wynik wpisany jako 0 poszerza rozrzut tak samo jak prawdziwe 0. Aby sprawdzić, ile wartości zostało użytych, wpisz obok wyniku =ILE.LICZB(B2:B9).
Najczęściej zadawane pytania
Jaka jest formuła na odchylenie standardowe w Excelu?
=ODCH.STANDARD.PRÓBKI(B2:B9) dla próby i =ODCH.STAND.POPUL(B2:B9) dla całej populacji. Obie pomijają tekst i puste komórki w zakresie.
Użyć ODCH.STANDARD.PRÓBKI czy ODCH.STAND.POPUL?
Używaj ODCH.STAND.POPUL tylko wtedy, gdy zakres zawiera każdego członka opisywanej grupy, na przykład wszystkie 8 osób w zespole. Gdy dane to próba używana do opisu czegoś większego (część klientów, część przebiegów testu), użyj ODCH.STANDARD.PRÓBKI. Przy wielu wartościach wyniki są bliskie; przy niewielu ODCH.STANDARD.PRÓBKI jest wyraźnie większe.
Czym różni się ODCH.STANDARDOWE od ODCH.STANDARD.PRÓBKI?
Wynikiem niczym. ODCH.STANDARDOWE (STDEV) i STDEVP to nazwy sprzed 2010 roku, zachowane dla zgodności; ODCH.STANDARD.PRÓBKI (STDEV.S) i ODCH.STAND.POPUL (STDEV.P) to nazwy obecne. Arkusze Google akceptują oba zestawy angielskich nazw.
Jak obliczyć wariancję w Excelu?
Użyj =WARIANCJA.PRÓBKI(B2:B9) dla próby i =WARIANCJA.POP(B2:B9) dla populacji. Wariancja to kwadrat odchylenia standardowego, więc =ODCH.STANDARD.PRÓBKI(B2:B9)^2 daje tę samą liczbę co WARIANCJA.PRÓBKI.
Jak obliczyć błąd standardowy w Excelu?
Excel nie ma funkcji na błąd standardowy średniej. Podziel odchylenie standardowe próby przez pierwiastek z liczby wartości: =ODCH.STANDARD.PRÓBKI(B2:B9)/PIERWIASTEK(ILE.LICZB(B2:B9)).
