Metoda najmanjih kvadrata je matematički postupak za konstrukciju linearne jednadžbe koja bi najbliže odgovarala skupu od dva reda brojeva. Svrha ove metode je minimiziranje ukupne kvadratne pogreške. Excel ima alate za upotrebu ove metode za izračunavanje. Da vidimo kako se to radi.
Korišćenje metode u Excelu
Metoda najmanjih kvadrata (OLS) je matematički opis zavisnosti jedne varijable od druge. Može se koristiti u prognoziranju.
Omogućavanje dodatka "Solution Finder"
Da biste koristili OLS u Excelu, morate omogućiti programski dodatak "Traži rješenje"koja je podrazumevano onemogućena.
- Idite na karticu "Datoteka".
- Kliknite na ime sekcije "Opcije".
- U prozoru koji se otvori, zaustavite izbor na podsekciji Dodaci.
- U bloku "Upravljanje"koji se nalazi u donjem delu prozora, postavite prekidač na položaj Excel-ovi dodaci (ako je u njemu postavljena druga vrijednost) i pritisnite tipku "Idi ...".
- Otvara se mali prozor. Stavili smo kvačicu oko parametra "Pronalaženje rješenja". Pritisnemo dugme "OK".
Sada radi Pronalaženje rješenja Excel je aktiviran i njegovi alati se pojavljuju na traci.
Lekcija: Potražite rješenje u Excelu
Uslovi problema
Opisujemo upotrebu MNC-a sa specifičnim primjerom. Imamo dva reda brojeva x i y, čiji je redosled prikazan na slici ispod.
Najtačnije, ova zavisnost može opisati funkciju:
y = a + nx
Istovremeno se zna da je sa x = 0 y jednako 0. Stoga se ova jednačina može opisati zavisnošću y = nx.
Moramo pronaći minimalnu sumu kvadrata razlike.
Rešenje
Pređimo na opis direktne primjene metode.
- Lijevo od prve vrijednosti x stavi broj 1. To će biti približna vrijednost prve vrijednosti koeficijenta. n.
- Desno od kolone y dodaj još jednu kolonu - nx. U prvoj ćeliji ove kolone napišite formulu za množenje koeficijenta n na prvoj varijabilnoj ćeliji x. Istovremeno, upućivanje na polje sa koeficijentom apsolutnim, jer se ova vrijednost neće mijenjati. Kliknite na dugme Enter.
- Koristeći marker za popunjavanje, kopirajte ovu formulu u cijeli raspon tablice u stupcu ispod.
- U zasebnoj ćeliji izračunavamo sumu razlika kvadrata vrijednosti. y i nx. Da biste to uradili, kliknite na dugme "Umetni funkciju".
- U otvorenom "Master of Functions" u potrazi za rekordom "SUMMKVRAZN". Izaberite je i kliknite na dugme. "OK".
- Otvara se prozor argumenta. Na terenu "Array_x" unesite opseg ćelija kolone y. Na terenu "Array_y" unesite opseg ćelija kolone nx. Da biste unijeli vrijednosti, jednostavno postavite kursor u polje i odaberite odgovarajući raspon na listu. Nakon unosa kliknite na dugme "OK".
- Idite na karticu "Data". Na traku u bloku alata "Analiza" pritisnite dugme "Pronalaženje rješenja".
- Otvara se prozor parametara ovog alata. Na terenu "Optimiziraj ciljnu funkciju" navedite adresu ćelije pomoću formule "SUMMKVRAZN". U parametru "Do Obavezno postavite prekidač u položaj "Minimum". Na terenu "Promena ćelija" navedemo adresu s vrijednošću koeficijenta n. Pritisnemo dugme "Pronađite rješenje".
- Rješenje će biti prikazano u ćeliji koeficijenta. n. Ova vrijednost će biti najmanji kvadrat funkcije. Ako rezultat zadovoljava korisnika, kliknite na gumb "OK" u dodatnom prozoru.
Kao što vidimo, primjena metode najmanjih kvadrata je prilično složena matematička procedura. Pokazali smo ga u akciji sa najjednostavnijim primerom, a postoje mnogo složeniji slučajevi. Međutim, Microsoft Excel alat je dizajniran da pojednostavi izračune koliko god je to moguće.
