Metoda najmanjši kvadrati(MNC)

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. matrica normalna enačba 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.

Metoda najmanjših kvadratov je matematični postopek za konstruiranje linearna enačba, ki bi se najbolj ujemala z nizom dveh nizov števil. Namen uporabe te metode je zmanjšati skupno kvadratno napako. Excel ima orodja, s katerimi lahko ta metoda med izračuni. Ugotovimo, kako se to naredi.

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.

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

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

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.

Metoda najmanjših kvadratov (OLS) se nanaša na kroglo regresijska analiza. 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.

Po navedbah matematična statistika, 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 visoke stopnje, vendar te možnosti ni le težko izvesti, 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 (LS) temelji na minimiziranju vsote kvadratov odstopanj izbrane funkcije od proučevanih podatkov. V tem članku smo približali razpoložljive podatke z uporabo linearna funkcija l = a x + b .

Metoda najmanjših kvadratov(Angleščina) Vsakdanji Vsaj Kvadrati , O.L.S.) je ena izmed osnovnih metod regresijske analize v smislu ocenjevanja neznanih parametrov regresijski modeli po vzorčnih podatkih.

Oglejmo si aproksimacijo s funkcijami, ki so odvisne samo od ene spremenljivke:

• Linearno: y=ax+b (ta članek)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+с
• : y=ax 2 +bx+c

Opomba: V članku so obravnavani primeri aproksimacije s polinomom od 3. do 6. stopnje. Tukaj je obravnavana aproksimacija s trigonometričnim polinomom.

Linearna odvisnost

Zanima nas povezava med 2 spremenljivkama X in l. Obstaja domneva, da l odvisno od X po linearnem zakonu l = sekira + b. Za določitev parametrov tega razmerja je raziskovalec izvedel opazovanja: za vsako vrednost x i je bila opravljena meritev y i (glejte primer datoteke). V skladu s tem naj bo 20 parov vrednosti (x i; y i).

Opomba:Če je korak spremembe X je konstantna, nato graditi razpršene ploskve lahko uporabite, če ne, potem morate uporabiti vrsto grafikona Spot .

Iz diagrama je očitno, da je razmerje med spremenljivkami blizu linearnemu. Da bi razumeli, katera od številnih ravnih črt najbolj "pravilno" opisuje razmerje med spremenljivkami, je treba določiti merilo, po katerem se črte primerjajo.

Kot tak kriterij uporabljamo izraz:

Kje ŷ jaz = a * x i + b ; n – število parov vrednosti (v našem primeru n=20)

Zgornji izraz je vsota kvadratov razdalj med opazovanimi vrednostmi y i in ŷ i in je pogosto označen kot SSE ( vsota od Na kvadrat Napake (Ostanki), vsota kvadratov napak (ostanki)) .

Metoda najmanjših kvadratov je izbrati takšno vrstico ŷ = sekira + b, za katerega ima zgornji izraz najmanjšo vrednost.

Opomba: Vsaka črta v dvodimenzionalnem prostoru je enolično določena z vrednostmi dveh parametrov: a (naklon) in b (premik).

Menijo, da manjša kot je vsota kvadratov razdalj, bolje se ustrezna črta približa razpoložljivim podatkom in jo je mogoče nadalje uporabiti za napovedovanje vrednosti y iz spremenljivke x. Jasno je, da tudi če v resnici ni povezave med spremenljivkami ali je povezava nelinearna, bo OLS še vedno izbral »najboljšo« vrstico. Tako metoda najmanjših kvadratov ne pove ničesar o prisotnosti resničnega razmerja med spremenljivkami; metoda preprosto omogoča izbiro takšnih funkcijskih parametrov a in b , za katerega je zgornji izraz minimalen.

Z izvajanjem ne zelo zapletenih matematičnih operacij (za več podrobnosti glejte) lahko izračunate parametre a in b :

Kot je razvidno iz formule, parameter a predstavlja razmerje kovariance in , zato v MS EXCEL za izračun parametra A Uporabite lahko naslednje formule (glejte Datoteka primera linearnega lista):

= KOVAR(B26:B45;C26:C45)/ DISP.G(B26:B45) oz

= COVARIANCE.B(B26:B45;C26:C45)/DISP.B(B26:B45)

Tudi za izračun parametra A lahko uporabite formulo = NAGIB(C26:C45;B26:B45). Za parameter b uporabite formulo = LEG(C26:C45;B26:B45) .

Končno vam funkcija LINEST() omogoča izračun obeh parametrov hkrati. Za vnos formule LINEST(C26:C45;B26:B45) Izbrati morate 2 celici v vrsti in klikniti CTRL + SHIFT + ENTER(glej članek o). Vrednost bo vrnjena v levi celici A , na desni - b .

Opomba: Da se izognete zapletom z vnosom matrične formule boste morali dodatno uporabiti funkcijo INDEX(). Formula = INDEX(LINEST(C26:C45;B26:B45);1) ali samo = LINEST(C26:C45;B26:B45) vrne parameter, odgovoren za naklon črte, tj. A . Formula = INDEX(LINEST(C26:C45;B26:B45);2) vrne parameter, odgovoren za presečišče črte z osjo Y, tj. b .

Po izračunu parametrov, raztreseni diagram lahko narišete ustrezno črto.

Drug način za risanje ravne črte z uporabo metode najmanjših kvadratov je orodje za graf Linija trenda. Če želite to narediti, izberite diagram, izberite v meniju Zavihek Postavitev, V skupinska analiza kliknite Linija trenda, potem Linearni približek .

Če v pogovornem oknu potrdite polje »prikaži enačbo v diagramu«, se lahko prepričate, da se zgornji parametri ujemajo z vrednostmi v diagramu.

Opomba: Da se parametri ujemajo, mora biti vrsta diagrama . Bistvo je, da pri sestavljanju diagrama Urnik Vrednosti osi X uporabnik ne more določiti (uporabnik lahko določi samo oznake, ki ne vplivajo na lokacijo točk). Namesto vrednosti X se uporablja zaporedje 1; 2; 3; ... (za številčenje kategorij). Zato, če gradite linija trenda na tipskem diagramu Urnik, potem bodo namesto dejanskih vrednosti X uporabljene vrednosti tega zaporedja, kar bo vodilo do napačnega rezultata (razen če seveda dejanske vrednosti X ne sovpadajo z zaporedjem 1; 2; 3; ...).

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

Na diagram narišimo regresijsko premico. Izberite eksperimentalne točke na grafu, kliknite z desno miškino tipko 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., Računalniške metode – 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 analiza – 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 izvedbo laboratorijsko delo v MS EXCEL. Za študente vseh specialnosti. Donetsk, DonNTU, 2004. 112 str.