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 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ă: În acest articol sunt luate în considerare cazuri de aproximare printr-un polinom de la gradul 3 până la gradul 6. 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 multele 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ă ce cantitate mai mica distanțe pătrate, 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 LSM-ul va 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; ...).
4.1. Utilizarea funcțiilor încorporate
calcul coeficienții de regresie efectuate cu ajutorul funcției
LINEST(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 LINEST returnează statistici suplimentare de regresie. Dacă argumentul Statistici are sensul FALS sau omis, apoi funcția LINEST 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 LINEST. 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 din 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. calculati valori teoretice funcții 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și apoi formatați aspect diagrame primite.
Orez. 17 afișează foaia de lucru MS Excel după ce au fost efectuate calculele.
Metoda celor mai mici pătrate este o procedură matematică de construcție ecuație liniară, care s-ar potrivi cel mai bine cu setul de două rânduri de numere. Scopul acestei metode este de a minimiza eroarea pătrată totală. Excel are instrumente care vă ajută să aplicați aceasta metoda la calcul. Să vedem cum se face.
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.
Acum funcția Găsirea unei soluțiiîn Excel este activat, iar instrumentele sale apar pe panglică.
Condițiile problemei
Să descriem aplicarea celor mai mici pătrate pe exemplu concret. 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.
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.
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ă pentru întreaga gamă a 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.
Puteți specifica un mod simplu și general de compunere ecuații 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.
