Analiza regresji Excel (spis treści)
- Analiza regresji w programie Excel
- Wyjaśnienie regresji matematycznej
- Jak wykonać regresję liniową w programie Excel?
- # 1 - Narzędzie regresji za pomocą Analysis ToolPak w programie Excel
- # 2 - Analiza regresji za pomocą wykresu rozrzutu z linią trendu w programie Excel
Analiza regresji w programie Excel
Regresja liniowa jest techniką statystyczną, która bada liniową zależność między zmienną zależną a jedną lub większą liczbą zmiennych niezależnych.
- Zmienna zależna (inaczej zmienna odpowiedzi / wyniku): Jest to zmienna będąca przedmiotem zainteresowania i którą chcesz przewidzieć na podstawie dostępnych informacji o zmiennych niezależnych.
- Niezależna zmienna (inaczej zmienna objaśniająca / predykcyjna): Jest / są zmiennymi, od których zależy zmienna odpowiedzi. Co oznacza, że są to zmienne, za pomocą których można przewidzieć zmienną odpowiedzi.
Zależność liniowa oznacza, że zmiana zmiennej niezależnej (zmiennych niezależnych) powoduje zmianę zmiennej zależnej.
Istnieją zasadniczo dwa rodzaje relacji liniowych.
- Pozytywna zależność liniowa: gdy wzrasta zmienna niezależna, wzrasta również zmienna zależna.
- Ujemna zależność liniowa: Kiedy zmienna niezależna rośnie, zmienna zależna maleje.
To były niektóre z warunków wstępnych, zanim przystąpisz do analizy regresji w programie Excel.
Istnieją dwa podstawowe sposoby przeprowadzania regresji liniowej w programie Excel przy użyciu:
- Narzędzie regresji za pomocą Analysis ToolPak
- Wykres punktowy z linią trendu
W rzeczywistości istnieje jeszcze jedna metoda, która wykorzystuje formuły ręczne do obliczania regresji liniowej. Ale dlaczego warto to robić, skoro Excel wykonuje dla ciebie obliczenia?
Dlatego będziemy rozmawiać tylko o dwóch metodach omówionych powyżej.
Załóżmy, że masz dane dotyczące wzrostu i masy 10 osób. Jeśli wykreślisz te informacje za pomocą wykresu, zobaczmy, co daje.
Jak pokazuje powyższy zrzut ekranu, zależność liniową można znaleźć na wykresie w pozycji Wysokość i Ciężar. Nie angażuj się teraz zbytnio w wykresy, w każdym razie zamierzamy go zagłębić w drugiej części tego artykułu.
Wyjaśnienie regresji matematycznej
Mamy matematyczne wyrażenie na regresję liniową, jak poniżej:
Y = aX + b + ε
Gdzie,
- Y jest zmienną zależną lub zmienną odpowiedzi.
- X jest niezależną zmienną lub predyktorem.
- a jest nachyleniem linii regresji. Co oznacza, że gdy X zmienia się, następuje zmiana Y o jednostki „a”.
- b przechwytuje. Jest to wartość, którą przyjmuje Y, gdy wartość X wynosi zero.
- ε jest wartością błędu losowego. Występuje, ponieważ przewidywana wartość Y nigdy nie będzie dokładnie taka sama jak rzeczywista wartość dla danego X. Ten błąd nie musi się martwić. Ponieważ istnieją programy, które obliczają ten błąd w backendie. Excel jest jednym z tego oprogramowania.
W takim przypadku równanie staje się
Y = aX + b
Które mogą być reprezentowane jako:
Waga = a * Wysokość + b
Postaramy się znaleźć wartości tych aib za pomocą metod omówionych powyżej.
Jak wykonać regresję liniową w programie Excel?
Następny artykuł wyjaśnia podstawy analizy regresji w programie Excel i pokazuje kilka różnych sposobów wykonywania regresji liniowej w programie Excel.
Możesz pobrać ten szablon Excel analizy regresji tutaj - Szablon Excel analizy regresji# 1 - Narzędzie regresji za pomocą Analysis ToolPak w programie Excel
W naszym przykładzie postaramy się dopasować regresję do wartości masy (która jest zmienną zależną) za pomocą wartości wysokości (która jest zmienną niezależną).
- W arkuszu kalkulacyjnym programu Excel kliknij opcję Analiza danych (obecna w grupie analizy ) w sekcji Dane.
- Wyszukaj regresję . Wybierz i naciśnij OK.
- Użyj następujących danych wejściowych w okienku Regresja, które się otworzy.
- Zakres wejściowy Y : Wybierz komórki zawierające zmienną zależną (w tym przykładzie B1: B11)
- Wprowadź zakres X : Wybierz komórki zawierające zmienną niezależną (w tym przykładzie A1: A11).
- Zaznacz pole o nazwie Etykiety, jeśli dane mają nazwy kolumn (w tym przykładzie mamy nazwy kolumn).
- Poziom ufności jest domyślnie ustawiony na 95%, który można zmienić zgodnie z wymaganiami użytkowników.
- W obszarze Opcje wyjściowe możesz dostosować, gdzie chcesz zobaczyć wyniki analizy regresji w Excelu. W takim przypadku chcemy zobaczyć wynik na tym samym arkuszu. Dlatego odpowiednio podany zakres.
- W opcji Residuals masz opcjonalne dane wejściowe, takie jak Residuals, Residual Plots, Standaryzowane Residuals, Line Fit Plots, które możesz wybrać według swoich potrzeb. W takim przypadku zaznacz pole wyboru Resztki, abyśmy mogli zobaczyć rozrzut między przewidywanymi a rzeczywistymi wartościami.
- W opcji Normalne prawdopodobieństwo możesz wybrać Wykresy normalnego prawdopodobieństwa, które pomogą ci sprawdzić normalność predyktorów. Kliknij OK .
- Excel obliczy dla ciebie analizę regresji w ułamku sekundy.
Do tej pory było to łatwe i nie aż tak logiczne. Jednak interpretacja tego wyniku i uzyskanie cennych wglądów jest trudnym zadaniem.
Jedną ważną częścią tego całego wyniku jest kwadrat R / skorygowany kwadrat R pod tabelą PODSUMOWANIE. Który dostarcza informacji, jak dobry jest nasz model. W tym przypadku wartość R Square wynosi 0, 9547. Co oznacza, że model ma dokładność 95, 47% (dobre dopasowanie). Lub w innym języku informacja o zmiennej Y jest wyjaśniona 95, 47% przez zmienną X.
Inną ważną częścią całej produkcji jest tabela współczynników. Podaje wartości współczynników, które można wykorzystać do zbudowania modelu dla przyszłych prognoz.
Teraz nasze równanie regresji dla predykcji staje się:
Waga = 0, 6746 * Wysokość - 38, 45508 (Wartość nachylenia dla wysokości wynosi 0, 6746… a przecięcie wynosi -38, 45508…)
Czy dostałeś to, co zdefiniowałeś? Zdefiniowałeś funkcję, w której teraz musisz tylko podać wartość Wysokość, a otrzymasz wartość Ciężar.
# 2 - Analiza regresji za pomocą wykresu rozrzutu z linią trendu w programie Excel
Teraz zobaczymy, jak w programie Excel możemy dopasować równanie regresji do samego wykresu rozrzutu.
- Wybierz całe dwie kolumny danych (w tym nagłówki).
- Kliknij Wstaw i wybierz Wykres rozrzutu w sekcji wykresów, jak pokazano na poniższym obrazku.
- Zobacz wykres wyjściowy.
- Teraz musimy mieć linię regresji o najmniejszym kwadracie na tym wykresie. Aby dodać tę linię, kliknij prawym przyciskiem myszy dowolny punkt danych na wykresie i wybierz opcję Dodaj linię trendu .
- Pozwoli ci to mieć linię trendu regresji najmniejszych kwadratów jak poniżej.
- W opcji Formatuj linię trendu zaznacz pole Wyświetl równanie na wykresie.
- Pozwala zobaczyć równanie linii regresji metodą najmniejszych kwadratów na wykresie.
Jest to równanie, za pomocą którego możemy przewidzieć wartości masy dla dowolnego zestawu wartości wysokości.
O czym należy pamiętać o analizie regresji w programie Excel
- Możesz zmienić układ linii trendu w opcji Formatuj linię trendu na wykresie punktowym.
- Zawsze zaleca się przyjrzenie się wykresom resztkowym podczas analizy regresji za pomocą pakietu Data Analysis ToolPak w programie Excel. Daje to lepsze zrozumienie rozkładu rzeczywistych wartości Y i oszacowanych wartości X.
- Prosta regresja liniowa w programie Excel nie wymaga ANOVA i skorygowanego kwadratu R do sprawdzenia. Funkcje te można wziąć pod uwagę w przypadku wielokrotnej regresji liniowej. Który wykracza poza zakres tego artykułu.
Polecane artykuły
To był przewodnik po analizie regresji w programie Excel. Tutaj omawiamy, jak przeprowadzić analizę regresji w programie Excel wraz z przykładami programu Excel i szablonem programu Excel do pobrania. Możesz także przejrzeć nasze inne sugerowane artykuły -
- Narzędzie Excel do analizy danych
- Oblicz ANOVA w Excelu
- Jak znaleźć średnie ruchome Excela
- Przykłady Z TESTU w Excelu