W analizie statystycznej zmienność wśród członków zbioru danych pokazuje, jak daleko od siebie znajdują się punkty danych od linii trendu, znanej również jako linia regresji. Im wyższa wariancja, tym bardziej rozłożone są punkty danych. Badanie analizy wariancji pokazuje, które części wariancji można wyjaśnić charakterystyką danych i które można przypisać czynnikom losowym. Część wariancji, której nie można wyjaśnić, nazywa się wariancją resztkową.
Używanie arkuszy kalkulacyjnych Excel do obliczania wariancji resztkowej
Wzór do obliczania wariancji resztkowej obejmuje liczne złożone obliczenia. W przypadku małych zestawów danych proces obliczania wariancji resztkowej ręcznie może być uciążliwy. W przypadku dużych zestawów danych zadanie może być wyczerpujące. Korzystając z arkusza kalkulacyjnego Excel, wystarczy wprowadzić punkty danych i wybrać poprawną formułę. Program obsługuje złożone obliczenia i szybko dostarcza wyniki.
Punkty danych
Otwórz nowy arkusz kalkulacyjny programu Excel i wprowadź punkty danych w dwóch kolumnach. Linie regresji wymagają, aby każdy punkt danych miał dwa elementy. Statystycy zazwyczaj określają te elementy jako "X" i "Y". Na przykład Generic Insurance Co. chce znaleźć resztkową wariancję wysokości i masy swoich pracowników. Zmienna X reprezentuje wysokość, a zmienna Y oznacza wagę. Wprowadź wysokości w kolumnie A i wagi do kolumny B.
Znajdowanie średniej
The oznaczać reprezentuje średnią dla każdego elementu w zbiorze danych. W tym przykładzie ubezpieczenie ogólne chce znaleźć średnie, standardowe odchylenie i kowariancję wysokości i ciężarów 10 pracowników. Średnią wysokości wymienionych w kolumnie A można znaleźć, wprowadzając funkcję "= ŚREDNIA (A1: A10)" do komórki F1. Średnią masę wymienioną w kolumnie B można znaleźć, wprowadzając funkcję "= ŚREDNIA (B1: B10)" do komórki F3.
Znajdowanie odchylenia standardowego i kowariancji
The odchylenie standardowe określa, w jakim stopniu punkty danych są rozłożone od średniej. The kowariancja mierzy, jak bardzo oba elementy punktu danych zmieniają się razem. Standardowe odchylenie wysokości znajduje się w funkcji "= STDEV (A1: A10)" w komórce F2. Standardowe odchylenie wag znajduje się w funkcji "= STDEV (B1: B10)" w komórce F4. Kowariancję między wysokościami i wagami można znaleźć wchodząc w funkcję "= COVAR (A1: A10, B1: B10)" do komórki F5.
Znalezienie linii regresji
The linia regresji reprezentuje funkcję liniową, która podąża za trendem punktów danych. Wzór na linię regresji wygląda następująco: Y = aX + b.
Użytkownik może znaleźć wartości "a" i "b", używając obliczeń dla średnich, odchyleń standardowych i kowariancji. Wartość "b" oznacza punkt, w którym linia regresji przechwytuje oś Y. Wartość można znaleźć, biorąc kowariancję i dzieląc ją przez kwadrat odchylenia standardowego wartości X. Formuła programu Excel przechodzi do komórki F6 i wygląda następująco: = F5 / F2 ^ 2.
Wartość "a" reprezentuje nachylenie linii regresji. Formuła Excel przechodzi do komórki F7 i wygląda następująco: = F3-F6 * F1.
Aby zobaczyć formułę dla linii regresji, wprowadź to połączenie ciągów do komórki F8:
= CONCATENATE ("Y ="; ROUND (F6; 2); "X"; IF (ZNAK (F7) = 1; "+"; "-"); ABS (ROUND (F7; 2)))
Oblicz wartości Y
Następny krok polega na obliczeniu wartości Y na linii regresji dla podanych wartości X w zbiorze danych. Wzór do znalezienia wartości Y przechodzi do kolumny C i wygląda następująco:
= $ F 6 $ * A (i) + $ F 7 $
Gdzie A (i) jest wartością dla kolumny A w wierszu (i). Formuły wyglądają tak w arkuszu kalkulacyjnym:
= $ F 6 $ * A1 + $ 7 F
= $ F 6 $ * A2 + $ F 7 $
= $ F 6 $ * A3 + $ 7 F $, i tak dalej
Wpisy w kolumnie D pokazują różnice między oczekiwaną i rzeczywistą wartością dla Y. Formuły wyglądają tak:
= B (i) -C (i), Gdzie B (i) i C (i) są odpowiednio wartościami w wierszu (i) w kolumnach B i C.
Znalezienie wariancji resztkowej
The wzór na wariancję resztkową przechodzi do komórki F9 i wygląda następująco:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
Gdzie SUMSQ (D1: D10) jest sumą kwadratów różnic między rzeczywistymi a oczekiwanymi wartościami Y, a (COUNT (D1: D10) -2) jest liczbą punktów danych, minus 2 dla stopni swobody w dane.
