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

---

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: 

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

 – metraż [w ]

 – piętro

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

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

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

Oszacuję zatem parametry modelu:

.

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

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 . 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ć:

 .

 

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 🙂