Jak wykonać regresję kwadratową w programie excel

Regresja to technika statystyczna, którą możemy zastosować do wyjaśnienia związku między jedną lub większą liczbą zmiennych predykcyjnych a zmienną odpowiedzi. Najpopularniejszym rodzajem regresji jest regresja liniowa , którą stosujemy, gdy związek między zmienną predykcyjną a zmienną odpowiedzi ma charakter liniowy .

Innymi słowy, gdy zmienna predykcyjna wzrasta, zmienna odpowiedzi również ma tendencję do wzrostu. Na przykład możemy użyć modelu regresji liniowej do opisania zależności pomiędzy liczbą przepracowanych godzin (zmienna predykcyjna) a oceną, jaką student otrzymuje z egzaminu (zmienna odpowiedzi).

Czasami jednak związek między zmienną predykcyjną a zmienną odpowiedzi jest nieliniowy . Powszechnym typem zależności nieliniowej jest zależność kwadratowa , która na wykresie może wyglądać jak U lub odwrócone U.

Oznacza to, że wraz ze wzrostem zmiennej predykcyjnej zmienna odpowiedzi również ma tendencję do wzrostu, ale po pewnym momencie zmienna odpowiedzi zaczyna się zmniejszać w miarę dalszego wzrostu zmiennej predykcyjnej.

Na przykład możemy użyć modelu regresji kwadratowej, aby opisać związek między liczbą godzin spędzonych w pracy a raportowanym poziomem szczęścia danej osoby. Być może im więcej ktoś pracuje, tym bardziej czuje się spełniony, ale kiedy osiągnie pewien próg, większa ilość pracy w rzeczywistości prowadzi do stresu i zmniejszenia szczęścia. W tym przypadku model regresji kwadratowej lepiej pasowałby do danych niż model regresji liniowej.

Przyjrzyjmy się przykładowi przeprowadzenia regresji kwadratowej w programie Excel.

Regresja kwadratowa w Excelu

Załóżmy, że mamy dane dotyczące liczby godzin przepracowanych tygodniowo i zgłoszonego poziomu szczęścia (w skali od 0 do 100) dla 16 różnych osób:

Najpierw utwórzmy wykres rozrzutu, aby sprawdzić, czy regresja liniowa jest odpowiednim modelem pasującym do danych.

Zaznacz komórki A2:B17 . Następnie kliknij kartę WSTAW na górnej wstążce, a następnie kliknij opcję Rozpraszanie w obszarze Wykresy . Spowoduje to utworzenie wykresu rozrzutu danych:

Łatwo zauważyć, że związek pomiędzy przepracowanymi godzinami a raportowanym szczęściem nie jest liniowy. W rzeczywistości ma kształt litery „U”, co czyni go idealnym kandydatem do regresji kwadratowej .

Przed dopasowaniem modelu regresji kwadratowej do danych musimy utworzyć nową kolumnę zawierającą kwadraty wartości naszej zmiennej predykcyjnej.

Najpierw zaznacz wszystkie wartości w kolumnie B i przeciągnij je do kolumny C.

Następnie wpisz formułę =A2^2 w komórce B2. Daje to wartość 36 . Następnie kliknij prawy dolny róg komórki B2 i przeciągnij formułę w dół, aby wypełnić pozostałe komórki w kolumnie B.

Następnie dopasujemy model regresji kwadratowej.

Kliknij DANE na górnej wstążce, a następnie kliknij opcję Analiza danych po prawej stronie. Jeśli nie widzisz tej opcji, musisz najpierw zainstalować bezpłatne oprogramowanie Analysis ToolPak .

Po kliknięciu Analiza danych pojawi się okno. Kliknij opcję Regresja , a następnie kliknij przycisk OK .

Następnie wpisz następujące wartości w wyświetlonym polu Regresja . Następnie kliknij OK .

Wyświetlone zostaną następujące wyniki:

Oto jak interpretować różne liczby w wynikach:

Kwadrat R: Znany również jako współczynnik determinacji. Jest to proporcja wariancji zmiennej odpowiedzi, którą można wyjaśnić za pomocą zmiennych predykcyjnych. W tym przykładzie R kwadrat wynosi 0,9092 , co wskazuje, że 90,92% rozbieżności w raportowanych poziomach szczęścia można wyjaśnić liczbą przepracowanych godzin i liczbą przepracowanych godzin ^2.

Błąd standardowy: Błąd standardowy regresji to średnia odległość pomiędzy obserwowanymi wartościami a linią regresji. W tym przykładzie zaobserwowane wartości odbiegają średnio o 9519 jednostek od linii regresji.

Statystyka F : Statystykę F oblicza się jako MS regresji/MS resztowe. Ta statystyka wskazuje, czy model regresji zapewnia lepsze dopasowanie do danych niż model, który nie zawiera zmiennych niezależnych. Zasadniczo sprawdza, czy model regresji jako całość jest przydatny. Ogólnie rzecz biorąc, jeśli żadna ze zmiennych predykcyjnych w modelu nie jest istotna statystycznie, ogólna statystyka F również nie jest istotna statystycznie. W tym przykładzie statystyka F wynosi 65,09 , a odpowiadająca jej wartość p wynosi <0,0001. Ponieważ ta wartość p jest mniejsza niż 0,05, model regresji jako całość jest istotny.

Współczynniki regresji: Współczynniki regresji w ostatniej tabeli dają nam liczby potrzebne do napisania oszacowanego równania regresji:

y _kapelusz = b ₀ + b ₁ x ₁ + b ₂ x ₁ ²

W tym przykładzie oszacowane równanie regresji wygląda następująco:

deklarowany poziom szczęścia = -30,252 + 7,173 (przepracowane godziny) -0,106 (przepracowane godziny) ²

Możemy użyć tego równania do obliczenia oczekiwanego poziomu szczęścia danej osoby na podstawie przepracowanych godzin. Przykładowo oczekiwany poziom szczęścia osoby pracującej 30 godzin tygodniowo wynosi:

raportowany poziom szczęścia = -30,252 + 7,173(30) -0,106(30) ² = 88,649 .

Dodatkowe zasoby

Jak dodać kwadratową linię trendu w programie Excel
Jak czytać i interpretować tabelę regresji
Jaka jest dobra wartość R-kwadrat?
Zrozumienie błędu standardowego regresji
Prosty przewodnik po zrozumieniu testu F ogólnego znaczenia w regresji