Analiza regresji liniowej parami. Matematyka na palcach: metody najmniejszych kwadratów Stosowanie metody najmniejszych kwadratów do aproksymacji liniowej

Przybliżenie, Lub przybliżenie- metoda naukowa polegająca na zastąpieniu niektórych obiektów innymi, w pewnym sensie zbliżonymi do oryginalnych, ale prostszymi.

Aproksymacja pozwala na badanie cech numerycznych i właściwości jakościowych obiektu, redukując problem do badania obiektów prostszych lub wygodniejszych (na przykład takich, których charakterystykę można łatwo obliczyć lub których właściwości są już znane). W teorii liczb bada się przybliżenia diofantyczne, w szczególności przybliżenia liczb niewymiernych przez wymierne. W geometrii uwzględnia się przybliżenia krzywych liniami przerywanymi. Niektóre gałęzie matematyki są w zasadzie w całości poświęcone aproksymacji, na przykład teoria aproksymacji funkcji, numeryczne metody analizy.

W sensie przenośnym jest ono używane w filozofii jako metoda aproksymacyjna, wskazanie o charakterze przybliżonym, nieostatecznym. Na przykład w tym sensie terminu „przybliżenie” aktywnie użył Søren Kierkegaard (1813-1855) w „The Final Unscientific Afterword…”

Jeśli funkcja służy tylko do interpolacji, wystarczy przybliżyć punkty wielomianem, powiedzmy, piątego stopnia:

Sytuacja jest znacznie bardziej skomplikowana, jeśli powyższe dane naturalne służą jako punkty odniesienia do identyfikacji prawa zmian ze znanymi warunkami brzegowymi. Na przykład: i . Tutaj jakość wyniku zależy od profesjonalizmu badacza. W tym przypadku najwłaściwszym prawem byłoby:

W celu optymalnego doboru parametrów równania najczęściej stosuje się metodę najmniejszych kwadratów.

Metoda najmniejszych kwadratów (LSM,język angielskiZwykły Najmniej Kwadraty , O.L.S. ) - metoda matematyczna służąca do rozwiązywania różnych problemów, polegająca na minimalizacji sumy kwadratów pewnych funkcji pożądanych zmiennych. Można go zastosować do „rozwiązywania” nadokreślonych układów równań (gdy liczba równań przekracza liczbę niewiadomych), do znalezienia rozwiązania w przypadku zwykłych (nieprzedeterminowanych) nieliniowych układów równań, do aproksymacji wartości punktowych za pomocą jakąś funkcję. OLS jest jedną z podstawowych metod analizy regresji służącą do estymacji nieznanych parametrów modeli regresji na podstawie przykładowych danych.

Jeśli pewna wielkość fizyczna zależy od innej wielkości, wówczas zależność tę można zbadać, mierząc y przy różnych wartościach x. W wyniku pomiarów uzyskuje się szereg wartości:

x 1, x 2, ..., x i, ..., x n;

y 1 , y 2 , ..., y ja , ... , y n .

Na podstawie danych takiego eksperymentu można skonstruować wykres zależności y = ƒ(x). Otrzymana krzywa pozwala ocenić postać funkcji ƒ(x). Jednak stałe współczynniki, które wchodzą w skład tej funkcji, pozostają nieznane. Można je wyznaczyć metodą najmniejszych kwadratów. Punkty eksperymentalne z reguły nie leżą dokładnie na krzywej. Metoda najmniejszych kwadratów wymaga, aby suma kwadratów odchyleń punktów doświadczalnych od krzywej, tj. 2 był najmniejszy.

W praktyce metodę tę najczęściej (i najprościej) stosuje się w przypadku zależności liniowej, tj. Gdy

y = kx Lub y = a + bx.

Zależność liniowa jest bardzo rozpowszechniona w fizyce. A nawet gdy zależność jest nieliniowa, zazwyczaj próbują skonstruować wykres tak, aby otrzymać linię prostą. Przykładowo, jeśli przyjmiemy, że współczynnik załamania światła szkła n jest powiązany z długością fali światła λ zależnością n = a + b/λ 2, to na wykresie wykreślana jest zależność n od λ -2.

Rozważ zależność y = kx(linia prosta przechodząca przez początek). Skomponujmy wartość φ - sumę kwadratów odchyleń naszych punktów od prostej

Wartość φ jest zawsze dodatnia i okazuje się tym mniejsza, im bliżej prostej znajdują się nasze punkty. Metoda najmniejszych kwadratów zakłada, że wartość k należy dobrać tak, aby φ posiadało minimum

lub (19)

Obliczenia pokazują, że błąd średniokwadratowy przy określaniu wartości k jest równy

, (20) gdzie n jest liczbą pomiarów.

Rozważmy teraz nieco trudniejszy przypadek, gdy punkty muszą spełniać wzór y = a + bx(linia prosta, która nie przechodzi przez początek).

Zadanie polega na znalezieniu najlepszych wartości aib z dostępnego zbioru wartości x i, y i.

Utwórzmy ponownie postać kwadratową φ, równą sumie kwadratów odchyleń punktów x i, y i od prostej

i znajdź wartości aib, dla których φ ma minimum

;

Daje wspólne rozwiązanie tych równań

(21)

Pierwiastki średniokwadratowe błędów wyznaczania a i b są równe

(23)

. (24)

Opracowując wyniki pomiarów tą metodą, wygodnie jest podsumować wszystkie dane w tabeli, w której wstępnie wyliczone są wszystkie wielkości zawarte we wzorach (19)–(24). Formy tych tabel podano w poniższych przykładach.

Przykład 1. Badano podstawowe równanie dynamiki ruchu obrotowego ε = M/J (prosta przechodząca przez początek układu współrzędnych). Przy różnych wartościach momentu M mierzono przyspieszenie kątowe ε pewnego ciała. Należy wyznaczyć moment bezwładności tego ciała. Wyniki pomiarów momentu siły i przyspieszenia kątowego zestawiono w kolumnach drugiej i trzeciej tabela 5.

Tabela 5

Korzystając ze wzoru (19) wyznaczamy:

Aby wyznaczyć pierwiastek błędu średniokwadratowego, korzystamy ze wzoru (20)

0.005775 kg-1 · M -2 .

Zgodnie ze wzorem (18) mamy

S J = (2,996 0,005775)/0,3337 = 0,05185 kg m 2 .

Ustalając niezawodność P = 0,95, korzystając z tabeli współczynników Studenta dla n = 5, znajdujemy t = 2,78 i wyznaczamy błąd bezwzględny ΔJ = 2,78 0,05185 = 0,1441 ≈ 0,2 kg m 2 .

Zapiszmy wyniki w postaci:

J = (3,0 ± 0,2) kg m 2 ;

Przykład 2. Obliczmy współczynnik temperaturowy oporu metalu metodą najmniejszych kwadratów. Opór zależy liniowo od temperatury

R t = R 0 (1 + α t°) = R 0 + R 0 α t°.

Wolny człon określa rezystancję R 0 w temperaturze 0 ° C, a nachylenie jest iloczynem współczynnika temperaturowego α i rezystancji R 0 .

Wyniki pomiarów i obliczeń podano w tabeli ( patrz tabela 6).

Tabela 6

							(r - bt - a) 2 ,10 -6

Korzystając ze wzorów (21), (22) wyznaczamy

R 0 = ¯R- α R 0 ¯ t = 1,4005 - 0,002645 85,83333 = 1,1735 Om .

Znajdźmy błąd w definicji α. Ponieważ , to zgodnie ze wzorem (18) mamy:

Korzystając ze wzorów (23), (24) mamy

;

0.014126 Om.

Ustalając niezawodność na P = 0,95, korzystając z tabeli współczynników Studenta dla n = 6, znajdujemy t = 2,57 i wyznaczamy błąd bezwzględny Δα = 2,57 0,000132 = 0,000338 grad -1 .

α = (23 ± 4) 10 -4 grad-1 przy P = 0,95.

Przykład 3. Wymagane jest określenie promienia krzywizny soczewki za pomocą pierścieni Newtona. Zmierzono promienie pierścieni Newtona r m i wyznaczono numery tych pierścieni m. Promienie pierścieni Newtona są powiązane z promieniem krzywizny soczewki R i liczbą pierścieni za pomocą równania

r 2 m = mλR - 2d 0 R,

gdzie d 0 jest grubością szczeliny między soczewką a płytką płasko-równoległą (lub odkształceniem soczewki),

λ jest długością fali padającego światła.

λ = (600 ± 6) nm; r 2 m = y; m = x; λR = b; -2d 0 R = a,

wtedy równanie przyjmie postać y = a + bx.

Wyniki pomiarów i obliczeń są wpisywane tabela 7.

Tabela 7

y = r 2, 10 -2 mm 2	y - bx - a, 10 -4	(y - bx - a) 2 , 10 -6

Obliczamy:

1. aib według wzorów (21), (22).

a = ¯r 2 - b¯m = (0,208548333 - 0,0594957 3,5) = 0,0003133 mm 2 .

2. Oblicz błędy średniokwadratowe dla wartości b i a, korzystając ze wzorów (23), (24)

3. Przy rzetelności P = 0,95 korzystając z tabeli współczynników Studenta dla n = 6 znajdujemy t = 2,57 i wyznaczamy błędy bezwzględne

Δb = 2,57 · 0,000211179 = 6,10 -4 mm 2 ;

Δa = 2,57 0,000822424 = 3 10 -3 mm 2 .

4. Zapisz wyniki

b = (595 ± 6) 10 -4 mm 2 przy P = 0,95;

a = (0,3 ± 3)·10 -3 mm 2 przy P = 0,95;

Z otrzymanych wyników eksperymentalnych wynika, że w granicach błędu tego doświadczenia prosta r 2 m = ƒ(m) przechodzi przez początek współrzędnych, gdyż jeżeli błąd w wartości któregokolwiek parametru okaże się porównywalny lub przekracza wartość parametru, oznacza to, że najprawdopodobniej rzeczywista wartość tego parametru wynosi zero.

W warunkach tego eksperymentu wartość a nie jest interesująca. Dlatego nie będziemy się już tym zajmować.

5. Oblicz promień krzywizny soczewki:

R = b / λ = 594,5 / 6 = 99,1 mm.

6. Ponieważ dla długości fali podany jest błąd systematyczny, obliczmy także błąd systematyczny dla R ze wzoru (16), przyjmując za błąd systematyczny wielkości b jej błąd losowy Δb.

Zapisujemy wynik końcowy R = (99 ± 2) mmε ≈ 3% przy P = 0,95.

Co znajduje najszersze zastosowanie w różnych dziedzinach nauki i działalności praktycznej. Może to być fizyka, chemia, biologia, ekonomia, socjologia, psychologia i tak dalej, i tak dalej. Zrządzeniem losu często muszę zajmować się gospodarką, dlatego dziś zorganizuję dla Was wycieczkę do niesamowitego kraju zwanego Ekonometria=) ...Jak możesz tego nie chcieć?! Jest tam bardzo dobrze – trzeba się tylko zdecydować! ...Ale prawdopodobnie na pewno chcesz nauczyć się rozwiązywać problemy metoda najmniejszych kwadratów. A szczególnie pilni czytelnicy nauczą się je rozwiązywać nie tylko dokładnie, ale i BARDZO SZYBKO ;-) Ale najpierw ogólne przedstawienie problemu+ dołączony przykład:

Przeanalizujmy wskaźniki w określonym obszarze tematycznym, które mają wyraz ilościowy. Jednocześnie istnieją podstawy, aby sądzić, że wskaźnik zależy od wskaźnika. Założenie to może być hipotezą naukową lub opierać się na podstawowym zdrowym rozsądku. Zostawmy jednak naukę na boku i zajmijmy się bardziej apetycznymi rejonami – czyli sklepami spożywczymi. Oznaczmy przez:

– powierzchnia handlowa sklepu spożywczego mkw.,
– roczny obrót sklepu spożywczego, mln rubli.

Jest całkowicie jasne, że im większa powierzchnia sklepu, tym w większości przypadków większe będą jego obroty.

Załóżmy, że po przeprowadzeniu obserwacji/eksperymentów/obliczeń/tańców z tamburynem dysponujemy danymi liczbowymi:

W przypadku sklepów spożywczych myślę, że wszystko jest jasne: - jest to powierzchnia pierwszego sklepu, - jego roczny obrót, - powierzchnia drugiego sklepu, - jego roczny obrót itp. Notabene posiadanie dostępu do materiałów niejawnych wcale nie jest konieczne – w miarę dokładną ocenę obrotów handlowych można uzyskać za pomocą statystyka matematyczna. Jednak nie dajmy się rozpraszać, kurs szpiegostwa komercyjnego jest już opłacony =)

Dane tabelaryczne można również zapisać w formie punktów i przedstawić w znanej formie Układ kartezjański .

Odpowiedzmy sobie na ważne pytanie: Ile punktów potrzeba do badania jakościowego?

Im większy tym lepszy. Minimalny akceptowalny set to 5-6 punktów. Ponadto, gdy ilość danych jest niewielka, nie można uwzględnić w próbie wyników „anomalnych”. Na przykład mały elitarny sklep może zarobić o rząd wielkości więcej niż „jego koledzy”, zniekształcając w ten sposób ogólny wzór, który musisz znaleźć!

Mówiąc najprościej, musimy wybrać funkcję, harmonogram który przechodzi jak najbliżej punktów . Ta funkcja nazywa się przybliżanie (przybliżenie - przybliżenie) Lub funkcja teoretyczna . Ogólnie rzecz biorąc, natychmiast pojawia się tutaj oczywisty „kontener” - wielomian wysokiego stopnia, którego wykres przechodzi przez WSZYSTKIE punkty. Ale ta opcja jest skomplikowana i często po prostu niepoprawna. (ponieważ wykres będzie się cały czas „zapętlał” i słabo odzwierciedlał główny trend).

Zatem poszukiwana funkcja musi być dość prosta i jednocześnie odpowiednio odzwierciedlać zależność. Jak można się domyślić, jedna z metod znajdowania takich funkcji nazywa się metoda najmniejszych kwadratów. Najpierw spójrzmy ogólnie na jego istotę. Niech jakaś funkcja przybliży dane eksperymentalne:

Jak ocenić dokładność tego przybliżenia? Obliczmy także różnice (odchylenia) pomiędzy wartościami doświadczalnymi i funkcjonalnymi (studiujemy rysunek). Pierwszą myślą, która przychodzi na myśl, jest oszacowanie, jak duża jest to suma, problem jednak polega na tym, że różnice mogą być ujemne (Na przykład, ) a odchylenia powstałe w wyniku takiego sumowania będą się wzajemnie znosić. Dlatego też, jako oszacowanie dokładności przybliżenia, aż prosi się o przyjęcie sumy moduły odchylenia:

lub upadł: (gdyby ktoś nie wiedział: – to ikona sumy, oraz – pomocnicza zmienna „licznik”, która przyjmuje wartości od 1 do ).

Aproksymując punkty eksperymentalne różnymi funkcjami, otrzymamy różne wartości i oczywiście, gdy suma ta jest mniejsza, funkcja ta jest dokładniejsza.

Taka metoda istnieje i nazywa się metoda najmniejszego modułu. Jednak w praktyce stało się to znacznie bardziej powszechne metoda najmniejszych kwadratów, w którym możliwe wartości ujemne są eliminowane nie przez moduł, ale przez podniesienie odchyleń do kwadratu:

, po czym dąży się do wybrania takiej funkcji, która będzie sumą kwadratów odchyleń był tak mały, jak to tylko możliwe. Właściwie stąd wzięła się nazwa tej metody.

A teraz wracamy do innego ważnego punktu: jak wspomniano powyżej, wybrana funkcja powinna być dość prosta - ale takich funkcji jest również wiele: liniowy , hiperboliczny, wykładniczy, logarytmiczny, kwadratowy itp. I oczywiście tutaj chciałbym od razu „zmniejszyć pole działania”. Jaką klasę funkcji wybrać do badań? Prymitywna, ale skuteczna technika:

– Najłatwiej jest przedstawić punkty na rysunku i przeanalizuj ich położenie. Jeśli mają tendencję do biegania w linii prostej, powinieneś poszukać równanie linii z optymalnymi wartościami i . Inaczej mówiąc, zadaniem jest znaleźć TAKIE współczynniki, aby suma kwadratów odchyleń była jak najmniejsza.

Jeśli punkty znajdują się np. wzdłuż hiperbola, to jest oczywiste, że funkcja liniowa daje słabe przybliżenie. W tym przypadku szukamy najbardziej „korzystnych” współczynników dla równania hiperboli – takie, które dają minimalną sumę kwadratów .

Teraz zauważ, że w obu przypadkach mówimy funkcje dwóch zmiennych, którego argumentami są szukane parametry zależności:

Zasadniczo musimy rozwiązać standardowy problem - znaleźć funkcja minimalna dwóch zmiennych.

Przypomnijmy nasz przykład: załóżmy, że punkty „sklepowe” zwykle leżą na linii prostej i istnieją podstawy, aby tak sądzić zależność liniowa obrotów z powierzchni handlowej. Znajdźmy TAKIE współczynniki „a” i „be” takie, że suma kwadratów odchyleń był najmniejszy. Wszystko jest jak zwykle - najpierw Pochodne cząstkowe pierwszego rzędu. Według reguła liniowości Możesz rozróżnić bezpośrednio pod ikoną sumy:

Jeśli chcesz wykorzystać te informacje w pracy esejowej lub semestralnej, będę bardzo wdzięczny za link w wykazie źródeł, tak szczegółowe wyliczenia znajdziesz w kilku miejscach:

Stwórzmy standardowy system:

Każde równanie redukujemy o „dwa” i dodatkowo „rozbijamy” sumy:

Notatka : niezależnie przeanalizuj, dlaczego „a” i „być” można wyjąć poza ikoną sumy. Nawiasem mówiąc, formalnie można to zrobić za pomocą sumy

Przepiszmy system w formie „stosowanej”:

po czym zaczyna się wyłaniać algorytm rozwiązania naszego problemu:

Czy znamy współrzędne punktów? Wiemy. Kwoty czy możemy to znaleźć? Łatwo. Zróbmy najprostsze układ dwóch równań liniowych z dwiema niewiadomymi(„a” i „być”). Rozwiązujemy układ np. Metoda Cramera, w wyniku czego otrzymujemy punkt stacjonarny. Kontrola warunek wystarczający na ekstremum, możemy sprawdzić, że w tym momencie funkcja sięga dokładnie minimum. Sprawdzenie wiąże się z dodatkowymi obliczeniami, dlatego pozostawimy to za kulisami (w razie potrzeby można obejrzeć brakującą klatkę). Wyciągamy ostateczny wniosek:

Funkcjonować Najlepszym sposobem (przynajmniej w porównaniu z jakąkolwiek inną funkcją liniową) przybliża punkty doświadczalne . Z grubsza rzecz biorąc, jego wykres przebiega jak najbliżej tych punktów. W tradycji ekonometria wynikowa funkcja aproksymująca jest również nazywana sparowane równanie regresji liniowej .

Rozważany problem ma duże znaczenie praktyczne. W naszej przykładowej sytuacji równanie. pozwala przewidzieć jakie obroty handlowe („Igrek”) sklep będzie miał taką czy inną wartość powierzchni sprzedażowej (takie czy inne znaczenie „x”). Tak, powstała prognoza będzie jedynie prognozą, ale w wielu przypadkach okaże się dość dokładna.

Przeanalizuję tylko jeden problem z liczbami „prawdziwymi”, ponieważ nie ma w nim żadnych trudności - wszystkie obliczenia są na poziomie programu nauczania w szkole 7-8 klasy. W 95 procentach przypadków zostaniesz poproszony o znalezienie tylko funkcji liniowej, ale na samym końcu artykułu pokażę, że znalezienie równań optymalnej hiperboli, funkcji wykładniczej i niektórych innych nie jest już trudniejsze.

Tak naprawdę pozostaje tylko rozdać obiecane gadżety - abyście mogli nauczyć się rozwiązywać takie przykłady nie tylko dokładnie, ale i szybko. Dokładnie badamy standard:

Zadanie

W wyniku badania zależności pomiędzy dwoma wskaźnikami otrzymano następujące pary liczb:

Korzystając z metody najmniejszych kwadratów, znajdź funkcję liniową, która najlepiej przybliża funkcję empiryczną (doświadczony) dane. Wykonaj rysunek, na podstawie którego skonstruujesz punkty doświadczalne oraz wykres funkcji aproksymującej w prostokątnym układzie współrzędnych kartezjańskich . Znajdź sumę kwadratów odchyleń między wartościami empirycznymi i teoretycznymi. Dowiedz się, czy ta funkcja byłaby lepsza (z punktu widzenia metody najmniejszych kwadratów) przybliżyć punkty doświadczalne.

Należy pamiętać, że znaczenia „x” są naturalne i ma to charakterystyczne znaczenie znaczące, o którym opowiem nieco później; ale oczywiście mogą być również ułamkowe. Ponadto, w zależności od treści konkretnego zadania, zarówno wartości „X”, jak i „gry” mogą być całkowicie lub częściowo ujemne. Cóż, dostaliśmy zadanie „bez twarzy” i zaczynamy je rozwiązanie:

Znajdujemy współczynniki funkcji optymalnej jako rozwiązanie układu:

W celu bardziej zwięzłego zapisu można pominąć zmienną „licznik”, ponieważ jest już jasne, że sumowanie odbywa się od 1 do .

Wygodniej jest obliczyć wymagane kwoty w formie tabelarycznej:

Obliczenia można przeprowadzić na mikrokalkulatorze, ale znacznie lepiej jest korzystać z Excela - zarówno szybciej, jak i bez błędów; obejrzyj krótki film:

W ten sposób otrzymujemy, co następuje system:

Tutaj możesz pomnożyć drugie równanie przez 3 i odejmij drugie od pierwszego równania wyraz po wyrazie. Ale to szczęście – w praktyce systemy często nie są prezentem, a w takich przypadkach oszczędzają Metoda Cramera:
co oznacza, że system posiada unikalne rozwiązanie.

Sprawdźmy. Rozumiem, że nie chcesz, ale po co pomijać błędy, których absolutnie nie da się przeoczyć? Podstawmy znalezione rozwiązanie po lewej stronie każdego równania układu:

Otrzymuje się prawe strony odpowiednich równań, co oznacza, że układ jest rozwiązany poprawnie.

Zatem pożądana funkcja aproksymująca: – od wszystkie funkcje liniowe To ona najlepiej przybliża dane eksperymentalne.

w odróżnieniu prosty zależności obrotów sklepu od jego powierzchni, znaleziona zależność wynosi odwracać (zasada „im więcej, tym mniej”), a fakt ten jest natychmiast ujawniany przez negatyw nachylenie. Funkcjonować mówi nam, że wraz ze wzrostem pewnego wskaźnika o 1 jednostkę wartość wskaźnika zależnego maleje przeciętny o 0,65 jednostki. Jak mówią, im wyższa cena gryki, tym mniej się jej sprzedaje.

Aby wykreślić wykres funkcji aproksymującej, znajdujemy jej dwie wartości:

i wykonaj rysunek:

Zbudowana linia prosta nazywa się linia trendu (mianowicie liniowa linia trendu, tj. w ogólnym przypadku trend niekoniecznie jest linią prostą). Każdemu znane jest wyrażenie „być w trendzie” i myślę, że to określenie nie wymaga dodatkowego komentarza.

Obliczmy sumę kwadratów odchyleń pomiędzy wartościami empirycznymi i teoretycznymi. Geometrycznie jest to suma kwadratów długości odcinków „malinowych”. (z czego dwa są tak małe, że nawet ich nie widać).

Podsumujmy obliczenia w tabeli:

Ponownie można to zrobić ręcznie, na wszelki wypadek podam przykład dla punktu 1:

ale o wiele skuteczniej jest zrobić to w znany już sposób:

Powtarzamy jeszcze raz: Jakie jest znaczenie uzyskanego wyniku? Z wszystkie funkcje liniowe funkcja wskaźnik jest najmniejszy, czyli w swojej rodzinie jest najlepszym przybliżeniem. I tutaj, nawiasem mówiąc, ostatnie pytanie problemu nie jest przypadkowe: co by było, gdyby proponowana funkcja wykładnicza czy lepiej byłoby przybliżyć punkty eksperymentalne?

Znajdźmy odpowiednią sumę kwadratów odchyleń - dla rozróżnienia oznaczę je literą „epsilon”. Technika jest dokładnie taka sama:

I znowu, na wszelki wypadek, obliczenia dla 1. punktu:

W Excelu używamy funkcji standardowej DO POTĘGI (składnię można znaleźć w Pomocy programu Excel).

Wniosek: , co oznacza, że funkcja wykładnicza przybliża punkty eksperymentalne gorzej niż linia prosta .

Ale tutaj należy zauważyć, że „gorsze” jest nie znaczy jeszcze, co jest nie tak. Teraz zbudowałem wykres tej funkcji wykładniczej - i ona również przechodzi blisko punktów - do tego stopnia, że bez badań analitycznych trudno stwierdzić, która funkcja jest dokładniejsza.

Na tym kończy się rozwiązanie i wracam do kwestii naturalnych wartości argumentu. W różnych badaniach, zwykle ekonomicznych lub socjologicznych, naturalne „X” są używane do numerowania miesięcy, lat lub innych równych przedziałów czasu. Rozważmy na przykład następujący problem.

Metoda najmniejszych kwadratów jest jedną z najpowszechniejszych i najbardziej rozwiniętych ze względu na swoje możliwości prostota i efektywność metod estymacji parametrów liniowych. Jednocześnie przy jego stosowaniu należy zachować pewną ostrożność, gdyż modele zbudowane przy jego pomocy mogą nie spełniać szeregu wymagań co do jakości swoich parametrów i w efekcie nie odzwierciedlać „dobrze” wzorców rozwoju procesów wystarczająco.

Rozważmy bardziej szczegółowo procedurę szacowania parametrów liniowego modelu ekonometrycznego metodą najmniejszych kwadratów. Model taki w ogólności można przedstawić za pomocą równania (1.2):

y t = za 0 + za 1 x 1 t +...+ za n x nt + ε t.

Dane początkowe przy estymacji parametrów a 0 , a 1 ,..., a n są wektorem wartości zmiennej zależnej y= (y 1 , y 2 , ... , y T)” i macierz wartości zmiennych niezależnych

w którym pierwsza kolumna składająca się z jedynek odpowiada współczynnikowi modelu.

Metoda najmniejszych kwadratów otrzymała swoją nazwę w oparciu o podstawową zasadę, że otrzymane na jej podstawie estymatory parametrów muszą spełniać: suma kwadratów błędu modelu powinna być minimalna.

Przykłady rozwiązywania problemów metodą najmniejszych kwadratów

Przykład 2.1. Przedsiębiorstwo handlowe posiada sieć 12 sklepów, informacje o działalności przedstawiono w tabeli. 2.1.

Kierownictwo przedsiębiorstwa chciałoby wiedzieć, w jaki sposób roczna kwota zależy od powierzchni handlowej sklepu.

Tabela 2.1

Numer sklepu	Roczny obrót, miliony rubli.	Powierzchnia handlowa, tys. m2

Rozwiązanie metodą najmniejszych kwadratów. Oznaczmy roczny obrót sklepu, milion rubli; — powierzchnia handlowa sklepu, tys. m2.

Ryc.2.1. Wykres rozrzutu dla przykładu 2.1

Aby określić postać zależności funkcjonalnej między zmiennymi, skonstruujemy diagram rozproszenia (ryc. 2.1).

Na podstawie diagramu punktowego możemy stwierdzić, że roczny obrót jest dodatnio zależny od powierzchni handlowej (tj. y będzie rosło wraz ze wzrostem). Najbardziej odpowiednią formą połączenia funkcjonalnego jest liniowy.

Informacje do dalszych obliczeń przedstawiono w tabeli. 2.2. Metodą najmniejszych kwadratów szacujemy parametry liniowego jednoczynnikowego modelu ekonometrycznego

Tabela 2.2

Zatem,

Zatem przy wzroście powierzchni handlowej o 1 tys. m2, przy pozostałych czynnikach niezmienionych, średni roczny obrót wzrasta o 67,8871 mln rubli.

Przykład 2.2. Zarząd firmy zauważył, że roczny obrót zależy nie tylko od powierzchni sprzedażowej sklepu (patrz przykład 2.1), ale także od średniej liczby odwiedzających. Odpowiednie informacje przedstawiono w tabeli. 2.3.

Tabela 2.3

Rozwiązanie. Oznaczmy średnią liczbę odwiedzających dziennie sklep, tys. osób.

Aby określić postać zależności funkcjonalnej między zmiennymi, skonstruujemy diagram rozrzutu (ryc. 2.2).

Na podstawie wykresu rozrzutu możemy stwierdzić, że roczny obrót jest dodatnio zależny od średniej liczby odwiedzających dziennie (tj. y będzie rosło wraz ze wzrostem ). Forma zależności funkcjonalnej jest liniowa.

Ryż. 2.2. Wykres rozrzutu dla przykładu 2.2

Tabela 2.4

Generalnie konieczne jest określenie parametrów dwuczynnikowego modelu ekonometrycznego

y t = za 0 + za 1 x 1 t + za 2 x 2 t + ε t

Informacje potrzebne do dalszych obliczeń przedstawiono w tabeli. 2.4.

Oszacujmy parametry liniowego dwuczynnikowego modelu ekonometrycznego metodą najmniejszych kwadratów.

Zatem,

Oszacowanie współczynnika =61,6583 pokazuje, że przy niezmienionych warunkach, wraz ze wzrostem powierzchni handlowej o 1 tys. m 2, roczny obrót wzrośnie średnio o 61,6583 mln rubli.

Metoda najmniejszych kwadratów

W ostatniej lekcji tematu zapoznamy się z najsłynniejszą aplikacją FNP, co znajduje najszersze zastosowanie w różnych dziedzinach nauki i działalności praktycznej. Może to być fizyka, chemia, biologia, ekonomia, socjologia, psychologia i tak dalej, i tak dalej. Zrządzeniem losu często muszę zajmować się gospodarką, dlatego dziś zorganizuję dla Was wycieczkę do niesamowitego kraju zwanego Ekonometria=) ...Jak możesz tego nie chcieć?! Jest tam bardzo dobrze – trzeba się tylko zdecydować! ...Ale prawdopodobnie na pewno chcesz nauczyć się rozwiązywać problemy metoda najmniejszych kwadratów. A szczególnie pilni czytelnicy nauczą się je rozwiązywać nie tylko dokładnie, ale i BARDZO SZYBKO ;-) Ale najpierw ogólne przedstawienie problemu+ dołączony przykład:

– powierzchnia handlowa sklepu spożywczego mkw.,
– roczny obrót sklepu spożywczego, mln rubli.

Jest całkowicie jasne, że im większa powierzchnia sklepu, tym w większości przypadków większe będą jego obroty.

Dane tabelaryczne można również zapisać w formie punktów i przedstawić w znanej formie Układ kartezjański .

Odpowiedzmy sobie na ważne pytanie: Ile punktów potrzeba do badania jakościowego?

Mówiąc najprościej, musimy wybrać funkcję, harmonogram który przechodzi jak najbliżej punktów . Ta funkcja nazywa się przybliżanie (przybliżenie - przybliżenie) Lub funkcja teoretyczna . Ogólnie rzecz biorąc, natychmiast pojawia się tutaj oczywisty „kontener” - wielomian wysokiego stopnia, którego wykres przechodzi przez WSZYSTKIE punkty. Ale ta opcja jest skomplikowana i często po prostu niepoprawna. (ponieważ wykres będzie się cały czas „zapętlał” i słabo odzwierciedlał główny trend).

Zatem poszukiwana funkcja musi być dość prosta i jednocześnie odpowiednio odzwierciedlać zależność. Jak można się domyślić, jedna z metod znajdowania takich funkcji nazywa się metoda najmniejszych kwadratów. Najpierw spójrzmy ogólnie na jego istotę. Niech jakaś funkcja przybliży dane eksperymentalne:

Jak ocenić dokładność tego przybliżenia? Obliczmy także różnice (odchylenia) pomiędzy wartościami doświadczalnymi i funkcjonalnymi (studiujemy rysunek). Pierwszą myślą, która przychodzi na myśl, jest oszacowanie, jak duża jest to suma, problem jednak polega na tym, że różnice mogą być ujemne (Na przykład, ) a odchylenia powstałe w wyniku takiego sumowania będą się wzajemnie znosić. Dlatego też, jako oszacowanie dokładności przybliżenia, aż prosi się o przyjęcie sumy moduły odchylenia:

lub upadł: (jeśli ktoś nie wie: jest ikoną sumy, oraz – pomocnicza zmienna „licznikowa”, która przyjmuje wartości od 1 do ) .

Aproksymując punkty eksperymentalne różnymi funkcjami, otrzymamy różne wartości i oczywiście, gdy suma ta jest mniejsza, funkcja ta jest dokładniejsza.

, po czym dąży się do wybrania takiej funkcji, która będzie sumą kwadratów odchyleń był tak mały, jak to tylko możliwe. Właściwie stąd wzięła się nazwa tej metody.

A teraz wracamy do innego ważnego punktu: jak wspomniano powyżej, wybrana funkcja powinna być dość prosta - ale takich funkcji jest również wiele: liniowy , hiperboliczny , wykładniczy , logarytmiczny , kwadratowy itp. I oczywiście tutaj chciałbym od razu „zmniejszyć pole działania”. Jaką klasę funkcji wybrać do badań? Prymitywna, ale skuteczna technika:

Teraz zauważ, że w obu przypadkach mówimy funkcje dwóch zmiennych, którego argumentami są szukane parametry zależności:

Zasadniczo musimy rozwiązać standardowy problem - znaleźć funkcja minimalna dwóch zmiennych.

Jeśli chcesz wykorzystać te informacje w pracy esejowej lub semestralnej, będę bardzo wdzięczny za link w wykazie źródeł, tak szczegółowe wyliczenia znajdziesz w kilku miejscach:

Stwórzmy standardowy system:

Każde równanie redukujemy o „dwa” i dodatkowo „rozbijamy” sumy:

Notatka : niezależnie przeanalizuj, dlaczego „a” i „być” można wyjąć poza ikoną sumy. Nawiasem mówiąc, formalnie można to zrobić za pomocą sumy

Przepiszmy system w formie „stosowanej”:

po czym zaczyna się wyłaniać algorytm rozwiązania naszego problemu:

Tak naprawdę pozostaje tylko rozdać obiecane gadżety - abyście mogli nauczyć się rozwiązywać takie przykłady nie tylko dokładnie, ale i szybko. Dokładnie badamy standard:

Zadanie

Znajdujemy współczynniki funkcji optymalnej jako rozwiązanie układu:

W celu bardziej zwięzłego zapisu można pominąć zmienną „licznik”, ponieważ jest już jasne, że sumowanie odbywa się od 1 do .

W ten sposób otrzymujemy, co następuje system:

Zatem pożądana funkcja aproksymująca: – od wszystkie funkcje liniowe To ona najlepiej przybliża dane eksperymentalne.

w odróżnieniu prosty zależności obrotów sklepu od jego powierzchni, znaleziona zależność wynosi odwracać (zasada „im więcej, tym mniej”), a fakt ten jest natychmiast ujawniany przez negatyw nachylenie. Funkcjonować mówi nam, że wraz ze wzrostem pewnego wskaźnika o 1 jednostkę wartość wskaźnika zależnego maleje przeciętny o 0,65 jednostki. Jak mówią, im wyższa cena gryki, tym mniej się jej sprzedaje.

Aby wykreślić wykres funkcji aproksymującej, znajdujemy jej dwie wartości:

i wykonaj rysunek:

Zbudowana linia prosta nazywa się linia trendu (mianowicie liniowa linia trendu, tj. w ogólnym przypadku trend niekoniecznie jest linią prostą). Każdemu znane jest wyrażenie „być w trendzie” i myślę, że to określenie nie wymaga dodatkowego komentarza.

Podsumujmy obliczenia w tabeli:

Ponownie można to zrobić ręcznie, na wszelki wypadek podam przykład dla punktu 1:

ale o wiele skuteczniej jest zrobić to w znany już sposób:

Wniosek: , co oznacza, że funkcja wykładnicza przybliża punkty eksperymentalne gorzej niż linia prosta .

Dostępne są następujące dane dotyczące obrotów detalicznych sklepu za pierwsze półrocze:

Korzystając z analitycznego wyrównania liniowego, określ wielkość obrotów za lipiec.

Tak, nie ma problemu: numerujemy miesiące 1, 2, 3, 4, 5, 6 i stosujemy zwykły algorytm, w wyniku którego otrzymujemy równanie - z tą tylko różnicą, że jeśli chodzi o czas, zwykle używają litera „te” (chociaż nie jest to krytyczne). Z otrzymanego równania wynika, że w pierwszym półroczu obroty handlowe wzrosły średnio o 27,74 jednostki. na miesiąc. Poznajmy prognozę na lipiec (miesiąc nr 7): d.e.

A takich zadań jest niezliczona ilość. Chętni mogą skorzystać z dodatkowej usługi, a mianowicie my Kalkulator Excela (wersja demo), Który rozwiązuje analizowany problem niemal natychmiast! Dostępna jest działająca wersja programu w zamian albo za symboliczna opłata.

Na koniec lekcji krótka informacja o wyszukiwaniu zależności niektórych innych typów. Właściwie nie ma wiele do powiedzenia, ponieważ podstawowe podejście i algorytm rozwiązania pozostają takie same.

Załóżmy, że układ punktów doświadczalnych przypomina hiperbolę. Następnie, aby znaleźć współczynniki najlepszej hiperboli, trzeba znaleźć minimum funkcji - każdy może przeprowadzić szczegółowe obliczenia i dojść do podobnego układu:

Z formalnego technicznego punktu widzenia uzyskuje się go z układu „liniowego”. (oznaczmy to gwiazdką) zastępując „x” przez . No właśnie, a co z kwotami? obliczyć, po czym do optymalnych współczynników „a” i „być” pod ręką.

Jeśli istnieją podstawy, aby sądzić, że punkty znajdują się wzdłuż krzywej logarytmicznej, wówczas aby znaleźć wartości optymalne, znajdujemy minimum funkcji . Formalnie w systemie (*) należy zastąpić:

Wykonując obliczenia w programie Excel, użyj funkcji LN. Przyznam, że stworzenie kalkulatorów dla każdego z rozpatrywanych przypadków nie byłoby dla mnie szczególnie trudne, ale byłoby jeszcze lepiej, gdybyś sam „zaprogramował” obliczenia. Filmy szkoleniowe, które mogą Ci pomóc.

W przypadku zależności wykładniczej sytuacja jest nieco bardziej skomplikowana. Aby sprowadzić sprawę do przypadku liniowego, bierzemy logarytm funkcji i używamy właściwości logarytmu:

Teraz porównując otrzymaną funkcję z funkcją liniową dochodzimy do wniosku, że w systemie (*) należy zastąpić , i – przez . Dla wygody oznaczmy:

Należy pamiętać, że układ jest rozwiązywany ze względu na i dlatego po znalezieniu pierwiastków nie można zapomnieć o znalezieniu samego współczynnika.

Aby przybliżyć punkty doświadczalne optymalna parabola , powinien się znaleźć minimalna funkcja trzech zmiennych . Po wykonaniu standardowych czynności otrzymujemy następujące „działające” system:

Tak, oczywiście, kwot jest tutaj więcej, ale korzystanie z ulubionej aplikacji nie sprawia żadnych trudności. Na koniec powiem Ci, jak szybko sprawdzić w Excelu i zbudować pożądaną linię trendu: utwórz wykres punktowy, wybierz dowolny punkt myszką i kliknij prawym przyciskiem myszy wybierz opcję „Dodaj linię trendu”. Następnie wybierz typ wykresu i na zakładce „Opcje” aktywuj opcję „Pokaż równanie na diagramie”. OK

Jak zawsze chcę zakończyć artykuł jakimś pięknym zwrotem i prawie napisałam „Bądź trendy!” Ale z czasem zmienił zdanie. I nie dlatego, że jest to stereotypowe. Nie wiem jak u kogokolwiek, ale ja nie bardzo chcę podążać za promowanym amerykańskim, a zwłaszcza europejskim trendem =) Dlatego życzę każdemu z Was, aby trzymał się swojej linii!

http://www.grandars.ru/student/vysshaya-matematika/metod-naimenshih-kvadratov.html

Metoda najmniejszych kwadratów jest jedną z najpowszechniejszych i najbardziej rozwiniętych ze względu na swoje możliwości prostota i efektywność metod estymacji parametrów liniowych modeli ekonometrycznych. Jednocześnie przy jego stosowaniu należy zachować pewną ostrożność, gdyż modele zbudowane przy jego pomocy mogą nie spełniać szeregu wymagań co do jakości swoich parametrów i w efekcie nie odzwierciedlać „dobrze” wzorców rozwoju procesów wystarczająco.

y t = za 0 + za 1 x 1t +...+ za n x nt + ε t.

Dane początkowe przy estymacji parametrów a 0 , a 1 ,..., a n są wektorem wartości zmiennej zależnej y= (y 1 , y 2 , ... , y T)” i macierz wartości zmiennych niezależnych

w którym pierwsza kolumna składająca się z jedynek odpowiada współczynnikowi modelu.

Przykłady rozwiązywania problemów metodą najmniejszych kwadratów

Przykład 2.1. Przedsiębiorstwo handlowe posiada sieć 12 sklepów, informacje o działalności przedstawiono w tabeli. 2.1.

Kierownictwo przedsiębiorstwa chciałoby wiedzieć, jak wielkość rocznych obrotów zależy od powierzchni handlowej sklepu.

Tabela 2.1

Numer sklepu	Roczny obrót, miliony rubli.	Powierzchnia handlowa, tys. m2
	19,76	0,24
	38,09	0,31
	40,95	0,55
	41,08	0,48
	56,29	0,78
	68,51	0,98
	75,01	0,94
	89,05	1,21
	91,13	1,29
	91,26	1,12
	99,84	1,29
	108,55	1,49

Rozwiązanie metodą najmniejszych kwadratów. Oznaczmy roczny obrót sklepu, milion rubli; - powierzchnia handlowa sklepu, tys. m2.

Ryc.2.1. Wykres rozrzutu dla przykładu 2.1

Aby określić postać zależności funkcjonalnej między zmiennymi, skonstruujemy diagram rozproszenia (ryc. 2.1).

Informacje do dalszych obliczeń przedstawiono w tabeli. 2.2. Metodą najmniejszych kwadratów szacujemy parametry liniowego jednoczynnikowego modelu ekonometrycznego

Tabela 2.2

T	t	x 1t	tak 2	x 1t 2	x 1t i t

	19,76	0,24	390,4576	0,0576	4,7424
	38,09	0,31	1450,8481	0,0961	11,8079
	40,95	0,55	1676,9025	0,3025	22,5225
	41,08	0,48	1687,5664	0,2304	19,7184
	56,29	0,78	3168,5641	0,6084	43,9062
	68,51	0,98	4693,6201	0,9604	67,1398
	75,01	0,94	5626,5001	0,8836	70,5094
	89,05	1,21	7929,9025	1,4641	107,7505
	91,13	1,29	8304,6769	1,6641	117,5577
	91,26	1,12	8328,3876	1,2544	102,2112
	99,84	1,29	9968,0256	1,6641	128,7936
	108,55	1,49	11783,1025	2,2201	161,7395
S	819,52	10,68	65008,554	11,4058	858,3991
Przeciętny	68,29	0,89

Zatem,

Zatem przy wzroście powierzchni handlowej o 1 tys. m2, przy pozostałych czynnikach niezmienionych, średni roczny obrót wzrasta o 67,8871 mln rubli.

Tabela 2.3

Rozwiązanie. Oznaczmy - średnią liczbę odwiedzających dziennie sklep VI, tys. osób.

Aby określić postać zależności funkcjonalnej między zmiennymi, skonstruujemy diagram rozrzutu (ryc. 2.2).

Ryż. 2.2. Wykres rozrzutu dla przykładu 2.2

Tabela 2.4

T	x 2t	x 2t 2	y t x 2 t	x 1 t x 2 t

	8,25	68,0625	163,02	1,98
	10,24	104,8575	390,0416	3,1744
	9,31	86,6761	381,2445	5,1205
	11,01	121,2201	452,2908	5,2848
	8,54	72,9316	480,7166	6,6612
	7,51	56,4001	514,5101	7,3598
	12,36	152,7696	927,1236	11,6184
	10,81	116,8561	962,6305	13,0801
	9,89	97,8121	901,2757	12,7581
	13,72	188,2384	1252,0872	15,3664
	12,27	150,5529	1225,0368	15,8283
	13,92	193,7664	1511,016	20,7408
S	127,83	1410,44	9160,9934	118,9728
Przeciętny	10,65

Generalnie konieczne jest określenie parametrów dwuczynnikowego modelu ekonometrycznego

y t = za 0 + za 1 x 1 t + za 2 x 2 t + ε t

Informacje potrzebne do dalszych obliczeń przedstawiono w tabeli. 2.4.

Oszacujmy parametry liniowego dwuczynnikowego modelu ekonometrycznego metodą najmniejszych kwadratów.

Zatem,

Oszacowanie współczynnika =61,6583 pokazuje, że przy niezmienionych warunkach, wraz ze wzrostem powierzchni handlowej o 1 tys. m 2, roczny obrót wzrośnie średnio o 61,6583 mln rubli.

Współczynnik szacunkowy = 2,2748 pokazuje, że przy pozostałych czynnikach niezmiennych, przy wzroście średniej liczby odwiedzających na 1 tys. osób. dziennie roczny obrót wzrośnie średnio o 2,2748 mln rubli.

Przykład 2.3. Korzystając z informacji przedstawionych w tabeli. 2.2 i 2.4 oszacuj parametr jednoczynnikowego modelu ekonometrycznego

gdzie jest wyśrodkowana wartość rocznego obrotu sklepu, miliony rubli; - wyśrodkowana wartość średniej dziennej liczby odwiedzających t-ty sklep, tys. osób. (patrz przykłady 2.1-2.2).

Rozwiązanie. Dodatkowe informacje potrzebne do obliczeń przedstawiono w tabeli. 2.5.

Tabela 2.5



	-48,53	-2,40	5,7720	116,6013
	-30,20	-0,41	0,1702	12,4589
	-27,34	-1,34	1,8023	36,7084
	-27,21	0,36	0,1278	-9,7288
	-12,00	-2,11	4,4627	25,3570
	0,22	-3,14	9,8753	-0,6809
	6,72	1,71	2,9156	11,4687
	20,76	0,16	0,0348	3,2992
	22,84	-0,76	0,5814	-17,413
	22,97	3,07	9,4096	70,4503
	31,55	1,62	2,6163	51,0267
	40,26	3,27	10,6766	131,5387
Kwota			48,4344	431,0566

Korzystając ze wzoru (2.35) otrzymujemy

Zatem,

http://www.cleverstudents.ru/articles/mnk.html

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ś.

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.

Dowód.

Tak więc, gdy zostanie znaleziony A I B funkcja przyjmuje najmniejszą wartość, konieczne jest, aby w tym miejscu macierz postaci kwadratowej różniczki drugiego rzędu dla funkcji był dodatnio określony. Pokażmy to.

Różniczka drugiego rzędu ma postać:

To jest

Zatem macierz postaci kwadratowej ma postać

a wartości elementów nie zależą od A I B.

Pokażmy, że macierz jest dodatnio określona. Aby to zrobić, nieletni kątowe muszą być dodatnie.

Moll kątowy pierwszego rzędu . Nierówność jest ścisła, ponieważ punkty

Przykład.

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

W wyniku ich wyrównania uzyskuje się funkcję

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 nachylenia regresji liniowej do osi x; 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 do tworzenia 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 lata 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.