LSM pentru o funcție a două variabile. Aproximarea datelor experimentale. Metoda celor mai mici pătrate. Implementarea practică a celor mai mici pătrate pentru dependența liniară de un calculator neprogramabil

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 în raport cu 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 ) ș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ă.

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.

De ce este nevoie de acest lucru, de ce toate aceste aproximări?

Eu personal îl folosesc pentru a rezolva probleme de netezire a datelor, probleme de interpolare și extrapolare (în exemplul original li se poate cere să găsească valoarea unei valori observate y la x=3 sau când x=6 folosind metoda celor mai mici pătrate). Dar vom vorbi mai multe despre asta mai târziu într-o altă secțiune a site-ului.

Dovada.

Așa că atunci când este găsit AȘi b funcția ia cea mai mică valoare, este necesar ca în acest moment matricea formei pătratice a diferenţialului de ordinul doi pentru funcţie a fost pozitiv definit. Să o arătăm.

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 cunoașteț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 una dintre cele mai comune și mai dezvoltate datorită ei simplitatea și eficiența metodelor de estimare a parametrilor liniar. În același timp, atunci când îl utilizați, trebuie să aveți grijă, deoarece modelele construite folosindu-l pot să nu satisfacă o serie de cerințe pentru calitatea parametrilor lor și, ca urmare, să nu reflecte „bine” modelele de dezvoltare a procesului. suficient.

Să luăm în considerare mai detaliat procedura de estimare a parametrilor unui model econometric liniar folosind metoda celor mai mici pătrate. Un astfel de model în general poate fi reprezentat prin ecuația (1.2):

y t = a 0 + a 1 x 1 t +...+ a n x nt + ε t.

Datele inițiale la estimarea parametrilor a 0 , a 1 ,..., a n sunt un vector de valori ale variabilei dependente y= (y 1 , y 2 , ... , y T)" și matricea valorilor variabilelor independente

în care prima coloană, formată din unele, corespunde coeficientului de model.

Metoda celor mai mici pătrate și-a primit numele pe baza principiului de bază conform căruia estimările parametrilor obținute pe baza ei trebuie să satisfacă: suma pătratelor erorii de model ar trebui să fie minimă.

Exemple de rezolvare a problemelor folosind metoda celor mai mici pătrate

Exemplul 2.1.Întreprinderea comercială are o rețea de 12 magazine, informații despre activitățile cărora sunt prezentate în tabel. 2.1.

Conducerea întreprinderii ar dori să știe cum depinde suma anuală de spațiul de vânzare cu amănuntul al magazinului.

Tabelul 2.1

Numărul magazinului	Cifra de afaceri anuală, milioane de ruble.	Suprafata comerciala, mii m2

Soluția celor mai mici pătrate. Să notăm cifra de afaceri anuală a celui de-al-lea magazin, milioane de ruble; — suprafața de vânzare cu amănuntul a celui de-al-lea magazin, mii m2.

Fig.2.1. Scatterplot pentru Exemplul 2.1

Pentru a determina forma relației funcționale dintre variabile și vom construi o diagramă de dispersie (Fig. 2.1).

Pe baza diagramei de dispersie, putem concluziona că cifra de afaceri anuală este dependentă pozitiv de spațiul comercial (adică, y va crește odată cu creșterea ). Cea mai potrivită formă de conexiune funcțională este liniar.

Informațiile pentru calcule suplimentare sunt prezentate în tabel. 2.2. Folosind metoda celor mai mici pătrate, estimăm parametrii unui model econometric liniar cu un singur factor

Tabelul 2.2

Prin urmare,

Prin urmare, cu o creștere a spațiului de vânzare cu amănuntul cu 1 mie m2, celelalte lucruri fiind egale, cifra de afaceri medie anuală crește cu 67,8871 milioane de ruble.

Exemplul 2.2. Conducerea companiei a observat că cifra de afaceri anuală depinde nu doar de aria de vânzare a magazinului (vezi exemplul 2.1), ci și de numărul mediu de vizitatori. Informațiile relevante sunt prezentate în tabel. 2.3.

Tabelul 2.3

Soluţie. Să notăm numărul mediu de vizitatori la cel de-al-lea magazin pe zi, mii de oameni.

Pentru a determina forma relației funcționale dintre variabile și vom construi o diagramă de dispersie (Fig. 2.2).

Pe baza graficului de dispersie, putem concluziona că cifra de afaceri anuală depinde pozitiv de numărul mediu de vizitatori pe zi (adică, y va crește odată cu creșterea ). Forma dependenței funcționale este liniară.

Orez. 2.2. Scatterplot pentru Exemplul 2.2

Tabelul 2.4

În general, este necesar să se determine parametrii unui model econometric cu doi factori

y t = a 0 + a 1 x 1 t + a 2 x 2 t + ε t

Informațiile necesare pentru calcule ulterioare sunt prezentate în tabel. 2.4.

Să estimăm parametrii unui model econometric liniar cu doi factori folosind metoda celor mai mici pătrate.

Prin urmare,

Estimarea coeficientului =61,6583 arată că, în condițiile egale, cu o creștere a spațiului comercial cu 1 mie m 2, cifra de afaceri anuală va crește cu o medie de 61,6583 milioane ruble.

Metoda celor mai mici pătrate

Metoda celor mai mici pătrate ( MCO, MCO, Cele mai mici pătrate obișnuite) - una dintre metodele de bază de analiză de regresie pentru estimarea parametrilor necunoscuți ai modelelor de regresie folosind date eșantion. Metoda se bazează pe minimizarea sumei pătratelor reziduurilor de regresie.

Trebuie remarcat faptul că metoda celor mai mici pătrate în sine poate fi numită o metodă de rezolvare a unei probleme în orice domeniu dacă soluția se află sau satisface un anumit criteriu de minimizare a sumei pătratelor unor funcții ale variabilelor cerute. Prin urmare, metoda celor mai mici pătrate poate fi folosită și pentru o reprezentare aproximativă (aproximare) a unei anumite funcții prin alte funcții (mai simple), atunci când se găsesc o mulțime de mărimi care satisfac ecuații sau constrângeri, al căror număr depășește numărul acestor mărimi. , etc.

Esența MNC

Să fie dat un model (parametric) al unei relații probabilistice (de regresie) între variabila (explicată). yși mulți factori (variabile explicative) X

unde este vectorul parametrilor necunoscuți ai modelului

- eroare aleatoare de model.

Să existe și eșantion de observații ale valorilor acestor variabile. Fie numărul de observație (). Apoi sunt valorile variabilelor din a-a observație. Apoi, pentru valorile date ale parametrilor b, este posibil să se calculeze valorile teoretice (modelului) ale variabilei explicate y:

Mărimea reziduurilor depinde de valorile parametrilor b.

Esența metodei celor mai mici pătrate (obișnuită, clasică) este de a găsi parametrii b pentru care suma pătratelor reziduurilor (ing. Suma reziduală a pătratelor) va fi minimă:

În cazul general, această problemă poate fi rezolvată prin metode de optimizare (minimizare) numerică. În acest caz ei vorbesc despre cele mai mici pătrate neliniare(NLS sau NLLS - engleză) Cele mai mici pătrate neliniare). În multe cazuri este posibilă obținerea unei soluții analitice. Pentru a rezolva problema de minimizare, este necesar să se găsească puncte staționare ale funcției prin diferențierea acesteia față de parametrii necunoscuți b, echivalând derivatele la zero și rezolvând sistemul de ecuații rezultat:

Dacă erorile aleatoare ale modelului sunt distribuite în mod normal, au aceeași varianță și sunt necorelate, estimările parametrilor MCO sunt aceleași cu estimările cu probabilitatea maximă (MLM).

MCO în cazul unui model liniar

Fie dependența de regresie liniară:

Lăsa y este un vector coloană de observații ale variabilei explicate și este o matrice de observații factoriale (rândurile matricei sunt vectorii valorilor factorilor dintr-o observație dată, coloanele sunt vectorul valorilor unui factor dat în toate observaţiile). Reprezentarea matricială a modelului liniar este:

Atunci vectorul estimărilor variabilei explicate și vectorul reziduurilor de regresie vor fi egali

În consecință, suma pătratelor reziduurilor de regresie va fi egală cu

Diferențiând această funcție în raport cu vectorul parametrilor și echivalând derivatele la zero, obținem un sistem de ecuații (sub formă de matrice):

Rezolvarea acestui sistem de ecuații oferă formula generală pentru estimările celor mai mici pătrate pentru un model liniar:

În scopuri analitice, cea din urmă reprezentare a acestei formule este utilă. Dacă într-un model de regresie datele centrat, atunci în această reprezentare prima matrice are semnificația unei matrice de covarianță eșantion de factori, iar a doua este un vector de covarianțe de factori cu variabila dependentă. Dacă în plus datele sunt de asemenea normalizat către MSE (adică, în cele din urmă standardizate), atunci prima matrice are semnificația unei matrice de corelație eșantion de factori, al doilea vector - un vector de corelații de eșantion de factori cu variabila dependentă.

O proprietate importantă a estimărilor MOL pentru modele cu constantă- linia regresiei construite trece prin centrul de greutate al datelor eșantionului, adică egalitatea este satisfăcută:

În special, în cazul extrem, când singurul regresor este o constantă, constatăm că estimarea MCO a singurului parametru (constanta însăși) este egală cu valoarea medie a variabilei explicate. Adică, media aritmetică, cunoscută pentru proprietățile sale bune din legile numerelor mari, este și o estimare a celor mai mici pătrate - satisface criteriul sumei minime a abaterilor pătrate de la aceasta.

Exemplu: cea mai simplă regresie (în perechi).

În cazul regresiei liniare perechi, formulele de calcul sunt simplificate (puteți face fără algebra matriceală):

Proprietățile estimatorilor MCO

În primul rând, observăm că pentru modelele liniare, estimările MCO sunt estimări liniare, după cum rezultă din formula de mai sus. Pentru estimările MCO nepărtinitoare, este necesar și suficient să se îndeplinească cea mai importantă condiție a analizei de regresie: așteptarea matematică a unei erori aleatoare, condiționată de factori, trebuie să fie egală cu zero. Această condiție, în special, este îndeplinită dacă

așteptarea matematică a erorilor aleatoare este zero și
factorii și erorile aleatoare sunt variabile aleatoare independente.

A doua condiție - condiția de exogeneitate a factorilor - este fundamentală. Dacă această proprietate nu este îndeplinită, atunci putem presupune că aproape orice estimări vor fi extrem de nesatisfăcătoare: nici măcar nu vor fi consecvente (adică chiar și o cantitate foarte mare de date nu ne permite să obținem estimări de înaltă calitate în acest caz ). În cazul clasic, se face o presupunere mai puternică despre determinismul factorilor, spre deosebire de o eroare aleatorie, ceea ce înseamnă automat că este îndeplinită condiția de exogeneitate. În cazul general, pentru consistența estimărilor, este suficient să se satisfacă condiția de exogeneitate împreună cu convergența matricei către o matrice nesingulară pe măsură ce dimensiunea eșantionului crește la infinit.

Pentru ca, pe lângă consecvență și imparțialitate, estimările celor mai mici pătrate (obișnuite) să fie și eficiente (cele mai bune din clasa estimărilor liniare imparțiale), trebuie îndeplinite proprietăți suplimentare ale erorii aleatoare:

Aceste ipoteze pot fi formulate pentru matricea de covarianță a vectorului de eroare aleatorie

Un model liniar care satisface aceste condiții se numește clasic. Estimările MCO pentru regresia liniară clasică sunt estimări imparțiale, consistente și cele mai eficiente din clasa tuturor estimărilor nepărtinitoare liniare (în literatura engleză abrevierea este uneori folosită ALBASTRU (Cel mai bun estimator liniar nebazat) - cea mai bună estimare liniară imparțială; în literatura rusă este mai des citată teorema Gauss-Markov). După cum este ușor de arătat, matricea de covarianță a vectorului estimărilor de coeficienți va fi egală cu:

MCO generalizată

Metoda celor mai mici pătrate permite o generalizare largă. În loc de a minimiza suma pătratelor reziduurilor, se poate minimiza o formă pătratică definită pozitivă a vectorului de reziduuri, unde este o matrice de greutate definită pozitivă simetrică. Cele mai mici pătrate convenționale sunt un caz special al acestei abordări, în care matricea de greutate este proporțională cu matricea de identitate. După cum se știe din teoria matricelor (sau operatorilor) simetrice, pentru astfel de matrici există o descompunere. În consecință, funcționalitatea specificată poate fi reprezentată după cum urmează, adică această funcțională poate fi reprezentată ca suma pătratelor unor „rămăși” transformate. Astfel, putem distinge o clasă de metode ale celor mai mici pătrate - metodele LS (Least Squares).

S-a dovedit (teorema lui Aitken) că pentru un model de regresie liniară generalizată (în care nu sunt impuse restricții asupra matricei de covarianță a erorilor aleatoare), cele mai eficiente (din clasa estimărilor liniare nepărtinitoare) sunt așa-numitele estimări. Cele mai mici pătrate generalizate (GLS - Generalized Least Squares)- Metoda LS cu o matrice de ponderi egală cu matricea de covarianță inversă a erorilor aleatoare: .

Se poate demonstra că formula pentru estimările GLS ale parametrilor unui model liniar are forma

Matricea de covarianță a acestor estimări va fi în consecință egală cu

De fapt, esența MOL constă într-o anumită transformare (liniară) (P) a datelor originale și aplicarea MCO obișnuită la datele transformate. Scopul acestei transformări este ca pentru datele transformate, erorile aleatoare să satisfacă deja ipotezele clasice.

MCO ponderate

În cazul unei matrice de ponderi diagonale (și, prin urmare, a unei matrice de covarianță a erorilor aleatoare), avem așa-numitele Least Squares (WLS) ponderate. În acest caz, suma ponderată a pătratelor reziduurilor modelului este minimizată, adică fiecare observație primește o „pondere” care este invers proporțională cu varianța erorii aleatoare din această observație: . De fapt, datele sunt transformate prin ponderarea observațiilor (împărțirea la o sumă proporțională cu abaterea standard estimată a erorilor aleatoare), iar datelor ponderate se aplică MCO obișnuite.

Câteva cazuri speciale de utilizare a MNC în practică

Aproximarea dependenței liniare

Să luăm în considerare cazul când, ca urmare a studierii dependenței unei anumite mărimi scalare de o anumită mărime scalară (Acesta ar putea fi, de exemplu, dependența tensiunii de puterea curentului: , unde este o valoare constantă, rezistența lui conductor), s-au efectuat măsurători ale acestor mărimi, în urma cărora valorile și valorile corespunzătoare acestora. Datele de măsurare trebuie înregistrate într-un tabel.

Masa. Rezultatele măsurătorilor.

Masura nr.
1
2
3
4
5
6

Întrebarea este: ce valoare a coeficientului poate fi selectată pentru a descrie cel mai bine dependența? Conform metodei celor mai mici pătrate, această valoare ar trebui să fie astfel încât suma abaterilor pătrate ale valorilor de la valori

a fost minim

Suma abaterilor pătrate are un extremum - un minim, ceea ce ne permite să folosim această formulă. Să găsim din această formulă valoarea coeficientului. Pentru a face acest lucru, îi transformăm partea stângă după cum urmează:

Ultima formulă ne permite să găsim valoarea coeficientului, care este ceea ce a fost cerut în problemă.

Poveste

Până la începutul secolului al XIX-lea. oamenii de știință nu aveau anumite reguli pentru rezolvarea unui sistem de ecuații în care numărul de necunoscute este mai mic decât numărul de ecuații; Până atunci se foloseau tehnici private care depindeau de tipul de ecuații și de inteligența calculatoarelor și, prin urmare, calculatoare diferite, bazate pe aceleași date de observație, ajungeau la concluzii diferite. Gauss (1795) a fost primul care a folosit metoda, iar Legendre (1805) a descoperit-o și a publicat-o independent sub numele său modern (franceză. Méthode des moindres quarrés ). Laplace a legat metoda de teoria probabilității, iar matematicianul american Adrain (1808) a luat în considerare aplicațiile sale teoretice ale probabilității. Metoda a fost răspândită și îmbunătățită prin cercetări ulterioare ale lui Encke, Bessel, Hansen și alții.

Utilizări alternative ale OLS

Ideea metodei celor mai mici pătrate poate fi folosită și în alte cazuri care nu au legătură directă cu analiza de regresie. Cert este că suma pătratelor este una dintre cele mai comune măsuri de proximitate pentru vectori (metrică euclidiană în spații cu dimensiuni finite).

O aplicație este „soluția” sistemelor de ecuații liniare în care numărul de ecuații este mai mare decât numărul de variabile

unde matricea nu este pătrată, ci dreptunghiulară de dimensiune.

Un astfel de sistem de ecuații, în cazul general, nu are soluție (dacă rangul este de fapt mai mare decât numărul de variabile). Prin urmare, acest sistem poate fi „rezolvat” doar în sensul alegerii unui astfel de vector pentru a minimiza „distanța” dintre vectori și . Pentru a face acest lucru, puteți aplica criteriul minimizării sumei pătratelor diferențelor dintre laturile stânga și dreapta ale ecuațiilor sistemului, adică. Este ușor de demonstrat că rezolvarea acestei probleme de minimizare duce la rezolvarea următorului sistem de ecuații

Exemplu.

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

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

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

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.

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);