Metoda najmanjih kvadrata (OLS) pripada području regresijske analize. Ima mnogo primjena jer omogućuje približan prikaz dane funkcije drugim jednostavnijim. LSM može biti izuzetno koristan u obradi opažanja, a aktivno se koristi za procjenu nekih veličina na temelju rezultata mjerenja drugih koji sadrže slučajne pogreške. U ovom ćete članku naučiti kako implementirati izračune najmanjih kvadrata u Excelu.

Prikaz problema na konkretnom primjeru

Pretpostavimo da postoje dva indikatora X i Y. Štoviše, Y ovisi o X. Budući da nas OLS zanima sa stajališta regresijske analize (u Excelu su njegove metode implementirane pomoću ugrađenih funkcija), trebali bismo odmah prijeći na razmatranje konkretan problem.

Dakle, neka X bude maloprodajni prostor trgovine mješovitom robom, mjeren u kvadratnim metrima, a Y godišnji promet, mjeren u milijunima rubalja.

Potrebno je napraviti prognozu koliki će promet (Y) prodavaonica imati ako bude imala ovaj ili onaj prodajni prostor. Očito je da funkcija Y = f (X) raste, jer hipermarket prodaje više robe nego štand.

Nekoliko riječi o ispravnosti početnih podataka korištenih za predviđanje

Recimo da imamo tablicu izgrađenu pomoću podataka za n trgovina.

Prema matematičkoj statistici, rezultati će biti više-manje točni ako se ispitaju podaci o najmanje 5-6 objekata. Osim toga, ne mogu se koristiti "anomalni" rezultati. Konkretno, elitni mali butik može imati promet koji je nekoliko puta veći od prometa velikih maloprodajnih mjesta klase "masmarket".

Suština metode

Podaci iz tablice mogu se prikazati na kartezijanskoj ravnini u obliku točaka M 1 (x 1, y 1), ... M n (x n, y n). Sada će se rješenje problema svesti na izbor aproksimirajuće funkcije y = f (x), koja ima graf koji prolazi što je moguće bliže točkama M 1, M 2, .. M n.

Naravno, možete koristiti polinom visok stupanj, ali ovu opciju nije samo teško implementirati, već je i jednostavno netočna, jer neće odražavati glavni trend koji treba otkriti. Najrazumnije rješenje je tražiti ravnu liniju y = ax + b, koja najbolje aproksimira eksperimentalne podatke, točnije koeficijente a i b.

Procjena točnosti

Kod svake aproksimacije, procjena njene točnosti je od posebne važnosti. Označimo s e i razliku (odstupanje) između funkcionalne i eksperimentalne vrijednosti za točku x i, tj. e i = y i - f (x i).

Očito, da biste procijenili točnost aproksimacije, možete koristiti zbroj odstupanja, tj. kada birate ravnu liniju za aproksimativni prikaz ovisnosti X o Y, trebali biste dati prednost onoj s najmanjom vrijednošću sum e i u svim točkama koje se razmatraju. No, nije sve tako jednostavno, jer će uz pozitivna odstupanja biti i negativna.

Problem se može riješiti pomoću modula odstupanja ili njihovih kvadrata. Posljednja metoda je najčešće korištena. Koristi se u mnogim područjima, uključujući regresijsku analizu (implementirana u Excelu pomoću dvije ugrađene funkcije), i odavno je dokazala svoju učinkovitost.

Metoda najmanjeg kvadrata

Excel, kao što znate, ima ugrađenu funkciju AutoSum koja vam omogućuje izračunavanje vrijednosti svih vrijednosti koje se nalaze u odabranom rasponu. Dakle, ništa nas neće spriječiti da izračunamo vrijednost izraza (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

U matematičkom zapisu to izgleda ovako:

Budući da je prvotno donesena odluka da se aproksimacija koristi ravnom linijom, imamo:

Dakle, zadatak pronalaska ravne linije koja najbolje opisuje specifičnu ovisnost veličina X i Y svodi se na izračunavanje minimuma funkcije dviju varijabli:

Da biste to učinili, trebate izjednačiti parcijalne derivacije u odnosu na nove varijable a i b na nulu i riješiti primitivni sustav koji se sastoji od dvije jednadžbe s 2 nepoznanice oblika:

Nakon nekoliko jednostavnih transformacija, uključujući dijeljenje s 2 i manipulaciju zbrojevima, dobivamo:

Rješavajući ga, na primjer, pomoću Cramerove metode, dobivamo stacionarnu točku s određenim koeficijentima a * i b *. To je minimum, odnosno za predviđanje koliki će promet prodavaonica imati za određeno područje pogodna je pravac y = a * x + b *, što je regresijski model za predmetni primjer. Naravno, to vam neće omogućiti da pronađete točan rezultat, ali će vam pomoći da steknete ideju o tome hoće li se kupnja određenog područja na kredit u trgovini isplatiti.

Kako implementirati metode najmanjih kvadrata u Excelu

Excel ima funkciju za izračunavanje vrijednosti korištenjem najmanjih kvadrata. Ima sljedeći oblik: “TREND” (poznate Y vrijednosti; poznate X vrijednosti; nove X vrijednosti; konstanta). Primijenimo formulu za izračunavanje OLS-a u Excelu na našu tablicu.

Da biste to učinili, unesite znak “=” u ćeliju u kojoj bi trebao biti prikazan rezultat izračuna metodom najmanjih kvadrata u Excelu i odaberite funkciju “TREND”. U prozoru koji se otvori ispunite odgovarajuća polja, označivši:

• raspon poznatih vrijednosti za Y (u ovom slučaju podaci o trgovačkom prometu);
• raspon x 1 , …x n , tj. veličina prodajnog prostora;
• poznate i nepoznate vrijednosti x, za koje morate saznati veličinu prometa (za informacije o njihovom položaju na radnom listu, pogledajte dolje).

Osim toga, formula sadrži logičku varijablu "Const". Ako u odgovarajuće polje unesete 1, to će značiti da trebate izvršiti izračune, pod pretpostavkom da je b = 0.

Ako trebate saznati prognozu za više od jedne x vrijednosti, tada nakon unosa formule ne biste trebali pritisnuti "Enter", već morate na tipkovnici upisati kombinaciju "Shift" + "Control" + "Enter".

Neke značajke

Regresijska analiza može biti dostupna čak i lutkama. Excelovu formulu za predviđanje vrijednosti niza nepoznatih varijabli—TREND—mogu koristiti čak i oni koji nikada nisu čuli za metode najmanjih kvadrata. Dovoljno je samo znati neke od značajki njegovog rada. Posebno:

• Ako rasporedite raspon poznatih vrijednosti varijable y u jedan redak ili stupac, tada će svaki redak (stupac) s poznatim vrijednostima x program percipirati kao zasebnu varijablu.
• Ako raspon s poznatim x nije naveden u prozoru TREND, tada će ga, kada koristite funkciju u Excelu, program tretirati kao polje koje se sastoji od cijelih brojeva, čiji broj odgovara rasponu s danim vrijednostima varijabla y.
• Za izlaz niza "predviđenih" vrijednosti, izraz za izračun trenda mora se unijeti kao formula polja.
• Ako nove vrijednosti x nisu navedene, tada ih funkcija TREND smatra jednakima poznatima. Ako nisu navedeni, tada se niz 1 uzima kao argument; 2; 3; 4;…, što je razmjerno rasponu s već navedenim parametrima y.
• Raspon koji sadrži nove x vrijednosti mora imati iste ili više redaka ili stupaca kao raspon koji sadrži dane y vrijednosti. Drugim riječima, mora biti proporcionalan nezavisnim varijablama.
• Niz s poznatim vrijednostima x može sadržavati više varijabli. Međutim, ako govorimo samo o jednom, tada je potrebno da rasponi sa zadanim vrijednostima x i y budu proporcionalni. U slučaju više varijabli, potrebno je da raspon sa zadanim vrijednostima y stane u jedan stupac ili jedan red.

PREDICTION funkcija

Regresijska analiza u Excelu implementirana je pomoću nekoliko funkcija. Jedan od njih se zove “PREDIKCIJA”. Slično je "TRENDU", tj. daje rezultat izračuna metodom najmanjih kvadrata. Međutim, samo za jedan X, za koji je vrijednost Y nepoznata.

Sada znate formule u Excelu za lutke koje vam omogućuju predviđanje buduće vrijednosti određenog pokazatelja prema linearnom trendu.

Metoda najmanjih kvadrata je matematički postupak za konstruiranje linearne jednadžbe koja će najtočnije odgovarati skupu od dva niza brojeva. Svrha korištenja ove metode je minimiziranje ukupne kvadratne pogreške. Excel ima alate koji vam mogu pomoći da ovu metodu primijenite na svoje izračune. Hajde da shvatimo kako se to radi.

· Korištenje metode u Excelu

o Omogućavanje dodatka “Traženje rješenja”.

o Problemski uvjeti

o Rješenje

Korištenje metode u Excelu

Metoda najmanjih kvadrata (LSM) je matematički opis ovisnosti jedne varijable o drugoj. Može se koristiti za predviđanje.

Omogućivanje dodatka Find Solution

Kako biste koristili MNC u Excelu, morate omogućiti dodatak "Pronalaženje rješenja", što je prema zadanim postavkama onemogućeno.

1. Idite na karticu "Datoteka".

2. Kliknite na naziv odjeljka "Opcije".

3. U prozoru koji se otvori odaberite pododjeljak "Dodaci".

4. U bloku "Kontrolirati", koji se nalazi na dnu prozora, postavite prekidač u položaj "Excel dodaci"(ako ima drugu vrijednost) i kliknite na gumb "Ići...".

5. Otvara se mali prozor. Stavili smo kvačicu pored parametra "Pronalaženje rješenja". Kliknite na gumb "U REDU".

Sada funkcija Pronalaženje rješenja u Excelu je aktiviran, a njegovi se alati pojavljuju na vrpci.

Lekcija: Pronalaženje rješenja u Excelu

Uvjeti problema

Opišimo korištenje LSM-a na konkretnom primjeru. Imamo dva reda brojeva x I g, čiji je redoslijed prikazan na slici ispod.

Ova se ovisnost najpreciznije može opisati funkcijom:

Pritom se zna da kada x=0 y također jednako 0 . Stoga se ova jednadžba može opisati ovisnošću y=nx.

Moramo pronaći minimalni zbroj kvadrata razlike.

Riješenje

Prijeđimo na opis izravne primjene metode.

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

2. Desno od stupca g dodaj još jedan stupac - nx. U prvu ćeliju ovog stupca upisujemo formulu za množenje koeficijenta n po ćeliji prve varijable x. U isto vrijeme vezu s poljem s koeficijentom činimo apsolutnim, jer se ta vrijednost neće promijeniti. Kliknite na gumb Unesi.

3. Pomoću markera za popunjavanje kopirajte ovu formulu na cijeli raspon tablice u donjem stupcu.

4. U zasebnoj ćeliji izračunajte zbroj razlika između kvadrata vrijednosti g I nx. Da biste to učinili, kliknite na gumb "Umetni funkciju".

5. U otvorenom "Čarobnjak za funkcije" tražeći ulaz "SUMMKVARNA". Odaberite ga i pritisnite gumb "U REDU".

6. Otvara se prozor s argumentima. U polju "Niz_x" g. U polju "Niz_y" unesite raspon ćelija stupca nx. Kako biste unijeli vrijednosti, jednostavno postavite kursor u polje i odaberite odgovarajući raspon na listu. Nakon unosa kliknite na gumb "U REDU".

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

8. Otvara se prozor s parametrima za ovaj alat. U polju “Optimizacija funkcije cilja” navedite adresu ćelije s formulom "SUMMKVARNA". U parametru "Prije" svakako postavite prekidač u položaj "Minimum". U polju "Promjena ćelija" navedite adresu s vrijednošću koeficijenta n. Kliknite na gumb "Pronađi rješenje".

9. Rješenje će biti prikazano u ćeliji koeficijenta n. Ova vrijednost će biti najmanji kvadrat funkcije. Ako rezultat zadovoljava korisnika, kliknite na gumb "U REDU" u dodatnom prozoru.

Kao što vidite, primjena metode najmanjih kvadrata prilično je složen matematički postupak. Pokazali smo to na djelu na jednostavnom primjeru, ali ima mnogo složenijih slučajeva. Međutim, Microsoft Excel alati su dizajnirani da pojednostave izračune što je više moguće.

http://multitest.semico.ru/mnk.htm

Opće odredbe

Što je manji broj u apsolutnoj vrijednosti, to je bolja odabrana ravna linija (2). Kao karakteristiku točnosti odabira pravca (2) možemo uzeti zbroj kvadrata

Minimalni uvjeti za S bit će

	(6)
	(7)

Jednadžbe (6) i (7) mogu se napisati na sljedeći način:

	(8)
	(9)

Iz jednadžbi (8) i (9) lako je pronaći a i b iz eksperimentalnih vrijednosti xi i y i. Pravac (2), definiran jednadžbama (8) i (9), naziva se pravac dobiven metodom najmanjih kvadrata (taj naziv naglašava da zbroj kvadrata S ima minimum). Jednadžbe (8) i (9) iz kojih se određuje pravac (2) nazivamo normalnim jednadžbama.

Možete navesti jednostavan i općenit način sastavljanja normalnih jednadžbi. Koristeći eksperimentalne točke (1) i jednadžbu (2), možemo napisati sustav jednadžbi za a i b

y 1 = ax 1 + b,
y 2 = ax 2 + b, ...		(10)
y n = ax n + b,

Pomnožimo lijevu i desnu stranu svake od ovih jednadžbi s koeficijentom prve nepoznanice a (tj. s x 1, x 2, ..., x n) i zbrojimo dobivene jednadžbe, što rezultira prvom normalnom jednadžbom (8) .

Pomnožimo lijevu i desnu stranu svake od ovih jednadžbi s koeficijentom druge nepoznanice b, tj. za 1 i zbrojite dobivene jednadžbe, rezultat je druga normalna jednadžba (9).

Ova metoda dobivanja normalnih jednadžbi je opća: prikladna je, na primjer, za funkciju

postoji konstantna vrijednost i mora se odrediti iz eksperimentalnih podataka (1).

Sustav jednadžbi za k može se napisati:

Odredite ravnu liniju (2) metodom najmanjih kvadrata.

Riješenje. Pronašli smo:

X i =21, y i =46,3, x i 2 =91, x i y i =179,1.

Napišemo jednadžbe (8) i (9)91a+21b=179.1,

21a+6b=46.3, odavde nalazimo
a=0,98 b=4,3.

Metoda najmanjeg kvadrata koristi se za procjenu parametara regresijske jednadžbe.

Jedna od metoda za proučavanje stohastičkih odnosa između karakteristika je regresijska analiza.
Regresijska analiza je izvođenje regresijske jednadžbe, uz pomoć koje se nalazi prosječna vrijednost slučajne varijable (atributa rezultata) ako je poznata vrijednost druge (ili drugih) varijabli (atributa faktora). Uključuje sljedeće korake:

1. odabir oblika veze (vrsta jednadžbe analitičke regresije);
2. procjena parametara jednadžbe;
3. procjena kvalitete analitičke regresijske jednadžbe.

Najčešće se linearni oblik koristi za opisivanje statističkog odnosa značajki. Usredotočenost na linearne odnose objašnjava se jasnim ekonomskim tumačenjem njegovih parametara, ograničenom varijacijom varijabli i činjenicom da se u većini slučajeva nelinearni oblici odnosa pretvaraju (logaritmom ili zamjenom varijabli) u linearni oblik za izvođenje izračuna. .
U slučaju linearnog parnog odnosa, regresijska jednadžba će imati oblik: y i =a+b·x i +u i . Parametri a i b ove jednadžbe procjenjuju se iz podataka statističkog promatranja x i y. Rezultat takve procjene je jednadžba: , gdje su , procjene parametara a i b , vrijednost rezultirajućeg atributa (varijable) dobivena iz regresijske jednadžbe (izračunata vrijednost).

Najčešće se koristi za procjenu parametara metoda najmanjih kvadrata (LSM).
Metoda najmanjih kvadrata daje najbolje (dosljedne, učinkovite i nepristrane) procjene parametara regresijske jednadžbe. Ali samo ako su zadovoljene određene pretpostavke u vezi sa slučajnim članom (u) i nezavisnom varijablom (x) (vidi OLS pretpostavke).

Problem procjene parametara jednadžbe linearnog para metodom najmanjih kvadrata je kako slijedi: dobiti takve procjene parametara , , pri kojima je zbroj kvadrata odstupanja stvarnih vrijednosti rezultantne karakteristike - y i od izračunatih vrijednosti - minimalan.
Formalno OLS kriterij može se napisati ovako: .

Klasifikacija metoda najmanjih kvadrata

1. Metoda najmanjeg kvadrata.
2. Metoda maksimalne vjerojatnosti (za normalan klasični model linearne regresije, normalnost regresijskih reziduala je postulirana).
3. Generalizirana OLS metoda najmanjih kvadrata koristi se u slučaju autokorelacije pogrešaka iu slučaju heteroskedastičnosti.
4. Metoda ponderiranih najmanjih kvadrata (poseban slučaj OLS-a s heteroskedastičkim rezidualima).

Ilustrirajmo poantu klasična metoda najmanjih kvadrata grafički. Da bismo to učinili, konstruirat ćemo dijagram raspršenja na temelju podataka opažanja (x i, y i, i=1;n) u pravokutnom koordinatnom sustavu (takav dijagram raspršenja naziva se korelacijsko polje). Pokušajmo odabrati ravnu liniju koja je najbliža točkama korelacijskog polja. Prema metodi najmanjih kvadrata pravac je odabran tako da je zbroj kvadrata okomitih udaljenosti između točaka korelacijskog polja i ovog pravca minimalan.

Matematička oznaka za ovaj problem: .
Vrijednosti y i i x i =1...n su nam poznate; to su podaci promatranja. U S funkciji predstavljaju konstante. Varijable u ovoj funkciji su tražene procjene parametara - , . Da bismo pronašli minimum funkcije dviju varijabli, potrebno je izračunati parcijalne derivacije te funkcije za svaki od parametara i izjednačiti ih s nulom, tj. .
Kao rezultat toga dobivamo sustav od 2 normalne linearne jednadžbe:
Rješavajući ovaj sustav, nalazimo potrebne procjene parametara:

Ispravnost izračuna parametara regresijske jednadžbe može se provjeriti usporedbom iznosa (može doći do odstupanja zbog zaokruživanja izračuna).
Da biste izračunali procjene parametara, možete napraviti tablicu 1.
Predznak koeficijenta regresije b označava smjer odnosa (ako je b >0, odnos je direktan, ako je b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formalno, vrijednost parametra a je prosječna vrijednost y s x jednakim nuli. Ako atribut-faktor nema i ne može imati nultu vrijednost, tada gornja interpretacija parametra a nema smisla.

Procjena bliskosti odnosa između karakteristika provedeno korištenjem koeficijenta korelacije linearnog para - r x,y. Može se izračunati pomoću formule: . Osim toga, koeficijent korelacije linearnog para može se odrediti pomoću koeficijenta regresije b: .
Raspon prihvatljivih vrijednosti koeficijenta korelacije linearnog para je od –1 do +1. Predznak koeficijenta korelacije označava smjer odnosa. Ako je r x, y >0, tada je veza izravna; ako je r x, y<0, то связь обратная.
Ako je taj koeficijent blizu jedinice po veličini, tada se odnos između karakteristika može tumačiti kao prilično blizak linearan. Ako je njegov modul jednak jedan ê r x , y ê =1, tada je odnos između karakteristika funkcionalno linearan. Ako su značajke x i y linearno neovisne, tada je r x,y blizu 0.
Za izračun r x,y, također možete koristiti tablicu 1.

Za procjenu kvalitete dobivene regresijske jednadžbe, izračunajte teorijski koeficijent determinacije - R 2 yx:

,
gdje je d 2 varijanca y objašnjena regresijskom jednadžbom;
e 2 - rezidualna (neobjašnjena regresijskom jednadžbom) varijanca y;
s 2 y - ukupna (ukupna) varijanca y.
Koeficijent determinacije karakterizira udio varijacije (disperzije) rezultantnog atributa y objašnjenog regresijom (i, posljedično, faktora x) u ukupnoj varijaciji (disperziji) y. Koeficijent determinacije R 2 yx ima vrijednosti od 0 do 1. Prema tome, vrijednost 1-R 2 yx karakterizira udio varijance y uzrokovan utjecajem drugih čimbenika koji nisu uzeti u obzir u modelu i greškama specifikacije.
S uparenom linearnom regresijom, R 2 yx =r 2 yx.

Metoda najmanjih kvadrata (LSM)

Sustav od m linearnih jednadžbi s n nepoznanica ima oblik:

Moguća su tri slučaja: m n. Slučaj kada je m=n razmatran je u prethodnim paragrafima. Kada je m

Ako je m>n i sustav je konzistentan, tada matrica A ima najmanje m - n linearno ovisnih redaka. Ovdje se rješenje može dobiti odabirom n bilo kojih linearno neovisnih jednadžbi (ako postoje) i primjenom formule X = A -1 CV, odnosno svođenjem problema na prethodno riješen. U tom slučaju, dobiveno rješenje uvijek će zadovoljiti preostale m - n jednadžbe.

Međutim, kada se koristi računalo, praktičnije je koristiti općenitiji pristup – metodu najmanjih kvadrata.

Algebarska metoda najmanjih kvadrata

Algebarska metoda najmanjih kvadrata je metoda za rješavanje sustava linearnih jednadžbi

minimiziranjem euklidske norme

Sjekira? b? >inf. (1.2)

Analiza eksperimentalnih podataka

Razmotrimo neki eksperiment tijekom kojeg u trenucima vremena

Na primjer, mjeri se temperatura Q(t). Neka su rezultati mjerenja određeni nizom

Pretpostavimo da su eksperimentalni uvjeti takvi da se mjerenja provode s poznatom greškom. U tim se slučajevima zakon promjene temperature Q(t) traži pomoću određenog polinoma

P(t) = + + + ... +,

određivanje nepoznatih koeficijenata, ..., iz razmatranja da je vrijednost E(, ...,), definirana jednakošću

Gaussova algebarska exel aproksimacija

uzeo minimalnu vrijednost. Budući da je zbroj kvadrata minimiziran, ova se metoda naziva aproksimacija podataka metodom najmanjih kvadrata.

Ako P(t) zamijenimo njegovim izrazom, dobivamo

Postavimo zadatak definiranja niza tako da vrijednost bude minimalna, tj. Definirajmo niz metodom najmanjih kvadrata. Da bismo to učinili, parcijalne derivacije izjednačavamo s nulom:

Ako unesete m × n matricu A = (), i = 1, 2..., m; j = 1, 2, ..., n, gdje je

I = 1, 2..., m; j = 1, 2, ..., n,

tada će zapisana jednakost poprimiti oblik

Prepišimo napisanu jednakost u smislu operacija s matricama. Prema definiciji množenja matrice stupcem, imamo

Za transponiranu matricu sličan odnos izgleda ovako

Uvodimo oznaku: označit ćemo i-tu komponentu vektora Ax Sukladno napisanim matričnim jednakostima imat ćemo

U matričnom obliku ova se jednakost može prepisati kao

A T x=AT B (1.3)

Ovdje je A pravokutna m×n matrica. Štoviše, u problemima aproksimacije podataka u pravilu je m > n. Jednadžba (1.3) naziva se normalnom jednadžbom.

Bilo je moguće od samog početka, korištenjem euklidske norme vektora, napisati problem u obliku ekvivalentne matrice:

Naš cilj je minimizirati ovu funkciju u x. Da bi se dosegnuo minimum u točki rješenja, prve derivacije u odnosu na x u toj točki moraju biti jednake nuli. Izvodnice ove funkcije su

2A T B + 2A T Ax

pa stoga rješenje mora zadovoljiti sustav linearnih jednadžbi

(AT A)x = (AT B).

Ove se jednadžbe nazivaju normalne jednadžbe. Ako je A m × n matrica, tada je A>A - n × n matrica, tj. Matrica normalne jednadžbe uvijek je kvadratna simetrična matrica. Štoviše, ima svojstvo pozitivne određenosti u smislu da je (A>Ax, x) = (Ax, Ax) ? 0.

Komentar. Ponekad se rješenje jednadžbe oblika (1.3) naziva rješenjem sustava Ax = B, gdje je A pravokutna m × n (m > n) matrica primjenom metode najmanjih kvadrata.

Problem najmanjih kvadrata može se grafički protumačiti kao minimiziranje okomitih udaljenosti od podatkovnih točaka do krivulje modela (vidi sliku 1.1). Ova se ideja temelji na pretpostavci da sve pogreške u aproksimaciji odgovaraju pogreškama u opažanjima. Ako također postoje pogreške u nezavisnim varijablama, tada bi moglo biti prikladnije minimizirati euklidsku udaljenost od podataka do modela.

MNC u Excelu

Algoritam u nastavku za implementaciju OLS-a u Excel pretpostavlja da su svi početni podaci već poznati. Obje strane matrične jednadžbe AČX=B sustava s lijeve strane pomnožimo s transponiranom matricom sustava A T:

A T OKS=A T B

Zatim pomnožimo obje strane jednadžbe s lijeve strane s matricom (AT A) -1. Ako ta matrica postoji, onda je sustav definiran. S obzirom na to

(AT A) -1 *(AT A)=E, dobivamo

X=(AT A) -1 A T B.

Rezultirajuća matrična jednadžba rješenje je sustava od m linearnih jednadžbi s n nepoznanica za m>n.

Razmotrimo primjenu gornjeg algoritma na konkretnom primjeru.

Primjer. Neka je potrebno riješiti sustav

U Excelu list rješenja u načinu prikaza formule za ovaj problem izgleda ovako:

Rezultati izračuna:

Traženi vektor X nalazi se u rasponu E11:E12.

Pri rješavanju zadanog sustava linearnih jednadžbi korištene su sljedeće funkcije:

1. MOBR - vraća inverznu matricu za matricu pohranjenu u nizu.

Sintaksa: MOBR(niz).

Niz je numerički niz s jednakim brojem redaka i stupaca.

2. MULTIPULT - vraća umnožak matrica (matrice su pohranjene u nizovima). Rezultat je niz s istim brojem redaka kao niz1 i istim brojem stupaca kao niz2.

Sintaksa: MULTIPLE(niz1,niz2).

Niz1, niz2 su višestruki nizovi.

Nakon unosa funkcije u gornju lijevu ćeliju raspona niza, odaberite niz, počevši od ćelije koja sadrži formulu, pritisnite F2, a zatim pritisnite CTRL+SHIFT+ENTER.

3. TRANSPORT - pretvara vertikalni skup ćelija u horizontalni, ili obrnuto. Kao rezultat korištenja ove funkcije pojavljuje se niz s brojem redaka jednakim broju stupaca izvornog niza i brojem stupaca jednakim broju redaka početnog niza.

4.1. Korištenje ugrađenih funkcija

Kalkulacija koeficijenti regresije provodi pomoću funkcije

LINEST(Vrijednosti_y; x-vrijednosti; Konst; statistika),

Vrijednosti_y- niz y vrijednosti,

x-vrijednosti- izborni niz vrijednosti x, ako niz x izostavljen, pretpostavlja se da je ovo niz (1;2;3;...) iste veličine kao Vrijednosti_y,

Konst- Booleova vrijednost koja pokazuje je li konstanta potrebna b bila jednaka 0. Ako Konst ima značenje PRAVI ili izostavljeno, dakle b izračunava se na uobičajeni način. Ako argument Konst je onda FALSE b pretpostavlja se da je 0 i vrijednosti a odabrani su tako da je relacija ispunjena y=os.

Statistika je Booleova vrijednost koja pokazuje je li potrebna dodatna regresijska statistika za vraćanje. Ako argument Statistika ima značenje PRAVI, zatim funkcija LINEST vraća dodatne regresijske statistike. Ako argument Statistika ima značenje LAŽ ili izostavljena, zatim funkcija LINEST vraća samo koeficijent a i konstantan b.

Mora se zapamtiti da rezultat funkcija LINEST() je skup vrijednosti – niz.

Za izračun koeficijent korelacije koristi se funkcija

CORREL(Niz1;Niz2),

vraćanje vrijednosti koeficijenta korelacije, gdje Niz1- niz vrijednosti g, Niz2- niz vrijednosti x. Niz1 I Niz2 moraju biti iste veličine.

PRIMJER 1. Ovisnost g(x) prikazan je u tablici. Izgraditi regresijska linija i izračunati koeficijent korelacije.

g		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Unesite tablicu vrijednosti u MS Excel list i izgradite raspršeni dijagram. Radni list će imati oblik prikazan na sl. 2.

Kako bi se izračunale vrijednosti koeficijenata regresije A I b odaberite ćelije A7:B7, Idemo u čarobnjak za funkcije iu kategoriju Statistički odaberite funkciju LINEST. Ispunimo dijaloški okvir koji se pojavi kao što je prikazano na sl. 3 i pritisnite u redu.

Kao rezultat toga, izračunata vrijednost pojavit će se samo u ćeliji A6(slika 4). Da bi se vrijednost pojavila u ćeliji B6 morate ući u način uređivanja (tipka F2), a zatim pritisnite kombinaciju tipki CTRL+SHIFT+ENTER.

Za izračunavanje vrijednosti koeficijenta korelacije u ćeliji C6 uvedena je sljedeća formula:

C7=CORREL(B3:J3;B2:J2).

Poznavajući koeficijente regresije A I b izračunajmo vrijednosti funkcije g=sjekira+b za dato x. Da bismo to učinili, uvodimo formulu

B5=$A$7*B2+$B$7

i kopirajte ga u raspon C5:J5(slika 5).

Nacrtajmo regresijsku liniju na dijagramu. Odaberite eksperimentalne točke na grafikonu, kliknite desnom tipkom miša i odaberite naredbu Početni podaci. U dijaloškom okviru koji se pojavi (slika 5) odaberite karticu Red i kliknite na gumb Dodati. Ispunimo polja za unos kao što je prikazano na sl. 6 i pritisnite gumb u redu. Grafikonu eksperimentalnih podataka bit će dodan regresijski pravac. Prema zadanim postavkama, njegov će se grafikon nacrtati kao točke koje nisu povezane glatkim linijama.

Riža. 6

Da biste promijenili izgled regresijske linije, izvršite sljedeće korake. Kliknite desnom tipkom miša na točke koje prikazuju linijski grafikon i odaberite naredbu Vrsta grafikona i postavite vrstu dijagrama raspršenosti, kao što je prikazano na sl. 7.

Vrsta linije, boja i debljina mogu se promijeniti na sljedeći način. Odaberite liniju na dijagramu, kliknite desnom tipkom miša i odaberite naredbu u kontekstnom izborniku Format serije podataka... Zatim napravite postavke, na primjer, kao što je prikazano na sl. 8.

Kao rezultat svih transformacija dobivamo graf eksperimentalnih podataka i regresijsku liniju u jednom grafičkom području (slika 9).

4.2. Korištenje linije trenda.

Konstrukcija različitih aproksimirajućih ovisnosti u MS Excelu implementirana je kao svojstvo grafikona - linija trenda.

PRIMJER 2. Kao rezultat pokusa utvrđena je određena tablična ovisnost.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Odaberite i konstruirajte aproksimirajuću ovisnost. Konstruirati grafove tabličnih i odabranih analitičkih ovisnosti.

Rješavanje problema može se podijeliti u sljedeće faze: unos početnih podataka, izrada dijagrama raspršenja i dodavanje linije trenda na ovaj grafikon.

Pogledajmo ovaj proces u detalje. Unesite početne podatke u radni list i iscrtajte eksperimentalne podatke. Zatim odaberite eksperimentalne točke na grafikonu, kliknite desnom tipkom miša i upotrijebite naredbu Dodati l linija trenda(slika 10).

Dijaloški okvir koji se pojavljuje omogućuje vam da izgradite aproksimirajuću ovisnost.

Prva kartica (slika 11) ovog prozora označava vrstu aproksimativne ovisnosti.

Na drugom (slika 12) određuju se parametri konstrukcije:

· naziv aproksimativne ovisnosti;

· prognoza naprijed (natrag) po n jedinice (ovaj parametar određuje za koliko jedinica naprijed (unazad) linija trenda treba biti produžena);

treba li prikazati sjecište krivulje s ravnom linijom y=konst;

· prikazati aproksimirajuću funkciju na dijagramu ili ne (mogućnost prikaza jednadžbe na dijagramu);

· hoće li vrijednost standardne devijacije biti postavljena na dijagram ili ne (mogućnost postavljanja vrijednosti pouzdanosti aproksimacije na dijagram).

Izaberimo polinom drugog stupnja kao aproksimirajuću ovisnost (slika 11) i prikažimo jednadžbu koja opisuje taj polinom na grafu (slika 12). Dobiveni dijagram prikazan je na sl. 13.

Slično koristeći trend linije možete odabrati parametre takvih ovisnosti kao što su

linearni g=a∙x+b,

logaritamski g=a∙ln(x)+b,

· eksponencijalni g=a∙e b,

· staložen g=a∙x b,

polinom g=a∙x 2 +b∙x+c, g=a∙x 3 +b∙x 2 +c∙x+d i tako dalje, do polinoma 6. stupnja uključivo,

· linearna filtracija.

4.3. Korištenje bloka za rješavanje

Od velikog je interesa implementacija u MS Excel odabira parametara metodom najmanjih kvadrata pomoću bloka rješavača. Ova tehnika omogućuje odabir parametara funkcije bilo koje vrste. Razmotrimo ovu mogućnost koristeći sljedeći problem kao primjer.

PRIMJER 3. Kao rezultat pokusa dobivena je ovisnost z(t) prikazana u tablici

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Odaberite koeficijente ovisnosti Z(t)=At 4 +Bt 3 +Ct 2 +Dt+K metoda najmanjih kvadrata.

Ovaj problem je ekvivalentan problemu pronalaženja minimuma funkcije pet varijabli

Razmotrimo proces rješavanja optimizacijskog problema (slika 14).

Neka vrijednosti A, U, S, D I DO pohranjeni u stanicama A7:E7. Izračunajmo teorijske vrijednosti funkcije Z(t)=Na 4 +Bt 3 +Ct 2 +Dt+K za dato t(B2:J2). Da biste to učinili, u ćeliji B4 unesite vrijednost funkcije u prvu točku (ćelija B2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Kopirajmo ovu formulu u raspon C4:J4 te dobiti očekivanu vrijednost funkcije u točkama čije su apscise pohranjene u ćelijama B2:J2.

U ćeliju B5 Uvedimo formulu koja izračunava kvadrat razlike između eksperimentalne i izračunate točke:

B5=(B4-B3)^2,

i kopirajte ga u raspon C5:J5. U ćeliji F7 pohranit ćemo ukupnu kvadratnu grešku (10). Da biste to učinili, unesite formulu:

F7 = SUM(B5:J5).

Upotrijebimo naredbu Usluga®Traži rješenje i riješiti problem optimizacije bez ograničenja. U skladu s tim ispunimo polja za unos u dijaloškom okviru prikazanom na sl. 14 i pritisnite tipku Izvršiti. Ako se pronađe rješenje, prozor prikazan na Sl. 15.

Rezultat bloka odluke bit će ispisan u ćelije A7:E7vrijednosti parametara funkcije Z(t)=Na 4 +Bt 3 +Ct 2 +Dt+K. U stanicama B4:J4 dobivamo očekivana vrijednost funkcije na polazištima. U ćeliji F7 bit će pohranjeni ukupna kvadratna pogreška.

Možete prikazati eksperimentalne točke i prilagođenu liniju u jednom grafičkom području odabirom raspona B2:J4, nazovi Čarobnjak za grafikone a zatim formatirajte izgled primljene grafove.

Riža. 17 prikazuje MS Excel radnu tablicu nakon izvršenih izračuna.

5. LITERATURA

1. Alekseev E.R., Chesnokova O.V., Rješavanje problema računalne matematike u paketima Mathcad12, MATLAB7, Maple9. – NT Press, 2006.–596 str. :il. – (Tutorial)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudčenko, Scilab, rješavanje inženjerskih i matematičkih problema. –M., BINOM, 2008.–260 str.

3. Berezin I.S., Židkov N.P., Metode proračuna – M.: Nauka, 1966. – 632 str.

4. Garnaev A.Yu., Korištenje MS EXCEL i VBA u ekonomiji i financijama. – Sankt Peterburg: BHV - Petersburg, 1999.–332 str.

5. Demidovich B.P., Maron I.A., Shuvalova V.Z., Numeričke metode analize – M.: Nauka, 1967. – 368 str.

6. Korn G., Korn T., Matematički priručnik za znanstvenike i inženjere – M., 1970., 720 str.

7. Alekseev E.R., Chesnokova O.V. Upute za izvođenje laboratorijskih radova u MS EXCEL-u. Za studente svih specijalnosti. Donjeck, DonNTU, 2004. 112 str.