Excel

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. Pojdite na kartico Datoteka v programu Microsoft Excel

  2. Kliknemo na ime razdelka »Parametri« .

    3. Pojdite na možnosti v programu Microsoft Excel

  3. V oknu, ki se odpre, prenehamo izbrati pododdelek »Dodatki« .

    4. Pojdite na podskupni dodatek v programu Microsoft Excel

  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. Pojdite na dodatke Excel v programu Microsoft Excel

  5. Odpre se majhno okno. Vstavili smo kljukico o parametru "Iskanje rešitev" . Kliknite gumb "V redu" .

Omogočanje iskalnih rešitev v programu Microsoft Excel

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.

Spremenljivke v programu Microsoft Excel

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. Vrednost koeficienta n v Microsoft Excelu

  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. Vrednost nx v Microsoft Excelu

  3. Uporaba oznaka za polnjenje , kopirajte to formulo na celotno območje tabele v spodnjem stolpcu.

    4. Kopiranje formule v Microsoft Excel

  4. V ločeni celici izračunajte vsoto razlike kvadratov vrednosti y in nx . Če želite to narediti, kliknite gumb »Vstavi funkcijo« .

    5. Odprite čarovnika za funkcije v programu Microsoft Excel

  5. V odprtem "Čarovniku funkcij" iščemo vnos "SUMMKVRAZN" . Izberite in kliknite gumb »V redu« .

    6. Čarovnik za funkcije v programu Microsoft Excel

  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. Vnos argumentov funkcij v programu Microsoft Excel

  7. Pojdite na kartico »Podatki «. Na traku v orodjarni "Analiza" kliknite gumb "Find Solution" .

    8. Pojdite k iskanju rešitve v programu Microsoft Excel

  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. Iskanje najmanjše kvadratne rešitve v programu Microsoft Excel

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

Potrdite rezultat v programu Microsoft Excel

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.