Odchylenie standardowe - Excel i Arkusze Google

Ten samouczek pokazuje, jak używać Funkcja odchylenia standardowego Excel w programie Excel, aby obliczyć odchylenie standardowe dla całej populacji.

ODCHYLENIE STANDARDOWE Przegląd funkcji

Funkcja ODCHYLENIE STANDARDOWE Oblicza odchylenie standardowe dla całej populacji.

Aby użyć funkcji arkusza programu Excel ODCHYLENIE STANDARDOWE, wybierz komórkę i wpisz:

(Zwróć uwagę, jak pojawiają się dane wejściowe formuły)

Funkcja ODCHYLENIA STANDARDOWEGO Składnia i wejścia:

1	=ODCH.STANDARDOWE(liczba1,[liczba2];…)

liczby-Wartości do uzyskania standardowej wariancji

Jak obliczyć odchylenie standardowe w programie Excel

Za każdym razem, gdy masz do czynienia z danymi, będziesz chciał przeprowadzić kilka podstawowych testów, które pomogą Ci je zrozumieć. Zazwyczaj zaczynasz od obliczenia średniej za pomocą funkcji Excela ŚREDNIA<>.

Daje to wyobrażenie o tym, gdzie znajduje się „środek” danych. A stamtąd będziesz chciał zobaczyć, jak rozłożone są dane wokół tego środkowego punktu. Tutaj pojawia się odchylenie standardowe.

Program Excel udostępnia szereg funkcji do obliczania odchylenia standardowego - ODCH.STANDARDOWE, ODCH.STANDARDOWE.P, ODCH.STANDARDOWE.S i ODCH.STANDARDOWE. Dojdziemy do nich wszystkich, ale najpierw dowiedzmy się, jakie jest odchylenie standardowe jest, dokładnie.

Co to jest odchylenie standardowe?

Odchylenie standardowe daje wyobrażenie o tym, jak daleko są twoje punkty danych od średniej. Weź następujący zestaw danych z wyników testu na 100:

1	48,49,50,51,52

Średnia tego zbioru danych wynosi 50 (zsumuj wszystkie liczby i podziel przez n, gdzie n to liczba wartości w zakresie).

Teraz spójrz na następny zestaw danych:

1	10,25,50,75,90

Średnia tego zbioru danych to także 50 - ale te dwa zakresy opowiadają zupełnie inną historię. Gdybyś po prostu użył średniej, mógłbyś pomyśleć, że te dwie grupy były mniej więcej równe w swoich umiejętnościach - i przeciętnie są.

Ale w pierwszej grupie mamy 5 osób, które uzyskały bardzo podobne, bardzo przeciętne wyniki. A w drugiej grupie, my, para osób o wysokich lotach, zrównoważona przez parę osób o słabych wynikach, z jedną osobą w środku. ten szerzyć się wyników jest bardzo różna, co sprawia, że ​​twoja interpretacja danych również jest bardzo różna.

Miarą tego spreadu jest odchylenie standardowe.

Jak obliczane jest odchylenie standardowe

Aby zrozumieć, czym jest odchylenie standardowe i jak działa, pomocne może być ręczne przeanalizowanie przykładu. W ten sposób będziesz wiedział, co się dzieje „pod maską”, gdy dojdziemy do funkcji Excela, z których możesz korzystać.

Aby obliczyć odchylenie standardowe, przechodzisz przez ten proces:

1) Oblicz średnią

Weźmy nasz pierwszy zestaw danych powyżej: 48,49,50,51,52

Znamy już średnią (50), którą potwierdziłem tutaj funkcją Excela ŚREDNIA<>:

1	=ŚREDNIA(C4:C8)

2) Odejmij średnią od każdej wartości w zbiorze danych

Zrobiłem to za pomocą następującego wzoru:

1

=C4-$H$4

Nasza średnia jest w H4 i „zablokowałem” odwołanie do komórki, umieszczając znaki dolara przed kolumną i wierszem (naciskając F4). Oznacza to, że mogę skopiować formułę w dół kolumny bez aktualizowania odwołania do komórki.

Wynik:

Zatrzymajmy się teraz na chwilę. Jeśli spojrzysz na nową kolumnę - zobaczysz, że liczby tutaj sumują się do zera. Średnia tych liczb również wynosi zero.

Oczywiście rozrzut naszych danych nie może być zerowy - wiemy, że jest tam pewna zmienność. Potrzebujemy sposobu na przedstawienie tej zmienności, tak aby średnia nie okazała się zerem.

3) Wyrównaj różnice

Możemy to osiągnąć poprzez wyrównanie różnic. Dodajmy więc nową kolumnę i podnieśmy do kwadratu liczby w kolumnie D:

1

=D4*D4

To wygląda lepiej. Teraz mamy pewną zmienność, a wielkość zmienności jest powiązana z odległością każdego wyniku od średniej.

4) Oblicz wariancję - średnią z kwadratów różnic

Następnym krokiem jest uzyskanie średniej z tych kwadratów różnic. Podczas obliczania odchylenia standardowego można to zrobić na dwa sposoby.

• Jeśli używasz dane o populacji, po prostu bierzesz średnią (podsumuj wartości i podziel przez n)
• Jeśli używasz przykładowe dane, sumujesz wartości i dzielisz przez n-1

Dane o populacji oznaczają, że masz „pełny zestaw” swoich danych, na przykład masz dane o każdym uczniu w danej klasie.

Przykładowe dane oznaczają, że nie masz wszystkich swoich danych, tylko próbkę pobraną z większej populacji. Zazwyczaj Twoim celem z przykładowymi danymi jest oszacowanie, jaka jest wartość w większej populacji.

Sondaż opinii politycznej jest dobrym przykładem danych próbnych – badacze przeprowadzają ankietę, powiedzmy, 1000 osób, aby zorientować się, co myśli cały kraj lub stan.

Tutaj nie mamy próbki. Mamy tylko pięciu statystycznie nastawionych członków rodziny, którzy chcą obliczyć odchylenie standardowe testu, który wszyscy wykonali. Mamy wszystkie punkty danych i nie szacujemy większej grupy osób. To są dane o populacji - więc możemy wziąć tutaj średnią:

1	=ŚREDNIA(E4:E8)

OK, więc mamy 2. Ten wynik jest znany jako „wariancja” i jest punktem wyjścia dla wielu testów statystycznych, w tym odchylenia standardowego. Możesz przeczytać więcej o wariancji na jej stronie głównej: jak obliczyć wariancję w Excelu<>.

5) Uzyskaj pierwiastek kwadratowy wariancji

Wcześniej podnieśliśmy nasze liczby do kwadratu, co oczywiście nieco zawyża wartości. Tak więc, aby zrównać liczbę z rzeczywistymi różnicami wyników od średniej, musimy wyprowadzić pierwiastek kwadratowy z wyniku kroku 4:

1	=PIERWIASTEK(H4)

I mamy nasz wynik: odchylenie standardowe wynosi 1,414

Ponieważ mamy pierwiastek kwadratowy naszych wcześniej podniesionych do kwadratu liczb, odchylenie standardowe podane w tych samych jednostkach, co oryginalne dane. Więc nasze odchylenie standardowe wynosi tutaj 1,414 punktów testowych.

Odchylenie standardowe, gdy dane są bardziej rozproszone

Wcześniej mieliśmy drugi przykładowy zakres danych: 10,25,50,75,90

Dla zabawy zobaczmy, co się stanie, gdy obliczymy odchylenie standardowe tych danych:

Wszystkie formuły są dokładnie takie same jak poprzednio (zauważ, że ogólna średnia nadal wynosi 50).

Jedyną rzeczą, która się zmieniła, był rozrzut wyników w kolumnie C. Ale teraz nasze odchylenie standardowe jest znacznie wyższe i wynosi 29,832 punktów testowych.

Oczywiście, ponieważ mamy tylko 5 punktów danych, bardzo łatwo zauważyć, że rozkład wyników różni się między tymi dwoma zestawami. Ale kiedy masz setki lub tysiące punktów danych, nie możesz tego stwierdzić, po prostu szybko skanując dane. I właśnie dlatego używamy odchylenia standardowego.

Funkcje programu Excel do obliczania odchylenia standardowego

Teraz, gdy wiesz, jak działa odchylenie standardowe, nie musisz przechodzić przez cały proces, aby uzyskać odchylenie standardowe. Możesz po prostu użyć jednej z wbudowanych funkcji programu Excel.

Excel ma w tym celu kilka funkcji:

• P oblicza odchylenie standardowe dla danych populacyjnych (stosując dokładną metodę, której użyliśmy w powyższym przykładzie)
• S oblicza odchylenie standardowe dla danych próbki (stosując metodę n-1, o której wspominaliśmy wcześniej)
• ODCH.STANDARDOWE jest dokładnie taki sam jak STDEV.S. Jest to starsza funkcja, która została zastąpiona przez STDEV.S i STDEV.P.
• STDEVA jest bardzo podobny do STDEV.S, z wyjątkiem tego, że podczas obliczania uwzględnia komórki tekstowe i komórki logiczne (PRAWDA/FAŁSZ).
• ODCH.STANDARDOWE.PA jest bardzo podobny do ODCH.STANDARDOWE.P, z wyjątkiem tego, że podczas obliczania uwzględnia komórki tekstowe i komórki logiczne (PRAWDA/FAŁSZ).

Wow, wiele opcji tutaj! Nie daj się zastraszyć - w zdecydowanej większości przypadków będziesz używać STDEV.P lub STDEV.S.

Przyjrzyjmy się każdemu z nich po kolei, zaczynając od STDEV.P, ponieważ jest to metoda, nad którą właśnie pracowaliśmy.

Funkcja Excel ODCH.STANDARDOWE.P

ODCH.STANDARDOWE.P oblicza odchylenie standardowe dla danych populacyjnych. Używasz go w ten sposób:

1	=ODCH.STANDARDOWE.P(C4:C8)

Definiujesz jeden argument w ODCH.STANDARDOWE.P: zakres danych, dla którego chcesz obliczyć odchylenie standardowe.

Jest to ten sam przykład, który przeszliśmy krok po kroku powyżej, kiedy ręcznie obliczyliśmy odchylenie standardowe. I jak widać powyżej, otrzymujemy dokładnie ten sam wynik - 1,414.

Uwaga ODCH.STANDARDOWE.P ignoruje wszystkie komórki zawierające tekst lub wartości logiczne (PRAWDA/FAŁSZ). Jeśli chcesz je uwzględnić, użyj STDEVPA.

Funkcja Excel ODCH.STANDARDOWE.S

ODCH.STANDARDOWE.S oblicza odchylenie standardowe dla danych próbki. Użyj go w ten sposób:

1	=ODCH.STANDARDOWE.S(C4:C8)

Ponownie, potrzebny jest jeden argument - zakres danych, dla którego chcesz poznać odchylenie standardowe.

Zanim przejdziemy do przykładu, omówmy różnicę między STDEV.S i STDEV.P.

Jak już omówiliśmy, STDEV.S powinno być używane na przykładowych danych - gdy dane są częścią większego zestawu. Załóżmy więc teraz, że w powyższym przykładzie więcej osób przystąpiło do testu. Chcemy oszacować odchylenie standardowe wszystkich, którzy wzięli udział w teście, używając tylko tych pięciu wyników. Teraz używamy przykładowych danych.

Teraz obliczenia różnią się od kroku (4) powyżej, kiedy obliczamy wariancję – średnią kwadratową różnicy każdego wyniku od ogólnej średniej.

Zamiast używać normalnej metody - zsumuj wszystkie wartości i podzielimy przez n, zsumujemy wszystkie wartości i podzielimy przez n-1:

1	=SUMA(E4:E8) / (LICZBA(E4:E8)-1)

W tej formule:

• SUM otrzymuje sumę kwadratów różnic
• LICZBA zwraca nasze n, od którego odejmujemy 1
• Następnie po prostu dzielimy naszą sumę przez n-1

Tym razem średnia z kwadratów różnic wynosi 2,5 (przypominacie sobie, że wcześniej wynosiła 2, więc jest nieco wyższa).

Dlaczego więc dzielimy przez n-1 zamiast n, gdy mamy do czynienia z przykładowymi danymi?

Odpowiedź jest dość złożona, a jeśli próbujesz po prostu analizować liczby, aby zrozumieć swoje dane, nie jest to coś, czym naprawdę musisz się martwić. Tylko upewnij się, że używasz STDEV.S do przykładowych danych i STDEV.P do danych o populacji, a wszystko będzie dobrze.

Jeśli naprawdę chcesz wiedzieć, dlaczego, zobacz stronę główną, jak obliczyć wariancję w Excelu<>.

OK, więc mamy teraz wariancję dla próbki, więc aby uzyskać odchylenie standardowe dla próbki, otrzymalibyśmy pierwiastek kwadratowy z wariancji:

1	=PIERWIASTEK(H4)

Otrzymujemy 1,581.

ODCH.STANDARDOWE.S wykonuje wszystkie powyższe obliczenia za nas i zwraca odchylenie standardowe próbki tylko w jednej komórce. Zobaczmy więc, co z tego wyjdzie…

1	=ODCH.STANDARDOWE.S(C4:C8)

Tak, znowu 1,581.

Funkcja Excel ODCH.STANDARDOWE

Funkcja ODCH.STANDARDOWE Excela działa dokładnie w taki sam sposób jak ODCH.STANDARDOWE.S - to znaczy oblicza odchylenie standardowe dla próbki danych.

Używasz go w ten sam sposób:

1	=ODCH.STANDARDOWE(C4:C8)

Ponownie otrzymujemy ten sam wynik.

Ważna uwaga: STDEV to "Funkcja zgodności", co w zasadzie oznacza, że ​​Microsoft się jej pozbywa. Na razie nadal działa, więc wszystkie starsze arkusze kalkulacyjne będą nadal działać normalnie. Ale w przyszłych wersjach programu Excel Microsoft może całkowicie go porzucić, więc powinieneś używać STDEV.S zamiast STDEV tam, gdzie to możliwe.

Funkcja Excel STDEVA

ODCH.STANDARDOWE.A służy również do obliczania odchylenia standardowego dla próbki, ale ma kilka ważnych różnic, o których musisz wiedzieć:

• Wartości TRUE są liczone jako 1
• Wartości FALSE są liczone jako 0
• Ciągi tekstowe są liczone jako 0

Użyj go w następujący sposób:

1	=ODCH.STANDARDOWE(C4:C8)

Czterech kolejnych przyjaciół i członków rodziny podało wyniki swoich testów. Są one pokazane w kolumnie C, a kolumna D wskazuje, jak STDEVA interpretuje te dane.

Ponieważ komórki te są interpretowane jako tak niskie wartości, tworzy to znacznie większy rozrzut między naszymi danymi niż widzieliśmy wcześniej, co znacznie zwiększyło odchylenie standardowe, obecnie wynoszące 26,246.

Funkcja Excel STDEVPA

ODCH.STANDARD.POPUL. oblicza odchylenie standardowe dla populacji w taki sam sposób jak ODCH.STANDARDOWE.POP. Jednak uwzględnia w obliczeniach również wartości logiczne i ciągi tekstowe, które są interpretowane w następujący sposób:

• Wartości TRUE są liczone jako 1
• Wartości FALSE są liczone jako 0
• Ciągi tekstowe są liczone jako 0

Używasz go w ten sposób:

1	=ODCH.STANDARD.POP(C4:C12)

Filtrowanie danych przed obliczeniem odchylenia standardowego

W prawdziwym świecie nie zawsze będziesz mieć dokładne dane, których potrzebujesz, w ładnej, uporządkowanej tabeli. Często będziesz mieć duży arkusz kalkulacyjny pełen danych, które musisz przefiltrować przed obliczeniem odchylenia standardowego.

Możesz to zrobić bardzo łatwo za pomocą funkcji bazy danych Excela: DSTDEV (dla próbek) i DSTDEVP (dla populacji).

Te funkcje umożliwiają stworzenie tabeli kryteriów, w której można zdefiniować wszystkie potrzebne filtry. Funkcje stosują te filtry zakulisowo przed zwróceniem odchylenia standardowego. W ten sposób nie musisz dotykać Autofiltra ani wyciągać danych do osobnego arkusza – DSTDEV i SDTDEVVP mogą to wszystko zrobić za Ciebie.

Dowiedz się więcej na stronie głównej funkcji Excel DSTDEV i DSTDEVP<>.

Funkcja ODCHYLENIA STANDARDOWEGO w Arkuszach Google

Funkcja ODCHYLENIA STANDARDOWEGO działa dokładnie tak samo w Arkuszach Google jak w Excelu: