Erori absolute ale celor mai mici pătrate. Analiză de regresie liniară pe perechi

Dacă o cantitate fizică depinde de o altă mărime, atunci această dependență poate fi investigată prin măsurarea y la diferite valori ale lui x. În urma măsurătorilor, se obține o serie de valori:

x 1 , x 2 , ..., x i , ... , x n ;

y 1 , y 2 , ..., y i , ... , y n .

Pe baza datelor unui astfel de experiment, este posibilă reprezentarea grafică a dependenței y = ƒ(x). Curba rezultată face posibilă aprecierea formei funcției ƒ(x). Cu toate acestea, coeficienții constanți care intră în această funcție rămân necunoscuți. Ele pot fi determinate folosind metoda celor mai mici pătrate. Punctele experimentale, de regulă, nu se află exact pe curbă. Metoda celor mai mici pătrate necesită ca suma abaterilor pătrate ale punctelor experimentale de la curbă, i.e. 2 a fost cel mai mic.

În practică, această metodă este folosită cel mai des (și cel mai simplu) în cazul unei relații liniare, adică Când

y=kx sau y = a + bx.

Dependența liniară este foarte răspândită în fizică. Și chiar și atunci când dependența este neliniară, de obicei încearcă să construiască un grafic în așa fel încât să obțină o linie dreaptă. De exemplu, dacă se presupune că indicele de refracție al sticlei n este legat de lungimea de undă λ a undei luminoase prin relația n = a + b/λ 2 , atunci dependența lui n de λ -2 este reprezentată pe grafic .

Luați în considerare dependența y=kx(linie dreaptă care trece prin origine). Să compunem valoarea φ suma abaterilor pătrate ale punctelor noastre de la dreapta

Valoarea lui φ este întotdeauna pozitivă și se dovedește a fi mai mică, cu atât punctele noastre sunt mai aproape de linie dreaptă. Metoda celor mai mici pătrate afirmă că pentru k ar trebui să se aleagă o astfel de valoare la care φ are un minim

sau
(19)

Calculul arată că eroarea pătratică medie în determinarea valorii lui k este egală cu

, (20)
unde n este numărul de dimensiuni.

Să luăm acum în considerare un caz ceva mai dificil, când punctele trebuie să satisfacă formula y = a + bx(o linie dreaptă care nu trece prin origine).

Sarcina este de a găsi cele mai bune valori ale lui a și b din setul dat de valori x i , y i .

Din nou compunem o formă pătratică φ egală cu suma abaterilor pătrate ale punctelor x i , y i de la dreapta

și găsiți valorile a și b pentru care φ are un minim

;

.

Rezolvarea comună a acestor ecuații dă

(21)

Erorile pătratice medii ale determinării a și b sunt egale

(23)

.  (24)

La procesarea rezultatelor măsurătorilor prin această metodă, este convenabil să rezumați toate datele într-un tabel în care sunt calculate preliminar toate sumele incluse în formulele (19)(24). Formele acestor tabele sunt prezentate în exemplele de mai jos.

Exemplul 1 A fost studiată ecuația de bază a dinamicii mișcării de rotație ε = M/J (o dreaptă care trece prin origine). Pentru diferite valori ale momentului M, a fost măsurată accelerația unghiulară ε a unui anumit corp. Este necesar să se determine momentul de inerție al acestui corp. Rezultatele măsurătorilor momentului de forță și accelerației unghiulare sunt enumerate în a doua și a treia coloană tabelele 5.

Tabelul 5

Prin formula (19) determinăm:

.

Pentru a determina eroarea pătratică medie, folosim formula (20)

0.005775kg-1 · m -2 .

Prin formula (18) avem

SJ = (2,996 0,005775)/0,3337 = 0,05185 kg m2.

Având în vedere fiabilitatea P = 0,95 , conform tabelului coeficienților Student pentru n = 5, găsim t = 2,78 și determinăm eroarea absolută ΔJ = 2,78 0,05185 = 0,1441 ≈ 0,2 kg m2.

Scriem rezultatele sub forma:

J = (3,0 ± 0,2) kg m2;

Exemplul 2 Calculăm coeficientul de temperatură al rezistenței metalului folosind metoda celor mai mici pătrate. Rezistența depinde de temperatură conform unei legi liniare

R t \u003d R 0 (1 + α t °) \u003d R 0 + R 0 α t °.

Termenul liber determină rezistența R 0 la o temperatură de 0 ° C, iar coeficientul unghiular este produsul dintre coeficientul de temperatură α și rezistența R 0 .

Rezultatele măsurătorilor și calculelor sunt prezentate în tabel ( vezi tabelul 6).

Tabelul 6

Prin formulele (21), (22) determinăm

R 0 = ¯ R- α R 0 ¯ t = 1,4005 - 0,002645 85,83333 = 1,1735 Ohm.

Să găsim o eroare în definiția lui α. Deoarece , atunci prin formula (18) avem:

.

Folosind formulele (23), (24) avem

;

0.014126 Ohm.

Având în vedere fiabilitatea P = 0,95, conform tabelului coeficienților lui Student pentru n = 6, găsim t = 2,57 și determinăm eroarea absolută Δα = 2,57 0,000132 = 0,000338 grade -1.

α = (23 ± 4) 10 -4 grindină-1 la P = 0,95.

Exemplul 3 Este necesară determinarea razei de curbură a lentilei din inelele lui Newton. Au fost măsurate razele inelelor lui Newton r m și au fost determinate numerele acestor inele m. Razele inelelor lui Newton sunt legate de raza de curbură a lentilei R și de numărul inelului prin ecuație

r 2 m = mλR - 2d 0 R,

unde d 0 grosimea spațiului dintre lentilă și placa plan-paralelă (sau deformarea lentilei),

λ este lungimea de undă a luminii incidente.

A = (600 ± 6) nm;
r 2 m = y;
m = x;
λR = b;
-2d 0 R = a,

atunci ecuația va lua forma y = a + bx.

Rezultatele măsurătorilor și calculelor sunt introduse tabelul 7.

Tabelul 7

• tutorial

Introducere

Eu sunt un programator. Am făcut cel mai mare salt din cariera mea când am învățat să spun: "Eu nu înțeleg nimic!" Acum nu mi-e rușine să-i spun luminatorului științei că îmi ține o prelegere, că nu înțeleg despre ce îmi vorbește acesta, luminatorul. Și este foarte greu. Da, este greu și jenant să recunoști că nu știi. Cui îi place să recunoască că nu știe elementele de bază ale ceva. În virtutea profesiei mele, trebuie să asist la un număr mare de prezentări și prelegeri, unde, mărturisesc, în marea majoritate a cazurilor îmi este somnoros, pentru că nu înțeleg nimic. Și nu înțeleg pentru că problema uriașă a situației actuale în știință constă în matematică. Se presupune că toți elevii sunt familiarizați cu absolut toate domeniile matematicii (ceea ce este absurd). Să recunoști că nu știi ce este un derivat (că acesta este puțin mai târziu) este păcat.

Dar am învățat să spun că nu știu ce este înmulțirea. Da, nu știu ce este o subalgebră peste o algebră Lie. Da, nu știu de ce sunt necesare ecuații patratice în viață. Apropo, dacă ești sigur că știi, atunci avem despre ce să vorbim! Matematica este o serie de trucuri. Matematicienii încearcă să încurce și să intimideze publicul; unde nu există confuzie, nici reputație, nici autoritate. Da, este prestigios să vorbești într-un limbaj cel mai abstract posibil, ceea ce este un nonsens în sine.

Știți ce este un derivat? Cel mai probabil îmi veți spune despre limita relației de diferență. În primul an de matematică la Universitatea de Stat din Sankt Petersburg, Viktor Petrovici Khavin mă definit derivată ca coeficient al primului termen al seriei Taylor al funcției la punctul (a fost o gimnastică separată pentru a determina seria Taylor fără derivate). Am râs mult timp de această definiție, până am înțeles în sfârșit despre ce este vorba. Derivata nu este altceva decât o măsură a cât de mult este similară funcției pe care o diferențiem cu funcția y=x, y=x^2, y=x^3.

Acum am onoarea de a preda studenți care frică matematică. Dacă ți-e frică de matematică - suntem pe drum. De îndată ce încerci să citești ceva text și ți se pare că este prea complicat, atunci știi că este prost scris. Susțin că nu există o singură zonă a matematicii despre care să nu se poată vorbi despre „pe degete” fără a pierde acuratețea.

Provocarea pentru viitorul apropiat: mi-am instruit elevii să înțeleagă ce este un controler liniar-quadratic. Nu fi timid, pierde trei minute din viața ta, urmărește linkul. Dacă nu înțelegi nimic, atunci suntem pe drum. Nici eu (matematician-programator profesionist) nu am inteles nimic. Și vă asigur că asta se poate rezolva „pe degete”. Momentan nu știu ce este, dar vă asigur că vom reuși să ne dăm seama.

Așadar, prima prelegere pe care o voi ține studenților mei după ce vor veni în fugă la mine îngroziți cu cuvintele că controlerul liniar-quadratic este un bug teribil pe care nu îl vei stăpâni niciodată în viața ta este metodele celor mai mici pătrate. Puteți rezolva ecuații liniare? Dacă citiți acest text, atunci cel mai probabil nu.

Deci, având în vedere două puncte (x0, y0), (x1, y1), de exemplu, (1,1) și (3,2), sarcina este de a găsi ecuația unei drepte care trece prin aceste două puncte:

ilustrare

Această linie dreaptă ar trebui să aibă o ecuație ca următoarea:

Aici alfa și beta ne sunt necunoscute, dar două puncte ale acestei linii sunt cunoscute:

Puteți scrie această ecuație sub formă de matrice:

Aici ar trebui să facem o digresiune lirică: ce este o matrice? O matrice nu este altceva decât o matrice bidimensională. Acesta este un mod de stocare a datelor, nu ar trebui să i se atașeze mai multe valori. Depinde de noi cum să interpretăm exact o anumită matrice. Periodic, o voi interpreta ca o mapare liniară, periodic ca o formă pătratică și uneori pur și simplu ca un set de vectori. Toate acestea vor fi clarificate în context.

Să înlocuim matricele specifice cu reprezentarea lor simbolică:

Apoi (alfa, beta) pot fi găsite cu ușurință:

Mai precis pentru datele noastre anterioare:

Ceea ce duce la următoarea ecuație a unei drepte care trece prin punctele (1,1) și (3,2):

Bine, totul este clar aici. Și să găsim ecuația unei drepte care trece prin Trei puncte: (x0,y0), (x1,y1) și (x2,y2):

Oh-oh-oh, dar avem trei ecuații pentru două necunoscute! Matematicianul standard va spune că nu există o soluție. Ce va spune programatorul? Și mai întâi va rescrie sistemul anterior de ecuații în următoarea formă:

În cazul nostru, vectorii i, j, b sunt tridimensionali, prin urmare, (în cazul general) nu există o soluție pentru acest sistem. Orice vector (alfa\*i + beta\*j) se află în planul acoperit de vectorii (i, j). Dacă b nu aparține acestui plan, atunci nu există soluție (egalitatea în ecuație nu poate fi obținută). Ce să fac? Să căutăm un compromis. Să notăm prin e(alfa, beta) cum exact nu am atins egalitatea:

Și vom încerca să minimizăm această eroare:

De ce un pătrat?

Căutăm nu doar minimul normei, ci și minimul pătratului normei. De ce? Punctul minim în sine coincide, iar pătratul dă o funcție netedă (o funcție pătratică a argumentelor (alfa,beta)), în timp ce doar lungimea dă o funcție sub formă de con, nediferențiabilă la punctul minim. Brr. Square este mai convenabil.

Evident, eroarea este minimizată atunci când vectorul e ortogonală cu planul acoperit de vectori iȘi j.

Ilustrare

Cu alte cuvinte: căutăm o dreaptă astfel încât suma pătratelor lungimii distanțelor de la toate punctele la această dreaptă să fie minimă:

UPDATE: aici am un jamb, distanța până la linie trebuie măsurată pe verticală, nu proiecție ortografică. comentatorul are dreptate.

Ilustrare

Cu cuvinte complet diferite (atenție, prost formalizate, dar ar trebui să fie clar pe degete): luăm toate liniile posibile între toate perechile de puncte și căutăm linia medie între toate:

Ilustrare

O altă explicație pe degete: atașăm un arc între toate punctele de date (aici avem trei) și linia pe care o căutăm, iar linia stării de echilibru este exact ceea ce căutăm.

Forma cuadratică minimă

Cu alte cuvinte, căutăm un vector x=(alfa, beta) astfel încât:

Vă reamintesc că acest vector x=(alfa, beta) este minimul funcției pătratice ||e(alfa, beta)||^2:

Aici este util să ne amintim că matricea poate fi interpretată la fel ca și forma pătratică, de exemplu, matricea de identitate ((1,0),(0,1)) poate fi interpretată ca o funcție a x^2 + y ^2:

formă pătratică

Toată această gimnastică este cunoscută ca regresie liniară.

Ecuația Laplace cu condiția la limită Dirichlet

Commit-ul original este disponibil. Pentru a minimiza dependențele externe, am luat codul programului meu de redare software, deja pe Habré. Pentru a rezolva sistemul liniar, folosesc OpenNL , este un solutor grozav, dar este foarte greu de instalat: trebuie să copiați două fișiere (.h + .c) în folderul proiectului. Toată netezirea se face prin următorul cod:

Pentru (int d=0; d<3; d++) { nlNewContext(); nlSolverParameteri(NL_NB_VARIABLES, verts.size()); nlSolverParameteri(NL_LEAST_SQUARES, NL_TRUE); nlBegin(NL_SYSTEM); nlBegin(NL_MATRIX); for (int i=0; i<(int)verts.size(); i++) { nlBegin(NL_ROW); nlCoefficient(i, 1); nlRightHandSide(verts[i][d]); nlEnd(NL_ROW); } for (unsigned int i=0; i&face = fețe[i]; pentru (int j=0; j<3; j++) { nlBegin(NL_ROW); nlCoefficient(face[ j ], 1); nlCoefficient(face[(j+1)%3], -1); nlEnd(NL_ROW); } } nlEnd(NL_MATRIX); nlEnd(NL_SYSTEM); nlSolve(); for (int i=0; i<(int)verts.size(); i++) { verts[i][d] = nlGetVariable(i); } }

Coordonatele X, Y și Z sunt separabile, le netezesc separat. Adică rezolv trei sisteme de ecuații liniare, fiecare cu același număr de variabile ca numărul de vârfuri din modelul meu. Primele n rânduri ale matricei A au doar un 1 pe rând, iar primele n rânduri ale vectorului b au coordonatele modelului original. Adică, fac legătura între noua poziție de vârf și vechea poziție de vârf - cele noi nu ar trebui să fie prea departe de cele vechi.

Toate rândurile ulterioare ale matricei A (faces.size()*3 = numărul de muchii ale tuturor triunghiurilor din grilă) au o apariție de 1 și o apariție de -1, în timp ce vectorul b are componente zero opuse. Aceasta înseamnă că am pus un arc pe fiecare margine a rețelei noastre triunghiulare: toate marginile încearcă să obțină același vârf ca punctele lor de început și de sfârșit.

Încă o dată: toate nodurile sunt variabile și nu se pot abate departe de poziția lor inițială, dar în același timp încearcă să devină asemănătoare între ele.

Iată rezultatul:

Totul ar fi bine, modelul este cu adevărat netezit, dar s-a îndepărtat de marginea inițială. Hai sa schimbam putin codul:

Pentru (int i=0; i<(int)verts.size(); i++) { float scale = border[i] ? 1000: 1; nlBegin(NL_ROW); nlCoefficient(i, scale); nlRightHandSide(scale*verts[i][d]); nlEnd(NL_ROW); }

În matricea noastră A, pentru vârfurile care sunt pe margine, nu adaug un rând din categoria v_i = verts[i][d], ci 1000*v_i = 1000*verts[i][d]. Ce se schimbă? Și asta schimbă forma noastră pătratică a erorii. Acum, o singură abatere de la partea de sus la margine va costa nu o unitate, ca înainte, ci 1000 * 1000 de unități. Adică am atârnat un arc mai puternic pe vârfurile extreme, soluția preferă să le întindă pe altele mai puternic. Iată rezultatul:

Să dublăm puterea arcurilor dintre vârfuri:
nlCoeficient(față[ j ], 2); nlCoeficient(față[(j+1)%3], -2);

Este logic că suprafața a devenit mai netedă:

Și acum chiar de o sută de ori mai puternic:

Ce este asta? Imaginează-ți că am scufundat un inel de sârmă în apă cu săpun. Drept urmare, pelicula de săpun rezultată va încerca să aibă cea mai mică curbură posibil, atingând aceeași margine - inelul nostru de sârmă. Este exact ceea ce am obținut fixând chenarul și cerând o suprafață netedă în interior. Felicitări, tocmai am rezolvat ecuația Laplace cu condițiile la limită Dirichlet. Suna bine? Dar, de fapt, un singur sistem de ecuații liniare de rezolvat.

Ecuația Poisson

Să zicem că am o imagine ca aceasta:

Toată lumea este bună, dar nu-mi place scaunul.

Am tăiat poza în jumătate:

Și voi alege un scaun cu mâinile mele:

Apoi voi trage tot ce este alb în mască în partea stângă a imaginii și, în același timp, voi spune pe parcursul întregii imagini că diferența dintre doi pixeli vecini ar trebui să fie egală cu diferența dintre doi pixeli vecini ai imaginii. imagine dreapta:

Pentru (int i=0; i

Iată rezultatul:

Codul și imaginile sunt disponibile

Esența metodei celor mai mici pătrate este în găsirea parametrilor unui model de tendință care descrie cel mai bine tendința de dezvoltare a unui fenomen aleatoriu în timp sau spațiu (o tendință este o linie care caracterizează tendința acestei dezvoltări). Sarcina metodei celor mai mici pătrate (OLS) este de a găsi nu doar un model de tendință, ci de a găsi cel mai bun sau optim model. Acest model va fi optim dacă suma abaterilor pătrate dintre valorile reale observate și valorile de tendință calculate corespunzătoare este minimă (cea mai mică):

unde este abaterea standard dintre valoarea reală observată

și valoarea de tendință calculată corespunzătoare,

Valoarea reală (observată) a fenomenului studiat,

Valoarea estimată a modelului de tendințe,

Numărul de observații ale fenomenului studiat.

MNC este rareori folosit pe cont propriu. De regulă, cel mai adesea este folosit doar ca tehnică necesară în studiile de corelare. Trebuie amintit că baza informațională a LSM poate fi doar o serie statistică de încredere, iar numărul de observații nu trebuie să fie mai mic de 4, în caz contrar, procedurile de netezire ale LSM-ului își pot pierde bunul simț.

Setul de instrumente OLS este redus la următoarele proceduri:

Prima procedură. Se dovedește dacă există vreo tendință de a schimba atributul rezultat atunci când factorul-argument selectat se schimbă sau, cu alte cuvinte, dacă există o legătură între " la " Și " X ».

A doua procedură. Se stabilește care linie (traiectorie) este cel mai în măsură să descrie sau să caracterizeze această tendință.

A treia procedură.

Exemplu. Să presupunem că avem informații despre randamentul mediu de floarea soarelui pentru ferma studiată (Tabelul 9.1).

Tabelul 9.1

Întrucât nivelul tehnologiei în producția de floarea soarelui în țara noastră nu s-a schimbat foarte mult în ultimii 10 ani, înseamnă că, cel mai probabil, fluctuațiile randamentului în perioada analizată au depins foarte mult de fluctuațiile condițiilor meteo și climatice. Este adevarat?

Prima procedură MNC. Se testează ipoteza despre existența unei tendințe de modificare a randamentului de floarea-soarelui în funcție de modificările condițiilor meteo și climatice pe parcursul celor 10 ani analizați.

În acest exemplu, pentru " y » este indicat să luați randamentul de floarea soarelui, iar pentru « X » este numărul anului observat în perioada analizată. Testarea ipotezei despre existența oricărei relații între " X " Și " y » se poate face in doua moduri: manual si cu ajutorul programelor de calculator. Desigur, odată cu disponibilitatea tehnologiei informatice, această problemă se rezolvă de la sine. Dar, pentru a înțelege mai bine setul de instrumente OLS, este recomandabil să testați ipoteza despre existența unei relații între " X " Și " y » manual, când sunt la îndemână doar un pix și un calculator obișnuit. În astfel de cazuri, ipoteza existenței unei tendințe este cel mai bine verificată vizual prin locația imaginii grafice a seriei temporale analizate - câmpul de corelație:

Câmpul de corelație din exemplul nostru este situat în jurul unei linii care crește încet. Acest lucru în sine indică existența unei anumite tendințe în schimbarea producției de floarea soarelui. Este imposibil să vorbim despre prezența oricărei tendințe doar atunci când câmpul de corelare arată ca un cerc, un cerc, un nor strict vertical sau strict orizontal sau este format din puncte împrăștiate aleatoriu. În toate celelalte cazuri, este necesar să se confirme ipoteza existenței unei relații între " X " Și " y și continuă cercetarea.

A doua procedură MNC. Se determină care linie (traiectorie) este cel mai în măsură să descrie sau să caracterizeze tendința modificărilor producției de floarea-soarelui pentru perioada analizată.

Odată cu disponibilitatea tehnologiei informatice, selectarea tendinței optime are loc automat. Cu prelucrarea „manuală”, alegerea funcției optime se realizează, de regulă, într-un mod vizual - prin locația câmpului de corelare. Adică, în funcție de tipul de diagramă, este selectată ecuația liniei, care se potrivește cel mai bine tendinței empirice (la traiectoria reală).

După cum știți, în natură există o mare varietate de dependențe funcționale, deci este extrem de dificil să analizați vizual chiar și o mică parte din ele. Din fericire, în practica economică reală, majoritatea relațiilor pot fi descrise cu acuratețe fie printr-o parabolă, fie printr-o hiperbolă, fie printr-o linie dreaptă. În acest sens, cu opțiunea „manual” pentru selectarea celei mai bune funcții, te poți limita doar la aceste trei modele.

Parabola de ordinul doi: :

Este ușor de observat că, în exemplul nostru, tendința de modificare a producției de floarea-soarelui pe parcursul celor 10 ani analizați este cel mai bine caracterizată printr-o linie dreaptă, astfel încât ecuația de regresie va fi o ecuație în linie dreaptă.

A treia procedură. Se calculează parametrii ecuației de regresie care caracterizează această linie sau, cu alte cuvinte, se determină o formulă analitică care descrie cel mai bun model de tendință.

Găsirea valorilor parametrilor ecuației de regresie, în cazul nostru, parametrii și , este nucleul LSM. Acest proces se reduce la rezolvarea unui sistem de ecuații normale.

(9.2)

Acest sistem de ecuații este destul de ușor de rezolvat prin metoda Gauss. Amintiți-vă că, ca urmare a soluției, în exemplul nostru, se găsesc valorile parametrilor și. Astfel, ecuația de regresie găsită va avea următoarea formă:

După aliniere, obținem o funcție de următoarea formă: g (x) = x + 1 3 + 1 .

Putem aproxima aceste date cu o relație liniară y = a x + b calculând parametrii corespunzători. Pentru a face acest lucru, va trebui să aplicăm așa-numita metodă a celor mai mici pătrate. De asemenea, va trebui să faceți un desen pentru a verifica care linie va alinia cel mai bine datele experimentale.

Yandex.RTB R-A-339285-1

Ce este exact MOL (metoda celor mai mici pătrate)

Principalul lucru pe care trebuie să-l facem este să găsim astfel de coeficienți de dependență liniară la care valoarea funcției a două variabile F (a, b) = ∑ i = 1 n (y i - (a x i + b)) 2 va fi valoarea cel mai mic. Cu alte cuvinte, pentru anumite valori ale lui a și b, suma abaterilor pătrate ale datelor prezentate de la linia dreaptă rezultată va avea o valoare minimă. Acesta este sensul metodei celor mai mici pătrate. Tot ce trebuie să facem pentru a rezolva exemplul este să găsim extremul funcției a două variabile.

Cum se obțin formule pentru calcularea coeficienților

Pentru a deriva formule de calcul a coeficientilor este necesara alcatuirea si rezolvarea unui sistem de ecuatii cu doua variabile. Pentru a face acest lucru, calculăm derivatele parțiale ale expresiei F (a , b) = ∑ i = 1 n (y i - (a x i + b)) 2 față de a și b și le echivalăm cu 0 .

δ F (a , b) δ a = 0 δ F (a , b) δ b = 0 ⇔ - 2 ∑ i = 1 n (y i - (a x i + b)) x i = 0 - 2 ∑ i = 1 n ( y i - (a x i + b)) = 0 ⇔ a ∑ i = 1 n x i 2 + b ∑ i = 1 n x i = ∑ i = 1 n x i y i a ∑ i = 1 n x i + ∑ i = 1 n b = ∑ i = 1 n y i ⇔ ∑ i = 1 n x i 2 + b ∑ i = 1 n x i = ∑ i = 1 n x i y i a ∑ i = 1 n x i + n b = ∑ i = 1 n y i

Pentru a rezolva un sistem de ecuații, puteți utiliza orice metodă, cum ar fi substituția sau metoda lui Cramer. Ca rezultat, ar trebui să obținem formule care calculează coeficienții folosind metoda celor mai mici pătrate.

n ∑ i = 1 n x i y i - ∑ i = 1 n x i ∑ i = 1 n y i n ∑ i = 1 n - ∑ i = 1 n x i 2 b = ∑ i = 1 n y i - a ∑ i = 1 n x i n

Am calculat valorile variabilelor pentru care funcția
F (a , b) = ∑ i = 1 n (y i - (a x i + b)) 2 va lua valoarea minimă. În al treilea paragraf, vom demonstra de ce este așa.

Aceasta este aplicarea metodei celor mai mici pătrate în practică. Formula sa, care este folosită pentru a găsi parametrul a , include ∑ i = 1 n x i , ∑ i = 1 n y i , ∑ i = 1 n x i y i , ∑ i = 1 n x i 2 , iar parametrul
n - denotă cantitatea de date experimentale. Vă sfătuim să calculați fiecare sumă separat. Valoarea coeficientului b se calculează imediat după a .

Să revenim la exemplul inițial.

Exemplul 1

Aici avem n egal cu cinci. Pentru a face mai convenabil calculul sumelor necesare incluse în formulele coeficientului, completăm tabelul.

Soluţie

Al patrulea rând conține datele obținute prin înmulțirea valorilor din al doilea rând cu valorile celui de-al treilea pentru fiecare individ i . A cincea linie conține datele din al doilea pătrat. Ultima coloană arată sumele valorilor rândurilor individuale.

Să folosim metoda celor mai mici pătrate pentru a calcula coeficienții a și b de care avem nevoie. Pentru a face acest lucru, înlocuiți valorile dorite din ultima coloană și calculați sumele:

n ∑ i = 1 n x i y i - ∑ i = 1 n x i ∑ i = 1 n y i n ∑ i = 1 n - ∑ i = 1 n x i 2 b = ∑ i = 1 n y i - a ∑ i = 1 n x i n ⇒ a = 5 8 - 12 12, 9 5 46 - 12 2 b = 12, 9 - a 12 5 ⇒ a ≈ 0, 165 b ≈ 2, 184

Am obținut că linia dreaptă de aproximare dorită va arăta ca y = 0, 165 x + 2, 184. Acum trebuie să determinăm care linie va aproxima cel mai bine datele - g (x) = x + 1 3 + 1 sau 0 , 165 x + 2 , 184 . Să facem o estimare folosind metoda celor mai mici pătrate.

Pentru a calcula eroarea, trebuie să găsim sumele abaterilor pătrate ale datelor din liniile σ 1 = ∑ i = 1 n (y i - (a x i + b i)) 2 și σ 2 = ∑ i = 1 n (y i - g (x i)) 2 , valoarea minimă va corespunde unei linii mai potrivite.

σ 1 = ∑ i = 1 n (y i - (a x i + b i)) 2 = = ∑ i = 1 5 (y i - (0 , 165 x i + 2 , 184)) 2 ≈ 0 , 019 σ 2 = ∑ i = 1 n (y i - g (x i)) 2 = = ∑ i = 1 5 (y i - (x i + 1 3 + 1)) 2 ≈ 0 , 096

Răspuns: deoarece σ 1< σ 2 , то прямой, наилучшим образом аппроксимирующей исходные данные, будет
y = 0 , 165 x + 2 , 184 .

Metoda celor mai mici pătrate este prezentată clar în ilustrația grafică. Linia roșie marchează linia dreaptă g (x) = x + 1 3 + 1, linia albastră marchează y = 0, 165 x + 2, 184. Datele brute sunt marcate cu puncte roz.

Să explicăm de ce sunt necesare exact aproximări de acest tip.

Ele pot fi utilizate în probleme care necesită netezirea datelor, precum și în acelea în care datele trebuie interpolate sau extrapolate. De exemplu, în problema discutată mai sus, se poate găsi valoarea mărimii observate y la x = 3 sau la x = 6 . Am dedicat un articol separat unor astfel de exemple.

Dovada metodei LSM

Pentru ca funcția să ia valoarea minimă atunci când se calculează a și b, este necesar ca la un punct dat matricea formei pătratice a diferenţialului funcţiei de forma F (a, b) = ∑ i = 1 n (y i - (a x i + b)) 2 fi definit pozitiv. Să vă arătăm cum ar trebui să arate.

Exemplul 2

Avem o diferenţială de ordinul doi de următoarea formă:

d 2 F (a ; b) = δ 2 F (a ; b) δ a 2 d 2 a + 2 δ 2 F (a ; b) δ a δ b d a d b + δ 2 F (a ; b) δ b 2 d 2b

Soluţie

δ 2 F (a ; b) δ a 2 = δ δ F (a ; b) δ a δ a = = δ - 2 ∑ i = 1 n (y i - (a x i + b)) x i δ a = 2 ∑ i = 1 n (x i) 2 δ 2 F (a ; b) δ a δ b = δ δ F (a ; b) δ a δ b = = δ - 2 ∑ i = 1 n (y i - (a x i + b) ) x i δ b = 2 ∑ i = 1 n x i δ 2 F (a ; b) δ b 2 = δ δ F (a ; b) δ b δ b = δ - 2 ∑ i = 1 n (y i - (a x i + b)) δ b = 2 ∑ i = 1 n (1) = 2 n

Cu alte cuvinte, se poate scrie astfel: d 2 F (a ; b) = 2 ∑ i = 1 n (x i) 2 d 2 a + 2 2 ∑ x i i = 1 n d a d b + (2 n) d 2 b .

Am obţinut o matrice de formă pătratică M = 2 ∑ i = 1 n (x i) 2 2 ∑ i = 1 n x i 2 ∑ i = 1 n x i 2 n .

În acest caz, valorile elementelor individuale nu se vor schimba în funcție de a și b. Este această matrice pozitivă definită? Pentru a răspunde la această întrebare, să verificăm dacă minorele sale unghiulare sunt pozitive.

Calculați unghiular de ordinul întâi: 2 ∑ i = 1 n (x i) 2 > 0 . Deoarece punctele x i nu coincid, inegalitatea este strictă. Vom ține cont de acest lucru în calculele ulterioare.

Calculăm minorul unghiular de ordinul doi:

d e t (M) = 2 ∑ i = 1 n (x i) 2 2 ∑ i = 1 n x i 2 ∑ i = 1 n x i 2 n = 4 n ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2

După aceea, trecem la demonstrarea inegalității n ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 > 0 folosind inducția matematică.

1. Să verificăm dacă această inegalitate este valabilă pentru n arbitrar. Să luăm 2 și să calculăm:

2 ∑ i = 1 2 (x i) 2 - ∑ i = 1 2 x i 2 = 2 x 1 2 + x 2 2 - x 1 + x 2 2 = = x 1 2 - 2 x 1 x 2 + x 2 2 = x 1 + x 2 2 > 0

Am obținut egalitatea corectă (dacă valorile x 1 și x 2 nu se potrivesc).

1. Să presupunem că această inegalitate va fi adevărată pentru n , i.e. n ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 > 0 – adevărat.
2. Acum să demonstrăm validitatea pentru n + 1 , adică. că (n + 1) ∑ i = 1 n + 1 (x i) 2 - ∑ i = 1 n + 1 x i 2 > 0 dacă n ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 > 0 .

Calculam:

(n + 1) ∑ i = 1 n + 1 (x i) 2 - ∑ i = 1 n + 1 x i 2 = = (n + 1) ∑ i = 1 n (x i) 2 + x n + 1 2 - ∑ i = 1 n x i + x n + 1 2 = = n ∑ i = 1 n (x i) 2 + n x n + 1 2 + ∑ i = 1 n (x i) 2 + x n + 1 2 - - ∑ i = 1 n x i 2 + 2 x n + 1 ∑ i = 1 n x i + x n + 1 2 = = ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 + n x n + 1 2 - x n + 1 ∑ i = 1 n x i + ∑ i = 1 n (x i) 2 = = ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 + x n + 1 2 - 2 x n + 1 x 1 + x 1 2 + + x n + 1 2 - 2 x n + 1 x 2 + x 2 2 + . . . + x n + 1 2 - 2 x n + 1 x 1 + x n 2 = = n ∑ i = 1 n (x i) 2 - ∑ i = 1 n x i 2 + + (x n + 1 - x 1) 2 + (x n + 1) - x 2) 2 + . . . + (x n - 1 - x n) 2 > 0

Expresia cuprinsă între acolade va fi mai mare decât 0 (pe baza a ceea ce am presupus la pasul 2), iar restul termenilor va fi mai mare decât 0 deoarece toți sunt pătrate de numere. Am dovedit inegalitatea.

Răspuns: a și b găsite vor corespunde celei mai mici valori a funcției F (a, b) = ∑ i = 1 n (y i - (a x i + b)) 2, ceea ce înseamnă că sunt parametrii necesari ai metodei celor mai mici pătrate (LSM).

Dacă observați o greșeală în text, vă rugăm să o evidențiați și să apăsați Ctrl+Enter

Exemplu.

Date experimentale despre valorile variabilelor XȘi la sunt date în tabel.

Ca urmare a alinierii lor, funcția

Folosind metoda celor mai mici pătrate, aproximați aceste date cu o dependență liniară y=ax+b(găsiți opțiuni AȘi b). Aflați care dintre cele două linii este mai bună (în sensul metodei celor mai mici pătrate) aliniază datele experimentale. Faceți un desen.

Esența metodei celor mai mici pătrate (LSM).

Problema este de a găsi coeficienții de dependență liniară pentru care funcția a două variabile AȘi b ia cea mai mică valoare. Adică având în vedere datele AȘi b suma abaterilor pătrate ale datelor experimentale de la linia dreaptă găsită va fi cea mai mică. Acesta este punctul întreg al metodei celor mai mici pătrate.

Astfel, soluția exemplului se reduce la găsirea extremului unei funcții a două variabile.

Derivarea formulelor pentru găsirea coeficienților.

Se compilează și se rezolvă un sistem de două ecuații cu două necunoscute. Găsirea derivatelor parțiale ale funcțiilor prin variabile AȘi b, echivalăm aceste derivate cu zero.

Rezolvăm sistemul de ecuații rezultat prin orice metodă (de exemplu metoda de substitutie sau metoda lui Cramer) și obțineți formule pentru găsirea coeficienților folosind metoda celor mai mici pătrate (LSM).

Cu date AȘi b funcţie ia cea mai mică valoare. Dovada acestui fapt este dată sub textul de la sfârșitul paginii.

Aceasta este întreaga metodă a celor mai mici pătrate. Formula pentru găsirea parametrului A conține sumele ,, și parametrul n- cantitatea de date experimentale. Se recomandă ca valorile acestor sume să fie calculate separat. Coeficient b găsit după calcul A.

Este timpul să ne amintim de exemplul original.

Soluţie.

În exemplul nostru n=5. Completam tabelul pentru confortul calculării sumelor care sunt incluse în formulele coeficienților necesari.

Valorile din al patrulea rând al tabelului se obțin prin înmulțirea valorilor celui de-al 2-lea rând cu valorile celui de-al 3-lea rând pentru fiecare număr i.

Valorile din al cincilea rând al tabelului se obțin prin pătrarea valorilor din al doilea rând pentru fiecare număr i.

Valorile ultimei coloane a tabelului sunt sumele valorilor de pe rânduri.

Folosim formulele metodei celor mai mici pătrate pentru a găsi coeficienții AȘi b. Înlocuim în ele valorile corespunzătoare din ultima coloană a tabelului:

Prin urmare, y=0,165x+2,184 este linia dreaptă de aproximare dorită.

Rămâne să aflăm care dintre rânduri y=0,165x+2,184 sau aproximează mai bine datele originale, adică să facă o estimare folosind metoda celor mai mici pătrate.

Estimarea erorii metodei celor mai mici pătrate.

Pentru a face acest lucru, trebuie să calculați sumele abaterilor pătrate ale datelor originale din aceste linii Și , o valoare mai mică corespunde unei linii care aproximează mai bine datele originale în ceea ce privește metoda celor mai mici pătrate.

De la , apoi linia y=0,165x+2,184 aproximează mai bine datele originale.

Ilustrare grafică a metodei celor mai mici pătrate (LSM).

Totul arată grozav în topuri. Linia roșie este linia găsită y=0,165x+2,184, linia albastră este , punctele roz sunt datele originale.

În practică, la modelarea diferitelor procese - în special, economice, fizice, tehnice, sociale - una sau alta metodă de calculare a valorilor aproximative ale funcțiilor din valorile lor cunoscute în unele puncte fixe este utilizată pe scară largă.

Probleme de aproximare a funcțiilor de acest fel apar adesea:

la construirea formulelor aproximative pentru calcularea valorilor cantităților caracteristice procesului studiat în funcție de datele tabelare obținute în urma experimentului;

în integrarea numerică, diferențierea, rezolvarea ecuațiilor diferențiale etc.;

dacă este necesar să se calculeze valorile funcțiilor în punctele intermediare ale intervalului considerat;

la determinarea valorilor cantităților caracteristice ale procesului în afara intervalului luat în considerare, în special la prognoză.

Dacă, pentru a modela un anumit proces specificat de un tabel, se construiește o funcție care descrie aproximativ acest proces pe baza metodei celor mai mici pătrate, se va numi funcție de aproximare (regresie), iar sarcina de a construi funcții de aproximare va fi însăși fi o problemă de aproximare.

Acest articol discută posibilitățile pachetului MS Excel pentru rezolvarea unor astfel de probleme, în plus, sunt prezentate metode și tehnici de construire (creare) regresii pentru funcții date tabelar (care stă la baza analizei de regresie).

Există două opțiuni pentru a construi regresii în Excel.

Adăugarea regresiilor selectate (linii de tendință) la o diagramă construită pe baza unui tabel de date pentru caracteristica procesului studiat (disponibilă numai dacă este construită o diagramă);

Folosind funcțiile statistice încorporate ale foii de lucru Excel, care vă permite să obțineți regresii (linii de tendință) direct din tabelul de date sursă.

Adăugarea liniilor de tendință la o diagramă

Pentru un tabel de date care descrie un anumit proces și reprezentat printr-o diagramă, Excel are un instrument eficient de analiză a regresiei care vă permite să:

construiți pe baza metodei celor mai mici pătrate și adăugați la diagramă cinci tipuri de regresii care modelează procesul studiat cu diferite grade de acuratețe;

adăugați la diagramă o ecuație a regresiei construite;

determinați gradul de conformitate a regresiei selectate cu datele afișate pe diagramă.

Pe baza datelor din diagramă, Excel vă permite să obțineți tipuri de regresii liniare, polinomiale, logaritmice, exponențiale, exponențiale, care sunt date de ecuația:

y = y(x)

unde x este o variabilă independentă, care ia adesea valorile unei secvențe de numere naturale (1; 2; 3; ...) și produce, de exemplu, o numărătoare inversă a timpului procesului studiat (caracteristici) .

1 . Regresia liniară este bună la modelarea caracteristicilor care cresc sau descresc la o rată constantă. Acesta este cel mai simplu model al procesului studiat. Este construit după ecuația:

y=mx+b

unde m este tangenta pantei regresiei liniare la axa x; b - coordonata punctului de intersecție al regresiei liniare cu axa y.

2 . O linie de tendință polinomială este utilă pentru descrierea caracteristicilor care au mai multe extreme distincte (maxime și minime). Alegerea gradului polinomului este determinată de numărul de extreme ale caracteristicii studiate. Astfel, un polinom de gradul doi poate descrie bine un proces care are un singur maxim sau minim; polinom de gradul al treilea - nu mai mult de două extreme; polinom de gradul al patrulea - nu mai mult de trei extreme etc.

În acest caz, linia de tendință este construită în conformitate cu ecuația:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

unde coeficienții c0, c1, c2,... c6 sunt constante ale căror valori sunt determinate în timpul construcției.

3 . Linia de tendință logaritmică este utilizată cu succes în modelarea caracteristicilor, ale căror valori se modifică rapid la început, apoi se stabilizează treptat.

y = c ln(x) + b

4 . Linia de tendință a puterii dă rezultate bune dacă valorile dependenței studiate sunt caracterizate de o schimbare constantă a ratei de creștere. Un exemplu de astfel de dependență poate servi ca un grafic al mișcării accelerate uniform a mașinii. Dacă există valori zero sau negative în date, nu puteți utiliza o linie de tendință de putere.

Este construit în conformitate cu ecuația:

y = cxb

unde coeficienții b, c sunt constante.

5 . Ar trebui utilizată o linie de tendință exponențială dacă rata de modificare a datelor crește continuu. Pentru datele care conțin valori zero sau negative, acest tip de aproximare nu este, de asemenea, aplicabil.

Este construit în conformitate cu ecuația:

y=cebx

unde coeficienții b, c sunt constante.

La selectarea unei linii de tendință, Excel calculează automat valoarea lui R2, care caracterizează acuratețea aproximării: cu cât valoarea R2 este mai aproape de unul, cu atât linia de tendință aproximează mai fiabil procesul studiat. Dacă este necesar, valoarea lui R2 poate fi întotdeauna afișată pe diagramă.

Determinat prin formula:

Pentru a adăuga o linie de tendință la o serie de date:

activați diagrama construită pe baza seriei de date, adică faceți clic în zona diagramei. Elementul Chart va apărea în meniul principal;

după ce faceți clic pe acest articol, pe ecran va apărea un meniu, în care ar trebui să selectați comanda Adăugare linie de tendință.

Aceleași acțiuni sunt ușor de implementat dacă treceți cu mouse-ul peste graficul corespunzător uneia dintre seriile de date și faceți clic dreapta; în meniul contextual care apare, selectați comanda Adăugare linie de tendință. Caseta de dialog Trendline va apărea pe ecran cu fila Tip deschisă (Fig. 1).

După aceea ai nevoie de:

În fila Tip, selectați tipul de linie de tendință necesar (Liniar este selectat implicit). Pentru tipul Polinom, în câmpul Grad, specificați gradul polinomului selectat.

1 . Câmpul Construit pe serie listează toate seriile de date din diagrama în cauză. Pentru a adăuga o linie de tendință la o anumită serie de date, selectați numele acesteia în câmpul Construit pe serie.

Dacă este necesar, accesând fila Parametri (Fig. 2), puteți seta următorii parametri pentru linia de tendință:

schimbați numele liniei de tendință în câmpul Numele curbei de aproximare (netezite).

setați numărul de perioade (înainte sau înapoi) pentru prognoză în câmpul Prognoză;

afișați ecuația liniei de tendință în zona graficului, pentru care ar trebui să activați caseta de selectare pentru a afișa ecuația pe diagramă;

afișați valoarea fiabilității aproximării R2 în zona diagramei, pentru care ar trebui să activați caseta de selectare puneți valoarea fiabilității aproximării (R^2) pe diagramă;

setați punctul de intersecție al liniei de tendință cu axa Y, pentru care ar trebui să activați caseta de selectare Intersecția curbei cu axa Y într-un punct;

faceți clic pe butonul OK pentru a închide caseta de dialog.

Există trei moduri de a începe editarea unei linii de tendințe deja construite:

utilizați comanda Selected trend line din meniul Format, după ce ați selectat trend line;

selectați comanda Format Trendline din meniul contextual, care este apelată făcând clic dreapta pe linia de tendință;

făcând dublu clic pe linia de tendință.

Pe ecran va apărea caseta de dialog Format Trendline (Fig. 3), care conține trei file: View, Type, Parameters, iar conținutul ultimelor două coincide complet cu file similare din caseta de dialog Trendline (Fig. 1-2). ). În fila Vizualizare, puteți seta tipul de linie, culoarea și grosimea acesteia.

Pentru a șterge o linie de tendință deja construită, selectați linia de tendință de șters și apăsați tasta Ștergere.

Avantajele instrumentului de analiză de regresie considerată sunt:

ușurința relativă de a trasa o linie de tendință pe diagrame fără a crea un tabel de date pentru aceasta;

o listă destul de largă de tipuri de linii de tendință propuse, iar această listă include cele mai frecvent utilizate tipuri de regresie;

posibilitatea de a prezice comportamentul procesului studiat pentru un număr arbitrar (de bun simț) de pași înainte, precum și înapoi;

posibilitatea de a obține ecuația liniei de tendință într-o formă analitică;

posibilitatea, dacă este necesar, de a obține o evaluare a fiabilității aproximării.

Dezavantajele includ următoarele puncte:

construirea unei linii de tendință se realizează numai dacă există o diagramă construită pe o serie de date;

procesul de generare a serii de date pentru caracteristica studiată pe baza ecuațiilor liniei de tendință obținute pentru aceasta este oarecum aglomerat: ecuațiile de regresie dorite sunt actualizate cu fiecare modificare a valorilor seriei de date originale, dar numai în zona graficului , în timp ce seria de date formată pe baza vechii tendințe a ecuației de linie, rămâne neschimbată;

În rapoartele PivotChart, atunci când modificați vizualizarea diagramei sau raportul PivotTable asociat, liniile de tendință existente nu sunt păstrate, așa că trebuie să vă asigurați că aspectul raportului corespunde cerințelor dumneavoastră înainte de a desena linii de tendință sau de a formata în alt mod raportul PivotChart.

Liniile de tendință pot fi adăugate la seriile de date prezentate pe diagrame, cum ar fi un grafic, histogramă, diagrame cu zone plate nenormalizate, diagrame cu bare, împrăștiere, cu bule și bursiere.

Nu puteți adăuga linii de tendință la seriile de date din diagramele 3D, Standard, Radar, Pie și Donut.

Utilizarea funcțiilor Excel încorporate

Excel oferă, de asemenea, un instrument de analiză de regresie pentru trasarea liniilor de tendință în afara zonei diagramei. O serie de funcții statistice ale foii de lucru pot fi utilizate în acest scop, dar toate vă permit să construiți doar regresii liniare sau exponențiale.

Excel are mai multe funcții pentru construirea regresiei liniare, în special:

TENDINŢĂ;

• PANTĂ și TĂIERE.

Precum și câteva funcții pentru construirea unei linii de tendință exponențială, în special:

LGRFPaprox.

Trebuie remarcat faptul că tehnicile de construire a regresiilor folosind funcțiile TREND și GROWTH sunt practic aceleași. Același lucru se poate spune despre perechea de funcții LINEST și LGRFPRIBL. Pentru aceste patru funcții, atunci când se creează un tabel de valori, sunt utilizate caracteristici Excel, cum ar fi formulele matrice, ceea ce aglomerează oarecum procesul de construire a regresiilor. De asemenea, observăm că construcția unei regresii liniare, în opinia noastră, este cel mai ușor de implementat folosind funcțiile SLOPE și INTERCEPT, unde prima dintre ele determină panta regresiei liniare, iar a doua determină segmentul tăiat de regresie. pe axa y.

Avantajele instrumentului de funcții încorporate pentru analiza regresiei sunt:

un proces destul de simplu de același tip de formare a serii de date ale caracteristicii studiate pentru toate funcțiile statistice încorporate care stabilesc linii de tendință;

o tehnică standard pentru construirea liniilor de tendință bazate pe seriile de date generate;

capacitatea de a prezice comportamentul procesului studiat pentru numărul necesar de pași înainte sau înapoi.

Și dezavantajele includ faptul că Excel nu are funcții încorporate pentru crearea altor tipuri (cu excepția liniilor liniare și exponențiale) de linii de tendință. Această împrejurare nu permite adesea alegerea unui model suficient de precis al procesului studiat, precum și obținerea de previziuni apropiate de realitate. În plus, când se utilizează funcțiile TREND și GROW, ecuațiile liniilor de tendință nu sunt cunoscute.

Trebuie remarcat faptul că autorii nu și-au stabilit scopul articolului de a prezenta cursul analizei regresiei cu diferite grade de completitudine. Sarcina sa principală este de a arăta capacitățile pachetului Excel în rezolvarea problemelor de aproximare folosind exemple specifice; să demonstreze ce instrumente eficiente are Excel pentru a construi regresii și prognoză; ilustrează cât de ușor pot fi rezolvate astfel de probleme chiar și de către un utilizator care nu are cunoștințe profunde despre analiza regresiei.

Exemple de rezolvare a unor probleme specifice

Luați în considerare soluția unor probleme specifice utilizând instrumentele enumerate ale pachetului Excel.

Sarcina 1

Cu un tabel de date privind profitul unei întreprinderi de transport auto pe perioada 1995-2002. trebuie să faceți următoarele.

Construiți o diagramă.

Adăugați în diagramă linii de tendință liniare și polinomiale (pătratice și cubice).

Folosind ecuațiile liniei de tendință, obțineți date tabelare despre profitul întreprinderii pentru fiecare linie de tendință pentru 1995-2004.

Faceți o prognoză de profit pentru întreprindere pentru 2003 și 2004.

Rezolvarea problemei

În intervalul de celule A4:C11 din foaia de lucru Excel, introducem foaia de lucru prezentată în Fig. 4.

După ce am selectat intervalul de celule B4:C11, construim o diagramă.

Activăm graficul construit și, folosind metoda descrisă mai sus, după selectarea tipului de linie de tendință în caseta de dialog Linie de tendință (vezi Fig. 1), adăugăm alternativ linii de tendință liniare, pătratice și cubice graficului. În aceeași casetă de dialog, deschideți fila Parametri (vezi Fig. 2), în câmpul Numele curbei de aproximare (netezite), introduceți numele tendinței de adăugat, iar în câmpul Forecast forward for: periods, setați valoarea 2, deoarece se preconizează realizarea unei previziuni de profit pentru doi ani înainte. Pentru a afișa ecuația de regresie și valoarea fiabilității aproximării R2 în zona diagramei, activați casetele de selectare Afișați ecuația pe ecran și plasați valoarea fiabilității aproximării (R^2) pe diagramă. Pentru o mai bună percepție vizuală, schimbăm tipul, culoarea și grosimea liniilor de tendință trasate, pentru care folosim fila View din caseta de dialog Trend Line Format (vezi Fig. 3). Graficul rezultat cu linii de tendință adăugate este prezentat în fig. 5.

Pentru a obține date tabelare privind profitul întreprinderii pentru fiecare linie de tendință pentru anii 1995-2004. Să folosim ecuațiile liniilor de tendință prezentate în fig. 5. Pentru a face acest lucru, în celulele din intervalul D3:F3, introduceți informații textuale despre tipul liniei de tendință selectate: Tendință liniară, Tendință patratică, Tendință cubică. Apoi, introduceți formula de regresie liniară în celula D4 și, folosind marcatorul de umplere, copiați această formulă cu referințe relative la intervalul de celule D5:D13. Trebuie remarcat faptul că fiecare celulă cu o formulă de regresie liniară din intervalul de celule D4:D13 are ca argument o celulă corespunzătoare din intervalul A4:A13. În mod similar, pentru regresia pătratică, intervalul de celule E4:E13 este umplut, iar pentru regresia cubică, intervalul de celule F4:F13 este umplut. Astfel, s-a făcut o prognoză a profitului întreprinderii pentru anii 2003 și 2004. cu trei tendinţe. Tabelul de valori rezultat este prezentat în fig. 6.

Sarcina 2

Construiți o diagramă.

Adăugați în diagramă linii de tendință logaritmice, exponențiale și exponențiale.

Deduceți ecuațiile liniilor de tendință obținute, precum și valorile fiabilității aproximării R2 pentru fiecare dintre ele.

Folosind ecuațiile liniei de tendință, obțineți date tabelare despre profitul întreprinderii pentru fiecare linie de tendință pentru 1995-2002.

Faceți o prognoză a profitului pentru afaceri pentru 2003 și 2004 folosind aceste linii de tendință.

Rezolvarea problemei

Urmând metodologia dată în rezolvarea problemei 1, obținem o diagramă cu linii de tendință logaritmice, exponențiale și exponențiale adăugate (Fig. 7). În plus, folosind ecuațiile liniei de tendință obținute, completăm tabelul de valori pentru profitul întreprinderii, inclusiv valorile prezise pentru 2003 și 2004. (Fig. 8).

Pe fig. 5 și fig. se poate observa că modelul cu tendință logaritmică corespunde celei mai mici valori a fiabilității aproximării

R2 = 0,8659

Cele mai mari valori ale lui R2 corespund modelelor cu tendință polinomială: pătratică (R2 = 0,9263) și cubică (R2 = 0,933).

Sarcina 3

Cu un tabel de date privind profitul unei întreprinderi de transport auto pe perioada 1995-2002, prezentat în sarcina 1, trebuie să efectuați următorii pași.

Obțineți serii de date pentru linii de tendințe liniare și exponențiale folosind funcțiile TREND și GROW.

Folosind funcțiile TREND și GROWTH, faceți o prognoză a profitului pentru întreprindere pentru 2003 și 2004.

Pentru datele inițiale și seria de date primite, construiți o diagramă.

Rezolvarea problemei

Să folosim foaia de lucru a sarcinii 1 (vezi Fig. 4). Să începem cu funcția TREND:

selectați intervalul de celule D4:D11, care trebuie completat cu valorile funcției TREND corespunzătoare datelor cunoscute despre profitul întreprinderii;

apelați comanda Funcție din meniul Inserare. În caseta de dialog Function Wizard care apare, selectați funcția TREND din categoria Statistical, apoi faceți clic pe butonul OK. Aceeași operațiune poate fi efectuată prin apăsarea butonului (funcția de inserare) din bara de instrumente standard.

În caseta de dialog Function Arguments care apare, introduceți intervalul de celule C4:C11 în câmpul Known_values_y; în câmpul Known_values_x - intervalul de celule B4:B11;

pentru a face din formula introdusă o formulă matrice, utilizați combinația de taste + + .

Formula pe care am introdus-o în bara de formule va arăta astfel: =(TREND(C4:C11;B4:B11)).

Ca urmare, intervalul de celule D4:D11 este umplut cu valorile corespunzătoare ale funcției TREND (Fig. 9).

Pentru a face o prognoză a profitului companiei pentru 2003 și 2004. necesar:

selectați intervalul de celule D12:D13, unde vor fi introduse valorile prezise de funcția TREND.

apelați funcția TREND și în caseta de dialog Function Arguments care apare, introduceți în câmpul Known_values_y - intervalul de celule C4:C11; în câmpul Known_values_x - intervalul de celule B4:B11; iar în câmpul New_values_x - intervalul de celule B12:B13.

transformați această formulă într-o formulă matrice folosind comanda rapidă de la tastatură Ctrl + Shift + Enter.

Formula introdusă va arăta astfel: =(TREND(C4:C11;B4:B11;B12:B13)), iar intervalul de celule D12:D13 va fi completat cu valorile prezise ale funcției TREND (vezi Fig. 9).

În mod similar, o serie de date este completată folosind funcția GROWTH, care este utilizată în analiza dependențelor neliniare și funcționează exact la fel ca omologul său liniar TREND.

Figura 10 prezintă tabelul în modul de afișare a formulei.

Pentru datele inițiale și seriile de date obținute, diagrama prezentată în fig. unsprezece.

Sarcina 4

Cu un tabel de date privind primirea cererilor de servicii de către serviciul de dispecerat al unei întreprinderi de transport auto pentru perioada de la 1 la 11 zi a lunii în curs, trebuie efectuate următoarele acțiuni.

Obține serii de date pentru regresia liniară: folosind funcțiile SLOPE și INTERCEPT; folosind funcția LINEST.

Preluați o serie de date pentru regresie exponențială utilizând funcția LYFFPRIB.

Folosind funcțiile de mai sus, faceți o prognoză cu privire la primirea cererilor către serviciul de expediere pentru perioada 12-14 a lunii în curs.

Pentru seriile de date originale și primite, construiți o diagramă.

Rezolvarea problemei

Rețineți că, spre deosebire de funcțiile TREND și GROW, niciuna dintre funcțiile enumerate mai sus (SLOPE, INTERCEPTION, LINEST, LGRFPRIB) nu sunt regresii. Aceste funcții joacă doar un rol auxiliar, determinând parametrii de regresie necesari.

Pentru regresiile liniare și exponențiale construite folosind funcțiile SLOPE, INTERCEPT, LINEST, LGRFPRIB, aspectul ecuațiilor acestora este întotdeauna cunoscut, în contrast cu regresiile liniare și exponențiale corespunzătoare funcțiilor TREND și GROWTH.

1 . Să construim o regresie liniară care are ecuația:

y=mx+b

folosind funcțiile SLOPE și INTERCEPT, panta regresiei m fiind determinată de funcția SLOPE, iar termenul constant b - de funcția INTERCEPT.

Pentru a face acest lucru, efectuăm următoarele acțiuni:

introduceți tabelul sursă în intervalul de celule A4:B14;

valoarea parametrului m va fi determinată în celula C19. Selectați din categoria Statistică funcția Pantă; introduceți intervalul de celule B4:B14 în câmpul cunoscute_valori_y și intervalul de celule A4:A14 în câmpul cunoscute_valori_x. Formula va fi introdusă în celula C19: =SLOPE(B4:B14;A4:A14);

folosind o metodă similară, se determină valoarea parametrului b din celula D19. Și conținutul său va arăta astfel: = INTERCEPT(B4:B14;A4:A14). Astfel, valorile parametrilor m și b, necesari pentru construirea unei regresii liniare, vor fi stocate, respectiv, în celulele C19, D19;

apoi introducem formula de regresie liniară în celula C4 sub forma: = $ C * A4 + $ D. În această formulă, celulele C19 și D19 sunt scrise cu referințe absolute (adresa celulei nu ar trebui să se schimbe cu o posibilă copiere). Semnul de referință absolut $ poate fi tastat fie de la tastatură, fie folosind tasta F4, după plasarea cursorului pe adresa celulei. Folosind mânerul de umplere, copiați această formulă în intervalul de celule C4:C17. Obținem seria de date dorită (Fig. 12). Datorită faptului că numărul de solicitări este un întreg, ar trebui să setați formatul numărului în fila Număr a ferestrei Format de celule cu numărul de zecimale la 0.

2 . Acum să construim o regresie liniară dată de ecuația:

y=mx+b

folosind funcția LINEST.

Pentru aceasta:

introduceți funcția LINEST ca formulă matrice în intervalul de celule C20:D20: =(LINEST(B4:B14;A4:A14)). Ca rezultat, obținem valoarea parametrului m în celula C20 și valoarea parametrului b în celula D20;

introduceți formula în celula D4: =$C*A4+$D;

copiați această formulă folosind marcatorul de umplere în intervalul de celule D4:D17 și obțineți seria de date dorită.

3 . Construim o regresie exponențială care are ecuația:

cu ajutorul funcției LGRFPRIBL, se realizează în mod similar:

în intervalul de celule C21:D21, introduceți funcția LGRFPRIBL ca formulă matrice: =( LGRFPRIBL (B4:B14;A4:A14)). În acest caz, valoarea parametrului m va fi determinată în celula C21, iar valoarea parametrului b va fi determinată în celula D21;

se introduce formula în celula E4: =$D*$C^A4;

folosind marcatorul de umplere, această formulă este copiată în intervalul de celule E4:E17, unde va fi localizată seria de date pentru regresia exponențială (vezi Fig. 12).

Pe fig. 13 prezintă un tabel în care putem vedea funcțiile pe care le folosim cu intervalele de celule necesare, precum și formulele.

Valoare R 2 numit coeficient de determinare.

Sarcina de a construi o dependență de regresie este de a găsi vectorul coeficienților m ai modelului (1) la care coeficientul R ia valoarea maximă.

Pentru a evalua semnificația lui R, se folosește testul F Fisher, calculat prin formula

Unde n- dimensiunea eșantionului (număr de experimente);

k este numărul de coeficienți ai modelului.

Dacă F depășește o anumită valoare critică pentru date nȘi kși nivelul de încredere acceptat, atunci valoarea lui R este considerată semnificativă. Tabelele cu valorile critice ale lui F sunt date în cărțile de referință despre statistica matematică.

Astfel, semnificația lui R este determinată nu numai de valoarea sa, ci și de raportul dintre numărul de experimente și numărul de coeficienți (parametri) modelului. Într-adevăr, raportul de corelație pentru n=2 pentru un model liniar simplu este 1 (prin 2 puncte pe plan, puteți desena întotdeauna o singură linie dreaptă). Cu toate acestea, dacă datele experimentale sunt variabile aleatoare, o astfel de valoare a lui R ar trebui să fie de încredere cu mare grijă. De obicei, pentru a obține un R semnificativ și o regresie fiabilă, se urmărește să se asigure că numărul de experimente depășește semnificativ numărul de coeficienți ai modelului (n>k).

Pentru a construi un model de regresie liniară, trebuie să:

1) pregătiți o listă de n rânduri și m coloane care conțin datele experimentale (coloana care conține valoarea de ieșire Y trebuie să fie primul sau ultimul din listă); de exemplu, să luăm datele sarcinii anterioare, adăugând o coloană numită „numărul perioadei”, numerotând numerele de perioade de la 1 la 12. (acestea vor fi valorile X)

2) accesați meniul Date/Data Analysis/Regression

Dacă elementul „Analiza datelor” din meniul „Instrumente” lipsește, atunci ar trebui să accesați elementul „Suplimente” din același meniu și să bifați caseta „Pachet de analiză”.

3) în caseta de dialog „Regresie”, setați:

intervalul de intrare Y;

intervalul de intrare X;

interval de ieșire - celula din stânga sus a intervalului în care vor fi plasate rezultatele calculului (se recomandă plasarea acesteia pe o nouă foaie de lucru);

4) faceți clic pe „Ok” și analizați rezultatele.