blog

Szybka regresja w Excelu – mało znana formuła REGLINP

Joanna

Kierownik Działu Nauczania eTrapez.
Absolwentka matematyki finansowej oraz informatyki i ekonometrii na Uniwersytecie w Białymstoku. Doświadczony korepetytor w zakresie przedmiotów matematycznych i ekonomicznych.
Mieszka w Białymstoku. Uwielbia podróżować i chodzić po górach. Wolny czas przeznacza na spotkania z rodziną i z przyjaciółmi. Lubi eksperymenty w kuchni oraz siatkówkę.


Jednym z bardzo ważnych etapów procesu modelowania ekonometrycznego jest oszacowanie jego parametrów. W moim Kursie Ekonometria poświęciłam temu zagadnieniu calutką Lekcję nr 3

W lekcji pokazałam „ręczny” sposób obliczania współczynników strukturalnych modelu. Był to sposób obliczeń z bezpośrednich wzorów (takich z sumami) w regresji prostej oraz sposób macierzowy w regresji wielorakiej. Wszystko to oczywiście z wykorzystaniem Metody Najmniejszych Kwadratów

Jednak bardzo często na zajęciach, czy też przy wykonywaniu różnorakich projektów ekonometrycznych, wykorzystuje się do obliczeń program MS Excel. Tam za pomocą funkcji „REGRESJI” można bardzo szybko i bardzo łatwo otrzymać niemal wszystkie potrzebne wyniki do oszacowania i późniejszej weryfikacji modelu. To również pokazałam w moim Kursie. 

 

W tym artykule chciałabym Ci pokazać jeszcze jedną dodatkową funkcję wykorzystywaną w programie Excel. Jest to formuła REGLINP()

Mam taki oto przykładowy model ekonometryczny, opisujący zależność wartości mieszkania od czterech zmiennych objaśniających: 

Y – wartość mieszkania [w tys. zł]

X subscript 1 – metraż [w m squared]

X subscript 2 – piętro

X subscript 3 – roczne dochody kupującego [w tys. zł]

X subscript 4 – odległość od centrum usługowego/sklepu itp. [w m]

Dokładne dane są następujące: 

Oszacuję zatem parametry modelu:

Y space equals space alpha subscript 0 space plus alpha subscript 1 X subscript 1 space plus space alpha subscript 2 X subscript 2 space plus space alpha subscript 3 X subscript 3 space plus space alpha subscript 4 X subscript 4 space plus space epsilon.

Wykorzystam do tego właśnie funkcję REGLINP(). 

Funkcja ta jest funkcją tablicową. Zatem przed jej wyborem, tzn. zanim wstawisz funkcję REGLINP(), musisz na początku przygotować miejsce dla wyniku, czyli zaznaczyć tyle komórek w WIERSZU, ile jest parametrów modelu. W naszym przykładzie będzie to 5 komórek (od alpha subscript 0 do alpha subscript 4).

Wybierz teraz z menu w zakładach FORMUŁY –> WSTAW FUNKCJĘ. Następnie w okienku, które Ci wyskoczy, poszukaj właśnie funkcji REGLINP. 

 

Po kliknięciu OK powinno wyskoczyć Ci takie oto okienko do uzupełnienia:

 

No to uzupełniamy. 

„Znane_y” to zakres wartości liczbowych zmiennej objaśnianej Y. Natomiast „Znane_x” to zakres wartości wszystkich zmiennych objaśniających X subscript i. Ustaw kursor w komórce w okienku i potem po prostu zaznacz zakres wszystkich odpowiednich liczb z tabelki z danymi.

 

Dalej. Uzupełniamy dwie ostatnie formuły. Najpierw „stała”. Należy wpisać tam jedną z dwóch wartości  – 0 lub 1. Zależy to od tego, czy nasz model jest ze stałą, to znaczy czy zawiera wyraz wolny, czy też nie. W naszym przykładzie wyraz wolny istnieje, stąd wpisuję „1”. 

 

Ostatnia komórka do uzupełnienia: „statystyka”. Podobnie jak wyżej, wpisać można tam dwie wartości logiczne: 0 (fałsz) – gdy chcesz otrzymać jedynie wartości współczynników do oszacowania lub 1 (prawda), gdy chcesz by pokazały się dodatkowe wartości statystyk modelu ekonometrycznego (np. błędy szacunkowe, współczynnik determinacji, itp.)

No to wypadałoby teraz kliknąć OK i czekać na pokazanie się wyniku.

Ale ale – moment! Po kliknięciu „OK” faktycznie pokaże Ci się wynik, ale tylko w pierwszej komórce… A reszta?

Pamiętaj, w formułach macierzowych w Excelu, gdy chcesz otrzymać ostateczny wynik we wszystkich komórkach, nie możesz kliknąć po prostu OK, ale musisz wybrać kombinację klawiszy na klawiaturze:

<CTRL>+<SHIFT>+<ENTER>.

Dopiero teraz pojawi się nasz ostateczny i w pełni poprawny wynik:

I kolejna ważna wskazówka – obliczone szacunkowe wartości parametrów są odczytywane OD TYŁU. No cóż, tak ktoś to zaprogramował 🙂 

Zatem nasz oszacowany model będzie miał postać:

Y with hat on top space equals space minus 7 comma 7969 plus 3 comma 2114 X subscript 1 space minus 0 comma 8855 X subscript 2 plus 0 comma 8168 X subscript 3 plus 0 comma 0305 X subscript 4 .

 

Mam nadzieję, że spodoba Ci się ta dodatkowa opcja wykorzystywana w Excelu. Zapraszam Cię oczywiście do mojego Kursu, tam dowiesz się co robi się dalej z tak oszacowanym modelem 🙂

Jedna z wielu opinii o naszych Kursach...

Rewelacyjny sposób prowadzenia pozwala na skuteczne zrozumienie każdego omawianego zagadnienia, co ważne każdy kurs rozpoczyna się od podstaw więc mimo braku znajomości wstępnych zagadnień każdy jest w stanie skorzystać w 100%, jak dla mnie świetna sprawa i polecam serdecznie.

Mateusz Andrzejewski

Szukasz korepetycji z matematyki na poziomie studiów lub szkoły średniej? A może potrzebujesz kursu, który przygotuje Cię do matury?

Jesteśmy ekipą eTrapez. Uczymy matematyki w sposób jasny, prosty i bardzo dokładny - trafimy nawet do najbardziej opornego na wiedzę.

Stworzyliśmy tłumaczone zrozumiałym językiem Kursy video do pobrania na komputer, tablet czy telefon. Włączasz nagranie, oglądasz i słuchasz, jak na korepetycjach. O dowolnej porze dnia i nocy.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *

Twój komentarz będzie dostępny publicznie na naszej stronie razem z powyższym podpisem. Adres email nie będzie dostępny publicznie. Komentarz możesz zmienić, lub usunąć w każdej chwili. Dane osobowe zawarte w komentarzu i podpisie traktujemy zgodnie z naszą polityką prywatności.