Metoda najmniejszych kwadratów w Excelu. Analiza regresji

Metoda najmniejszych kwadratów (OLS) odnosi się dosfera analizy regresji. Ma wiele zastosowań, ponieważ umożliwia przybliżoną reprezentację danej funkcji przez inne prostsze. OLS może być niezwykle przydatny w przetwarzaniu obserwacji i jest aktywnie wykorzystywany do szacowania niektórych wielkości na podstawie wyników pomiarów innych, które zawierają błędy losowe. W tym artykule dowiesz się, jak zaimplementować obliczenia metodą najmniejszych kwadratów w programie Excel.

Opis problemu na konkretnym przykładzie

Załóżmy, że istnieją dwa wykładniki X i Y.Co więcej, Y zależy od X. Ponieważ OLS interesuje nas z punktu widzenia analizy regresji (w Excelu jej metody są implementowane za pomocą wbudowanych funkcji), to należy od razu przejść do rozważenia konkretnego problemu.

Niech więc X będzie powierzchnią sklepu spożywczego mierzoną w metrach kwadratowych, a Y - rocznym obrotem mierzonym w milionach rubli.

Wymagane jest sporządzenie prognozy jaki obrót (Y)będzie w sklepie, jeśli ma taką lub inną powierzchnię handlową. Oczywiście funkcja Y = f (X) rośnie, ponieważ hipermarket sprzedaje więcej towarów niż stragan.

Kilka słów o poprawności danych wyjściowych użytych do predykcji

Załóżmy, że mamy tabelę zbudowaną z danych dla n sklepów.

Według statystyk matematycznych wynikibędą mniej lub bardziej poprawne, jeśli zbadane zostaną dane dotyczące co najmniej 5-6 obiektów. Ponadto nie można używać wyników „nienormalnych”. W szczególności elitarny mały butik może mieć obroty wielokrotnie większe niż obroty dużych punktów sprzedaży detalicznej klasy „masmarket”.

Istota metody

Dane tabeli można wykreślić na płaszczyźnie kartezjańskiej jako punkty M₁ (x₁, tak₁),… M_Pan (x_Pan, tak_Pan). Teraz rozwiązanie problemu sprowadzi się do wyboru funkcji aproksymującej y = f (x) z wykresem przechodzącym jak najbliżej punktów M_1, M._{2, ..}M._Pan.

Oczywiście możesz użyć wysokiego wielomianustopnia, ale ta opcja jest nie tylko trudna do wdrożenia, ale także po prostu niepoprawna, ponieważ nie będzie odzwierciedlać głównego trendu, który należy wykryć. Najrozsądniejszym rozwiązaniem jest znalezienie prostej y = ax + b, która najlepiej przybliża dane eksperymentalne, a raczej współczynniki - a i b.

Ocena dokładności

Dla każdego przybliżenia szczególnie ważna jest ocena jego dokładności. Oznaczamy przez e_i różnica (odchylenie) między wartościami funkcjonalnymi i eksperymentalnymi dla punktu x_i, czyli e_i = y_i - f (x_i).

Oczywiście, aby oszacować dokładność przybliżeniamożna zastosować sumę odchyleń, to znaczy wybierając linię prostą do przybliżonego przedstawienia zależności X od Y, należy preferować tę o najmniejszej wartości sumy e_i we wszystkich rozważanych punktach. Jednak nie wszystko jest takie proste, ponieważ wraz z odchyleniami dodatnimi praktycznie będą obecne odchylenia ujemne.

Możesz rozwiązać ten problem za pomocą modułów odchyleńlub ich kwadraty. Ta ostatnia metoda jest najczęściej stosowana. Jest używany w wielu obszarach, w tym w analizie regresji (Excel implementuje ją z dwiema wbudowanymi funkcjami) i od dawna sprawdza się.

Metoda najmniejszych kwadratów

Wiadomo, że Excel ma wbudowanąfunkcja autosumowania, która oblicza wartości wszystkich wartości znajdujących się w wybranym zakresie. Nic więc nie stoi na przeszkodzie, aby obliczyć wartość wyrażenia (e₁² + e₂² + e₃²+ ... e_Pan²).

W notacji matematycznej wygląda to tak:

Ponieważ początkowo podjęto decyzję o aproksymacji za pomocą linii prostej, mamy:

Zatem problem znalezienia prostej najlepiej opisującej określoną zależność wielkości X i Y sprowadza się do obliczenia minimum funkcji dwóch zmiennych:

Wymaga to zrównania do zera pochodnych cząstkowych po nowych zmiennych a i b oraz rozwiązania układu pierwotnego składającego się z dwóch równań z 2 niewiadomymi postaci:

Po kilku prostych przekształceniach, w tym dzieleniu przez 2 i manipulowaniu sumami, otrzymujemy:

Rozwiązując go np. metodą Cramera otrzymujemy punkt stacjonarny o pewnych współczynnikach a^* oraz b^*... Jest to minimum, czyli do przewidzenia jaki obrót będzie miał sklep dla określonej powierzchni, linia prosta y = a^*x + b^*, który jest modelem regresji dlaomawiany przykład. Oczywiście nie pozwoli Ci to znaleźć dokładnego wyniku, ale pomoże zorientować się, czy zakup sklepu na kredyt na konkretną okolicę się opłaci.

Jak zaimplementować metodę najmniejszych kwadratów w Excelu?

Excel ma funkcję obliczania wartościprzez OLS. Ma następującą postać: „TREND” (znane wartości Y; znane wartości X; nowe wartości X; stała). Zastosujmy do naszej tabeli wzór obliczania OLS w Excelu.

Aby to zrobić, w komórce, w której powinno byćwyświetlany jest wynik obliczeń metodą najmniejszych kwadratów w Excelu, należy wpisać znak „=” i wybrać funkcję „TREND”. W oknie, które się otworzy, wypełnij odpowiednie pola, podkreślając:

• zakres znanych wartości dla Y (w tym przypadku dane dotyczące obrotów);
• zakres x₁,… X_Pan, czyli wielkość powierzchni handlowej;
• oraz znane i nieznane wartości x, dla których należy znaleźć wielkość obrotu (informacje o ich lokalizacji na arkuszu znajdują się poniżej).

Dodatkowo formuła zawiera zmienną logiczną „Const”. Jeśli wpiszesz 1 w odpowiednim polu, będzie to oznaczało, że obliczenia należy wykonać przy założeniu, że b = 0.

Jeśli chcesz znać prognozę dla więcej niż jednej wartości x, to po wprowadzeniu formuły nie powinieneś naciskać „Enter”, ale musisz wpisać na klawiaturze kombinację „Shift” + „Control” + „Enter” ("Wejść").

Niektóre funkcje

Analiza regresji może być nawet dostępnaczajniki. Formuła Excela do przewidywania wartości tablicy nieznanych zmiennych - "TREND" - może być używana nawet przez tych, którzy nigdy nie słyszeli o metodzie najmniejszych kwadratów. Wystarczy poznać niektóre cechy jej twórczości. W szczególności:

• Jeśli ustawisz zakres znanych wartościzmienna y w jednym wierszu lub kolumnie, to każdy wiersz (kolumna) ze znanymi wartościami x będzie postrzegany przez program jako osobna zmienna.
• Jeśli okno „TREND” nie zawiera zakresu zznanym x, to w przypadku użycia funkcji w Excelu program potraktuje ją jako tablicę złożoną z liczb całkowitych, których liczba odpowiada zakresowi o podanych wartościach zmiennej y.
• Aby uzyskać tablicę „przewidywanych” wartości jako dane wyjściowe, wyrażenie trendu należy wprowadzić jako formułę tablicową.
• Jeśli nie określono nowych wartości x, to funkcja"TREND" uważa je za równie znane. Jeśli nie są podane, jako argument przyjmuje się tablicę 1; 2; 3; 4;…, co jest współmierne do zakresu o podanych już parametrach y.
• Zakres zawierający nowe wartości x musiskładać się z tego samego lub większej liczby wierszy lub kolumn, co zakres z podanymi wartościami y. Innymi słowy, powinien być współmierny do zmiennych niezależnych.
• W tablicy ze znanymi wartościami x możezawierać kilka zmiennych. Jeśli jednak mówimy tylko o jednym, to wymagane jest, aby przedziały o podanych wartościach x i y były współmierne. W przypadku wielu zmiennych chcesz, aby zakres z podanymi wartościami y mieścił się w jednej kolumnie lub jednym wierszu.

funkcja PROGNOZA

Analiza regresji w Excelu jest zaimplementowana za pomocąza pomocą kilku funkcji. Jeden z nich nosi nazwę „PROGNOZA”. Jest podobny do „TREND”, czyli daje wynik obliczeń metodą najmniejszych kwadratów. Jednak tylko dla jednego X, dla którego wartość Y jest nieznana.

Teraz znasz formuły w Excelu dla manekinów, które pozwalają przewidzieć przyszłą wartość danego wskaźnika zgodnie z trendem liniowym.