Odchylenie standardowe funkcji w programie Excel. Co to jest odchylenie standardowe - użycie funkcji odchylenia standardowego do obliczenia odchylenia standardowego w programie Excel

Jednym z głównych narzędzi analizy statystycznej jest obliczenie odchylenia standardowego. Wskaźnik ten pozwala oszacować odchylenie standardowe dla próby lub dla populacji ogólnej. Nauczmy się korzystać ze wzoru na odchylenie standardowe w Excelu.

Zdefiniujmy od razu, czym jest odchylenie standardowe i jak wygląda jego wzór. Ta wartość jest pierwiastkiem kwadratowym średniej arytmetycznej kwadratów różnicy między wszystkimi wartościami szeregu i ich średnią arytmetyczną. Istnieje identyczna nazwa tego wskaźnika - odchylenie standardowe. Obie nazwy są całkowicie równoważne.

Ale oczywiście w Excelu użytkownik nie musi tego obliczać, ponieważ program robi wszystko za niego. Nauczmy się obliczać odchylenie standardowe w Excelu.

Obliczenia w Excelu

Możesz obliczyć określoną wartość w Excelu za pomocą dwóch specjalnych funkcji STDEV.V(według próbki) i ODCH.STANDARDOWE.G(według ogółu ludności). Zasada ich działania jest absolutnie taka sama, ale można je nazwać na trzy sposoby, które omówimy poniżej.

Metoda 1: Kreator funkcji

Metoda 2: Zakładka Formuły

Metoda 3: Ręczne wprowadzenie formuły

Istnieje również sposób, w którym w ogóle nie trzeba wywoływać okna argumentów. Aby to zrobić, wprowadź formułę ręcznie.

Jak widać, mechanizm obliczania odchylenia standardowego w Excelu jest bardzo prosty. Użytkownik musi tylko wprowadzić liczby z populacji lub linki do komórek, które je zawierają. Wszystkie obliczenia są wykonywane przez sam program. Znacznie trudniej jest zrozumieć, czym jest obliczony wskaźnik i jak wyniki obliczeń można zastosować w praktyce. Ale zrozumienie tego należy już bardziej do sfery statystyki niż do nauki pracy z oprogramowaniem.

Dyspersja jest miarą dyspersji, która opisuje względne odchylenie między wartościami danych a średnią. Jest to najczęściej stosowana miara dyspersji w statystyce, obliczana przez zsumowanie, podniesione do kwadratu, odchylenie każdej wartości danych od średniej. Wzór na obliczenie wariancji przedstawiono poniżej:

s 2 - wariancja próby;

x cf jest średnią wartością próbki;

N — wielkość próby (liczba wartości danych),

(x i – x cf) to odchylenie od wartości średniej dla każdej wartości zbioru danych.

Aby lepiej zrozumieć formułę, spójrzmy na przykład. Nie lubię gotować, więc rzadko to robię. Aby jednak nie umrzeć z głodu, co jakiś czas muszę podejść do pieca, aby zrealizować plan nasycenia organizmu białkami, tłuszczami i węglowodanami. Poniższy zestaw danych pokazuje, ile razy w miesiącu Renat gotuje jedzenie:

Pierwszym krokiem w obliczeniu wariancji jest określenie średniej próbki, która w naszym przykładzie wynosi 7,8 razy w miesiącu. Pozostałe obliczenia można ułatwić za pomocą poniższej tabeli.

Końcowa faza obliczania wariancji wygląda następująco:

Dla tych, którzy lubią wykonywać wszystkie obliczenia za jednym razem, równanie będzie wyglądać następująco:

Korzystanie z metody surowego liczenia (przykład gotowania)

Istnieje bardziej wydajny sposób obliczania wariancji, znany jako metoda „surowego liczenia”. Chociaż na pierwszy rzut oka równanie może wydawać się dość kłopotliwe, w rzeczywistości nie jest takie straszne. Możesz to zweryfikować, a następnie zdecydować, która metoda najbardziej Ci odpowiada.

jest sumą każdej wartości danych po podniesieniu do kwadratu,

jest kwadratem sumy wszystkich wartości danych.

Nie trać teraz głowy. Ujmijmy to wszystko w formie tabeli, a wtedy zobaczysz, że jest tu mniej obliczeń niż w poprzednim przykładzie.

Jak widać, wynik jest taki sam, jak przy użyciu poprzedniej metody. Zalety tej metody stają się oczywiste wraz ze wzrostem wielkości próby (n).

Obliczanie wariancji w Excelu

Jak zapewne już się domyśliłeś, Excel posiada formułę, która pozwala obliczyć wariancję. Ponadto, począwszy od programu Excel 2010, można znaleźć 4 odmiany formuły dyspersji:

1) VAR.V - Zwraca wariancję próbki. Wartości logiczne i tekst są ignorowane.

2) VAR.G - Zwraca wariancję populacji. Wartości logiczne i tekst są ignorowane.

3) VASP - Zwraca wariancję próbki, biorąc pod uwagę wartości logiczne i tekstowe.

4) WARIANCJA.POPUL - Zwraca wariancję populacji, biorąc pod uwagę wartości logiczne i tekstowe.

Najpierw przyjrzyjmy się różnicy między próbą a populacją. Celem statystyki opisowej jest podsumowanie lub wyświetlenie danych w taki sposób, aby szybko uzyskać duży obraz, że tak powiem, przegląd. Wnioskowanie statystyczne umożliwia wnioskowanie o populacji na podstawie próbki danych z tej populacji. Populacja reprezentuje wszystkie możliwe wyniki lub pomiary, które nas interesują. Próbka jest podzbiorem populacji.

Na przykład interesuje nas cała grupa studentów z jednego z rosyjskich uniwersytetów i musimy określić średni wynik tej grupy. Możemy obliczyć średnie wyniki uczniów, a wtedy wynikowa liczba będzie parametrem, ponieważ w naszych obliczeniach uwzględniona zostanie cała populacja. Jeśli jednak chcemy obliczyć GPA wszystkich uczniów w naszym kraju, to ta grupa będzie naszą próbą.

Różnica we wzorze do obliczania wariancji między próbą a populacją jest w mianowniku. Gdzie dla próby będzie równe (n-1), a dla populacji ogólnej tylko n.

Zajmijmy się teraz funkcjami obliczania wariancji z końcówkami A, w opisie którego jest napisane, że obliczenia uwzględniają wartości tekstowe i logiczne. W takim przypadku podczas obliczania wariancji określonego zestawu danych, w którym występują wartości nieliczbowe, program Excel zinterpretuje tekst i fałszywe wartości logiczne jako 0, a prawdziwe wartości logiczne jako 1.

Tak więc, jeśli masz tablicę danych, obliczenie jej wariancji nie będzie trudne za pomocą jednej z wymienionych powyżej funkcji Excela.

Wśród wielu wskaźników używanych w statystyce należy zwrócić uwagę na obliczanie wariancji. Należy zauważyć, że ręczne wykonanie tych obliczeń jest dość żmudnym zadaniem. Na szczęście w Excelu istnieją funkcje, które pozwalają zautomatyzować procedurę obliczeniową. Poznajmy algorytm pracy z tymi narzędziami.

Dyspersja jest wskaźnikiem zmienności, który jest średnim kwadratem odchyleń od oczekiwań matematycznych. W ten sposób wyraża rozrzut liczb wokół średniej. Obliczenie dyspersji można przeprowadzić zarówno dla populacji ogólnej, jak i dla próby.

Metoda 1: obliczenie na populacji ogólnej

Aby obliczyć ten wskaźnik w programie Excel dla populacji ogólnej, używana jest funkcja WYŚW.G. Składnia tego wyrażenia jest następująca:

DISP.G(Liczba1;Liczba2;…)

W sumie można zastosować od 1 do 255 argumentów. Argumentami mogą być zarówno wartości liczbowe, jak i odwołania do komórek, w których się znajdują.

Zobaczmy, jak obliczyć tę wartość dla zakresu danych liczbowych.

Metoda 2: przykładowe obliczenie

W przeciwieństwie do wyliczenia wartości dla populacji generalnej, w wyliczeniu dla próby mianownikiem nie jest łączna liczba liczb, lecz o jedną mniej. Odbywa się to w celu naprawienia błędu. Excel uwzględnia ten niuans w specjalnej funkcji przeznaczonej do tego typu obliczeń - DISP.V. Jego składnia jest reprezentowana przez następującą formułę:

WARIANCJA.B(Liczba1;Liczba2;…)

Liczba argumentów, podobnie jak w poprzedniej funkcji, również może wynosić od 1 do 255.

Jak widać, program Excel jest w stanie znacznie ułatwić obliczenie wariancji. Ta statystyka może być obliczona przez aplikację zarówno dla populacji, jak i próby. W takim przypadku wszystkie działania użytkownika sprowadzają się właściwie tylko do określenia zakresu liczb do przetworzenia, a Excel sam wykonuje główną pracę. Oczywiście zaoszczędzi to znaczną ilość czasu użytkownikom.

Pojęcie odchylenia procentowego implikuje różnicę między dwiema wartościami liczbowymi w procentach. Podajmy konkretny przykład: powiedzmy, że jednego dnia z hurtowni sprzedano 120 sztuk tabletek, a następnego dnia 150 sztuk. Różnica w wolumenie sprzedaży jest oczywista, następnego dnia sprzedano kolejne 30 tabletek. Odejmując liczbę 120 od 150, otrzymujemy odchylenie, które jest równe liczbie +30. Powstaje pytanie: jakie jest odchylenie procentowe?

Jak obliczyć odchylenie procentowe w programie Excel

Procent odchylenia oblicza się odejmując starą wartość od nowej wartości, a następnie dzieląc wynik przez starą wartość. Wynik obliczenia tej formuły w programie Excel powinien być wyświetlany w formacie procentowym komórki. W tym przykładzie formuła obliczeniowa wygląda następująco (150-120)/120=25%. Formułę łatwo sprawdzić 120+25%=150.

Notatka! Jeśli zamienimy stare i nowe liczby, otrzymamy formułę do obliczania narzutu.

Poniższy rysunek pokazuje przykład, jak przedstawić powyższe obliczenia w postaci formuły programu Excel. Formuła w komórce D2 oblicza procentową różnicę między sprzedażą bieżącą a zeszłoroczną: =(C2-B2)/B2

Należy zwrócić uwagę na obecność nawiasów w tym wzorze. Domyślnie w programie Excel dzielenie zawsze ma pierwszeństwo przed odejmowaniem. Dlatego jeśli nie wstawimy nawiasów, to wartość zostanie najpierw podzielona, ​​a następnie odjęta od niej inna wartość. Takie obliczenie (bez nawiasów) będzie błędne. Zamknięcie pierwszej części obliczeń w formule z nawiasami automatycznie podnosi priorytet operacji odejmowania nad operacją dzielenia.

Prawidłowo z nawiasami wprowadź formułę w komórce D2, a następnie po prostu skopiuj ją do pozostałych pustych komórek w zakresie D2:D5. Aby najszybciej skopiować formułę, wystarczy przesunąć kursor myszy do znacznika kursora klawiatury (w prawy dolny róg) tak, aby kursor myszy zmienił się ze strzałki na czarny krzyżyk. Następnie wystarczy dwukrotnie kliknąć lewym przyciskiem myszy, a Excel automatycznie wypełni puste komórki formułą, jednocześnie określając zakres D2:D5, który należy wypełnić do komórki D5 i nie więcej. To bardzo przydatny lifehack w Excelu.



Alternatywna formuła do obliczania procentu odchylenia w programie Excel

W alternatywnej formule, która oblicza względne odchylenie wartości sprzedaży od bieżącego roku, należy natychmiast podzielić przez wartości sprzedaży z poprzedniego roku, a dopiero potem od wyniku odjąć jeden: \u003d C2 / B2-1.

Jak widać na rysunku, wynik obliczenia alternatywnej formuły jest taki sam jak w poprzedniej, co oznacza, że ​​jest poprawna. Ale łatwiej jest zapisać alternatywną formułę, chociaż komuś może być trudniej ją przeczytać, aby zrozumieć, jak to działa. Lub trudniej jest zrozumieć, jaką wartość daje dana formuła w wyniku obliczeń, jeśli nie jest podpisana.

Jedyną wadą tej alternatywnej formuły jest niemożność obliczenia odchylenia procentowego dla liczb ujemnych w liczniku lub substytucie. Nawet jeśli użyjemy funkcji ABS w formule, formuła zwróci błędny wynik z liczbą ujemną w symbolu zastępczym.

Ponieważ w Excelu domyślnie operator dzielenia ma pierwszeństwo przed operatorem odejmowania, nie ma potrzeby stosowania nawiasów w tej formule.

Program Excel jest wysoko ceniony zarówno przez profesjonalistów, jak i amatorów, ponieważ może z nim pracować użytkownik na każdym poziomie wyszkolenia. Na przykład każdy, kto ma minimalne umiejętności „komunikacji” z Excelem, może narysować prosty wykres, zrobić porządny znak itp.

Jednocześnie ten program pozwala nawet na wykonywanie różnego rodzaju obliczeń, na przykład obliczeń, ale to już wymaga nieco innego poziomu wyszkolenia. Jeśli jednak dopiero zacząłeś bliższą znajomość z tym programem i interesuje Cię wszystko, co pomoże Ci stać się bardziej zaawansowanym użytkownikiem, ten artykuł jest dla Ciebie. Dzisiaj powiem ci, czym jest formuła odchylenia standardowego w programie Excel, dlaczego w ogóle jest potrzebna iw rzeczywistości, kiedy jest stosowana. Iść!

Co to jest

Zacznijmy od teorii. Odchylenie standardowe jest zwykle nazywane pierwiastkiem kwadratowym, otrzymywanym ze średniej arytmetycznej wszystkich kwadratów różnic między dostępnymi wartościami, a także ich średniej arytmetycznej. Nawiasem mówiąc, ta wartość jest zwykle nazywana grecką literą „sigma”. Odchylenie standardowe jest obliczane odpowiednio za pomocą wzoru ODCH.STANDARDOWE, program robi to za samego użytkownika.

Istotą tej koncepcji jest określenie stopnia zmienności instrumentu, czyli jest on na swój sposób wskaźnikiem ze statystyki opisowej. Ujawnia zmiany zmienności instrumentu w dowolnym okresie czasu. Za pomocą formuł STDEV można oszacować odchylenie standardowe próbki, podczas gdy wartości logiczne i tekstowe są ignorowane.

Formuła

Pomaga obliczyć odchylenie standardowe w formule programu Excel, która jest automatycznie dostarczana w programie Excel. Aby go znaleźć, musisz znaleźć sekcję formuły w Excelu i już tam wybrać tę, która ma nazwę STDEV, więc jest to bardzo proste.

Następnie pojawi się przed tobą okno, w którym będziesz musiał wprowadzić dane do obliczeń. W szczególności należy wpisać w specjalne pola dwie liczby, po których program automatycznie obliczy odchylenie standardowe dla próbki.

Niewątpliwie wzory i obliczenia matematyczne są dość skomplikowanym zagadnieniem i nie wszyscy użytkownicy mogą sobie z tym poradzić od razu. Jeśli jednak pogrzebiesz trochę głębiej i zrozumiesz problem nieco bardziej szczegółowo, okaże się, że nie wszystko jest takie smutne. Mam nadzieję, że przekonałeś się o tym na przykładzie obliczania odchylenia standardowego.

Wideo, aby pomóc