Metoda najmanjših kvadratov (OLS) spada na področje regresijske analize. Ima veliko aplikacij, saj omogoča približno predstavitev dane funkcije z drugimi preprostejšimi. LSM je lahko izjemno koristen pri obdelavi opazovanj in se aktivno uporablja za ocenjevanje nekaterih količin na podlagi rezultatov meritev drugih, ki vsebujejo naključne napake. V tem članku se boste naučili, kako izvajati izračune najmanjših kvadratov v Excelu.

Izjava problema na konkretnem primeru

Recimo, da obstajata dva indikatorja X in Y. Poleg tega je Y odvisen od X. Ker nas OLS zanima z vidika regresijske analize (v Excelu so njegove metode implementirane z vgrajenimi funkcijami), bi morali takoj preiti na obravnavo specifičen problem.

Naj bo torej X maloprodajni prostor trgovine z živili, merjen v kvadratnih metrih, Y pa letni promet, merjen v milijonih rubljev.

Narediti je treba napoved, kakšen promet (Y) bo imela trgovina, če bo imela ta ali oni maloprodajni prostor. Očitno je, da funkcija Y = f (X) narašča, saj hipermarket proda več blaga kot stojnica.

Nekaj ​​besed o pravilnosti začetnih podatkov, uporabljenih za napoved

Recimo, da imamo tabelo, zgrajeno s podatki za n trgovin.

Glede na matematično statistiko bodo rezultati bolj ali manj pravilni, če se pregledajo podatki o vsaj 5-6 objektih. Poleg tega ni mogoče uporabiti "nenormalnih" rezultatov. Zlasti elitni mali butik ima lahko promet, ki je nekajkrat večji od prometa velikih maloprodajnih mest razreda "masmarket".

Bistvo metode

Podatke tabele lahko prikažemo na kartezični ravnini v obliki točk M 1 (x 1, y 1), ... M n (x n, y n). Zdaj se bo rešitev problema zmanjšala na izbiro aproksimativne funkcije y = f (x), ki ima graf, ki poteka čim bližje točkam M 1, M 2, .. M n.

Seveda lahko uporabite polinom visoka stopnja, vendar ta možnost ni samo težko izvedljiva, ampak tudi preprosto napačna, saj ne bo odražala glavnega trenda, ki ga je treba zaznati. Najbolj smiselna rešitev je iskanje premice y = ax + b, ki najbolje približa eksperimentalne podatke oziroma natančneje koeficienta a in b.

Ocena točnosti

Pri vsakem približku je še posebej pomembna ocena njegove natančnosti. Označimo z e i razliko (odklon) med funkcionalno in eksperimentalno vrednostjo za točko x i, to je e i = y i - f (x i).

Očitno je, da lahko za oceno točnosti približka uporabite vsoto odstopanj, tj. pri izbiri ravne črte za približno predstavitev odvisnosti X od Y morate dati prednost tisti z najmanjšo vrednostjo vsota e i na vseh obravnavanih točkah. Vendar ni vse tako preprosto, saj bodo poleg pozitivnih odstopanj tudi negativna.

Zadevo je mogoče rešiti z moduli odstopanj ali njihovimi kvadrati. Zadnja metoda je najpogosteje uporabljena. Uporablja se na številnih področjih, vključno z regresijsko analizo (implementirana v Excelu z uporabo dveh vgrajenih funkcij), in je že dolgo dokazala svojo učinkovitost.

Metoda najmanjših kvadratov

Excel, kot veste, ima vgrajeno funkcijo AutoSum, ki vam omogoča izračun vrednosti vseh vrednosti, ki se nahajajo v izbranem obsegu. Tako nas nič ne ovira pri izračunavanju vrednosti izraza (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

V matematičnem zapisu je to videti takole:

Ker je bila sprva sprejeta odločitev za približek z uporabo ravne črte, imamo:

Tako se naloga iskanja ravne črte, ki najbolje opisuje določeno odvisnost količin X in Y, zmanjša na izračun minimuma funkcije dveh spremenljivk:

Če želite to narediti, morate parcialne odvode glede na novi spremenljivki a in b enačiti na nič in rešiti primitivni sistem, sestavljen iz dveh enačb z 2 neznankama oblike:

Po nekaj preprostih transformacijah, vključno z deljenjem z 2 in manipulacijo vsot, dobimo:

Če jo rešimo, na primer z uporabo Cramerjeve metode, dobimo stacionarno točko z določenimi koeficienti a * in b *. To je minimum, tj. za predvidevanje, kakšen promet bo imela trgovina za določeno območje, je primerna premica y = a * x + b *, ki je regresijski model za obravnavani primer. Seveda vam ne bo omogočilo, da bi našli točen rezultat, vendar vam bo pomagalo dobiti idejo o tem, ali se bo nakup določenega območja na kredit v trgovini izplačal.

Kako implementirati metode najmanjših kvadratov v Excel

Excel ima funkcijo za izračun vrednosti z uporabo najmanjših kvadratov. Ima naslednjo obliko: »TREND« (znane vrednosti Y; znane vrednosti X; nove vrednosti X; konstanta). Uporabimo formulo za izračun OLS v Excelu v naši tabeli.

To storite tako, da v celico, v kateri naj se izpiše rezultat izračuna po metodi najmanjših kvadratov v Excelu, vnesete znak “=” in izberete funkcijo “TREND”. V oknu, ki se odpre, izpolnite ustrezna polja in označite:

• obseg znanih vrednosti za Y (v tem primeru podatki o prometu trgovine);
• obseg x 1 , …x n , to je velikost prodajnega prostora;
• znane in neznane vrednosti x, za katere morate ugotoviti velikost prometa (za informacije o njihovi lokaciji na delovnem listu glejte spodaj).

Poleg tega formula vsebuje logično spremenljivko »Const«. Če v ustrezno polje vpišete 1, to pomeni, da morate izvesti izračune ob predpostavki, da je b = 0.

Če morate izvedeti napoved za več kot eno vrednost x, potem po vnosu formule ne smete pritisniti "Enter", ampak morate na tipkovnici vnesti kombinacijo "Shift" + "Control" + "Enter".

Nekatere funkcije

Regresijska analiza je lahko dostopna tudi telebanom. Excelovo formulo za napovedovanje vrednosti niza neznanih spremenljivk – TREND – lahko uporabljajo tudi tisti, ki še nikoli niso slišali za najmanjše kvadrate. Dovolj je le poznati nekatere značilnosti njegovega dela. Še posebej:

• Če razporedite obseg znanih vrednosti spremenljivke y v eno vrstico ali stolpec, bo program vsako vrstico (stolpec) z znanimi vrednostmi x zaznal kot ločeno spremenljivko.
• Če obseg z znanim x ni naveden v oknu TREND, ga bo program pri uporabi funkcije v Excelu obravnaval kot matriko, sestavljeno iz celih števil, katerih število ustreza obsegu z danimi vrednostmi spremenljivka y.
• Za izpis matrike "predvidenih" vrednosti je treba izraz za izračun trenda vnesti kot matrično formulo.
• Če nove vrednosti x niso določene, jih funkcija TREND šteje za enake znanim. Če niso navedeni, se kot argument vzame niz 1; 2; 3; 4;…, kar je sorazmerno z razponom z že podanimi parametri y.
• Obseg, ki vsebuje nove vrednosti x, mora imeti enakih ali več vrstic ali stolpcev kot obseg, ki vsebuje podane vrednosti y. Z drugimi besedami, mora biti sorazmeren z neodvisnimi spremenljivkami.
• Matrika z znanimi vrednostmi x lahko vsebuje več spremenljivk. Če pa govorimo samo o enem, potem je potrebno, da so razponi z danimi vrednostmi x in y sorazmerni. V primeru več spremenljivk je potrebno, da obseg z danimi vrednostmi y ustreza enemu stolpcu ali eni vrstici.

funkcija PREDICTION

Regresijska analiza v Excelu se izvaja z uporabo več funkcij. Eden od njih se imenuje "NAPOVED". Podoben je "TRENDU", tj. daje rezultat izračuna z uporabo metode najmanjših kvadratov. Vendar le za en X, za katerega vrednost Y ni znana.

Zdaj poznate formule v Excelu za lutke, ki vam omogočajo napovedovanje prihodnje vrednosti določenega kazalnika glede na linearni trend.

Metoda najmanjših kvadratov je matematični postopek za sestavo linearne enačbe, ki bo najbolj natančno ustrezala nizu dveh nizov števil. Namen uporabe te metode je zmanjšati skupno kvadratno napako. Excel ima orodja, ki vam lahko pomagajo uporabiti to metodo pri izračunih. Ugotovimo, kako se to naredi.

· Uporaba metode v Excelu

o Omogočanje dodatka »Iskanje rešitev«.

o Problemski pogoji

o Rešitev

Uporaba metode v Excelu

Metoda najmanjših kvadratov (LSM) je matematični opis odvisnosti ene spremenljivke od druge. Lahko se uporablja za napovedovanje.

Omogočanje dodatka Find Solution

Če želite uporabljati MNC v Excelu, morate omogočiti dodatek "Iskanje rešitve", ki je privzeto onemogočen.

1. Pojdite na zavihek "Mapa".

2. Kliknite na ime razdelka "Opcije".

3. V oknu, ki se odpre, izberite pododdelek "Dodatki".

4. V bloku "Nadzor", ki se nahaja na dnu okna, nastavite stikalo v položaj "Dodatki za Excel"(če ima drugačno vrednost) in kliknite na gumb "Pojdi ...".

5. Odpre se majhno okno. Ob parametru postavimo kljukico "Iskanje rešitve". Kliknite na gumb "V REDU".

Zdaj pa funkcija Iskanje rešitve v Excelu je aktiviran, njegova orodja pa so prikazana na traku.

Lekcija: Iskanje rešitve v Excelu

Pogoji problema

Opišimo uporabo LSM na konkretnem primeru. Imamo dve vrstici številk x in l, katerega zaporedje je prikazano na spodnji sliki.

To odvisnost lahko najbolj natančno opišemo s funkcijo:

Ob tem je znano, ko x=0 y tudi enakovredna 0 . Zato lahko to enačbo opišemo z odvisnostjo y=nx.

Najti moramo najmanjšo vsoto kvadratov razlike.

rešitev

Preidimo na opis neposredne uporabe metode.

1. Levo od prve vrednosti x daj številko 1 . To bo približna vrednost prve vrednosti koeficienta n.

2. Desno od stolpca l dodajte še en stolpec - nx. V prvo celico tega stolpca zapišemo formulo za množenje koeficienta n na celico prve spremenljivke x. Hkrati naredimo povezavo do polja s koeficientom absolutno, saj se ta vrednost ne spremeni. Kliknite na gumb Vnesite.

3. Z oznako za polnjenje kopirajte to formulo v celoten obseg tabele v spodnjem stolpcu.

4. V ločeni celici izračunajte vsoto razlik med kvadrati vrednosti l in nx. Če želite to narediti, kliknite na gumb "Vstavi funkcijo".

5. V odprtem "Čarovnik za funkcije" išče vstop "SUMMKVARNA". Izberite ga in pritisnite gumb "V REDU".

6. Odpre se okno z argumenti. Na terenu "Matrika_x" l. Na terenu "Matrika_y" vnesite obseg celic stolpca nx. Če želite vnesti vrednosti, preprosto postavite kazalec v polje in izberite ustrezen obseg na listu. Po vnosu kliknite na gumb "V REDU".

7. Pojdite na zavihek "Podatki". Na traku v orodju "Analiza" kliknite na gumb "Iskanje rešitve".

8. Odpre se okno s parametri za to orodje. Na terenu "Optimiziraj funkcijo cilja" navedite naslov celice s formulo "SUMMKVARNA". V parametru "pred" obvezno nastavite stikalo v položaj "Minimalno". Na terenu "Spreminjanje celic" navedite naslov z vrednostjo koeficienta n. Kliknite na gumb "Najdi rešitev".

9. Rešitev bo prikazana v celici s koeficientom n. Ta vrednost bo najmanjši kvadrat funkcije. Če rezultat zadovolji uporabnika, kliknite na gumb "V REDU" v dodatnem oknu.

Kot lahko vidite, je uporaba metode najmanjših kvadratov precej zapleten matematični postopek. Pokazali smo ga v akciji na preprostem primeru, vendar obstajajo veliko bolj zapleteni primeri. Vendar so orodja Microsoft Excel zasnovana tako, da čim bolj poenostavijo izračune.

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

Splošne določbe

Manjše kot je število v absolutni vrednosti, boljša je izbrana premica (2). Kot značilnost natančnosti izbire ravne črte (2) lahko vzamemo vsoto kvadratov

Minimalni pogoji za S bodo

	(6)
	(7)

Enačbi (6) in (7) lahko zapišemo takole:

	(8)
	(9)

Iz enačb (8) in (9) je enostavno najti a in b iz eksperimentalnih vrednosti xi in y i. Premica (2), definirana z enačbama (8) in (9), se imenuje premica, dobljena z metodo najmanjših kvadratov (to ime poudarja, da ima vsota kvadratov S minimum). Enačbi (8) in (9), iz katerih je določena premica (2), imenujemo normalne enačbe.

Navedete lahko preprost in splošen način sestavljanja normalnih enačb. Z uporabo eksperimentalnih točk (1) in enačbe (2) lahko zapišemo sistem enačb za a in b

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

Pomnožimo levo in desno stran vsake od teh enačb s koeficientom prve neznanke a (tj. z x 1, x 2, ..., x n) in seštejmo dobljene enačbe, tako da dobimo prvo normalno enačbo (8) .

Pomnožimo levo in desno stran vsake od teh enačb s koeficientom druge neznanke b, tj. za 1 in dobljene enačbe seštejemo, rezultat je druga normalna enačba (9).

Ta metoda pridobivanja normalnih enačb je splošna: primerna je na primer za funkcijo

obstaja konstantna vrednost in jo je treba določiti iz eksperimentalnih podatkov (1).

Sistem enačb za k lahko zapišemo:

Poiščite premico (2) z uporabo metode najmanjših kvadratov.

rešitev. Najdemo:

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

Zapišemo enačbi (8) in (9)91a+21b=179.1,

21a+6b=46,3, od tu najdemo
a=0,98 b=4,3.

Metoda najmanjših kvadratov uporablja za oceno parametrov regresijske enačbe.

Ena od metod za proučevanje stohastičnih odnosov med karakteristikami je regresijska analiza.
Regresijska analiza je izpeljava regresijske enačbe, s pomočjo katere se najde povprečna vrednost naključne spremenljivke (rezultatnega atributa), če je znana vrednost druge (ali drugih) spremenljivk (faktorskih atributov). Vključuje naslednje korake:

1. izbira oblike povezave (tip analitične regresijske enačbe);
2. ocena parametrov enačbe;
3. ocena kakovosti analitične regresijske enačbe.

Najpogosteje se linearna oblika uporablja za opis statističnega odnosa značilnosti. Osredotočenost na linearna razmerja je razložena z jasno ekonomsko razlago njegovih parametrov, omejeno variacijo spremenljivk in dejstvom, da se v večini primerov nelinearne oblike razmerij pretvorijo (z logaritmom ali zamenjavo spremenljivk) v linearno obliko za izvedbo izračunov. .
V primeru linearne parne povezave bo regresijska enačba imela obliko: y i =a+b·x i +u i . Parametra a in b te enačbe sta ocenjena iz podatkov statističnega opazovanja x in y. Rezultat takega ocenjevanja je enačba: , kjer sta , oceni parametrov a in b , vrednost nastalega atributa (spremenljivke), dobljena iz regresijske enačbe (izračunana vrednost).

Najpogosteje se uporablja za oceno parametrov metoda najmanjših kvadratov (LSM).
Metoda najmanjših kvadratov zagotavlja najboljše (dosledne, učinkovite in nepristranske) ocene parametrov regresijske enačbe. Vendar le, če so izpolnjene določene predpostavke glede naključnega člena (u) in neodvisne spremenljivke (x) (glejte predpostavke OLS).

Problem ocenjevanja parametrov enačbe linearnega para z uporabo metode najmanjših kvadratov je naslednji: pridobiti takšne ocene parametrov , , pri katerih je vsota kvadratnih odstopanj dejanskih vrednosti rezultantne karakteristike - y i od izračunanih vrednosti - minimalna.
Formalno OLS test lahko zapišemo takole: .

Klasifikacija metod najmanjših kvadratov

1. Metoda najmanjših kvadratov.
2. Metoda največje verjetnosti (za običajni klasični linearni regresijski model je postulirana normalnost regresijskih ostankov).
3. Posplošena metoda najmanjših kvadratov OLS se uporablja v primeru avtokorelacije napak in v primeru heteroskedastičnosti.
4. Metoda uteženih najmanjših kvadratov (poseben primer OLS s heteroskedastičnimi ostanki).

Ponazorimo bistvo klasična metoda najmanjših kvadratov grafično. Da bi to naredili, bomo na podlagi opazovalnih podatkov (x i, y i, i=1;n) v pravokotnem koordinatnem sistemu zgradili razpršeni graf (takšen razpršeni graf imenujemo korelacijsko polje). Poskusimo izbrati ravno črto, ki je najbližje točkam korelacijskega polja. Po metodi najmanjših kvadratov je premica izbrana tako, da je vsota kvadratov navpičnih razdalj med točkami korelacijskega polja in to premico minimalna.

Matematični zapis za ta problem: .
Vrednosti y i in x i =1...n so nam znane; to so opazovalni podatki. V funkciji S predstavljajo konstante. Spremenljivke v tej funkciji so zahtevane ocene parametrov - , . Da bi našli minimum funkcije dveh spremenljivk, je treba izračunati delne odvode te funkcije za vsakega od parametrov in jih enačiti na nič, tj. .
Kot rezultat dobimo sistem dveh normalnih linearnih enačb:
Z reševanjem tega sistema najdemo zahtevane ocene parametrov:

Pravilnost izračuna parametrov regresijske enačbe lahko preverimo s primerjavo zneskov (lahko pride do odstopanja zaradi zaokroževanja izračunov).
Za izračun ocen parametrov lahko sestavite tabelo 1.
Predznak regresijskega koeficienta b označuje smer povezave (če je b >0, je povezava direktna, če b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formalno je vrednost parametra a povprečna vrednost y z x enakim nič. Če faktor atributa nima in ne more imeti vrednosti nič, potem zgornja razlaga parametra a ni smiselna.

Ocenjevanje tesnosti razmerja med značilnostmi izvedemo z uporabo korelacijskega koeficienta linearnega para - r x,y. Lahko se izračuna po formuli: . Poleg tega je korelacijski koeficient linearnega para mogoče določiti z regresijskim koeficientom b: .
Razpon sprejemljivih vrednosti korelacijskega koeficienta linearnega para je od –1 do +1. Predznak korelacijskega koeficienta kaže smer razmerja. Če je r x, y >0, je povezava neposredna; če je r x, y<0, то связь обратная.
Če je ta koeficient po velikosti blizu enote, potem je razmerje med značilnostmi mogoče interpretirati kot precej tesno linearno. Če je njen modul enak ena ê r x , y ê =1, potem je razmerje med karakteristikama funkcionalno linearno. Če sta lastnosti x in y linearno neodvisni, potem je r x,y blizu 0.
Za izračun r x,y lahko uporabite tudi tabelo 1.

Za oceno kakovosti nastale regresijske enačbe izračunajte teoretični koeficient determinacije - R 2 yx:

,
kjer je d 2 varianca y, razložena z regresijsko enačbo;
e 2 - preostala (nepojasnjena z regresijsko enačbo) varianca y;
s 2 y - skupna (skupna) varianca y.
Koeficient determinacije označuje delež variacije (razpršenosti) rezultantnega atributa y, razloženega z regresijo (in posledično faktorja x) v celotni variaciji (disperziji) y. Koeficient determinacije R 2 yx ima vrednosti od 0 do 1. V skladu s tem vrednost 1-R 2 yx označuje delež variance y, ki je posledica vpliva drugih dejavnikov, ki niso upoštevani v modelu in specifikacijskih napak.
S parno linearno regresijo je R 2 yx =r 2 yx.

Metoda najmanjših kvadratov (LSM)

Sistem m linearnih enačb z n neznankami ima obliko:

Možni so trije primeri: m n. Primer, ko je m=n, smo obravnavali v prejšnjih odstavkih. Ko m

Če je m>n in je sistem konsistenten, ima matrika A vsaj m - n linearno odvisnih vrstic. Tu lahko rešitev dobimo tako, da izberemo n poljubnih linearno neodvisnih enačb (če obstajajo) in uporabimo formulo X = A -1 CV, to je reduciramo problem na predhodno rešenega. V tem primeru bo dobljena rešitev vedno zadostila preostalim m - n enačbam.

Pri uporabi računalnika pa je bolj priročno uporabiti bolj splošen pristop – metodo najmanjših kvadratov.

Algebraična metoda najmanjših kvadratov

Algebraična metoda najmanjših kvadratov je metoda za reševanje sistemov linearnih enačb

z minimizacijo evklidske norme

Sekira? b? >inf. (1,2)

Analiza eksperimentalnih podatkov

Oglejmo si poskus, med katerim v trenutkih časa

Na primer, izmeri se temperatura Q(t). Naj bodo rezultati meritev določeni z nizom

Predpostavimo, da so eksperimentalni pogoji takšni, da se meritve izvajajo z znano napako. V teh primerih se zakon temperaturne spremembe Q(t) išče z uporabo določenega polinoma

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

določanje neznanih koeficientov, ..., iz premislekov, da je vrednost E(, ...,), definirana z enakostjo

Gaussov algebrski exel približek

vzel minimalno vrednost. Ker je vsota kvadratov minimizirana, se ta metoda imenuje aproksimacija podatkov po metodi najmanjših kvadratov.

Če zamenjamo P(t) z njegovim izrazom, dobimo

Zastavimo nalogo definiranja matrike tako, da bo vrednost minimalna, tj. Definirajmo matriko z metodo najmanjših kvadratov. Da bi to naredili, izenačimo delne odvode na nič:

Če vnesete m × n matriko A = (), i = 1, 2..., m; j = 1, 2, ..., n, kjer je

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

potem bo zapisana enakost dobila obliko

Prepišimo zapisano enakost v smislu operacij z matricami. Po definiciji množenja matrike s stolpcem imamo

Za transponirano matriko je podobno razmerje videti takole

Uvedemo zapis: označili bomo i-to komponento vektorja Ax V skladu z zapisanimi matričnimi enačbami bomo imeli

V matrični obliki lahko to enakost prepišemo kot

A T x=A T B (1.3)

Tukaj je A pravokotna m×n matrika. Poleg tega je pri problemih aproksimacije podatkov praviloma m > n. Enačbo (1.3) imenujemo normalna enačba.

Že od samega začetka je bilo mogoče z uporabo evklidske norme vektorjev zapisati problem v ekvivalentni matrični obliki:

Naš cilj je minimizirati to funkcijo v x. Da bi bil dosežen minimum na točki rešitve, morajo biti prvi odvodi glede na x na tej točki enaki nič. Izpeljanke te funkcije so

2A T B + 2A T Ax

zato mora rešitev zadoščati sistemu linearnih enačb

(AT A)x = (AT B).

Te enačbe imenujemo normalne enačbe. Če je A matrika m × n, potem je A>A - n × n matrika, tj. Matrika normalne enačbe je vedno kvadratna simetrična matrika. Poleg tega ima lastnost pozitivne določnosti v smislu, da je (A>Ax, x) = (Ax, Ax)? 0.

Komentiraj. Včasih rešitev enačbe oblike (1.3) imenujemo rešitev sistema Ax = B, kjer je A pravokotna m × n (m > n) matrika z uporabo metode najmanjših kvadratov.

Problem najmanjših kvadratov je mogoče grafično interpretirati kot minimiziranje navpičnih razdalj od podatkovnih točk do krivulje modela (glej sliko 1.1). Ta ideja temelji na predpostavki, da vse napake v aproksimaciji ustrezajo napakam v opazovanjih. Če so tudi napake v neodvisnih spremenljivkah, potem je morda bolj primerno zmanjšati evklidsko razdaljo od podatkov do modela.

MNC v Excelu

Spodnji algoritem za implementacijo OLS v Excel predpostavlja, da so vsi začetni podatki že znani. Obe strani matrične enačbe AЧX=B sistema na levi pomnožimo s transponirano matriko sistema А Т:

DAVEK=A T B

Nato obe strani enačbe na levi pomnožimo z matriko (AT A) -1. Če ta matrika obstaja, potem je sistem definiran. Glede na to

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

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

Nastala matrična enačba je rešitev sistema m linearnih enačb z n neznankami za m>n.

Oglejmo si uporabo zgornjega algoritma na posebnem primeru.

Primer. Naj bo treba rešiti sistem

V Excelu je list z rešitvami v načinu prikaza formule za to težavo videti takole:

Rezultati izračuna:

Zahtevani vektor X se nahaja v območju E11:E12.

Pri reševanju danega sistema linearnih enačb so bile uporabljene naslednje funkcije:

1. MOBR - vrne inverzno matriko za matriko, shranjeno v matriki.

Sintaksa: MOBR(niz).

Matrika je številska matrika z enakim številom vrstic in stolpcev.

2. MULTIPULT - vrne zmnožek matrik (matrike so shranjene v nizih). Rezultat je matrika z enakim številom vrstic kot matrika1 in enakim številom stolpcev kot matrika2.

Sintaksa: MULTIPLE(matrika1,matrika2).

Matrika1, matrika2 sta množični matriki.

Ko vnesete funkcijo v zgornjo levo celico obsega matrike, izberite matriko, začenši s celico, ki vsebuje formulo, pritisnite F2 in nato pritisnite CTRL+SHIFT+ENTER.

3. TRANSPORT - pretvori navpični niz celic v vodoravnega ali obratno. Kot rezultat uporabe te funkcije se prikaže matrika s številom vrstic, ki je enako številu stolpcev prvotne matrike, in številom stolpcev, ki je enako številu vrstic začetne matrike.

4.1. Uporaba vgrajenih funkcij

Izračun regresijski koeficienti izvede s funkcijo

LINEST(Vrednosti_y; x-vrednosti; Konst; statistika),

Vrednosti_y- niz vrednosti y,

x-vrednosti- izbirno polje vrednosti x, če niz X izpuščen, se predpostavlja, da je to polje (1;2;3;...) enake velikosti kot Vrednosti_y,

Konst- logična vrednost, ki kaže, ali je konstanta zahtevana b je bila enaka 0. Če Konst ima pomen PRAV ali izpuščeno, torej b se izračuna na običajen način. Če argument Konst je FALSE, potem b se predpostavlja, da je 0 in vrednosti a so izbrani tako, da je razmerje izpolnjeno y=ax.

Statistika je logična vrednost, ki označuje, ali je treba vrniti dodatne regresijske statistike. Če argument Statistika ima pomen PRAV, nato funkcijo LINEST vrne dodatno regresijsko statistiko. Če argument Statistika ima pomen LAŽI ali izpuščeno, nato funkcijo LINEST vrne le koeficient a in stalna b.

Ne smemo pozabiti, da je rezultat funkcij LINEST() je niz vrednosti – niz.

Za izračun korelacijski koeficient se uporablja funkcija

CORREL(Niz1;Array2),

vračanje vrednosti korelacijskega koeficienta, kjer Niz1- niz vrednosti l, Array2- niz vrednosti x. Niz1 in Array2 morajo biti enake velikosti.

PRIMER 1. Zasvojenost l(x) je predstavljen v tabeli. Zgradite regresijska črta in izračunaj korelacijski koeficient.

l		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

Vnesimo tabelo vrednosti v list MS Excel in zgradimo raztreseni graf. Delovni list bo dobil obliko, prikazano na sl. 2.

Za izračun vrednosti regresijskih koeficientov A in b izberite celice A7:B7, Pojdimo v čarovnika za funkcije in v kategorijo Statistični izberite funkcijo LINEST. Izpolnimo pogovorno okno, ki se prikaže, kot je prikazano na sl. 3 in pritisnite v redu.

Posledično bo izračunana vrednost prikazana samo v celici A6(slika 4). Da se vrednost prikaže v celici B6 vstopiti morate v način urejanja (tipka F2) in nato pritisnite kombinacijo tipk CTRL+SHIFT+ENTER.

Za izračun vrednosti korelacijskega koeficienta v celici C6 uvedena je bila naslednja formula:

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

Poznavanje regresijskih koeficientov A in b izračunajmo vrednosti funkcij l=sekira+b za dano x. Da bi to naredili, uvedemo formulo

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

in ga kopirajte v obseg C5:J5(slika 5).

Narišite regresijsko premico na diagram. Izberite eksperimentalne točke na grafu, kliknite z desno tipko miške in izberite ukaz Začetni podatki. V pogovornem oknu, ki se prikaže (slika 5), ​​izberite zavihek Vrsti in kliknite na gumb Dodaj. Izpolnimo vnosna polja, kot je prikazano na sl. 6 in pritisnite gumb v redu. Grafu eksperimentalnih podatkov bo dodana regresijska črta. Privzeto bo njegov graf narisan kot točke, ki niso povezane z gladkimi črtami.

riž. 6

Če želite spremeniti videz regresijske črte, izvedite naslednje korake. Z desno miškino tipko kliknite točke, ki prikazujejo črtni graf in izberite ukaz Vrsta grafikona in nastavite vrsto raztresenega diagrama, kot je prikazano na sl. 7.

Vrsto črte, barvo in debelino lahko spremenite na naslednji način. Izberite črto na diagramu, kliknite z desno miškino tipko in v kontekstnem meniju izberite ukaz Oblika niza podatkov ... Nato naredite nastavitve, na primer, kot je prikazano na sl. 8.

Kot rezultat vseh transformacij dobimo graf eksperimentalnih podatkov in regresijsko premico v enem grafičnem območju (slika 9).

4.2. Uporaba trendne linije.

Konstrukcija različnih aproksimacijskih odvisnosti v MS Excelu je implementirana kot lastnost grafikona - linija trenda.

PRIMER 2. Kot rezultat poskusa je bila določena določena odvisnost tabele.

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

Izberite in sestavite aproksimativno odvisnost. Izdelajte grafe tabelaričnih in izbranih analitičnih odvisnosti.

Reševanje problema lahko razdelimo na naslednje faze: vnos začetnih podatkov, izdelava raztresenega grafa in dodajanje trendne črte na ta graf.

Oglejmo si ta postopek podrobno. Vnesemo začetne podatke v delovni list in narišemo eksperimentalne podatke. Nato izberite eksperimentalne točke na grafu, kliknite z desno tipko miške in uporabite ukaz Dodaj l linija trenda(Slika 10).

Pogovorno okno, ki se prikaže, vam omogoča, da zgradite približno razmerje.

Prvi zavihek (slika 11) tega okna označuje vrsto aproksimacijske odvisnosti.

Na drugem (slika 12) so določeni konstrukcijski parametri:

· ime aproksimativne odvisnosti;

· napoved naprej (nazaj) po n enot (ta parameter določa, za koliko enot naprej (nazaj) je treba podaljšati trendno črto);

ali prikazati presečišče krivulje z ravnico y=konst;

· prikaz aproksimativne funkcije na diagramu ali ne (možnost prikaza enačbe na diagramu);

· ali vrednost standardnega odklona umestiti na diagram ali ne (možnost vpisa vrednosti aproksimacijske zanesljivosti na diagram).

Za aproksimativno odvisnost izberimo polinom druge stopnje (slika 11) in na grafu prikažimo enačbo, ki ta polinom opisuje (slika 12). Nastali diagram je prikazan na sl. 13.

Podobno z uporabo trendne linije lahko izberete parametre takšnih odvisnosti kot

linearni l=a∙x+b,

logaritemski l=a∙ln(x)+b,

· eksponentna l=a∙e b,

· umirjeno l=a∙x b,

polinom l=a∙x 2 +b∙x+c, l=a∙x 3 +b∙x 2 +c∙x+d in tako naprej, do vključno polinoma 6. stopnje,

· linearna filtracija.

4.3. Uporaba reševalnega bloka

Zelo zanimiva je implementacija v MS Excelu izbire parametrov po metodi najmanjših kvadratov z uporabo reševalnega bloka. Ta tehnika vam omogoča izbiro parametrov funkcije katere koli vrste. Razmislimo o tej možnosti na primeru naslednjega problema.

PRIMER 3. Kot rezultat eksperimenta je bila pridobljena odvisnost z(t), prikazana v tabeli

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

Izberite koeficiente odvisnosti Z(t)=At 4 +Bt 3 +Ct 2 +Dt+K metoda najmanjših kvadratov.

Ta problem je enakovreden problemu iskanja minimuma funkcije petih spremenljivk

Oglejmo si postopek reševanja optimizacijskega problema (slika 14).

Naj vrednote A, IN, Z, D in TO shranjeni v celicah A7:E7. Izračunajmo teoretične vrednosti funkcije Z(t)=Pri 4 +Bt 3 +Ct 2 +Dt+K za dano t(B2:J2). Če želite to narediti, v celici B4 vnesite vrednost funkcije na prvo točko (celica B2):

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

Prekopirajmo to formulo v obseg C4:J4 in dobimo pričakovano vrednost funkcije v točkah, katerih abscise so shranjene v celicah B2:J2.

V celico B5 Predstavimo formulo, ki izračuna kvadrat razlike med eksperimentalno in izračunano točko:

B5=(B4-B3)^2,

in ga kopirajte v obseg C5:J5. V celici F7 shranili bomo skupno kvadratno napako (10). Če želite to narediti, vnesite formulo:

F7 = SUM(B5:J5).

Uporabimo ukaz Storitev® Iskanje rešitve in rešiti problem optimizacije brez omejitev. Ustrezno izpolnimo vnosna polja v pogovornem oknu, prikazanem na sl. 14 in pritisnite gumb Izvedi. Če je rešitev najdena, se okno, prikazano na sl. 15.

Rezultat odločitvenega bloka bo izpisan v celice A7:E7vrednosti parametrov funkcije Z(t)=Pri 4 +Bt 3 +Ct 2 +Dt+K. V celicah B4:J4 dobimo pričakovana vrednost funkcije na izhodiščih. V celici F7 bo shranjeno skupna kvadratna napaka.

Eksperimentalne točke in prilagojeno črto lahko prikažete v enem grafičnem območju tako, da izberete obseg B2:J4, pokliči Čarovnik za grafikone in nato formatiraj videz prejeli grafe.

riž. 17 prikaže delovni list MS Excel po opravljenih izračunih.

5. REFERENCE

1. Alekseev E.R., Chesnokova O.V., Reševanje problemov računalniške matematike v paketih Mathcad12, MATLAB7, Maple9. – NT Press, 2006.–596 str. :il. – (Vadnica)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudčenko, Scilab, reševanje inženirskih in matematičnih problemov. –M., BINOM, 2008.–260 str.

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

4. Garnaev A.Yu., Uporaba MS EXCEL in VBA v ekonomiji in financah. – St. Petersburg: BHV - Petersburg, 1999.–332 str.

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

6. Korn G., Korn T., Priročnik iz matematike za znanstvenike in inženirje – M., 1970, 720 str.

7. Alekseev E.R., Chesnokova O.V. Navodila za izvajanje laboratorijskih vaj v MS EXCEL. Za študente vseh specialnosti. Donetsk, DonNTU, 2004. 112 str.