VARIANCE – Excel i Arkusze Google

Ten samouczek pokazuje, jak używać Funkcja Wariancji Excel w Excelu, aby oszacować wariancję na podstawie danej próbki.

Przegląd funkcji VARIANCE

Funkcja VARIANCE Oblicza wariancję oszacowania na podstawie danej próbki.

Aby użyć funkcji arkusza kalkulacyjnego VARIANCE Excel, wybierz komórkę i wpisz:

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

Funkcja VARIANCE Składnia i wejścia:

1	=WARIANCJA(liczba1,[liczba2];…)

liczby-Wartości, aby uzyskać wariancję

Jak obliczyć wariancję w programie Excel

Wariancja mówi, jak rozłożone są wartości w zestawie danych od średniej. Mówiąc matematycznie, wariancja jest średnią kwadratową różnicy każdego wyniku od średniej (ale wkrótce do tego dojdziemy).

Excel udostępnia szereg funkcji do obliczania wariancji - WARIANCJA.S, WARIANCJA.P, WARIANCJA, WARIANCJA, WARUNKI oraz dwie starsze funkcje WARIANCJA i WARIANCJA.POPUL.

Zanim zagłębimy się w te funkcje i nauczymy się z nich korzystać, porozmawiajmy o wariancji i sposobie jej obliczania.

Co to jest wariancja?

Podczas analizy danych powszechnym pierwszym krokiem jest obliczenie średniej. Jest to oczywiście przydatna statystyka do obliczenia, ale nie daje pełnego obrazu tego, co dzieje się z Twoimi danymi.

Weź następujący zestaw danych, który może być grupą wyników testu ocenianych na 100:

1	48,49,50,51,52

Średnia tego zakresu wynosi 50 (podsumuj liczby i podziel przez n, gdzie n to liczba wartości).

Następnie weź następujący zestaw wyników testu:

1	10,25,50,75,90

Średnia z tego zakresu to także 50 - ale oczywiście mamy tutaj dwa bardzo różne zakresy danych.

Sam środek nie może ci nic powiedzieć o tym, jak rozłożone są wyniki. Nie mówi ci, czy wszystkie wartości są zebrane, jak w pierwszych przykładach, czy też szeroko rozstawione, jak w drugim. Wariancja może pomóc ci się tego nauczyć.

Wariancja jest również wykorzystywana jako punkt bazowy dla szeregu bardziej złożonych procedur statystycznych.

Jak obliczana jest wariancja

Przeanalizujmy podstawowy przykład i ręcznie obliczmy wariancję. W ten sposób dowiesz się, co dzieje się za kulisami, gdy zaczniesz wprowadzać w życie funkcje wariancji programu Excel.

Załóżmy, że mamy zestaw danych reprezentujący trzy karty do gry: 4, 6 i 8.

Aby obliczyć wariancję, przechodzisz przez ten proces:

1) Oblicz średnią

Najpierw obliczamy średnią. Wiemy, że nasz zakres danych to 4, 6, 8, więc średnia będzie wynosić:

1	(6 + 4 + 8) / 3 = 6

Potwierdziłem to poniżej za pomocą funkcji Excel AVERAGE<>:

1	=ŚREDNIA(C4:C6)

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

Następnie od każdej z naszych wartości odejmujemy średnią.

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

1

=C4-$H$4

Średnia jest przechowywana w H4, więc odejmuję ją od każdej wartości w tabeli. Znaki dolara w tym miejscu po prostu „blokują” to odwołanie do komórki do H4, więc kiedy skopiuję ją w dół kolumny, pozostaje ona taka sama.

Wyniki:

Mamy:

123	4 - 6 = -26 - 6 = 08 - 6 = 2

Musimy uzyskać średnią tych różnic ze średniej, ale średnia z tych trzech wartości wynosi zero! Musimy więc podkreślić różnice, co robimy poprzez ich kwadraturę.

3) Wyrównaj różnice

Dodajmy nową kolumnę i podnieśmy do kwadratu liczby w kolumnie D:

1

=D4*D4

Dobra, tak jest lepiej. Teraz, gdy różnice nie osiągają uśrednienia do zera, możemy obliczyć wariancję.

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

Tu spotykamy rozwidlenie dróg. Istnieją dwa sposoby obliczania wariancji, a ten, którego używasz, zależy od typu posiadanych danych.

• Jeśli używasz dane o populacji, po prostu przyjmujesz średnią jako normalną (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 wszystkie potrzebne dane, na przykład, jeśli chcesz poznać średni wiek nauczycieli w określonej szkole i masz dane dotyczące wieku każdego nauczyciela w tej szkole, masz dane o populacji.

Przykładowe dane oznaczają, że nie masz wszystkich swoich danych, tylko próbkę pobraną z większej populacji. Więc jeśli chcesz uzyskać średni wiek nauczycieli w całym kraju, a masz dane tylko o nauczycielach w jednej szkole, masz przykładowe dane.

W naszym przykładzie mamy dane o populacji. Interesują nas tylko nasze trzy karty - to populacja, a nie pobraliśmy z nich próbki. Możemy więc po prostu wziąć średnią z kwadratów różnic w normalny sposób:

1	=ŚREDNIA(E4:E8)

Zatem wariancja naszej populacji wynosi 2,666.

Jeśli to było przykładowe dane (być może wyciągnęliśmy te trzy karty z większego zestawu), obliczylibyśmy średnią w następujący sposób:

1	Wariancja próbki = (4 + 0 + 4) / (3 - 1)

Lub:

1	Wariancja próbki = 8 / 2 = 4

Dlaczego dzielić przez n-1 z przykładowymi danymi, a nie tylko przez n?

Krótka odpowiedź na to pytanie brzmi: „Ponieważ daje właściwą odpowiedź”. Ale wyobrażam sobie, że będziesz chciał trochę więcej! Jest to złożony temat, więc przedstawię tutaj tylko krótki przegląd.

Pomyśl o tym w ten sposób: jeśli pobierzesz próbkę danych z populacji, wartości te będą bliższe średniej próbka niż są do średniej populacja.

Oznacza to, że jeśli po prostu podzielisz przez n, trochę zaniżysz wariancję populacji. Dzielenie przez n-1 trochę to koryguje.

Dzięki naszemu zestawowi trzech kart jesteśmy w dobrym miejscu, aby przetestować tę teorię. Ponieważ są tylko trzy karty, istnieje niewielka liczba próbek, które możemy ewentualnie pobrać.

Weźmy próbki dwóch kart. Wybierzemy jedną kartę, odłożymy ją, potasujemy, a następnie wybierzemy inną. Oznacza to, że możemy wybrać dziewięć kombinacji dwóch kart.

Mając tylko dziewięć możliwych próbek, możemy obliczyć każdą możliwą wariancję próbki za pomocą obu metod (podziel przez n i podziel przez n-1), weź ich średnią i zobacz, która daje nam właściwą odpowiedź.

W poniższej tabeli przedstawiłem wszystko. Każdy wiersz tabeli jest inną próbką, a kolumny B i C pokazują dwie karty, które zostały wybrane w każdej próbce. Następnie dodałem dwie kolejne kolumny: jedną, w której obliczyłem wariancję tej próbki dwóch kart, dzieląc przez n, a drugą, w której podzieliłem przez n - 1.

Spójrz:

Po prawej stronie tabeli pokazałem średnie z kolumn D i E.

Średnia z kolumny D, podzielona przez n, daje nam wariancję 1,333.

Średnia z kolumny E, podzielona przez n-1, daje nam wariancję 2,666.

Wiemy już z naszego poprzedniego przykładu, że wariancja populacji wynosi 2,666. Tak więc dzielenie przez n-1 przy użyciu przykładowych danych daje nam dokładniejsze oszacowania.

Funkcje programu Excel do obliczania wariancji

Teraz, gdy zobaczyłeś przykład obliczania wariancji, przejdźmy do funkcji programu Excel.

Masz tutaj kilka opcji:

• P zwraca wariancję dla danych o populacji (przy użyciu metody dzielenia przez n)
• S zwraca wariancję dla danych próbki (dzielenie przez n-1)
• VAR to starsza funkcja, która działa dokładnie tak samo jak VAR.S
• VARA jest taki sam jak VAR.S, z wyjątkiem tego, że zawiera komórki tekstowe i wartości logiczne
• VARPA jest taki sam jak VAR.P, z wyjątkiem tego, że zawiera komórki tekstowe i wartości logiczne

Przeanalizujmy je jeden po drugim.

Funkcja Excel VAR.P

VAR.P oblicza wariancję dla danych populacyjnych (za pomocą metody dzielenia przez n). Użyj go w ten sposób:

1	=WARIANCJA.P(C4:C6)

Definiujesz tylko jeden argument w VAR.P: zakres danych, dla którego chcesz obliczyć wariancję. W naszym przypadku są to wartości kart w C4:C6.

Jak widać powyżej, VAR.P zwraca 2,666 dla naszego zestawu trzech kart. Jest to ta sama wartość, którą wcześniej obliczyliśmy ręcznie.

Zauważ, że WARIANCJA.P całkowicie ignoruje komórki zawierające tekst lub wartości logiczne (PRAWDA/FAŁSZ). Jeśli chcesz je uwzględnić, użyj zamiast tego VARPA.

Funkcja Excel VAR.S

VAR.S oblicza wariancję dla danych próbki (dzielenie przez n-1). Używasz go w ten sposób:

1	=WARIANCJA.S(C4:C6)

Ponownie, jest tylko jeden argument – ​​zakres danych.

W tym przypadku VAR.S zwraca 4. Tę samą liczbę otrzymaliśmy w kroku 4, gdy wykonaliśmy powyższe obliczenia ręczne.

WARIANCJA.S całkowicie ignoruje komórki zawierające tekst lub wartości logiczne (PRAWDA/FAŁSZ). Jeśli chcesz je uwzględnić, użyj zamiast tego VARA.

Funkcja Excel VAR

VAR jest całkowicie równoważny VAR.S: oblicza wariancje dla danych próbki (przy użyciu metody n-1). Oto jak z niego korzystać:

1	=WARIANCJA(C4:C6)

VAR to „funkcja zgodności”. Oznacza to, że firma Microsoft jest w trakcie usuwania tej funkcji z programu Excel. W tej chwili jest nadal dostępny do użytku, ale zamiast tego należy używać VAR.S, aby arkusze kalkulacyjne pozostały zgodne z przyszłymi wersjami programu Excel.

Funkcja Excel VARA

VARA zwraca również wariancję danych próbki, ale ma pewne kluczowe różnice w stosunku do VAR i VAR.S. Mianowicie uwzględnia w swoich obliczeniach wartości logiczne i tekstowe:

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

Oto jak z niego korzystasz:

1	=WARIANCJA(C4:C11)

Dodaliśmy pięć kolejnych wierszy do tabeli: J, Q, K, PRAWDA i FAŁSZ. Kolumna D pokazuje, jak VARA interpretuje te wartości.

Ponieważ w naszej tabeli mamy teraz nową partię niskich wartości, wariancja wzrosła do 10,268.

Funkcja Excel VARPA

VARPA oblicza wariancję danych populacyjnych. Jest podobny do VAR.P, z tą różnicą, że w obliczeniach uwzględnia również wartości logiczne i ciągi tekstowe:

• 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	=WARIANCJA.POPUL(C4:C12)

Dodaliśmy pięć kolejnych wierszy do tabeli: J, Q, K, PRAWDA i FAŁSZ. Kolumna D pokazuje, jak VARPA interpretuje te wartości.

W wyniku dodania do danych tej grupy niższych wartości wariancja wzrosła do 8,984.

Funkcja VARIANCE w Arkuszach Google

Funkcja CORREL działa dokładnie tak samo w Arkuszach Google, jak w Excelu: