Metoda wyznaczania współczynników metodą najmniejszych kwadratów. Aproksymacja danych eksperymentalnych

Stół 4

x n

y n

Stół 5

ja ja

0,705

0,495

0,426

0,357

0,368

0,406

0,549

0,768

Tabela 6

№0

x

0,1

0,2

0,3

0,4

0,5

0,6

0,7

0,8

tak

3,030

3,142

3,358

3,463

3,772

3,251

3,170

3,665

№ 1

3,314

3,278

3,262

3,292

3,332

3,397

3,487

3,563

№ 2

1,045

1,162

1,264

1,172

1,070

0,898

0,656

0,344

№ 3

6,715

6,735

6,750

6,741

6,645

6,639

6,647

6,612

№ 4

2,325

2,515

2,638

2,700

2,696

2,626

2,491

2,291

№ 5

1.752

1,762

1,777

1,797

1,821

1,850

1,884

1,944

№ 6

1,924

1,710

1,525

1,370

1,264

1,190

1,148

1,127

№ 7

1,025

1,144

1,336

1,419

1,479

1,530

1,568

1,248

№ 8

5,785

5,685

5,605

5,545

5,505

5,480

5,495

5,510

№ 9

4,052

4,092

4,152

4,234

4,338

4,468

4,599

Przykład.

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

W wyniku ich wyrównania funkcja

Za pomocą metoda najmniejszych kwadratów, przybliż te dane liniową zależnością y=ax+b(znajdź parametry a oraz b). Dowiedz się, która z dwóch linii jest lepsza (w sensie metody najmniejszych kwadratów) dopasowuje dane eksperymentalne. Narysuj coś.

Istota metody najmniejszych kwadratów (LSM).

Problem polega na znalezieniu współczynników zależności liniowej, dla których funkcja dwóch zmiennych a oraz b przyjmuje najmniejszą wartość. To znaczy, biorąc pod uwagę dane a oraz b suma kwadratów odchyleń danych eksperymentalnych od znalezionej linii prostej będzie najmniejsza. To jest cały punkt metody najmniejszych kwadratów.

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

Wyprowadzanie wzorów do znajdowania współczynników.

Zostaje opracowany i rozwiązany układ dwóch równań z dwiema niewiadomymi. Znajdowanie pochodnych cząstkowych funkcji według zmiennych a oraz b, przyrównujemy te pochodne do zera.

Powstały układ równań rozwiązujemy dowolną metodą (na przykład metoda substytucji lub Metoda Cramera) i uzyskaj wzory na obliczanie współczynników metodą najmniejszych kwadratów (LSM).

Z danymi a oraz b funkcjonować przyjmuje najmniejszą wartość. Dowód na ten fakt jest podany pod tekstem na końcu strony.

To cała metoda najmniejszych kwadratów. Wzór na znalezienie parametru a zawiera sumy ,,, oraz parametr n- ilość danych eksperymentalnych. Zaleca się, aby wartości tych sum były obliczane osobno. 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 obliczania kwot zawartych we wzorach wymaganych współczynników.

Wartości w czwartym rzędzie tabeli uzyskuje się mnożąc wartości drugiego rzędu przez wartości trzeciego rzędu dla każdej liczby i.

Wartości w piątym rzędzie tabeli uzyskuje się podnosząc do kwadratu wartości drugiego rzędu dla każdej liczby i.

Wartości ostatniej kolumny tabeli to sumy wartości w wierszach.

Aby obliczyć współczynniki, korzystamy ze wzorów metody najmniejszych kwadratów a oraz b. Zastępujemy w nich odpowiednie wartości z ostatniej kolumny tabeli:

W konsekwencji, y=0,165x+2,184 jest pożądaną przybliżoną linią prostą.

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

Estymacja błędu metody najmniejszych kwadratów.

Aby to zrobić, musisz obliczyć sumy kwadratów odchyleń oryginalnych danych z tych linii oraz , mniejsza wartość odpowiada linii, która lepiej przybliża oryginalne dane za pomocą metody najmniejszych kwadratów.

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

Graficzna ilustracja metody najmniejszych kwadratów (LSM).

Wszystko świetnie prezentuje się na listach przebojów. Czerwona linia to znaleziona linia y=0,165x+2,184, niebieska linia to , różowe kropki to oryginalne dane.

W praktyce przy modelowaniu różnych procesów - w szczególności ekonomicznych, fizycznych, technicznych, społecznych - szeroko stosowane są te lub inne metody obliczania przybliżonych wartości funkcji z ich znanych wartości w pewnych stałych punktach.

Często pojawiają się problemy aproksymacji tego rodzaju funkcji:

przy konstruowaniu przybliżonych wzorów do obliczania wartości charakterystycznych wielkości badanego procesu zgodnie z danymi tabelarycznymi uzyskanymi w wyniku eksperymentu;

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

jeśli konieczne jest obliczenie wartości funkcji w punktach pośrednich rozważanego przedziału;

przy określaniu wartości charakterystycznych wielkości procesu poza rozważanym przedziałem, w szczególności przy prognozowaniu.

Jeżeli w celu zamodelowania pewnego procesu określonego tabelą skonstruuje się funkcję, która w przybliżeniu opisuje ten proces w oparciu o metodę najmniejszych kwadratów, będzie ona nazywana funkcją aproksymującą (regresją), a samo zadanie konstrukcji funkcji aproksymującej będzie być problemem przybliżenia.

W artykule omówiono możliwości pakietu MS Excel w rozwiązywaniu takich problemów, dodatkowo podano metody i techniki konstruowania (tworzenia) regresji dla funkcji danych tabelarycznie (co jest podstawą analizy regresji).

Istnieją dwie opcje tworzenia regresji w programie Excel.

Dodanie wybranych regresji (linii trendów) do wykresu zbudowanego na podstawie tabeli danych dla badanej charakterystyki procesu (dostępne tylko w przypadku budowy wykresu);

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

Dodawanie linii trendu do wykresu

W przypadku tabeli danych opisujących pewien proces i reprezentowanych przez diagram, Excel ma skuteczne narzędzie do analizy regresji, które pozwala:

zbudować w oparciu o metodę najmniejszych kwadratów i dodać do wykresu pięć typów regresji, które modelują badany proces z różnym stopniem dokładności;

dodać do diagramu równanie skonstruowanej regresji;

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

Na podstawie danych wykresu Excel pozwala uzyskać liniowe, wielomianowe, logarytmiczne, wykładnicze, wykładnicze typy regresji, które są podane 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 odliczanie czasu badanego procesu (charakterystyka) .

1 . Regresja liniowa jest dobra w modelowaniu cech, które rosną lub maleją w stałym tempie. To najprostszy model badanego procesu. Jest zbudowany zgodnie z równaniem:

y=mx+b

gdzie m jest tangensem nachylenia regresji liniowej do osi x; b - współrzędna punktu przecięcia regresji liniowej z osią y.

2 . Wielomianowa linia trendu jest przydatna do opisywania cech, które mają kilka wyraźnych ekstremów (wysoki i minima). O wyborze stopnia wielomianu decyduje liczba ekstremów badanej cechy. Tak więc 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 budowana jest 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 są określane podczas budowy.

3 . Logarytmiczna linia trendu jest z powodzeniem wykorzystywana w modelowaniu charakterystyk, których wartości początkowo szybko się zmieniają, a następnie stopniowo stabilizują.

y = c ln(x) + b

4 . Linia trendu mocy daje dobre wyniki, jeśli wartości badanej zależności charakteryzują się stałą zmianą tempa wzrostu. Przykładem takiej zależności może być wykres jednostajnie przyspieszonego ruchu samochodu. Jeśli w danych występują wartości zero lub ujemne, nie można użyć linii trendu mocy.

Jest zbudowany zgodnie z równaniem:

y = cxb

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

5 . Wykładniczą linię trendu należy stosować, jeśli tempo zmian danych stale rośnie. W przypadku danych zawierających wartości zerowe lub ujemne tego rodzaju przybliżenie również nie ma zastosowania.

Jest zbudowany zgodnie z równaniem:

y=cebx

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

Przy wyborze linii trendu Excel automatycznie oblicza wartość R2, która charakteryzuje dokładność aproksymacji: im bliższa jest wartość R2, tym bardziej wiarygodnie linia trendu przybliża badany proces. W razie potrzeby wartość R2 można zawsze wyświetlić na wykresie.

Określone wzorem:

Aby dodać linię trendu do serii danych:

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

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

Te same działania można łatwo wdrożyć, jeśli najedziesz kursorem na wykres odpowiadający jednej z serii danych i klikniesz prawym przyciskiem myszy; z wyświetlonego menu kontekstowego wybierz polecenie Dodaj linię trendu. Na ekranie pojawi się okno dialogowe Trendline z otwartą zakładką Type (rys. 1).

Następnie potrzebujesz:

Na karcie Typ wybierz żądany typ linii trendu (domyślnie jest to opcja Liniowa). Dla typu Wielomian w polu Stopień określ stopień wybranego wielomianu.

1 . W polu Zbudowany na serii wyświetlane są wszystkie serie danych na danym wykresie. Aby dodać linię trendu do określonej serii danych, wybierz jej nazwę w polu Zbudowany na serii.

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

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

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

wyświetlić równanie linii trendu w obszarze wykresu, dla którego należy zaznaczyć checkbox pokaż równanie na wykresie;

wyświetl wartość wiarygodności aproksymacji R2 w obszarze wykresu, dla którego należy odznaczyć checkbox 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ęcie krzywej z osią Y w punkcie;

kliknij przycisk OK, aby zamknąć okno dialogowe.

Istnieją trzy sposoby rozpoczęcia edycji już zbudowanej linii trendu:

użyć polecenia Wybrana linia trendu z menu Format, po wybraniu linii trendu;

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

klikając dwukrotnie linię trendu.

Na ekranie pojawi się okno dialogowe Formatuj linię trendu (rys. 3), zawierające trzy zakładki: Widok, Typ, Parametry, a 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ąć już skonstruowaną linię trendu, wybierz linię trendu do usunięcia i naciśnij klawisz Delete.

Zalety rozważanego narzędzia do analizy regresji to:

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

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

możliwość przewidywania zachowania się badanego procesu dla dowolnej (w granicach zdrowego rozsądku) liczby kroków do przodu, jak i do tyłu;

możliwość uzyskania równania linii trendu w postaci analitycznej;

możliwość, jeśli to konieczne, uzyskania oceny wiarygodności aproksymacji.

Wady obejmują następujące punkty:

budowa linii trendu jest przeprowadzana tylko wtedy, gdy istnieje wykres 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 obrębie obszaru wykresu , natomiast serie danych utworzone na podstawie trendu starego równania linii pozostają niezmienione;

W raportach w formie wykresu przestawnego po zmianie widoku wykresu lub skojarzonego raportu w formie tabeli przestawnej istniejące linie trendu nie są zachowywane, dlatego przed narysowaniem linii trendu lub sformatowaniem raportu w formie wykresu przestawnego należy upewnić się, że układ raportu spełnia wymagania.

Linie trendu można dodawać do serii danych prezentowanych na wykresach, takich jak wykres, histogram, płaskie nieznormalizowane wykresy warstwowe, wykresy słupkowe, punktowe, bąbelkowe i giełdowe.

Nie można dodawać linii trendu do serii danych na wykresach 3-W, standardowym, radarowym, kołowym i pierścieniowym.

Korzystanie z wbudowanych funkcji programu Excel

Excel zapewnia również narzędzie do analizy regresji do wykreślania linii trendu poza obszarem wykresu. W tym celu można użyć wielu funkcji arkusza statystycznego, ale wszystkie z nich umożliwiają budowanie tylko regresji liniowej lub wykładniczej.

Excel ma kilka funkcji do budowania 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:

LGRFPok.

Należy zauważyć, że techniki konstruowania regresji za pomocą funkcji TREND i WZROST są praktycznie takie same. To samo można powiedzieć o parze funkcji LINEST i LGRFPRIBL. W przypadku tych czterech funkcji podczas tworzenia tabeli wartości używane są funkcje programu Excel, takie jak formuły tablicowe, co nieco zaśmieca proces budowania regresji. Zwracamy również uwagę, że konstrukcję regresji liniowej, naszym zdaniem, najłatwiej zaimplementować za pomocą funkcji SLOPE i INTERCEPT, gdzie pierwsza z nich określa nachylenie regresji liniowej, a druga wyznacza odcinek odcięty przez regresję na osi Y.

Zaletami wbudowanego narzędzia funkcji do analizy regresji są:

dość prosty proces tego samego typu tworzenia serii danych badanej cechy dla wszystkich wbudowanych funkcji statystycznych, które wyznaczają linie trendu;

standardowa technika konstruowania linii trendu na podstawie wygenerowanych serii danych;

umiejętność przewidywania zachowania badanego procesu dla wymaganej liczby kroków do przodu lub do tyłu.

Wadą jest fakt, że Excel nie ma wbudowanych funkcji do tworzenia innych (poza liniowymi i wykładniczymi) typami linii trendu. Okoliczność ta często nie pozwala na wybór wystarczająco dokładnego modelu badanego procesu, a także uzyskanie prognoz zbliżonych do rzeczywistości. Ponadto przy korzystaniu z funkcji TREND i GROW nie są znane równania linii trendu.

Należy zauważyć, że autorzy nie postawili sobie za cel artykułu prezentowania przebiegu analizy regresji z różnym stopniem kompletności. Jego głównym zadaniem jest pokazanie możliwości pakietu Excel w rozwiązywaniu problemów aproksymacyjnych na konkretnych przykładach; zademonstrować, jakie skuteczne narzędzia ma Excel do budowania regresji i prognozowania; pokazują, jak stosunkowo łatwo takie problemy może rozwiązać nawet użytkownik, który nie ma głębokiej wiedzy z zakresu analizy regresji.

Przykłady rozwiązywania konkretnych problemów

Rozważ rozwiązanie konkretnych problemów za pomocą wymienionych narzędzi pakietu Excel.

Zadanie 1

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

Zbuduj wykres.

Dodaj do wykresu linie trendu liniowe i wielomianowe (kwadratowe i sześcienne).

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

Przygotuj prognozę zysków dla przedsiębiorstwa na lata 2003 i 2004.

Rozwiązanie problemu

W zakresie komórek A4:C11 arkusza kalkulacyjnego Excel wchodzimy do arkusza roboczego pokazanego na ryc. cztery.

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

Aktywujemy skonstruowany wykres i zgodnie z opisaną powyżej metodą po wybraniu typu linii trendu w oknie dialogowym Linia trendu (patrz rys. 1) naprzemiennie dodajemy do wykresu linie trendu liniowe, kwadratowe i sześcienne. W tym samym oknie dialogowym otwórz zakładkę Parametry (patrz rys. 2), w polu Nazwa krzywej aproksymującej (wygładzonej) wpisz nazwę dodawanego trendu, a w polu Prognoza naprzód na: okresy ustaw wartość 2, ponieważ planowane jest sporządzenie prognozy zysków na dwa lata do przodu. Aby wyświetlić równanie regresji i wartość wiarygodności aproksymacji R2 w obszarze wykresu, zaznacz pola wyboru Pokaż równanie na ekranie i umieść na wykresie wartość wiarygodności aproksymacji (R^2). Dla lepszej percepcji wzrokowej zmieniamy rodzaj, kolor i grubość konstruowanych linii trendu, dla czego korzystamy z zakładki Widok okna dialogowego Format linii trendu (patrz Rys. 3). Wynikowy wykres z dodanymi liniami trendu pokazano na ryc. 5.

Uzyskanie danych tabelarycznych o zysku przedsiębiorstwa dla każdej linii trendu w latach 1995-2004. Wykorzystajmy równania linii trendu przedstawione na ryc. 5. W tym celu w komórkach zakresu D3:F3 wprowadź informacje tekstowe o rodzaju wybranej linii trendu: Trend liniowy, Trend kwadratowy, Trend sześcienny. Następnie wprowadź formułę regresji liniowej w komórce D4 i za pomocą znacznika wypełnienia skopiuj tę formułę z odniesieniami względnymi do zakresu komórek D5:D13. Należy zauważyć, że każda komórka z 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łniany jest zakres komórek E4:E13, a w przypadku regresji sześciennej wypełniany jest zakres komórek F4:F13. W ten sposób sporządzono prognozę zysku przedsiębiorstwa na lata 2003 i 2004. z trzema trendami. Wynikową tabelę wartości pokazano na ryc. 6.

Zadanie 2

Zbuduj wykres.

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

Wyprowadź równania otrzymanych linii trendu, a także wartości wiarygodności aproksymacji 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.

Opracuj prognozę zysków dla firmy na lata 2003 i 2004, korzystając z tych linii trendu.

Rozwiązanie problemu

Postępując zgodnie z metodologią podaną w rozwiązaniu problemu 1, otrzymujemy wykres z dodanymi liniami trendu logarytmicznego, wykładniczego i wykładniczego (rys. 7). Ponadto, korzystając z uzyskanych równań linii trendu, wypełniamy tabelę wartości dla zysku przedsiębiorstwa, w tym przewidywane wartości na lata 2003 i 2004. (rys. 8).

Na ryc. 5 i ryc. widać, że model o trendzie logarytmicznym odpowiada najniższej wartości wiarygodności aproksymacji

R2 = 0,8659

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

Zadanie 3

Mając 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 liniowych i wykładniczych linii trendu za pomocą funkcji TREND i WZROST.

Korzystając z funkcji TREND i WZROST sporządź prognozę zysku dla przedsiębiorstwa na lata 2003 i 2004.

Dla danych początkowych i otrzymanych serii danych skonstruuj diagram.

Rozwiązanie problemu

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

wybrać zakres komórek D4:D11, który należy wypełnić wartościami funkcji TREND odpowiadające 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ć naciskając przycisk (funkcja Wstaw) standardowego paska 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 wpisana formuła stała się formułą tablicową, użyj kombinacji klawiszy + + .

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

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

Sporządzenie prognozy zysku firmy na lata 2003 i 2004. niezbędny:

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

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

zamień tę formułę w formułę tablicową za pomocą skrótu klawiaturowego 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).

Podobnie seria danych jest wypełniana za pomocą funkcji WZROST, która jest wykorzystywana w analizie 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 uzyskanych serii danych wykres przedstawiony na ryc. jedenaście.

Zadanie 4

Z tabelą danych dotyczących przyjmowania wniosków o wykonanie usług przez służbę dyspozytorską 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.

Pobierz serię danych dla regresji wykładniczej za pomocą funkcji LYFFPRIB.

Korzystając z powyższych funkcji dokonaj prognozy wpływu zgłoszeń do służby dyspozytorskiej na okres od 12 do 14 dnia bieżącego miesiąca.

Dla oryginalnej i otrzymanej serii danych skonstruuj diagram.

Rozwiązanie problemu

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

W przypadku regresji liniowych i wykładniczych zbudowanych przy użyciu funkcji NACHYLENIE, PRZECIĘCIE, WYKRES, LGRFINB wygląd ich równań jest zawsze znany, w przeciwieństwie do regresji liniowej i wykładniczej odpowiadającej funkcjom TREND i WZROST.

1 . Zbudujmy regresję liniową, która ma równanie:

y=mx+b

za pomocą funkcji SLOPE i INTERCEPT, przy czym nachylenie regresji m jest określane przez funkcję SLOPE, a stały wyraz b - przez funkcję INTERCEPT.

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

wprowadź tabelę źródłową w zakresie komórek A4:B14;

wartość parametru m zostanie określona w komórce C19. Wybierz z kategorii Statystyka funkcję Slope; wprowadź 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 wprowadzona do komórki C19: =SLOPE(B4:B14;A4:A14);

przy użyciu podobnej metody określa się wartość parametru bw komórce D19. A jego zawartość będzie wyglądać tak: = PRZECIĘCIE(B4:B14;A4:A14). W ten sposób wartości parametrów mib, niezbędne do skonstruowania regresji liniowej, będą przechowywane odpowiednio w komórkach C19, D19;

następnie wpisujemy formułę regresji liniowej w komórce C4 w postaci: = $ C * A4 + $ D. W tej formule komórki C19 i D19 są zapisywane z odwołaniami bezwzględnymi (adres komórki nie powinien się zmieniać przy ewentualnym kopiowaniu). Znak odniesienia bezwzględnego $ można wpisać albo z klawiatury, albo za pomocą klawisza F4, po umieszczeniu kursora na adresie komórki. Za pomocą uchwytu wypełniania skopiuj tę formułę do zakresu komórek C4:C17. Otrzymujemy pożądaną serię danych (ryc. 12). Z uwagi na to, że liczba żądań jest liczbą całkowitą, należy ustawić format liczb w zakładce Number okna Cell Format z liczbą miejsc dziesiętnych na 0.

2 . Teraz zbudujmy regresję liniową podaną równaniem:

y=mx+b

za pomocą funkcji REGLINP.

Dla tego:

wprowadź funkcję REGLINP jako formułę tablicową w zakresie komórek C20:D20: =(REGLINP(B4:B14;A4:A14)). W rezultacie otrzymujemy wartość parametru mw komórce C20 oraz wartość parametru bw komórce D20;

wprowadź 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 . Budujemy regresję wykładniczą, która ma równanie:

za pomocą funkcji LGRFPRIBL wykonuje się to podobnie:

w zakresie komórek C21:D21 wprowadź funkcję LGRFPRIBL jako formułę tablicową: =( 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ła jest wprowadzana do komórki E4: =$D*$C^A4;

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

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

Wartość R 2 nazywa współczynnik determinacji.

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

Do oceny istotności R stosuje się test F Fishera, obliczony ze wzoru

gdzie n- wielkość próby (liczba eksperymentów);

k to liczba współczynników modelu.

Jeśli F przekracza pewną wartość krytyczną dla danych n oraz k i przyjętym poziomie ufności, wtedy wartość R uważa się za znaczącą. Tabele wartości krytycznych F podano w podręcznikach dotyczących statystyki matematycznej.

O istotności R decyduje zatem 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 (przez 2 punkty na płaszczyźnie zawsze można narysować pojedynczą linię prostą). Jeśli jednak dane eksperymentalne są zmiennymi losowymi, takiej wartości R należy ufać z dużą ostrożnością. Zwykle w celu uzyskania istotnego R i wiarygodnej regresji dąży się do tego, aby liczba eksperymentów znacznie przekraczała liczbę współczynników modelu (n>k).

Aby zbudować model regresji liniowej, musisz:

1) przygotuj listę n wierszy i m kolumn zawierających dane eksperymentalne (kolumna zawierająca wartość wyjściową) Tak musi znajdować się na pierwszym lub ostatnim miejscu na liście); na przykład weźmy dane z poprzedniego zadania, dodając kolumnę o nazwie „numer okresu”, numerująca numery okresów od 1 do 12. (będą to wartości X)

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

Jeśli brakuje pozycji „Analiza danych” w menu „Narzędzia”, należy przejść do pozycji „Dodatki” tego samego menu i zaznaczyć pole „Pakiet analizy”.

3) w oknie dialogowym „Regresja” ustaw:

interwał wejściowy Y;

interwał wejściowy X;

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

4) kliknij „OK” i przeanalizuj wyniki.

Istota metody polega na tym, że kryterium jakości rozważanego rozwiązania jest suma kwadratów błędów, którą dąży się do minimalizacji. Aby to zastosować, wymagane jest wykonanie jak największej liczby pomiarów nieznanej zmiennej losowej (im więcej - tym wyższa dokładność rozwiązania) oraz pewnego zestawu oczekiwanych rozwiązań, z których należy wybrać najlepsze . Jeżeli zestaw rozwiązań jest sparametryzowany, to należy znaleźć optymalną wartość parametrów.

Dlaczego minimalizowane są kwadraty błędów, a nie same błędy? Faktem jest, że w większości przypadków błędy występują w obu kierunkach: oszacowanie może być większe niż pomiar lub mniejsze od niego. Jeśli dodamy błędy o różnych znakach, zniosą się one nawzajem, w wyniku czego suma da nam błędne wyobrażenie o jakości oszacowania. Często, aby ostateczne oszacowanie miało ten sam wymiar co zmierzone wartości, pierwiastek kwadratowy jest pobierany z sumy kwadratów błędów.

LSM znajduje zastosowanie w matematyce, w szczególności w teorii prawdopodobieństwa i statystyce matematycznej. Ta metoda ma największe zastosowanie w problemach filtrowania, gdy konieczne jest oddzielenie użytecznego sygnału od nałożonego na niego szumu.

Jest również używany w analizie matematycznej do przybliżonej reprezentacji danej funkcji za pomocą prostszych funkcji. Kolejnym obszarem zastosowania LSM jest rozwiązywanie układów równań o mniejszej liczbie niewiadomych niż liczba równań.

Wymyśliłem jeszcze kilka bardzo nieoczekiwanych zastosowań LSM, o których chciałbym opowiedzieć w tym artykule.

MNC i literówki

Literówki i błędy ortograficzne to plaga automatycznych tłumaczy i wyszukiwarek. Rzeczywiście, jeśli słowo różni się tylko o 1 literę, program traktuje je jako inne słowo i tłumaczy/szuka go niepoprawnie lub nie tłumaczy/w ogóle go nie znajduje.

Miałem podobny problem: były dwie bazy danych z adresami domów moskiewskich i trzeba je było połączyć w jedną. Ale adresy były napisane w innym stylu. W jednej bazie danych znajdował się standard KLADR (ogólnorosyjski klasyfikator adresów), na przykład: „BABUSHKINA PILOT UL., D10K3”. A w innej bazie był styl pocztowy, na przykład: „Św. Pilot Babuszkin, dom 10 budynek 3. Wydaje się, że w obu przypadkach nie ma błędów, a automatyzacja procesu jest niesamowicie trudna (każda baza danych ma 40 000 rekordów!). Chociaż literówek też było dość... Jak sprawić, by komputer zrozumiał, że 2 adresy powyżej należą do tego samego domu? Tutaj przydał mi się MNC.

Co ja zrobiłem? Znalazłszy kolejny list pod pierwszym adresem, szukałem tego samego listu pod drugim adresem. Jeśli oba były w tym samym miejscu, to przyjąłem, że błąd dla tej litery wynosi 0. Jeśli znajdowały się w sąsiednich pozycjach, błąd wynosił 1. Jeśli było przesunięcie o 2 pozycje, błąd wynosił 2, a itd. Jeśli w innym adresie w ogóle nie było takiej litery, to przyjęto, że błąd wynosi n+1, gdzie n jest liczbą liter w pierwszym adresie. W ten sposób obliczyłem sumę kwadratów błędów i połączyłem te rekordy, w których ta suma była minimalna.

Oczywiście numery domów i budynków były przetwarzane oddzielnie. Nie wiem, czy wymyśliłem kolejny „rower”, czy rzeczywiście tak było, ale problem został szybko i sprawnie rozwiązany. Zastanawiam się, czy ta metoda jest używana w wyszukiwarkach? Być może jest używany, ponieważ każda szanująca się wyszukiwarka, spotykając nieznane słowo, oferuje zamiennik znanych słów („być może miałeś na myśli ...”). Mogą jednak przeprowadzić tę analizę jakoś inaczej.

OLS i wyszukiwanie po zdjęciach, twarzach i mapach

Tę metodę można również zastosować do wyszukiwania według zdjęć, rysunków, map, a nawet twarzy osób.

Zdjęcie:

Teraz wszystkie wyszukiwarki, zamiast wyszukiwania według obrazów, w rzeczywistości używają wyszukiwania według podpisów obrazów. Jest to niewątpliwie przydatna i wygodna usługa, ale proponuję uzupełnić ją o prawdziwe wyszukiwanie obrazów.

Wprowadza się przykładowy obraz i ocenia wszystkie obrazy przez sumę kwadratów odchyleń punktów charakterystycznych. Określenie tych bardzo charakterystycznych punktów samo w sobie jest zadaniem nietrywialnym. Jest to jednak całkiem możliwe do rozwiązania: na przykład w przypadku twarzy są to kąciki oczu, usta, czubek nosa, nozdrza, krawędzie i środki brwi, źrenice itp.

Porównując te parametry, można znaleźć twarz najbardziej zbliżoną do próbki. Widziałem już strony, na których działa taka usługa, i można znaleźć celebrytę, który jest najbardziej podobny do sugerowanego przez ciebie zdjęcia, a nawet skomponować animację, która zamieni cię w celebrytę iz powrotem. Z pewnością ta sama metoda działa w bazach Ministerstwa Spraw Wewnętrznych, zawierających tożsamościowe wizerunki przestępców.

Zdjęcie: pixabay.com

Tak, a odciski palców można przeszukiwać w ten sam sposób. Wyszukiwanie na mapie koncentruje się na naturalnych nierównościach obiektów geograficznych - zakolach rzek, pasmach górskich, zarysach wybrzeży, lasach i polach.

Oto taka wspaniała i wszechstronna metoda OLS. Jestem pewien, że Wy, drodzy czytelnicy, znajdziecie dla siebie wiele nietypowych i nieoczekiwanych zastosowań tej metody.

Ma wiele zastosowań, ponieważ umożliwia przybliżoną reprezentację danej funkcji przez inne prostsze. 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 zaimplementować obliczenia metodą najmniejszych kwadratów w programie Excel.

Stwierdzenie problemu na konkretnym przykładzie

Załóżmy, że istnieją dwa wskaźniki X i Y. Ponadto Y zależy od X. Ponieważ OLS jest dla nas interesujący z punktu widzenia analizy regresji (w Excelu jej metody są implementowane za pomocą wbudowanych funkcji), powinniśmy od razu przejść rozważyć konkretny problem.

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

Wymagane jest sporządzenie prognozy, jaki obrót (Y) będzie miał sklep, 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 danymi dla n sklepów.

Według statystyk matematycznych wyniki będą mniej więcej poprawne, jeśli zbadane zostaną dane dotyczące co najmniej 5-6 obiektów. Nie można również użyć wyników „anomalnych”. W szczególności elitarny mały butik może mieć obroty wielokrotnie większe niż obroty dużych placówek klasy „masmarket”.

Istota metody

Dane tabeli mogą być wyświetlane na płaszczyźnie kartezjańskiej jako punkty M 1 (x 1, y 1), ... M n (x n, y n). Teraz rozwiązanie problemu sprowadzi się do wyboru funkcji aproksymującej y = f (x), której wykres przebiega możliwie najbliżej punktów M 1, M 2, .. M n .

Oczywiście można użyć wielomianu wysokiego stopnia, ale ta opcja jest nie tylko trudna do zaimplementowania, ale 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.

Wynik dokładności

Dla każdego przybliżenia szczególnie ważna jest ocena jego dokładności. Oznacz 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ń, tzn. 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. Jednak nie wszystko jest takie proste, ponieważ wraz z odchyleniami dodatnimi praktycznie będą ujemne.

Możesz rozwiązać problem za pomocą modułów odchyleń lub ich kwadratów. Ta ostatnia metoda jest najczęściej stosowana. Wykorzystywany jest w wielu obszarach, w tym w analizie regresji (w Excelu jej implementacja odbywa się za pomocą dwóch wbudowanych funkcji) i od dawna sprawdza się jako skuteczna.

Metoda najmniejszych kwadratów

W Excelu, jak wiadomo, jest wbudowana funkcja autosumowania, która pozwala obliczyć wartości wszystkich wartości znajdujących się w wybranym zakresie. Zatem nic nie przeszkodzi nam w obliczeniu wartości wyrażenia (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

W notacji matematycznej wygląda to tak:

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

Zatem zadanie znalezienia prostej, która najlepiej opisuje określoną zależność między X i Y, sprowadza się do obliczenia minimum funkcji dwóch zmiennych:

Wymaga to zrównania do zera pochodnych cząstkowych względem 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 prostych przekształceniach, w tym dzieleniu przez 2 i manipulowaniu sumami, otrzymujemy:

Rozwiązując go np. metodą Cramera otrzymujemy punkt stacjonarny o określonych współczynnikach a * i b * . Jest to minimum, czyli do przewidzenia jaki obrót będzie miał sklep dla określonej powierzchni, 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 sklepu na kredyt dla konkretnego obszaru się opłaci.

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

Excel posiada funkcję obliczania wartości najmniejszych kwadratów. Ma 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.

W tym celu w komórce, w której ma być wyświetlany wynik obliczenia 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);
• zasięg x 1 , …x n , czyli wielkość powierzchni handlowej;
• oraz znane i nieznane wartości x, dla których należy określić wielkość obrotu (informacje o ich lokalizacji na arkuszu znajdują się poniżej).

Dodatkowo w formule znajduje się zmienna logiczna „Const”. Jeśli wpiszesz 1 w odpowiadającym mu polu, będzie to oznaczać, że należy przeprowadzić obliczenia, zakładając, że b \u003d 0.

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

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 pracy. W szczególności:

• Jeśli ułożysz zakres znanych wartości zmiennej 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żeli zakres ze znanym x nie jest podany w oknie TREND, 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 wyprowadzić tablicę „przewidywanych” wartości, wyrażenie trendu musi zostać wprowadzone jako formuła tablicowa.
• Jeśli nie określono nowych wartości x, funkcja TREND uważa je za równe znanym. Jeśli nie są podane, jako argument przyjmuje się tablicę 1; 2; 3; 4;…, co jest współmierne do zakresu przy podanych już parametrach y.
• Zakres zawierający nowe wartości x musi mieć tyle samo lub więcej wierszy lub kolumn co zakres z podanymi wartościami 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 przedziały z podanymi wartościami x i y były współmierne. W przypadku kilku zmiennych konieczne jest, aby zakres z podanymi wartościami y mieścił się w jednej kolumnie lub jednym wierszu.

funkcja PROGNOZA

Jest realizowany za pomocą kilku funkcji. Jeden z nich nazywa się „PRZEWIDYWANIE”. 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 Excela dla manekinów, które pozwalają przewidzieć wartość przyszłej wartości wskaźnika zgodnie z trendem liniowym.

Najmniejsze kwadraty to matematyczna procedura konstruowania równania liniowego, która najlepiej pasuje do zestawu uporządkowanych par, znajdując wartości dla a i b, współczynników w równaniu linii prostej. Celem metody najmniejszych kwadratów jest zminimalizowanie całkowitego błędu kwadratowego między wartościami y i ŷ. Jeżeli dla każdego punktu wyznaczymy błąd ŷ, metoda najmniejszych kwadratów minimalizuje:

gdzie n = liczba uporządkowanych par wokół linii. najbardziej istotne dla danych.

Koncepcję tę ilustruje rysunek

Sądząc po rysunku, linia, która najlepiej pasuje do danych, linia regresji, minimalizuje całkowity kwadrat błędu czterech punktów na wykresie. W poniższym przykładzie pokażę, jak to wyznaczyć metodą najmniejszych kwadratów.

Wyobraź sobie młodą parę, która od niedawna mieszka razem i dzieli toaletkę w łazience. Młody człowiek zaczął zauważać, że połowa jego stołu kurczy się nieubłaganie, tracąc grunt na rzecz musów do włosów i kompleksów sojowych. W ciągu ostatnich kilku miesięcy facet uważnie monitorował tempo, w jakim rośnie liczba przedmiotów w jej części stołu. Poniższa tabela pokazuje liczbę przedmiotów, które dziewczyna ma na stole w łazience, które nagromadziły się w ciągu ostatnich kilku miesięcy.

Ponieważ naszym celem jest sprawdzenie, czy liczba elementów wzrasta w czasie, „Miesiąc” będzie zmienną niezależną, a „Liczba elementów” będzie zmienną zależną.

Metodą najmniejszych kwadratów określamy równanie, które najlepiej pasuje do danych, obliczając wartości a, odcinka na osi y, oraz b, nachylenia linii:

a = y cf - bx cf

gdzie x cf jest wartością średnią zmiennej niezależnej x, a y cf jest wartością średnią zmiennej niezależnej y.

Poniższa tabela podsumowuje obliczenia wymagane dla tych równań.

Krzywa efektu dla naszego przykładu wanny zostałaby podana przez następujące równanie:

Ponieważ nasze równanie ma dodatnie nachylenie wynoszące 0,976, facet ma dowód, że liczba przedmiotów na stole wzrasta w czasie średnio o 1 przedmiot na miesiąc. Wykres przedstawia krzywą efektu z uporządkowanymi parami.

Przewidywana liczba pozycji na kolejne półrocze (16 miesiąc) zostanie obliczona w następujący sposób:

ŷ = 5,13 + 0,976x = 5,13 + 0,976(16) ~ 20,7 = 21 pozycji

Czas więc, aby nasz bohater podjął jakieś działanie.

Funkcja TREND w Excelu

Jak można się domyślić, Excel ma funkcję obliczania wartości z metoda najmniejszych kwadratów. Ta funkcja nazywa się TREND. Jego składnia jest następująca:

TREND (znane wartości Y; znane wartości X; nowe wartości X; const)

znane wartości Y - tablica zmiennych zależnych, w naszym przypadku liczba pozycji na stole

znane wartości X - tablica zmiennych niezależnych, w naszym przypadku jest to miesiąc

nowe wartości X – nowe wartości X (miesiąc) dla których funkcja TREND zwraca wartość oczekiwaną zmiennych zależnych (liczba pozycji)

const - opcjonalnie. Wartość logiczna określająca, czy stała b musi wynosić 0.

Na przykład na rysunku pokazano funkcję TREND wykorzystywaną do określenia oczekiwanej liczby artykułów na stole łazienkowym w 16. miesiącu.