Odchylenie standardowe w Excelu: ODCH.STANDARD.PRÓBKI czy ODCH.STAND.POPUL?

Odchylenie standardowe pokazuje, jak bardzo wartości w serii odbiegają średnio od swojej średniej. W Excelu obliczysz je funkcją =ODCH.STANDARD.PRÓBKI(B2:B7), gdy dane są próbką (najczęstszy przypadek), albo =ODCH.STAND.POPUL(B2:B7), gdy obejmują całą badaną populację.
Wynik ma tę samą jednostkę co dane: złote przy sprzedaży, dni przy czasie dostawy. Im jest większy, tym bardziej wartości są rozproszone.
Przykład: dwa sklepy, ta sama średnia, inny rozrzut
Tygodniowa sprzedaż dwóch sklepów w ciągu sześciu tygodni:
| A | B | C | |
|---|---|---|---|
| 1 | Tydzień | Kraków (zł) | Gdańsk (zł) |
| 2 | T1 | 1 200 | 900 |
| 3 | T2 | 1 250 | 1 500 |
| 4 | T3 | 1 180 | 1 100 |
| 5 | T4 | 1 220 | 1 400 |
| 6 | T5 | 1 150 | 1 000 |
| 7 | T6 | 1 200 | 1 300 |
Oba sklepy mają tę samą średnią: =ŚREDNIA(B2:B7) i =ŚREDNIA(C2:C7) zwracają 1 200 zł. Odchylenie standardowe pokazuje to, czego nie widać w średniej:
=ODCH.STANDARD.PRÓBKI(B2:B7)zwraca 34,06 zł dla Krakowa,=ODCH.STANDARD.PRÓBKI(C2:C7)zwraca 236,64 zł dla Gdańska.
Kraków sprzedaje co tydzień mniej więcej tyle samo. Gdańsk ma na przemian dobre i słabe tygodnie, więc tam trudniej zaplanować zapasy i grafik. Widać to na wykresie liniowym z obiema kolumnami: linia Krakowa jest prawie płaska, a linia Gdańska rośnie i spada z tygodnia na tydzień.
ODCH.STANDARD.PRÓBKI czy ODCH.STAND.POPUL?
Obie funkcje liczą tak samo, z jedną różnicą: suma kwadratów odchyleń jest dzielona przez n − 1 w ODCH.STANDARD.PRÓBKI i przez n w ODCH.STAND.POPUL (n = liczba wartości).
- ODCH.STANDARD.PRÓBKI (próbka): twoje dane to część większego zbioru i chcesz wyciągnąć ogólny wniosek. Sześć tygodni sprzedaży, żeby ocenić stabilność sklepu, 50 ankietowanych klientów z 2 000, 20 sprawdzonych sztuk z partii produkcyjnej: to próbki.
- ODCH.STAND.POPUL (populacja): dane obejmują wszystkie elementy, które cię interesują, i nie uogólniasz. Oceny wszystkich 28 uczniów klasy, żeby opisać właśnie tę klasę, pensje wszystkich pracowników małej firmy: to cała populacja.
W razie wątpliwości wybierz ODCH.STANDARD.PRÓBKI. Dla naszego przykładu:
| Sklep | ODCH.STANDARD.PRÓBKI | ODCH.STAND.POPUL |
|---|---|---|
| Kraków | 34,06 zł | 31,09 zł |
| Gdańsk | 236,64 zł | 216,02 zł |
ODCH.STANDARD.PRÓBKI zawsze daje nieco większy wynik. Przy 6 wartościach różnica jest wyraźna, przy kilkuset oba wyniki są prawie takie same.
Pozostałe funkcje odchylenia standardowego
Na liście funkcji znajdziesz sześć nazw. Oto co oznaczają:
| Funkcja | Dane | Warto wiedzieć |
|---|---|---|
| ODCH.STANDARD.PRÓBKI | Próbka | Wybór domyślny. |
| ODCH.STAND.POPUL | Populacja | Gdy masz wszystkie wartości. |
| ODCH.STANDARDOWE | Próbka | Starsza nazwa, ten sam wynik co ODCH.STANDARD.PRÓBKI. Zostawiona dla zgodności ze starszymi skoroszytami. |
| ODCH.STANDARD.POPUL | Populacja | Starsza nazwa, ten sam wynik co ODCH.STAND.POPUL. |
| ODCH.STANDARDOWE.A | Próbka | Uwzględnia też tekst (jako 0) i wartości logiczne (PRAWDA = 1, FAŁSZ = 0) w zakresie. |
| ODCH.STANDARD.POPUL.A | Populacja | Ta sama zasada co ODCH.STANDARDOWE.A, dla populacji. |
ODCH.STANDARD.PRÓBKI i ODCH.STAND.POPUL pomijają puste komórki, tekst i wartości logiczne w zakresie. Wersje z „.A” przydają się rzadko: tylko wtedy, gdy komórka typu „nb” lub FAŁSZ ma liczyć się jako zero.
Jak czytać wynik
Samo odchylenie standardowe mówi niewiele: 236 zł to dużo przy sprzedaży 1 200 zł i prawie nic przy 100 000 zł. Żeby porównywać, podziel je przez średnią. To współczynnik zmienności:
=ODCH.STANDARD.PRÓBKI(B2:B7)/ŚREDNIA(B2:B7)
W formacie procentowym formuła daje 2,8% dla Krakowa i 19,7% dla Gdańska. Ten sam wskaźnik pozwala porównać serie o różnych jednostkach lub skali.
Odchylenie standardowe równe 0 oznacza, że wszystkie wartości są identyczne. Nigdy nie jest ujemne.
Odchylenie standardowe z warunkiem
Jeśli dane są w formie listy (Miasto w kolumnie A, Kwota w kolumnie B, wiersze od 2 do 13), nie znajdziesz funkcji typu „ODCH.STANDARD.JEŻELI”. Zagnieźdź JEŻELI w ODCH.STANDARD.PRÓBKI:
=ODCH.STANDARD.PRÓBKI(JEŻELI(A2:A13="Kraków";B2:B13))
JEŻELI zwraca kwotę dla wierszy „Kraków” i FAŁSZ dla pozostałych, a funkcja to pomija. W Excelu 365 i 2021 wystarczy Enter. W starszych wersjach zatwierdź formułę klawiszami Ctrl + Shift + Enter (formuła tablicowa), inaczej wynik będzie błędny.
W Excelu 365 lub 2021 czytelniejsza jest funkcja FILTRUJ:
=ODCH.STANDARD.PRÓBKI(FILTRUJ(B2:B13;A2:A13="Kraków"))
Błędy i nieoczekiwane wyniki
#DZIEL/0!
ODCH.STANDARD.PRÓBKI potrzebuje co najmniej dwóch liczb, bo dzieli przez n − 1. Zakres z jedną liczbą albo z samym tekstem zwraca #DZIEL/0!. Sprawdź, ile liczb Excel naprawdę widzi: =ILE.LICZB(B2:B7).
Wynik za mały lub za duży
- Liczby zapisane jako tekst: zaimportowane kwoty (wyrównane do lewej, z zielonym trójkątem) są pomijane bez ostrzeżenia. Jeśli
=ILE.LICZB(B2:B7)zwraca mniej niż liczba wierszy, przekonwertuj je (przycisk ostrzeżenia, potem „Konwertuj na liczbę”). - Zero zamiast pustej komórki: tydzień bez danych wpisany jako 0 liczy się jak tydzień ze sprzedażą 0 zł i mocno zawyża odchylenie. Zostaw komórkę pustą, jeśli wartości nie znasz.
- Wiersz sumy w zakresie: jeśli zakres obejmuje wiersz Suma, ta odległa wartość zaburza całe obliczenie.
Gdy wartości mają różną wagę, przeczytaj nasz poradnik o średniej ważonej w Excelu. Dokumentacja Microsoft szczegółowo opisuje funkcję ODCH.STANDARD.PRÓBKI.


