meetod vähimruudud(MNC)

M lineaarvõrrandi süsteemil, millel on n tundmatut, on vorm:

Võimalikud on kolm juhtumit: m n. Eelmistes lõikudes käsitleti juhtu, kui m = n. Kell m

Kui m>n ja süsteem on järjekindel, siis maatriksil A on vähemalt m - n lineaarselt sõltuvat rida. Siin saab lahenduse, valides suvalise n lineaarselt sõltumatut võrrandit (kui need on olemas) ja rakendades valemit X = A -1 CV, st taandada ülesande varem lahendatuks. Sel juhul rahuldab saadud lahendus alati ülejäänud m - n võrrandid.

Arvuti kasutamisel on aga mugavam kasutada üldisemat lähenemist – vähimruutude meetodit.

Algebraline vähimruutude meetod

Algebraline vähimruutude meetod on meetod lineaarvõrrandisüsteemide lahendamiseks

minimeerides Eukleidise normi

Kirves? b? > inf. (1.2)

Katseandmete analüüs

Vaatleme mõnda katset, mille käigus teatud ajahetkedel

Näiteks mõõdetakse temperatuuri Q(t). Olgu mõõtmistulemused määratud massiiviga

Oletame, et katsetingimused on sellised, et mõõtmised viiakse läbi teadaoleva veaga. Nendel juhtudel otsitakse temperatuurimuutuse seadust Q(t) teatud polünoomi abil

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

tundmatute koefitsientide määramine, ..., lähtudes kaalutlustest, et võrdsusega määratletud väärtus E(, ...,)

gaussi algebraline exeli lähendus

võttis minimaalse väärtuse. Kuna ruutude summa on minimeeritud, nimetatakse seda meetodit andmete vähimruutude lähendamiseks.

Kui asendame P(t) selle avaldisega, saame

Seadke ülesandeks defineerida massiiv nii, et väärtus oleks minimaalne, s.t. Defineerime massiivi vähimruutude meetodil. Selleks võrdsustame osatuletised nulliga:

Kui sisestate m × n maatriksi A = (), i = 1, 2..., m; j = 1, 2, ..., n, kus

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

siis saab kirjalik võrdsus vormi

Kirjutame kirjaliku võrdsuse ümber maatriksitega tehtavatena. Maatriksi veeruga korrutamise definitsiooni järgi on meil

Transponeeritud maatriksi puhul näeb sarnane seos välja järgmine:

Tutvustame tähistust: tähistame vektori Ax i-ndat komponenti Kooskõlas kirjutatud maatriksivõrdsustega saame

IN maatriksvorm selle võrdsuse saab ümber kirjutada kui

A T x = A T B (1,3)

Siin on A ristkülikukujuline m × n maatriks. Pealegi on andmete lähendamise ülesannetes reeglina m > n. Võrrandit (1.3) nimetatakse normaalvõrrandiks.

Algusest peale oli võimalik Eukleidilise vektorite normi abil ülesanne kirjutada samaväärse maatriksi kujul:

Meie eesmärk on minimeerida seda funktsiooni x-is. Lahenduspunktis miinimumi saavutamiseks peavad esimesed tuletised x-i suhtes selles punktis olema võrdsed nulliga. Selle funktsiooni tuletised on

2A T B + 2A T Ax

ja seetõttu peab lahendus rahuldama lineaarvõrrandisüsteemi

(A TA)x = (AT B).

Neid võrrandeid nimetatakse normaalvõrranditeks. Kui A on m× n maatriks, siis A>A - n × n on maatriks, st. maatriks normaalne võrrand alati ruudukujuline sümmeetriline maatriks. Veelgi enam, sellel on positiivse määratluse omadus selles mõttes, et (A>Ax, x) = (Ax, Ax) ? 0.

Kommenteeri. Mõnikord nimetatakse vormi (1.3) võrrandi lahendit süsteemi Ax = B lahendiks, kus A on ristkülikukujuline m × n (m > n) maatriks, kasutades vähimruutude meetodit.

Vähimruutude ülesannet saab graafiliselt tõlgendada kui vertikaalsete kauguste minimeerimist andmepunktidest mudelikõverani (vt joonis 1.1). See idee põhineb eeldusel, et kõik lähendamise vead vastavad vaatluste vigadele. Kui sõltumatutes muutujates on ka vigu, võib olla sobivam minimeerida eukleidiline kaugus andmete ja mudeli vahel.

MNC Excelis

Allolev algoritm OLS-i rakendamiseks Excelis eeldab, et kõik algandmed on juba teada. Korrutame vasakpoolse süsteemi maatriksvõrrandi AЧX=B mõlemad pooled süsteemi А Т transponeeritud maatriksiga:

A T AX = A T B

Seejärel korrutame võrrandi mõlemad pooled vasakult maatriksiga (A T A) -1. Kui see maatriks on olemas, on süsteem defineeritud. Arvestades seda

(A T A) -1 *(A T A)=E, saame

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

Saadud maatriksvõrrand on m lineaarvõrrandi süsteemi lahendus, mille m>n korral on n tundmatut.

Vaatleme ülaltoodud algoritmi rakendamist konkreetse näite abil.

Näide. Olgu vaja süsteem lahendada

Excelis näeb selle probleemi valemikuva režiimis lahendusleht välja järgmine:

Arvutuste tulemused:

Vajalik vektor X asub vahemikus E11:E12.

Antud lineaarvõrrandisüsteemi lahendamisel kasutati järgmisi funktsioone:

1. MOBR – tagastab massiivi salvestatud maatriksi pöördmaatriksi.

Süntaks: MOBR(massiiv).

Massiiv on võrdse arvu ridade ja veergudega numbriline massiiv.

2. MULTIPULT - tagastab maatriksite korrutise (maatriksid salvestatakse massiividesse). Tulemuseks on massiiv, millel on sama arv ridu kui massiiv1 ja sama arv veerge kui massiiv2.

Süntaks: MULTIPLE(massiivi1,massiivi2).

Massiiv1, massiiv2 on korrutatavad massiivid.

Pärast funktsiooni sisestamist massiivivahemiku vasakpoolsesse ülaossa valige massiiv, alustades valemit sisaldavast lahtrist, vajutage klahvi F2 ja seejärel klahvikombinatsiooni CTRL+SHIFT+ENTER.

3. TRANSPORT – teisendab vertikaalse lahtrite komplekti horisontaalseks või vastupidi. Selle funktsiooni kasutamise tulemusena ilmub massiiv, mille ridade arv võrdub algse massiivi veergude arvuga ja veergude arv on võrdne esialgse massiivi ridade arvuga.

Vähimruutude meetod on konstrueerimise matemaatiline protseduur lineaarvõrrand, mis vastaks kõige rohkem kahest numbrisarjast koosnevale komplektile. Selle meetodi kasutamise eesmärk on minimeerida koguruudu viga. Excelis on tööriistad, mida saate kasutada seda meetodit arvutuste ajal. Mõelgem välja, kuidas seda tehakse.

Vähimruutude meetod (LSM) on ühe muutuja sõltuvuse matemaatiline kirjeldus teisest. Seda saab kasutada prognoosimiseks.

Otsi lahenduse lisandmooduli lubamine

MNC kasutamiseks Excelis peate lisandmooduli lubama "Lahenduse leidmine", mis on vaikimisi keelatud.

Nüüd funktsioon Lahenduse leidmine Excelis on aktiveeritud ja selle tööriistad kuvatakse lindil.

Probleemsed tingimused

Kirjeldame LSM-i kasutamist konkreetse näite abil. Meil on kaks numbririda x Ja y , mille järjestus on näidatud alloleval pildil.

Seda sõltuvust saab kõige täpsemalt kirjeldada funktsiooniga:

Samas on teada, et millal x=0 y ka võrdsed 0 . Seetõttu saab seda võrrandit kirjeldada sõltuvusega y=nx .

Peame leidma erinevuse minimaalse ruutude summa.

Lahendus

Liigume edasi meetodi otsese rakendamise kirjelduse juurde.

Nagu näete, on vähimruutude meetodi rakendamine üsna keeruline matemaatiline protseduur. Näitasime seda lihtsa näite abil, kuid on palju keerulisemaid juhtumeid. Microsoft Exceli tööriistad on aga loodud arvutusi võimalikult palju lihtsustama.

Vähimruutude meetod (OLS) viitab sfäärile regressioonianalüüs. Sellel on palju rakendusi, kuna see võimaldab antud funktsiooni ligikaudset esitust teiste lihtsamate funktsioonidega. LSM võib olla väga kasulik vaatluste töötlemisel ja seda kasutatakse aktiivselt mõnede suuruste hindamiseks teiste juhuslikke vigu sisaldavate mõõtmistulemuste põhjal. Sellest artiklist saate teada, kuidas Excelis vähimruutude arvutusi rakendada.

Probleemi avaldus konkreetse näite abil

Oletame, et on kaks indikaatorit X ja Y. Veelgi enam, Y sõltub X-st. Kuna OLS huvitab meid regressioonanalüüsi seisukohast (Excelis on selle meetodid rakendatud sisseehitatud funktsioonide abil), tuleks kohe asuda kaaluma konkreetne probleem.

Olgu siis X toidupoe kaubanduspind, mõõdetuna ruutmeetrites, ja Y aastakäive miljonites rublades.

On vaja teha prognoos, milline on käive (Y), kui kauplusel on see või teine ​​kaubanduspind. Ilmselgelt funktsioon Y = f (X) suureneb, kuna hüpermarket müüb rohkem kaupa kui müügilett.

Paar sõna ennustuseks kasutatud algandmete õigsusest

Oletame, et meil on n poe andmete põhjal koostatud tabel.

Vastavalt matemaatiline statistika, on tulemused enam-vähem õiged, kui uurida andmeid vähemalt 5-6 objekti kohta. Lisaks ei saa kasutada anomaalseid tulemusi. Eelkõige võib elitaarse väikese butiigi käive olla mitu korda suurem kui "masmarketi" klassi suurte jaemüügipunktide käive.

Meetodi olemus

Tabeliandmeid saab Descartes'i tasapinnal kujutada punktidena M 1 (x 1, y 1), ... M n (x n, y n). Nüüd taandatakse ülesande lahendus lähendava funktsiooni y = f (x) valikule, mille graafik läbib võimalikult lähedalt punktidele M 1, M 2, .. M n.

Muidugi võite kasutada kõrge astme polünoomi, kuid seda valikut pole mitte ainult raske rakendada, vaid ka lihtsalt vale, kuna see ei kajasta peamist suundumust, mida tuleb tuvastada. Kõige mõistlikum lahendus on otsida sirget y = ax + b, mis kõige paremini lähendab katseandmeid ehk täpsemalt koefitsiente a ja b.

Täpsuse hindamine

Mis tahes lähendamise korral on selle täpsuse hindamine eriti oluline. Tähistame e i-ga punkti x i funktsionaalsete ja eksperimentaalsete väärtuste erinevust (hälvet), st e i = y i - f (x i).

Ilmselt saate lähenduse täpsuse hindamiseks kasutada hälvete summat, st kui valite sirge X-i sõltuvuse Y-st ligikaudseks esitamiseks, peate eelistama seda, mille väärtus on väikseim summa e i kõigis vaadeldavates punktides. Kuid kõik pole nii lihtne, kuna koos positiivsete kõrvalekalletega on ka negatiivseid.

Probleemi saab lahendada hälbemoodulite või nende ruutude abil. Viimane meetod on kõige laialdasemalt kasutatav. Seda kasutatakse paljudes valdkondades, sealhulgas regressioonanalüüsis (Excelis rakendatakse seda kahe sisseehitatud funktsiooni abil) ja selle tõhusus on juba ammu tõestatud.

Vähimruutude meetod

Nagu teate, on Excelil sisseehitatud funktsioon AutoSum, mis võimaldab teil arvutada kõigi valitud vahemikus asuvate väärtuste väärtused. Seega ei takista miski meil avaldise väärtust (e 1 2 + e 2 2 + e 3 2 + ... e n 2) arvutamast.

Matemaatilises tähistuses näeb see välja järgmine:

Kuna algselt otsustati ligikaudselt sirgjoont kasutada, on meil:

Seega taandub ülesanne leida sirgjoon, mis kõige paremini kirjeldab suuruste X ja Y spetsiifilist sõltuvust, kahe muutuja funktsiooni miinimumi arvutamisele:

Selleks peate võrdsustama uute muutujate a ja b osatuletised nulliga ning lahendama primitiivse süsteemi, mis koosneb kahest võrrandist kahe tundmatu kujuga:

Pärast mõningaid lihtsaid teisendusi, sealhulgas 2-ga jagamist ja summadega manipuleerimist, saame:

Seda lahendades, kasutades näiteks Crameri meetodit, saame teatud koefitsientidega a * ja b * statsionaarse punkti. See on miinimum, s.t et ennustada, milline on kaupluse käive teatud piirkonnas, sobib sirge y = a * x + b *, mis on vaadeldava näite regressioonimudel. Loomulikult ei võimalda see teil täpset tulemust leida, kuid aitab teil saada aimu, kas konkreetse ala poekrediidiga ostmine tasub end ära.

Kuidas rakendada Excelis vähimruutusid

Excelis on funktsioon väärtuste arvutamiseks vähimruutude abil. Sellel on järgmine vorm: "TREND" (teadaolevad Y väärtused; teadaolevad X väärtused; uued X väärtused; konstant). Rakendame oma tabelisse OLS-i arvutamise valemit Excelis.

Selleks sisestage lahtrisse, kus peaks kuvama Exceli vähimruutude meetodil tehtud arvutuse tulemust, märk “=” ja valige funktsioon “TREND”. Avanevas aknas täitke vastavad väljad, tõstes esile:

• Y teadaolevate väärtuste vahemik (antud juhul kaubakäibe andmed);
• vahemik x 1, …x n, st kaubanduspinna suurus;
• x-i teadaolevad ja tundmatud väärtused, mille jaoks peate välja selgitama käibe suuruse (teavet nende asukoha kohta töölehel vt allpool).

Lisaks sisaldab valem loogilist muutujat “Const”. Kui sisestate vastavale väljale 1, tähendab see, et peaksite tegema arvutused, eeldades, et b = 0.

Kui teil on vaja teada saada rohkem kui ühe x väärtuse prognoos, siis pärast valemi sisestamist ärge vajutage sisestusklahvi, vaid peate klaviatuuril tippima kombinatsiooni "Shift" + "Control" + "Enter".

Mõned funktsioonid

Regressioonanalüüs on kättesaadav isegi mannekeenidele. Exceli valemit tundmatute muutujate massiivi väärtuse ennustamiseks – TREND – saavad kasutada isegi need, kes pole vähimruutudest kuulnudki. Piisab vaid mõne selle töö funktsiooni tundmisest. Eelkõige:

• Kui korraldate muutuja y teadaolevate väärtuste vahemiku ühte ritta või veergu, siis tajub programm iga rida (veerg) teadaolevate väärtustega x eraldi muutujana.
• Kui TREND aknas ei ole määratud vahemikku, mille x-iga on teada, siis Excelis funktsiooni kasutamisel käsitleb programm seda täisarvudest koosneva massiivina, mille arv vastab vahemikule antud väärtustega. muutuja y.
• Ennustatud väärtuste massiivi väljastamiseks tuleb trendi arvutamise avaldis sisestada massiivivalemina.
• Kui x uusi väärtusi pole määratud, loeb funktsioon TREND need võrdseks teadaolevatega. Kui neid ei täpsustata, võetakse argumendiks massiiv 1; 2; 3; 4;…, mis on proportsionaalne juba määratud parameetritega y vahemikuga.
• Uusi x väärtusi sisaldav vahemik peab sisaldama sama või enamat rida või veergu kui antud y väärtusi sisaldavas vahemikus. Teisisõnu, see peab olema proportsionaalne sõltumatute muutujatega.
• Teadaolevate x väärtustega massiiv võib sisaldada mitut muutujat. Kui aga räägime ainult ühest, siis on nõutav, et antud väärtustega x ja y vahemikud oleksid proportsionaalsed. Mitme muutuja puhul on vajalik, et antud y väärtustega vahemik mahuks ühte veergu või ühte ritta.

funktsioon PRODICTION

Regressioonianalüüsi Excelis rakendatakse mitme funktsiooni abil. Üks neist kannab nime “ENNUSTUS”. See sarnaneb TRENDiga, st annab vähimruutude meetodil tehtud arvutuste tulemuse. Kuid ainult ühe X puhul, mille Y väärtus on teadmata.

Nüüd teate Excelis mannekeenide valemeid, mis võimaldavad ennustada konkreetse indikaatori tulevast väärtust vastavalt lineaarsele trendile.

Vähimruutude meetod (LS) põhineb valitud funktsiooni uuritavatest andmetest tulenevate ruutude kõrvalekallete summa minimeerimisel. Selles artiklis hindame olemasolevaid andmeid ligikaudselt kasutades lineaarne funktsioon y = a x + b .

Vähimruutude meetod(inglise) Tavaline Vähemalt Ruudud , O.L.S.) on üks regressioonanalüüsi põhimeetodeid tundmatute parameetrite hindamisel regressioonimudelid näidisandmete järgi.

Vaatleme lähendamist funktsioonide järgi, mis sõltuvad ainult ühest muutujast:

• Lineaarne: y=ax+b (see artikkel)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+с
• : y=ax 2 +bx+c

Märkus: Selles artiklis käsitletakse 3. kuni 6. astme polünoomiga lähendamise juhtumeid. Siin käsitletakse lähendamist trigonomeetrilise polünoomi järgi.

Lineaarne sõltuvus

Meid huvitab seos kahe muutuja vahel X Ja y. On oletus, et y oleneb X lineaarse seaduse järgi y = kirves + b. Selle seose parameetrite määramiseks tegi uurija vaatlusi: iga x i väärtuse kohta tehti y i mõõtmine (vt näidisfaili). Vastavalt olgu siis 20 paari väärtusi (x i; y i).

Märkus. Kui muutmise samm on X on pidev, siis ehitada hajutatud krundid saab kasutada, kui mitte, siis peate kasutama diagrammi tüüpi Koht .

Diagrammil on ilmne, et muutujate vaheline seos on lineaarsele lähedane. Et mõista, milline paljudest sirgjoontest kõige „õigemini” kirjeldab muutujate vahelist seost, on vaja kindlaks määrata kriteerium, mille alusel sirgeid võrreldakse.

Sellise kriteeriumina kasutame väljendit:

Kus ŷ i = a * x i + b ; n – väärtuste paaride arv (meie puhul n=20)

Ülaltoodud avaldis on y i ja ŷ i vaadeldud väärtuste vahekauguste ruudu summa ning seda tähistatakse sageli kui SSE ( Summa kohta Ruuduline Vead (Jäägid), vigade ruudu summa (jääk)) .

Vähimruutude meetod on valida selline rida ŷ = kirves + b, mille jaoks ülaltoodud avaldis võtab minimaalse väärtuse.

Märkus. Iga joon kahemõõtmelises ruumis määratakse üheselt kahe parameetri väärtusega: a (kalle) ja b (nihe).

Arvatakse, et mida väiksem on kauguste ruudu summa, seda paremini vastab vastav joon saadaolevatele andmetele ligikaudselt ja seda saab edasi kasutada y väärtuste ennustamiseks muutuja x põhjal. On selge, et isegi kui tegelikkuses muutujate vahel seos puudub või seos on mittelineaarne, valib OLS ikkagi “parima” rea. Seega ei ütle vähimruutude meetod midagi muutujatevahelise tegeliku seose olemasolu kohta, meetod võimaldab lihtsalt selliseid funktsiooni parameetreid valida a Ja b , mille puhul ülaltoodud avaldis on minimaalne.

Tehes mitte väga keerulisi matemaatilisi tehteid (vt täpsemalt), saate arvutada parameetrid a Ja b :

Nagu valemist näha, parameeter a tähistab kovariatsiooni suhet ja seega MS EXCELIS parameetri arvutamiseks A Võite kasutada järgmisi valemeid (vt Lineaarse lehe näidisfail):

= KOVAR(B26:B45;C26:C45)/ DISP.G(B26:B45) või

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

Samuti parameetri arvutamiseks A võite kasutada valemit = TILT(C26:C45;B26:B45). Parameetri jaoks b kasutage valemit = JAL(C26:C45;B26:B45) .

Lõpuks võimaldab funktsioon LINEST() arvutada mõlemad parameetrid korraga. Valemi sisestamiseks LINEST(C26:C45;B26:B45) Peate valima 2 lahtrit järjest ja klõpsama CTRL + SHIFT + SISESTA(vt artiklit selle kohta). Väärtus tagastatakse vasakpoolses lahtris A , paremal - b .

Märkus: sisendiga segamise vältimiseks massiivi valemid peate lisaks kasutama funktsiooni INDEX(). Valem = INDEX(LINEST(C26:C45,B26:B45),1) või lihtsalt = LINEST(C26:C45;B26:B45) tagastab joone kalde eest vastutava parameetri, st. A . Valem = INDEKS(LINEST(C26:C45,B26:B45),2) tagastab parameetri, mis vastutab sirge ristumiskoha eest Y-teljega, st. b .

Pärast parameetrite arvutamist hajuvusdiagramm saate tõmmata vastava joone.

Teine viis sirge joonistamiseks vähimruutude meetodil on graafiku tööriist Trendijoon. Selleks valige diagramm, valige menüüst Paigutus vahekaart, V rühma analüüs klõpsa Trendijoon, siis Lineaarne lähendus .

Märkides dialoogiboksis kasti "näita võrrandit diagrammil", saate veenduda, et ülaltoodud parameetrid vastavad diagrammil olevatele väärtustele.

Märkus: parameetrite sobitamiseks peab diagrammi tüüp olema . Asi on selles, et diagrammi koostamisel Ajakava X-telje väärtusi ei saa kasutaja määrata (kasutaja saab määrata ainult silte, mis ei mõjuta punktide asukohta). X väärtuste asemel kasutatakse jada 1; 2; 3; ... (kategooriate nummerdamiseks). Seega, kui ehitate trendijoon tüübidiagrammil Ajakava, siis X tegelike väärtuste asemel kasutatakse selle jada väärtusi, mis toob kaasa vale tulemuse (muidugi juhul, kui X tegelikud väärtused ei kattu jadaga 1; 2; 3; ...).

4.1. Sisseehitatud funktsioonide kasutamine

Arvutamine regressioonikoefitsiendid teostatakse funktsiooni abil

LINEST(Väärtused_y; x-väärtused; Konst; statistika),

Väärtused_y- y väärtuste massiiv,

x-väärtused- valikuline väärtuste massiiv x, kui massiiv X jäetakse välja, eeldatakse, et tegemist on massiiviga (1;2;3;...) sama suurusega kui Väärtused_y,

Konst- tõeväärtus, mis näitab, kas konstant on nõutav b oli võrdne 0-ga. Kui Konst küsimustes TÕSI või siis ära jäetud b arvutatakse tavapärasel viisil. Kui argument Konst on siis VALE b eeldatakse, et see on 0 ja väärtused a valitakse nii, et seos oleks täidetud y = kirves.

Statistika on tõeväärtus, mis näitab, kas täiendavat regressioonistatistikat on vaja tagastada. Kui argument Statistika küsimustes TÕSI, siis funktsioon LINEST tagastab täiendava regressioonistatistika. Kui argument Statistika küsimustes VALETA või jäetud välja, siis funktsioon LINEST tagastab ainult koefitsiendi a ja pidev b.

Tuleb meeles pidada, et funktsioonide tulemus LINE() on väärtuste kogum – massiiv.

Arvutamiseks korrelatsioonikordaja funktsiooni kasutatakse

KORREL(Massiiv1;Massiiv2),

korrelatsioonikordaja väärtuste tagastamine, kus Massiiv1- väärtuste hulk y, Massiiv2- väärtuste hulk x. Massiiv1 Ja Massiiv2 peab olema sama suur.

NÄIDE 1. Sõltuvus y(x) on esitatud tabelis. Ehitada regressioonijoon ja arvutada korrelatsioonikordaja.

y		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

Sisestame väärtuste tabeli MS Exceli lehele ja koostame hajuvusdiagrammi. Tööleht on joonisel fig. 2.

Regressioonikoefitsientide väärtuste arvutamiseks A Ja b valige lahtrid A7:B7, Läheme funktsiooniviisardi juurde ja kategooriasse Statistiline valige funktsioon LINEST. Täidame ilmuva dialoogiboksi, nagu on näidatud joonisel fig. 3 ja vajutage OK.

Selle tulemusena kuvatakse arvutatud väärtus ainult lahtris A6(joonis 4). Selleks, et väärtus lahtrisse ilmuks B6 peate sisenema redigeerimisrežiimi (klahv F2) ja seejärel vajutage klahvikombinatsiooni CTRL+SHIFT+ENTER.

Lahtri korrelatsioonikordaja väärtuse arvutamiseks C6 võeti kasutusele järgmine valem:

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

Regressioonikordajate tundmine A Ja b arvutame funktsiooni väärtused y=kirves+b antud eest x. Selleks tutvustame valemit

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

ja kopeerige see vahemikku C5:J5(joonis 5).

Joonistame diagrammile regressioonisirge. Valige graafikul katsepunktid, paremklõpsake ja valige käsk Esialgsed andmed. Ilmuvas dialoogiboksis (joonis 5) valige vahekaart Rida ja klõpsake nuppu Lisa. Täidame sisestusväljad, nagu on näidatud joonisel fig. 6 ja vajutage nuppu OK. Katseandmete graafikule lisatakse regressioonijoon. Vaikimisi joonistatakse selle graafik punktidena, mida ei ühenda silumisjooned.

Riis. 6

Regressioonijoone välimuse muutmiseks toimige järgmiselt. Paremklõpsake joongraafikut kujutavatel punktidel ja valige käsk Diagrammi tüüp ja määrake hajusdiagrammi tüüp, nagu on näidatud joonisel fig. 7.

Joone tüüpi, värvi ja paksust saab muuta järgmiselt. Valige diagrammil rida, paremklõpsake ja valige kontekstimenüüst käsk Andmeseeria vorming... Järgmisena tehke sätted, näiteks nagu näidatud joonisel fig. 8.

Kõigi teisenduste tulemusena saame ühes graafilises piirkonnas katseandmete graafiku ja regressioonijoone (joonis 9).

4.2. Trendijoone kasutamine.

Erinevate ligikaudsete sõltuvuste konstrueerimine MS Excelis on realiseeritud diagrammi atribuudi kujul - trendijoon.

NÄIDE 2. Katse tulemusena määrati teatud tabeli sõltuvus.

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

Valige ja konstrueerige ligikaudne sõltuvus. Tabeliliste ja valitud analüütiliste sõltuvuste graafikute koostamine.

Ülesande lahendamise võib jagada järgmisteks etappideks: algandmete sisestamine, hajuvusdiagrammi konstrueerimine ja trendijoone lisamine sellele graafikule.

Vaatame seda protsessi üksikasjalikult. Sisestame algandmed töölehel ja joonistame katseandmed. Järgmiseks valige graafikul katsepunktid, paremklõpsake ja kasutage käsku Lisa l trendijoon(joonis 10).

Ilmuv dialoogiboks võimaldab teil luua ligikaudse seose.

Selle akna esimene sakk (joonis 11) näitab ligikaudse sõltuvuse tüüpi.

Teisel (joonis 12) määratakse ehitusparameetrid:

· lähendava sõltuvuse nimetus;

· prognoosi edasi (tagasi) võrra n units (see parameeter määrab, mitu ühikut edasi (tagasi) on vaja trendijoont pikendada);

kas näidata kõvera lõikepunkti sirgjoonega y=konst;

· näidata diagrammil ligikaudset funktsiooni või mitte (võimalus näidata diagrammil võrrandit);

· kas panna diagrammile standardhälbe väärtus või mitte (võimalus paigutada diagrammile lähenduskindluse väärtus).

Valime lähendavaks sõltuvuseks teise astme polünoomi (joonis 11) ja kuvame seda polünoomi kirjeldava võrrandi graafikul (joonis 12). Saadud diagramm on näidatud joonisel fig. 13.

Samamoodi kasutades trendijooned saate valida selliste sõltuvuste parameetrid nagu

lineaarne y=a∙x+b,

logaritmiline y=a∙ln(x)+b,

· eksponentsiaalne y=a∙e b,

· rahustav toime y=a∙x b,

polünoom y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+d ja nii edasi kuni 6. astme polünoomini (kaasa arvatud),

· lineaarne filtreerimine.

4.3. Lahendusploki kasutamine

Märkimisväärset huvi pakub MS Excelis parameetrite valimine lahendajaploki abil vähimruutude meetodil. See meetod võimaldab teil valida mis tahes tüüpi funktsiooni parameetreid. Vaatleme seda võimalust, kasutades näitena järgmist ülesannet.

NÄIDE 3. Katse tulemusena saadi sõltuvus z(t), mis on toodud tabelis

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

Valige sõltuvuskoefitsiendid Z(t)=4 +Bt3 +Ct2 +Dt+K vähimruutude meetod.

See ülesanne on samaväärne viie muutuja funktsiooni miinimumi leidmise probleemiga

Vaatleme optimeerimisülesande lahendamise protsessi (joonis 14).

Laske väärtustel A, IN, KOOS, D Ja TO rakkudesse salvestatud A7:E7. Arvutame funktsiooni teoreetilised väärtused Z(t)=Kell 4 +Bt 3 +Ct 2 +Dt+K antud eest t(B2:J2). Selleks lahtris B4 sisestage funktsiooni väärtus esimesse punkti (lahtrisse B2):

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

Kopeerime selle valemi vahemikku C4:J4 ja saada funktsiooni eeldatav väärtus punktides, mille abstsissid on lahtritesse salvestatud B2:J2.

Rakku B5 Tutvustame valemit, mis arvutab katse- ja arvutatud punktide vahe ruudu:

B5=(B4-B3)^2,

ja kopeerige see vahemikku C5:J5. Lahtris F7 salvestame kogu ruudus vea (10). Selleks sisestage valem:

F7 = SUM(B5:J5).

Kasutame käsku Service®Otsige lahendust ja lahendage optimeerimisprobleem piiranguteta. Täidame vastavalt joonisel näidatud dialoogiboksi sisestusväljad. 14 ja vajutage nuppu Käivitage. Kui lahendus leitakse, kuvatakse joonisel fig. 15.

Otsustusploki tulemus väljastatakse lahtritesse A7:E7parameetrite väärtused funktsioonid Z(t)=Kell 4 +Bt 3 +Ct 2 +Dt+K. Rakkudes B4:J4 saame funktsiooni eeldatav väärtus alguspunktides. Lahtris F7 salvestatakse koguruudu viga.

Saate kuvada katsepunkte ja sobitatud joont ühes graafilises piirkonnas, valides vahemiku B2:J4, helista Diagrammi viisard ja seejärel vormindada välimus saadud graafikud.

Riis. 17 kuvab pärast arvutuste tegemist MS Exceli töölehe.

5. VIITED

1. Alekseev E.R., Chesnokova O.V., Arvutusmatemaatika ülesannete lahendamine pakettides Mathcad12, MATLAB7, Maple9. – NT Press, 2006.–596 lk. :il. – (Õpetus)

2. Aleksejev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, inseneri- ja matemaatikaülesannete lahendamine. –M., BINOM, 2008.–260 lk.

3. Berezin I.S., Zhidkov N.P., Arvutusmeetodid – M.: Nauka, 1966. – 632 lk.

4. Garnaev A.Yu., MS EXCELi ja VBA kasutamine majanduses ja rahanduses. – Peterburi: BHV - Peterburi, 1999.–332 lk.

5. Demidovitš B.P., Maron I.A., Šuvalova V.Z., Numbrilised meetodid analüüs – M.: Nauka, 1967. – 368 lk.

6. Korn G., Korn T., Matemaatika käsiraamat teadlastele ja inseneridele – M., 1970, 720 lk.

7. Alekseev E.R., Chesnokova O.V. Juhised rakendamiseks laboritööd MS EXCELIS. Kõigi erialade üliõpilastele. Donetsk, DonNTU, 2004. 112 lk.