Tworzenie modelu regresji liniowej w programie Excel
Regresja liniowa to wykres danych, który przedstawia liniową zależność między zmienną niezależną i zależną. Zwykle służy do wizualnego pokazania siły związku i rozproszenia wyników - wszystko w celu wyjaśnienia zachowania zmiennej zależnej.
Powiedzmy, że chcieliśmy sprawdzić siłę związku między ilością zjedzonych lodów a otyłością. Bierzemy zmienną niezależną, ilość lodów, i odnosimy ją do zmiennej zależnej, otyłości, aby sprawdzić, czy istnieje związek. Biorąc pod uwagę, że regresja jest graficznym przedstawieniem tej zależności, im niższa zmienność danych, tym silniejsza relacja i tym ściślejsze dopasowanie do linii regresji.
Kluczowe dania na wynos
- Regresja liniowa modeluje zależność między zmienną (zmiennymi) zależną i niezależną.
- Analizę regresji można przeprowadzić, jeśli zmienne są niezależne, nie występuje heteroscedastyczność, a terminy błędów zmiennych nie są skorelowane.
- Modelowanie regresji liniowej w programie Excel jest łatwiejsze dzięki pakietowi Data Analysis ToolPak.
Ważne uwagi
Istnieje kilka krytycznych założeń dotyczących zestawu danych, które muszą być prawdziwe, aby kontynuować analizę regresji:
- Zmienne muszą być naprawdę niezależne (przy użyciu testu chi-kwadrat).
- Dane nie mogą mieć różnych wariancji błędów (nazywa się to heteroskedastycznością (również pisaną heteroscedastycznością)).
- Warunki błędu każdej zmiennej muszą być nieskorelowane. Jeśli nie, oznacza to, że zmienne są skorelowane szeregowo.
Jeśli te trzy rzeczy wydają się skomplikowane, to są. Ale efekt nierzeczywistości jednego z tych rozważań jest tendencyjnym oszacowaniem. Zasadniczo błędnie oceniłbyś relację, którą mierzysz.
Wyprowadzanie regresji w programie Excel
Pierwszym krokiem w uruchomieniu analizy regresji w programie Excel jest dwukrotne sprawdzenie, czy zainstalowana jest bezpłatna wtyczka Excel Data Analysis ToolPak. Ta wtyczka bardzo ułatwia obliczanie zakresu statystyk. Nie jest wymagane wykreślanie linii regresji liniowej, ale ułatwia tworzenie tabel statystyk. Aby sprawdzić, czy jest zainstalowany, wybierz „Dane” z paska narzędzi. Jeśli opcja „Analiza danych” jest opcją, funkcja jest zainstalowana i gotowa do użycia. Jeśli nie jest zainstalowany, możesz poprosić o tę opcję, klikając przycisk Office i wybierając „Opcje programu Excel”.
Za pomocą pakietu narzędzi do analizy danych utworzenie wyniku regresji to tylko kilka kliknięć.
Zmienna niezależna wchodzi w zakres X.
Biorąc pod uwagę zwroty S&P 500, powiedzmy, że chcemy wiedzieć, czy możemy oszacować siłę i relację zwrotów akcji Visa (V). Akcja Visa (V) zwraca dane wypełniające kolumnę 1 jako zmienną zależną. S&P 500 zwraca dane wypełniające kolumnę 2 jako zmienną niezależną.
- Wybierz „Dane” z paska narzędzi. Zostanie wyświetlone menu „Dane”.
- Wybierz „Analiza danych”. Zostanie wyświetlone okno dialogowe Analiza danych - Narzędzia analizy.
- Z menu wybierz „Regresja” i kliknij „OK”.
- W oknie dialogowym Regresja kliknij pole „Zakres wejściowy Y” i wybierz dane zmiennych zależnych (zwroty zapasów Visa (V)).
- Kliknij pole „Input X Range” i wybierz dane zmiennej niezależnej (zwraca S&P 500).
- Kliknij „OK”, aby uruchomić wyniki.
[Uwaga: jeśli tabela wydaje się niewielka, kliknij obraz prawym przyciskiem myszy i otwórz nową kartę, aby uzyskać wyższą rozdzielczość.]
Interpretuj wyniki
Korzystając z tych danych (takich samych jak w naszym artykule z kwadratu R), otrzymujemy następującą tabelę:
Wartość R2, znana również jako współczynnik determinacji, mierzy proporcję zmienności zmiennej zależnej wyjaśnioną przez zmienną niezależną lub stopień dopasowania modelu regresji do danych. Wartość R2 wynosi od 0 do 1, a wyższa wartość oznacza lepsze dopasowanie. Wartość p lub wartość prawdopodobieństwa również mieści się w zakresie od 0 do 1 i wskazuje, czy test jest istotny. W przeciwieństwie do wartości R2 korzystniejsza jest mniejsza wartość p, ponieważ wskazuje ona na korelację między zmiennymi zależnymi i niezależnymi.
Tworzenie wykresu regresji w programie Excel
Możemy wykreślić regresję w Excelu, podświetlając dane i wykreślając je jako wykres rozproszenia. Aby dodać linię regresji, wybierz „Układ” z menu „Narzędzia wykresów”. W oknie dialogowym wybierz „Linia trendu”, a następnie „Linia trendu liniowego”. Aby dodać wartość R 2, wybierz „Więcej opcji linii trendu” z „Menu linii trendu. Na koniec wybierz„ Wyświetl wartość R-kwadrat na wykresie ”. Wynik wizualny podsumowuje siłę związku, choć kosztem niedostarczenia tyle szczegółów, ile w powyższej tabeli.