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

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

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

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

Metode de clasificare a celor mai mici pătrate

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

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

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

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

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

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

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

Metoda celor mai mici pătrate (LSM)

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

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

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

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

Metoda algebrică a celor mai mici pătrate

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

prin minimizarea normei euclidiene

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

Analiza datelor experimentale

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

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

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

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

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

aproximarea exel algebric gauss

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

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

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

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

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

atunci egalitatea scrisă va lua forma

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

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

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

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

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

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

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

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

2A T B + 2A T Ax

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

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

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

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

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

MNC în Excel

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

A T AX = A T B

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

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

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

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

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

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

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

Rezultatele calculului:

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

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

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

Sintaxă: MOBR (matrice).

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

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

Sintaxă: MULTIPLE(matrice1,matrice2).

Array1, array2 sunt matrice multiplicabile.

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

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

Care găsește cea mai largă aplicație în diverse domenii ale științei și activității practice. Aceasta ar putea fi fizică, chimie, biologie, economie, sociologie, psihologie și așa mai departe și așa mai departe. Prin voința sorții, de multe ori trebuie să mă ocup de economie și, prin urmare, astăzi vă voi aranja o excursie într-o țară uimitoare numită Econometrie=) ...Cum sa nu-l vrei?! E foarte bine acolo – trebuie doar să te hotărăști! ...Dar ceea ce probabil că vrei cu siguranță este să înveți cum să rezolvi problemele metoda celor mai mici pătrate. Și mai ales cititorii harnici vor învăța să le rezolve nu numai cu acuratețe, ci și FOARTE RAPID ;-) Dar mai întâi expunerea generală a problemei+ exemplu însoțitor:

Să presupunem că într-un anumit domeniu sunt studiați indicatori care au o expresie cantitativă. În același timp, există toate motivele să credem că indicatorul depinde de indicator. Această ipoteză poate fi fie o ipoteză științifică, fie bazată pe bunul simț de bază. Să lăsăm totuși știința deoparte și să explorăm zone mai apetisante - și anume, magazinele alimentare. Să notăm prin:

– suprafata comerciala a unui magazin alimentar, mp,
– cifra de afaceri anuală a unui magazin alimentar, milioane de ruble.

Este absolut clar că, cu cât suprafața magazinului este mai mare, cu atât va fi mai mare în majoritatea cazurilor cifra de afaceri a acestuia.

Sa presupunem ca dupa efectuarea observatiilor/experimentelor/calculelor/dansurilor cu tamburina avem la dispozitie date numerice:

Cu magazinele alimentare, cred că totul este clar: - aceasta este zona primului magazin, - cifra de afaceri anuală a acestuia, - zona celui de-al doilea magazin, - cifra de afaceri anuală etc. Apropo, nu este deloc necesar să aveți acces la materiale clasificate - o evaluare destul de precisă a cifrei de afaceri comerciale poate fi obținută prin intermediul statistici matematice. Totuși, să nu ne distragem, cursul de spionaj comercial este deja plătit =)

Datele tabelare pot fi, de asemenea, scrise sub formă de puncte și descrise în forma familiară Sistemul cartezian .

Să răspundem la o întrebare importantă: Câte puncte sunt necesare pentru un studiu calitativ?

Cu cât mai mare cu atât mai bine. Setul minim acceptabil este format din 5-6 puncte. În plus, atunci când cantitatea de date este mică, rezultatele „anomale” nu pot fi incluse în eșantion. Deci, de exemplu, un mic magazin de elită poate câștiga ordine de mărime mai mult decât „colegii săi”, distorsionând astfel modelul general pe care trebuie să-l găsiți!

Pentru a spune foarte simplu, trebuie să selectăm o funcție, programa care trece cât mai aproape de puncte . Această funcție este numită aproximând (aproximare - aproximare) sau functie teoretica . În general, aici apare imediat un „concurent” evident - un polinom de grad înalt, al cărui grafic trece prin TOATE punctele. Dar această opțiune este complicată și adesea pur și simplu incorectă. (deoarece graficul va „în bucla” tot timpul și reflectă slab tendința principală).

Astfel, funcția căutată ar trebui să fie destul de simplă și, în același timp, să reflecte adecvat dependența. După cum ați putea ghici, una dintre metodele pentru găsirea unor astfel de funcții este numită metoda celor mai mici pătrate. În primul rând, să ne uităm la esența sa în vedere generala. Lasă o anumită funcție să aproximeze datele experimentale:


Cum se evaluează acuratețea acestei aproximări? Să calculăm și diferențele (abaterile) dintre valorile experimentale și cele funcționale (studiam desenul). Primul gând care îmi vine în minte este de a estima cât de mare este suma, dar problema este că diferențele pot fi negative (De exemplu, ) iar abaterile ca urmare a unei astfel de însumări se vor anula reciproc. Prin urmare, ca o estimare a preciziei aproximării, se cere să se ia suma module abateri:

sau prăbușit: (în cazul în care cineva nu știe: – aceasta este pictograma sumă și – o variabilă „contor” auxiliară, care ia valori de la 1 la ).

Prin aproximarea punctelor experimentale cu diferite funcții, vom obține valori diferite și, evident, acolo unde această sumă este mai mică, acea funcție este mai precisă.

O astfel de metodă există și se numește metoda modulului minim. Cu toate acestea, în practică a devenit mult mai răspândită metoda celor mai mici pătrate, în care posibilele valori negative sunt eliminate nu de modul, ci prin pătrarea abaterilor:

, după care eforturile sunt îndreptate spre selectarea unei funcții astfel încât suma abaterilor pătrate era cât se poate de mică. De fapt, de aici provine numele metodei.

Și acum ne întoarcem la altceva punct important: după cum sa menționat mai sus, funcția selectată ar trebui să fie destul de simplă - dar există și multe astfel de funcții: liniar , hiperbolic, exponenţială, logaritmică, pătratică etc. Și, desigur, aici aș dori imediat să „reduc domeniul de activitate”. Ce clasă de funcții ar trebui să aleg pentru cercetare? O tehnică primitivă, dar eficientă:

– Cel mai simplu mod este să descrii puncte pe desen și analizați locația acestora. Dacă au tendința de a alerga în linie dreaptă, atunci ar trebui să cauți ecuația unei linii cu valori optime și . Cu alte cuvinte, sarcina este de a găsi ACEPTĂ coeficienți astfel încât suma abaterilor pătrate să fie cea mai mică.

Dacă punctele sunt situate, de exemplu, de-a lungul hiperbolă, atunci este evident clar că funcția liniară va da o aproximare slabă. În acest caz, căutăm cei mai „favorabili” coeficienți pentru ecuația hiperbolei – cele care dau suma minimă de pătrate .

Acum rețineți că în ambele cazuri vorbim funcţiile a două variabile, ale căror argumente sunt parametrii de dependență căutați:

Și, în esență, trebuie să rezolvăm o problemă standard - găsiți funcţie minimă a două variabile.

Să ne amintim exemplul nostru: să presupunem că punctele „de depozit” tind să fie situate în linie dreaptă și există toate motivele să credem că dependență liniară cifra de afaceri comercială din spațiu de vânzare cu amănuntul. Să găsim astfel de coeficienți „a” și „fi” astfel încât suma abaterilor pătrate a fost cel mai mic. Totul este ca de obicei - mai întâi Derivate parțiale de ordinul I. Conform regula liniarității Puteți diferenția chiar sub pictograma sumă:

Dacă doriți să folosiți aceste informații pentru un eseu sau o lucrare de termen, vă voi fi foarte recunoscător pentru linkul din lista de surse, veți găsi astfel de calcule detaliate în câteva locuri:

Să creăm un sistem standard:

Reducem fiecare ecuație cu „două” și, în plus, „despărțim” sumele:

Notă : analizați în mod independent de ce „a” și „fi” pot fi scoase dincolo de pictograma sumei. Apropo, formal acest lucru se poate face cu suma

Să rescriem sistemul în formă „aplicată”:

după care începe să apară algoritmul pentru rezolvarea problemei noastre:

Cunoaștem coordonatele punctelor? Noi stim. Sume îl putem găsi? Uşor. Să facem cel mai simplu sistem de două ecuații liniare în două necunoscute(„a” și „fi”). Rezolvăm sistemul, de exemplu, metoda lui Cramer, în urma căruia obținem un punct staționar. Control condiție suficientă pentru un extremum, putem verifica că în acest moment funcția ajunge exact minim. Verificarea presupune calcule suplimentare și, prin urmare, o vom lăsa în culise (dacă este necesar, cadrul lipsă poate fi vizualizat). Tragem concluzia finală:

Funcţie cel mai bun mod (cel puțin în comparație cu oricare altul funcție liniară) apropie punctele experimentale . În linii mari, graficul său trece cât mai aproape de aceste puncte. In traditie econometrie funcţia de aproximare rezultată se mai numeşte ecuație de regresie liniară pereche .

Problema luată în considerare este de mare importanță practică. În situația noastră exemplu, Eq. vă permite să preziceți ce cifră de afaceri comercială ("Igrec") magazinul va avea la una sau alta valoare a zonei de vânzare (unul sau altul sens al lui „x”). Da, prognoza rezultată va fi doar o prognoză, dar în multe cazuri se va dovedi a fi destul de precisă.

Voi analiza doar o problemă cu numerele „reale”, deoarece nu există dificultăți în ea - toate calculele sunt la nivelul curriculum-ului școlar de clasa a VII-a-8. În 95 la sută din cazuri, vi se va cere să găsiți doar o funcție liniară, dar la sfârșitul articolului voi arăta că nu este mai dificil să găsiți ecuațiile hiperbolei optime, ale exponențiale și ale altor funcții.

De fapt, tot ce rămâne este să distribuiți bunătățile promise - astfel încât să puteți învăța să rezolvați astfel de exemple nu numai cu acuratețe, ci și rapid. Studiem cu atenție standardul:

Sarcină

În urma studierii relației dintre doi indicatori, s-au obținut următoarele perechi de numere:

Folosind metoda celor mai mici pătrate, găsiți funcția liniară care aproximează cel mai bine empiric (cu experienta) date. Realizați un desen pe care să construiți puncte experimentale și un grafic al funcției de aproximare într-un sistem de coordonate dreptunghiular cartezian . Aflați suma abaterilor pătrate dintre valorile empirice și teoretice. Aflați dacă funcția ar fi mai bună (din punct de vedere al metodei celor mai mici pătrate) apropie punctele experimentale.

Vă rugăm să rețineți că semnificațiile „x” sunt naturale, iar aceasta are un sens caracteristic caracteristic, despre care voi vorbi puțin mai târziu; dar ele, desigur, pot fi și fracționate. În plus, în funcție de conținutul unei anumite sarcini, atât valorile „X” cât și „joc” pot fi complet sau parțial negative. Ei bine, ni s-a dat o sarcină „fără chip” și o începem soluţie:

Găsim coeficienții funcției optime ca soluție a sistemului:

În scopul unei înregistrări mai compacte, variabila „contor” poate fi omisă, deoarece este deja clar că însumarea se realizează de la 1 la .

Este mai convenabil să calculați sumele necesare în formă tabelară:


Calculele pot fi efectuate pe un microcalculator, dar este mult mai bine să utilizați Excel - atât mai rapid, cât și fără erori; vezi un scurt video:

Astfel, obținem următoarele sistem:

Aici puteți înmulți a doua ecuație cu 3 și scădeți al 2-lea din prima ecuație termen cu termen. Dar acesta este noroc - în practică, sistemele nu sunt adesea un cadou și, în astfel de cazuri, economisesc metoda lui Cramer:
, ceea ce înseamnă că sistemul are o soluție unică.

Sa verificam. Înțeleg că nu vrei, dar de ce să sari peste erorile în care nu pot fi ratate? Să înlocuim soluția găsită în partea stângă a fiecărei ecuații a sistemului:

Se obțin părțile din dreapta ecuațiilor corespunzătoare, ceea ce înseamnă că sistemul este rezolvat corect.

Astfel, funcția de aproximare dorită: – de la toate funcțiile liniare Ea este cea care aproximează cel mai bine datele experimentale.

Spre deosebire de Drept dependenţa cifrei de afaceri a magazinului de suprafaţa acestuia, dependenţa constatată este verso (principiul „cu cât mai mult, cu atât mai puțin”), iar acest fapt este imediat relevat de negativ pantă. Funcţie ne spune că cu o creștere a unui anumit indicator cu 1 unitate, valoarea indicatorului dependent scade in medie cu 0,65 unități. După cum se spune, cu cât prețul hrișcii este mai mare, cu atât se vinde mai puțin.

Pentru a reprezenta graficul funcției de aproximare, să găsim cele două valori ale acesteia:

și executați desenul:


Linia dreaptă construită se numește linie de tendință (și anume, o linie de tendință liniară, adică, în cazul general, o tendință nu este neapărat o linie dreaptă). Toată lumea este familiarizată cu expresia „a fi în tendință” și cred că acest termen nu are nevoie de comentarii suplimentare.

Să calculăm suma abaterilor pătrate între valorile empirice şi cele teoretice. Din punct de vedere geometric, aceasta este suma pătratelor lungimii segmentelor „zmeură”. (dintre care două sunt atât de mici încât nici măcar nu sunt vizibile).

Să rezumam calculele într-un tabel:


Se pot face din nou manual pentru orice eventualitate, voi da un exemplu pentru primul punct:

dar este mult mai eficient să o faci în modul deja cunoscut:

Repetăm ​​încă o dată: Care este semnificația rezultatului obținut? Din toate funcțiile liniare funcția y indicatorul este cel mai mic, adică din familia sa este cea mai bună aproximare. Și aici, apropo, întrebarea finală a problemei nu este întâmplătoare: ce se întâmplă dacă funcția exponențială propusă ar fi mai bine să apropii punctele experimentale?

Să găsim suma corespunzătoare a abaterilor pătrate - pentru a distinge, le voi desemna cu litera „epsilon”. Tehnica este exact aceeași:


Și din nou, pentru orice eventualitate, calculele pentru primul punct:

În Excel folosim funcția standard EXP (sintaxa poate fi găsită în Ajutor Excel).

Concluzie: , ceea ce înseamnă că funcția exponențială aproximează punctele experimentale mai rău decât o dreaptă .

Dar aici trebuie remarcat că „mai rău” este nu înseamnă încă, Ce s-a întâmplat. Acum am construit un grafic al acestei funcții exponențiale - și, de asemenea, trece aproape de puncte - atât de mult încât fără cercetări analitice este greu de spus care funcție este mai precisă.

Aceasta încheie soluția și revin la întrebarea valorilor naturale ale argumentului. În diverse studii, de obicei economice sau sociologice, „X”-urile naturale sunt folosite pentru a număra luni, ani sau alte intervale de timp egale. Luați în considerare, de exemplu, următoarea problemă.

Ei bine, la serviciu ne-am raportat la inspecție, articolul a fost scris acasă pentru conferință - acum putem scrie pe blog. În timp ce procesam datele, mi-am dat seama că nu mă puteam abține să nu scriu despre un program de completare foarte tare și necesar în Excel numit . Deci articolul va fi dedicat acestui supliment special și vă voi spune despre el folosind un exemplu de utilizare metoda celor mai mici pătrate(LSM) pentru a căuta coeficienți de ecuație necunoscuți atunci când descriu datele experimentale.

Cum să activați suplimentul „căutare soluție”.

Mai întâi, să ne dăm seama cum să activăm acest supliment.

1. Accesați meniul „Fișier” și selectați „Opțiuni Excel”

2. În fereastra care apare, selectați „Search for a solution” și faceți clic pe „go”.

3. În fereastra următoare, bifați caseta de lângă „căutați o soluție” și faceți clic pe „OK”.

4. Suplimentul este activat - acum poate fi găsit în elementul de meniu „Date”.

Metoda celor mai mici pătrate

Acum pe scurt despre metoda celor mai mici pătrate (LSM) si unde poate fi folosit.

Să presupunem că avem un set de date după ce am efectuat un fel de experiment, în care am studiat influența valorii X asupra valorii Y.

Vrem să descriem această influență matematic, astfel încât să putem folosi această formulă și să știm că dacă schimbăm valoarea lui X cu atât de mult, vom obține valoarea lui Y așa și cutare...

Voi lua un exemplu super-simplu (vezi figura).

Nu este o idee că punctele sunt situate unul după altul ca pe o linie dreaptă și, prin urmare, presupunem cu siguranță că dependența noastră este descrisă de o funcție liniară y=kx+b. În același timp, suntem absolut siguri că atunci când X este egal cu zero, valoarea lui Y este, de asemenea, egală cu zero. Aceasta înseamnă că funcția care descrie dependența va fi și mai simplă: y=kx (rețineți programa școlară).

În general, trebuie să găsim coeficientul k. Cu asta vom face MNC folosind suplimentul „căutare soluție”.

Metoda este că (aici - atenție: trebuie să vă gândiți la asta) suma pătratelor diferențelor dintre valorile obținute experimental și valorile calculate corespunzătoare este minimă. Adică, când X1=1 valoarea măsurată reală Y1=4,6, iar y1=f (x1) calculată este egal cu 4, pătratul diferenței va fi (y1-Y1)^2=(4-4.6)^ 2=0,36. Este același lucru cu următoarele: când X2=2, valoarea măsurată reală a lui Y2=8,1, iar y2 calculat este 8, pătratul diferenței va fi (y2-Y2)^2=(8-8.1)^2 =0,01. Și suma tuturor acestor pătrate ar trebui să fie cât mai mică posibil.

Deci, să începem antrenamentul despre utilizarea LSM și Programe de completare Excel „căutare soluție” .

Aplicarea suplimentului și găsirea unei soluții

1. Dacă nu ați activat suplimentul „căutare soluție”, atunci reveniți la punctul Cum să activați suplimentul „căutare soluție” și să îl activați 🙂

2. În celula A1, introduceți valoarea „1”. Această unitate va fi prima aproximare a valorii reale a coeficientului (k) a relației noastre funcționale y=kx.

3. În coloana B avem valorile parametrului X, în coloana C avem valorile parametrului Y. În celulele coloanei D introducem formula: „coeficient k înmulțit cu valoarea X. ” De exemplu, în celula D1 introducem „=A1*B1”, în celula D2 introducem „=A1*B2”, etc.

4. Considerăm că coeficientul k este egal cu unu și funcția f (x)=y=1*x este prima aproximare a soluției noastre. Putem calcula suma diferențelor pătrate dintre valorile măsurate ale lui Y și cele calculate folosind formula y=1*x. Putem face toate acestea manual introducând referințele de celule corespunzătoare în formula: „=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... etc. În final, vom greșim și realizăm că am pierdut mult timp În Excel, există o formulă specială pentru calcularea sumei diferențelor pătrate, „SUMMQRAS”, care va face totul pentru noi : intervalul valorilor măsurate Y (coloana C) și intervalul valorilor Y calculate (coloana D).

4. S-a calculat suma diferențelor pătratelor - acum accesați fila „Date” și selectați „Căutați o soluție”.

5. In meniul care apare, selectati celula A1 (cea cu coeficientul k) ca celula de schimbat.

6. Selectați celula A2 ca țintă și setați condiția „setat egal cu valoarea minimă”. Ne amintim că aceasta este celula în care calculăm suma pătratelor diferențelor dintre valorile calculate și măsurate, iar această sumă ar trebui să fie minimă. Faceți clic pe „execută”.

7. A fost selectat coeficientul k. Acum puteți verifica că valorile calculate sunt acum foarte apropiate de cele măsurate.

P.S.

În general, desigur, pentru a aproxima datele experimentale în Excel, există instrumente speciale care vă permit să descrieți datele folosind funcții liniare, exponențiale, de putere și polinomiale, astfel încât să puteți face adesea fără suplimente „căutare soluție”.. Am vorbit despre toate aceste metode de aproximare în a mea, așa că dacă sunteți interesat, aruncați o privire. Dar când vine vorba de o funcție exotică cu un coeficient necunoscut sau probleme de optimizare, atunci aici suprastructură nu putea veni la un moment mai bun.

Supliment de căutare de soluții poate fi folosit pentru alte sarcini, principalul lucru este să înțelegem esența: există o celulă în care selectăm o valoare și există o celulă țintă în care este specificată condiția pentru selectarea unui parametru necunoscut.
Asta e tot! În următorul articol vă voi spune un basm despre o vacanță, așa că pentru a nu rata publicarea articolului,