Primjena metode najmanje kvadrata u Excelu

Metoda najmanje kvadrata matematički je postupak za izgradnju linearne jednadžbe koja bi najviše odgovarala skupu od dva niza brojeva. Svrha primjene ove metode je minimizirati ukupnu kvadratnu grešku. Excel ima alate pomoću kojih ovu metodu možete koristiti u proračunima. Da vidimo kako se to radi.

Korištenje metode u Excelu

Metoda najmanje kvadrata (najmanje kvadrata) je matematički opis ovisnosti jedne varijable o drugoj. Može se koristiti u prognoziranju.

Omogućavanje dodatka za pretraživanje rješenja

Da biste koristili OLS u Excelu, morate omogućiti dodatak „Pronalaženje rešenja“koja je podrazumevano onemogućena.

1. Idite na karticu Datoteka.



2. Kliknite na naziv odjeljka "Opcije".



3. U prozoru koji se otvori zaustavite odabir u pododjeljku "Dodaci".



4. U bloku "Uprava"koji se nalazi na dnu prozora, postavite prekidač na Excel dodaci (ako je u njemu postavljena druga vrijednost) i kliknite na gumb "Idi ...".



5. Otvara se mali prozor. Stavite kvačicu u njega u blizini parametra "Pronalaženje rešenja". Kliknite na dugme "OK".

Funkcija sada Potražite rješenje aktivirana u Excelu, a njegovi se alati pojavili na vrpci.

Lekcija: Pronalaženje rješenja u Excelu

Uvjeti zadatka

Opisali smo upotrebu OLS-a na konkretnom primjeru. Imamo dva reda brojeva x i yčiji je slijed prikazan na slici ispod.

Funkcija može najzastupljenije opisati ovu ovisnost:

y = a + nx

Štaviše, poznato je da sa x = 0 y takođe ravnopravno 0. Stoga se ova jednadžba može opisati zavisnošću y = nx.

Moramo pronaći najmanju sumu kvadrata razlike.

Rješenje

Okrenimo se opisu direktne primjene metode.

1. Lijevo od prve vrijednosti x stavi broj 1. To će biti približna vrijednost prve vrijednosti koeficijenta n.



2. Desno od kolone y dodaj još jedan stupac - nx. U prvu ćeliju ovog stupca pišemo formulu množenja koeficijenta n po ćeliji prve promenljive x. Istovremeno, vezujemo na polje s koeficijentom apsolutnim, jer se ta vrijednost neće mijenjati. Kliknite na dugme Unesite.



3. Koristite marker za unos, kopirajte ovu formulu u cijeli raspon tablice u stupcu ispod.



4. U zasebnoj ćeliji izračunavamo zbroj razlika kvadrata vrijednosti y i nx. Da biste to učinili, kliknite na dugme "Umetanje funkcije".



5. Na otvorenom "Čarobnjak za funkcije" tražeći zapis SUMMKVRAZN. Odaberite ga i kliknite na gumb. "OK".



6. Otvara se prozor argumenta. U polju "Array_x" unesite raspon ćelija stupca y. U polju Array_y unesite raspon ćelija stupca nx. Da bismo unijeli vrijednosti, jednostavno postavimo kursor u polje i odaberemo odgovarajući raspon na listu. Nakon unosa pritisnite gumb "OK".



7. Idite na karticu "Podaci". Na vrpci u kutiji s alatima "Analiza" kliknite na gumb "Pronalaženje rešenja".



8. Otvara se prozor s opcijama za ovaj alat. U polju "Optimiziranje objektivne funkcije" odredite adresu ćelije formulom SUMMKVRAZN. U parametru "Do" obavezno postavite prekidač u položaj "Minimum". U polju "Promjena ćelija" odredite adresu s vrijednosti koeficijenta n. Kliknite na dugme "Pronađite rješenje".



9. Rešenje će biti prikazano u ćeliji koeficijenta n. Upravo će ta vrijednost biti najmanji kvadrat funkcije. Ako rezultat zadovolji korisnika, kliknite na gumb "OK" u dodatnom prozoru.

Kao što vidite, primjena metode najmanje kvadrata prilično je kompliciran matematički postupak. Prikazali smo to na djelu najjednostavnijim primjerom, a ima mnogo složenijih slučajeva. Međutim, Microsoft Excel alatni alat dizajniran je da pojednostavi izračune u najvećoj mogućoj mjeri.