Uporaba metode najmanjših kvadratov v Excelu

Metoda najmanjših kvadratov je matematični postopek za izgradnjo linearne enačbe, ki bi najbolj natančno ustrezala nizu dveh nizov števil. Namen te metode je zmanjšati skupno kvadratno napako. V Excelu so orodja, s katerimi lahko uporabite to metodo v svojih izračunih. Poglejmo, kako je to storjeno.

Uporaba metode v Excelu

Metoda najmanjših kvadratov (OLS) je matematični opis odvisnosti ene spremenljivke od druge. Lahko se uporablja pri napovedovanju.

Omogočanje rešitve za iskanje dodatka

Če želite uporabiti OLS v Excelu, morate omogočiti dodatek »Find Solution«, ki je privzeto onemogočen.

1. Pojdite na kartico »Datoteka «.
2. Kliknemo na ime razdelka »Parametri« .
3. V oknu, ki se odpre, prenehamo izbrati pododdelek »Dodatki« .
4. V bloku "Upravljanje" , ki se nahaja na dnu okna, nastavite stikalo na položaj "Excel Add-ins" (če ima drugačno vrednost) in kliknite gumb "Pojdi ..." .
5. Odpre se majhno okno. Vstavili smo kljukico o parametru "Iskanje rešitev" . Kliknite gumb "V redu" .

Zdaj je funkcija Find Solution in Excel omogočena in njena orodja se pojavijo na traku.

Lekcija: Iskanje rešitev v Excelu

Pogoji za nalogo

Opišite uporabo OLS na konkreten primer. Imamo dve vrstici številk x in y , katerih zaporedje je prikazano na spodnji sliki.

Funkcija lahko najbolje opiše to odvisnost:

y=a+nx

V tem primeru je znano, da je pri x = 0 y tudi 0 . Zato lahko to enačbo opišemo z odvisnostjo y = nx .

Najti moramo najmanjšo vsoto kvadratov razlike.

Rešitev

Zdaj opiramo neposredno uporabo metode.

1. Levo od prve vrednosti x položimo številko 1 . To je približna vrednost prve vrednosti koeficienta n .
2. Na desni strani stolpca y dodajte še en stolpec - nx . V prvi celici tega stolpca napišemo formulo za množenje koeficienta n s celico prve spremenljivke x . Hkrati se naredi povezava na polje s koeficientom absolutno , ker se ta vrednost ne bo spremenila. Kliknite gumb Enter .
3. Uporaba oznaka za polnjenje , kopirajte to formulo na celotno območje tabele v spodnjem stolpcu.
4. V ločeni celici izračunajte vsoto razlike kvadratov vrednosti y in nx . Če želite to narediti, kliknite gumb »Vstavi funkcijo« .
5. V odprtem "Čarovniku funkcij" iščemo vnos "SUMMKVRAZN" . Izberite in kliknite gumb »V redu« .
6. Odpre okno argumentov. V polju "Array_x" vnesemo obseg celic stolpca y . V polju "Array_y" vnesemo obseg celic stolpca nx . Če želite vnesti vrednosti, preprosto postavite kurzor v polje in izberite ustrezno območje na listu. Po vnosu kliknite na gumb »V redu« .
7. Pojdite na kartico »Podatki «. Na traku v orodjarni "Analiza" kliknite gumb "Find Solution" .
8. Odpre se okno parametrov za to orodje. V polju »Optimizirajte ciljno funkcijo« podamo naslov celice s formulo »SUMMKVRAZN« . V parametru "To" moramo nastaviti stikalo na položaj "Minimum" . V polju "Spreminjanje celic" določimo naslov z vrednostjo koeficienta n . Kliknite gumb »Poišči rešitev« .
9. Rešitev bo prikazana v celici koeficienta n . Ta vrednost bo najmanjša kvadratka funkcije. Če rezultat zadošča uporabniku, v dodatnem oknu kliknite gumb »V redu« .

Kot smo videli, je uporaba metode najmanjših kvadratov precej zapleten matematični postopek. Na preprost način smo to pokazali v akciji in obstaja veliko bolj zapletenih primerov. Vendar je Microsoft Excel orodje namenjeno poenostavitvi izračuna v največji možni meri.