Regresia în Excel: ecuație, exemple. Regresie liniară

Analiza de regresie este o metodă statistică de investigare, care face posibilă demonstrarea dependenței unui parametru de una sau mai multe variabile independente. În era de precomputer, utilizarea sa a fost destul de dificilă, mai ales atunci când era vorba de cantități mari de date. Astăzi, după ce ați învățat cum să construiți o regresie în Excel, puteți rezolva probleme statistice complexe în doar câteva minute. Mai jos sunt exemple specifice din domeniul economiei.

Tipuri de regresie

Acest concept a fost introdus în matematică Francis Galton în 1886. Apare regresia:

• liniar;
• parabolic;
• legea puterii;
• exponențială;
• hiperbolică;
• exponențială;
• logaritmică.

Exemplul 1

Să analizăm problema determinării dependenței numărului de membri pensionari din colectiv de salariul mediu la 6 întreprinderi industriale.

Sarcina. Șase întreprinderi au analizat salariul mediu lunar și numărul de angajați care au ieșit din proprie inițiativă. În formă tabelară avem:

Pentru problema determinării dependenței numărului de salariați care au rămas la salariul mediu la 6 întreprinderi, modelul de regresie are forma ecuației Y = a₀ + și₁x₁ +hellip + a_kx_k, unde x_eu - influențarea variabilelor, a_eu - coeficienții de regresie și k - numărul de factori.

Pentru această sarcină, Y este indicatorul angajaților care au plecat, iar factorul care influențează este salariul, care este notat de X.

Utilizarea procesorului de masă Excel

Analiza regresiei în Excel trebuie precedată de aplicarea funcțiilor încorporate în datele tabulare disponibile. Cu toate acestea, pentru aceste scopuri este mai bine să folosiți pachetul de analiză foarte util. Pentru ao activa, aveți nevoie de:

• Din fila "Fișier" accesați secțiunea "Opțiuni";
• în fereastra deschisă selectați linia "Suplimente";
• Faceți clic pe butonul "Mergeți", situat în partea de jos, în partea dreaptă a liniei "Management";
• Puneți un semn de selectare lângă numele "Pachetul de analiză" și confirmați acțiunile făcând clic pe "Ok".

Dacă este făcută corect, partea dreapta a filei „date“, situată deasupra foii de lucru „Excel“, arată butonul dorit.

Regresia liniară în Excel

Acum, că avem la dispoziție toate instrumentele virtuale necesare pentru a implementa calculele econometrice, putem începe să ne rezolvăm problema. Pentru a face acest lucru:

• faceți clic pe butonul "Analiza datelor";
• în fereastra deschisă faceți clic pe butonul "Regresie";
• în fila care apare, introduceți intervalul de valori pentru Y (numărul de angajați care au plecat) și pentru X (salariul lor);
• ne confirmăm acțiunile făcând clic pe butonul "Ok".

Ca rezultat, programul va completa automat o foaie nouă a procesorului de masă cu date de analiză de regresie. Fiți atenți! În Excel, există posibilitatea de a stabili în mod independent locul pe care îl preferați în acest scop. De exemplu, aceasta ar putea fi aceeași foaie ca și valorile Y și X sau chiar o carte nouă proiectată special pentru a stoca astfel de date.

Analiza rezultatelor regresiei pentru R-pătrat

În Excel, datele obținute în timpul prelucrării datelor din exemplul în cauză au forma:

Mai întâi de toate, ar trebui să acordați atenție valorii R-pătratului. Este coeficientul de determinare. In acest exemplu, R-pătrat = 0,755 (75,5%), m. E. Parametrii calculați ai modelului pentru a explica relația dintre parametrii considerați cu 75,5%. Cu cât este mai mare valoarea coeficientului de determinare, modelul selectat este considerat mai aplicabil pentru o anumită sarcină. Se crede că descrie corect situația reală la valoarea R pătrat peste 0,8. Dacă R-pătratul<0.5, atunci o astfel de analiză a regresiei în Excel nu poate fi considerată rezonabilă.

Analiza coeficienților

Numărul 64.1428 arată ce va fi valoarea lui Y dacă toate valorile xi din modelul în cauză vor fi resetate. Cu alte cuvinte, se poate argumenta că valoarea parametrului analizat este influențată de alți factori care nu sunt descriși într-un anumit model.

Următorul factor -0.16285 situat în B18 de celule, arată influența importantă a variabilei X la Y. Acest lucru înseamnă că salariul mediu al angajaților în cadrul modelului afectează numărul de demisii din greutatea -0.16285, t. E. Gradul de impactul său la toate mici. Semnul „-“ indică faptul că coeficientul este negativ. Este evident, din moment ce știm cu toții că mai mult salariul în întreprindere, mai puțin oamenii și-au exprimat dorința de a rezilia contractul de muncă sau respins.

Regresie multiplă

Prin acest termen se înțelege ecuația de conectare cu mai multe variabile independente ale formei:

y = f (x₁+x₂+hellip-x_m) + epsilon-, unde y este atributul rezultat (variabila dependenta), si x₁, x₂, hellip-x_m - acestea sunt semne-factori (variabile independente).

Evaluarea parametrilor

Pentru regresia multiplă (MP), aceasta se realizează folosind metoda OLS (least squares). Pentru ecuațiile liniare cu forma Y = a + b₁x₁ +hellip- + b_mx_m+ epsilon - construim un sistem de ecuații normale (vezi mai jos)

Pentru a înțelege principiul metodei, luați în considerare cazul cu două factori. Atunci avem o situație descrisă de formula

Prin urmare, obținem:

unde sigma - este variația caracteristicilor corespunzătoare reflectate în index.

OLS este aplicabil ecuației MP pe o scară standard. În acest caz, obținem ecuația:

în care t_y, T_{x1 hellip-}T_xm - variabilele standardizate pentru care valorile medii sunt 0- beta-_eu - coeficienții de regresie standardizați, iar abaterea standard este de 1.

Vă rugăm să rețineți că toate beta-_eu în acest caz sunt date ca fiind normalizate și centralizate, astfel încât compararea lor între ele este considerată corectă și admisibilă. În plus, se obișnuiește să se precizeze factori, care elimină cele care au cele mai scăzute valori beta-i.

Problema folosind ecuația de regresie liniară

Să presupunem că există o tabelă a dinamicii prețurilor pentru o anumită marfă N în ultimele 8 luni. Este necesar să se ia o decizie cu privire la oportunitatea achiziționării lotului său la un preț de 1850 de ruble pe tonă.

Pentru a rezolva această problemă în procesorul de masă Excel, trebuie să utilizați instrumentul deja cunoscut "Analiza datelor". Apoi selectați secțiunea "Regresie" și specificați parametrii. Trebuie să ne amintim că în „intervalul de intrare Y» ar trebui să fie introduse într-o serie de valori ale variabilei dependente (în acest caz, prețul mărfurilor în luni anumite perioade ale anului) și în „intrare intervalul X» - pentru o organizație independentă (luna). Confirmați acțiunea făcând clic pe "Ok". Pe noua foaie (dacă se specifică așa), obținem datele pentru regresie.

Noi construim pe ele ecuație liniară de forma y = ax + b, în ​​cazul în care ca parametrii a și b sunt coeficienții din numărul liniei lunii și numele coeficienților și «Y-intersecția“ linia a foii cu rezultatele analizei de regresie. Astfel, ecuația de regresie liniară (VR) pentru problema 3 este scrisă ca:

Prețul mărfii N = 11,714 * numărul lunii + 1727,54.

sau în notație algebrică

y = 11,714 x + 1727,54

Analiza rezultatelor

Pentru a determina dacă ecuația de regresie liniară rezultată este adecvată, se folosesc coeficienții corelării multiple (KMC) și determinarea, precum și criteriul Fisher și testul Student. În tabelul Excel cu rezultate de regresie acestea apar sub numele de mai multe R, R-squared, F-statistici și t-statistici, respectiv.

KMC R face posibilă estimarea stării relației probabiliste dintre variabilele independente și cele dependente. Valoarea ridicată indică o conexiune suficient de puternică între variabila „Numărul lunii“ și „N Prețul produsului în ruble per 1 tonă.“ Cu toate acestea, natura acestei relații rămâne necunoscută.

Pătratul coeficientului de determinare R²(RI) este o caracteristică numerică a fracțiunii din răspândirea totală și prezintă scatterul a cărui parte din datele experimentale, adică Valorile variabilei dependente corespund ecuației de regresie liniară. În problema examinată, această valoare este de 84,8%, adică datele statistice sunt descrise cu un grad ridicat de precizie de către SD-ul primit.

F-statistica, numită și criteriul lui Fisher, este folosită pentru a evalua semnificația dependenței liniare, respingând sau confirmând ipoteza existenței sale.

Valoarea t-statistic (testul t al lui Student) ajută la evaluarea semnificației coeficientului de la orice membru dependență liniară necunoscută liber. Dacă valoarea testului t> t_cr, atunci ipoteza nesemnificativității termenului liber al ecuației liniare este respinsă.

În această problemă pentru un termen gratuit prin intermediul instrumentelor „Excel“, sa constatat că t = 169,20903, și p = 2,89E-12, t. E. Au o probabilitate zero, ca credincioși va fi respins ipoteza insignifianța termenului liber. Pentru coeficientul cu t = 5.79405 necunoscut și p = 0.001158. Cu alte cuvinte, probabilitatea ca o ipoteză corectă a respins va insignifianța coeficientului de necunoscut, este de 0,12%.

Astfel, se poate argumenta că ecuația de regresie liniară rezultată este adecvată.

Problema oportunității de a cumpăra un bloc de acțiuni

Regresia multiplă în Excel se realizează utilizând același instrument "Analiza datelor". Să luăm în considerare o problemă specifică aplicată.

Compania de administrare "NNN" ar trebui să ia o decizie cu privire la oportunitatea de a cumpăra o participație de 20% în SA "MMM". Costul pachetului (SP) este de 70 de milioane de dolari SUA. Specialiștii din "NNN" au colectat date despre tranzacții similare. Sa decis să se evalueze valoarea participației la acești parametri, exprimată în milioane de dolari americani, astfel:

• conturi de plătit (VK);
• volumul cifrei de afaceri anuale (VO);
• conturi de încasat (VD);
• valoarea mijloacelor fixe (SOF).

În plus, parametrul arieratelor salariale (V3 P) este utilizat în mii de dolari SUA.

Soluție utilizând foaia de calcul Excel

Mai întâi de toate, trebuie să creați un tabel de date de intrare. Are următoarea formă:

următor:

• Apelați fereastra "Analiza datelor";
• selectați secțiunea "Regresie";
• În caseta "Interval de introducere Y" introduceți intervalul de valori al variabilelor dependente din coloana G;
• faceți clic pe pictograma cu săgeata roșie din partea dreaptă a ferestrei "Interval de introducere X" și selectați intervalul tuturor valorilor din coloanele B, C, D, F de pe foaie.

Marcați elementul "Foaie nouă de lucru" și faceți clic pe "Ok".

O analiză de regresie este obținută pentru această sarcină.

Rezultatele studiilor și concluziile

"Colectarea" din datele rotunjite prezentate mai sus pe foaia de calcul tabelar Excel, ecuația de regresie:

SP = 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265,844.

Într-o formă matematică mai cunoscută, ea poate fi scrisă ca:

y = 0.103 * x1 + 0.541 * x2 - 0.031 * x3 + 0.405 * x4 + 0.691 * x5 - 265.844

Datele pentru MMM sunt următoarele:

Înlocuindu-le în ecuația de regresie, obțineți o cifră de 64,72 milioane de dolari SUA. Aceasta înseamnă că acțiunile MMM nu ar trebui să fie cumpărate, deoarece valoarea lor de 70 de milioane de dolari este destul de supraestimată.

După cum puteți vedea, utilizarea procesorului de masă Excel și a ecuației de regresie a făcut posibilă luarea unei decizii informate cu privire la fezabilitatea unei tranzacții foarte specifice.

Acum știi ce regresie este. Exemplele din Excel, discutate mai sus, vă vor ajuta în rezolvarea problemelor practice din domeniul econometriei.