Regresja w Excelu

Regresja to jedno z narzędzi statystycznych służących do prognozowania. Regresja liniowa polega na obliczeniu statystyki za pomocą metody najmniejszych kwadratów. Powstała linia prosta jest najlepiej dopasowana do danych oraz zwraca wartości definiujące linię. W poniższym tekście zostają omówione 2 opcje wykorzystania tej funkcji w MS Excel, a mianowicie: funkcja REGLINP oraz dodatek z analizą danych.

Metoda ta cieszy się bardzo dużą popularnością – w programie MS Excel z narzędzia, jakim jest funkcja regresji można skorzystać na 3 sposoby:

• wykorzystując funkcję REGLINP,

• wykorzystując dodatek z analizy danych (Analysis ToolPak),

• pisząc odpowiednie formuły.

W artykule przedstawiono narzędzia umożliwiające obliczenie współczynników w regresji na przykładzie cen mieszkań w zależności od ich metrażu (Rys. 1). 

Rys. 1. Przykładowe ceny mieszkania w zależności od powierzchni  

Funkcja REGLINP w MS Excel

Najszybszym sposobem na obliczenie wspomnianych współczynników regresji jest skorzystanie z funkcji REGLINP. Funkcja ta przyjmuje cztery argumenty (przy czym trzy ostatnie są opcjonalne):

• REGLINP(zmienna_y; zmienna_z; czy_stała; czy_dodatkowe_statystyki),
• Zmienna y to zmienna objaśniana, czyli zmienna zależna,
• Zmienna x to jedna lub kilka zmiennych objaśniających, czyli zmiennych niezależnych,
• Argument czy_stala umożliwia sprecyzowanie, czy Excel ma obliczać stałą, czy może przyjąć ją, jako zero. Wartość tego argumentu to PRAWDA, albo FAŁSZ.

Jeśli chcemy otrzymać dodatkowe statystyki związane z regresją można to zrobić ustawiając ostatni argument funkcji jako PRAWDA, w przeciwnym przypadku jako FAŁSZ.

Znając budowę funkcji należy do komórki D2 wprowadzić formułę =REGLINP(B2:B18;A2:A18), ponieważ cena mieszkania jest zmienną zależną, natomiast metraż – zmienną niezależną (Rys. 2).

Rys. 2. Formuła obliczająca jeden ze współczynników regresji 

Komplet współczynników zostanie wyświetlony wtedy, gdy użyjemy funkcji, jako formuły tablicowej. W tym celu proszę zaznaczyć zakres komórek od D2 do E2, (ponieważ są obliczone dwa współczynniki) a następnie przejść w tryb edycji poprzez wykorzystanie przycisku funkcyjnego F2 i przy pomocy skrótu klawiszowego Ctrl + Shift + Enter, otrzymamy żądane statystyki. Efektem wykonania powyższych czynności będzie podobny do tego, co jest widoczne na Rys. 3.

Rys. 3. Obliczone współczynniki przy wykorzystaniu funkcji REGLINP 

Dodatek Analysis ToolPak w MS Excel

Kolejnym sposobem na obliczenie współczynników regresji jest skorzystanie z dodatku Excela – Analysis ToolPak.

Aby włączyć dodatek należy wybrać Opcje z menu Plik, a następnie po kliknięciu na Dodatki kliknąć na Przejdź. W nowym oknie (Rys. 4) należy zaznaczyć opcję Analisys ToolPak (jeśli jest odznaczona) oraz kliknąć OK.

Rys. 4. Dodatek Analysis ToolPak 

Dzięki powyższym czynnościom pojawi się opcja Analiza danych na karcie Dane (Rys. 5).

Rys. 5. Analiza danych na karcie Dane

Wybranie tej opcji spowoduje wyświetlenie okna, w którym należy wybrać opcję Regresja. Dzięki temu można wprowadzić odpowiednie dane. W oknie Regresja proszę uzupełnić Zakres wejściowy Y wskazując dane zmiennej objaśnianej (kolumna B). Dane dla zmiennej objaśniającej znajdują się w kolumnie A i należy je wprowadzić do pola oznaczonego nazwą Zakres wejściowy X. W przypadku, gdy zaznaczono nagłówki, to proszę zaznaczyć opcję Tytuły (Rys. 6).

Rys. 6. Regresja w analizie danych 

Akceptacji wprowadzonych danych będzie skutkować utworzeniem nowego arkusza – a wraz z nim – odpowiednich statystyk. Współczynniki regresji oznaczone są kolorem żółtym (Rys. 7).

Rys. 7. Obliczone współczynniki przy pomocy narzędzia Analizy danych 

Regresja – praktyczne zastosowanie

Cel w postaci obliczonych współczynników został osiągnięty. Jednak najważniejsze jest praktyczne zastosowanie uzyskanych wyników. Oto przedstawienie wyniku w postaci wzoru:

y = 18,35835x + 15,10643
y – zmienna objaśniana (cena mieszkania)
x – zmienna objaśniająca (metraż)

Wzrost metrażu mieszkania o jednostkę spowoduje wzrost ceny mieszkania o 18 358,35 zł. Stała 15 106,43 nie ma interpretacji.

Autorem tekstu jest Grzegorz Plak - trener Cognity.

Temat regresji jest omawiany na szkoleniach:

• Kurs Excel w Analizach Danych
• Kurs Excel Zaawansowany
• Kurs Excel i analiza statystyczna w praktyce. Analysis toolpack

Możesz być rownież zainteresowny:

• Wykres Gaussa w Excelu
• Tworzenie szablonów programie MS Excel
• 10 najważniejszych skrótów klawiaturowych w MS Excel
• Blog Excel

Regresja w Excelu by Cognity is licensed under a Creative Commons Uznanie autorstwa-Użycie niekomercyjne 4.0 Międzynarodowe License.