Jak obliczyć odchylenie standardowe w Excelu
Odchylenie standardowe to z miara zmienności zbioru obserwacji wokół jego średniej arytmetycznej. Innymi słowy w pewnych dziedzinach można również traktować je jako odchylanie od wartości oczekiwanej. Dla zbioru obserwacji takimi jak oceny w klasie możemy powiedzieć czy poziom jest równomierny, czy może mamy duże dysproporcje pomiędzy uczniami. Natomiast w przypadku inwestora analizujące historyczne stopy zwrotów dla danej spółki odchylenie będzie dodatkową miarą ryzyka, ponieważ pozwoli do pewnego stopnia ocenić jaki może być poziom wahań, a nie każdy jest gotowy na drastyczne spadki, nawet jeśli będą one równoważone w przyszłości.
Statystycznie można rzecz, że odchylenie standardowe pozwala rzucić światło na poziom rozproszenia populacji lub próbki z obserwacji, w zależności o jakim odchyleniu mówimy. To pierwsze dotyczy całego zbioru – mamy komplet danych. To drugie stanowi jedynie próbkę, fragment informacji. Czyli jest formą przybliżenia. Ponadto odchylenie jest mierzone tą samą miarą co obserwacje, których dotyczy. Czyli jak mówimy o notowaniach to będą to najpewniej złotówki, a jeśli temperatura to pewnie stopnie Celsjusza.
Wzór na odchylenie standardowe
Jak należy liczyć odchylenie standardowe ? Na początek będziemy potrzebować średniej arytmetycznej, czyli w skrócie sumy wartości podzielonej przez ilość.
,gdzie
- μ – średnia arytmetyczna populacji
- xi – dana obserwacja
- N – liczba obserwacji
Będzie ona potrzebna aby policzyć jaka jest różnica między średnią, a daną obserwacją, dla każdej obserwacji i kolejno zsumować te wartości podniesione do drugiej potęgi.
Tę wartość należy kolejno podzielić przez liczbę obserwacji oraz spierwiastkować.
Kalkulacja wprost ze wzoru
Idąc dalej powyższymi krokami możemy policzyć odchylenie standardowe dla wybranych wartości, przy czym zakładamy, że jest to cała populacja. Wtedy kalkulacja dla 10 obserwacji może wyglądać jak niżej:
Można przyjąć, że dla zadanych danych odchylenie standardowe sugeruje rozproszenie danych na poziomie 2,12. Dla porównania, gdyby obserwacje były równe to odchylenie byłoby zerowe.
Dla obserwacji zbliżonych odchylenie będzie niewielkie.
W przypadku wartości bardziej zróżnicowanych będzie większe
Kalkulacja poprzez wbudowane formuły
Excel zawiera własne funkcje do kalkulacji odchylenia standardowego. Przykład powyższy można przeliczyć również formułą =ODCH.STAND.POPUL(A2:A11)
Dodatkowo Excel oferuje inne formuły. Powyższa pomija wartości logiczne i tekstowe ale jest jeszcze funkcja:
=ODCH.STANDARD.POPUL.A() – działa analogicznie do formuły powyżej ale dodatkowo uwzględnia wartości logiczne oraz tekstowe. Przy czym tekstowe i wartość fałsz są traktowane jako 0, natomiast prawda jako 1.
Różnicę w ich działaniu możemy przedstawić w poniższy sposób.
Do tego dochodzą funkcje, które działają analogicznie jak wyżej ale dotyczą odchylenie standardowego ale liczonego z próbki.
=ODCH.STANDARD.PRÓBKI()
=ODCH.STANDARDOWE.A()
Główna różnica jest oczywista – jedna miara jest liczona na całej populacji a druga na próbce, i cała reszta konsekwencji, która za tym idzie.
Jeśli chodzi o różnice w sposobie kalkulacji to jedna zasadnicza jest taka, że w mianowniku, gdzie podajemy liczbę obserwacji, w przypadku próbki interesuje nas liczba obserwacji w próbce pomniejszona on 1.
,gdzie
- s – odchylenie standardowe próbki
- n – liczebność próbki
- x̄ – średnia arytmetyczna próbki
W odniesieniu do naszego przykładu, zestawienie mogłoby być następujące.
Konkluzja
W Excelu odchylenie standardowe możemy policzyć zarówno wprost na podstawie wzoru jak również wykorzystać jedną z istniejących formuł.
Miara odchylenia jest jedną z powszechnie wykorzystywanych miar zmienności w prawdziwym życiu. Z przykładowych zastosowań możemy wymienić:
- Określanie poziomu ryzyka w branży ubezpieczeniowej.
- Ustalanie przewidywanego poziomu zmian pogodowych na danym obszarze.
- Wcześniej wspomniane kalkulowanie zmienności stóp zwrotu w branży finansowej.
- Przybliżanie zakresu wahań cen na rynku nieruchomości.