Dyspersja jest miarą dyspersji opisującą odchylenie porównawcze między wartościami danych a średnią. Jest to najczęściej używana miara rozproszenia w statystyce, obliczana poprzez zsumowanie i podniesienie do kwadratu odchylenia każdej wartości danych od średniej. Wzór na obliczenie wariancji podano poniżej:
s 2 – wariancja próbki;
x av – średnia próbki;
N — wielkość próby (liczba wartości danych),
(x i – x śr.) 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 robię to rzadko. Aby jednak nie zgłodnieć, od czasu do czasu muszę sięgnąć do pieca, aby zrealizować plan nasycenia organizmu białkami, tłuszczami i węglowodanami. Poniższy zestaw danych pokazuje, ile razy Renat gotuje w każdym miesiącu:
Pierwszym krokiem w obliczaniu wariancji jest określenie średniej próbki, która w naszym przykładzie wynosi 7,8 razy w miesiącu. Pozostałą część obliczeń można ułatwić korzystając z poniższej tabeli.
Ostatnia 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:
Stosowanie metody liczenia surowego (przykład gotowania)
Istnieje bardziej efektywny 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 się tego upewnić, 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. Umieśćmy to wszystko w tabeli, a zobaczysz, że jest tu mniej obliczeń niż w poprzednim przykładzie.
Jak widać, wynik był taki sam, jak przy zastosowaniu poprzedniej metody. Zalety tej metody stają się widoczne wraz ze wzrostem wielkości próby (n).
Obliczanie wariancji w programie Excel
Jak już zapewne się domyślasz, w Excelu dostępna jest formuła pozwalająca obliczyć wariancję. Ponadto, począwszy od Excela 2010, można znaleźć 4 rodzaje formuł na wariancję:
1) VARIANCE.V – Zwraca wariancję próbki. Wartości logiczne i tekst są ignorowane.
2) DISP.G - Zwraca wariancję populacji. Wartości logiczne i tekst są ignorowane.
3) VARIANCE - Zwraca wariancję próbki, biorąc pod uwagę wartości logiczne i tekstowe.
4) Wariancja - Zwraca wariancję populacji z uwzględnieniem wartości logicznych i tekstowych.
Najpierw zrozummy różnicę między próbą a populacją. Celem statystyk opisowych jest podsumowanie lub wyświetlenie danych, dzięki czemu można szybko uzyskać pełny obraz, że tak powiem, przegląd. Wnioskowanie statystyczne umożliwia wyciąganie wniosków na temat populacji na podstawie próbki danych z tej populacji. Populacja reprezentuje wszystkie możliwe wyniki lub pomiary, które nas interesują. Próba jest podzbiorem populacji.
Przykładowo interesuje nas grupa studentów z jednego z rosyjskich uniwersytetów i musimy ustalić średni wynik tej grupy. Możemy obliczyć średnie wyniki uczniów, a wtedy uzyskana liczba będzie parametrem, ponieważ w naszych obliczeniach zostanie zaangażowana cała populacja. Jeśli jednak będziemy chcieli policzyć średnią ocen wszystkich uczniów w naszym kraju, to właśnie ta grupa będzie naszą próbą.
Mianownikiem jest różnica we wzorze na obliczenie wariancji między próbą a populacją. Gdzie dla próby będzie ona równa (n-1), a dla populacji ogólnej tylko n.
Przyjrzyjmy się teraz funkcjom obliczania wariancji z zakończeniami A, którego opis stwierdza, że w obliczeniach uwzględniane są wartości tekstowe i logiczne. W takim przypadku przy obliczaniu wariancji konkretnego zbioru danych, w którym występują wartości nieliczbowe, Excel zinterpretuje tekst i fałszywe wartości logiczne jako równe 0, a prawdziwe wartości logiczne jako równe 1.
Jeśli więc masz tablicę danych, obliczenie jej wariancji nie będzie trudne przy użyciu jednej z wymienionych powyżej funkcji programu Excel.
Statystyka wykorzystuje ogromną liczbę wskaźników, a jednym z nich jest obliczanie wariancji w Excelu. Jeśli zrobisz to sam ręcznie, zajmie to dużo czasu i możesz popełnić wiele błędów. Dzisiaj przyjrzymy się, jak podzielić formuły matematyczne na proste funkcje. Przyjrzyjmy się najprostszym, najszybszym i najwygodniejszym metodom obliczeniowym, które pozwolą Ci zrobić wszystko w ciągu kilku minut.
Oblicz wariancję
Wariancja zmiennej losowej to matematyczne oczekiwanie kwadratu odchylenia zmiennej losowej od jej oczekiwań matematycznych.
Obliczenia opieramy na populacji ogólnej
Aby obliczyć matę. Oczekiwanie, aż program użyje funkcji DISP.G, a jej składnia wygląda następująco: „=DISP.G(Number1;Number2;…)”.
Można użyć maksymalnie 255 argumentów, nie więcej. Argumentami mogą być liczby pierwsze lub odniesienia do komórek, w których są określone. Przyjrzyjmy się, jak obliczyć wariancję w programie Microsoft Excel:
1. Pierwszym krokiem jest wybranie komórki, w której zostanie wyświetlony wynik obliczenia, a następnie kliknięcie przycisku „Wstaw funkcję”.
2. Otworzy się powłoka zarządzania funkcjami. Tam musisz poszukać funkcji „DISP.G”, która może znajdować się w kategorii „Statystyczne” lub „Pełna lista alfabetyczna”. Po znalezieniu należy go zaznaczyć i kliknąć „OK”.
3. Otworzy się okno z argumentami funkcji. W nim musisz wybrać linię „Numer 1” i na arkuszu wybrać zakres komórek z serią liczb.
4. Następnie wyniki obliczeń zostaną wyświetlone w komórce, w której wprowadzono funkcję.
W ten sposób możesz łatwo znaleźć rozbieżność w programie Excel.
Obliczeń dokonujemy na podstawie próbki
W tym przypadku wariancję próbki w programie Excel oblicza się z mianownikiem wskazującym nie całkowitą liczbę liczb, ale o jedną mniej. Odbywa się to w przypadku mniejszego błędu za pomocą specjalnej funkcji DISP.V, której składnia to =DISP.V(Numer1;Numer2;...). Algorytm działań:
- Podobnie jak w poprzedniej metodzie, musisz wybrać komórkę, w której chcesz uzyskać wynik.
- W Kreatorze funkcji powinieneś znaleźć „DISP.B” w kategorii „Pełna lista alfabetyczna” lub „Statystyczne”.
- Następnie pojawi się okno, w którym należy postępować analogicznie jak w poprzedniej metodzie.
Wideo: Obliczanie wariancji w programie Excel
Wniosek
Wariancję w programie Excel oblicza się bardzo prosto, znacznie szybciej i wygodniej niż robić to ręcznie, ponieważ matematyczna funkcja oczekiwań jest dość złożona i jej obliczenie może zająć dużo czasu i wysiłku.
Program Excel jest wysoko ceniony zarówno przez profesjonalistów, jak i amatorów, ponieważ mogą z nim pracować użytkownicy na każdym poziomie umiejętności. Na przykład każdy, kto ma minimalne umiejętności „komunikacyjne” w Excelu, może narysować prosty wykres, zrobić porządny talerz itp.
Jednocześnie program ten umożliwia nawet wykonywanie różnego rodzaju obliczeń, na przykład obliczeń, ale wymaga to nieco innego poziomu wyszkolenia. Jeśli jednak dopiero zacząłeś bliżej zapoznawać się z tym programem i interesuje Cię wszystko, co pomoże Ci stać się bardziej zaawansowanym użytkownikiem, ten artykuł jest dla Ciebie. Dziś opowiem Wam, czym jest formuła odchylenia standardowego w Excelu, dlaczego w ogóle jest potrzebna i, ściśle rzecz biorąc, kiedy się ją stosuje. chodźmy!
Co to jest
Zacznijmy od teorii. Odchylenie standardowe nazywa się zwykle pierwiastkiem kwadratowym uzyskanym ze średniej arytmetycznej wszystkich kwadratów różnic między dostępnymi wielkościami, a także ich średnią arytmetyczną.
Nawiasem mówiąc, wartość ta jest zwykle nazywana grecką literą „sigma”. Odchylenie standardowe oblicza się za pomocą wzoru ROZWIĄZANIE STANDARDOWE, program robi to sam za użytkownika.
Istotą tej koncepcji jest określenie stopnia zmienności instrumentu, czyli jest on na swój sposób wskaźnikiem wyprowadzonym ze statystyki opisowej. Identyfikuje zmiany zmienności instrumentu w dowolnym okresie. Wzory STDEV można wykorzystać do oszacowania odchylenia standardowego próbki, ignorując wartości logiczne i tekstowe.
Formuła
Formuła automatycznie dostarczana w programie Excel pomaga obliczyć odchylenie standardowe w programie Excel. Aby go znaleźć, należy znaleźć w Excelu sekcję z formułami, a następnie wybrać tę o nazwie STANDARDEVAL, 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 czym program sam obliczy odchylenie standardowe dla próbki.
Nie ulega wątpliwości, że wzory i obliczenia matematyczne są dość złożonym zagadnieniem i nie każdy użytkownik jest w stanie od razu sobie z tym poradzić. Jeśli jednak pogrzebać nieco głębiej i przyjrzeć się sprawie 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.
Pojęcie odchylenia procentowego odnosi się do różnicy między dwiema wartościami liczbowymi wyrażonej w procentach. Podajmy konkretny przykład: powiedzmy, że jednego dnia z hurtowni sprzedano 120 tabletów, a następnego – 150 sztuk. Różnica w wolumenie sprzedaży jest wyraźna – następnego dnia sprzedano 30 kolejnych tabletów. Odejmując liczbę 120 od 150, otrzymujemy odchylenie równe liczbie +30. Powstaje pytanie: czym jest odchylenie procentowe?
Jak obliczyć odchylenie procentowe w programie Excel
Odchylenie procentowe oblicza się odejmując starą wartość od nowej, a następnie dzieląc wynik przez starą wartość. Wynik obliczenia tej formuły w programie Excel powinien zostać wyświetlony w formacie procentowym komórki. W tym przykładzie wzór obliczeniowy jest następujący (150-120)/120=25%. Wzór jest łatwy do sprawdzenia: 120+25%=150.
Uważać na! Jeśli zamienimy stare i nowe liczby, otrzymamy wzór na obliczenie znacznika.
Poniższy rysunek przedstawia przykład przedstawienia powyższego obliczenia w postaci formuły Excel. Formuła w komórce D2 oblicza procentowe odchylenie pomiędzy wartościami sprzedaży dla roku bieżącego i poprzedniego: =(C2-B2)/B2
Ważne jest, aby zwrócić uwagę na obecność nawiasów w tej formule. Domyślnie w programie Excel operacja dzielenia zawsze ma pierwszeństwo przed operacją odejmowania. Jeżeli zatem nie wstawimy nawiasów, to wartość zostanie najpierw podzielona, a następnie odjęta zostanie od niej kolejna wartość. Takie obliczenie (bez obecności nawiasów) będzie błędne. Zamknięcie pierwszej części obliczenia we wzorze w nawiasach automatycznie podnosi priorytet operacji odejmowania nad operację dzielenia.
Wpisz poprawnie formułę w nawiasach w komórce D2, a następnie po prostu skopiuj ją do pozostałych pustych komórek z zakresu D2:D5. Aby najszybciej skopiować formułę, wystarczy przesunąć kursor myszy na znacznik kursora klawiatury (w prawym dolnym rogu), 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łą i określi zakres D2:D5, który należy wypełnić do komórki D5 i nie więcej. To bardzo przydatny trik na życie w Excelu.
Alternatywny wzór na obliczenie odchylenia procentowego w Excelu
W alternatywnym wzorze obliczającym względne odchylenie wartości sprzedaży od roku bieżącego należy od razu podzielić przez wartości sprzedaży z roku poprzedniego i dopiero wtedy od wyniku odjąć jeden: =C2/B2-1.
Jak widać na rysunku, wynik obliczenia alternatywnego wzoru jest taki sam jak w poprzednim, a więc poprawny. Ale alternatywną formułę łatwiej jest napisać, chociaż niektórym może być trudniej przeczytać, aby zrozumieć zasadę jej działania. Albo trudniej jest zrozumieć, jaką wartość daje dana formuła w wyniku obliczeń, jeśli nie jest podpisana.
Jedyną wadą tego alternatywnego wzoru jest brak możliwości obliczenia odchylenia procentowego dla liczb ujemnych w liczniku lub w substytucie. Nawet jeśli w formule użyjemy funkcji ABS, formuła zwróci błędny wynik, jeśli liczba w podstawce będzie ujemna.
Ponieważ w programie Excel domyślnie jest priorytet operacji dzielenia nad operacją odejmowania, w tej formule nie ma potrzeby używania nawiasów.
Aby znaleźć średnią wartość w Excelu (nieważne, czy jest to wartość liczbowa, tekstowa, procentowa czy inna), istnieje wiele funkcji. A każdy z nich ma swoje własne cechy i zalety. Rzeczywiście, w tym zadaniu można postawić pewne warunki.
Na przykład średnie wartości serii liczb w programie Excel są obliczane za pomocą funkcji statystycznych. Możesz także ręcznie wprowadzić własną formułę. Rozważmy różne opcje.
Jak znaleźć średnią arytmetyczną liczb?
Aby znaleźć średnią arytmetyczną, należy dodać wszystkie liczby w zestawie i podzielić sumę przez ilość. Np. oceny ucznia z informatyki: 3, 4, 3, 5, 5. Co wchodzi w skład kwartału: 4. Średnią arytmetyczną obliczyliśmy ze wzoru: =(3+4+3+5+5) /5.
Jak to szybko zrobić korzystając z funkcji Excela? Weźmy na przykład serię liczb losowych w ciągu:
Lub: utwórz aktywną komórkę i po prostu wprowadź formułę ręcznie: =ŚREDNIA(A1:A8).
Zobaczmy teraz, co jeszcze potrafi funkcja ŚREDNIA.
Znajdźmy średnią arytmetyczną dwóch pierwszych i trzech ostatnich liczb. Wzór: =ŚREDNIA(A1:B1,F1:H1). Wynik:
Stan średni
Warunkiem znalezienia średniej arytmetycznej może być kryterium numeryczne lub tekstowe. Skorzystamy z funkcji: =ŚREDNIA JEŻELI().
Znajdź średnią arytmetyczną liczb większych lub równych 10.
Funkcja: =ŚREDNIAJEŻELI(A1:A8,">=10")
Wynik użycia funkcji ŚREDNIA JEŻELI pod warunkiem „>=10”: Trzeci argument – „Zakres uśredniania” – zostaje pominięty. Przede wszystkim nie jest to wymagane. Po drugie, zakres analizowany przez program zawiera WYŁĄCZNIE wartości liczbowe. Komórki określone w pierwszym argumencie zostaną przeszukane zgodnie z warunkiem określonym w drugim argumencie.
Uwaga! Kryterium wyszukiwania można określić w komórce. I utwórz link do niego w formule.
Znajdźmy średnią wartość liczb, korzystając z kryterium tekstowego. Na przykład średnia sprzedaż produktu „stoły”.
Funkcja będzie wyglądać następująco: =ŚREDNIA JEŻELI($A$2:$A$12,A7,$B$2:$B$12). Asortyment – kolumna z nazwami produktów. Kryterium wyszukiwania stanowi link do komórki zawierającej słowo „tabele” (zamiast linku A7 można wstawić słowo „tabele”). Zakres uśredniania – te komórki, z których zostaną pobrane dane do obliczenia wartości średniej.
W wyniku obliczenia funkcji otrzymujemy następującą wartość:
Uwaga! Dla kryterium tekstowego (warunku) należy podać zakres uśredniania.
Jak obliczyć średnią ważoną cenę w Excelu?
Jak ustaliliśmy średnią ważoną cenę?
Wzór: =SUMA(C2:C12,B2:B12)/SUMA(C2:C12).
Korzystając ze wzoru SUMPRODUCT, obliczamy całkowity przychód po sprzedaży całej ilości towaru. Natomiast funkcja SUMA sumuje ilość towaru. Dzieląc całkowity przychód ze sprzedaży towarów przez łączną liczbę jednostek towaru, otrzymaliśmy średnią ważoną cenę. Wskaźnik ten uwzględnia „wagę” każdej ceny. Jego udział w ogólnej masie wartości.
Odchylenie standardowe: wzór w Excelu
Istnieją odchylenia standardowe dla populacji ogólnej i próby. W pierwszym przypadku jest to pierwiastek wariancji ogólnej. W drugim, z wariancji próbki.
Aby obliczyć ten wskaźnik statystyczny, sporządzany jest wzór dyspersji. Wyciąga się z niego korzeń. Ale w Excelu istnieje gotowa funkcja do znajdowania odchylenia standardowego.
Odchylenie standardowe jest powiązane ze skalą danych źródłowych. Nie wystarczy to do graficznego przedstawienia zmienności analizowanego zakresu. Aby uzyskać względny poziom rozproszenia danych, oblicza się współczynnik zmienności:
odchylenie standardowe / średnia arytmetyczna
Formuła w programie Excel wygląda następująco:
STDEV (zakres wartości) / ŚREDNIA (zakres wartości).
Współczynnik zmienności oblicza się w procentach. Dlatego ustawiamy format procentowy w komórce.