Co to jest wariancja?
Wykorzystujemy wariancję danych do oszacowania zakresu przyszłej wartości w seriach. Wariancja mówi, ile danych może się różnić od mieć na myśli zbioru danych. Wariancja jest często określana jako wartość błędu. Nie jest to najbardziej godna zaufania statystyka i nie używamy jej samodzielnie do przewidywania przyszłej wartości.
Mówiąc matematycznie, wariancja jest średnią kwadratową różnicy punktów danych od średniej danych. Wariancja to kwadratowa wartość odchylenia standardowego. Poniżej znajdują się dwie formuły wariancji.
Nie będziemy musieli używać tych formuł do obliczania wariancji w programie Excel. Excel ma do tego dwie formuły VAR.P i VAR.S. Jeśli chcesz tylko wiedzieć, jak obliczyć wariancję w programie Excel, użyj formuł opisanych poniżej. Jeśli chcesz wiedzieć, co to jest wariancja i kiedy użyć której formuły wariancji, przeczytaj cały artykuł.
Jak znaleźć wariancję w programie Excel?
Weźmy przykład.
Swoją stronę uruchomiłem w styczniu 2019 roku. Tutaj co miesiąc mam dane o nowych użytkownikach dołączonych do mojej strony internetowej. Chcę poznać wariancję tych danych.
To są kompletne dane. Kiedy przechwycimy pełne dane (całą populację), obliczamy wariancję populacji (dlaczego? wyjaśnię to w dalszej części artykułu). Funkcja Excel do obliczania wariancji populacji to VAR.P. Składnia VAR.P to
=WARIANCJA.P(liczba1,[liczba2];…)
Numer1, numer2,… : są to liczby, dla których chcesz obliczyć wariancję.
Pierwsza cyfra jest obowiązkowa.
Użyjmy tego wzoru, aby obliczyć wariancję naszych danych. Mamy dane w komórce C2:C15. Więc formuła będzie wyglądać tak:
| =WARIANCJA.P(C2:C15) |
Zwraca to wartość 186.4285714, co jest dość dużą wariancją, biorąc pod uwagę nasze dane.
Ponieważ moja strona została uruchomiona od stycznia 2019, mam wszystkie dane. Zakładając, że swoją stronę uruchomiłem dawno temu, ale dane, które mam, to tylko od stycznia 2019 do lutego 2020. Nie mam pełnych danych. To tylko przykładowe dane. W takim przypadku nie użyję VAR.P zamiast tego użyję funkcji VAR.S do obliczenia wariancji.
| =WARIANCJA.S(C2:C15) |
Funkcja WARIANCJA.S zwraca większą wariancję niż WARIANCJA.P. Zwraca 200.7692308.
Jak wariancja jest obliczana ręcznie w programie Excel?
Tak, w ten sposób obliczasz wariancję w programie Excel. Ale jak te funkcje wariancji obliczają te liczby? Jeśli to znasz, możesz lepiej zrozumieć te liczby i mądrze z nich korzystać. W przeciwnym razie te liczby są tylko liczbami losowymi. Aby to zrozumieć, musimy ręcznie obliczyć wariancję.
Ręczne obliczanie wariancji populacji w programie Excel
Użyjemy tych samych danych, których użyliśmy w powyższym przykładzie. Wzór matematyczny na wariancję populacji to:
Aby obliczyć wariancję, musimy obliczyć średnią (ŚREDNIA) danych, różnicę każdej wartości od średniej, zsumować je i ostatecznie podzielić tę sumę przez całkowitą liczbę obserwacji.
Krok 1. Oblicz średnią danych
Do obliczenia średniej danych w Excelu używamy funkcji ŚREDNIA.
Użyj tej formuły w komórce C17 (lub gdziekolwiek chcesz).
| =ŚREDNIA(C2:C15) |
To zwraca 233.0.
Krok 2: Znajdź różnicę każdego punktu danych od średniej
Teraz przejdź do komórki D2 i odejmij średnią (C17) od C2 (x). Użyj tej formuły w D2 i przeciągnij ją do D15.
| =C2-$17$ |
Krok 3: Wygraj każdą różnicę.
Teraz musimy wyrównać każdą różnicę, którą tutaj otrzymaliśmy. W komórce E2 wpisz formułę poniżej i przeciągnij w dół do E15:
| =MOC(D2,2) |
Krok 4: Sumuj kwadraty
Teraz musimy zsumować te kwadraty różnic. Użyj więc tej formuły w komórce C18:
| =SUMA(E2:E13) |
Ostatni krok: Podziel sumę kwadratów przez liczbę obserwacji.
Mam 14 obserwacji. Możesz obliczyć za pomocą funkcji ILE.LICZB, jeśli chcesz.
Użyj tej formuły w komórce C19, aby obliczyć wariancję populacji.
| =C18/LICZBA(C2:C15) |
Zwraca to wartość 186.429…, która jest dokładnie taka sama, jak wariancja zwrócona przez powyższą formułę wariancji programu Excel WAR.P.
Teraz, gdy wiesz, jak wariancja populacji jest obliczana ręcznie w programie Excel, będziesz mógł wiedzieć, jak ją wykorzystać w rzeczywistej analizie.
Ręczne obliczanie wariancji próbki w programie Excel
W większości przypadków nie da się zebrać wszystkich danych do analizy. Zwykle wybieramy losową próbkę z danych i analizujemy ją, aby zinterpretować charakter danych. W takim przypadku, jeśli użyjemy wariancji populacji, może to być analiza destrukcyjna. Aby być po bezpiecznej stronie, używamy formuły wariancji próbki. Wzór na wariancję próbki to:
Jedyną różnicą w wariancji próby i populacji jest mianownik. W wariancji próby odejmujemy jeden od liczby obserwacji (n-1). Nazywa się to bezstronną analizą. Gwarantuje to, że dane nie są niedoszacowane, a możliwy zakres błędów jest nieco szerszy.
Aby ręcznie obliczyć wariancję próbki w programie Excel, musimy powtórzyć kroki od 1 do 4 wariancji populacji. W ostatnim kroku użyj poniższej formuły:
| =C18/(LICZBA(C2:C15)-1) |
To zwraca 200 769. Jest to dokładnie to samo, co wariancja zwracana przez funkcję WAR.S. Potrzeba większego zakresu błędu niż VAR.P, aby zapewnić mniejsze prawdopodobieństwo błędów w przewidywaniu.
Wariancja danych nie jest tak zaufana w przewidywaniu. Obliczamy odchylenie standardowe, które jest pierwiastkiem kwadratowym z wariancji i wiele innych statystyk, aby zminimalizować prawdopodobieństwo błędu w przewidywaniu.
Więc tak, tak można obliczyć wariancję w programie Excel. Mam nadzieję, że było to objaśniające i pomocne. Jeśli masz jakiekolwiek wątpliwości co do tej rozbieżności w Excelu lub jakichkolwiek innych statystykach, sekcja komentarzy należy do Ciebie.
Jak obliczyć odchylenie standardowe w programie Excel : Aby obliczyć odchylenie standardowe, mamy wiele wzorów. Odchylenie standardowe to po prostu pierwiastek kwadratowy z wariancji. Mówi więcej o danych niż o wariancji.
Jak korzystać z funkcji VAR.P w programie Excel : Oblicz wariancję dla liczb danych populacji w programie Excel za pomocą funkcji VAR.P
Jak korzystać z funkcji ODCH.STANDARDOWE.P w programie Excel? : Oblicz odchylenie standardowe dla liczb danych populacji w programie Excel za pomocą funkcji VAR.P
Jak korzystać z funkcji DSTDEVP w programie Excel? : Oblicz odchylenie standardowe dla przykładowych liczb danych mających wiele kryteriów w programie Excel za pomocą funkcji DSTDEVP
Jak korzystać z funkcji WARIANCJA w programie Excel : Oblicz wariancję dla przykładowych liczb danych w programie Excel za pomocą funkcji WARIANCJA.
Analiza regresji w Excelu: Regresja to narzędzie analityczne, którego używamy do analizowania dużych ilości danych oraz tworzenia prognoz i przewidywań w programie Microsoft Excel.
Jak utworzyć wykres odchylenia standardowego : Odchylenie standardowe mówi, jak bardzo dane są skupione wokół średniej danych.
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Funkcja WYSZUKAJ.PIONOWO w programie Excel | Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
LICZ.JEŻELI w Excelu 2016 | Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.