cognity

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.

22 sierpnia 2022
blog

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).

rys2 formuła obliczająca

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.

rys3 funkcja REGLINP

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.

rys4 dodatek Analysis ToolPack

Rys. 4. Dodatek Analysis ToolPak 

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

rys5 Analiza danych na karcie dane

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).

rys6 regresja w analizie danych

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.

Diagnoza Regresji w MS Excel

Zbieranie danych:

  • Skompletuj dane, w których masz dwie kolumny - zmienną niezależną (X) i zmienną zależną (Y).

Wstawianie wykresu punktowego:

  • Umieść dane w arkuszu kalkulacyjnym.
  • Zaznacz zakres danych.
  • Przejdź do zakładki "Wstaw" i wybierz "Wykres punktowy" (Scatter plot).

Dodawanie linii trendu:

  • Kliknij dwukrotnie na punkcie na wykresie, aby otworzyć opcje punktu.
  • Wybierz "Dodaj linię trendu" i dostosuj parametry według potrzeb.

Analiza statystyczna:

  • Kliknij prawym przyciskiem myszy na linię trendu i wybierz "Opcje trendu linii".
  • Zaznacz opcję "Wyświetl równanie na wykresie" i "Wyświetl współczynniki determinacji (R-kwadrat)".

Sprawdzanie statystyk regresji:

  • Skorzystaj z funkcji ANALIZA DANYCH (Data Analysis) w Excelu, aby uzyskać pełne statystyki regresji, w tym współczynniki regresji, istotność statystyczną, itp.

 

Regresja, narzędzie analizy statystycznej, umożliwia zrozumienie związku między zmiennymi. W przypadku regresji liniowej modeluje się, jak zmiana jednej zmiennej wpływa na inną. Kluczowe elementy podsumowania obejmują cele regresji, współczynniki regresji interpretujące związki między zmiennymi, współczynnik determinacji (R-kwadrat) mierzący dopasowanie modelu do danych, oraz testy istotności statystycznej.

Dostosowywanie modelu i unikanie nadmiernego dopasowania są istotne, a interpretacja wyników powinna być klarowna, skupiając się na praktycznym znaczeniu związku między zmiennymi. Warto również uwzględnić ograniczenia modelu i rozważyć alternatywne wyjaśnienia dla obserwowanych związków.

Autorem tekstu jest Grzegorz Plak - trener Cognity.

Kurs Microsoft Excel - tworzenie czytelnych raportów budżetowych i sprawozdań finansowych
średnio zaawansowany
cena
od 950 zł + VAT dla szkoleń otwartych
szkolenia zamknietę
Zapytaj o cenę dla szkoleń zamkniętych
Kurs Microsoft Excel - tworzenie czytelnych raportów budżetowych...
Kurs Arkusz kalkulacyjny Google - analiza danych i obliczeń, tworzenie zestawień danych oraz udostępnianie plików
początkujący
cena
od 3500 zł + VAT dla szkoleń otwartych
szkolenia zamknietę
Zapytaj o cenę dla szkoleń zamkniętych
Kurs Arkusz kalkulacyjny Google - analiza danych i obliczeń...
Kurs Microsoft Excel - obsługa programu przy pomocy praktycznych technik
początkujący
cena
od 495 zł + VAT dla szkoleń otwartych
szkolenia zamknietę
Zapytaj o cenę dla szkoleń zamkniętych
Kurs Microsoft Excel - obsługa programu...
Kurs Microsoft PowerPoint i Excel Business Intelligence (PowerPivot, Power Map, Power BI Desktop) - tworzenie profesjonalnych prezentacji w Microsoft PowerPoint i efektywne przetwarzanie danych w Microsoft Power BI Desktop
początkujący
cena
od 3500 zł + VAT dla szkoleń otwartych
szkolenia zamknietę
Zapytaj o cenę dla szkoleń zamkniętych
Kurs Microsoft PowerPoint i Excel Business Intelligence...
Kurs Microsoft Excel średniozaawansowany
średnio zaawansowany
cena
od 790 zł + VAT dla szkoleń otwartych
szkolenia zamknietę
Zapytaj o cenę dla szkoleń zamkniętych
Kurs Microsoft Excel średniozaawansowany...
Kurs Microsoft Excel średniozaawansowany - formuły, funkcje, wykresy oraz wprowadzenie do tabel przestawnych i makr
średnio zaawansowany
cena
od 790 zł + VAT dla szkoleń otwartych
szkolenia zamknietę
Zapytaj o cenę dla szkoleń zamkniętych
Kurs Microsoft Excel średniozaawansowany - formuły, funkcje, wykresy...
Kurs Microsoft Excel zaawansowany
zaawansowany
cena
od 850 zł + VAT dla szkoleń otwartych
szkolenia zamknietę
Zapytaj o cenę dla szkoleń zamkniętych
Kurs Microsoft Excel zaawansowany...
Kurs Microsoft Excel zaawansowany - usprawnienie pracy programu dzięki zaawansowanym narzędziom: tabelom przestawnym i makropoleceniom
zaawansowany
cena
od 850 zł + VAT dla szkoleń otwartych
szkolenia zamknietę
Zapytaj o cenę dla szkoleń zamkniętych
Kurs Microsoft Excel zaawansowany - usprawnienie pracy programu...
Kurs Microsoft Excel podstawowy - formatowanie danych, tworzenie estetycznych wykresów i efektywnych raportów
początkujący
cena
od 750 zł + VAT dla szkoleń otwartych
szkolenia zamknietę
Zapytaj o cenę dla szkoleń zamkniętych
Kurs Microsoft Excel podstawowy - formatowanie danych...
Kurs Microsoft Excel podstawowy
początkujący
cena
od 750 zł + VAT dla szkoleń otwartych
szkolenia zamknietę
Zapytaj o cenę dla szkoleń zamkniętych
Kurs Microsoft Excel podstawowy...
Kurs Microsoft Excel zaawansowany - usprawnienie pracy programu dzięki zaawansowanym narzędziom: tabelom przestawnym i makropoleceniom
zaawansowany
cena
od 850 zł + VAT dla szkoleń otwartych
szkolenia zamknietę
Zapytaj o cenę dla szkoleń zamkniętych
Kurs Microsoft Excel zaawansowany - usprawnienie pracy programu...
icon

Formularz kontaktowyContact form

Imię *Name
NazwiskoSurname
Adres e-mail *E-mail address
Telefon *Phone number
UwagiComments