Analiza regresiei. Metoda celor mai mici pătrate în Excel. Analiza regresiei Metoda celor mai mici pătrate în funcția de putere Excel

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 contează 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 o 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 contează ADEVĂRAT, apoi funcția LINIST returnează statistici suplimentare de regresie. Dacă argumentul Statistici contează MINCIUNĂ sau omis, apoi funcția LINIST returnează doar coeficientul oși constantă 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 î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 OŞi b selectați celulele A7:B7, Să mergem la vrăjitorul de funcții și la categorie Statistic alegeț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 OŞ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. La graficul de date experimentale va fi adăugată o linie de regresie. În mod implicit, graficul său va fi desenat 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 cu linii și selectați comanda Tipul graficuluiși setați tipul diagramei 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ă sub forma unei proprietăți grafice - linie de tendință.

EXEMPLUL 2. În urma experimentului s-a determinat o anumită 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 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 aproximativă.

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. Utilizarea instrumentului de analiză a opțiunilor: Găsirea unei soluții.

Un interes semnificativ este implementarea selecției parametrilor în MS Excel dependenta functionala metodă cele mai mici pătrate folosind instrumentul de analiză a opțiunilor: Găsirea unei soluții. 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 O, Î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 pătrată totală (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 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, sunați Chart Wizardși apoi formatați aspect grafice 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 un set de două serii de numere. Scopul utilizării acestei metode este de a minimiza eroarea pătrată totală. Excel are instrumente pe care le puteți folosi această metodăîn timpul calculelor. 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 în 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 "Controla", 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ții de problemă

Să descriem aplicarea LSM 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 de funcția:

În același timp, se știe că atunci când x=0 y de asemenea egale 0 . De aceea ecuația dată 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 Intră.

3. Folosind marcatorul de umplere, copiați această formulă pentru întreaga gamă a 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 "La" 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 „Găsiți o soluție”.

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 concepute pentru a simplifica calculele cât mai mult posibil.

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

Prevederi generale

Cum număr mai micîn valoare absolută, cu atât mai bine este selectată linia dreaptă (2). 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 (LS) 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 = o x + b .

Metoda celor mai mici pătrate(engleză) Comun Cel mai puţin Pătrate , O.L.S.) 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.

Să luăm în considerare aproximarea prin funcții care depind doar de o variabilă:

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

Nota: 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ă

Ne interesează legătura dintre 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 (x i; y i).

Nota: Dacă pasul de schimbare este X este constantă, apoi să construiască diagrame de dispersie poate fi folosit, dacă nu, atunci trebuie să utilizați tipul de diagramă Loc .

Din diagramă este evident că relația dintre variabile este apropiată de liniară. Pentru a înțelege care dintre numeroasele linii 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 = o * 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ă.

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

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 OLS va selecta totuși linia „cea mai bună”. Astfel, metoda celor mai mici pătrate nu spune nimic despre prezența unei relații reale între variabile, metoda vă permite pur și simplu să selectați astfel de parametri ai funcției; o Şi b , pentru care expresia de mai sus este minimă.

Efectuând operații matematice nu foarte complexe (pentru mai multe detalii, vezi), puteți calcula parametrii o Şi b :

După cum se poate vedea din formulă, parametrul o reprezintă raportul de covarianță și, prin urmare, în MS EXCEL pentru a calcula parametrul O Puteți utiliza următoarele formule (vezi Fișier exemplu de foaie liniară):

= KOVAR(B26:B45;C26:C45)/ DISP.G(B26:B45) sau

= COVARIANȚĂ.B(B26:B45;C26:C45)/DISP.B(B26:B45)

De asemenea, pentru a calcula parametrul O puteți folosi formula = INCLINARE(C26:C45;B26:B45). Pentru parametru b utilizați formula = PICIOARE(C26:C45;B26:B45) .

În cele din urmă, funcția LINEST() vă permite să calculați ambii parametrii simultan. Pentru a introduce o formulă LINIE(C26:C45;B26:B45) Trebuie să selectați 2 celule la rând și să faceți clic CTRL + SCHIMBARE + ENTER(vezi articolul despre). Valoarea va fi returnată în celula din stânga O , pe dreapta - b .

Nota: Pentru a evita încurcătura cu intrarea formule matrice va trebui să utilizați suplimentar funcția INDEX(). Formula = INDEX(LINIA(C26:C45;B26:B45),1) sau doar = LINIE(C26:C45;B26:B45) va returna parametrul responsabil pentru panta dreptei, i.e. O . Formula = INDEX(LINIA(C26:C45;B26:B45),2) va returna parametrul responsabil pentru intersectia liniei cu axa Y, i.e. b .

După calcularea parametrilor, diagrama de dispersie puteți trage linia corespunzătoare.

Un alt mod de a trasa o linie dreaptă folosind metoda celor mai mici pătrate este instrumentul grafic Linia de tendințe. Pentru a face acest lucru, selectați diagrama, selectați din meniu fila Aspect, V Analiza de grup clic Linia de tendințe, atunci 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 coincid cu valorile din diagramă.

Nota: Pentru ca parametrii să se potrivească, tipul diagramei trebuie să fie . Ideea este că atunci când construiești o diagramă Programa Valorile axei X nu pot fi specificate 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 categorii de numerotare). Prin urmare, dacă construiești linie de tendință pe o diagramă de tip Programa, atunci în locul valorilor reale ale lui X se vor folosi valorile acestei secvențe, ceea ce va duce la un rezultat incorect (cu excepția cazului în care, desigur, valorile reale ale lui X nu coincid cu secvența 1; 2; 3; ...).

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,

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 î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 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 OŞ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).

Orez. 6

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ă sub forma unei proprietăți grafice - linie de tendință.

EXEMPLUL 2. În urma experimentului s-a determinat o anumită 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 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.

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

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ă).

Î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).

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 pătrată totală (10). Pentru a face acest lucru, introduceți formula:

F7 = SUMA(B5:J5).

Puteți afișa puncte experimentale și o linie adaptată într-o zonă grafică selectând un interval B2:J4, sunați Chart Wizardși apoi formatați aspectul graficelor rezultate.

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, soluție pentru inginerie și probleme matematice. –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 analiză – M.: Nauka, 1967. – 368 p.

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

7. Alekseev E.R., Chesnokova O.V. Orientări la implementare munca de laboratorîn MS EXCEL. Pentru studenții de toate specialitățile. Doneţk, DonNTU, 2004. 112 p.

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.

Metoda celor mai mici pătrate (LSM) este o descriere matematică a dependenței unei variabile de alta. Poate fi folosit în 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.

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

Condiții de problemă

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

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.