Metoda celor mai mici pătrate (MCO) aparține domeniului analizei regresiei. Are multe aplicații, deoarece permite o reprezentare aproximativă a unei anumite funcții de către altele mai simple. LSM poate fi extrem de util în procesarea observațiilor și este utilizat în mod activ pentru a estima unele cantități pe baza rezultatelor măsurătorilor altora care conțin erori aleatoare. În acest articol, veți învăța cum să implementați calculele celor mai mici pătrate în Excel.

Enunțarea problemei folosind un exemplu specific

Să presupunem că există doi indicatori X și Y. Mai mult, Y depinde de X. Deoarece OLS ne interesează din punct de vedere al analizei de regresie (în Excel metodele sale sunt implementate folosind funcții încorporate), ar trebui să trecem imediat la luarea în considerare a unui problemă specifică.

Deci, să fie X spațiul de vânzare cu amănuntul al unui magazin alimentar, măsurat în metri pătrați, și Y să fie cifra de afaceri anuală, măsurată în milioane de ruble.

Este necesar să se facă o prognoză a cifrei de afaceri (Y) va avea magazinul dacă are cutare sau cutare spațiu comercial. Evident, funcția Y = f (X) este în creștere, deoarece hipermarketul vinde mai multe mărfuri decât taraba.

Câteva cuvinte despre corectitudinea datelor inițiale utilizate pentru predicție

Să presupunem că avem un tabel construit folosind date pentru n magazine.

Conform statisticilor matematice, rezultatele vor fi mai mult sau mai puțin corecte dacă se examinează datele pe cel puțin 5-6 obiecte. În plus, rezultatele „anomale” nu pot fi utilizate. În special, un mic butic de elită poate avea o cifră de afaceri de câteva ori mai mare decât cifra de afaceri a magazinelor mari de vânzare cu amănuntul din clasa „masmarket”.

Esența metodei

Datele din tabel pot fi reprezentate pe un plan cartezian sub forma punctelor M 1 (x 1, y 1), ... M n (x n, y n). Acum soluția problemei se va reduce la selectarea unei funcții de aproximare y = f (x), care are un grafic care trece cât mai aproape de punctele M 1, M 2, .. M n.

Desigur, puteți folosi un polinom grad înalt, dar această opțiune nu este doar dificil de implementat, ci și pur și simplu incorectă, deoarece nu va reflecta tendința principală care trebuie detectată. Soluția cea mai rezonabilă este căutarea dreptei y = ax + b, care aproximează cel mai bine datele experimentale, sau mai precis, coeficienții a și b.

Evaluarea acurateței

Cu orice aproximare, evaluarea acurateței sale este de o importanță deosebită. Să notăm cu e i diferența (abaterea) dintre valorile funcționale și experimentale pentru punctul x i, adică e i = y i - f (x i).

Evident, pentru a evalua acuratețea aproximării, puteți utiliza suma abaterilor, adică atunci când alegeți o linie dreaptă pentru o reprezentare aproximativă a dependenței lui X de Y, ar trebui să acordați prioritate celei cu cea mai mică valoare a sum e i la toate punctele luate în considerare. Cu toate acestea, nu totul este atât de simplu, deoarece împreună cu abaterile pozitive vor exista și unele negative.

Problema poate fi rezolvată folosind module de abatere sau pătratele acestora. Ultima metodă este cea mai utilizată. Este folosit în multe domenii, inclusiv în analiza de regresie (implementată în Excel folosind două funcții încorporate) și și-a dovedit de mult eficacitatea.

Metoda celor mai mici pătrate

După cum știți, Excel are o funcție încorporată AutoSum care vă permite să calculați valorile tuturor valorilor situate în intervalul selectat. Astfel, nimic nu ne va împiedica să calculăm valoarea expresiei (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

În notație matematică, aceasta arată astfel:

Deoarece a fost luată inițial decizia de a aproxima folosind o linie dreaptă, avem:

Astfel, sarcina de a găsi linia dreaptă care descrie cel mai bine dependența specifică a mărimilor X și Y se rezumă la calcularea minimului unei funcții a două variabile:

Pentru a face acest lucru, trebuie să echivalați derivatele parțiale față de noile variabile a și b la zero și să rezolvați un sistem primitiv format din două ecuații cu 2 necunoscute de forma:

După câteva transformări simple, inclusiv împărțirea cu 2 și manipularea sumelor, obținem:

Rezolvând-o, de exemplu, folosind metoda lui Cramer, obținem un punct staționar cu anumiți coeficienți a * și b *. Acesta este minimul, adică pentru a prezice ce cifră de afaceri va avea un magazin pentru o anumită zonă, este potrivită linia dreaptă y = a * x + b *, care este un model de regresie pentru exemplul în cauză. Desigur, nu vă va permite să găsiți rezultatul exact, dar vă va ajuta să vă faceți o idee dacă achiziționarea unei anumite zone din creditul magazinului va fi rentabilă.

Cum se implementează cele mai mici pătrate în Excel

Excel are o funcție pentru calcularea valorilor folosind cele mai mici pătrate. Are următoarea formă: „TENDINȚA” (valori Y cunoscute; valori X cunoscute; valori X noi; constantă). Să aplicăm formula de calcul OLS în Excel la tabelul nostru.

Pentru a face acest lucru, introduceți semnul „=” în celula în care ar trebui să fie afișat rezultatul calculului folosind metoda celor mai mici pătrate în Excel și selectați funcția „TENDINȚA”. În fereastra care se deschide, completați câmpurile corespunzătoare, evidențiind:

• intervalul de valori cunoscute pentru Y (în acest caz, date pentru cifra de afaceri comercială);
• interval x 1 , …x n , adică dimensiunea spațiului comercial cu amănuntul;
• atât valorile cunoscute, cât și cele necunoscute ale lui x, pentru care trebuie să aflați dimensiunea cifrei de afaceri (pentru informații despre locația lor pe foaia de lucru, consultați mai jos).

În plus, formula conține variabila logică „Const”. Dacă introduceți 1 în câmpul corespunzător, aceasta va însemna că trebuie să efectuați calculele, presupunând că b = 0.

Dacă trebuie să aflați prognoza pentru mai mult de o valoare x, atunci după introducerea formulei nu trebuie să apăsați „Enter”, ci trebuie să introduceți combinația „Shift” + „Control” + „Enter” pe tastatură.

Unele caracteristici

Analiza de regresie poate fi accesibilă chiar și pentru manechin. Formula Excel pentru prezicerea valorii unei matrice de variabile necunoscute — TREND — poate fi folosită chiar și de cei care nu au auzit niciodată de cele mai mici pătrate. Este suficient doar să cunoașteți câteva dintre caracteristicile muncii sale. În special:

• Dacă aranjați intervalul de valori cunoscute ale variabilei y într-un rând sau coloană, atunci fiecare rând (coloană) cu valori cunoscute ale lui x va fi perceput de program ca o variabilă separată.
• Dacă un interval cu x cunoscut nu este specificat în fereastra TREND, atunci când utilizați funcția în Excel, programul o va trata ca o matrice formată din numere întregi, al căror număr corespunde intervalului cu valorile date ale variabila y.
• Pentru a scoate o matrice de valori „prevăzute”, expresia pentru calcularea tendinței trebuie introdusă ca formulă matrice.
• Dacă nu sunt specificate valori noi ale lui x, atunci funcția TREND le consideră egale cu cele cunoscute. Dacă nu sunt specificate, atunci tabloul 1 este luat ca argument; 2; 3; 4;…, care este proporțional cu intervalul cu parametrii deja specificați y.
• Intervalul care conține noile valori x trebuie să aibă aceleași sau mai multe rânduri sau coloane ca și intervalul care conține valorile y date. Cu alte cuvinte, trebuie să fie proporțional cu variabilele independente.
• O matrice cu valori x cunoscute poate conține mai multe variabile. Cu toate acestea, dacă vorbim despre unul singur, atunci este necesar ca intervalele cu valorile date ale lui x și y să fie proporționale. În cazul mai multor variabile, este necesar ca intervalul cu valorile y date să se încadreze într-o coloană sau un rând.

Funcția PREDICTION

Analiza de regresie în Excel este implementată folosind mai multe funcții. Una dintre ele se numește „PREDICȚIE”. Este similar cu „TENDINȚA”, adică oferă rezultatul calculelor folosind metoda celor mai mici pătrate. Cu toate acestea, doar pentru un X, pentru care valoarea lui Y este necunoscută.

Acum știți formule în Excel pentru manechine care vă permit să preziceți valoarea viitoare a unui anumit indicator în conformitate cu o tendință liniară.

Metoda celor mai mici pătrate este o procedură matematică pentru construirea unei ecuații liniare care se va potrivi cel mai precis într-un set de două serii de numere. Scopul utilizării acestei metode este de a minimiza eroarea pătrată totală. Excel are instrumente care vă pot ajuta să aplicați această metodă la calcule. Să ne dăm seama cum se face asta.

· Utilizarea metodei în Excel

o Activarea suplimentului „Solution Search”.

o Condiții de problemă

o Soluție

Folosind metoda din Excel

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

Activarea suplimentului Find Solution

Pentru a utiliza MNC în Excel, trebuie să activați programul de completare „Găsirea unei soluții”, 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, selectați subsecțiunea „Suplimente”.

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

5. Se deschide o fereastră mică. Punem o bifă lângă parametru „Găsirea unei soluții”. 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 utilizarea LSM folosind 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ă atunci când 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.

Soluţie

Să trecem la o descriere a aplicării directe a metodei.

1. În stânga primei valori X pune un număr 1 . Aceasta va fi o valoare 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 pe celulă a primei variabile X. În același timp, facem legătura cu câmpul cu coeficientul absolut, deoarece această valoare nu se va modifica. Faceți clic pe butonul introduce.

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

4. Într-o celulă separată, calculați suma diferențelor dintre pătratele 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 "SUMMKVARNA". Selectați-l și apăsați butonul "BINE".

6. Se deschide fereastra de argumente. În câmp „Matrice_x” y. În câmp „Matrice_y” introduceți intervalul de celule ale coloanei 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 „Găsirea unei soluții”.

8. Se deschide fereastra de parametri pentru acest instrument. În câmp „Optimizați funcția obiectiv” indicați adresa celulei cu formula "SUMMKVARNA". În parametru "Inainte de" asigurați-vă că setați comutatorul în poziție "Minim". În câmp „Schimbarea celulelor” indicați 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 complexă. Am arătat-o ​​în acțiune folosind un exemplu simplu, dar există cazuri mult mai complexe. Cu toate acestea, instrumentele Microsoft Excel sunt concepute 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 linia dreaptă aleasă (2) este mai bună. Ca o caracteristică a preciziei selectării 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 după cum urmează:

	(8)
	(9)

Din ecuațiile (8) și (9) este ușor de găsit a și b din valorile experimentale ale lui xi ș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 indica un mod simplu și general de a compune ecuații normale. Folosind punctele experimentale (1) și ecuația (2), putem scrie un sistem de ecuații pentru a și b

y 1 =ax 1 +b,
y 2 =ax 2 +b, ...		(10)
y n = ax n + b,

Să înmulțim părțile din stânga și din dreapta fiecăreia dintre aceste ecuații cu coeficientul primei necunoscute a (adică cu x 1, x 2, ..., x n) și să adunăm ecuațiile rezultate, rezultând prima ecuație normală (8) .

Să înmulțim părțile din stânga și din dreapta 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, rezultatul este a doua ecuație normală (9).

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

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

Sistemul de ecuații pentru k se poate scrie:

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

Soluţie. 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.

Metoda celor mai mici pătrate utilizat pentru estimarea parametrilor ecuației de regresie.

Una dintre metodele de studiu a relațiilor stocastice dintre caracteristici este analiza de regresie.
Analiza regresiei este derivarea unei ecuații de regresie, cu ajutorul căreia se găsește valoarea medie a unei variabile aleatoare (atribut rezultat) dacă se cunoaște valoarea altei (sau a altor) variabile (atribute-factor). Acesta include următorii pași:

1. selectarea formei de conectare (tipul ecuației de regresie analitică);
2. estimarea parametrilor ecuației;
3. evaluarea calității ecuației de regresie analitică.

Cel mai adesea, o formă liniară este folosită pentru a descrie relația statistică a caracteristicilor. Accentul pus pe relațiile liniare se explică prin interpretarea economică clară a parametrilor săi, variația limitată a variabilelor și faptul că, în majoritatea cazurilor, formele neliniare de relații sunt convertite (prin logaritm sau înlocuirea variabilelor) într-o formă liniară pentru a efectua calcule. .
În cazul unei relații liniare pe perechi, ecuația de regresie va lua forma: y i =a+b·x i +u i . Parametrii a și b ai acestei ecuații sunt estimați din datele de observație statistică x și y. Rezultatul unei astfel de evaluări este ecuația: , unde , sunt estimări ale parametrilor a și b , este valoarea atributului (variabilă) rezultat obținut din ecuația de regresie (valoarea calculată).

Cel mai adesea folosit pentru estimarea parametrilor metoda celor mai mici pătrate (LSM).
Metoda celor mai mici pătrate oferă cele mai bune estimări (consistente, eficiente și nepărtinitoare) ale parametrilor ecuației de regresie. Dar numai dacă sunt îndeplinite anumite ipoteze privind termenul aleator (u) și variabila independentă (x) (vezi ipotezele MCO).

Problema estimării parametrilor unei ecuații de perechi liniare folosind metoda celor mai mici pătrate este după cum urmează: pentru a obține astfel de estimări ale parametrilor , , la care suma abaterilor pătrate a valorilor reale ale caracteristicii rezultante - y i din valorile calculate - este minimă.
Oficial criteriul OLS se poate scrie asa: .

Metode de clasificare a celor mai mici pătrate

1. Metoda celor mai mici pătrate.
2. Metoda maximei probabilități (pentru un model de regresie liniară clasică normală, se postulează normalitatea reziduurilor de regresie).
3. Metoda MOL a celor mai mici pătrate generalizate este utilizată în cazul autocorelării erorilor și în cazul heteroscedasticității.
4. Metoda celor mai mici pătrate ponderate (un caz special de MCO cu reziduuri heteroscedastice).

Să ilustrăm ideea metoda clasică a celor mai mici pătrate grafic. Pentru a face acest lucru, vom construi un grafic de împrăștiere pe baza datelor observaționale (x i, y i, i=1;n) într-un sistem de coordonate dreptunghiular (un astfel de diagramă de împrăștiere se numește câmp de corelație). Să încercăm să selectăm o linie dreaptă care este cea mai apropiată de punctele câmpului de corelație. Conform metodei celor mai mici pătrate, linia este selectată astfel încât suma pătratelor distanțelor verticale dintre punctele câmpului de corelație și această linie să fie minimă.

Notație matematică pentru această problemă: .
Valorile lui y i și x i =1...n ne sunt cunoscute; acestea sunt date observaționale. În funcția S ele reprezintă constante. Variabilele din această funcție sunt estimările necesare ale parametrilor - , . Pentru a găsi minimul unei funcții de două variabile, este necesar să se calculeze derivatele parțiale ale acestei funcții pentru fiecare dintre parametri și să le echivaleze cu zero, i.e. .
Ca rezultat, obținem un sistem de 2 ecuații liniare normale:
Rezolvând acest sistem, găsim estimările parametrilor necesari:

Corectitudinea calculului parametrilor ecuației de regresie poate fi verificată prin compararea sumelor (poate exista unele discrepanțe din cauza rotunjirii calculelor).
Pentru a calcula estimările parametrilor, puteți construi Tabelul 1.
Semnul coeficientului de regresie b indică direcția relației (dacă b >0, relația este directă, dacă b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
În mod formal, valoarea parametrului a este valoarea medie a lui y cu x egal cu zero. Dacă factorul-atribut nu are și nu poate avea o valoare zero, atunci interpretarea de mai sus a parametrului a nu are sens.

Evaluarea gradului de apropiere a relației dintre caracteristici efectuată folosind coeficientul de corelație liniară pereche - r x,y. Poate fi calculat folosind formula: . În plus, coeficientul de corelație liniară a perechii poate fi determinat prin coeficientul de regresie b: .
Intervalul valorilor acceptabile ale coeficientului de corelație al perechii liniare este de la –1 la +1. Semnul coeficientului de corelație indică direcția relației. Dacă r x, y >0, atunci conexiunea este directă; dacă r x, y<0, то связь обратная.
Dacă acest coeficient este aproape de unitate în mărime, atunci relația dintre caracteristici poate fi interpretată ca una liniară destul de apropiată. Dacă modulul său este egal cu un ê r x , y ê =1, atunci relația dintre caracteristici este liniară funcțională. Dacă caracteristicile x și y sunt liniar independente, atunci r x,y este aproape de 0.
Pentru a calcula r x,y, puteți utiliza și Tabelul 1.

Pentru a evalua calitatea ecuației de regresie rezultată, calculați coeficientul teoretic de determinare - R 2 yx:

,
unde d 2 este varianța lui y explicată prin ecuația de regresie;
e 2 - varianța reziduală (neexplicată prin ecuația de regresie) a lui y;
s 2 y - variația totală (totală) a lui y.
Coeficientul de determinare caracterizează proporția de variație (dispersie) a atributului rezultat y explicată prin regresie (și, în consecință, factorul x) în variația totală (dispersia) y. Coeficientul de determinare R 2 yx ia valori de la 0 la 1. În consecință, valoarea 1-R 2 yx caracterizează proporția de varianță y cauzată de influența altor factori neluați în considerare în erorile de model și de specificație.
Cu regresie liniară pereche, R 2 yx =r 2 yx.

Metoda celor mai mici pătrate (LSM)

Un sistem de m ecuații liniare cu n necunoscute are forma:

Sunt posibile trei cazuri: m n. Cazul când m=n a fost luat în considerare în paragrafele precedente. Când m

Dacă m>n și sistemul este consistent, atunci matricea A are cel puțin m - n rânduri dependente liniar. Aici soluția poate fi obținută selectând n orice ecuații liniar independente (dacă există) și aplicând formula X = A -1 CV, adică reducând problema la una rezolvată anterior. În acest caz, soluția rezultată va satisface întotdeauna m - n ecuații rămase.

Cu toate acestea, atunci când utilizați un computer, este mai convenabil să folosiți o abordare mai generală - metoda celor mai mici pătrate.

Metoda algebrică a celor mai mici pătrate

Metoda algebrică a celor mai mici pătrate este o metodă de rezolvare a sistemelor de ecuații liniare

prin minimizarea normei euclidiene

Topor? b? >inf. (1,2)

Analiza datelor experimentale

Să luăm în considerare un experiment în care la anumite momente

De exemplu, se măsoară temperatura Q(t). Lăsați rezultatele măsurătorii să fie specificate printr-o matrice

Să presupunem că condițiile experimentale sunt astfel încât măsurătorile sunt efectuate cu o eroare cunoscută. În aceste cazuri, legea schimbării temperaturii Q(t) este căutată folosind un anumit polinom

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

determinarea coeficienților necunoscuți, ..., din considerentele că valoarea E(, ...,), definită de egalitate

aproximarea exel algebric gauss

a luat valoarea minimă. Deoarece suma pătratelor este minimizată, această metodă se numește aproximare a celor mai mici pătrate la date.

Dacă înlocuim P(t) cu expresia sa, obținem

Să stabilim sarcina de a defini o matrice astfel încât valoarea să fie minimă, adică. Să definim tabloul folosind metoda celor mai mici pătrate. Pentru a face acest lucru, echivalăm derivatele parțiale cu zero:

Dacă introduceți matricea m × n A = (), i = 1, 2..., m; j = 1, 2, ..., n, unde

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

atunci egalitatea scrisă va lua forma

Să rescriem egalitatea scrisă în termeni de operații cu matrici. După definiția înmulțirii unei matrice cu o coloană, avem

Pentru o matrice transpusă, o relație similară arată astfel

Introducem notatia: vom nota i-a componenta a vectorului Ax In conformitate cu egalitatile matriceale scrise, vom avea

Sub formă de matrice, această egalitate poate fi rescrisă ca

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

Aici A este o matrice dreptunghiulară m×n. Mai mult, în problemele de aproximare a datelor, de regulă, m > n. Ecuația (1.3) se numește ecuație normală.

A fost posibil încă de la început, folosind norma euclidiană a vectorilor, să scriem problema sub formă de matrice echivalentă:

Scopul nostru este de a minimiza această funcție în x. Pentru ca un minim să fie atins într-un punct de soluție, primele derivate față de x în acest punct trebuie să fie egale cu zero. Derivatele acestei funcții sunt

2A T B + 2A T Ax

și prin urmare soluția trebuie să satisfacă sistemul de ecuații liniare

(A T A)x = (A T B).

Aceste ecuații se numesc ecuații normale. Dacă A este o matrice m× n, atunci A>A - n × n este o matrice, adică. Matricea unei ecuații normale este întotdeauna o matrice pătrată simetrică. Mai mult, are proprietatea de definire pozitivă în sensul că (A>Ax, x) = (Ax, Ax) ? 0.

Cometariu. Uneori, soluția unei ecuații de forma (1.3) se numește soluție a sistemului Ax = B, unde A este o matrice dreptunghiulară m × n (m > n) folosind metoda celor mai mici pătrate.

Problema celor mai mici pătrate poate fi interpretată grafic ca minimizarea distanțelor verticale de la punctele de date la o curbă a modelului (vezi Figura 1.1). Această idee se bazează pe presupunerea că toate erorile de aproximare corespund erorilor din observații. Dacă există și erori în variabilele independente, atunci poate fi mai potrivit să se minimizeze distanța euclidiană de la date la model.

MNC în Excel

Algoritmul de mai jos pentru implementarea OLS în Excel presupune că toate datele inițiale sunt deja cunoscute. Înmulțim ambele părți ale ecuației matriceale AЧX=B a sistemului din stânga cu matricea transpusă a sistemului А Т:

A T AX = A T B

Apoi înmulțim ambele părți ale ecuației din stânga cu matricea (A T A) -1. Dacă această matrice există, atunci sistemul este definit. Având în vedere că

(A T A) -1 *(A T A)=E, obținem

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

Ecuația matriceală rezultată este o soluție a unui sistem de m ecuații liniare cu n necunoscute pentru m>n.

Să luăm în considerare aplicarea algoritmului de mai sus folosind un exemplu specific.

Exemplu. Să fie necesar să rezolvăm sistemul

În Excel, foaia de soluții în modul de afișare a formulei pentru această problemă arată astfel:

Rezultatele calculului:

Vectorul X necesar este situat în intervalul E11:E12.

La rezolvarea unui sistem dat de ecuații liniare au fost utilizate următoarele funcții:

1. MOBR - returnează matricea inversă pentru matricea stocată în tablou.

Sintaxă: MOBR (matrice).

Array este o matrice numerică cu un număr egal de rânduri și coloane.

2. MULTIPULT - returnează produsul matricelor (matricele sunt stocate în matrice). Rezultatul este o matrice cu același număr de rânduri ca și array1 și același număr de coloane ca și array2.

Sintaxă: MULTIPLE(matrice1,matrice2).

Array1, array2 sunt matrice multiplicabile.

După ce ați introdus o funcție în celula din stânga sus a unui interval de matrice, selectați matricea, începând cu celula care conține formula, apăsați F2, apoi apăsați CTRL+SHIFT+ENTER.

3. TRANSPORT - convertește un set vertical de celule într-unul orizontal sau invers. Ca urmare a utilizării acestei funcții, apare o matrice cu numărul de rânduri egal cu numărul de coloane al matricei inițiale și numărul de coloane egal cu numărul de rânduri ale matricei inițiale.

4.1. Utilizarea funcțiilor încorporate

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

LINIST(Valori_y; valorile x; Const; statistici),

Valori_y- matrice de valori y,

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

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

Statistici este 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 MINCIUNĂ sau omis, apoi funcția LINIST returnează doar coeficientul Ași constantă b.

Trebuie amintit că rezultatul funcțiilor LINEST() 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 într-o foaie 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 celulele A7:B7, Să mergem la vrăjitorul de funcții și la categorie Statistic selectați o funcție LINIST. Să completăm 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 valoarea să apară în 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 într-o celulă C6 a fost introdusă următoarea formulă:

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

Cunoașterea coeficienților de regresie AȘi b să calculăm 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 C5:J5(Fig. 5).

Să trasăm linia de regresie pe diagramă. Selectați punctele experimentale de pe grafic, 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. Să completăm câmpurile de intrare 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 desenat ca puncte neconectate prin linii de netezire.

Orez. 6

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

Tipul liniei, culoarea și grosimea pot fi modificate după cum urmează. Selectați o linie pe diagramă, faceți clic dreapta ș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. 8.

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 anumită dependență de masă.

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

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ă ne uităm la acest proces în detaliu. Să introducem datele inițiale în foaia de lucru și să trasăm datele experimentale. Apoi, selectați punctele experimentale din grafic, 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 relație de aproximare.

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

Pe al doilea (Fig. 12) se determină parametrii de construcție:

· denumirea dependenței de aproximare;

· prognoză înainte (înapoi) de n unități (acest parametru determină câte unități înainte (înapoi) trebuie extinsă linia de tendință);

dacă să arate punctul de intersecție al unei curbe cu o linie dreaptă y=const;

· afișați sau nu funcția de aproximare pe diagramă (opțiunea de a afișa ecuația pe diagramă);

· dacă se plasează sau nu valoarea abaterii standard pe diagramă (opțiunea de a plasa valoarea fiabilității aproximării pe diagramă).

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

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

liniar y=a∙x+b,

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

· exponenţial y=a∙e b,

· calmante 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 un polinom de gradul 6 inclusiv,

· filtrare liniară.

4.3. Folosind un bloc de rezolvare

Un interes semnificativ este implementarea în MS Excel a selectării parametrilor folosind metoda celor mai mici pătrate folosind un bloc rezolvator. Această tehnică vă permite să selectați parametrii unei funcții de orice tip. Să luăm în considerare această posibilitate folosind următoarea problemă ca exemplu.

EXEMPLUL 3. În urma experimentului s-a obținut 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 metoda celor mai mici pătrate.

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

Să luăm în considerare procesul de rezolvare a problemei de optimizare (Fig. 14).

Lasă valorile A, ÎN, CU, DȘi LA stocate în celule A7:E7. Să calculăm valorile teoretice ale funcției Z(t)=La 4 +Bt 3 +Ct 2 +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.

Să copiem această formulă în interval C4:J4și obțineți valoarea așteptată a funcției în punctele ale căror abscise sunt stocate în celule B2:J2.

La celulă B5 Să 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 C5:J5. Într-o celulă F7 vom stoca eroarea totală pătrată (10). Pentru a face acest lucru, introduceți formula:

F7 = SUMA(B5:J5).

Să folosim comanda Service®Căutați o soluțieși rezolvați problema de optimizare fără restricții. Să completăm în mod corespunzător câmpurile de introducere din caseta de dialog prezentată în Fig. 14 și apăsați butonul A executa. Dacă se găsește o soluție, fereastra prezentată în fig. 15.

Rezultatul blocului de decizie va fi transmis în celule A7:E7valorile parametrilor funcții Z(t)=La 4 +Bt 3 +Ct 2 +Dt+K. În celule B4:J4 primim valoarea așteptată a funcției la punctele de plecare. Într-o celulă F7 vor fi stocate eroare pătrată totală.

Puteți afișa puncte experimentale și o linie adaptată într-o zonă grafică selectând un interval B2:J4, apel Chart Wizardși apoi formatați aspect grafice primite.

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

5. REFERINȚE

1. Alekseev E.R., Chesnokova O.V., Rezolvarea problemelor de matematică computațională în pachetele Mathcad12, MATLAB7, Maple9. – NT Press, 2006.–596 p. :il. -(Tutorial)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, rezolvarea problemelor de inginerie și matematică. –M., BIOM, 2008.–260 p.

3. Berezin I.S., Zhidkov N.P., Metode de calcul – M.: Nauka, 1966. – 632 p.

4. Garnaev A.Yu., Utilizarea MS EXCEL și VBA în economie și finanțe. – Sankt Petersburg: BHV - Petersburg, 1999.–332 p.

5. Demidovich B.P., Maron I.A., Shuvalova V.Z., Metode numerice de analiză – M.: Nauka, 1967. – 368 p.

6. Korn G., Korn T., Manual de matematică pentru oameni de știință și ingineri – M., 1970, 720 p.

7. Alekseev E.R., Chesnokova O.V. Ghid pentru efectuarea lucrărilor de laborator în MS EXCEL. Pentru studenții de toate specialitățile. Doneţk, DonNTU, 2004. 112 p.