Metoda najmniejszych kwadratów dla paraboli. Aproksymacja danych eksperymentalnych. Metoda najmniejszych kwadratów

Programowanie

• Instruktaż

Wstęp

Jestem matematykiem i programistą. Największym krokiem w mojej karierze był moment, gdy nauczyłem się mówić: "Niczego nierozumiem!" Teraz nie wstydzę się powiedzieć luminarzowi nauki, że wygłasza dla mnie wykład, że nie rozumiem, co on, luminarz, mówi mi. I to jest bardzo trudne. Tak, przyznanie się do swojej niewiedzy jest trudne i zawstydzające. Kto lubi przyznać się do tego, że nie zna jakiejś podstawy? Ze względu na zawód muszę być obecny duże ilości prezentacje i wykłady, na których, przyznaję, w zdecydowanej większości przypadków chce mi się spać, bo nic nie rozumiem. Ale nie rozumiem, bo ogromny problem obecnej sytuacji w nauce leży w matematyce. Zakłada, że ​​wszyscy słuchacze znają absolutnie wszystkie dziedziny matematyki (co jest absurdem). Przyznanie się, że nie wiesz, czym jest pochodna (o tym, czym jest, porozmawiamy nieco później) jest wstydliwe.

Ale nauczyłem się mówić, że nie wiem, co to jest mnożenie. Tak, nie wiem, czym jest podalgebra w stosunku do algebry Liego. Tak, nie wiem, po co są potrzebne w życiu równania kwadratowe. Swoją drogą, jeśli jesteś pewien, że wiesz, to mamy o czym rozmawiać! Matematyka to seria sztuczek. Matematycy próbują dezorientować i zastraszać opinię publiczną; gdzie nie ma zamieszania, nie ma reputacji, nie ma autorytetu. Tak, mówienie możliwie abstrakcyjnym językiem jest prestiżem, co jest kompletną bzdurą.

Czy wiesz, co to jest pochodna? Najprawdopodobniej powiesz mi o granicy stosunku różnicy. Na pierwszym roku matematyki i mechaniki na Uniwersytecie Państwowym w Petersburgu powiedział mi Wiktor Pietrowicz Chawin określony pochodna jako współczynnik pierwszego wyrazu szeregu Taylora funkcji w punkcie (była to osobna gimnastyka wyznaczania szeregu Taylora bez pochodnych). Długo się śmiałem z tej definicji, aż w końcu zrozumiałem, o co w niej chodzi. Pochodna to nic innego jak prosta miara tego, jak podobna jest funkcja, którą różniczkujemy, do funkcji y=x, y=x^2, y=x^3.

Teraz mam zaszczyt prowadzić wykłady dla studentów, którzy przestraszony matematyka. Jeśli boisz się matematyki, jesteśmy na tej samej ścieżce. Gdy tylko spróbujesz przeczytać jakiś tekst i wydaje Ci się, że jest on zbyt skomplikowany, to wiedz, że jest słabo napisany. Twierdzę, że nie ma takiego obszaru matematyki, którego nie da się omówić „na palcach” bez utraty dokładności.

Zadanie na najbliższą przyszłość: Poleciłem moim uczniom zrozumienie, czym jest liniowy regulator kwadratowy. Nie wstydź się, poświęć trzy minuty swojego życia i kliknij link. Jeśli niczego nie rozumiesz, oznacza to, że jesteśmy na tej samej ścieżce. Ja (zawodowy matematyk-programista) też nic nie rozumiałem. Zapewniam, że można to rozgryźć „na palcach”. NA ten moment Nie wiem, co to jest, ale zapewniam, że możemy to rozgryźć.

Zatem pierwszy wykład, jaki wygłoszę moim studentom po tym, jak przybiegną do mnie z przerażeniem i powiedzą, że regulator liniowo-kwadratowy to straszna rzecz, której nigdy w życiu nie opanujecie, to metody najmniejszych kwadratów. Czy możesz zdecydować równania liniowe? Jeśli czytasz ten tekst, to najprawdopodobniej nie.

Zatem mając dane dwa punkty (x0, y0), (x1, y1), na przykład (1,1) i (3,2), zadaniem jest znalezienie równania prostej przechodzącej przez te dwa punkty:

ilustracja

Linia ta powinna mieć równanie podobne do poniższego:

Tutaj alfa i beta nie są nam znane, ale znane są dwa punkty tej linii:

Równanie to możemy zapisać w postaci macierzowej:

W tym miejscu należy dokonać lirycznej dygresji: czym jest matrix? Macierz to nic innego jak tablica dwuwymiarowa. Jest to sposób przechowywania danych i nie należy do niego przywiązywać żadnego innego znaczenia. Od nas zależy, jak dokładnie zinterpretujemy daną macierz. Okresowo będę to interpretował jako odwzorowanie liniowe, okresowo jako postać kwadratową, a czasami po prostu jako zbiór wektorów. Wszystko zostanie wyjaśnione w kontekście.

Zastąpmy konkretne macierze ich symboliczną reprezentacją:

Następnie (alfa, beta) można łatwo znaleźć:

Dokładniej dla naszych poprzednich danych:

Co prowadzi do następującego równania prostej przechodzącej przez punkty (1,1) i (3,2):

OK, tutaj wszystko jest jasne. Znajdźmy równanie prostej przechodzącej przez nią trzy punkty: (x0,y0), (x1,y1) i (x2,y2):

Och, och, och, ale mamy trzy równania z dwiema niewiadomymi! Zwykły matematyk powie, że nie ma rozwiązania. Co powie programista? I najpierw przepisze poprzedni układ równań w następującej formie:

W naszym przypadku wektory i,j,b są trójwymiarowe, zatem (w ogólnym przypadku) nie ma rozwiązania dla tego układu. Dowolny wektor (alfa\*i + beta\*j) leży w płaszczyźnie rozpiętej przez wektory (i, j). Jeśli b nie należy do tej płaszczyzny, to nie ma rozwiązania (w równaniu nie można osiągnąć równości). Co robić? Szukajmy kompromisu. Oznaczmy przez e(alfa, beta) dokładnie, jak daleko nie osiągnęliśmy równości:

Postaramy się zminimalizować ten błąd:

Dlaczego kwadratowy?

Szukamy nie tylko minimum normy, ale także minimum kwadratu normy. Dlaczego? Sam punkt minimalny pokrywa się, a kwadrat daje funkcję gładką (funkcję kwadratową argumentów (alfa, beta)), natomiast sama długość daje funkcję w kształcie stożka, niezróżniczkowalną w punkcie minimalnym. Br. Kwadrat jest wygodniejszy.

Oczywiście błąd jest minimalizowany, gdy wektor mi prostopadłe do płaszczyzny rozpiętej na wektorach I I J.

Ilustracja

Innymi słowy: szukamy takiej prostej, aby suma kwadratów długości odległości wszystkich punktów od tej prostej była minimalna:

AKTUALIZACJA: Mam tutaj problem, odległość do linii prostej należy mierzyć w pionie, a nie w rzucie ortogonalnym. Komentator ma rację.

Ilustracja

Zupełnie innymi słowami (ostrożnie, słabo sformalizowany, ale powinno być jasne): bierzemy wszystkie możliwe linie pomiędzy wszystkimi parami punktów i szukamy średniej linii pomiędzy wszystkimi:

Ilustracja

Inne wyjaśnienie jest proste: dołączamy sprężynę pomiędzy wszystkimi punktami danych (tutaj mamy trzy) a linią prostą, której szukamy, a linia prosta stanu równowagi jest dokładnie tym, czego szukamy.

Minimalna forma kwadratowa

Biorąc pod uwagę ten wektor B oraz płaszczyzna rozpięta wektorami kolumnowymi macierzy A(W w tym przypadku(x0,x1,x2) i (1,1,1)), szukamy wektora mi o minimalnej długości kwadratowej. Oczywiście minimum można osiągnąć tylko dla wektora mi, prostopadła do płaszczyzny rozpiętej przez wektory kolumnowe macierzy A:

Inaczej mówiąc, szukamy wektora x=(alfa, beta) takiego, że:

Przypomnę, że ten wektor x=(alfa, beta) jest minimum funkcja kwadratowa||e(alfa, beta)||^2:

W tym miejscu warto pamiętać, że macierz można interpretować także w postaci kwadratowej, np. macierz jednostkowa ((1,0),(0,1)) można interpretować jako funkcję x^2 + y^ 2:

forma kwadratowa

Cała ta gimnastyka znana jest pod nazwą regresji liniowej.

Równanie Laplace'a z warunkiem brzegowym Dirichleta

Teraz najprościej prawdziwe wyzwanie: istnieje pewna trójkątna powierzchnia, należy ją wygładzić. Na przykład załadujmy model mojej twarzy:

Oryginalne zatwierdzenie jest dostępne. Minimalizować zależności zewnętrzne Wziąłem kod mojego oprogramowania renderującego, już na Habré. Dla rozwiązań układ liniowy Ja używam OpenNL, jest to doskonały solwer, który jednak jest bardzo trudny w instalacji: trzeba skopiować dwa pliki (.h+.c) do folderu z projektem. Całe wygładzanie odbywa się za pomocą następującego kodu:

Dla (int d=0; d<3; d++) { nlNewContext(); nlSolverParameteri(NL_NB_VARIABLES, verts.size()); nlSolverParameteri(NL_LEAST_SQUARES, NL_TRUE); nlBegin(NL_SYSTEM); nlBegin(NL_MATRIX); for (int i=0; i<(int)verts.size(); i++) { nlBegin(NL_ROW); nlCoefficient(i, 1); nlRightHandSide(verts[i][d]); nlEnd(NL_ROW); } for (unsigned int i=0; i&twarz = twarze[i]; dla (int j=0; j<3; j++) { nlBegin(NL_ROW); nlCoefficient(face[ j ], 1); nlCoefficient(face[(j+1)%3], -1); nlEnd(NL_ROW); } } nlEnd(NL_MATRIX); nlEnd(NL_SYSTEM); nlSolve(); for (int i=0; i<(int)verts.size(); i++) { verts[i][d] = nlGetVariable(i); } }

Współrzędne X, Y i Z są rozłączne, wygładzam je osobno. Oznacza to, że rozwiązuję trzy układy równań liniowych, każdy z liczbą zmiennych równą liczbie wierzchołków mojego modelu. W pierwszych n wierszach macierzy A znajduje się tylko jedna cyfra 1 w każdym wierszu, a pierwsze n wierszy wektora b ma oryginalne współrzędne modelu. Oznacza to, że wiążę sprężynę pomiędzy nową pozycją wierzchołka a starą pozycją wierzchołka - nowe nie powinny zbytnio oddalać się od starych.

We wszystkich kolejnych wierszach macierzy A (faces.size()*3 = liczba krawędzi wszystkich trójkątów w siatce) występuje jedno wystąpienie wartości 1 i jedno wystąpienie -1, przy czym wektor b ma przeciwne składowe zerowe. Oznacza to, że umieściłem sprężynę na każdej krawędzi naszej trójkątnej siatki: wszystkie krawędzie starają się uzyskać ten sam wierzchołek, co ich punkt początkowy i końcowy.

Jeszcze raz: wszystkie wierzchołki są zmienne i nie mogą oddalić się od swojego pierwotnego położenia, ale jednocześnie starają się upodobnić do siebie.

Oto wynik:

Wszystko byłoby w porządku, model rzeczywiście jest wygładzony, jednak odszedł od pierwotnej krawędzi. Zmieńmy trochę kod:

Dla (int i=0; tj<(int)verts.size(); i++) { float scale = border[i] ? 1000: 1; nlBegin(NL_ROW); nlCoefficient(i, scale); nlRightHandSide(scale*verts[i][d]); nlEnd(NL_ROW); }

W naszej macierzy A dla wierzchołków znajdujących się na krawędzi dodaję nie wiersz z kategorii v_i = verts[i][d], ale 1000*v_i = 1000*verts[i][d]. Co to zmienia? A to zmienia naszą kwadratową postać błędu. Teraz pojedyncze odchylenie od góry przy krawędzi będzie kosztować nie jedną jednostkę, jak poprzednio, ale 1000*1000 jednostek. Oznacza to, że na skrajnych wierzchołkach zawiesiliśmy mocniejszą sprężynę, rozwiązanie będzie wolało mocniej naciągnąć pozostałe. Oto wynik:

Podwoimy siłę sprężyny między wierzchołkami:
nlWspółczynnik(twarz[j], 2); nlWspółczynnik(twarz[(j+1)%3], -2);

Logiczne jest, że powierzchnia stała się gładsza:

A teraz jeszcze sto razy silniejszy:

Co to jest? Wyobraź sobie, że zanurzyliśmy druciany pierścień w wodzie z mydłem. W rezultacie powstały film mydlany będzie starał się mieć jak najmniejszą krzywiznę, dotykając granicy - naszego drucianego pierścienia. Dokładnie to uzyskaliśmy ustalając brzeg i prosząc o gładką powierzchnię wewnątrz. Gratulacje, właśnie rozwiązaliśmy równanie Laplace'a z warunkami brzegowymi Dirichleta. Brzmi nieźle? Ale w rzeczywistości wystarczy rozwiązać jeden układ równań liniowych.

Równanie Poissona

Zapamiętajmy kolejną fajną nazwę.

Powiedzmy, że mam taki obraz:

Wszystkim się podoba, ale mi nie podoba się to krzesło.

Przetnę zdjęcie na pół:

I wybiorę krzesło własnymi rękami:

Następnie przeciągnę wszystko, co białe w masce na lewą stronę obrazu, a jednocześnie na całym obrazie powiem, że różnica między dwoma sąsiednimi pikselami powinna być równa różnicy między dwoma sąsiednimi pikselami po prawej stronie zdjęcie:

Dla (int i=0; tj

Oto wynik:

Dostępny kod i zdjęcia

Przykład.

Dane eksperymentalne dotyczące wartości zmiennych X I Na podano w tabeli.

W wyniku ich wyrównania uzyskuje się funkcję

Za pomocą metoda najmniejszych kwadratów, aproksymuj te dane za pomocą zależności liniowej y=topór+b(znajdź parametry A I B). Dowiedz się, która z dwóch linii lepiej (w sensie metody najmniejszych kwadratów) wyrównuje dane eksperymentalne. Narysuj coś.

Istota metody najmniejszych kwadratów (LSM).

Zadanie polega na znalezieniu współczynników zależności liniowej, przy której funkcjonuje funkcja dwóch zmiennych A I B przyjmuje najmniejszą wartość. To znaczy, dane A I B suma kwadratów odchyleń danych eksperymentalnych od znalezionej prostej będzie najmniejsza. Na tym polega cały sens metody najmniejszych kwadratów.

Zatem rozwiązanie przykładu sprowadza się do znalezienia ekstremum funkcji dwóch zmiennych.

Wyprowadzanie wzorów na znalezienie współczynników.

Układ dwóch równań z dwiema niewiadomymi jest kompilowany i rozwiązywany. Znajdowanie pochodnych cząstkowych funkcji przez zmienne A I B, przyrównujemy te pochodne do zera.

Powstały układ równań rozwiązujemy dowolną metodą (np metodą podstawieniową Lub Metoda Cramera) i uzyskać wzory na znalezienie współczynników metodą najmniejszych kwadratów (LSM).

Dany A I B funkcjonować przyjmuje najmniejszą wartość. Podano dowód tego faktu poniżej w tekście na końcu strony.

To cała metoda najmniejszych kwadratów. Wzór na znalezienie parametru A zawiera sumy ,, i parametr N- ilość danych eksperymentalnych. Zalecamy oddzielne obliczanie wartości tych kwot. Współczynnik B znalezione po obliczeniach A.

Czas przypomnieć sobie oryginalny przykład.

Rozwiązanie.

W naszym przykładzie n=5. Wypełniamy tabelę dla wygody obliczenia kwot uwzględnionych we wzorach wymaganych współczynników.

Wartości w czwartym wierszu tabeli uzyskuje się poprzez pomnożenie wartości drugiego wiersza przez wartości trzeciego wiersza dla każdej liczby I.

Wartości w piątym wierszu tabeli uzyskuje się przez podniesienie do kwadratu wartości w drugim wierszu dla każdej liczby I.

Wartości w ostatniej kolumnie tabeli są sumami wartości w wierszach.

Do znalezienia współczynników używamy wzorów metody najmniejszych kwadratów A I B. Podstawiamy do nich odpowiednie wartości z ostatniej kolumny tabeli:

Stąd, y = 0,165x+2,184- żądana przybliżająca linia prosta.

Pozostaje dowiedzieć się, która z linii y = 0,165x+2,184 Lub lepiej przybliża oryginalne dane, czyli dokonuje oszacowania metodą najmniejszych kwadratów.

Estymacja błędu metodą najmniejszych kwadratów.

Aby to zrobić, musisz obliczyć sumę kwadratów odchyleń oryginalnych danych od tych linii I , mniejsza wartość odpowiada linii, która lepiej przybliża oryginalne dane w sensie metody najmniejszych kwadratów.

Od , potem prosto y = 0,165x+2,184 lepiej przybliża oryginalne dane.

Graficzna ilustracja metody najmniejszych kwadratów (LS).

Wszystko doskonale widać na wykresach. Czerwona linia to znaleziona linia prosta y = 0,165x+2,184, niebieska linia to , różowe kropki to dane oryginalne.

W praktyce przy modelowaniu różnych procesów - w szczególności ekonomicznych, fizycznych, technicznych, społecznych - powszechnie stosuje się tę lub inną metodę obliczania przybliżonych wartości funkcji na podstawie ich znanych wartości w określonych punktach stałych.

Często pojawia się tego rodzaju problem aproksymacji funkcji:

przy konstruowaniu przybliżonych wzorów do obliczania wartości wielkości charakterystycznych badanego procesu na podstawie danych tabelarycznych uzyskanych w wyniku eksperymentu;

w całkowaniu numerycznym, różniczkowaniu, rozwiązywaniu równań różniczkowych itp.;

w razie potrzeby obliczyć wartości funkcji w punktach pośrednich rozpatrywanego przedziału;

przy wyznaczaniu wartości wielkości charakterystycznych procesu poza rozpatrywanym przedziałem, w szczególności przy prognozowaniu.

Jeżeli do modelowania pewnego procesu określonego tabelą skonstruujemy funkcję, która w przybliżeniu opisuje ten proces w oparciu o metodę najmniejszych kwadratów, będzie to nazywać się funkcją aproksymującą (regresją), a samo zadanie konstruowania funkcji aproksymujących będzie nazywane problem przybliżenia.

W artykule omówiono możliwości pakietu MS Excel do rozwiązywania tego typu problemów, ponadto przedstawiono metody i techniki konstruowania (tworzenia) regresji dla funkcji tabelarycznych (co jest podstawą analizy regresji).

W programie Excel dostępne są dwie opcje tworzenia regresji.

Dodanie wybranych regresji (linii trendu) do diagramu zbudowanego na podstawie tabeli danych dla badanej charakterystyki procesu (dostępne tylko w przypadku zbudowania diagramu);

Wykorzystanie wbudowanych funkcji statystycznych arkusza Excel, pozwalających na uzyskanie regresji (linii trendu) bezpośrednio z tabeli danych źródłowych.

Dodawanie linii trendu do wykresu

W przypadku tabeli danych opisującej proces i przedstawionej w postaci diagramu Excel udostępnia skuteczne narzędzie do analizy regresji, które umożliwia:

budować w oparciu o metodę najmniejszych kwadratów i dodawać do diagramu pięć rodzajów regresji, które modelują badany proces z różnym stopniem dokładności;

dodaj do diagramu skonstruowane równanie regresji;

określić stopień zgodności wybranej regresji z danymi wyświetlanymi na wykresie.

Na podstawie danych wykresowych Excel pozwala uzyskać regresje liniowe, wielomianowe, logarytmiczne, potęgowe, wykładnicze, które są określone równaniem:

y = y(x)

gdzie x jest zmienną niezależną, która często przyjmuje wartości ciągu liczb naturalnych (1; 2; 3; ...) i daje na przykład odliczenie czasu badanego procesu (charakterystyka).

1 . Regresja liniowa jest dobra do modelowania cech, których wartości rosną lub maleją w stałym tempie. Jest to najprostszy model do skonstruowania dla badanego procesu. Konstruuje się go według równania:

y = mx + b

gdzie m jest tangensem kąta nachylenia regresja liniowa do osi odciętej; b - współrzędna punktu przecięcia regresji liniowej z osią rzędnych.

2 . Linia trendu wielomianowego jest przydatna do opisywania cech, które mają kilka różnych ekstremów (maksimów i minimów). O wyborze stopnia wielomianu decyduje liczba ekstremów badanej cechy. Zatem wielomian drugiego stopnia może dobrze opisać proces, który ma tylko jedno maksimum lub minimum; wielomian trzeciego stopnia - nie więcej niż dwa ekstrema; wielomian czwartego stopnia - nie więcej niż trzy ekstrema itp.

W tym przypadku linia trendu jest konstruowana zgodnie z równaniem:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

gdzie współczynniki c0, c1, c2,... c6 są stałymi, których wartości wyznaczane są w trakcie budowy.

3 . Linię trendu logarytmicznego z powodzeniem stosuje się przy modelowaniu cech, których wartości początkowo szybko się zmieniają, a następnie stopniowo stabilizują.

y = do ln(x) + b

4 . Linia trendu prawa potęgowego daje dobre wyniki, jeśli wartości badanej zależności charakteryzują się stałą zmianą tempa wzrostu. Przykładem takiej zależności jest wykres ruchu samochodu z jednostajnym przyspieszeniem. Jeśli w danych znajdują się wartości zerowe lub ujemne, nie można użyć linii trendu mocy.

Zbudowane zgodnie z równaniem:

y = doxb

gdzie współczynniki b, c są stałymi.

5 . Jeśli tempo zmian danych stale rośnie, należy zastosować linię trendu wykładniczego. W przypadku danych zawierających wartości zerowe lub ujemne ten rodzaj przybliżenia również nie ma zastosowania.

Zbudowane zgodnie z równaniem:

y = do ebx

gdzie współczynniki b, c są stałymi.

Wybierając linię trendu, Excel automatycznie oblicza wartość R2, która charakteryzuje niezawodność aproksymacji: im wartość R2 jest bliższa jedności, tym bardziej wiarygodnie linia trendu przybliża badany proces. W razie potrzeby wartość R2 można zawsze wyświetlić na wykresie.

Określone według wzoru:

Aby dodać linię trendu do serii danych:

aktywuj wykres na podstawie serii danych, czyli kliknij w obszarze wykresu. W menu głównym pojawi się pozycja Diagram;

po kliknięciu tej pozycji na ekranie pojawi się menu, w którym należy wybrać polecenie Dodaj linię trendu.

Te same działania można łatwo wykonać przesuwając wskaźnik myszy nad wykresem odpowiadającym jednej z serii danych i klikając prawym przyciskiem myszy; W wyświetlonym menu kontekstowym wybierz polecenie Dodaj linię trendu. Na ekranie pojawi się okno dialogowe Trendline z otwartą zakładką Type (rys. 1).

Następnie potrzebujesz:

Wybierz żądany typ linii trendu na karcie Typ (domyślnie wybrany jest typ Liniowy). W przypadku typu Wielomian w polu Stopień określ stopień wybranego wielomianu.

1 . Pole Seria zbudowana na podstawie zawiera listę wszystkich serii danych na danym wykresie. Aby dodać linię trendu do określonej serii danych, wybierz jej nazwę w polu Zbudowana na serii.

W razie potrzeby wchodząc w zakładkę Parametry (rys. 2) można ustawić następujące parametry linii trendu:

zmienić nazwę linii trendu w polu Nazwa krzywej aproksymowanej (wygładzonej).

w polu Prognoza ustaw liczbę okresów (do przodu lub do tyłu) prognozy;

wyświetlić równanie linii trendu w obszarze wykresu, dla którego należy włączyć opcję pokazuj równanie na wykresie;

wyświetlić w obszarze wykresu wartość wiarygodności aproksymacji R2, dla której należy zaznaczyć opcję Umieść na wykresie wartość wiarygodności aproksymacji (R^2);

ustawić punkt przecięcia linii trendu z osią Y, dla którego należy zaznaczyć checkbox przecięcia krzywej z osią Y w punkcie;

Kliknij przycisk OK, aby zamknąć okno dialogowe.

Aby rozpocząć edycję narysowanej już linii trendu, można skorzystać z trzech sposobów:

użyj polecenia Wybrana linia trendu z menu Format, po wcześniejszym wybraniu linii trendu;

z menu kontekstowego wybierz polecenie Formatuj linię trendu, które wywołuje się klikając prawym przyciskiem myszy na linię trendu;

kliknij dwukrotnie linię trendu.

Na ekranie pojawi się okno dialogowe Format linii trendu (rys. 3), zawierające trzy zakładki: Widok, Typ, Parametry, przy czym zawartość dwóch ostatnich całkowicie pokrywa się z podobnymi zakładkami okna dialogowego Linia trendu (rys. 1). -2). Na karcie Widok możesz ustawić rodzaj linii, jej kolor i grubość.

Aby usunąć narysowaną już linię trendu, wybierz linię trendu do usunięcia i naciśnij klawisz Delete.

Zaletami rozważanego narzędzia analizy regresji są:

względna łatwość konstruowania linii trendu na wykresach bez tworzenia dla niej tabeli danych;

dość szeroka lista typów proponowanych linii trendu, a lista ta obejmuje najczęściej stosowane typy regresji;

umiejętność przewidywania zachowania badanego procesu poprzez dowolną (w granicach zdrowego rozsądku) liczbę kroków do przodu, a także do tyłu;

możliwość otrzymania równania linii trendu w formie analitycznej;

możliwość, w razie potrzeby, uzyskania oceny wiarygodności przybliżenia.

Wady obejmują:

konstrukcja linii trendu odbywa się tylko wtedy, gdy istnieje diagram zbudowany na serii danych;

proces generowania serii danych dla badanej cechy na podstawie uzyskanych dla niej równań linii trendu jest nieco zaśmiecony: wymagane równania regresji są aktualizowane przy każdej zmianie wartości oryginalnej serii danych, ale tylko w obszarze wykresu , natomiast szeregi danych utworzone na podstawie trendu starego równania liniowego pozostają niezmienione;

W raportach wykresu przestawnego zmiana widoku wykresu lub powiązanego raportu w formie tabeli przestawnej nie powoduje zachowania istniejących linii trendu, co oznacza, że ​​przed narysowaniem linii trendu lub innym formatowaniem raportu w formie wykresu przestawnego należy upewnić się, że układ raportu spełnia wymagane wymagania.

Linie trendu można wykorzystać do uzupełnienia serii danych prezentowanych na wykresach, takich jak wykresy, histogramy, płaskie, niestandaryzowane wykresy warstwowe, wykresy słupkowe, wykresy punktowe, wykresy bąbelkowe i wykresy giełdowe.

Nie można dodawać linii trendu do serii danych na wykresach 3D, znormalizowanych, radarowych, kołowych i pierścieniowych.

Korzystanie z wbudowanych funkcji programu Excel

Excel posiada także narzędzie do analizy regresji umożliwiające wykreślanie linii trendu poza obszarem wykresu. Istnieje wiele funkcji arkusza statystycznego, których można użyć w tym celu, ale wszystkie pozwalają jedynie na budowanie regresji liniowej lub wykładniczej.

Excel ma kilka funkcji do konstruowania regresji liniowej, w szczególności:

TENDENCJA;

• NACHYLENIE i CIĘCIE.

A także kilka funkcji do konstruowania wykładniczej linii trendu, w szczególności:

LGRFPRIBL.

Należy zauważyć, że techniki konstruowania regresji przy użyciu funkcji TREND i WZROST są prawie takie same. To samo można powiedzieć o parze funkcji LINEST i LGRFPRIBL. W przypadku tych czterech funkcji przy tworzeniu tabeli wartości wykorzystuje się funkcje Excela takie jak formuły tablicowe, co nieco zaśmieca proces budowania regresji. Zauważmy też, że konstrukcję regresji liniowej naszym zdaniem najłatwiej przeprowadzić korzystając z funkcji SLOPE i INTERCEPT, gdzie pierwsza z nich wyznacza nachylenie regresji liniowej, a druga wyznacza odcinek przechwycony przez regresję na oś Y.

Zalety wbudowanego narzędzia funkcyjnego do analizy regresji to:

dość prosty, jednolity proces generowania serii danych o badanej charakterystyce dla wszystkich wbudowanych funkcji statystycznych wyznaczających linie trendu;

standardowa metodyka konstruowania linii trendu na podstawie wygenerowanych serii danych;

umiejętność przewidywania zachowania badanego procesu poprzez wymaganą liczbę kroków do przodu lub do tyłu.

Do wad można zaliczyć fakt, że Excel nie posiada wbudowanych funkcji umożliwiających tworzenie innych (poza liniowymi i wykładniczymi) typów linii trendu. Okoliczność ta często nie pozwala na wybór wystarczająco dokładnego modelu badanego procesu, a także na uzyskanie prognoz bliskich rzeczywistości. Dodatkowo przy korzystaniu z funkcji TREND i WZROST nie są znane równania linii trendu.

Należy zaznaczyć, że autorzy nie postawili sobie za cel przedstawienia przebiegu analizy regresji w jakimkolwiek stopniu kompletności. Jego głównym zadaniem jest pokazanie na konkretnych przykładach możliwości pakietu Excel przy rozwiązywaniu problemów aproksymacyjnych; zademonstrować, jakie skuteczne narzędzia ma Excel do budowania regresji i prognozowania; ilustrują, jak takie problemy mogą być stosunkowo łatwo rozwiązane nawet przez użytkownika, który nie ma rozległej wiedzy na temat analizy regresji.

Przykłady rozwiązania konkretnych problemów

Przyjrzyjmy się rozwiązywaniu konkretnych problemów za pomocą wymienionych narzędzi Excela.

Problem 1

Z tabelą danych o zyskach przedsiębiorstwa transportu samochodowego za lata 1995-2002. musisz wykonać następujące czynności:

Zbuduj diagram.

Dodaj do wykresu linie trendu liniowego i wielomianowego (kwadratowego i sześciennego).

Korzystając z równań linii trendu, uzyskaj dane tabelaryczne dotyczące zysków przedsiębiorstw dla każdej linii trendu w latach 1995-2004.

Proszę o prognozę zysków przedsiębiorstwa na lata 2003 i 2004.

Rozwiązanie problemu

W obszarze komórek A4:C11 arkusza Excel wpisz arkusz pokazany na ryc. 4.

Po wybraniu zakresu komórek B4:C11 budujemy diagram.

Aktywujemy skonstruowany diagram i zgodnie z metodą opisaną powyżej, po wybraniu rodzaju linii trendu w oknie dialogowym Linia trendu (patrz rys. 1), dodajemy do wykresu naprzemiennie linie trendu liniowego, kwadratowego i sześciennego. W tym samym oknie dialogowym należy otworzyć zakładkę Parametry (patrz rys. 2), w polu Nazwa krzywej aproksymowanej (wygładzanej) wpisać nazwę dodawanego trendu, a w polu Prognoza do przodu na: okresy ustawić wartość wartość 2, gdyż planuje się sporządzenie prognozy zysków na dwa lata do przodu. Aby wyświetlić równanie regresji i wartość niezawodności aproksymacji R2 w obszarze wykresu, należy włączyć opcję pokazywania równania na ekranie i umieścić na wykresie wartość wiarygodności aproksymacji (R^2). Dla lepszej percepcji wizualnej zmieniamy rodzaj, kolor i grubość konstruowanych linii trendu, do czego służy zakładka Widok okna dialogowego Format linii trendu (patrz rys. 3). Powstały diagram z dodanymi liniami trendu pokazano na ryc. 5.

Uzyskanie danych tabelarycznych o zyskach przedsiębiorstw dla każdej linii trendu za lata 1995-2004. Skorzystajmy z równań linii trendu przedstawionych na ryc. 5. W tym celu w komórkach zakresu D3:F3 należy wpisać informację tekstową o rodzaju wybranej linii trendu: Trend liniowy, Trend kwadratowy, Trend sześcienny. Następnie wpisz formułę regresji liniowej w komórce D4 i korzystając ze znacznika wypełnienia, skopiuj tę formułę z odniesieniami względnymi do zakresu komórek D5:D13. Należy zaznaczyć, że każda komórka posiadająca formułę regresji liniowej z zakresu komórek D4:D13 ma jako argument odpowiadającą komórkę z zakresu A4:A13. Podobnie w przypadku regresji kwadratowej wypełnij zakres komórek E4:E13, a w przypadku regresji sześciennej wypełnij zakres komórek F4:F13. W związku z tym sporządzono prognozę zysków przedsiębiorstwa na lata 2003 i 2004. wykorzystując trzy trendy. Wynikową tabelę wartości pokazano na ryc. 6.

Problem 2

Zbuduj diagram.

Dodaj do wykresu linie trendu logarytmicznego, potęgowego i wykładniczego.

Wyprowadź równania uzyskanych linii trendu, a także wartości niezawodności przybliżenia R2 dla każdej z nich.

Korzystając z równań linii trendu, uzyskaj dane tabelaryczne dotyczące zysku przedsiębiorstwa dla każdej linii trendu za lata 1995-2002.

Korzystając z tych linii trendu, sporządź prognozę zysków firmy na lata 2003 i 2004.

Rozwiązanie problemu

Kierując się metodologią podaną przy rozwiązaniu zadania 1, otrzymujemy diagram z dodanymi do niego liniami trendu logarytmicznego, potęgowego i wykładniczego (rys. 7). Następnie korzystając z otrzymanych równań linii trendu wypełniamy tabelę wartości zysku przedsiębiorstwa zawierającą przewidywane wartości na rok 2003 i 2004. (ryc. 8).

Na ryc. 5 i rys. widać, że model z trendem logarytmicznym odpowiada najniższej wartości niezawodności aproksymacji

R2 = 0,8659

Największe wartości R2 odpowiadają modelom o trendzie wielomianowym: kwadratowym (R2 = 0,9263) i sześciennym (R2 = 0,933).

Problem 3

Mając do dyspozycji tabelę danych o zyskach przedsiębiorstwa transportu samochodowego za lata 1995-2002 podaną w zadaniu 1 należy wykonać następujące czynności.

Uzyskaj serie danych dla linii trendu liniowego i wykładniczego za pomocą funkcji TREND i GROW.

Korzystając z funkcji TREND i WZROST, oszacuj prognozę zysków przedsiębiorstwa na lata 2003 i 2004.

Utwórz diagram dla oryginalnych danych i wynikowych serii danych.

Rozwiązanie problemu

Skorzystajmy z arkusza ćwiczeń dla zadania 1 (patrz rys. 4). Zacznijmy od funkcji TREND:

wybierz zakres komórek D4:D11, który należy wypełnić wartościami funkcji TREND odpowiadającymi znanym danym o zysku przedsiębiorstwa;

Wywołaj polecenie Funkcja z menu Wstaw. W wyświetlonym oknie dialogowym Kreator funkcji wybierz funkcję TREND z kategorii Statystyka, a następnie kliknij przycisk OK. Tę samą operację można wykonać, klikając przycisk (Wstaw funkcję) na standardowym pasku narzędzi.

W wyświetlonym oknie dialogowym Argumenty funkcji wprowadź zakres komórek C4:C11 w polu Znane_wartości_y; w polu Znane_wartości_x - zakres komórek B4:B11;

Aby wprowadzona formuła stała się formułą tablicową należy użyć kombinacji klawiszy + + .

Formuła, którą wpisaliśmy w pasku formuły, będzie wyglądać następująco: =(TREND(C4:C11,B4:B11)).

W rezultacie zakres komórek D4:D11 zostaje wypełniony odpowiednimi wartościami funkcji TREND (rys. 9).

Sporządzenie prognozy zysków przedsiębiorstwa na lata 2003 i 2004. niezbędny:

wybierz zakres komórek D12:D13, w którym zostaną wprowadzone wartości przewidywane przez funkcję TREND.

wywołaj funkcję TREND i w wyświetlonym oknie Argumenty funkcji wpisz w polu Znane_wartości_y - zakres komórek C4:C11; w polu Znane_wartości_x - zakres komórek B4:B11; oraz w polu Nowe_wartości_x - zakres komórek B12:B13.

zamień tę formułę w formułę tablicową, używając kombinacji klawiszy Ctrl + Shift + Enter.

Wprowadzona formuła będzie wyglądać następująco: =(TREND(C4:C11;B4:B11;B12:B13)), a zakres komórek D12:D13 zostanie wypełniony przewidywanymi wartościami funkcji TREND (patrz rys. 9).

Serię danych wypełnia się w podobny sposób za pomocą funkcji WZROST, która służy do analizy zależności nieliniowych i działa dokładnie tak samo, jak jej liniowy odpowiednik TREND.

Rysunek 10 przedstawia tabelę w trybie wyświetlania formuły.

Dla danych początkowych i otrzymanych serii danych schemat pokazany na rys. jedenaście.

Problem 4

Mając tabelę danych o przyjęciu wniosków o usługi przez służbę spedycyjną przedsiębiorstwa transportu samochodowego za okres od 1 do 11 dnia bieżącego miesiąca, należy wykonać następujące czynności.

Uzyskaj serie danych dla regresji liniowej: za pomocą funkcji SLOPE i INTERCEPT; za pomocą funkcji REGLINP.

Uzyskaj serię danych do regresji wykładniczej za pomocą funkcji LGRFPRIBL.

Korzystając z powyższych funkcji, sporządź prognozę wpływu wniosków do działu spedycyjnego na okres od 12 do 14 dnia bieżącego miesiąca.

Utwórz diagram dla oryginalnej i otrzymanej serii danych.

Rozwiązanie problemu

Należy zauważyć, że w przeciwieństwie do funkcji TREND i WZROST żadna z funkcji wymienionych powyżej (NACHYLENIE, PRZECIĘCIE, REGLINP, LGRFPRIB) nie jest regresją. Funkcje te pełnią jedynie rolę pomocniczą, wyznaczając niezbędne parametry regresji.

W przypadku regresji liniowych i wykładniczych budowanych za pomocą funkcji SLOPE, INTERCEPT, LINEST, LGRFPRIB zawsze znany jest wygląd ich równań, w przeciwieństwie do regresji liniowych i wykładniczych odpowiadających funkcjom TREND i GROWTH.

1 . Zbudujmy regresję liniową za pomocą równania:

y = mx+b

przy użyciu funkcji SLOPE i INTERCEPT, przy czym nachylenie regresji m jest określone funkcją SLOPE, a człon wolny b jest określany przez funkcję INTERCEPT.

W tym celu wykonujemy następujące czynności:

wprowadź oryginalną tabelę do zakresu komórek A4:B14;

wartość parametru m zostanie określona w komórce C19. Wybierz funkcję Nachylenie z kategorii Statystyka; wpisz zakres komórek B4:B14 w polu znane_wartości_y oraz zakres komórek A4:A14 w polu znane_wartości_x. Formuła zostanie wpisana w komórkę C19: =NACHYLENIE(B4:B14,A4:A14);

W podobny sposób określa się wartość parametru b w komórce D19. A jego zawartość będzie wyglądać następująco: =SEGMENT(B4:B14,A4:A14). Zatem wartości parametrów m i b wymagane do skonstruowania regresji liniowej zostaną zapisane odpowiednio w komórkach C19, D19;

Następnie wprowadź formułę regresji liniowej do komórki C4 w postaci: =$C*A4+$D. W tej formule komórki C19 i D19 zapisywane są z odwołaniami bezwzględnymi (adres komórki nie powinien zmieniać się podczas ewentualnego kopiowania). Znak odniesienia bezwzględnego $ można wpisać z klawiatury lub przy pomocy klawisza F4, po umieszczeniu kursora na adresie komórki. Używając uchwytu wypełniania, skopiuj tę formułę do zakresu komórek C4:C17. Otrzymujemy wymagane serie danych (ryc. 12). Z uwagi na to, że liczba żądań jest liczbą całkowitą, należy w zakładce Liczba okna Format komórki ustawić format liczb z liczbą miejsc po przecinku na 0.

2 . Zbudujmy teraz regresję liniową określoną równaniem:

y = mx+b

za pomocą funkcji REGLINP.

Dla tego:

Wprowadź funkcję REGLINP jako formułę tablicową w zakresie komórek C20:D20: =(LINEST(B4:B14,A4:A14)). W rezultacie otrzymujemy wartość parametru m w komórce C20 i wartość parametru b w komórce D20;

wpisz formułę w komórce D4: =$C*A4+$D;

skopiuj tę formułę za pomocą znacznika wypełnienia do zakresu komórek D4:D17 i uzyskaj żądaną serię danych.

3 . Regresję wykładniczą budujemy za pomocą równania:

korzystając z funkcji LGRFPRIBL wykonuje się to analogicznie:

W zakresie komórek C21:D21 wpisujemy funkcję LGRFPRIBL w postaci formuły tablicowej: =( LGRFPRIBL (B4:B14,A4:A14)). W tym przypadku wartość parametru m zostanie określona w komórce C21, a wartość parametru b zostanie określona w komórce D21;

formułę wpisuje się do komórki E4: =$D*$C^A4;

za pomocą znacznika wypełnienia formuła ta jest kopiowana do zakresu komórek E4:E17, gdzie będzie zlokalizowany szereg danych dla regresji wykładniczej (patrz rys. 12).

Na ryc. Rysunek 13 przedstawia tabelę, w której możesz zobaczyć funkcje, których używamy z wymaganymi zakresami komórek, a także formuły.

Ogrom R 2 zwany współczynnik determinacji.

Zadaniem konstrukcji zależności regresyjnej jest znalezienie wektora współczynników m modelu (1), przy którym współczynnik R przyjmuje wartość maksymalną.

Do oceny istotności R wykorzystuje się test F Fishera, obliczany ze wzoru

Gdzie N- wielkość próby (liczba eksperymentów);

k jest liczbą współczynników modelu.

Jeśli F przekracza pewną wartość krytyczną dla danych N I k i przyjęte prawdopodobieństwo ufności, wówczas wartość R uważa się za znaczącą. Tabele wartości krytycznych F podano w podręcznikach dotyczących statystyki matematycznej.

Zatem o istotności R decyduje nie tylko jego wartość, ale także stosunek liczby eksperymentów do liczby współczynników (parametrów) modelu. Rzeczywiście, współczynnik korelacji dla n=2 dla prostego modelu liniowego wynosi 1 (pojedynczą linię prostą można zawsze poprowadzić przez 2 punkty na płaszczyźnie). Jeśli jednak danymi eksperymentalnymi są zmienne losowe, takiej wartości R należy ufać z dużą ostrożnością. Zwykle, aby uzyskać istotny R i wiarygodną regresję, dążą do tego, aby liczba eksperymentów znacznie przekraczała liczbę współczynników modelu (n>k).

Aby zbudować model regresji liniowej, potrzebujesz:

1) przygotować listę n wierszy i m kolumn zawierających dane eksperymentalne (kolumna zawierająca wartość wyjściową Y musi być pierwszy lub ostatni na liście); Weźmy np. dane z poprzedniego zadania, dodając kolumnę o nazwie „Nr okresu”, ponumerujmy numery okresów od 1 do 12. (będą to wartości X)

2) przejdź do menu Dane/Analiza danych/Regresja

Jeżeli w menu „Narzędzia” brakuje pozycji „Analiza danych”, należy w tym samym menu przejść do pozycji „Dodatki” i zaznaczyć pole wyboru „Pakiet analiz”.

3) w oknie dialogowym „Regresja” ustaw:

· przedział wejściowy Y;

· przedział wejściowy X;

· przedział wyjściowy - lewa górna komórka przedziału, w którym będą umieszczane wyniki obliczeń (zaleca się umieszczenie ich na nowym arkuszu);

4) kliknij „OK” i przeanalizuj wyniki.

Metoda najmniejszych kwadratów

Metoda najmniejszych kwadratów ( OLS, OLS, zwykła metoda najmniejszych kwadratów) - jedna z podstawowych metod analizy regresji służąca do estymacji nieznanych parametrów modeli regresji na podstawie przykładowych danych. Metoda polega na minimalizacji sumy kwadratów reszt regresji.

Należy zauważyć, że samą metodę najmniejszych kwadratów można nazwać metodą rozwiązywania problemu w dowolnym obszarze, jeśli rozwiązanie leży lub spełnia jakieś kryterium minimalizacji sumy kwadratów niektórych funkcji wymaganych zmiennych. Zatem metodę najmniejszych kwadratów można zastosować także do przybliżonego przedstawienia (aproksymacji) danej funkcji przez inne (prostsze) funkcje, gdy znajdziemy zbiór wielkości spełniających równania lub ograniczenia, których liczba przekracza liczbę tych wielkości itp.

Esencja MNC

Niech zostanie podany jakiś (parametryczny) model probabilistycznej (regresji) zależności pomiędzy (objaśnioną) zmienną y i wiele czynników (zmiennych objaśniających) X

gdzie jest wektorem nieznanych parametrów modelu

- losowy błąd modelu.

Niech będą też przykładowe obserwacje wartości tych zmiennych. Niech będzie numerem obserwacji (). Następnie są wartości zmiennych w obserwacji. Następnie dla zadanych wartości parametrów b można obliczyć teoretyczne (modelowe) wartości zmiennej objaśnianej y:

Wielkość reszt zależy od wartości parametrów b.

Istotą metody najmniejszych kwadratów (zwykłej, klasycznej) jest znalezienie parametrów b, dla których suma kwadratów reszt (ang. Pozostała suma kwadratów) będzie minimalne:

W ogólnym przypadku problem ten można rozwiązać metodami optymalizacji numerycznej (minimalizacji). W tym przypadku o tym mówią nieliniowa metoda najmniejszych kwadratów(NLS lub NLLS – angielski) Nieliniowa metoda najmniejszych kwadratów). W wielu przypadkach możliwe jest otrzymanie rozwiązania analitycznego. Aby rozwiązać problem minimalizacji, należy znaleźć punkty stacjonarne funkcji, różniczkując ją ze względu na nieznane parametry b, przyrównując pochodne do zera i rozwiązując otrzymany układ równań:

Jeśli błędy losowe modelu mają rozkład normalny, mają tę samą wariancję i są nieskorelowane, oszacowania parametrów OLS są takie same jak oszacowania największej wiarygodności (MLM).

OLS w przypadku modelu liniowego

Niech zależność regresji będzie liniowa:

Pozwalać y jest wektorem kolumnowym obserwacji zmiennej objaśnianej i jest macierzą obserwacji czynnikowych (wiersze macierzy są wektorami wartości czynników w danej obserwacji, kolumny są wektorem wartości danego czynnika we wszystkich obserwacjach). Reprezentacja macierzowa modelu liniowego to:

Wtedy wektor oszacowań zmiennej objaśnianej i wektor reszt regresji będą równe

W związku z tym suma kwadratów reszt regresji będzie równa

Różniczkując tę ​​funkcję względem wektora parametrów i przyrównując pochodne do zera, otrzymujemy układ równań (w postaci macierzowej):

.

Rozwiązanie tego układu równań daje ogólny wzór na szacunki metodą najmniejszych kwadratów dla modelu liniowego:

Dla celów analitycznych przydatna jest druga reprezentacja tego wzoru. Jeśli w modelu regresji data wyśrodkowany, wówczas w tej reprezentacji pierwsza macierz ma znaczenie przykładowej macierzy kowariancji czynników, a druga jest wektorem kowariancji czynników ze zmienną zależną. Jeśli dodatkowo dane są również znormalizowany do MSE (czyli ostatecznie standaryzowane), wówczas pierwsza macierz ma znaczenie przykładowej macierzy korelacji czynników, drugi wektor - wektor przykładowych korelacji czynników ze zmienną zależną.

Ważna właściwość szacunków OLS dla modeli ze stałą- linia skonstruowanej regresji przechodzi przez środek ciężkości danych próbnych, czyli spełniona jest równość:

W szczególności w skrajnym przypadku, gdy jedynym regresorem jest stała, stwierdzamy, że estymacja OLS jedynego parametru (samej stałej) jest równa średniej wartości zmiennej objaśnianej. Oznacza to, że średnia arytmetyczna, znana ze swoich dobrych własności z praw wielkich liczb, jest jednocześnie estymacją metodą najmniejszych kwadratów – spełnia kryterium minimalnej sumy kwadratów odchyleń od niej.

Przykład: najprostsza regresja (parami).

W przypadku sparowanej regresji liniowej wzory obliczeniowe są uproszczone (można obejść się bez algebry macierzowej):

Własności estymatorów OLS

Przede wszystkim zauważamy, że w przypadku modeli liniowych estymatory OLS są estymatorami liniowymi, jak wynika z powyższego wzoru. Dla bezstronnych estymatorów OLS konieczne i wystarczające jest spełnienie najważniejszego warunku analizy regresji: matematyczne oczekiwanie błędu losowego, uzależnione od czynników, musi być równe zero. Warunek ten jest w szczególności spełniony, jeżeli

1. matematyczne oczekiwanie błędów losowych wynosi zero, oraz
2. czynniki i błędy losowe są niezależnymi zmiennymi losowymi.

Warunek drugi – warunek egzogeniczności czynników – jest zasadniczy. Jeśli ta właściwość nie jest spełniona, możemy założyć, że prawie wszystkie szacunki będą wyjątkowo niezadowalające: nie będą nawet spójne (to znaczy nawet bardzo duża ilość danych nie pozwala nam w tym przypadku uzyskać szacunków wysokiej jakości) ). W klasycznym przypadku przyjmuje się mocniejsze założenie o determinizmie czynników, w przeciwieństwie do błędu losowego, co automatycznie oznacza, że ​​warunek egzogeniczności jest spełniony. W ogólnym przypadku, dla spójności estymatorów wystarczy spełnienie warunku egzogeniczności wraz ze zbieżnością macierzy do jakiejś macierzy nieosobliwej w miarę zwiększania się liczebności próby do nieskończoności.

Aby oprócz spójności i bezstronności estymacje metodą (zwykłych) najmniejszych kwadratów były także efektywne (najlepsze w klasie estymatorów liniowych nieobciążonych), muszą zostać spełnione dodatkowe właściwości błędu losowego:

Założenia te można sformułować dla macierzy kowariancji wektora błędu losowego

Model liniowy spełniający te warunki nazywa się klasyczny. Szacunki OLS dla klasycznej regresji liniowej są bezstronnymi, spójnymi i najbardziej efektywnymi estymacjami w klasie wszystkich liniowych nieobciążonych estymatorów (w literaturze angielskiej czasami używany jest skrót NIEBIESKI (Najlepszy liniowy estymator bez podstawy) - najlepsze liniowe, nieobciążone oszacowanie; w literaturze rosyjskiej częściej przytacza się twierdzenie Gaussa-Markowa). Jak łatwo wykazać, macierz kowariancji wektora oszacowań współczynników będzie równa:

Uogólnione OLS

Metoda najmniejszych kwadratów pozwala na szerokie uogólnienia. Zamiast minimalizować sumę kwadratów reszt, można zminimalizować pewną dodatnio określoną postać kwadratową wektora reszt, gdzie jest pewna symetryczna macierz dodatnich określonych wag. Szczególnym przypadkiem tego podejścia są konwencjonalne metody najmniejszych kwadratów, gdzie macierz wag jest proporcjonalna do macierzy jednostkowej. Jak wiadomo z teorii macierzy symetrycznych (lub operatorów), dla takich macierzy następuje rozkład. W związku z tym określony funkcjonał można przedstawić w następujący sposób, to znaczy funkcjonał ten można przedstawić jako sumę kwadratów niektórych przekształconych „reszt”. Można zatem wyróżnić klasę metod najmniejszych kwadratów – metody LS (ang. Least Squares).

Udowodniono (twierdzenie Aitkena), że dla uogólnionego modelu regresji liniowej (w którym nie nakłada się ograniczeń na macierz kowariancji błędów losowych) najbardziej efektywne (w klasie liniowych estymatorów nieobciążonych) są tzw. estymaty. uogólnione najmniejsze kwadraty (GLS – uogólnione najmniejsze kwadraty)- metoda LS z macierzą wag równą macierzy odwrotnej kowariancji błędów losowych: .

Można wykazać, że wzór na estymatory GLS parametrów modelu liniowego ma postać

Macierz kowariancji tych szacunków będzie odpowiednio równa

Tak naprawdę istota OLS polega na pewnej (liniowej) transformacji (P) danych pierwotnych i zastosowaniu zwykłego OLS do danych przekształconych. Celem tej transformacji jest to, że dla przekształconych danych błędy losowe spełniają już klasyczne założenia.

Ważony OLS

W przypadku diagonalnej macierzy wag (a więc i macierzy kowariancji błędów losowych) mamy do czynienia z tzw. ważoną metodą najmniejszych kwadratów (WLS). W tym przypadku suma ważona kwadratów reszt modelu jest minimalizowana, czyli każda obserwacja otrzymuje „wagę” odwrotnie proporcjonalną do wariancji błędu losowego w tej obserwacji: . W rzeczywistości dane są przekształcane poprzez ważenie obserwacji (podzielenie przez kwotę proporcjonalną do oszacowanego odchylenia standardowego błędów losowych), a do danych ważonych stosuje się zwykły OLS.

Kilka szczególnych przypadków wykorzystania MNC w praktyce

Aproksymacja zależności liniowej

Rozważmy przypadek, gdy w wyniku badania zależności pewnej wielkości skalarnej od pewnej wielkości skalarnej (może to być na przykład zależność napięcia od natężenia prądu: , gdzie jest wartością stałą, rezystancja przewodnik) przeprowadzono pomiary tych wielkości, w wyniku czego otrzymano wartości i odpowiadające im wartości. Dane pomiarowe należy zapisać w tabeli.

Tabela. Wyniki pomiarów.

Pomiar nr.
1
2
3
4
5
6

Pytanie brzmi: jaką wartość współczynnika można wybrać, aby najlepiej opisać zależność? Według metody najmniejszych kwadratów wartość ta powinna być taka, aby suma kwadratów odchyleń wartości od wartości

był minimalny

Suma kwadratów odchyleń ma jedno ekstremum – minimum, co pozwala nam zastosować ten wzór. Znajdźmy z tego wzoru wartość współczynnika. Aby to zrobić, przekształcamy jego lewą stronę w następujący sposób:

Ostatni wzór pozwala nam znaleźć wartość współczynnika, czyli to, co było wymagane w zadaniu.

Fabuła

Do początków XIX wieku. naukowcy nie mieli pewnych zasad rozwiązywania układu równań, w którym liczba niewiadomych jest mniejsza niż liczba równań; Do tego czasu stosowano techniki prywatne, zależne od rodzaju równań i dowcipu kalkulatorów, dlatego różne kalkulatory, bazując na tych samych danych obserwacyjnych, dochodziły do ​​różnych wniosków. Gauss (1795) jako pierwszy zastosował tę metodę, a Legendre (1805) niezależnie odkrył ją i opublikował pod jej współczesną nazwą (francuską. Méthode des moindres quarrés ) . Laplace powiązał tę metodę z teorią prawdopodobieństwa, a amerykański matematyk Adrain (1808) rozważał jej zastosowania w teorii prawdopodobieństwa. Metoda ta była szeroko rozpowszechniona i udoskonalona dzięki dalszym badaniom Encke, Bessela, Hansena i innych.

Alternatywne zastosowania OLS

Ideę metody najmniejszych kwadratów można zastosować także w innych przypadkach niezwiązanych bezpośrednio z analizą regresji. Faktem jest, że suma kwadratów jest jedną z najczęstszych miar bliskości wektorów (metryka euklidesowa w przestrzeniach skończenie wymiarowych).

Jednym z zastosowań jest „rozwiązanie” układów równań liniowych, w których liczba równań jest większa niż liczba zmiennych

gdzie macierz nie jest kwadratowa, ale prostokątna.

Taki układ równań w ogólnym przypadku nie ma rozwiązania (jeśli ranga jest rzeczywiście większa niż liczba zmiennych). Zatem układ ten można „rozwiązać” jedynie w sensie doboru takiego wektora, aby zminimalizować „odległość” pomiędzy wektorami a . Można w tym celu zastosować kryterium minimalizacji sumy kwadratów różnic pomiędzy lewą i prawą stroną równań układu, tj. Łatwo pokazać, że rozwiązanie tego problemu minimalizacji prowadzi do rozwiązania następującego układu równań

Ma wiele zastosowań, gdyż pozwala na przybliżone przedstawienie danej funkcji za pomocą innych, prostszych. LSM 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 zawierających błędy losowe. W tym artykule dowiesz się, jak wdrożyć obliczenia metodą najmniejszych kwadratów w programie Excel.

Sformułowanie problemu na konkretnym przykładzie

Załóżmy, że istnieją dwa wskaźniki X i Y. Co więcej, Y zależy od X. Ponieważ OLS interesuje nas z punktu widzenia analizy regresji (w Excelu jego metody są realizowane przy użyciu wbudowanych funkcji), powinniśmy od razu przejść do rozważania konkretny problem.

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

Należy prognozować, jakie obroty (Y) będzie miał sklep, jeżeli będzie posiadał taką czy inną powierzchnię handlową. Oczywiście funkcja Y = f (X) jest rosnąca, ponieważ hipermarket sprzedaje więcej towarów niż stragan.

Kilka słów o poprawności danych wyjściowych wykorzystanych do predykcji

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

Według statystyki matematycznej wyniki będą mniej więcej poprawne, jeśli zbadane zostaną dane dotyczące co najmniej 5-6 obiektów. Ponadto nie można zastosować wyników „anomalnych”. W szczególności elitarny mały butik może osiągać obroty kilkakrotnie większe niż obroty dużych sklepów detalicznych klasy „masmarket”.

Istota metody

Dane tabeli można przedstawić na płaszczyźnie kartezjańskiej w postaci punktów M 1 (x 1, y 1), ... M n (x n, y n). Teraz rozwiązanie problemu sprowadzimy do wyboru funkcji aproksymującej y = f (x), która ma wykres przechodzący jak najbliżej punktów M 1, M 2, .. M n.

Oczywiście możesz użyć wielomianu wysokiego stopnia, 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 poszukiwanie prostej y = ax + b, która najlepiej przybliża dane eksperymentalne, a dokładniej współczynniki a i b.

Ocena dokładności

Przy każdym przybliżeniu szczególne znaczenie ma ocena jego dokładności. Oznaczmy przez e i różnicę (odchylenie) między wartościami funkcjonalnymi i eksperymentalnymi dla punktu x i, tj. e i = y i - f (x i).

Oczywiście do oceny dokładności aproksymacji można posłużyć się sumą odchyleń, czyli wybierając linię prostą do przybliżonego przedstawienia zależności X od Y, należy preferować tę, która ma najmniejszą wartość suma e i we wszystkich rozważanych punktach. Nie wszystko jest jednak takie proste, gdyż wraz z odchyleniami dodatnimi pojawią się również odchylenia ujemne.

Problem można rozwiązać za pomocą modułów odchyleń lub ich kwadratów. Ostatnia metoda jest najczęściej stosowana. Znajduje zastosowanie w wielu obszarach, m.in. w analizie regresji (realizowanej w Excelu za pomocą dwóch wbudowanych funkcji) i już dawno udowodniła swoją skuteczność.

Metoda najmniejszych kwadratów

Jak wiadomo, Excel ma wbudowaną funkcję AutoSum, która pozwala obliczyć wartości wszystkich wartości znajdujących się w wybranym zakresie. Zatem nic nie stoi na przeszkodzie, abyśmy obliczyli wartość wyrażenia (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

W zapisie matematycznym wygląda to następująco:

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

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

W tym celu należy przyrównać pochodne cząstkowe nowych zmiennych a i b do zera i rozwiązać układ pierwotny składający się z dwóch równań z 2 niewiadomymi postaci:

Po kilku prostych przekształceniach, obejmujących dzielenie przez 2 i manipulację sumami, otrzymujemy:

Rozwiązując to np. metodą Cramera otrzymujemy punkt stacjonarny o określonych współczynnikach a* i b*. Jest to minimum, czyli aby przewidzieć, jakie obroty będzie miał sklep na danym obszarze, odpowiednia jest linia prosta y = a * x + b *, która jest modelem regresji dla omawianego przykładu. Oczywiście nie pozwoli Ci to znaleźć dokładnego wyniku, ale pomoże Ci zorientować się, czy zakup konkretnego obszaru na kredyt sklepowy się opłaci.

Jak zaimplementować metodę najmniejszych kwadratów w programie Excel

Excel posiada funkcję obliczania wartości metodą najmniejszych kwadratów. 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 na obliczenie OLS w Excelu.

W tym celu należy wpisać znak „=” w komórkę, w której ma wyświetlić się wynik obliczeń metodą najmniejszych kwadratów w Excelu 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 handlowych);
• zakres x 1 , …x n , czyli wielkość powierzchni handlowej;
• zarówno znane, jak i nieznane wartości x, dla których musisz dowiedzieć się o wielkości obrotu (informacje o ich lokalizacji w arkuszu znajdziesz poniżej).

Dodatkowo formuła zawiera zmienną logiczną „Const”. Jeśli w odpowiednim polu wpiszesz 1, będzie to oznaczać, że powinieneś przeprowadzić obliczenia, zakładając, że b = 0.

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

Niektóre funkcje

Analiza regresji może być dostępna nawet dla manekinów. 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 jego działania. W szczególności:

• Jeśli zakres znanych wartości zmiennej y uporządkujesz w jednym wierszu lub kolumnie, to każdy wiersz (kolumna) ze znanymi wartościami x będzie postrzegany przez program jako osobna zmienna.
• Jeżeli w oknie TREND nie zostanie podany zakres o znanym x, to korzystając z funkcji w Excelu, program potraktuje go jako tablicę składającą się z liczb całkowitych, których liczba odpowiada zakresowi z podanymi wartościami zmienna y.
• Aby wyprowadzić tablicę „przewidywanych” wartości, wyrażenie służące do obliczenia trendu należy wprowadzić w postaci formuły tablicowej.
• Jeśli nie zostaną określone nowe wartości x, funkcja TREND uzna je za równe znanym. Jeżeli nie są one określone, wówczas jako argument przyjmowana jest tablica 1; 2; 3; 4;…, co jest proporcjonalne do zakresu o zadanych już parametrach y.
• Zakres zawierający nowe wartości x musi mieć tyle samo lub więcej wierszy lub kolumn co zakres zawierający podane wartości y. Innymi słowy, musi być proporcjonalna do zmiennych niezależnych.
• Tablica ze znanymi wartościami x może zawierać wiele zmiennych. Jeśli jednak mówimy tylko o jednym, to wymagane jest, aby zakresy z podanymi wartościami x i y były proporcjonalne. W przypadku kilku zmiennych konieczne jest, aby zakres z podanymi wartościami y zmieścił się w jednej kolumnie lub jednym wierszu.

funkcja PRZEWIDYWANIE

Realizowane przy użyciu kilku funkcji. Jedna z nich nazywa się „PREDYKCJA”. Działa podobnie jak „TREND”, czyli podaje wynik obliczeń metodą najmniejszych kwadratów. Jednak tylko dla jednego X, dla którego wartość Y nie jest znana.

Teraz znasz formuły w Excelu dla manekinów, które pozwalają przewidzieć przyszłą wartość konkretnego wskaźnika według trendu liniowego.

Aproksymacja danych eksperymentalnych to metoda polegająca na zastąpieniu danych uzyskanych eksperymentalnie funkcją analityczną, która najbardziej przechodzi lub pokrywa się w punktach węzłowych z wartościami pierwotnymi (dane uzyskane podczas eksperymentu lub eksperymentu). Obecnie istnieją dwa sposoby definiowania funkcji analitycznej:

Konstruując n-stopniowy wielomian interpolacyjny, który przechodzi bezpośrednio przez wszystkie punkty daną tablicę danych. W tym przypadku funkcję aproksymującą przedstawia się w postaci: wielomianu interpolacyjnego w postaci Lagrange'a lub wielomianu interpolacyjnego w postaci Newtona.

Konstruując n-stopniowy wielomian aproksymujący, który przechodzi w bezpośrednim sąsiedztwie punktów z danej tablicy danych. W ten sposób funkcja aproksymująca wygładza wszystkie losowe szumy (lub błędy), które mogą pojawić się podczas eksperymentu: zmierzone wartości podczas eksperymentu zależą od czynników losowych, które zmieniają się zgodnie z ich własnymi losowymi prawami (błędy pomiaru lub instrumentu, niedokładność lub eksperyment błędy). W tym przypadku funkcję aproksymującą wyznacza się metodą najmniejszych kwadratów.

Metoda najmniejszych kwadratów(w literaturze angielskiej Ordinary Least Squares, OLS) to metoda matematyczna polegająca na wyznaczeniu funkcji aproksymującej, która jest konstruowana w najbliższej odległości od punktów z danego układu danych eksperymentalnych. Zbliżenie funkcji pierwotnej i aproksymującej F(x) wyznacza się za pomocą miary numerycznej, a mianowicie: suma kwadratów odchyleń danych eksperymentalnych od krzywej aproksymującej F(x) powinna być najmniejsza.

Krzywa przybliżająca zbudowana metodą najmniejszych kwadratów

Stosuje się metodę najmniejszych kwadratów:

Rozwiązywanie nadokreślonych układów równań, gdy liczba równań przekracza liczbę niewiadomych;

Znalezienie rozwiązania w przypadku zwyczajnych (nie nadokreślonych) nieliniowych układów równań;

Aby przybliżyć wartości punktowe za pomocą pewnej funkcji aproksymującej.

Funkcję aproksymującą metodą najmniejszych kwadratów wyznacza się z warunku minimalnej sumy kwadratów odchyleń obliczonej funkcji aproksymującej z zadanego układu danych eksperymentalnych. To kryterium metody najmniejszych kwadratów zapisuje się jako następujące wyrażenie:

Wartości obliczonej funkcji aproksymującej w punktach węzłowych,

Dana tablica danych eksperymentalnych w punktach węzłowych.

Kryterium kwadratowe ma wiele „dobrych” właściwości, takich jak różniczkowalność, zapewniając unikalne rozwiązanie problemu aproksymacji za pomocą wielomianowych funkcji aproksymujących.

W zależności od warunków zadania funkcją aproksymującą jest wielomian stopnia m

Stopień funkcji aproksymującej nie zależy od liczby punktów węzłowych, jednak jej wymiar musi być zawsze mniejszy od wymiaru (liczby punktów) danego układu danych eksperymentalnych.

∙ Jeżeli stopień funkcji aproksymującej wynosi m=1, to funkcję tabelaryczną aproksymujemy linią prostą (regresja liniowa).

∙ Jeżeli stopień funkcji aproksymującej wynosi m=2, to funkcję tablicową aproksymujemy parabolą kwadratową (aproksymacja kwadratowa).

∙ Jeżeli stopień funkcji aproksymującej wynosi m=3, to funkcję tablicową aproksymujemy parabolą sześcienną (aproksymacja sześcienna).

W ogólnym przypadku, gdy konieczne jest skonstruowanie wielomianu aproksymującego stopnia m dla danych wartości z tabeli, warunek na minimum sumy kwadratów odchyleń po wszystkich punktach węzłowych przepisuje się w postaci:

- nieznane współczynniki wielomianu aproksymującego stopnia m;

Liczba określonych wartości tabeli.

Warunkiem koniecznym istnienia minimum funkcji jest równość jej pochodnych cząstkowych względem nieznanych zmiennych do zera . W efekcie otrzymujemy następujący układ równań:

Przekształćmy powstały liniowy układ równań: otwórz nawiasy i przesuń wolne wyrazy na prawą stronę wyrażenia. W rezultacie powstały układ liniowych wyrażeń algebraicznych zostanie zapisany w następującej postaci:

Ten system liniowych wyrażeń algebraicznych można zapisać w postaci macierzowej:

W rezultacie otrzymano układ równań liniowych o wymiarze m+1, który składa się z niewiadomych m+1. Układ ten można rozwiązać dowolną metodą rozwiązywania liniowych równań algebraicznych (na przykład metodą Gaussa). W wyniku rozwiązania zostaną znalezione nieznane parametry funkcji aproksymującej, które dają minimalną sumę kwadratów odchyleń funkcji aproksymującej od danych pierwotnych, tj. najlepsze możliwe przybliżenie kwadratowe. Należy pamiętać, że jeśli zmieni się chociaż jedna wartość danych źródłowych, wszystkie współczynniki zmienią swoje wartości, ponieważ są one całkowicie zdeterminowane przez dane źródłowe.

Aproksymacja danych źródłowych metodą zależności liniowej

(regresja liniowa)

Jako przykład rozważmy technikę wyznaczania funkcji aproksymującej, która jest określona w postaci zależności liniowej. Zgodnie z metodą najmniejszych kwadratów warunek na minimum sumy kwadratów odchyleń zapisuje się w postaci:

Współrzędne węzłów tabeli;

Nieznane współczynniki funkcji aproksymującej, która jest określona jako zależność liniowa.

Warunkiem koniecznym istnienia minimum funkcji jest równość jej pochodnych cząstkowych względem nieznanych zmiennych do zera. W efekcie otrzymujemy następujący układ równań:

Przekształćmy powstały liniowy układ równań.

Rozwiązujemy powstały układ równań liniowych. Współczynniki funkcji aproksymującej w postaci analitycznej wyznacza się następująco (metoda Cramera):

Współczynniki te zapewniają konstrukcję liniowej funkcji aproksymującej zgodnie z kryterium minimalizacji sumy kwadratów funkcji aproksymującej z zadanych wartości tabelarycznych (dane eksperymentalne).

Algorytm implementacji metody najmniejszych kwadratów

1. Dane wyjściowe:

Określono tablicę danych eksperymentalnych z liczbą pomiarów N

Określany jest stopień wielomianu aproksymującego (m).

2. Algorytm obliczeniowy:

2.1. Współczynniki wyznaczane są do budowy układu równań z wymiarami

Współczynniki układu równań (lewa strona równania)

- indeks numeru kolumny macierzy kwadratowej układu równań

Terminy swobodne układu równań liniowych (prawa strona równania)

- indeks numeru wiersza macierzy kwadratowej układu równań

2.2. Tworzenie układu równań liniowych o wymiarze.

2.3. Rozwiązywanie układu równań liniowych w celu wyznaczenia nieznanych współczynników wielomianu aproksymującego stopnia m.

2.4 Wyznaczanie sumy kwadratów odchyleń aproksymującego wielomianu od wartości pierwotnych we wszystkich punktach węzłowych

Znaleziona wartość sumy kwadratów odchyleń jest minimalną możliwą wartością.

Aproksymacja z wykorzystaniem innych funkcji

Należy zaznaczyć, że przy aproksymacji danych pierwotnych metodą najmniejszych kwadratów jako funkcję aproksymującą czasami wykorzystuje się funkcję logarytmiczną, funkcję wykładniczą i funkcję potęgową.

Przybliżenie logarytmiczne

Rozważmy przypadek, gdy funkcję aproksymującą podaje funkcja logarytmiczna o postaci: