Exemple de metoda celor mai mici pătrate cu soluții excel. Metoda celor mai mici pătrate în Excel. Analiza de regresie. Câteva cuvinte despre corectitudinea datelor inițiale utilizate pentru predicție

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?! Este foarte bine acolo – trebuie doar să vă hotărâți! ...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 doar cu acuratețe, ci și FOARTE RAPID ;-) Dar mai întâi expunerea generală a problemei+ exemplu însoțitor:

Să studiem indicatorii dintr-un anumit domeniu 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 se va „încerca” tot timpul și reflectă slab tendința principală).

Astfel, funcția căutată trebuie 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 din spațiul comercial. 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 un referat, 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 orice altă 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ția de regresie liniară pereche .

Problema luată în considerare este de mare importanță practică. În situația noastră exemplu, Ec. 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, 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. 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:


Din nou, pot fi făcute 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 trece, de asemenea, aproape de puncte - atât de mult încât fără cercetare analitică 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ă.

Metoda celor mai mici pătrate (LS) se bazează pe minimizarea sumei abaterilor pătrate ale funcției selectate din datele studiate. În acest articol vom aproxima datele disponibile folosind o funcție liniarăy = A X + b .

Metoda celor mai mici pătrate(Engleză) Comun Cel mai puţin Pătrate , O.L.S.) este una dintre metodele de bază ale analizei regresiei în ceea ce privește estimarea parametrilor necunoscuți modele de regresie conform datelor eșantionului.

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

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

Notă: Cazurile de aproximare printr-un polinom de la gradul 3 până la gradul 6 sunt luate în considerare în acest articol. Aici se consideră aproximarea printr-un polinom trigonometric.

Dependență liniară

Ne interesează legătura dintre 2 variabile XȘi y. Există o presupunere că y depinde de X conform legii liniare y = topor + b. Pentru a determina parametrii acestei relații, cercetătorul a făcut observații: pentru fiecare valoare a lui x i s-a făcut o măsurare a lui y i (vezi fișierul exemplu). În consecință, să fie 20 de perechi de valori (x i; y i).

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

Din diagramă este evident că relația dintre variabile este apropiată de liniară. Pentru a înțelege care dintre numeroasele linii drepte descrie cel mai „corect” relația dintre variabile, este necesar să se determine criteriul după care vor fi comparate liniile.

Ca atare criteriu folosim expresia:

Unde ŷ i = A * x i + b ; n – numărul de perechi de valori (în cazul nostru n=20)

Expresia de mai sus este suma distanțelor pătrate dintre valorile observate ale lui y i și ŷ i și este adesea notat ca SSE ( Sumă de Pătrat Erori (Reziduuri), suma erorilor pătrate (reziduale)) .

Metoda celor mai mici pătrate este de a selecta o astfel de linie ŷ = topor + b, pentru care expresia de mai sus ia valoarea minimă.

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

Se crede că cu cât suma distanțelor pătrate este mai mică, cu atât linia corespunzătoare aproximează mai bine datele disponibile și poate fi folosită în continuare pentru a prezice valorile lui y din variabila x. Este clar că, chiar dacă în realitate nu există o relație între variabile sau relația este neliniară, atunci OLS va selecta totuși „cea mai bună” linie. Astfel, metoda celor mai mici pătrate nu spune nimic despre prezența unei relații reale între variabile; metoda vă permite pur și simplu să selectați astfel de parametri ai funcției A Și b , pentru care expresia de mai sus este minimă.

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

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

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

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

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

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

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

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

Un alt mod de a trasa o linie dreaptă folosind metoda celor mai mici pătrate este instrumentul grafic Linia de tendințe. Pentru a face acest lucru, selectați diagrama, selectați din meniu fila Aspect, V Analiza de grup clic Linia de tendințe, apoi Aproximație liniară .

Bifând caseta „afișați ecuația în diagramă” din caseta de dialog, vă puteți asigura că parametrii găsiți mai sus se potrivesc cu valorile din diagramă.

Notă: Pentru ca parametrii să se potrivească, tipul diagramei trebuie să fie . Ideea este că atunci când construiești o diagramă Programa Valorile axei X nu pot fi specificate de utilizator (utilizatorul poate specifica doar etichete care nu afectează locația punctelor). În loc de valorile X, se utilizează secvența 1; 2; 3; ... (pentru categorii de numerotare). Prin urmare, dacă construiești linie de tendință pe o diagramă de tip Programa, atunci în locul valorilor reale ale lui X se vor folosi valorile acestei secvențe, ceea ce va duce la un rezultat incorect (cu excepția cazului în care, desigur, valorile reale ale lui X nu coincid cu secvența 1; 2; 3; ...).

Metoda celor mai mici pătrate este o procedură matematică pentru construirea unei ecuații liniare care se va potrivi cel mai bine unui set de două serii de numere. Scopul utilizării acestei metode este de a minimiza eroarea pătrată totală. Excel are instrumente care vă pot ajuta să aplicați această metodă la calcule. Să ne dăm seama cum se face asta.

· Utilizarea metodei în Excel

o Activarea suplimentului „Solution Search”.

o Condiții de problemă

o Soluție

Folosind metoda din Excel

Metoda celor mai mici pătrate (LSM) este o descriere matematică a dependenței unei variabile de alta. Poate fi folosit pentru prognoză.

Activarea suplimentului Find Solution

Pentru a utiliza MNC în Excel, trebuie să activați programul de completare „Găsirea unei soluții”, care este dezactivat implicit.

1. Accesați fila "Fişier".

2. Faceți clic pe numele secțiunii "Opțiuni".

3. În fereastra care se deschide, selectați subsecțiunea „Suplimente”.

4. În bloc "Control", care se află în partea de jos a ferestrei, setați comutatorul în poziție „Suplimente Excel”(dacă are o valoare diferită) și faceți clic pe butonul "Merge...".

5. Se deschide o fereastră mică. Punem o bifă lângă parametru „Găsirea unei soluții”. Faceți clic pe butonul "BINE".

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

Lecţie: Găsirea unei soluții în Excel

Condițiile problemei

Să descriem utilizarea LSM folosind un exemplu specific. Avem două rânduri de numere XȘi y, a cărei secvență este prezentată în imaginea de mai jos.

Această dependență poate fi descrisă cel mai precis prin funcția:

În același timp, se știe că atunci când x=0 y de asemenea egale 0 . Prin urmare, această ecuație poate fi descrisă prin dependență y=nx.

Trebuie să găsim suma minimă de pătrate a diferenței.

Soluţie

Să trecem la o descriere a aplicării directe a metodei.

1. În stânga primei valori X pune un număr 1 . Aceasta va fi o valoare aproximativă a primei valori a coeficientului n.

2. În dreapta coloanei y adăugați o altă coloană - nx. În prima celulă a acestei coloane scriem formula de înmulțire a coeficientului n pe celulă a primei variabile X. În același timp, facem legătura cu câmpul cu coeficientul absolut, deoarece această valoare nu se va modifica. Faceți clic pe butonul introduce.

3. Folosind marcatorul de umplere, copiați această formulă pentru întreaga gamă a tabelului din coloana de mai jos.

4. Într-o celulă separată, calculați suma diferențelor dintre pătratele valorilor yȘi nx. Pentru a face acest lucru, faceți clic pe butonul „Inserare funcție”.

5. În deschis „Asistent de funcții” caută o intrare "SUMMKVARNA". Selectați-l și apăsați butonul "BINE".

6. Se deschide fereastra de argumente. În câmp „Matrice_x” y. În câmp „Matrice_y” introduceți intervalul de celule ale coloanei nx. Pentru a introduce valori, pur și simplu plasați cursorul în câmp și selectați intervalul corespunzător de pe foaie. După ce ați intrat, faceți clic pe butonul "BINE".

7. Accesați fila "Date". Pe panglica din cutia de instrumente "Analiză" faceți clic pe butonul „Găsirea unei soluții”.

8. Se deschide fereastra de parametri pentru acest instrument. În câmp „Optimizați funcția obiectiv” indicați adresa celulei cu formula "SUMMKVARNA". În parametru "Inainte de" asigurați-vă că setați comutatorul în poziție "Minim". În câmp „Schimbarea celulelor” indicați adresa cu valoarea coeficientului n. Faceți clic pe butonul "Gaseste o solutie".

9. Soluția va fi afișată în celula coeficientului n. Această valoare va fi cel mai mic pătrat al funcției. Dacă rezultatul satisface utilizatorul, atunci faceți clic pe butonul "BINE"într-o fereastră suplimentară.

După cum puteți vedea, aplicarea metodei celor mai mici pătrate este o procedură matematică destul de complexă. Am arătat-o ​​în acțiune folosind un exemplu simplu, dar sunt mult mai multe cazuri complexe. Cu toate acestea, instrumentele Microsoft Excel sunt concepute pentru a simplifica calculele cât mai mult posibil.

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

Dispoziții generale

Cu cât numărul în valoare absolută este mai mic, cu atât linia dreaptă aleasă (2) este mai bună. Ca o caracteristică a preciziei selectării unei linii drepte (2), putem lua suma pătratelor

Condițiile minime pentru S vor fi

	(6)
	(7)

Ecuațiile (6) și (7) pot fi scrise după cum urmează:

	(8)
	(9)

Din ecuațiile (8) și (9) este ușor de găsit a și b din valorile experimentale ale lui xi și y i. Linia (2), definită prin ecuațiile (8) și (9), se numește dreptă obținută prin metoda celor mai mici pătrate (acest nume subliniază că suma pătratelor S are un minim). Ecuațiile (8) și (9), din care se determină linia dreaptă (2), se numesc ecuații normale.

Puteți indica un mod simplu și general de a compune ecuații normale. Folosind punctele experimentale (1) și ecuația (2), putem scrie un sistem de ecuații pentru a și b

Să înmulțim părțile din stânga și din dreapta fiecăreia dintre aceste ecuații cu coeficientul primei necunoscute a (adică cu x 1, x 2, ..., x n) și să adunăm ecuațiile rezultate, rezultând prima ecuație normală (8) .

Să înmulțim părțile din stânga și din dreapta fiecăreia dintre aceste ecuații cu coeficientul celei de-a doua necunoscute b, i.e. cu 1 și adăugați ecuațiile rezultate, rezultatul este a doua ecuație normală (9).

Această metodă de obținere a ecuațiilor normale este generală: este potrivită, de exemplu, pentru funcție

există o valoare constantă și trebuie determinată din datele experimentale (1).

Sistemul de ecuații pentru k se poate scrie:

Găsiți linia dreaptă (2) folosind metoda celor mai mici pătrate.

Soluţie. Găsim:

X i =21, y i =46,3, x i 2 =91, x i y i =179,1.

Scriem ecuațiile (8) și (9)91a+21b=179,1,

21a+6b=46,3, de aici găsim
a=0,98 b=4,3.

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) și 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 pentru a găsi o soluție

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 faceți o greșeală și realizați că am pierdut mult timp. În Excel, pentru a calcula suma diferențelor pătrate, există o formulă specială, „SUMQUARRENT”, care va face totul pentru noi. Introduceți-o în celula A2 și setați date inițiale: 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,

4.1. Utilizarea funcțiilor încorporate

Calcul coeficienții de regresie efectuate cu ajutorul funcției

LINIST(Valori_y; valorile x; Const; statistici),

Valori_y- matrice de valori y,

valorile x- matrice opțională de valori X, dacă matrice X este omisă, se presupune că aceasta este o matrice (1;2;3;...) de aceeași dimensiune ca și Valori_y,

Const- o valoare booleană care indică dacă constanta este necesară b a fost egal cu 0. Dacă Const are sensul ADEVĂRAT sau omis, atunci b se calculează în mod obișnuit. Dacă argumentul Const atunci este FALS b se presupune că este 0 și valorile A sunt selectate astfel încât relația să fie îndeplinită y=ax.

Statistici este o valoare booleană care indică dacă trebuie returnate statistici suplimentare de regresie. Dacă argumentul Statistici are sensul ADEVĂRAT, apoi funcția LINIST returnează statistici suplimentare de regresie. Dacă argumentul Statistici are sensul MINCIUNĂ sau omis, apoi funcția LINIST returnează doar coeficientul Ași constantă b.

Trebuie amintit că rezultatul funcțiilor LINEST() este un set de valori – o matrice.

Pentru calcul coeficient de corelație funcția este utilizată

CORREL(Matrice1;Matrice 2),

returnând valorile coeficientului de corelație, unde Matrice1- matrice de valori y, Matrice 2- matrice de valori X. Matrice1Și Matrice 2 trebuie să aibă aceeași dimensiune.

EXEMPLUL 1. Dependenta y(X) este prezentată în tabel. Construi linie de regresie si calculeaza coeficient de corelație.

Să introducem un tabel de valori într-o foaie MS Excel și să construim un grafic de dispersie. Foaia de lucru va lua forma prezentată în Fig. 2.

Pentru a calcula valorile coeficienților de regresie AȘi b selectați celulele A7:B7, Să mergem la vrăjitorul de funcții și la categorie Statistic selectați o funcție LINIST. Să completăm caseta de dialog care apare așa cum se arată în Fig. 3 și apăsați Bine.

Ca rezultat, valoarea calculată va apărea numai în celulă A6(Fig. 4). Pentru ca valoarea să apară în celulă B6 trebuie să intrați în modul de editare (tasta F2), apoi apăsați combinația de taste CTRL+SHIFT+ENTER.

Pentru a calcula valoarea coeficientului de corelație într-o celulă C6 a fost introdusă următoarea formulă:

C7=CORREL(B3:J3;B2:J2).

Cunoașterea coeficienților de regresie AȘi b să calculăm valorile funcției y=topor+b pentru dat X. Pentru a face acest lucru, introducem formula

B5=$A$7*B2+$B$7

și copiați-l în interval C5:J5(Fig. 5).

Să trasăm linia de regresie pe diagramă. Selectați punctele experimentale de pe grafic, faceți clic dreapta și selectați comanda Datele inițiale. În caseta de dialog care apare (Fig. 5), selectați fila Rândși faceți clic pe butonul Adăuga. Să completăm câmpurile de intrare așa cum se arată în Fig. 6 și apăsați butonul Bine. O linie de regresie va fi adăugată la graficul de date experimentale. În mod implicit, graficul său va fi desenat ca puncte neconectate prin linii de netezire.

Orez. 6

Pentru a modifica aspectul liniei de regresie, efectuați următorii pași. Faceți clic dreapta pe punctele care descriu graficul liniilor și selectați comanda Tipul graficuluiși setați tipul de diagramă de împrăștiere, așa cum se arată în Fig. 7.

Tipul liniei, culoarea și grosimea pot fi modificate după cum urmează. Selectați o linie pe diagramă, faceți clic dreapta și selectați comanda din meniul contextual Format serie de date... Apoi, faceți setările, de exemplu, așa cum se arată în Fig. 8.

Ca rezultat al tuturor transformărilor, obținem un grafic al datelor experimentale și o linie de regresie într-o zonă grafică (Fig. 9).

4.2. Folosind o linie de tendință.

Construcția diferitelor dependențe de aproximare în MS Excel este implementată ca o proprietate grafică - linie de tendință.

EXEMPLUL 2. Ca rezultat al experimentului, a fost determinată o anumită dependență de masă.

Selectați și construiți o dependență aproximativă. Construiți grafice ale dependențelor analitice tabelare și selectate.

Rezolvarea problemei poate fi împărțită în următoarele etape: introducerea datelor inițiale, construirea unui grafic de dispersie și adăugarea unei linii de tendință la acest grafic.

Să ne uităm la acest proces în detaliu. Să introducem datele inițiale în foaia de lucru și să trasăm datele experimentale. Apoi, selectați punctele experimentale din grafic, faceți clic dreapta și utilizați comanda Adăuga l linie de tendință(Fig. 10).

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

Prima filă (Fig. 11) a acestei ferestre indică tipul de dependență de aproximare.

Pe al doilea (Fig. 12) se determină parametrii de construcție:

· denumirea dependenței de aproximare;

· prognoză înainte (înapoi) de n unități (acest parametru determină câte unități înainte (înapoi) trebuie extinsă linia de tendință);

dacă să arate punctul de intersecție al unei curbe cu o linie dreaptă y=const;

· afișați sau nu funcția de aproximare pe diagramă (opțiunea de a afișa ecuația pe diagramă);

· dacă se plasează sau nu valoarea abaterii standard pe diagramă (opțiunea de a plasa valoarea fiabilității aproximării pe diagramă).

Să alegem un polinom de gradul doi ca dependență de aproximare (Fig. 11) și să afișăm ecuația care descrie acest polinom pe un grafic (Fig. 12). Diagrama rezultată este prezentată în Fig. 13.

În mod similar, folosind linii de tendință puteți selecta parametrii unor astfel de dependențe precum

liniar y=a∙x+b,

logaritmică y=a∙ln(X)+b,

· exponenţial y=a∙e b,

· calmante y=a∙x b,

polinom y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+dși așa mai departe, până la un polinom de gradul 6 inclusiv,

· filtrare liniară.

4.3. Folosind un bloc de rezolvare

Un interes semnificativ este implementarea în MS Excel a selectării parametrilor folosind metoda celor mai mici pătrate folosind un bloc rezolvator. Această tehnică vă permite să selectați parametrii unei funcții de orice tip. Să luăm în considerare această posibilitate folosind următoarea problemă ca exemplu.

EXEMPLUL 3. În urma experimentului s-a obținut dependența z(t), prezentată în tabel

Selectați coeficienții de dependență Z(t)=La 4 +Bt 3 +Ct 2 +Dt+K metoda celor mai mici pătrate.

Această problemă este echivalentă cu problema găsirii minimului unei funcții de cinci variabile

Să luăm în considerare procesul de rezolvare a problemei de optimizare (Fig. 14).

Lasă valorile A, ÎN, CU, DȘi LA stocate în celule A7:E7. Să calculăm valorile teoretice ale funcției Z(t)=La 4 +Bt 3 +Ct 2 +Dt+K pentru dat t(B2:J2). Pentru a face acest lucru, în celulă B4 introduceți valoarea funcției la primul punct (celula B2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Să copiem această formulă în interval C4:J4și obțineți valoarea așteptată a funcției în punctele ale căror abscise sunt stocate în celule B2:J2.

La celulă B5 Să introducem o formulă care calculează pătratul diferenței dintre punctele experimentale și cele calculate:

B5=(B4-B3)^2,

și copiați-l în interval C5:J5. Într-o celulă F7 vom stoca eroarea totală pătrată (10). Pentru a face acest lucru, introduceți formula:

F7 = SUMA(B5:J5).

Să folosim comanda Service®Căutați o soluțieși rezolvați problema de optimizare fără restricții. Să completăm în mod corespunzător câmpurile de introducere din caseta de dialog prezentată în Fig. 14 și apăsați butonul A executa. Dacă se găsește o soluție, fereastra prezentată în fig. 15.

Rezultatul blocului de decizie va fi transmis în celule A7:E7valorile parametrilor funcții Z(t)=La 4 +Bt 3 +Ct 2 +Dt+K. În celule B4:J4 primim valoarea așteptată a funcției la punctele de plecare. Într-o celulă F7 vor fi stocate eroare pătrată totală.

Puteți afișa puncte experimentale și o linie adaptată într-o zonă grafică selectând un interval B2:J4, apel Chart Wizardși apoi formatați aspect grafice primite.

Orez. 17 afișează foaia de lucru MS Excel după ce au fost efectuate calculele.

5. REFERINȚE

1. Alekseev E.R., Chesnokova O.V., Rezolvarea problemelor de matematică computațională în pachetele Mathcad12, MATLAB7, Maple9. – NT Press, 2006.–596 p. :il. -(Tutorial)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, rezolvarea problemelor de inginerie și matematică. –M., BIOM, 2008.–260 p.

3. Berezin I.S., Zhidkov N.P., Metode de calcul – M.: Nauka, 1966. – 632 p.

4. Garnaev A.Yu., Utilizarea MS EXCEL și VBA în economie și finanțe. – Sankt Petersburg: BHV - Petersburg, 1999.–332 p.

5. Demidovich B.P., Maron I.A., Shuvalova V.Z., Metode numerice de analiză – M.: Nauka, 1967. – 368 p.

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

7. Alekseev E.R., Chesnokova O.V. Ghid pentru efectuarea lucrărilor de laborator în MS EXCEL. Pentru studenții de toate specialitățile. Doneţk, DonNTU, 2004. 112 p.