Metoda celor mai mici pătrate pentru determinarea coeficienților. Aproximarea datelor experimentale

Masa 4

x n

y n

Masa 5

y eu

0,705

0,495

0,426

0,357

0,368

0,406

0,549

0,768

Tabelul 6

№0

X

0,1

0,2

0,3

0,4

0,5

0,6

0,7

0,8

y

3,030

3,142

3,358

3,463

3,772

3,251

3,170

3,665

№ 1

3,314

3,278

3,262

3,292

3,332

3,397

3,487

3,563

№ 2

1,045

1,162

1,264

1,172

1,070

0,898

0,656

0,344

№ 3

6,715

6,735

6,750

6,741

6,645

6,639

6,647

6,612

№ 4

2,325

2,515

2,638

2,700

2,696

2,626

2,491

2,291

№ 5

1.752

1,762

1,777

1,797

1,821

1,850

1,884

1,944

№ 6

1,924

1,710

1,525

1,370

1,264

1,190

1,148

1,127

№ 7

1,025

1,144

1,336

1,419

1,479

1,530

1,568

1,248

№ 8

5,785

5,685

5,605

5,545

5,505

5,480

5,495

5,510

№ 9

4,052

4,092

4,152

4,234

4,338

4,468

4,599

Exemplu.

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

Ca urmare a alinierii lor, se obține funcția

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

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

Sarcina este de a găsi coeficienții de dependență liniară la care funcția a două variabile AȘi b ia cea mai mică valoare. Adică dat AȘi b suma abaterilor pătrate a 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, rezolvarea 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 unei funcții prin variabile AȘi b, echivalăm aceste derivate cu zero.

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

Dat AȘi b funcţie ia cea mai mică valoare. Dovada acestui fapt este dată mai jos în 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. Vă recomandăm să calculați separat valorile acestor sume. 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 din ultima coloană 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 valorile corespunzătoare din ultima coloană a tabelului în ele:

Prin urmare, y = 0,165x+2,184- 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ă face o estimare folosind metoda celor mai mici pătrate.

Estimarea erorilor metodei celor mai mici pătrate.

Pentru a face acest lucru, trebuie să calculați suma abaterilor pătrate ale datelor originale din aceste linii Și , o valoare mai mică corespunde unei linii care aproximează mai bine datele originale în sensul metodei celor mai mici pătrate.

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

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

Totul este clar vizibil pe grafice. Linia roșie este linia dreaptă 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 anumite puncte fixe este utilizată pe scară largă.

Acest tip de problemă de aproximare a funcției apare adesea:

la construirea de formule aproximative pentru calcularea valorilor cantităților caracteristice ale procesului studiat folosind date tabelare obținute în urma experimentului;

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

dacă este necesar, calculați valorile funcțiilor în punctele intermediare ale intervalului considerat;

la determinarea valorilor cantităților caracteristice unui proces în afara intervalului considerat, în special la prognoză.

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

Acest articol discută capacitățile pachetului MS Excel pentru rezolvarea acestui tip de probleme, în plus, oferă metode și tehnici pentru construirea (crearea) regresiilor pentru funcțiile tabulate (care stă la baza analizei regresiei).

Excel are două opțiuni pentru a construi regresii.

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

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

Adăugarea liniilor de tendință la un grafic

Pentru un tabel de date care descrie un proces și este 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 cinci tipuri de regresii la diagramă, care modelează procesul studiat cu diferite grade de precizie;

adăugați la diagramă ecuația de regresie construită;

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

Pe baza datelor grafice, Excel vă permite să obțineți tipuri de regresii liniare, polinomiale, logaritmice, de putere, exponențiale, care sunt specificate 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ă pentru modelarea caracteristicilor ale căror valori cresc sau scad la o rată constantă. Acesta este cel mai simplu model de construit pentru procesul studiat. Este construit în conformitate cu ecuația:

y = mx + b

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

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 doar un maxim sau un 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 la modelarea caracteristicilor ale căror valori se modifică inițial rapid și apoi se stabilizează treptat.

y = c ln(x) + b

4 . O linie de tendință a legii puterii dă rezultate bune dacă valorile relației studiate sunt caracterizate de o schimbare constantă a ratei de creștere. Un exemplu de astfel de dependență este graficul mișcării uniform accelerate a unei mașini. Dacă există valori zero sau negative în date, nu puteți utiliza o linie de tendință de putere.

Construit în conformitate cu ecuația:

y = c xb

unde coeficienții b, c sunt constante.

5 . O linie de tendință exponențială ar trebui utilizată atunci când 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.

Construit în conformitate cu ecuația:

y = c ebx

unde coeficienții b, c sunt constante.

La selectarea unei linii de tendință, Excel calculează automat valoarea lui R2, care caracterizează fiabilitatea aproximării: cu cât valoarea R2 este mai aproape de unitate, cu atât linia de tendință aproximează mai fiabil procesul studiat. Dacă este necesar, valoarea 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 o diagramă bazată pe o serie de date, adică faceți clic în zona diagramei. Elementul Diagramă 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 pot fi implementate cu ușurință prin deplasarea cursorului mouse-ului peste graficul corespunzător uneia dintre seriile de date și făcând 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ă aceasta aveți nevoie de:

Selectați tipul de linie de tendință necesar în fila Tip (tipul Linear 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 diagramei, pentru care ar trebui să activați caseta de selectare afișare ecuație pe diagramă;

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

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

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

Pentru a începe editarea unei linii de tendințe deja desenate, există trei moduri:

utilizați comanda Selected trend line din meniul Format, având selectat în prealabil linia de tendință;

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

faceți dublu clic pe linia de tendință.

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

Pentru a șterge o linie de tendință care a fost deja desenată, 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 construi 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;

capacitatea de a prezice comportamentul procesului studiat printr-un număr arbitrar (în limitele bunului simț) de pași înainte și, de asemenea, înapoi;

capacitatea de a obține ecuația liniei de tendință în formă analitică;

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

Dezavantajele includ următoarele:

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 necesare 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 liniilor rămâne neschimbată;

În rapoartele PivotChart, schimbarea vizualizării unei diagrame sau a unui raport PivotTable asociat nu păstrează liniile de tendințe existente, ceea ce înseamnă că înainte de a desena linii de tendințe sau de a formata în alt mod un raport PivotChart, trebuie să vă asigurați că aspectul raportului îndeplinește cerințele necesare.

Liniile de tendință pot fi utilizate pentru a completa seriile de date prezentate pe diagrame, cum ar fi grafice, histograme, diagrame cu zone plate nestandardizate, diagrame cu bare, diagrame cu dispersie, diagrame cu bule și diagrame bursiere.

Nu puteți adăuga linii de tendință la seriile de date în diagrame 3D, normalizate, radar, plăcinte și gogoși.

Folosind funcțiile încorporate ale Excel

Excel are, de asemenea, un instrument de analiză de regresie pentru trasarea liniilor de tendință în afara zonei diagramei. Există o serie de funcții ale foii de lucru statistice pe care le puteți utiliza în acest scop, dar toate vă permit doar să construiți 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:

LGRFPRIBL.

Trebuie remarcat faptul că tehnicile de construire a regresiilor folosind funcțiile TREND și GROWTH sunt aproape aceleași. Același lucru se poate spune despre perechea de funcții LINEST și LGRFPRIBL. Pentru aceste patru funcții, crearea unui tabel de valori folosește caracteristici Excel, cum ar fi formulele matrice, care aglomerează oarecum procesul de construire a regresiilor. Să remarcăm, de asemenea, că construcția regresiei liniare, în opinia noastră, se realizează cel mai ușor folosind funcțiile SLOPE și INTERCEPT, unde prima dintre ele determină panta regresiei liniare, iar a doua determină segmentul interceptat de regresia pe axa y.

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

un proces destul de simplu, uniform de generare a serii de date ale caracteristicii studiate pentru toate funcțiile statistice încorporate care definesc liniile de tendință;

metodologie standard pentru construirea liniilor de tendință bazate pe serii de date generate;

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

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, atunci când se utilizează funcțiile TREND și GROWTH, ecuațiile liniilor de tendință nu sunt cunoscute.

Trebuie remarcat faptul că autorii nu și-au propus să prezinte cursul analizei de regresie cu niciun grad de completitudine. Sarcina sa principală este de a arăta, folosind exemple specifice, capacitățile pachetului Excel la rezolvarea problemelor de aproximare; să demonstreze ce instrumente eficiente are Excel pentru a construi regresii și prognoză; ilustrează modul în care astfel de probleme pot fi rezolvate relativ ușor chiar și de către un utilizator care nu are cunoștințe extinse de analiză de regresie.

Exemple de rezolvare a unor probleme specifice

Să ne uităm la rezolvarea unor probleme specifice utilizând instrumentele Excel enumerate.

Problema 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 profiturile întreprinderii pentru fiecare linie de tendință pentru 1995-2004.

Faceți o prognoză pentru profitul întreprinderii pentru 2003 și 2004.

Rezolvarea problemei

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

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

Activăm diagrama construită și, conform metodei descrise mai sus, după selectarea tipului de linie de tendință în caseta de dialog Linie de tendință (vezi Fig. 1), adăugăm alternativ în diagramă linii de tendință liniare, pătratice și cubice. Î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 care se adaugă, 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 de fiabilitate a aproximării R2 în zona diagramei, activați casetele de selectare afișare ecuație pe ecran și plasați valoarea de fiabilitate a aproximării (R^2) pe diagramă. Pentru o mai bună percepție vizuală, schimbăm tipul, culoarea și grosimea liniilor de tendință construite, pentru care folosim fila View din caseta de dialog Trend Line Format (vezi Fig. 3). Diagrama rezultată cu linii de tendință adăugate este prezentată în Fig. 5.

Pentru a obține date tabelare privind profiturile întreprinderii pentru fiecare linie de tendință pentru 1995-2004. Să folosim ecuațiile liniei de tendință prezentate în Fig. 5. Pentru a face acest lucru, în celulele din intervalul D3:F3, introduceți informații text 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ă, completați intervalul de celule E4:E13, iar pentru regresia cubică, completați intervalul de celule F4:F13. Astfel, a fost realizată o prognoză a profitului întreprinderii pentru 2003 și 2004. folosind trei tendințe. Tabelul de valori rezultat este prezentat în Fig. 6.

Problema 2

Construiți o diagramă.

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

Deduceți ecuațiile liniilor de tendință obținute, precum și valorile de fiabilitate ale 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 companiei 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, de putere și exponențiale adăugate acesteia (Fig. 7). În continuare, folosind ecuațiile liniei de tendință obținute, completăm un tabel de valori pentru profitul întreprinderii, inclusiv valorile prezise pentru 2003 și 2004. (Fig. 8).

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

Problema 3

Cu tabelul de date privind profitul unei întreprinderi de transport cu motor pentru 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ță liniare și exponențiale folosind funcțiile TREND și GROW.

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

Construiți o diagramă pentru datele originale și seria de date rezultate.

Rezolvarea problemei

Să folosim foaia de lucru pentru problema 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ă făcând clic pe butonul (Insert Function) 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 formula introdusă să devină 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 întreprinderii pentru 2003 și 2004. necesar:

selectați intervalul de celule D12:D13 în care 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 această formulă într-o formulă matrice folosind combinația de taste 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).

Seria de date este completată în mod similar utilizând funcția GROWTH, care este utilizată în analiza dependențelor neliniare și funcționează exact în același mod ca omologul său liniar TREND.

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

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

Problema 4

Cu tabelul de date privind primirea cererilor de servicii de către serviciul de expediere al unei întreprinderi de transport auto pentru perioada de la 1 la 11 a lunii în curs, trebuie să efectuați următoarele acțiuni.

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

Obțineți o serie de date pentru regresia exponențială folosind funcția LGRFPRIBL.

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

Creați o diagramă pentru seriile de date originale și primite.

Rezolvarea problemei

Rețineți că, spre deosebire de funcțiile TREND și GROWTH, niciuna dintre funcțiile enumerate mai sus (SLOPE, INTERCEPT, LINEST, LGRFPRIB) nu este regresie. Aceste funcții joacă doar un rol de susținere, 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, spre deosebire de regresiile liniare și exponențiale corespunzătoare funcțiilor TREND și GROWTH.

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

y = mx+b

folosind funcțiile SLOPE și INTERCEPT, cu panta de regresie m determinată de funcția SLOPE, iar termenul liber b de către funcția INTERCEPT.

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

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

valoarea parametrului m va fi determinată în celula C19. Selectați funcția Pantă din categoria Statistică; 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 tehnică similară, se determină valoarea parametrului b din celula D19. Și conținutul său va arăta astfel: =SEGMENT(B4:B14,A4:A14). Astfel, valorile parametrilor m și b necesari pentru construirea unei regresii liniare vor fi stocate în celulele C19, respectiv D19;

Apoi, introduceți 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 în timpul unei posibile copii). 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 necesară (Fig. 12). Datorită faptului că numărul de solicitări este un întreg, ar trebui să setați formatul numărului cu numărul de zecimale la 0 în fila Număr a ferestrei Format de celule.

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ă cu ecuația:

folosind funcția LGRFPRIBL se realizează în mod similar:

În intervalul de celule C21:D21 introducem funcția LGRFPRIBL ca o 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).

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

Magnitudinea 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 Fisher F, calculat folosind 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 probabilitatea 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 egal cu 1 (o singură linie dreaptă poate fi întotdeauna trasată prin 2 puncte pe un plan). Cu toate acestea, dacă datele experimentale sunt variabile aleatoare, o astfel de valoare a lui R ar trebui să fie de încredere cu mare precauție. De obicei, pentru a obține un R semnificativ și o regresie fiabilă, ei se străduiesc 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ă aveți nevoie de:

1) pregătiți o listă de n rânduri și m coloane care conțin date experimentale (coloana care conține valoarea de ieșire Y trebuie să fie primul sau ultimul din listă); De exemplu, să luăm datele din sarcina anterioară, adăugând o coloană numită „Nr. perioadă”, numerotați numerele perioadei 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 de selectare „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 lor pe o nouă foaie de lucru);

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

Esența metodei este că criteriul pentru calitatea soluției luate în considerare este suma erorilor pătrate, pe care se străduiesc să le minimizeze. Pentru a aplica acest lucru, este necesar să se efectueze cât mai multe măsurători ale variabilei aleatoare necunoscute (cu cât este mai mare, cu atât este mai mare acuratețea soluției) și un anumit set de soluții estimate din care trebuie selectată cea mai bună. Dacă setul de soluții este parametrizat, atunci trebuie să găsim valoarea optimă a parametrilor.

De ce sunt minimizate erorile pătrate și nu erorile în sine? Faptul este că, în majoritatea cazurilor, erorile merg în ambele sensuri: estimarea poate fi mai mare decât măsurarea sau mai mică decât aceasta. Dacă adunăm erori cu semne diferite, acestea se vor anula reciproc și, ca urmare, suma ne va oferi o idee incorectă asupra calității evaluării. Adesea, pentru ca estimarea finală să aibă aceeași dimensiune ca și valorile măsurate, se ia rădăcina pătrată a sumei erorilor pătrate.

LSM este utilizat în matematică, în special în teoria probabilităților și statistica matematică. Această metodă este cea mai utilizată în problemele de filtrare, atunci când este necesară separarea semnalului util de zgomotul suprapus pe acesta.

De asemenea, este folosit în analiza matematică pentru a aproxima reprezentarea unei funcții date prin funcții mai simple. Un alt domeniu de aplicare a celor mai mici pătrate este soluția sistemelor de ecuații cu un număr de necunoscute mai mic decât numărul de ecuații.

Am venit cu mai multe domenii de aplicare foarte neașteptate ale MNC-urilor, despre care aș dori să vorbesc în acest articol.

OLS și greșeli de scriere

Flagelul traducătorilor automati și al motoarelor de căutare sunt greșelile de scriere și ortografiile. Într-adevăr, dacă un cuvânt diferă doar cu 1 literă, programul îl tratează ca pe un alt cuvânt și îl traduce/căută incorect sau nu îl traduce/nu îl găsește deloc.

Am avut o problemă similară: aveam două baze de date cu adrese ale caselor din Moscova și trebuia să le combin într-una singură. Dar adresele erau scrise în stiluri diferite. O bază de date conținea standardul KLADR (All-Russian Address Clasifier), de exemplu: „BABUSHKINA LETCHIKA STREET, D10K3”. Și într-o altă bază de date era un stil poștal, de exemplu: „Sf. Pilot Babushkina, clădirea 10, clădirea 3.” Se pare că nu există erori în ambele cazuri, dar automatizarea procesului este incredibil de dificilă (fiecare bază de date are 40 de mii de înregistrări!). Deși au fost și multe greșeli de scriere... Cum să faci computerul să înțeleagă că cele 2 adrese de mai sus aparțin aceleiași case? Aici mi-a fost de folos MNC.

Ce am facut? După ce am găsit următoarea scrisoare la prima adresă, am căutat aceeași scrisoare la a doua adresă. Dacă erau amândoi în același loc, atunci am setat eroarea pentru acea literă să fie 0. Dacă erau în poziții adiacente, atunci eroarea a fost 1. Dacă a existat o deplasare cu 2 poziții, eroarea a fost 2 etc. Dacă nu a existat deloc o astfel de literă la o altă adresă, atunci eroarea s-a presupus a fi egală cu n+1, unde n este numărul de litere din prima adresă. Astfel, am calculat suma erorilor pătrate și am combinat acele înregistrări în care această sumă era minimă.

Desigur, numerele casei și ale clădirii au fost procesate separat. Nu știu dacă am inventat o altă „bicicletă”, sau dacă chiar a fost, dar problema a fost rezolvată rapid și eficient. Mă întreb dacă această metodă este folosită în motoarele de căutare? Poate că se aplică deoarece fiecare motor de căutare care se respectă, atunci când întâlnește un cuvânt necunoscut, oferă un înlocuitor de cuvinte familiare („poate ai vrut să spui...”). Cu toate acestea, ei pot face această analiză într-un alt mod.

OLS și căutare după imagini, chipuri și hărți

Această metodă poate fi folosită și pentru a căuta folosind imagini, desene, hărți și chiar fețe ale oamenilor.

Fotografie:

Acum, toate motoarele de căutare, în loc să caute după imagini, folosesc în esență căutarea după subtitrări la imagini. Acesta este, fără îndoială, un serviciu util și convenabil, dar îmi propun să îl completez cu o căutare reală de imagini.

Se introduce o imagine eșantion și se compila o evaluare pentru toate imaginile pe baza sumei abaterilor pătrate ale punctelor caracteristice. Determinarea acestor puncte cele mai caracteristice este în sine o sarcină netrivială. Cu toate acestea, este complet rezolvabil: de exemplu, pentru fețe acestea sunt colțurile ochilor, buzele, vârful nasului, nările, marginile și centrele sprâncenelor, pupilele etc.

Comparând acești parametri, puteți găsi fața care seamănă cel mai mult cu eșantionul. Am văzut deja site-uri unde funcționează acest serviciu și poți găsi celebritatea care seamănă cel mai mult cu fotografia pe care o sugerezi și chiar poți crea o animație care te transformă într-o celebritate și înapoi. Cu siguranță aceeași metodă funcționează în bazele de date ale Ministerului Afacerilor Interne care conțin imagini identikit ale infractorilor.

Foto: pixabay.com

Da, și puteți căuta folosind amprentele digitale folosind aceeași metodă. Căutarea pe hărți este axată pe neregulile naturale ale obiectelor geografice - coturi ale râurilor, lanțuri muntoase, contururi de maluri, păduri și câmpuri.

Aceasta este o metodă atât de minunată și universală a celor mai mici pătrate. Sunt sigur că voi, dragi cititori, veți putea găsi singuri multe domenii neobișnuite și neașteptate de aplicare a acestei metode.

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 utiliza un polinom de 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

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 potrivește cel mai bine unui set de perechi ordonate prin găsirea valorilor pentru a și b, coeficienții din ecuația dreptei. Scopul celor mai mici pătrate este de a minimiza eroarea pătrată totală dintre valorile lui y și ŷ. Dacă pentru fiecare punct determinăm eroarea ŷ, metoda celor mai mici pătrate minimizează:

unde n = numărul de perechi ordonate în jurul liniei. cât mai aproape de date.

Acest concept este ilustrat în figură

Pe baza figurii, linia care se potrivește cel mai bine datelor, linia de regresie, minimizează eroarea totală pătrată a celor patru puncte de pe grafic. Vă voi arăta cum să determinați acest lucru folosind cele mai mici pătrate cu următorul exemplu.

Imaginați-vă un cuplu tânăr care s-a mutat recent împreună și împart o masă de toaletă în baie. Tânărul a început să observe că jumătate din masa lui se micșora inexorabil, pierzând teren în fața spumei de păr și a complexelor de soia. În ultimele luni, tipul a monitorizat îndeaproape rata cu care crește numărul de obiecte de pe partea ei a mesei. Tabelul de mai jos arată numărul de articole pe care fata le-a acumulat pe toaleta de baie în ultimele luni.

Deoarece scopul nostru este să aflăm dacă numărul de articole crește în timp, „Luna” va fi variabila independentă, iar „Numărul de articole” va fi variabila dependentă.

Folosind metoda celor mai mici pătrate, determinăm ecuația care se potrivește cel mai bine datelor, calculând valorile lui a, intersecția cu y, și b, panta dreptei:

a = y avg - bx avg

unde x avg este valoarea medie a lui x, variabila independentă, y avg este valoarea medie a lui y, variabila independentă.

Tabelul de mai jos rezumă calculele necesare pentru aceste ecuații.

Curba efectului pentru exemplul nostru de cada de baie ar fi dată de următoarea ecuație:

Deoarece ecuația noastră are o pantă pozitivă de 0,976, tipul are dovezi că numărul de articole de pe tabel crește în timp cu o rată medie de 1 articol pe lună. Graficul arată curba efectului cu perechi ordonate.

Așteptările pentru numărul de articole în următoarele șase luni (luna 16) va fi calculată după cum urmează:

ŷ = 5,13 + 0,976x = 5,13 + 0,976(16) ~ 20,7 = 21 elemente

Așadar, este timpul ca eroul nostru să ia ceva măsuri.

Funcția TREND în Excel

După cum probabil ați ghicit deja, Excel are o funcție pentru calcularea valorilor prin metoda celor mai mici pătrate. Această funcție se numește TREND. Sintaxa sa este următoarea:

TENDINȚĂ (valori Y cunoscute; valori X cunoscute; valori X noi; constantă)

valori Y cunoscute – o matrice de variabile dependente, în cazul nostru, numărul de obiecte de pe tabel

valori cunoscute X – o serie de variabile independente, în cazul nostru aceasta este luna

noi valori X – noi valori X (luni) pentru care Funcția TREND returnează valoarea așteptată a variabilelor dependente (numărul de elemente)

const - optional. O valoare booleană care specifică dacă constanta b trebuie să fie 0.

De exemplu, figura arată funcția TENDINȚĂ folosită pentru a determina numărul așteptat de articole pe o chiodă de baie pentru a 16-a lună.