Pienimmän neliösumman menetelmä (LSM)

M lineaarisen yhtälön järjestelmällä, jossa on n tuntematonta, on muoto:

Kolme tapausta on mahdollista: m n. Tapausta, jossa m=n tarkasteltiin edellisissä kappaleissa. m

Jos m>n ja järjestelmä on johdonmukainen, niin matriisissa A on vähintään m - n lineaarisesti riippuvaa riviä. Tässä ratkaisu voidaan saada valitsemalla n mitä tahansa lineaarisesti riippumatonta yhtälöä (jos sellaisia ​​on) ja soveltamalla kaavaa X=A -1 CV, eli pelkistämällä ongelma aiemmin ratkaistuksi. Tässä tapauksessa tuloksena oleva ratkaisu täyttää aina loput m - n yhtälöt.

Tietokonetta käytettäessä on kuitenkin kätevämpää käyttää yleisempää lähestymistapaa - pienimmän neliösumman menetelmää.

Algebralliset pienimmän neliöt

Pienimmän neliösumman algebrallinen menetelmä ymmärretään menetelmäksi lineaaristen yhtälöjärjestelmien ratkaisemiseksi

minimoimalla euklidisen normin

Kirves? b? > info. (1.2)

Kokeellinen tietojen analyysi

Tarkastellaanpa jotain kokeilua, jonka aikana ajanhetkellä

esimerkiksi lämpötila Q(t) mitataan. Anna mittaustulokset taulukon avulla

Oletetaan, että kokeen olosuhteet ovat sellaiset, että mittaukset suoritetaan tunnetulla virheellä. Näissä tapauksissa lämpötilan muutoksen Q(t) lakia etsitään käyttämällä jotakin polynomia

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

tuntemattomien kertoimien määrittäminen, ..., siitä näkökulmasta, että yhtälön määrittelemä arvo E(, ...,)

gaussin algebrallinen exel-approksimaatio

otti minimiarvon. Koska neliöiden summa on minimoitu, tätä menetelmää kutsutaan dataan sopiviksi pienimmiksi neliöiksi.

Jos korvaamme P(t):n sen lausekkeella, saamme

Asetetaan tehtäväksi määritellä taulukko siten, että arvo on minimaalinen, ts. määrittää taulukon pienimmän neliösumman menetelmällä. Tätä varten osittaisderivaatat rinnastetaan nollaan:

Jos syötät m × n matriisi A = (), i = 1, 2..., m; j = 1, 2, ..., n, missä

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

silloin kirjallinen tasa-arvo saa muodon

Kirjoitetaan uudelleen kirjattu yhtäläisyys matriisioperaatioina. Määritelmän mukaan meillä on matriisin kertominen sarakkeella

Transponoidulle matriisille samanlainen suhde näyttää tältä

Esitämme seuraavan merkinnän: merkitsemme vektorin Ax i:ntä komponenttia Kirjoitettujen matriisiyhtälöiden mukaisesti meillä on

Matriisimuodossa tämä yhtälö voidaan kirjoittaa uudelleen muotoon

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

Tässä A on suorakaiteen muotoinen m×n matriisi. Lisäksi datan approksimaatioongelmissa yleensä m > n. Yhtälöä (1.3) kutsutaan normaaliyhtälöksi.

Tehtävä oli alusta alkaen mahdollista kirjoittaa ekvivalenttimatriisimuotoon euklidisen vektorin normin avulla:

Tavoitteenamme on minimoida tämä funktio x:ssä. Jotta ratkaisupisteessä saavutettaisiin minimi, tulee ensimmäisten derivaattojen x:n suhteen tässä pisteessä olla nolla. Tämän funktion derivaatat ovat

2A T B + 2A T Ax

ja siksi ratkaisun on täytettävä lineaarinen yhtälöjärjestelmä

(AT A)x = (AT B).

Näitä yhtälöitä kutsutaan normaaliyhtälöiksi. Jos A on m × n -matriisi, niin A>A - n × n on matriisi, ts. normaali yhtälömatriisi on aina neliömatriisi. Lisäksi sillä on positiivisen määrityksen ominaisuus siinä mielessä, että (A>Ax, x) = (Ax, Ax) ? 0.

Kommentti. Joskus (1.3) muotoisen yhtälön ratkaisua kutsutaan ratkaisuksi järjestelmään Ax = B, jossa A on suorakaiteen muotoinen m × n (m > n) matriisi pienimmän neliösumman menetelmällä.

Pienimmän neliösumman ongelma voidaan tulkita graafisesti siten, että se minimoi pystysuorat etäisyydet datapisteistä mallikäyrään (katso kuva 1.1). Tämä ajatus perustuu olettamukseen, että kaikki approksimaatiovirheet vastaavat havaintovirheitä. Jos myös selittävissä muuttujissa on virheitä, saattaa olla tarkoituksenmukaisempaa minimoida euklidinen etäisyys datasta malliin.

OLS Excelissä

Alla oleva algoritmi OLS:n toteuttamiseksi Excelissä olettaa, että kaikki lähtötiedot ovat jo tiedossa. Kerrotaan molemmat järjestelmän matriisiyhtälön AЧX=B osat vasemmalta järjestelmän transponoidulla matriisilla А Т:

A T AX \u003d A T B

Sitten kerrotaan molemmat vasemmanpuoleisen yhtälön osat matriisilla (A T A) -1. Jos tämä matriisi on olemassa, järjestelmä on määritelty. Ottaen huomioon sen tosiasian

(A T A) -1 * (A T A) \u003d E, saamme

X \u003d (A T A) -1 A T B.

Tuloksena oleva matriisiyhtälö on ratkaisu m lineaarisen yhtälön järjestelmään, jossa on n tuntematonta arvolle m>n.

Harkitse yllä olevan algoritmin soveltamista tietyssä esimerkissä.

Esimerkki. Olkoon se tarpeen ratkaista järjestelmä

Excelissä tämän ongelman ratkaisutaulukko kaavan näyttötilassa näyttää tältä:

Laskentatulokset:

Haluttu vektori X sijaitsee alueella E11:E12.

Kun ratkaistaan ​​tiettyä lineaarista yhtälöjärjestelmää, käytettiin seuraavia funktioita:

1. MINUUTI – Palauttaa taulukkoon tallennetun matriisin käänteisarvon.

Syntaksi: NBR(taulukko).

Taulukko on numeerinen taulukko, jossa on sama määrä rivejä ja sarakkeita.

2. MULTIP - palauttaa matriisien tulon (matriisit tallennetaan taulukoihin). Tuloksena on taulukko, jossa on sama määrä rivejä kuin array1 ja sama määrä sarakkeita kuin matriisi2.

Syntaksi: MULT(taulukko1, matriisi2).

Taulukko1, matriisi2 -- kerrotut taulukot.

Kun olet syöttänyt funktion taulukkoalueen vasemman yläkulman soluun, valitse taulukko kaavan sisältävästä solusta alkaen, paina F2-näppäintä ja paina sitten CTRL+SHIFT+ENTER-näppäimiä.

3. TRANSPOSE - muuntaa pystysuoran solujoukon vaakasuuntaiseksi tai päinvastoin. Tämän funktion käytön tulos on taulukko, jonka rivien määrä on yhtä suuri kuin alkuperäisen taulukon sarakkeiden lukumäärä ja sarakkeiden lukumäärä on yhtä suuri kuin alkuperäisen taulukon rivien lukumäärä.

Pienimmän neliösumman menetelmä on matemaattinen menetelmä lineaarisen yhtälön muodostamiseksi, joka vastaa parhaiten kahden numerosarjan joukkoa. Tämän menetelmän tarkoituksena on minimoida kokonaisneliövirhe. Excelissä on työkaluja, joilla tätä menetelmää voidaan käyttää laskelmissa. Katsotaan kuinka se tehdään.

Menetelmän käyttäminen Excelissä

o Ratkaisija-lisäosan käyttöönotto

o Tehtävän ehdot

o Päätös

Menetelmän käyttäminen Excelissä

Pienimmän neliösumman menetelmä (LSM) on matemaattinen kuvaus yhden muuttujan riippuvuudesta toisesta. Sitä voidaan käyttää ennustamiseen.

Ota Ratkaisija-apuohjelma käyttöön

Jotta voit käyttää OLS:ää Excelissä, sinun on otettava apuohjelma käyttöön "Etsi ratkaisua", joka on oletuksena pois käytöstä.

1. Siirry välilehdelle "Tiedosto".

2. Napsauta osion nimeä "Vaihtoehdot".

3. Pysäytä alaosion valinta avautuvassa ikkunassa "Lisäosat".

4. Lohkossa "Ohjaus", joka sijaitsee ikkunan alaosassa, aseta kytkin asentoon "Excel-lisäosat"(jos sillä on eri arvo) ja napsauta painiketta "Mennä...".

5. Pieni ikkuna avautuu. Laita valintamerkki vaihtoehdon viereen "Etsi ratkaisua". Napsauta painiketta OK.

Nyt toiminto Ratkaisun löytäminen Excelissä on aktivoitu, ja sen työkalut näkyvät nauhassa.

Oppitunti: Ratkaisun etsiminen Excelissä

Ongelman olosuhteet

Kuvataanpa LSM:n soveltamista tietyllä esimerkillä. Meillä on kaksi riviä numeroita x Ja y, jonka järjestys näkyy alla olevassa kuvassa.

Tämä riippuvuus voidaan kuvata tarkimmin funktiolla:

Samalla tiedetään, että x=0 v myös tasa-arvoinen 0 . Siksi tätä yhtälöä voidaan kuvata riippuvuudella y=nx.

Meidän on löydettävä erotuksen pienin neliösumma.

Ratkaisu

Jatketaan menetelmän suoran soveltamisen kuvaukseen.

1. Ensimmäisen arvon vasemmalla puolella x laita numero 1 . Tämä on kertoimen ensimmäisen arvon likimääräinen arvo n.

2. Sarakkeen oikealla puolella y lisää toinen sarake nx. Tämän sarakkeen ensimmäiseen soluun kirjoitetaan kaava kertoimen kertomiseksi n ensimmäisen muuttujan soluun x. Samalla teemme linkin kenttään kertoimella absoluuttisesti, koska tämä arvo ei muutu. Napsautamme painiketta Tulla sisään.

3. Kopioi tämä kaava täyttökahvalla alla olevan sarakkeen taulukon koko alueelle.

4. Laskemme erillisessä solussa arvojen neliöiden erojen summan y Ja nx. Voit tehdä tämän napsauttamalla painiketta "Lisää toiminto".

5. Avattu "Ohjattu toiminto" etsimässä sisäänkäyntiä "SUMMKVRAZN". Valitse se ja napsauta painiketta OK.

6. Argumentit-ikkuna avautuu. Kentällä "Matriisi_x" y. Kentällä "Matriisi_y" syötä sarakkeen solualue nx. Syöttääksesi arvot, aseta kohdistin kenttään ja valitse sopiva alue arkilta. Kun olet syöttänyt, napsauta painiketta OK.

7. Siirry välilehdelle "Data". Työkalulaatikon nauhalla "Analyysi" napsauta painiketta "Etsi ratkaisua".

8. Työkalun parametriikkuna avautuu. Kentällä "Optimoi tavoitefunktio" määritä solun osoite kaavalla "SUMMKVRAZN". Parametrissa "Ennen" muista asettaa kytkin asentoon "minimi". Kentällä "Solujen vaihtaminen" määritä osoite kertoimen arvolla n. Napsauta painiketta "Löytää ratkaisu".

9. Ratkaisu näkyy kerroinsolussa n. Tämä arvo on funktion pienin neliö. Jos tulos tyydyttää käyttäjää, napsauta painiketta OK lisäikkunassa.

Kuten näet, pienimmän neliösumman menetelmän soveltaminen on melko monimutkainen matemaattinen menettely. Olemme näyttäneet sen toiminnassa yksinkertaisimmalla esimerkillä, mutta on paljon monimutkaisempia tapauksia. Microsoft Excel -työkalupakki on kuitenkin suunniteltu yksinkertaistamaan laskelmia mahdollisimman paljon.

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

Yleiset määräykset

Mitä pienempi luku itseisarvossa on, sitä parempi suora (2) valitaan. Suoran valinnan tarkkuuden ominaispiirteeksi (2) voidaan ottaa neliöiden summa

S:n vähimmäisehdot ovat

	(6)
	(7)

Yhtälöt (6) ja (7) voidaan kirjoittaa seuraavassa muodossa:

	(8)
	(9)

Yhtälöistä (8) ja (9) on helppo löytää a ja b kokeellisista arvoista x i ja y i . Yhtälöillä (8) ja (9) määriteltyä suoraa (2) kutsutaan pienimmän neliösumman menetelmällä saaduksi suoraksi (tämä nimi korostaa, että neliöiden summalla S on minimi). Yhtälöitä (8) ja (9), joista suora (2) määritetään, kutsutaan normaaliyhtälöiksi.

On mahdollista osoittaa yksinkertainen ja yleinen tapa laatia normaaliyhtälöitä. Koepisteiden (1) ja yhtälön (2) avulla voimme kirjoittaa yhtälöjärjestelmän a:lle ja b:lle

Kerromme kunkin yhtälön vasen ja oikea osa kertoimella ensimmäisessä tuntemattomassa a:ssa (eli x 1 , x 2 , ..., x n) ja lisäämme tuloksena saadut yhtälöt, jolloin saadaan ensimmäinen normaaliyhtälö ( 8).

Kerromme näiden yhtälöiden vasen ja oikea puoli toisen tuntemattoman b:n kertoimella, ts. 1:llä ja lisää tuloksena saadut yhtälöt, jolloin saadaan toinen normaaliyhtälö (9).

Tämä menetelmä normaaliyhtälöiden saamiseksi on yleinen: se sopii esimerkiksi funktiolle

on vakioarvo ja se on määritettävä kokeellisista tiedoista (1).

K:n yhtälöjärjestelmä voidaan kirjoittaa:

Etsi viiva (2) pienimmän neliösumman menetelmällä.

Ratkaisu. Löydämme:

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

Kirjoitamme yhtälöt (8) ja (9)91a+21b=179.1,

21a+6b=46.3, täältä löydämme
a = 0,98 b = 4,3.

4.1. Sisäänrakennettujen toimintojen käyttö

laskeminen regressiokertoimet suoritetaan toiminnolla

LINEST(Arvot_y; Arvot_x; Konst; tilastot),

Arvot_y- joukko y-arvoja,

Arvot_x- valinnainen joukko arvoja x jos joukko X jätetään pois, oletetaan, että tämä on samankokoinen taulukko (1;2;3;...) Arvot_y,

Konst- Boolen arvo, joka osoittaa, vaaditaanko vakio b oli yhtä suuri kuin 0. Jos Konst on merkitys TOTTA tai sitten jätetty pois b lasketaan tavalliseen tapaan. Jos argumentti Konst on siis EPÄTOSI b oletetaan olevan 0 ja arvot a valitaan siten, että suhde y = kirves.

Tilastot- Boolen arvo, joka osoittaa, tarvitaanko lisäregressiotilastojen palauttamista. Jos argumentti Tilastot on merkitys TOTTA, sitten toiminto LINEST palauttaa lisää regressiotilastoja. Jos argumentti Tilastot on merkitys VALEHDELLA tai jätetty pois, sitten toiminto LINEST palauttaa vain kertoimen a ja pysyvä b.

On muistettava, että funktioiden tulos LINEST() on joukko arvoja - matriisi.

Laskemiseen korrelaatiokerroin toimintoa käytetään

CORREL(Taulukko1;Taulukko2),

palauttaa korrelaatiokertoimen arvot, missä Taulukko1- joukko arvoja y, Taulukko2- joukko arvoja x. Taulukko1 Ja Taulukko2 on oltava samankokoinen.

ESIMERKKI 1. Riippuvuus y(x) on esitetty taulukossa. Rakentaa regressioviiva ja laskea korrelaatiokerroin.

Syötetään arvotaulukko MS Excel -taulukkoon ja rakennetaan sirontakaavio. Tehtävätaulukko on kuvan mukaisessa muodossa. 2.

Regressiokertoimien arvojen laskemiseksi A Ja b valitse solut A7:B7, Siirrytään toimintovelhoon ja kategoriaan Tilastollinen valitse toiminto LINEST. Täytä näkyviin tuleva valintaikkuna kuvan 1 mukaisesti. 3 ja paina OK.

Tämän seurauksena laskettu arvo näkyy vain solussa A6(Kuva 4). Arvo näkyy solussa B6 sinun on siirryttävä muokkaustilaan (näppäin F2) ja paina sitten näppäinyhdistelmää CTRL+SHIFT+ENTER.

Korrelaatiokertoimen arvon laskeminen solua kohden C6 otettiin käyttöön seuraava kaava:

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

Regressiokertoimien tunteminen A Ja b laskea funktion arvot y=kirves+b annettuna x. Tätä varten esittelemme kaavan

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

ja kopioi se alueelle С5:J5(Kuva 5).

Piirretään regressioviiva kaavioon. Valitse koepisteet kaaviosta, napsauta hiiren kakkospainikkeella ja valitse komento Alkutiedot. Valitse näkyviin tulevasta valintaikkunasta (kuva 5) välilehti Rivi ja napsauta painiketta Lisätä. Täytä syöttökentät kuvan mukaisesti. 6 ja paina painiketta OK. Regressioviiva lisätään kokeelliseen datakaavioon. Oletuksena sen kaavio näytetään pisteinä, joita ei ole yhdistetty tasoitusviivoilla.

Riisi. 6

Voit muuttaa regressioviivan ulkoasua suorittamalla seuraavat vaiheet. Napsauta hiiren kakkospainikkeella viivakaaviota kuvaavia pisteitä, valitse komento Kaavion tyyppi ja aseta sirontakaavion tyyppi kuvan 1 mukaisesti. 7.

Viivan tyyppiä, väriä ja paksuutta voidaan muuttaa seuraavasti. Valitse kaavion rivi, paina hiiren oikeaa painiketta ja valitse komento pikavalikosta Datasarjan muoto… Tee seuraavaksi asetukset esimerkiksi kuvan 1 mukaisesti. 8.

Kaikkien muunnosten tuloksena saadaan kokeellisen datan kuvaaja ja regressioviiva yhdelle graafiselle alueelle (kuva 9).

4.2. Trendiviivaa käyttämällä.

Erilaisten approksimoivien riippuvuuksien rakentaminen MS Excelissä on toteutettu kaavioominaisuutena - trendiviiva.

ESIMERKKI 2. Kokeen tuloksena määritettiin jonkin verran taulukkoriippuvuutta.

Valitse ja muodosta likimääräinen riippuvuus. Rakenna kaavioita taulukkomuotoisista ja sovitetuista analyyttisistä riippuvuuksista.

Ongelman ratkaisu voidaan jakaa seuraaviin vaiheisiin: lähtötietojen syöttäminen, sirontakuvaajan rakentaminen ja trendiviivan lisääminen tähän kuvaajaan.

Tarkastellaan tätä prosessia yksityiskohtaisesti. Syötetään alkutiedot laskentataulukkoon ja piirretään kokeelliset tiedot. Valitse seuraavaksi kokeelliset pisteet kaaviosta, napsauta hiiren kakkospainikkeella ja käytä komentoa Lisätä l trendiviiva(Kuva 10).

Näyttöön tulevan valintaikkunan avulla voit muodostaa likimääräisen riippuvuuden.

Tämän ikkunan ensimmäinen välilehti (kuva 11) ilmaisee likimääräisen riippuvuuden tyypin.

Toinen (kuva 12) määrittelee rakenneparametrit:

approksimoivan riippuvuuden nimi;

Ennuste eteenpäin (taaksepäin) päällä n units (tämä parametri määrittää, kuinka monta yksikköä eteenpäin (taaksepäin) on tarpeen jatkaa trendiviivaa);

näytetäänkö käyrän ja viivan leikkauspiste y=vakio;

näytetäänkö kaaviossa approksimoiva funktio vai ei (näytä yhtälö kaavioparametrissa);

Laitetaanko kaavioon keskihajonnan arvo vai ei (parametri laittoi kaavioon approksimaatioluotettavuuden arvon).

Valitaan toisen asteen polynomi approksimoivaksi riippuvuudeksi (kuva 11) ja johdetaan tätä polynomia kuvaava yhtälö graafiin (kuva 12). Tuloksena oleva kaavio on esitetty kuvassa. 13.

Samoin kanssa trendilinjoja voit valita tällaisten riippuvuuksien parametrit kuten

lineaarinen y=a∙x+b,

logaritminen y=a ln(x)+b,

eksponentiaalinen y=a∙eb,

tehoa y=a x b,

polynomi y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+d ja niin edelleen, kuudennen asteen polynomiin asti,

Lineaarinen suodatus.

4.3. Deciderin käyttäminen

Merkittävää mielenkiintoa on toteuttaa MS Excelissä parametrien valinta pienimmän neliösumman menetelmällä päätöslohkon avulla. Tämän tekniikan avulla voit valita minkä tahansa funktion parametrit. Tarkastellaan tätä mahdollisuutta seuraavan ongelman esimerkissä.

ESIMERKKI 3. Kokeen tuloksena taulukossa esitetty riippuvuus z(t).

Valitse riippuvuuskertoimet Z(t) = 4:ssä +Bt3 +Ct2 +Dt+K pienimmän neliösumman menetelmällä.

Tämä ongelma vastaa viiden muuttujan funktion minimin löytämisen ongelmaa

Harkitse optimointitehtävän ratkaisuprosessia (kuva 14).

Anna arvot A, SISÄÄN, KANSSA, D Ja TO varastoitu soluihin A7:E7. Laske funktion teoreettiset arvot Z(t)=At4+Bt3+Ct2+Dt+K annettuna t(B2:J2). Voit tehdä tämän solussa B4 syötä funktion arvo ensimmäiseen pisteeseen (solu B2):

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

Kopioi tämä kaava alueelle С4:J4 ja saada funktion odotusarvo pisteistä, joiden abskissat on tallennettu soluihin B2:J2.

soluun B5 otamme käyttöön kaavan, joka laskee kokeellisen ja lasketun pisteen välisen eron neliön:

B5=(B4-B3)^2,

ja kopioi se alueelle С5:J5. Solussa F7 tallennamme neliöllisen kokonaisvirheen (10). Tätä varten otamme käyttöön kaavan:

F7 = SUMMA(B5:J5).

Käytetään komentoa Service®Etsi ratkaisua ja ratkaise optimointiongelma ilman rajoituksia. Täytä sopivat syöttökentät kuvassa 1 näkyvään valintaikkunaan. 14 ja paina painiketta Juosta. Jos ratkaisu löytyy, kuvassa oleva ikkuna. 15.

Päätöslohkon tulos on tulos soluille A7:E7parametrien arvot toimintoja Z(t)=At4+Bt3+Ct2+Dt+K. Soluissa B4:J4 saamme odotettu funktion arvo aloituspisteissä. Solussa F7 säilytetään kokonaisneliövirhe.

Voit näyttää koepisteet ja sovitetun viivan samalla graafisella alueella, jos valitset alueen B2:J4, puhelu Ohjattu kaaviotoiminto ja muotoile sitten tuloksena olevien kaavioiden ulkoasu.

Riisi. 17 näyttää MS Excel -laskentataulukon laskelmien suorittamisen jälkeen.

5. VIITTEET

1. Alekseev E.R., Chesnokova O.V., Laskennallisen matematiikan ongelmien ratkaiseminen paketeissa Mathcad12, MATLAB7, Maple9. – NT Press, 2006.–596s. :ill. – (Opetusohjelma)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, ratkaisee tekniikan ja matemaattisia ongelmia. –M., BINOM, 2008.–260s.

3. I. S. Berezin ja N. P. Zhidkov, Methods of Computation, Moskova: Nauka, 1966.

4. Garnaev A.Yu., MS EXCELIN ja VBA:n käyttö taloustieteessä ja rahoituksessa. - Pietari: BHV - Petersburg, 1999.-332s.

5. B. P. Demidovich, I. A. Maron ja V. Z. Shuvalova, Numerical Methods of Analysis.–M.: Nauka, 1967.–368s.

6. Korn G., Korn T., Matematiikan käsikirja tiedemiehille ja insinööreille. – M., 1970, 720 s.

7. Alekseev E.R., Chesnokova O.V. Ohjeet laboratoriotyön suorittamiseen MS EXCELissä. Kaikkien erikoisalojen opiskelijoille. Donetsk, DonNTU, 2004. 112 s.

Sillä on monia sovelluksia, koska se mahdollistaa likimääräisen esityksen tietystä funktiosta muilla yksinkertaisemmilla. LSM voi olla erittäin hyödyllinen havaintojen käsittelyssä, ja sitä käytetään aktiivisesti arvioimaan joitain suureita toisten satunnaisvirheitä sisältävien mittausten tuloksista. Tässä artikkelissa opit toteuttamaan pienimmän neliösumman laskelmia Excelissä.

Ongelman kuvaus tietyssä esimerkissä

Oletetaan, että on kaksi indikaattoria X ja Y. Lisäksi Y riippuu X:stä. Koska OLS kiinnostaa meitä regressioanalyysin näkökulmasta (Excelissä sen menetelmät toteutetaan sisäänrakennetuilla funktioilla), on ryhdyttävä välittömästi eteenpäin. pohtimaan tiettyä ongelmaa.

Olkoon X siis ruokakaupan myyntipinta-ala neliömetrinä mitattuna ja Y vuosiliikevaihto miljoonissa ruplissa.

On tehtävä ennuste liikevaihdosta (Y), jos sillä on yhtä tai toista myyntitilaa. On selvää, että funktio Y = f (X) kasvaa, koska hypermarket myy enemmän tavaraa kuin kioski.

Muutama sana ennustukseen käytettyjen lähtötietojen oikeellisuudesta

Oletetaan, että meillä on taulukko, joka on rakennettu n myymälän tiedoista.

Matemaattisten tilastojen mukaan tulokset ovat enemmän tai vähemmän oikein, jos tutkitaan vähintään 5-6 kohteen tiedot. Myöskään "poikkeavia" tuloksia ei voida käyttää. Erityisesti eliittipienen putiikin liikevaihto voi olla monta kertaa suurempi kuin "masmarket"-luokan suurten myyntipisteiden liikevaihto.

Menetelmän ydin

Taulukon tiedot voidaan näyttää suorakulmaisessa tasossa pisteinä M 1 (x 1, y 1), ... M n (x n, y n). Nyt tehtävän ratkaisu pelkistetään approksimoivan funktion y = f (x) valintaan, jonka graafi kulkee mahdollisimman läheltä pisteitä M 1, M 2, .. M n .

Tietysti voit käyttää korkean asteen polynomia, mutta tämä vaihtoehto ei ole vain vaikea toteuttaa, vaan se on yksinkertaisesti virheellinen, koska se ei heijasta päätrendiä, joka on havaittava. Järkevin ratkaisu on etsiä suoraa y = ax + b, joka parhaiten approksimoi kokeellisia tietoja ja tarkemmin kertoimia - a ja b.

Tarkkuuspisteet

Jokaisen likiarvon kannalta sen tarkkuuden arviointi on erityisen tärkeää. Merkitse e i:llä pisteen x i toiminnallisten ja kokeellisten arvojen ero (poikkeama), eli e i = y i - f (x i).

On selvää, että arvioidaksesi likiarvon tarkkuutta, voit käyttää poikkeamien summaa, eli kun valitset suoran X:n riippuvuuden likimääräiselle esitykselle Y:stä, etusijalle tulee antaa se, jolla on pienin arvo summa e i kaikissa tarkasteltavissa kohdissa. Kaikki ei kuitenkaan ole niin yksinkertaista, koska positiivisten poikkeamien ohella on käytännössä negatiivisiakin.

Voit ratkaista ongelman käyttämällä poikkeamamoduuleja tai niiden neliöitä. Jälkimmäinen menetelmä on yleisimmin käytetty. Sitä käytetään monilla aloilla, mukaan lukien regressioanalyysi (Excelissä sen toteutus suoritetaan kahdella sisäänrakennetulla funktiolla), ja se on pitkään osoittautunut tehokkaaksi.

Pienimmän neliön menetelmä

Kuten tiedät, Excelissä on sisäänrakennettu automaattinen summaustoiminto, jonka avulla voit laskea kaikkien valitulla alueella sijaitsevien arvojen arvot. Näin ollen mikään ei estä meitä laskemasta lausekkeen arvoa (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

Matemaattisessa merkinnässä tämä näyttää tältä:

Koska päätös tehtiin alun perin likimääräiseksi suoralla viivalla, meillä on:

Siten tehtävä löytää suora, joka parhaiten kuvaa tiettyä X:n ja Y:n välistä suhdetta, merkitsee kahden muuttujan funktion minimin laskemista:

Tämä edellyttää nollan osittaisderivaatta-arvon tasaamista uusien muuttujien a ja b suhteen ja primitiivisen järjestelmän ratkaisemista, joka koostuu kahdesta yhtälöstä, joiden muoto on 2 tuntematonta:

Yksinkertaisten muunnosten jälkeen, mukaan lukien jakaminen kahdella ja summien manipulointi, saamme:

Ratkaisemalla se esimerkiksi Cramerin menetelmällä saamme stationaarisen pisteen tietyillä kertoimilla a * ja b * . Tämä on minimi, eli liikkeen liikevaihdon ennustamiseen tietyllä alueella sopii suora y = a * x + b *, joka on regressiomalli kyseessä olevalle esimerkille. Tietenkään se ei anna sinun löytää tarkkaa tulosta, mutta se auttaa sinua saamaan käsityksen siitä, kannattaako myymälän ostaminen tietylle alueelle luotolla.

Kuinka ottaa pienimmän neliösumman menetelmä käyttöön Excelissä

Excelissä on funktio pienimmän neliösumman arvon laskemiseen. Sillä on seuraava muoto: TREND (tunnetut Y-arvot; tunnetut X-arvot; uudet X-arvot; vakio). Sovelletaan taulukkoomme kaavaa OLS:n laskemiseksi Excelissä.

Tätä varten kirjoita "="-merkki soluun, jossa Excelin pienimmän neliösumman menetelmällä suoritetun laskennan tulos tulee näkyä, ja valitse "TREND"-toiminto. Täytä avautuvassa ikkunassa tarvittavat kentät korostaen:

• Y:n tunnettujen arvojen alue (tässä tapauksessa liikevaihdon tiedot);
• alue x 1 , …x n , eli liiketilan koko;
• ja x:n tunnetut ja tuntemattomat arvot, joille sinun on selvitettävä liikevaihdon koko (lisätietoja niiden sijainnista laskentataulukossa, katso alla).

Lisäksi kaavassa on looginen muuttuja "Const". Jos kirjoitat 1 sitä vastaavaan kenttään, tämä tarkoittaa, että laskelmat on suoritettava olettaen, että b \u003d 0.

Jos haluat tietää ennusteen useammalle kuin yhdelle x-arvolle, kaavan syöttämisen jälkeen sinun ei pitäisi painaa "Enter", vaan sinun on kirjoitettava yhdistelmä "Shift" + "Control" + "Enter" ("Enter" ) näppäimistöllä.

Jotkut ominaisuudet

Regressioanalyysi voi olla jopa nukkejen saatavilla. Excel-kaavaa tuntemattomien muuttujien joukon arvon ennustamiseen - "TREND" - voivat käyttää myös ne, jotka eivät ole koskaan kuulleet pienimmän neliösumman menetelmästä. Riittää, kun tietää joitakin sen työn piirteitä. Erityisesti:

• Jos sijoitat muuttujan y tunnettujen arvojen alueen yhdelle riville tai sarakkeelle, ohjelma havaitsee jokaisen rivin (sarakkeen), jolla on tunnetut x:n arvot, erillisenä muuttujana.
• Jos aluetta tunnetulla x:llä ei ole määritetty TREND-ikkunassa, käytettäessä funktiota Excelissä, ohjelma pitää sitä taulukkona, joka koostuu kokonaisluvuista, joiden lukumäärä vastaa aluetta annetuilla arvoilla. muuttujasta y.
• "Ennustettujen" arvojen taulukon tulostamiseksi trendilauseke on syötettävä taulukkokaavana.
• Jos uusia x-arvoja ei ole määritetty, TREND-funktio pitää niitä yhtä suurena kuin tunnetut arvot. Jos niitä ei ole määritetty, taulukko 1 otetaan argumentiksi; 2; 3; 4;…, joka on verrannollinen jo annettujen parametrien y alueelle.
• Uudet x-arvot sisältävällä alueella on oltava sama tai useampi rivi tai sarake kuin alueella, jolla on annetut y-arvot. Toisin sanoen sen on oltava suhteessa riippumattomiin muuttujiin.
• Taulukko, jossa on tunnetut x-arvot, voi sisältää useita muuttujia. Kuitenkin, jos puhumme vain yhdestä, vaaditaan, että alueet, joilla on annetut x:n ja y:n arvot, ovat oikeassa suhteessa. Useamman muuttujan tapauksessa on välttämätöntä, että alue annetuilla y-arvoilla mahtuu yhteen sarakkeeseen tai yhteen riviin.

ENNUSTE-toiminto

Se toteutetaan useiden toimintojen avulla. Yksi niistä on nimeltään "PREDICTION". Se on samanlainen kuin TREND, eli se antaa laskutoimituksen tuloksen pienimmän neliösumman menetelmällä. Kuitenkin vain yhdelle X:lle, jonka Y:n arvoa ei tunneta.

Nyt tiedät Excel-kaavat tutille, joiden avulla voit ennustaa indikaattorin tulevan arvon arvon lineaarisen trendin mukaan.

Pienimmän neliösumman menetelmä on matemaattinen menetelmä lineaarisen yhtälön muodostamiseksi, joka vastaa parhaiten kahden numerosarjan joukkoa. Tämän menetelmän tarkoituksena on minimoida kokonaisneliövirhe. Excelissä on työkaluja, joilla tätä menetelmää voidaan käyttää laskelmissa. Katsotaan kuinka se tehdään.

Pienimmän neliösumman menetelmä (LSM) on matemaattinen kuvaus yhden muuttujan riippuvuudesta toisesta. Sitä voidaan käyttää ennustamiseen.

Ota Ratkaisija-apuohjelma käyttöön

Jotta voit käyttää OLS:ää Excelissä, sinun on otettava apuohjelma käyttöön "Etsi ratkaisua", joka on oletuksena pois käytöstä.

Nyt toiminto Ratkaisun löytäminen Excelissä on aktivoitu, ja sen työkalut näkyvät nauhassa.

Ongelman olosuhteet

Kuvataanpa LSM:n soveltamista tietyllä esimerkillä. Meillä on kaksi riviä numeroita x Ja y , jonka järjestys näkyy alla olevassa kuvassa.

Tämä riippuvuus voidaan kuvata tarkimmin funktiolla:

Samalla tiedetään, että x=0 y myös tasa-arvoinen 0 . Siksi tätä yhtälöä voidaan kuvata riippuvuudella y=nx .

Meidän on löydettävä erotuksen pienin neliösumma.

Ratkaisu

Jatketaan menetelmän suoran soveltamisen kuvaukseen.

Kuten näet, pienimmän neliösumman menetelmän soveltaminen on melko monimutkainen matemaattinen menettely. Olemme näyttäneet sen toiminnassa yksinkertaisimmalla esimerkillä, mutta on paljon monimutkaisempia tapauksia. Microsoft Excel -työkalupakki on kuitenkin suunniteltu yksinkertaistamaan laskelmia mahdollisimman paljon.