Pienimmän neliösumman menetelmä (LSM) perustuu valitun funktion neliöpoikkeamien summan minimoimiseen tutkittavasta tiedosta. Tässä artikkelissa arvioimme saatavilla olevat tiedot lineaarisen funktion avullay = a x + b .

Pienimmän neliön menetelmä(Englanti) Tavallinen Vähiten Neliöt , OLS) on yksi regressioanalyysin perusmenetelmistä tuntemattomien parametrien arvioinnissa regressiomallit näytetietojen mukaan.

Harkitse funktioiden likiarvoa vain yhdestä muuttujasta riippuen:

• Lineaarinen: y=ax+b (tämä artikkeli)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+c
• : y=ax 2 +bx+c

Huomautus: Tässä artikkelissa tarkastellaan tapauksia, joissa likiarvo on tehty polynomilla 3. ja 6. asteen välillä. Tässä tarkastellaan approksimaatiota trigonometrisellä polynomilla.

Lineaarinen riippuvuus

Olemme kiinnostuneita kahden muuttujan suhteesta X ja y. On olemassa oletus, että y riippuu X lineaarisen lain mukaan y = kirves + b. Tämän suhteen parametrien määrittämiseksi tutkija teki havaintoja: jokaiselle x i:n arvolle tehtiin y i:n mittaus (katso esimerkkitiedosto). Olkoon siis 20 arvoparia (х i ; y i).

Huomautus: Jos muutos askel askeleelta X on jatkuvaa, sitten rakentaa sirontakuvioita voidaan käyttää, jos ei, sinun on käytettävä kaaviotyyppiä pilkullinen .

Kaaviosta käy ilmi, että muuttujien välinen suhde on lähellä lineaarista. Ymmärtääkseen, mikä monista suorista "oikein" kuvaa muuttujien välistä suhdetta, on tarpeen määrittää kriteeri, jolla viivoja verrataan.

Tällaisena kriteerinä käytämme lauseketta:

missä ŷ i = a * x i + b ; n – arvoparien määrä (tässä tapauksessa n=20)

Yllä oleva lauseke on y i:n ja ŷ i:n havaittujen arvojen neliöityjen etäisyyksien summa, ja sitä kutsutaan usein SSE:ksi ( summa / neliöity Virheet (Jäännökset), virheiden neliösumma (jäännösarvot)) .

Pienimmän neliön menetelmä on valita tällainen linja ŷ = kirves + b, jolle yllä oleva lauseke saa vähimmäisarvon.

Huomautus: Mikä tahansa viiva kaksiulotteisessa avaruudessa määräytyy yksiselitteisesti kahden parametrin arvoilla: a (rinne) ja b (siirtää).

Uskotaan, että mitä pienempi on neliöityjen etäisyyksien summa, sitä paremmin vastaava suora likimääräinen käytettävissä oleva data on ja sitä voidaan edelleen käyttää ennustamaan y:n arvoja muuttujasta x. On selvää, että vaikka todellisuudessa muuttujien välillä ei olisi suhdetta tai suhde on epälineaarinen, LSM valitsee silti "parhaan" rivin. Siten LSM ei sano mitään muuttujien todellisen suhteen olemassaolosta, menetelmä antaa sinun yksinkertaisesti valita tällaiset funktion parametrit a ja b , jolle yllä oleva lauseke on minimaalinen.

Kun olet tehnyt ei kovin monimutkaisia ​​matemaattisia operaatioita (katso lisätietoja), voit laskea parametrit a ja b :

Kuten kaavasta voidaan nähdä, parametri a on kovarianssin suhde ja , joten MS EXCELissä parametrin laskemiseksi a Voit käyttää seuraavia kaavoja (katso esimerkkitiedostoarkki Lineaarinen):

= COVAR(B26:B45;C26:C45)/VAR.G(B26:B45) tai

= KOVARIATION.B(B26:B45;C26:C45)/VAR.B(B26:B45)

Myös parametrin laskemiseen a voit käyttää kaavaa = SLOPE(C26:C45;B26:B45). Parametrille b käytä kaavaa = INTERCUT(C26:C45;B26:B45) .

Ja lopuksi, LINEST()-funktio antaa sinun laskea molemmat parametrit kerralla. Kaavan syöttäminen LINEST(C26:C45;B26:B45) valitse 2 solua peräkkäin ja paina CTRL + SIIRTÄÄ + TULLA SISÄÄN(katso artikkeli aiheesta). Vasen solu palauttaa arvon a , oikealla b .

Huomautus: Älä sekoita syötteen kanssa taulukkokaavat sinun on käytettävä lisäksi INDEX()-funktiota. Kaava = INDEKSI(LINEST(C26:C45,B26:B45),1) tai vain = LINEST(C26:C45;B26:B45) palauttaa parametrin, joka vastaa viivan jyrkkyydestä, ts. a . Kaava = INDEKSI(LINEST(C26:C45,B26:B45),2) palauttaa parametrin, joka vastaa suoran leikkauspisteestä Y-akselin kanssa, ts. b .

Kun olet laskenut parametrit, sirontakaavio viiva voidaan vetää.

Toinen tapa piirtää suora viiva pienimmän neliösumman menetelmällä on kaaviotyökalu trendiviiva. Voit tehdä tämän valitsemalla kaavion, valitsemalla valikosta Asettelu-välilehti, sisään ryhmäanalyysi klikkaus trendiviiva, sitten Lineaarinen approksimaatio .

Valitsemalla valintaikkunan "näytä yhtälö kaaviossa" -ruudun, voit varmistaa, että yllä löydetyt parametrit vastaavat kaavion arvoja.

Huomautus: Jotta parametrit täsmäävät, kaavion tyypin on oltava . Tosiasia on, että kaaviota rakennettaessa Ajoittaa Käyttäjä ei voi asettaa x-akselin arvoja (käyttäjä voi määrittää vain tarrat, jotka eivät vaikuta pisteiden sijaintiin). X-arvojen sijasta käytetään sekvenssiä 1; 2; 3; … (luokkanumerointia varten). Siksi, jos rakentaa trendiviiva tyyppikaaviossa Ajoittaa, niin tämän sekvenssin arvoja käytetään X:n todellisten arvojen sijasta, mikä johtaa virheelliseen tulokseen (elleivät tietenkään X:n todelliset arvot vastaa sekvenssiä 1; 2 ; 3; ...).

No, töissä raportoitiin tarkastukseen, artikkeli kirjoitettiin kotona konferenssia varten - nyt voit kirjoittaa blogiin. Käsitellessäni tietojani tajusin, että en voinut olla kirjoittamatta erittäin siististä ja tarpeellisesta Excelin apuohjelmasta, jota kutsutaan nimellä . Joten artikkeli on omistettu tälle lisäosalle, ja kerron sinulle siitä käyttämällä esimerkkiä pienimmän neliösumman menetelmä(LSM) etsimään yhtälön tuntemattomia kertoimia kokeellisen tiedon kuvauksesta.

Kuinka ottaa käyttöön "Hae ratkaisua" -lisäosa

Selvitetään ensin, kuinka tämä lisäosa otetaan käyttöön.

1. Siirry "Tiedosto"-valikkoon ja valitse "Excel-asetukset".

2. Valitse näkyviin tulevasta ikkunasta "Etsi ratkaisua" ja napsauta "go".

3. Laita seuraavassa ikkunassa valintamerkki "hae ratkaisua" -kohdan eteen ja napsauta "OK".

4. Apuohjelma on aktivoitu - nyt se löytyy "Data"-valikkokohdasta.

Pienimmän neliön menetelmä

Nyt lyhyesti aiheesta pienimmän neliösumman menetelmä (LSM) ja missä sitä voidaan soveltaa.

Oletetaan, että meillä on tietojoukko sen jälkeen, kun olemme suorittaneet kokeen, jossa olemme tutkineet X-arvon vaikutuksia Y-arvoon.

Haluamme kuvata tätä vaikutusta matemaattisesti, jotta voimme myöhemmin käyttää tätä kaavaa ja tietää, että jos muutamme X:n arvoa niin paljon, saamme Y:n arvon sellaisella ja sellaisella ...

Otetaanpa erittäin yksinkertainen esimerkki (katso kuva).

Ei aavistustakaan, että pisteet sijaitsevat peräkkäin ikään kuin suorassa linjassa, ja siksi oletamme turvallisesti, että riippuvuutemme kuvataan lineaarisella funktiolla y=kx+b. Samalla olemme varmoja, että kun X on nolla, Y:n arvo on myös nolla. Tämä tarkoittaa, että riippuvuutta kuvaava funktio on vielä yksinkertaisempi: y=kx (muista koulun opetussuunnitelma).

Yleensä meidän on löydettävä kerroin k. Tällä me teemme MNC käyttämällä "Etsi ratkaisua" -lisäosaa.

Menetelmä on (tässä - huomio: sinun täytyy ajatella sitä) kokeellisesti saatujen ja vastaavien laskettujen arvojen välisten neliöerojen summa oli minimaalinen. Eli kun X1=1 todellinen mitattu arvo Y1=4,6 ja laskettu y1=f (x1) on 4, erotuksen neliö on (y1-Y1)^2=(4-4.6)^2= 0,36 . Sama seuraavien kanssa: kun X2=2, todellinen mitattu arvo Y2=8,1 ja laskettu y2 on 8, erotuksen neliö on (y2-Y2)^2=(8-8.1)^2=0.01. Ja kaikkien näiden neliöiden summan tulee olla mahdollisimman pieni.

Joten, aloitetaan koulutus LSM: n käytöstä ja Excelin lisäosat "etsi ratkaisua" .

Lisäosien etsintäratkaisun soveltaminen

1. Jos et ole ottanut "Hae ratkaisua" -laajennusta käyttöön, palaa vaiheeseen Kuinka ottaa käyttöön lisäosa "Hae ratkaisua" ja ottaa käyttöön 🙂

2. Kirjoita soluun A1 arvo "1". Tämä yksikkö on ensimmäinen approksimaatio funktionaalisen riippuvuutemme kertoimen (k) todelliselle arvolle y=kx.

3. Sarakkeessa B on parametrin X arvot, sarakkeessa C - parametrin Y arvot. Sarakkeen D soluihin syötetään kaava: "tekijä k kertaa X:n arvo". Kirjoita esimerkiksi soluun D1 "=A1*B1", soluun D2 "=A1*B2" ja niin edelleen.

4. Uskomme, että kerroin k on yhtä suuri kuin yksi ja funktio f (x) \u003d y \u003d 1 * x on ensimmäinen approksimaatio ratkaisullemme. Voimme laskea Y:n mitattujen arvojen ja kaavalla y=1*x laskettujen arvojen välisten erojen neliösumman. Voimme tehdä kaiken tämän manuaalisesti ohjaamalla asianmukaiset soluviittaukset kaavaan: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... jne. Lopulta me ovat erehtyneet ja ymmärtävät, että olemme menettäneet paljon aikaa. Excelissä neliöerojen summan laskemiseen on erityinen kaava "SUMQDIFF", joka tekee kaiken puolestamme.Syötetään se soluun A2 ja asetetaan alkutiedot: mittausarvojen alue Y (sarake C) ja laskettujen Y-arvojen alue (sarake D).

4. Neliöiden erojen summa laskettiin - mene nyt "Data"-välilehdelle ja valitse "Etsi ratkaisu".

5. Valitse avautuvasta valikosta muutettavaksi soluksi solu A1 (kerroin k).

6. Valitse kohteeksi solu A2 ja aseta ehto "set yhtä kuin vähimmäisarvo". Muista, että tässä solussa lasketaan laskettujen ja mitattujen arvojen neliöerojen summa, ja tämän määrän tulee olla minimaalinen. Painamme "suorita".

7. Kerroin k valitaan. Nyt voidaan nähdä, että lasketut arvot ovat nyt hyvin lähellä mitattuja.

P.S.

Yleensä tietysti kokeellisten tietojen likiarvoa varten Excelissä on erikoistyökaluja, joiden avulla voit kuvata tietoja lineaari-, eksponentiaal-, potenssi- ja polynomifunktiolla, joten voit usein tehdä ilman lisäosat "Etsi ratkaisua". Puhuin kaikista näistä lähentämismenetelmistä artikkelissani, joten jos olet kiinnostunut, katso. Mutta kun on kyse eksoottisista toiminnoista yhdellä tuntemattomalla kertoimella tai optimointiongelmia, niin tässä ylärakenne mahdollisimman hyvin.

Lisäosa "etsi ratkaisua" voidaan käyttää muihin tehtäviin, tärkeintä on ymmärtää ydin: on solu, jossa valitsemme arvon, ja on kohdesolu, jossa on asetettu ehto tuntemattoman parametrin valitsemiselle.
Siinä kaikki! Seuraavassa artikkelissa kerron sadun lomasta, joten jotta et missaa artikkelin julkaisua,

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 VÄÄRÄ 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.

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

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.

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

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.

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

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, sirontakäyrän 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);

Sijoitetaanko keskihajonnan arvo kaavioon vai ei (parametri, jolla asetetaan approksimaation luotettavuuden arvo kaavioon).

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

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. Vaihtoehtojen analyysityökalun käyttö: Ratkaisun löytäminen.

Huomattavan kiinnostava on MS Excelissä toteutettu funktionaalisen riippuvuuden parametrien valinta pienimmän neliösumman menetelmällä käyttämällä optioanalyysityökalua: Etsi ratkaisua. 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).

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

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 MUTTA, AT, Kanssa, D ja Vastaanottaja 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 2 näkyvään valintaikkunaan. 14 ja paina painiketta Juosta. Jos ratkaisu löytyy, kuvassa näkyvä ikkuna. viisitoista.

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, soittaa puhelimella Ohjattu kaaviotoiminto ja muotoile sitten tuloksena olevien kaavioiden ulkoasu.

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

Jolla on laajin sovellus eri tieteen ja käytännön aloilla. Se voi olla fysiikka, kemia, biologia, taloustiede, sosiologia, psykologia ja niin edelleen ja niin edelleen. Kohtalon tahdosta joudun usein käsittelemään taloutta, ja siksi järjestän tänään sinulle lipun hämmästyttävään maahan nimeltä Ekonometria=) … Miten et halua sitä?! Siellä on erittäin hyvä - sinun on vain päätettävä! …Mutta mitä varmasti haluat, on oppia ratkaisemaan ongelmia pienimmän neliösumman. Ja erityisen ahkerat lukijat oppivat ratkaisemaan ne paitsi tarkasti, myös ERITTÄIN NOPEASTI ;-) Mutta ensin yleiskuvaus ongelmasta+ asiaan liittyvä esimerkki:

Tutkitaanko indikaattoreita jollain aihealueella, jolla on määrällinen ilmaisu. Samalla on täysi syy uskoa, että indikaattori riippuu indikaattorista. Tämä oletus voi olla sekä tieteellinen hypoteesi että perustaa terveeseen järkeen. Jätetään kuitenkin tiede sivuun ja tutkitaan herkullisempia alueita - nimittäin ruokakauppoja. Merkitse:

– ruokakaupan liiketila, neliömetri,
- päivittäistavarakaupan vuotuinen liikevaihto, miljoonaa ruplaa.

On aivan selvää, että mitä suurempi myymälän pinta-ala, sitä suurempi on sen liikevaihto useimmissa tapauksissa.

Oletetaan, että havaintojen / kokeiden / laskelmien / tamburiinin kanssa tanssimisen jälkeen meillä on käytettävissämme numeeriset tiedot:

Ruokakaupoissa mielestäni kaikki on selvää: - tämä on 1. myymälän pinta-ala, - sen vuosiliikevaihto, - 2. myymälän pinta-ala, - sen vuosiliikevaihto jne. Muuten, turvaluokiteltuihin aineistoihin pääsy ei ole ollenkaan välttämätöntä - melko tarkka arvio liikevaihdosta voidaan saada käyttämällä matemaattiset tilastot. Älä kuitenkaan ole hajamielinen, kaupallisen vakoilun kurssi on jo maksettu =)

Taulukkotiedot voidaan kirjoittaa myös pisteiden muodossa ja kuvata meille tavalliseen tapaan. Karteesinen järjestelmä .

Vastataanpa tärkeään kysymykseen: kuinka monta pistettä tarvitaan laadulliseen tutkimukseen?

Mitä isompi sen parempi. Pienin sallittu sarja koostuu 5-6 pisteestä. Lisäksi pienellä tietomäärällä "epänormaalia" tuloksia ei pitäisi sisällyttää otokseen. Joten esimerkiksi pieni eliittikauppa voi auttaa suuruusluokkaa enemmän kuin "kollegansa", mikä vääristää yleistä mallia, joka on löydettävä!

Jos se on melko yksinkertaista, meidän on valittava funktio, ajoittaa joka kulkee mahdollisimman läheltä pisteitä . Tällaista funktiota kutsutaan likimääräinen (likiarvo - likiarvo) tai teoreettinen toiminto . Yleisesti ottaen tässä näkyy heti ilmeinen "teeskelija" - korkean asteen polynomi, jonka kaavio kulkee KAIKKI pisteet. Mutta tämä vaihtoehto on monimutkainen ja usein yksinkertaisesti väärä. (koska kaavio "tuulee" koko ajan ja heijastaa huonosti päätrendiä).

Näin ollen halutun funktion tulee olla riittävän yksinkertainen ja samalla heijastaa riippuvuutta riittävästi. Kuten arvata saattaa, yksi tällaisten funktioiden löytämismenetelmistä on nimeltään pienimmän neliösumman. Analysoidaan ensin sen olemusta yleisellä tavalla. Olkoon jonkin funktion likimääräinen kokeellinen data:


Kuinka arvioida tämän likiarvon tarkkuus? Lasketaan myös erot (poikkeamat) kokeellisten ja toiminnallisten arvojen välillä (tutkimme piirustusta). Ensimmäinen ajatus, joka tulee mieleen, on arvioida summan suuruus, mutta ongelmana on, että erot voivat olla negatiivisia. (Esimerkiksi, ) ja tällaisesta summauksesta johtuvat poikkeamat kumoavat toisensa. Siksi se ehdottaa ottavan summan arviona likiarvon tarkkuudesta moduulit poikkeamat:

tai taitettuna: (yhtäkkiä, kuka ei tiedä: on summakuvake ja apumuuttuja - "laskuri", joka ottaa arvot välillä 1 - ).

Approksimoimalla koepisteitä eri funktioilla saamme erilaisia ​​arvoja, ja on selvää, että missä tämä summa on pienempi, se funktio on tarkempi.

Tällainen menetelmä on olemassa ja sitä kutsutaan pienimmän moduulin menetelmä. Käytännössä se on kuitenkin yleistynyt huomattavasti. pienimmän neliösumman menetelmä, jossa mahdollisia negatiivisia arvoja ei eliminoida moduulilla, vaan neliöimällä poikkeamat:

, jonka jälkeen ponnistelut suuntautuvat sellaisen funktion valintaan, että neliöityjen poikkeamien summa oli mahdollisimman pieni. Itse asiassa, tästä menetelmän nimi.

Ja nyt palaamme toiseen tärkeään kohtaan: kuten yllä todettiin, valitun toiminnon tulisi olla melko yksinkertainen - mutta myös sellaisia ​​​​toimintoja on monia: lineaarinen , hyperbolinen, eksponentiaalinen, logaritminen, neliöllinen jne. Ja tietysti täällä haluaisin heti "vähentää toimintakenttää". Mikä funktioluokka valita tutkimukseen? Alkukantainen mutta tehokas tekniikka:

- Helpoin tapa nostaa pisteitä piirustukseen ja analysoida niiden sijaintia. Jos ne ovat yleensä suorassa linjassa, sinun tulee etsiä suora yhtälö optimaalisilla arvoilla ja . Toisin sanoen tehtävänä on löytää SELLAISIA kertoimia - niin, että neliöityjen poikkeamien summa on pienin.

Jos pisteet sijaitsevat esimerkiksi pitkin hyperbolia, silloin on selvää, että lineaarinen funktio antaa huonon approksimation. Tässä tapauksessa etsimme "suotuisimpia" kertoimia hyperboliyhtälölle - ne, jotka antavat pienimmän neliösumman .

Huomaa nyt, että molemmissa tapauksissa puhumme kahden muuttujan funktioita, jonka argumentit ovat etsii riippuvuusvaihtoehtoja:

Ja pohjimmiltaan meidän on ratkaistava standardiongelma - löytää vähintään kahden muuttujan funktio.

Muista esimerkkimme: oletetaan, että "myymälä"-pisteet sijaitsevat yleensä suorassa linjassa ja on täysi syy uskoa niiden olemassaolo lineaarinen riippuvuus liikevaihtoa kauppa-alueelta. Etsitään SELLAISIA kertoimia "a" ja "olla" niin, että neliöpoikkeamien summa oli pienin. Kaikki tavalliseen tapaan - ensin ensimmäisen asteen osittaiset johdannaiset. Mukaan lineaarisuussääntö voit erotella suoraan summakuvakkeen alla:

Jos haluat käyttää näitä tietoja esseessä tai kurssityössä, olen erittäin kiitollinen linkistä lähdeluettelossa, et löydä tällaisia ​​yksityiskohtaisia ​​laskelmia mistään:

Tehdään vakiojärjestelmä:

Vähennämme kutakin yhtälöä "kahdella" ja lisäksi "rikotaan" summat:

Huomautus : analysoi itsenäisesti, miksi "a" ja "be" voidaan poistaa summakuvakkeesta. Muuten, muodollisesti tämä voidaan tehdä summalla

Kirjoitetaan järjestelmä uudelleen "soveltuun" muotoon:

jonka jälkeen alkaa piirtää algoritmi ongelmamme ratkaisemiseksi:

Tiedämmekö pisteiden koordinaatit? Me tiedämme. Summat voimmeko löytää? Helposti. Koostamme yksinkertaisimman kahden lineaarisen yhtälön järjestelmä, jossa on kaksi tuntematonta("a" ja "beh"). Ratkaisemme järjestelmän mm. Cramerin menetelmä, jolloin tuloksena on paikallaan oleva piste . Tarkistetaan riittävä kunto ääripäälle, voimme varmistaa, että tässä vaiheessa funktio tavoittaa tarkasti minimi. Vahvistamiseen liittyy lisälaskelmia, joten jätämme sen kulissien taakse. (tarvittaessa puuttuvaa kehystä voi katsoa). Teemme lopullisen johtopäätöksen:

Toiminto paras tapa (ainakin verrattuna muihin lineaarisiin funktioihin) tuo koepisteitä lähemmäksi . Karkeasti ottaen sen kaavio kulkee mahdollisimman läheltä näitä pisteitä. Perinteessä ekonometria kutsutaan myös tuloksena olevaa approksimointifunktiota parillinen lineaarinen regressioyhtälö .

Käsiteltävänä olevalla ongelmalla on suuri käytännön merkitys. Esimerkkimme tilanteessa yhtälö voit ennustaa millaista liikevaihtoa ("yig") tulee olemaan myymälässä jollakin myyntialueen arvolla (yksi tai toinen "x":n merkitys). Kyllä, tuloksena oleva ennuste on vain ennuste, mutta monissa tapauksissa se osoittautuu melko tarkkaksi.

Analysoin vain yhden ongelman "oikeilla" numeroilla, koska siinä ei ole vaikeuksia - kaikki laskelmat ovat koulun opetussuunnitelman tasolla luokilla 7-8. 95 prosentissa tapauksista sinua pyydetään löytämään vain lineaarinen funktio, mutta aivan artikkelin lopussa osoitan, että optimaalisen hyperbelin, eksponentin ja joidenkin muiden funktioiden yhtälöiden löytäminen ei ole enää vaikeampaa.

Itse asiassa on vain jaettava luvatut herkut - jotta opit ratkaisemaan tällaiset esimerkit paitsi tarkasti, myös nopeasti. Tutkimme standardia huolellisesti:

Tehtävä

Kahden indikaattorin välisen suhteen tutkimisen tuloksena saatiin seuraavat numeroparit:

Etsi pienimmän neliösumman menetelmällä lineaarinen funktio, joka parhaiten approksimoi empiiristä (kokenut) tiedot. Piirrä piirustus, jolle suorakulmaisessa suorakulmaisessa koordinaattijärjestelmässä piirrä kokeelliset pisteet ja kuvaaja approksimoivasta funktiosta . Laske empiiristen ja teoreettisten arvojen neliöityjen poikkeamien summa. Selvitä, onko toiminto parempi (pienimpien neliöiden menetelmällä) likimääräiset koepisteet.

Huomaa, että "x"-arvot ovat luonnollisia arvoja, ja tällä on tyypillinen merkityksellinen merkitys, josta puhun hieman myöhemmin; mutta ne voivat tietysti olla murto-osia. Lisäksi tietyn tehtävän sisällöstä riippuen sekä "X"- että "G"-arvot voivat olla täysin tai osittain negatiivisia. No, meille on annettu "kasvoton" tehtävä, ja aloitamme sen päätös:

Löydämme optimaalisen funktion kertoimet ratkaisuksi järjestelmään:

Pienemmän merkinnän vuoksi "laskuri"-muuttuja voidaan jättää pois, koska on jo selvää, että summaus suoritetaan 1:stä .

Tarvittavat määrät on helpompi laskea taulukkomuodossa:


Laskelmat voidaan suorittaa mikrolaskimella, mutta on paljon parempi käyttää Exceliä - sekä nopeammin että ilman virheitä; katso lyhyt video:

Siten saamme seuraavan järjestelmä:

Täällä voit kertoa toisen yhtälön 3:lla ja vähennä 2. 1. yhtälöstä termi kerrallaan. Mutta tämä on onnea - käytännössä järjestelmät eivät usein ole lahjakkaita, ja sellaisissa tapauksissa se säästää Cramerin menetelmä:
, joten järjestelmällä on ainutlaatuinen ratkaisu.

Tehdään tarkistus. Ymmärrän, että en halua, mutta miksi ohittaa virheet, joissa et voi kerta kaikkiaan missata niitä? Korvaa löydetty ratkaisu järjestelmän kunkin yhtälön vasempaan reunaan:

Vastaavien yhtälöiden oikeat osat saadaan, mikä tarkoittaa, että järjestelmä on ratkaistu oikein.

Siten haluttu approksimoiva funktio: – alkaen kaikki lineaariset funktiot kokeellinen data on parhaiten arvioitu sen avulla.

Toisin kuin suoraan myymälän liikevaihdon riippuvuus sen pinta-alasta, todettu riippuvuus on käänteinen (periaate "mitä enemmän - sitä vähemmän"), ja tämä tosiasia paljastuu välittömästi negatiivisesti kulmakerroin. Toiminto ilmoittaa meille, että kun tiettyä indikaattoria lisätään 1 yksiköllä, riippuvan indikaattorin arvo pienenee keskiverto 0,65 yksiköllä. Kuten sanotaan, mitä korkeampi tattari maksaa, sitä vähemmän myydään.

Approksimoivan funktion piirtämiseksi löydämme kaksi sen arvoista:

ja suorita piirustus:


Rakennettua linjaa kutsutaan trendiviiva (eli lineaarinen trendiviiva, eli yleisessä tapauksessa trendi ei välttämättä ole suora). Kaikille on tuttu ilmaus "olla trendissä", eikä tämä termi mielestäni kaipaa lisäkommentteja.

Laske poikkeamien neliösumma empiiristen ja teoreettisten arvojen välillä. Geometrisesti tämä on "purinpunaisten" segmenttien pituuksien neliöiden summa (joista kaksi on niin pieniä, ettet edes näe niitä).

Tehdään laskelmat yhteenvetona taulukkoon:


Ne voidaan jälleen suorittaa manuaalisesti, jos annan esimerkin 1. kohdasta:

mutta paljon tehokkaampaa on tehdä jo tunnetulla tavalla:

Toistetaan: mikä on tuloksen merkitys? From kaikki lineaariset funktiot toiminto eksponentti on pienin, eli se on perheensä paras approksimaatio. Ja tässä, muuten, ongelman viimeinen kysymys ei ole sattumaa: entä jos ehdotettu eksponentiaalinen funktio olisiko parempi arvioida koepisteitä?

Etsitään vastaava neliöpoikkeamien summa - erottaakseni ne, merkitsen ne kirjaimella "epsilon". Tekniikka on täsmälleen sama:


Ja jälleen jokaiselle palolaskelmalle 1. pisteelle:

Excelissä käytämme vakiofunktiota EXP (Syntaksi löytyy Excelin ohjeesta).

Johtopäätös: , joten eksponentiaalinen funktio approkimoi koepisteitä huonommin kuin suora .

Mutta tässä on huomattava, että "huonompi" on ei tarkoita vielä, mikä hätänä. Nyt rakensin kaavion tästä eksponentiaalisesta funktiosta - ja se myös kulkee lähellä pisteitä - niin paljon, että ilman analyyttistä tutkimusta on vaikea sanoa, kumpi funktio on tarkempi.

Tämä täydentää ratkaisun, ja palaan kysymykseen väitteen luonnollisista arvoista. Erilaisissa tutkimuksissa yleensä taloudelliset tai sosiologiset, kuukaudet, vuodet tai muut vastaavat aikavälit on numeroitu luonnollisella "X":llä. Harkitse esimerkiksi tällaista ongelmaa.

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ämä yhtälö voidaan kuvata riippuvuudella y=nx.

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

Päätös

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 osoittaneet 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

y 1 \u003d ax 1 + b,
y2=ax2+b,...		(10)
yn=axn+b,

Kerro kunkin yhtälön vasen ja oikea osa kertoimella ensimmäisessä tuntemattomassa a:ssa (eli x 1 , x 2 , ..., x n) ja lisää 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ä.

Päätös. 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.