Metoda celor mai mici pătrate (LSM) se bazează pe minimizarea sumei abaterilor pătrate ale funcției selectate din datele studiate. În acest articol, aproximăm datele disponibile folosind o funcție liniarăy = A X + b .

Metoda celor mai mici pătrate(Engleză) Comun Cel mai puţin Pătrate , OLS) este una dintre metodele de bază ale analizei regresiei în ceea ce privește estimarea parametrilor necunoscuți modele de regresie conform datelor eșantionului.

Luați în considerare aproximarea prin funcții în funcție de o singură variabilă:

• Linear: y=ax+b (acest articol)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+c
• : y=ax 2 +bx+c

Notă: Cazurile de aproximare printr-un polinom de la gradul 3 până la gradul 6 sunt luate în considerare în acest articol. Aici se consideră aproximarea printr-un polinom trigonometric.

Dependență liniară

Suntem interesați de relația a 2 variabile Xși y. Există o presupunere că y depinde de X conform legii liniare y = topor + b. Pentru a determina parametrii acestei relații, cercetătorul a făcut observații: pentru fiecare valoare a lui x i s-a făcut o măsurare a lui y i (vezi fișierul exemplu). În consecință, să fie 20 de perechi de valori (х i ; y i).

Notă: Dacă schimbarea pas cu pas X este constantă, apoi să construiască diagrame de dispersie poate fi folosit, dacă nu, atunci trebuie să utilizați tipul de diagramă punctat .

Din diagramă este evident că relația dintre variabile este apropiată de liniară. Pentru a înțelege care dintre numeroasele drepte descrie cel mai „corect” relația dintre variabile, este necesar să se determine criteriul după care vor fi comparate liniile.

Ca atare criteriu, folosim expresia:

Unde ŷ i = A * x i + b ; n – numărul de perechi de valori (în cazul nostru n=20)

Expresia de mai sus este suma distanțelor pătrate dintre valorile observate ale lui y i și ŷ i și este adesea notat ca SSE ( sumă de pătrat Erori (Reziduuri), suma erorilor pătrate (reziduale)) .

Metoda celor mai mici pătrate este să selectezi o astfel de linie ŷ = topor + b, pentru care expresia de mai sus ia valoarea minimă.

Notă: Orice linie din spațiul bidimensional este determinată în mod unic de valorile a 2 parametri: A (pantă) și b (schimb).

Se crede că cu cât suma distanțelor pătrate este mai mică, cu atât linia corespunzătoare aproximează mai bine datele disponibile și poate fi folosită în continuare pentru a prezice valorile lui y din variabila x. Este clar că, chiar dacă în realitate nu există o relație între variabile sau relația este neliniară, atunci cele mai mici pătrate vor selecta totuși linia „cea mai bună”. Astfel, LSM nu spune nimic despre prezența unei relații reale de variabile, metoda pur și simplu vă permite să alegeți astfel de parametri ai funcției A și b , pentru care expresia de mai sus este minimă.

După ce ați efectuat operații matematice nu foarte complexe (a se vedea pentru mai multe detalii), puteți calcula parametrii A și b :

După cum se vede din formulă, parametrul A este raportul de covarianță și , deci în MS EXCEL pentru a calcula parametrul A Puteți folosi următoarele formule (vezi exemplu de fișă de fișier Linear):

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

= COVARIAȚIE.B(B26:B45;C26:C45)/VAR.B(B26:B45)

De asemenea, pentru a calcula parametrul A puteți folosi formula = PANTĂ(C26:C45;B26:B45). Pentru parametru b utilizați formula = INTERCUT(C26:C45;B26:B45) .

Și, în sfârșit, funcția LINEST() vă permite să calculați ambii parametrii simultan. Pentru a introduce o formulă LINIE(C26:C45;B26:B45) selectați 2 celule la rând și apăsați CTRL + SCHIMB + INTRODUCE(vezi articolul despre). Celula din stânga va returna valoarea A , pe dreapta b .

Notă: Pentru a nu te încurca cu intrarea formule matrice va trebui să utilizați suplimentar funcția INDEX(). Formula = INDEX(LINĂ(C26:C45;B26:B45),1) sau doar = LINIE(C26:C45;B26:B45) va returna parametrul responsabil pentru panta dreptei, i.e. A . Formula = INDEX(LINĂ(C26:C45;B26:B45),2) va returna parametrul responsabil pentru intersectia liniei cu axa Y, i.e. b .

După calcularea parametrilor, diagramă de dispersie se poate trasa linie.

O altă modalitate de a desena o linie dreaptă folosind metoda celor mai mici pătrate este instrumentul diagramă linie de tendință. Pentru a face acest lucru, selectați diagrama, selectați din meniu fila Aspect, în Analiza de grup clic linie de tendință, apoi Aproximație liniară .

Bifând caseta „afișați ecuația în diagramă” din caseta de dialog, vă puteți asigura că parametrii găsiți mai sus se potrivesc cu valorile din diagramă.

Notă: Pentru ca parametrii să se potrivească, tipul diagramei trebuie să fie . Faptul este că atunci când construiești o diagramă Programa Valorile axei x nu pot fi setate de utilizator (utilizatorul poate specifica doar etichete care nu afectează locația punctelor). În loc de valorile X, se utilizează secvența 1; 2; 3; … (pentru numerotarea categoriilor). Prin urmare, dacă se construiește linie de tendință pe diagrama de tip Programa, atunci valorile acestei secvențe vor fi folosite în locul valorilor reale ale lui X, ceea ce va duce la un rezultat incorect (cu excepția cazului în care, desigur, valorile reale ale lui X nu se potrivesc cu secvența 1; 2 ; 3; ...).

Ei bine, la locul de muncă au raportat la inspecție, articolul a fost scris acasă pentru conferință - acum puteți scrie pe blog. În timp ce procesam datele, mi-am dat seama că nu mă puteam abține să nu scriu despre un program de completare foarte cool și necesar în Excel, care se numește . Deci articolul va fi dedicat acestui program de completare și vă voi spune despre el folosind un exemplu de utilizare metoda celor mai mici pătrate(LSM) pentru a căuta coeficienți necunoscuți ai ecuației în descrierea datelor experimentale.

Cum să activați suplimentul „căutați o soluție”

Mai întâi, să ne dăm seama cum să activăm acest supliment.

1. Accesați meniul „Fișier” și selectați „Opțiuni Excel”

2. În fereastra care apare, selectați „Search for a solution” și faceți clic pe „go”.

3. În fereastra următoare, puneți o bifă în fața elementului „căutare soluție” și faceți clic pe „OK”.

4. Suplimentul este activat - acum poate fi găsit în elementul de meniu „Date”.

Metoda celor mai mici pătrate

Acum pe scurt despre metoda celor mai mici pătrate (LSM) și unde poate fi aplicat.

Să presupunem că avem un set de date după ce am efectuat un experiment în care am studiat efectele valorii X asupra valorii Y.

Vrem să descriem această influență matematic, astfel încât mai târziu să putem folosi această formulă și să știm că dacă schimbăm valoarea lui X cu atât de mult, vom obține valoarea lui Y așa și așa...

Să luăm un exemplu super-simplu (vezi imaginea).

Nu există nicio idee că punctele sunt situate unul după altul ca într-o linie dreaptă și, prin urmare, presupunem cu siguranță că dependența noastră este descrisă de o funcție liniară y=kx+b. În același timp, suntem siguri că atunci când X este egal cu zero, valoarea lui Y este, de asemenea, egală cu zero. Aceasta înseamnă că funcția care descrie dependența va fi și mai simplă: y=kx (rețineți programa școlară).

În general, trebuie să găsim coeficientul k. Cu asta vom face MNC folosind extensia „căutare soluție”.

Metoda este să (aici - atenție: trebuie să vă gândiți la asta) suma diferențelor pătrate dintre valorile obținute experimental și cele calculate corespunzătoare a fost minimă. Adică, când X1=1 valoarea măsurată reală Y1=4,6, iar y1=f (x1) calculată este 4, pătratul diferenței va fi (y1-Y1)^2=(4-4,6)^2= 0,36 . La fel cu următoarele: când X2=2, valoarea măsurată reală Y2=8,1, iar y2 calculată este 8, pătratul diferenței va fi (y2-Y2)^2=(8-8.1)^2=0.01. Și suma tuturor acestor pătrate ar trebui să fie cât mai mică posibil.

Deci, să începem antrenamentele despre utilizarea LSM și Programe de completare Excel „căutare soluție” .

Aplicarea soluției de găsire a suplimentului

1. Dacă nu ați activat suplimentul „căutare soluție”, apoi reveniți la pasul Cum să activați suplimentul „căutați o soluție” și să activați 🙂

2. În celula A1, introduceți valoarea „1”. Această unitate va fi prima aproximare a valorii reale a coeficientului (k) a dependenței noastre funcționale y=kx.

3. În coloana B avem valorile parametrului X, în coloana C - valorile parametrului Y. În celulele coloanei D introducem formula: „factorul k ori valoarea lui X”. De exemplu, în celula D1, introduceți „=A1*B1”, în celula D2, introduceți „=A1*B2”, și așa mai departe.

4. Credem că coeficientul k este egal cu unu și funcția f (x) \u003d y \u003d 1 * x este prima aproximare a soluției noastre. Putem calcula suma diferențelor pătrate dintre valorile măsurate ale lui Y și cele calculate folosind formula y=1*x. Putem face toate acestea manual introducând referințele de celule corespunzătoare în formula: „=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... etc. În final, vom greșim și înțelegem că am pierdut mult timp. În Excel, pentru calcularea sumei diferențelor pătrate, există o formulă specială, „SUMQDIFF”, care va face totul pentru noi. Să o introducem în celula A2 și să setăm date inițiale: intervalul valorilor măsurate Y (coloana C) și intervalul valorilor Y calculate (coloana D).

4. A fost calculată suma diferențelor pătratelor - acum accesați fila „Date” și selectați „Căutare soluție”.

5. In meniul care apare, selectati celula A1 ca celula de schimbat (cea cu coeficientul k).

6. Ca țintă, selectați celula A2 și setați condiția „setat egal cu valoarea minimă”. Rețineți că aceasta este celula în care calculăm suma diferențelor pătrate dintre valorile calculate și măsurate, iar această sumă ar trebui să fie minimă. Apăsăm pe „execută”.

7. Se selectează coeficientul k. Acum se poate observa că valorile calculate sunt acum foarte apropiate de cele măsurate.

P.S.

În general, desigur, pentru aproximarea datelor experimentale în Excel, există instrumente speciale care vă permit să descrieți datele folosind o funcție liniară, exponențială, de putere și polinomială, astfel încât să puteți face adesea fără suplimente „Căutați o soluție”. Despre toate aceste metode de aproximare am vorbit în articolul meu, așa că dacă ești interesat, aruncă o privire. Dar când vine vorba de o funcție exotică cu un coeficient necunoscut sau probleme de optimizare, atunci aici suprastructură cât mai bine posibil.

Supliment „căutați o soluție” poate fi folosit pentru alte sarcini, principalul lucru este să înțelegem esența: există o celulă în care selectăm o valoare și există o celulă țintă în care este setată o condiție pentru selectarea unui parametru necunoscut.
Asta e tot! În următorul articol voi spune un basm despre o vacanță, așa că pentru a nu rata lansarea articolului,

4.1. Utilizarea funcțiilor încorporate

calcul coeficienții de regresie efectuate cu ajutorul funcției

LINIST(Valori_y; Valori_x; Konst; statistici),

Valori_y- matrice de valori y,

Valori_x- matrice opțională de valori X dacă matrice X omisă, se presupune că aceasta este o matrice (1;2;3;...) de aceeași dimensiune ca și Valori_y,

Konst- o valoare booleană care indică dacă constanta este necesară b a fost egal cu 0. Dacă Konst are sensul ADEVĂRAT sau omis, atunci b calculată în mod obișnuit. Dacă argumentul Konst atunci este FALS b se presupune că este 0 și valorile A sunt alese astfel încât relaţia y=ax.

Statistici- o valoare booleană care indică dacă trebuie returnate statistici suplimentare de regresie. Dacă argumentul Statistici are sensul ADEVĂRAT, apoi funcția LINIST returnează statistici suplimentare de regresie. Dacă argumentul Statistici are sensul FALS sau omis, apoi funcția LINIST returnează doar coeficientul A si permanenta b.

Trebuie amintit că rezultatul funcțiilor LINEA() este un set de valori - o matrice.

Pentru calcul coeficient de corelație funcția este utilizată

CORREL(Matrice1;Matrice 2),

returnând valorile coeficientului de corelație, unde Matrice1- matrice de valori y, Matrice 2- matrice de valori X. Matrice1și Matrice 2 trebuie să aibă aceeași dimensiune.

EXEMPLUL 1. Dependenta y(X) este prezentată în tabel. Construi linie de regresie si calculeaza coeficient de corelație.

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

Să introducem un tabel de valori în foaia MS Excel și să construim un grafic de dispersie. Foaia de lucru va lua forma prezentată în Fig. 2.

Pentru a calcula valorile coeficienților de regresie Ași b selectați celule A7:B7, să trecem la vrăjitorul de funcții și în categorie Statistic alegeți o funcție LINIST. Completați caseta de dialog care apare așa cum se arată în Fig. 3 și apăsați Bine.

Ca rezultat, valoarea calculată va apărea numai în celulă A6(Fig. 4). Pentru ca o valoare să apară într-o celulă B6 trebuie să intrați în modul de editare (tasta F2) apoi apăsați combinația de taste CTRL+SHIFT+ENTER.

Pentru a calcula valoarea coeficientului de corelație per celulă C6 a fost introdusă următoarea formulă:

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

Cunoașterea coeficienților de regresie Ași b calculați valorile funcției y=topor+b pentru dat X. Pentru a face acest lucru, introducem formula

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

și copiați-l în interval С5:J5(Fig. 5).

Să trasăm linia de regresie pe diagramă. Selectați punctele experimentale de pe diagramă, faceți clic dreapta și selectați comanda Datele inițiale. În caseta de dialog care apare (Fig. 5), selectați fila Rândși faceți clic pe butonul Adăuga. Completați câmpurile de introducere, așa cum se arată în Fig. 6 și apăsați butonul Bine. O linie de regresie va fi adăugată la graficul de date experimentale. În mod implicit, graficul său va fi afișat ca puncte neconectate prin linii de netezire.

Pentru a modifica aspectul liniei de regresie, efectuați următorii pași. Faceți clic dreapta pe punctele care descriu graficul liniilor, selectați comanda Tipul graficuluiși setați tipul de diagramă de dispersie, așa cum se arată în Fig. 7.

Tipul liniei, culoarea și grosimea pot fi modificate după cum urmează. Selectați linia de pe diagramă, apăsați butonul dreapta al mouse-ului și selectați comanda din meniul contextual Format serie de date... Apoi, faceți setările, de exemplu, așa cum se arată în Fig. opt.

Ca rezultat al tuturor transformărilor, obținem un grafic al datelor experimentale și o linie de regresie într-o zonă grafică (Fig. 9).

4.2. Folosind o linie de tendință.

Construcția diferitelor dependențe de aproximare în MS Excel este implementată ca o proprietate grafică - linie de tendință.

EXEMPLUL 2. Ca rezultat al experimentului, a fost determinată o oarecare dependență tabelară.

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

Selectați și construiți o dependență aproximativă. Construiți grafice ale dependențelor analitice tabelare și adaptate.

Rezolvarea problemei poate fi împărțită în următoarele etape: introducerea datelor inițiale, construirea unui grafic de dispersie și adăugarea unei linii de tendință la acest grafic.

Să luăm în considerare acest proces în detaliu. Să introducem datele inițiale în foaia de lucru și să trasăm datele experimentale. Apoi, selectați punctele experimentale de pe diagramă, faceți clic dreapta și utilizați comanda Adăuga l linie de tendință(Fig. 10).

Caseta de dialog care apare vă permite să construiți o dependență aproximativă.

Prima filă (Fig. 11) a acestei ferestre indică tipul de dependență de aproximare.

Al doilea (Fig. 12) definește parametrii de construcție:

denumirea dependenței de aproximare;

Prognoza înainte (înapoi) activată n unități (acest parametru determină câte unități înainte (înapoi) este necesară extinderea liniei de tendință);

dacă să arate punctul de intersecție al curbei cu linia y=const;

dacă se afișează sau nu funcția de aproximare pe diagramă (afișează ecuația pe parametrul diagramei);

Dacă se plasează sau nu valoarea abaterii standard pe diagramă (parametrul pune valoarea fiabilității aproximării pe diagramă).

Să alegem un polinom de gradul doi ca dependență de aproximare (Fig. 11) și să derivăm o ecuație care descrie acest polinom pe grafic (Fig. 12). Diagrama rezultată este prezentată în fig. treisprezece.

În mod similar, cu linii de tendință puteți alege parametrii unor astfel de dependențe precum

liniar y=a∙x+b,

logaritmică y=a ln(X)+b,

exponenţială y=a∙eb,

putere y=a x b,

polinom y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+dși așa mai departe, până la polinomul de gradul 6 inclusiv,

Filtrare liniară.

4.3. Utilizarea instrumentului de analiză a opțiunilor: Găsirea unei soluții.

Un interes considerabil este implementarea în MS Excel a selecției parametrilor dependenței funcționale prin metoda celor mai mici pătrate folosind instrumentul de analiză a opțiunilor: Căutare soluție. Această tehnică vă permite să alegeți parametrii unei funcții de orice fel. Să luăm în considerare această posibilitate pe exemplul următoarei probleme.

EXEMPLUL 3. Ca rezultat al experimentului, dependența z(t) prezentată în tabel

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

Selectați coeficienții de dependență Z(t)=La 4 +Bt 3 +Ct 2 +Dt+K prin metoda celor mai mici pătrate.

Această problemă este echivalentă cu problema găsirii minimului unei funcții de cinci variabile

Luați în considerare procesul de rezolvare a problemei de optimizare (Fig. 14).

Lasă valorile DAR, LA, Cu, Dși La stocate în celule A7:E7. Calculați valorile teoretice ale funcției Z(t)=At4+Bt3+Ct2+Dt+K pentru dat t(B2:J2). Pentru a face acest lucru, în celulă B4 introduceți valoarea funcției la primul punct (celula B2):

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

Copiați această formulă în interval С4:J4și obțineți valoarea așteptată a funcției în puncte, ale căror abscise sunt stocate în celule B2:J2.

La celulă B5 introducem o formulă care calculează pătratul diferenței dintre punctele experimentale și cele calculate:

B5=(B4-B3)^2,

și copiați-l în interval С5:J5. Într-o celulă F7 vom stoca eroarea pătratică totală (10). Pentru a face acest lucru, introducem formula:

F7 = SUMA(B5:J5).

Să folosim comanda Service®Căutați o soluțieși rezolvați problema de optimizare fără constrângeri. Completați câmpurile de intrare corespunzătoare în caseta de dialog prezentată în Fig. 14 și apăsați butonul Alerga. Dacă se găsește o soluție, fereastra prezentată în fig. cincisprezece.

Rezultatul blocului de decizie va fi ieșirea către celule A7:E7valorile parametrilor funcții Z(t)=At4+Bt3+Ct2+Dt+K. În celule B4:J4 primim valoarea așteptată a funcției la punctele de plecare. Într-o celulă F7 va fi păstrat eroare totală pătrată.

Puteți afișa punctele experimentale și linia adaptată în aceeași zonă grafică dacă selectați intervalul B2:J4, apel Chart Wizard, apoi formatați aspectul graficelor rezultate.

Orez. 17 afișează foaia de lucru MS Excel după ce au fost efectuate calculele.

Care găsește cea mai largă aplicație în diverse domenii ale științei și practicii. Poate fi fizică, chimie, biologie, economie, sociologie, psihologie și așa mai departe și așa mai departe. Prin voința sorții, de multe ori trebuie să mă ocup de economie și, prin urmare, astăzi vă voi aranja un bilet către o țară uimitoare numită Econometrie=) … Cum nu vrei asta?! E foarte bine acolo - trebuie doar să te decizi! …Dar ceea ce probabil că vrei cu siguranță este să înveți cum să rezolvi problemele cele mai mici pătrate. Și mai ales cititorii harnici vor învăța să le rezolve nu doar cu acuratețe, ci și FOARTE RAPID ;-) Dar mai întâi expunerea generală a problemei+ exemplu înrudit:

Să fie studiați indicatorii într-o anumită materie care au o expresie cantitativă. În același timp, există toate motivele să credem că indicatorul depinde de indicator. Această ipoteză poate fi atât o ipoteză științifică, cât și bazată pe bun simț elementar. Să lăsăm totuși știința deoparte și să explorăm zone mai apetisante - și anume, magazinele alimentare. Se notează prin:

– spațiu comercial al unui magazin alimentar, mp,
- cifra de afaceri anuală a unui magazin alimentar, milioane de ruble.

Este destul de clar că, cu cât suprafața magazinului este mai mare, cu atât cifra de afaceri este mai mare în majoritatea cazurilor.

Să presupunem că după efectuarea de observații / experimente / calcule / dans cu tamburina, avem la dispoziție date numerice:

Cu magazinele alimentare, cred că totul este clar: - aceasta este zona primului magazin, - cifra de afaceri anuală a acestuia, - zona celui de-al doilea magazin, - cifra de afaceri anuală etc. Apropo, nu este deloc necesar să aveți acces la materiale clasificate - o evaluare destul de precisă a cifrei de afaceri poate fi obținută folosind statistici matematice. Cu toate acestea, nu vă lăsați distras, cursul de spionaj comercial este deja plătit =)

Datele tabelare pot fi scrise și sub formă de puncte și descrise în mod obișnuit pentru noi. Sistemul cartezian .

Să răspundem la o întrebare importantă: de câte puncte sunt necesare pentru un studiu calitativ?

Cu cât mai mare cu atât mai bine. Setul minim admis este format din 5-6 puncte. În plus, cu o cantitate mică de date, rezultatele „anormale” nu ar trebui incluse în eșantion. Deci, de exemplu, un mic magazin de elită poate ajuta ordine de mărime mai mult decât „colegii lor”, distorsionând astfel modelul general care trebuie găsit!

Dacă este destul de simplu, trebuie să alegem o funcție, programa care trece cât mai aproape de puncte . O astfel de funcție este numită aproximând (aproximare - aproximare) sau functie teoretica . În general, aici apare imediat un „pretendint” evident - un polinom de grad înalt, al cărui grafic trece prin TOATE punctele. Dar această opțiune este complicată și adesea pur și simplu incorectă. (deoarece graficul se va „vânta” tot timpul și reflectă slab tendința principală).

Astfel, funcția dorită trebuie să fie suficient de simplă și, în același timp, să reflecte adecvat dependența. După cum ați putea ghici, una dintre metodele pentru găsirea unor astfel de funcții este numită cele mai mici pătrate. În primul rând, să analizăm esența sa într-un mod general. Fie ca o funcție să aproximeze datele experimentale:


Cum se evaluează acuratețea acestei aproximări? Să calculăm și diferențele (abaterile) dintre valorile experimentale și cele funcționale (studiam desenul). Primul gând care îmi vine în minte este de a estima cât de mare este suma, dar problema este că diferențele pot fi negative. (De exemplu, ) iar abaterile ca urmare a unei astfel de însumări se vor anula reciproc. Prin urmare, ca o estimare a preciziei aproximării, se sugerează să ia suma module abateri:

sau în formă pliată: (deodată, cine nu știe: este pictograma sumă și este o variabilă auxiliară-„contor”, care ia valori de la 1 la ).

Prin aproximarea punctelor experimentale cu diferite funcții, vom obține diferite valori ale lui , și este evident că acolo unde această sumă este mai mică, acea funcție este mai precisă.

O astfel de metodă există și este numită metoda modulului minim. Cu toate acestea, în practică a devenit mult mai răspândită. metoda celor mai mici pătrate, în care posibilele valori negative sunt eliminate nu prin modul, ci prin pătrarea abaterilor:

, după care eforturile sunt direcționate către selectarea unei astfel de funcție încât suma abaterilor pătrate era cât se poate de mică. De fapt, de aici și numele metodei.

Și acum revenim la un alt punct important: după cum sa menționat mai sus, funcția selectată ar trebui să fie destul de simplă - dar există și multe astfel de funcții: liniar , hiperbolic, exponenţială, logaritmică, pătratică etc. Și, bineînțeles, aici aș vrea imediat să „reduiesc domeniul de activitate”. Ce clasă de funcții să alegeți pentru cercetare? Tehnica primitivă, dar eficientă:

- Cel mai simplu mod de a atrage puncte pe desen și analizați locația acestora. Dacă tind să fie în linie dreaptă, atunci ar trebui să cauți ecuație în linie dreaptă cu valori optime și . Cu alte cuvinte, sarcina este de a găsi ACEPTĂ coeficienți - astfel încât suma abaterilor pătrate să fie cea mai mică.

Dacă punctele sunt situate, de exemplu, de-a lungul hiperbolă, atunci este clar că funcția liniară va da o aproximare slabă. În acest caz, căutăm cei mai „favorabili” coeficienți pentru ecuația hiperbolei - cele care dau suma minima de patrate .

Acum observați că în ambele cazuri vorbim funcţiile a două variabile, ale căror argumente sunt opțiuni de dependență căutate:

Și, în esență, trebuie să rezolvăm o problemă standard - să găsim minim de o funcție a două variabile.

Amintiți-vă exemplul nostru: să presupunem că punctele „magazin” tind să fie situate în linie dreaptă și că există toate motivele să credem că prezența dependență liniară cifra de afaceri din zona de tranzactionare. Să găsim astfel de coeficienți „a” și „fi”, astfel încât suma abaterilor pătrate era cel mai mic. Totul ca de obicei - mai întâi derivate parțiale de ordinul I. Conform regula liniarității puteți diferenția chiar sub pictograma sumă:

Dacă doriți să folosiți aceste informații pentru un eseu sau un curs, vă voi fi foarte recunoscător pentru linkul din lista de surse, nu veți găsi nicăieri astfel de calcule detaliate:

Să facem un sistem standard:

Reducem fiecare ecuație cu un „doi” și, în plus, „despărțim” sumele:

Notă : analizați independent de ce „a” și „fi” pot fi scoase din pictograma sumă. Apropo, formal acest lucru se poate face cu suma

Să rescriem sistemul într-o formă „aplicată”:

după care începe să fie trasat algoritmul pentru rezolvarea problemei noastre:

Cunoaștem coordonatele punctelor? Noi stim. Sume putem gasi? Uşor. Compunem cel mai simplu sistem de două ecuații liniare cu două necunoscute("a" și "beh"). Rezolvăm sistemul, de exemplu, metoda lui Cramer, rezultând un punct staționar . Control condiție suficientă pentru un extremum, putem verifica că în acest moment funcția ajunge precis minim. Verificarea este asociată cu calcule suplimentare și, prin urmare, o vom lăsa în culise. (dacă este necesar, cadrul lipsă poate fi vizualizat). Tragem concluzia finală:

Funcţie cel mai bun mod (cel puțin în comparație cu orice altă funcție liniară) apropie punctele experimentale . În linii mari, graficul său trece cât mai aproape de aceste puncte. In traditie econometrie funcţia de aproximare rezultată se mai numeşte ecuația de regresie liniară pereche .

Problema luată în considerare este de mare importanță practică. În situația cu exemplul nostru, ecuația vă permite să preziceți ce fel de cifră de afaceri ("yig") va fi la magazinul cu una sau alta valoare a zonei de vânzare (unul sau altul sens al lui „x”). Da, prognoza rezultată va fi doar o prognoză, dar în multe cazuri se va dovedi a fi destul de precisă.

Voi analiza doar o singură problemă cu numerele „reale”, deoarece nu există dificultăți în ea - toate calculele sunt la nivelul programului școlar din clasele 7-8. În 95 la sută din cazuri, vi se va cere să găsiți doar o funcție liniară, dar la sfârșitul articolului voi arăta că nu este mai dificil să găsiți ecuațiile pentru hiperbola optimă, exponent și alte funcții.

De fapt, rămâne să distribuiți bunătățile promise - astfel încât să învățați cum să rezolvați astfel de exemple nu numai cu acuratețe, ci și rapid. Studiem cu atenție standardul:

Sarcină

În urma studierii relației dintre doi indicatori, s-au obținut următoarele perechi de numere:

Folosind metoda celor mai mici pătrate, găsiți funcția liniară care aproximează cel mai bine empiric (cu experienta) date. Realizați un desen pe care, într-un sistem de coordonate dreptunghiular cartezian, să trasați punctele experimentale și un grafic al funcției de aproximare . Aflați suma abaterilor pătrate dintre valorile empirice și teoretice. Aflați dacă funcția este mai bună (în ceea ce privește metoda celor mai mici pătrate) puncte experimentale aproximative.

Rețineți că valorile „x” sunt valori naturale, iar aceasta are o semnificație caracteristică, despre care voi vorbi puțin mai târziu; dar ele, desigur, pot fi fracționate. În plus, în funcție de conținutul unei anumite sarcini, atât valorile „X” cât și „G” pot fi complet sau parțial negative. Ei bine, ni s-a dat o sarcină „fără chip” și o începem decizie:

Găsim coeficienții funcției optime ca soluție a sistemului:

În scopul unei notații mai compacte, variabila „contor” poate fi omisă, deoarece este deja clar că însumarea se realizează de la 1 la .

Este mai convenabil să calculați sumele necesare într-o formă tabelară:


Calculele pot fi efectuate pe un microcalculator, dar este mult mai bine să utilizați Excel - atât mai rapid, cât și fără erori; vezi un scurt video:

Astfel, obținem următoarele sistem:

Aici puteți înmulți a doua ecuație cu 3 și scădeți al 2-lea din prima ecuație termen cu termen. Dar acesta este noroc - în practică, sistemele nu sunt adesea dotate și, în astfel de cazuri, economisesc metoda lui Cramer:
, astfel încât sistemul are o soluție unică.

Hai să facem o verificare. Înțeleg că nu vreau, dar de ce să sari peste greșelile în care nu le poți rata? Înlocuiți soluția găsită în partea stângă a fiecărei ecuații a sistemului:

Se obțin părțile corecte ale ecuațiilor corespunzătoare, ceea ce înseamnă că sistemul este rezolvat corect.

Astfel, funcția de aproximare dorită: – de la toate funcțiile liniare datele experimentale sunt cel mai bine aproximate prin aceasta.

Spre deosebire de Drept dependenţa cifrei de afaceri a magazinului de suprafaţa acestuia, dependenţa constatată este verso (principiul „cu cât mai mult – cu atât mai puțin”), iar acest fapt este imediat relevat de negativ coeficient unghiular. Funcţie ne informează că odată cu creșterea unui anumit indicator cu 1 unitate, valoarea indicatorului dependent scade in medie cu 0,65 unități. După cum se spune, cu cât prețul hrișcii este mai mare, cu atât se vinde mai puțin.

Pentru a reprezenta graficul funcției de aproximare, găsim două dintre valorile acesteia:

și executați desenul:


Linia construită se numește linie de tendință (și anume, o linie de tendință liniară, adică, în cazul general, o tendință nu este neapărat o linie dreaptă). Toată lumea este familiarizată cu expresia „a fi în trend”, și cred că acest termen nu are nevoie de comentarii suplimentare.

Calculați suma abaterilor pătrate între valorile empirice şi teoretice. Din punct de vedere geometric, aceasta este suma pătratelor lungimii segmentelor „crimson”. (dintre care două sunt atât de mici încât nici nu le poți vedea).

Să rezumăm calculele într-un tabel:


Ele pot fi din nou efectuate manual, doar în cazul în care voi da un exemplu pentru primul punct:

dar este mult mai eficient să faci modul deja cunoscut:

Să repetăm: care este sensul rezultatului? Din toate funcțiile liniare funcţie exponentul este cel mai mic, adică este cea mai bună aproximare din familia sa. Și aici, apropo, întrebarea finală a problemei nu este întâmplătoare: ce se întâmplă dacă funcția exponențială propusă va fi mai bine să aproximăm punctele experimentale?

Să găsim suma corespunzătoare a abaterilor pătrate - pentru a le distinge, le voi desemna cu litera „epsilon”. Tehnica este exact aceeași:


Și din nou pentru fiecare calcul de incendiu pentru primul punct:

În Excel, folosim funcția standard EXP (Sintaxa poate fi găsită în Ajutor Excel).

Concluzie: , deci funcția exponențială aproximează punctele experimentale mai rău decât dreapta .

Dar trebuie remarcat aici că „mai rău” este nu înseamnă încă, Ce s-a întâmplat. Acum am construit un grafic al acestei funcții exponențiale - și trece, de asemenea, aproape de puncte - atât de mult încât fără un studiu analitic este greu de spus care funcție este mai exactă.

Aceasta completează soluția și revin la întrebarea valorilor naturale ale argumentului. În diverse studii, de regulă, economice sau sociologice, lunile, anii sau alte intervale de timp egale sunt numerotate cu „X” natural. Luați în considerare, de exemplu, o astfel de problemă.

Metoda celor mai mici pătrate este o procedură matematică pentru construirea unei ecuații liniare care se potrivește cel mai bine cu un set de două serii de numere. Scopul acestei metode este de a minimiza eroarea pătrată totală. Excel are instrumente care pot fi folosite pentru a aplica această metodă în calcule. Să vedem cum se face.

Folosind metoda din Excel

o Activarea suplimentului Solver

o Condiții de sarcină

o Decizie

Folosind o metodă în Excel

Metoda celor mai mici pătrate (LSM) este o descriere matematică a dependenței unei variabile de alta. Poate fi folosit pentru prognoză.

Activați programul de completare Solver

Pentru a utiliza OLS în Excel, trebuie să activați programul de completare „Căutați o soluție”, care este dezactivat implicit.

1. Accesați fila "Fişier".

2. Faceți clic pe numele secțiunii "Opțiuni".

3. În fereastra care se deschide, opriți selecția pe subsecțiune „Suplimente”.

4. În bloc "Control", care se află în partea de jos a ferestrei, setați comutatorul în poziția „Suplimente Excel”(dacă are o altă valoare) și faceți clic pe butonul "Merge...".

5. Se deschide o fereastră mică. Pune o bifă lângă opțiune „Căutați o soluție”. Faceți clic pe butonul Bine.

Acum funcția Găsirea unei soluțiiîn Excel este activat, iar instrumentele sale apar pe panglică.

Lecţie: Găsirea unei soluții în Excel

Condițiile problemei

Să descriem aplicarea LSM pe un exemplu specific. Avem două rânduri de numere Xși y, a cărei secvență este prezentată în imaginea de mai jos.

Această dependență poate fi descrisă cel mai precis prin funcția:

În același timp, se știe că x=0 y de asemenea egale 0 . Prin urmare, această ecuație poate fi descrisă prin dependență y=nx.

Trebuie să găsim suma minimă de pătrate a diferenței.

Decizie

Să trecem la descrierea aplicării directe a metodei.

1. În stânga primei valori X pune un număr 1 . Aceasta va fi valoarea aproximativă a primei valori a coeficientului n.

2. În dreapta coloanei y adăugați o altă coloană nx. În prima celulă a acestei coloane scriem formula de înmulțire a coeficientului n la celula primei variabile X. În același timp, facem legătura cu câmpul cu coeficientul absolut, deoarece această valoare nu se va modifica. Facem clic pe buton introduce.

3. Folosind mânerul de umplere, copiați această formulă în întregul interval al tabelului din coloana de mai jos.

4. Într-o celulă separată, calculăm suma diferențelor pătratelor valorilor yși nx. Pentru a face acest lucru, faceți clic pe butonul „Inserare funcție”.

5. În deschis „Asistent de funcții” caută o intrare „SUMMKVRAZN”. Selectați-l și faceți clic pe butonul Bine.

6. Se deschide fereastra de argumente. În câmp „Matrice_x” y. În câmp „Matrice_y” introduceți un interval de celule de coloană nx. Pentru a introduce valori, pur și simplu plasați cursorul în câmp și selectați intervalul corespunzător de pe foaie. După ce ați intrat, faceți clic pe butonul Bine.

7. Accesați fila "Date". Pe panglica din cutia de instrumente "Analiză" faceți clic pe butonul „Căutați o soluție”.

8. Se deschide fereastra cu parametrii instrumentului. În câmp „Optimizați funcția obiectiv” specificați adresa celulei cu formula „SUMMKVRAZN”. În parametru "Inainte de" asigurați-vă că setați comutatorul în poziția "Minim". În câmp „Schimbarea celulelor” precizati adresa cu valoarea coeficientului n. Faceți clic pe butonul "Gaseste o solutie".

9. Soluția va fi afișată în celula coeficientului n. Această valoare va fi cel mai mic pătrat al funcției. Dacă rezultatul satisface utilizatorul, atunci faceți clic pe butonul Bineîntr-o fereastră suplimentară.

După cum puteți vedea, aplicarea metodei celor mai mici pătrate este o procedură matematică destul de complicată. Am arătat-o ​​în acțiune cu cel mai simplu exemplu, dar sunt cazuri mult mai complexe. Cu toate acestea, setul de instrumente Microsoft Excel este conceput pentru a simplifica calculele cât mai mult posibil.

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

Dispoziții generale

Cu cât numărul în valoare absolută este mai mic, cu atât se alege mai bine linia dreaptă (2). Ca o caracteristică a preciziei selecției unei linii drepte (2), putem lua suma pătratelor

Condițiile minime pentru S vor fi

	(6)
	(7)

Ecuațiile (6) și (7) pot fi scrise sub următoarea formă:

	(8)
	(9)

Din ecuațiile (8) și (9) este ușor de găsit a și b din valorile experimentale x i și y i . Linia (2) definită prin ecuațiile (8) și (9) se numește dreptă obținută prin metoda celor mai mici pătrate (acest nume subliniază că suma pătratelor S are un minim). Ecuațiile (8) și (9), din care se determină linia dreaptă (2), se numesc ecuații normale.

Este posibil să se indice un mod simplu și general de compilare a ecuațiilor normale. Folosind punctele experimentale (1) și ecuația (2), putem scrie sistemul de ecuații pentru a și b

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

Înmulțiți părțile din stânga și din dreapta fiecăreia dintre aceste ecuații cu coeficientul de la prima necunoscută a (adică x 1 , x 2 , ..., x n) și adăugați ecuațiile rezultate, rezultând prima ecuație normală (8).

Înmulțim părțile stânga și dreaptă ale fiecăreia dintre aceste ecuații cu coeficientul celei de-a doua necunoscute b, i.e. cu 1 și adăugați ecuațiile rezultate, rezultând a doua ecuație normală (9).

Această metodă de obținere a ecuațiilor normale este generală: este potrivită, de exemplu, pentru funcție

este o valoare constantă și trebuie determinată din datele experimentale (1).

Sistemul de ecuații pentru k se poate scrie:

Găsiți linia (2) folosind metoda celor mai mici pătrate.

Decizie. Găsim:

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

Scriem ecuațiile (8) și (9)91a+21b=179,1,

21a+6b=46,3, de aici găsim
a=0,98 b=4,3.