Regresja w programie Excel to sposób zautomatyzowania statystycznego procesu porównywania kilku zestawów informacji w celu sprawdzenia, jak zmiany zmiennych niezależnych wpływają na zmiany zmiennych zależnych. Jeśli kiedykolwiek chciałeś znaleźć korelację między dwiema rzeczami, użycie analizy regresji w programie Excel jest jednym z najlepszych sposobów, aby to zrobić.
Instrukcje zawarte w tym artykule dotyczą programów Excel 2019, Excel 2016, Excel 2013, Excel 2010.
Jakie jest znaczenie regresji?
Regresja to podejście do modelowania statystycznego, którego analitycy używają do określania relacji między wieloma zmiennymi.
Analiza regresji rozpoczyna się od pojedynczej zmiennej, którą próbujesz przeanalizować, oraz od zmiennych niezależnych, które testujesz, aby sprawdzić, czy mają one wpływ na tę pojedynczą zmienną. Analiza przygląda się zmianom zmiennych niezależnych i próbuje skorelować te zmiany z wynikającymi z tego zmianami w pojedynczej (zależnej) zmiennej.
Może to brzmieć jak zaawansowane statystyki, ale Excel udostępnia tę złożoną analizę każdemu.
Wykonywanie regresji liniowej w programie Excel
Najprostszą formą analizy regresji jest regresja liniowa. Prosta regresja liniowa analizuje relacje między tylko dwiema zmiennymi.
Na przykład poniższy arkusz kalkulacyjny pokazuje dane zawierające liczbę kalorii spożywanych każdego dnia oraz ich wagę w tym dniu.
Ponieważ ten arkusz kalkulacyjny zawiera dwie kolumny danych, a jedna zmienna może potencjalnie wpływać na drugą, możesz przeprowadzić analizę regresji na tych danych za pomocą programu Excel.
Włączanie dodatku Analysis ToolPak
Zanim będziesz mógł korzystać z funkcji analizy regresji programu Excel, musisz włączyć dodatek Analysis ToolPak na ekranie Opcje programu Excel.
-
W Excelu wybierz menu Plik i wybierz Opcje.
Image -
Wybierz Dodatki w lewym menu nawigacyjnym. Następnie upewnij się, że Excel Add-ins jest zaznaczone w polu Manage.
Image -
Na koniec wybierz przycisk Idź.
Image -
W wyskakującym oknie dodatków. Włącz Analysis ToolPack, klikając pole przed nim, aby dodać znacznik wyboru i wybrać OK.
Image
Teraz, gdy Analysis ToolPak jest włączony, możesz rozpocząć analizę regresji w programie Excel.
Jak wykonać prostą regresję liniową w programie Excel
Używając arkusza kalkulacyjnego wagi i kalorii jako przykładu, możesz przeprowadzić analizę regresji liniowej w programie Excel w następujący sposób.
-
Wybierz menu Dane. Następnie w grupie Analiza wybierz Analiza danych.
Image -
W oknie Analiza danych wybierz z listy Regresja i kliknij OK.
Image -
Zakres wejściowy Y to zakres komórek, który zawiera zmienną zależną. W tym przykładzie jest to waga. Zakres wejściowy X to zakres komórek, który zawiera zmienną niezależną. W tym przykładzie jest to kolumna kalorii.
Image -
Wybierz Labels dla komórek nagłówka, a następnie wybierz Nowy arkusz roboczy, aby wysłać wyniki do nowego arkusza roboczego. Wybierz OK, aby program Excel przeprowadził analizę i wysłał wyniki do nowego arkusza.
Image -
Zbadaj nowy arkusz. Wynik analizy zawiera szereg wartości, które należy zrozumieć, aby zinterpretować wyniki.
Image Każda z tych liczb ma następujące znaczenie:
- Wiele R: Współczynnik korelacji. 1 wskazuje na silną korelację między tymi dwiema zmiennymi, podczas gdy -1 oznacza, że istnieje silna negatywna zależność. 0 oznacza brak korelacji.
- R Kwadrat: Współczynnik determinacji, który pokazuje, ile punktów między dwiema zmiennymi znajduje się na linii regresji. Statystycznie jest to suma kwadratów odchyleń od średniej.
- Dostosowany kwadrat R: Wartość statystyczna zwana kwadratem R, dostosowana do liczby wybranych zmiennych niezależnych.
- Błąd standardowy: Jak dokładne są wyniki analizy regresji. Jeśli ten błąd jest mały, wyniki regresji są dokładniejsze.
- Obserwacje: Liczba obserwacji w Twoim modelu regresji.
Pozostałe wartości w wynikach regresji dostarczają szczegółowych informacji o mniejszych składnikach w analizie regresji.
- df: Wartość statystyczna znana jako stopnie swobody związane ze źródłami wariancji.
- SS: Suma kwadratów. Stosunek resztkowej sumy kwadratów do całkowitego SS powinien być mniejszy, jeśli większość danych pasuje do linii regresji.
- MS: Średni kwadrat danych regresji.
- F: Statystyka F (test F) dla hipotezy zerowej. Zapewnia to znaczenie modelu regresji.
- Istotność F: Wartość statystyczna znana jako wartość P F.
Dopóki nie rozumiesz statystyk i obliczania modeli regresji, wartości na dole podsumowania nie będą miały większego znaczenia. Jednak wielokrotne R i R kwadrat to dwa najważniejsze.
Jak widać, w tym przykładzie kalorie mają silną korelację z całkowitą wagą.
Analiza wielokrotnej regresji liniowej w programie Excel
Aby przeprowadzić tę samą regresję liniową, ale z wieloma zmiennymi niezależnymi, wybierz cały zakres (wiele kolumn i wierszy) dla Zakres wejściowy X.
Wybierając wiele zmiennych niezależnych, jest mniej prawdopodobne, że znajdziesz tak silną korelację, ponieważ jest tak wiele zmiennych.
Jednak analiza regresji w programie Excel może pomóc w znalezieniu korelacji z jedną lub kilkoma zmiennymi, o których istnieniu możesz nie wiedzieć, po prostu przeglądając dane ręcznie.
