Metoda cu cele mai mici pătrate din Excel. Analiza regresiei

Metoda celor mai mici pătrate (OLS) se referă la zona analizei de regresie. Are multe utilizări, deoarece permite o reprezentare aproximativă a unei funcții date de altele mai simple. OLS poate fi extrem de util în procesarea observațiilor și este utilizat în mod activ pentru a evalua anumite cantități din rezultatele măsurătorilor ale altora care conțin erori aleatorii. Din acest articol, veți învăța cum să implementați calculul celor mai mici pătrate în Excel.

Declarația problemei pe un exemplu concret

Să presupunem că există doi parametri X și Y. și Y depinde de X. Din moment ce CMN ne interesează în ceea ce privește analiza de regresie (Excel în metodele sale sunt puse în aplicare cu ajutorul funcțiilor încorporate), ar trebui să meargă imediat la luarea în considerare a unei sarcini specifice.

Astfel, X - zona comercială a magazinului alimentar, măsurată în metri pătrați, și Y - cifra de afaceri anuală, definită în milioane de ruble.

Este necesar să se facă o previziune, ce cifră de afaceri (Y) va fi la magazin, dacă are una sau alta zonă de vânzare. Evident, funcția Y = f (X) este în creștere, deoarece hipermarketul vinde mai multe bunuri decât standul.

Câteva cuvinte despre corectitudinea datelor inițiale utilizate pentru predicție

Să presupunem că avem o masă construită în funcție de datele pentru magazinele n.

Potrivit statisticilor matematice, rezultatele vor fi mai mult sau mai puțin corecte dacă se investighează date despre cel puțin 5-6 obiecte. În plus, nu puteți folosi rezultatele "anormale". În special, un mic magazin de elită poate avea o cifră de afaceri mai mare decât cifra de afaceri a marilor magazine din clasa "masmarket".

Esența metodei

Datele de masă pot fi reprezentate pe planul cartezian sub forma punctelor M₁ (x₁, y₁) hellip-M_n (x_n, y_n). Acum soluția problemei este redusă la selectarea funcției aproximative y = f (x) având un grafic care trece cât mai aproape posibil de punctele M₁ M_{2, ..}M_n.

Desigur, puteți folosi un polinom de grad înalt, dar această opțiune nu este numai dificil de implementat, ci pur și simplu nu este corectă, deoarece nu va reflecta tendința principală pe care trebuie să o descoperiți. Soluția cea mai rezonabilă este găsirea liniei drepte y = ax + b, care aproximează cel mai bine datele experimentale, mai precis, coeficienții - a și b.

Estimarea preciziei

Pentru orice aproximație, estimarea preciziei devine deosebit de importantă. Noi denotăm prin e_eu diferența (deviația) dintre valorile funcționale și experimentale pentru punctul x_eu, adică e_eu = y_eu- f (x_eu).

Este evident că, pentru a evalua acuratețea aproximarea poate fi utilizată suma abaterilor, t. E. În linia de selecție pentru reprezentarea aproximativă a dependenței X pe Y este necesar să se acorde preferință cel care are cea mai mică valoare a sumei e_eu în toate punctele considerate. Cu toate acestea, nu totul este atât de simplu, încât, împreună cu deviațiile pozitive, vor fi practic negative.

Puteți rezolva problema utilizând module de abatere sau pătrate. Ultima metodă a fost cea mai utilizată. Acesta este folosit în multe domenii, inclusiv analiza de regresie (în Excel, este implementat cu două funcții încorporate) și a fost mult timp dovedit eficient.

Metoda celor mai mici pătrate

În Excel, după cum știți, există o funcție de sincronizare automată care vă permite să calculați valorile tuturor valorilor din gama selectată. Astfel, nimic nu ne împiedică să calculam valoarea expresiei (de ex₁² + e₂² + e₃²+ ... e_n²).

În notația matematică, aceasta are forma:

Deoarece inițial a fost luată o decizie de aproximare cu o linie dreaptă, avem:

Astfel, problema găsirii unei linii care descrie cel mai bine dependența specifică a cantităților X și Y este redusă la calcularea minimei unei funcții a două variabile:

Pentru a face acest lucru, trebuie să echivalăm la zero derivații parțiali cu privire la noile variabile a și b și să rezolvăm un sistem primitiv format din două ecuații cu 2 necunoscute:

După transformări simple, inclusiv împărțirea cu 2 și manipularea sumelor, obținem:

Rezolvând-o, de exemplu, prin metoda Cramer, obținem un punct staționar cu câțiva coeficienți a^* și b^*. Acesta este cel minim, adică să prezicăm ce cifră de afaceri va fi la magazin pentru o anumită zonă, 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ă obțineți o idee dacă o achiziție dintr-o anumită zonă se va achita de împrumut.

Cum să implementați metoda celor mai mici pătrate în Excel

În "Excel" există o funcție pentru calcularea valorii OLS. Are următoarea formă: "TRENDS" (valori cunoscute ale valorilor Y cunoscute ale valorilor X-noi ale X-const.). Aplicăm formula pentru calculul OLS în Excel în tabelul nostru.

Pentru a face acest lucru, celula în cazul în care rezultatul calculului prin metoda celor mai mici pătrate în Excel pentru a fi afișate, tastați caracterele „=“ și alegeți funcția „Trends“. În fereastra deschisă completați câmpurile corespunzătoare, subliniind:

• intervalul de valori cunoscute pentru Y (în acest caz, date pentru cifra de afaceri);
• intervalul x₁, hellip-x_n, adică dimensiunea spațiului comercial;
• și valori cunoscute și necunoscute ale lui x, pentru care trebuie să aflați mărimea cifrei de afaceri (pentru informații despre locația lor pe foaia de lucru, a se vedea mai jos).

În plus, formula conține variabila logică "Konst". Dacă introduceți 1 în câmpul corespunzător acesteia, aceasta va însemna că trebuie să efectuați calcule, presupunând că b = 0.

Dacă doriți să știți prognoza pentru mai mult de o valoare a lui x, apoi, după introducerea unei formule, apăsați nu pe tasta „Enter“, și trebuie să tastați pe combinația tastatură de «Shift» + «control» + «Enter» ( «Punere»).

Unele caracteristici

Analiza de regresie poate fi accesată chiar și de către cazane. Formula Excel pentru prezicerea valorii unei game de variabile necunoscute - "TRENDS" - poate fi utilizată chiar și de cei care nu au auzit niciodată de metoda celor mai mici pătrate. Este suficient doar să știi câteva dintre trăsăturile ei. Î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ă în „TREND“ nu este specificat cu o serie de x cunoscute, apoi în cazul funcției în programul Excel va trata ca o matrice de numere întregi, al căror număr corespunde intervalului cu valorile date variabilei y.
• Pentru a obține o serie de valori "prezise" la ieșire, expresia pentru calcularea tendinței trebuie introdusă ca o formulă de matrice.
• Dacă nu sunt specificate noi valori ale lui x, funcția "TRENDS" consideră că acestea sunt egale cu cele cunoscute. Dacă nu sunt specificate, atunci matricea 1- 2- 3-4-hellip- este luată ca argument, proporțional cu intervalul cu parametrii deja specificați y.
• Intervalul care conține noile valori ale lui x trebuie să fie alcătuit din aceleași sau mai multe rânduri sau coloane ca și intervalul cu valorile date de y. Cu alte cuvinte, trebuie să fie proporțională cu variabilele independente.
• O matrice cu valori cunoscute ale lui x poate conține mai multe variabile. Cu toate acestea, dacă este doar unul, este necesar ca intervalele cu valori date de x și y să fie proporționale. În cazul mai multor variabile, este necesar ca intervalul cu valori date de y să fie cuprins într-o singură coloană sau într-o singură linie.

Funcția "PREDICȚIE"

Analiza de regresie în Excel este implementată utilizând mai multe funcții. Una dintre ele se numește "PREDICȚIE". Este similară cu "TRENDS", adică rezultă calcule folosind metoda celor mai mici pătrate. Cu toate acestea, numai pentru un X, pentru care valoarea lui Y este necunoscută.

Acum știți formulele din Excel pentru Dummies, permițându-vă să prezicați valoarea valorii viitoare a unui indicator dat în funcție de o tendință liniară.