Model de regresie în exemplu Excel. Analiza regresiei în excel

În postările anterioare, analiza s-a concentrat adesea pe o singură variabilă numerică, cum ar fi randamentele fondurilor mutuale, timpii de încărcare a paginilor web sau consumul de băuturi răcoritoare. În aceasta și în notele ulterioare, ne vom uita la metode de predicție a valorilor unei variabile numerice în funcție de valorile uneia sau mai multor alte variabile numerice.

Materialul va fi ilustrat cu un exemplu transversal. Estimarea volumului vânzărilor într-un magazin de îmbrăcăminte. Lanțul de magazine de îmbrăcăminte cu discount Sunflowers se extinde constant de 25 de ani. Cu toate acestea, compania nu are în prezent o abordare sistematică pentru selectarea de noi puncte de vânzare. Locația în care o companie intenționează să deschidă un nou magazin este determinată pe baza unor considerente subiective. Criteriile de selecție sunt condițiile de închiriere favorabile sau ideea managerului despre locația ideală a magazinului. Imaginează-ți că ești șeful departamentului de proiecte speciale și planificare. Ai fost însărcinat cu elaborarea unui plan strategic pentru deschiderea de noi magazine. Acest plan ar trebui să includă o prognoză a vânzărilor anuale pentru magazinele nou deschise. Considerați că spațiul comercial este direct legat de venituri și doriți să luați în considerare acest lucru în procesul de luare a deciziilor. Cum dezvoltați un model statistic pentru a estima vânzările anuale în funcție de dimensiunea unui nou magazin?

De obicei, analiza de regresie este utilizată pentru a prezice valorile unei variabile. Scopul său este de a dezvolta un model statistic care poate prezice valorile unei variabile dependente, sau răspuns, din valorile a cel puțin unei variabile independente sau explicative. În această notă, ne vom uita la regresia liniară simplă - o metodă statistică care vă permite să preziceți valorile unei variabile dependente Y prin valori ale variabilelor independente X. Notele ulterioare vor descrie un model de regresie multiplă conceput pentru a prezice valorile unei variabile independente Y pe baza valorilor mai multor variabile dependente ( X 1, X 2, …, X k).

Descărcați nota în sau format, exemple în format

Tipuri de modele de regresie

Unde ρ 1 – coeficientul de autocorelare; Dacă ρ 1 = 0 (fără autocorelare), D≈ 2; Dacă ρ 1 ≈ 1 (autocorelație pozitivă), D≈ 0; Dacă ρ 1 = -1 (autocorelație negativă), D ≈ 4.

În practică, aplicarea criteriului Durbin-Watson se bazează pe compararea valorii D cu valori teoretice critice d LȘi d U pentru un număr dat de observații n, numărul de variabile independente ale modelului k(pentru regresie liniară simplă k= 1) și nivelul de semnificație α. Dacă D< d L , ipoteza despre independența abaterilor aleatoare este respinsă (deci, există o autocorelație pozitivă); Dacă D>dU, ipoteza nu este respinsă (adică nu există autocorelație); Dacă d L< D < d U , nu există motive suficiente pentru a lua o decizie. Când valoarea calculată D depaseste 2, apoi cu d LȘi d U Nu coeficientul în sine este comparat D, iar expresia (4 – D).

Pentru a calcula statisticile Durbin-Watson în Excel, să ne întoarcem la tabelul de jos din Fig. 14 Retragerea soldului. Numătorul din expresia (10) este calculat folosind funcția =SUMMAR(array1;array2) și numitorul =SUMMAR(array) (Fig. 16).

Orez. 16. Formule pentru calcularea statisticilor Durbin-Watson

În exemplul nostru D= 0,883. Întrebarea principală este: ce valoare a statisticii Durbin-Watson ar trebui considerată suficient de mică pentru a concluziona că există o autocorelație pozitivă? Este necesar să se coreleze valoarea lui D cu valorile critice ( d LȘi d U), în funcție de numărul de observații nși nivelul de semnificație α (Fig. 17).

Orez. 17. Valorile critice ale statisticilor Durbin-Watson (fragment de tabel)

Astfel, în problema volumului vânzărilor într-un magazin care livrează mărfuri la domiciliu, există o variabilă independentă ( k= 1), 15 observații ( n= 15) și nivelul de semnificație α = 0,05. Prin urmare, d L= 1,08 și dU= 1,36. Deoarece D = 0,883 < d L= 1,08, există o autocorelare pozitivă între reziduuri, metoda celor mai mici pătrate nu poate fi utilizată.

Testarea ipotezelor despre panta si coeficientul de corelatie

Mai sus, regresia a fost folosită numai pentru prognoză. Pentru a determina coeficienții de regresie și pentru a prezice valoarea unei variabile Y pentru o anumită valoare variabilă X S-a folosit metoda celor mai mici pătrate. În plus, am examinat eroarea pătratică medie a estimării și coeficientul de corelație mixt. Dacă analiza reziduurilor confirmă că nu sunt încălcate condițiile de aplicabilitate ale metodei celor mai mici pătrate, iar modelul de regresie liniară simplă este adecvat, pe baza datelor eșantionate, se poate argumenta că există o relație liniară între variabilele din populatie.

Aplicațiet -criterii pentru panta. Testând dacă panta populației β 1 este egală cu zero, puteți determina dacă există o relație semnificativă statistic între variabile XȘi Y. Dacă această ipoteză este respinsă, se poate argumenta că între variabile XȘi Y există o relație liniară. Ipotezele nule și alternative sunt formulate astfel: H 0: β 1 = 0 (nu există dependență liniară), H1: β 1 ≠ 0 (există o dependență liniară). A-prioriu t-statistica este egală cu diferența dintre panta eșantionului și valoarea ipotetică a pantei populației, împărțită la rădăcina medie a erorii pătratice a estimării pantei:

(11) t = (b 1 – β 1 ) / S b 1

Unde b 1 – panta regresiei directe pe datele eșantionului, β1 – panta ipotetică a populației directe, , și statistici de testare t Are t-distributie cu n – 2 grade de libertate.

Să verificăm dacă există o relație semnificativă statistic între dimensiunea magazinului și vânzările anuale la α = 0,05. t-criteriul este afișat împreună cu alți parametri atunci când este utilizat Pachet de analize(opțiune Regresia). Rezultatele complete ale pachetului de analiză sunt prezentate în Fig. 4, fragment legat de t-statistica - în Fig. 18.

Orez. 18. Rezultatele aplicării t

De la numărul de magazine n= 14 (vezi Fig. 3), valoare critică t-statisticile la un nivel de semnificație de α = 0,05 pot fi găsite folosind formula: t L=STUDENT.ARV(0,025,12) = –2,1788, unde 0,025 este jumătate din nivelul de semnificație și 12 = n – 2; t U=STUDENT.OBR(0,975,12) = +2,1788.

Deoarece t-statistica = 10,64 > t U= 2,1788 (Fig. 19), ipoteză nulă H 0 respins. Pe de alta parte, R-valoare pentru X= 10,6411, calculat prin formula =1-STUDENT.DIST(D3,12,TRUE), este aproximativ egal cu zero, deci ipoteza H 0 din nou respins. Faptul că R-valoarea aproape zero înseamnă că, dacă nu ar exista o relație liniară adevărată între dimensiunile magazinelor și vânzările anuale, ar fi practic imposibil să o detectăm folosind regresia liniară. Prin urmare, există o relație liniară semnificativă statistic între vânzările medii anuale ale magazinului și dimensiunea magazinului.

Orez. 19. Testarea ipotezei despre panta populației la un nivel de semnificație de 0,05 și 12 grade de libertate

AplicațieF -criterii pentru panta. O abordare alternativă pentru testarea ipotezelor despre panta regresiei liniare simple este de a utiliza F-criterii. Să ne amintim asta F-test este folosit pentru a testa relația dintre două varianțe (pentru mai multe detalii, vezi). Când se testează ipoteza pantei, măsura erorilor aleatoare este varianța erorii (suma erorilor pătrate împărțită la numărul de grade de libertate), deci F-criteriul folosește raportul varianței explicat prin regresie (adică valoarea SSR, împărțit la numărul de variabile independente k), la variația erorii ( MSE = S YX 2 ).

A-prioriu F-statistica este egală cu pătratul mediu al regresiei (MSR) împărțit la varianța erorii (MSE): F = MSR/ MSE, Unde MSR=SSR / k, MSE =SSE/(n– k – 1), k– numărul de variabile independente în modelul de regresie. Test statistici F Are F-distributie cu kȘi n– k – 1 grade de libertate.

Pentru un nivel de semnificaţie dat α, regula de decizie se formulează astfel: dacă F>FU, se respinge ipoteza nulă; altfel nu se respinge. Rezultatele, prezentate sub forma unui tabel rezumat al analizei varianței, sunt prezentate în Fig. 20.

Orez. 20. Tabel de analiză a varianței pentru testarea ipotezei despre semnificația statistică a coeficientului de regresie

De asemenea t-criteriu F-criteriul este afișat în tabel atunci când este utilizat Pachet de analize(opțiune Regresia). Rezultatele complete ale lucrării Pachet de analize sunt prezentate în Fig. 4, fragment legat de F-statistica – în Fig. 21.

Orez. 21. Rezultatele aplicării F-criterii obtinute cu ajutorul pachetului de analiza Excel

Statistica F este 113,23 și R-valoare apropiată de zero (celula SemnificaţieF). Dacă nivelul de semnificație α este 0,05, determinați valoarea critică F-distributii cu unu si 12 grade de libertate pot fi obtinute folosind formula F U=F.OBR(1-0,05;1;12) = 4,7472 (Fig. 22). Deoarece F = 113,23 > F U= 4,7472 și R-valoare apropiată de 0< 0,05, нулевая гипотеза H 0 este respinsă, adică Mărimea unui magazin este strâns legată de vânzările sale anuale.

Orez. 22. Testarea ipotezei pantei populației la un nivel de semnificație de 0,05 cu unu și 12 grade de libertate

Interval de încredere care conţine panta β 1 . Pentru a testa ipoteza că există o relație liniară între variabile, puteți construi un interval de încredere care conține panta β 1 și puteți verifica dacă valoarea ipotetică β 1 = 0 aparține acestui interval. Centrul intervalului de încredere care conține panta β 1 este panta eșantionului b 1 , iar limitele sale sunt cantitățile b 1 ±tn –2 S b 1

După cum se arată în Fig. 18, b 1 = +1,670, n = 14, S b 1 = 0,157. t 12 =STUDENT.ARV(0,975,12) = 2,1788. Prin urmare, b 1 ±tn –2 S b 1 = +1,670 ± 2,1788 * 0,157 = +1,670 ± 0,342 sau + 1,328 ≤ β 1 ≤ +2,012. Astfel, există o probabilitate de 0,95 ca panta populației să se afle între +1,328 și +2,012 (adică, de la 1.328.000 USD la 2.012.000 USD). Deoarece aceste valori sunt mai mari decât zero, există o relație liniară semnificativă statistic între vânzările anuale și suprafața magazinului. Dacă intervalul de încredere ar conține zero, nu ar exista nicio relație între variabile. În plus, intervalul de încredere înseamnă că fiecare crește suprafața magazinului cu 1.000 mp. ft. duce la o creștere a volumului mediu de vânzări între 1.328.000 USD și 2.012.000 USD.

Utilizaret -criterii pentru coeficientul de corelare. a fost introdus coeficientul de corelare r, care este o măsură a relației dintre două variabile numerice. Poate fi folosit pentru a determina dacă există o relație semnificativă statistic între două variabile. Să notăm coeficientul de corelație dintre populațiile ambelor variabile prin simbolul ρ. Ipotezele nule și alternative sunt formulate după cum urmează: H 0: ρ = 0 (fără corelație), H 1: ρ ≠ 0 (există o corelație). Verificarea existenței unei corelații:

Unde r = + , Dacă b 1 > 0, r = – , Dacă b 1 < 0. Тестовая статистика t Are t-distributie cu n – 2 grade de libertate.

În problema despre lanțul de magazine Sunflowers r 2= 0,904, a b 1- +1,670 (vezi Fig. 4). Deoarece b 1> 0, coeficientul de corelație dintre vânzările anuale și dimensiunea magazinului este r= +√0,904 = +0,951. Să testăm ipoteza nulă că nu există o corelație între aceste variabile folosind t-statistici:

La un nivel de semnificație de α = 0,05, ipoteza nulă ar trebui respinsă deoarece t= 10,64 > 2,1788. Astfel, se poate susține că există o relație semnificativă statistic între vânzările anuale și dimensiunea magazinului.

Când se discută inferențe cu privire la panta populației, intervalele de încredere și testele de ipoteză sunt folosite în mod interschimbabil. Totuși, calcularea intervalului de încredere care conține coeficientul de corelație se dovedește a fi mai dificilă, deoarece tipul de distribuție prin eșantionare a statisticii r depinde de coeficientul de corelație adevărat.

Estimarea așteptărilor matematice și predicția valorilor individuale

Această secțiune discută metode de estimare a așteptărilor matematice ale unui răspuns Yși predicții ale valorilor individuale Y pentru valorile date ale variabilei X.

Construirea unui interval de încredere.În exemplul 2 (vezi secțiunea de mai sus Metoda celor mai mici pătrate) ecuația de regresie a făcut posibilă prezicerea valorii variabilei Y X. În problema alegerii unei locații pentru un punct de vânzare cu amănuntul, volumul mediu anual de vânzări într-un magazin cu o suprafață de 4000 mp. picioare a fost egală cu 7,644 milioane de dolari. Cu toate acestea, această estimare a așteptărilor matematice a populației generale este punctual. Pentru estimarea așteptărilor matematice a populației a fost propus conceptul de interval de încredere. În mod similar, putem introduce conceptul interval de încredere pentru așteptarea matematică a răspunsului pentru o anumită valoare variabilă X:

Unde , = b 0 + b 1 X i– valoarea prezisă este variabilă Y la X = X i, S YX– eroarea pătratică medie, n- marime de mostra, Xi- valoarea specificată a variabilei X, µ Y|X = Xi– așteptarea matematică a variabilei Y la X = Xi, SSX =

Analiza formulei (13) arată că lățimea intervalului de încredere depinde de mai mulți factori. La un anumit nivel de semnificație, o creștere a amplitudinii fluctuațiilor în jurul dreptei de regresie, măsurată folosind eroarea pătratică medie, duce la o creștere a lățimii intervalului. Pe de altă parte, așa cum ar fi de așteptat, o creștere a dimensiunii eșantionului este însoțită de o îngustare a intervalului. În plus, lățimea intervalului se modifică în funcție de valori Xi. Dacă valoarea variabilei Y prezis pentru cantităţi X, aproape de valoarea medie , intervalul de încredere se dovedește a fi mai îngust decât atunci când se prezică răspunsul pentru valori departe de medie.

Să presupunem că atunci când alegem locația unui magazin, dorim să construim un interval de încredere de 95% pentru vânzările medii anuale ale tuturor magazinelor a căror suprafață este de 4000 de metri pătrați. picioare:

Prin urmare, volumul mediu anual de vânzări în toate magazinele cu o suprafață de 4.000 mp. picioare, cu 95% probabilitate se află în intervalul de la 6,971 la 8,317 milioane de dolari.

Calculați intervalul de încredere pentru valoarea prezisă. Pe lângă intervalul de încredere pentru așteptarea matematică a răspunsului pentru o valoare dată a variabilei X, este adesea necesar să se cunoască intervalul de încredere pentru valoarea prezisă. Deși formula pentru calcularea unui astfel de interval de încredere este foarte similară cu formula (13), acest interval conține mai degrabă valoarea prezisă decât estimarea parametrului. Interval pentru răspunsul prezis YX = Xi pentru o anumită valoare variabilă Xi determinat de formula:

Să presupunem că, atunci când alegem o locație pentru un punct de vânzare cu amănuntul, dorim să construim un interval de încredere de 95% pentru volumul anual de vânzări estimat pentru un magazin a cărui suprafață este de 4000 de metri pătrați. picioare:

Prin urmare, volumul anual de vânzări estimat pentru un magazin cu o suprafață de 4000 mp. picioare, cu o probabilitate de 95% se află în intervalul de la 5,433 la 9,854 milioane de dolari După cum putem vedea, intervalul de încredere pentru valoarea de răspuns prezisă este mult mai larg decât intervalul de încredere pentru așteptarea sa matematică. Acest lucru se datorează faptului că variabilitatea în prezicerea valorilor individuale este mult mai mare decât în estimarea așteptărilor matematice.

Capcane și probleme etice asociate cu utilizarea regresiei

Dificultăți asociate cu analiza de regresie:

Ignorarea condițiilor de aplicabilitate a metodei celor mai mici pătrate.
Evaluarea eronată a condițiilor de aplicabilitate a metodei celor mai mici pătrate.
Alegerea incorectă a metodelor alternative atunci când sunt încălcate condițiile de aplicabilitate ale metodei celor mai mici pătrate.
Aplicarea analizei de regresie fără cunoaștere profundă a subiectului de cercetare.
Extrapolarea unei regresii dincolo de intervalul variabilei explicative.
Confuzie între relațiile statistice și cauzale.

Utilizarea pe scară largă a foilor de calcul și a software-ului statistic a eliminat problemele de calcul care împiedicaseră utilizarea analizei de regresie. Cu toate acestea, acest lucru a condus la faptul că analiza de regresie a fost utilizată de utilizatori care nu aveau suficiente calificări și cunoștințe. Cum pot utilizatorii să cunoască metodele alternative dacă mulți dintre ei nu au nicio idee despre condițiile de aplicabilitate a metodei celor mai mici pătrate și nu știu cum să le verifice implementarea?

Cercetătorul nu ar trebui să se lase dus de numere strânse - calculând deplasarea, panta și coeficientul de corelație mixt. Are nevoie de cunoștințe mai profunde. Să ilustrăm acest lucru cu un exemplu clasic luat din manuale. Anscombe a arătat că toate cele patru seturi de date prezentate în Fig. 23, au aceiași parametri de regresie (Fig. 24).

Orez. 23. Patru seturi de date artificiale

Orez. 24. Analiza de regresie a patru seturi de date artificiale; am terminat-o cu Pachet de analize(click pe poza pentru a mari imaginea)

Deci, din punctul de vedere al analizei de regresie, toate aceste seturi de date sunt complet identice. Dacă analiza s-ar termina acolo, am pierde o mulțime de informații utile. Acest lucru este evidențiat de diagramele de dispersie (Figura 25) și diagramele reziduale (Figura 26) construite pentru aceste seturi de date.

Orez. 25. Diagrame de dispersie pentru patru seturi de date

Diagramele de dispersie și diagramele reziduale indică faptul că aceste date diferă unele de altele. Singura multime distribuita de-a lungul unei linii drepte este multimea A. Graficul reziduurilor calculate din multimea A nu are nici un model. Acest lucru nu se poate spune despre mulțimile B, C și D. Graficul de dispersie reprezentat pentru setul B arată un model pătratic pronunțat. Această concluzie este confirmată de diagrama reziduală, care are o formă parabolică. Graficul de dispersie și graficul rezidual arată că setul de date B conține un valori abere. În această situație, este necesar să se excludă valorile aberante din setul de date și să se repete analiza. O metodă pentru detectarea și eliminarea valorii aberante din observații se numește analiză de influență. După eliminarea valorii aberante, rezultatul reestimării modelului poate fi complet diferit. Graficul de dispersie reprezentat din datele din setul G ilustrează o situație neobișnuită în care modelul empiric depinde în mod semnificativ de un răspuns individual ( X 8 = 19, Y 8 = 12,5). Astfel de modele de regresie trebuie calculate cu deosebită atenție. Deci, diagramele de dispersie și graficele reziduale sunt un instrument esențial pentru analiza regresiei și ar trebui să fie o parte integrantă a acesteia. Fără ele, analiza de regresie nu este credibilă.

Orez. 26. Grafice reziduale pentru patru seturi de date

Cum să evitați capcanele în analiza de regresie:

Analiza posibilelor relații între variabile XȘi Yîncepe întotdeauna prin desenarea unui grafic de dispersie.
Înainte de a interpreta rezultatele analizei de regresie, verificați condițiile de aplicabilitate a acesteia.
Reprezentați grafic reziduurile față de variabila independentă. Acest lucru va face posibil să se determine cât de bine modelul empiric se potrivește cu rezultatele observaționale și să se detecteze o încălcare a constantei varianței.
Utilizați histograme, diagrame cu tulpini și frunze, diagrame cu case și diagrame de distribuție normală pentru a testa ipoteza unei distribuții normale a erorilor.
Dacă nu sunt îndeplinite condițiile de aplicabilitate a metodei celor mai mici pătrate, utilizați metode alternative (de exemplu, modele de regresie pătratică sau multiplă).
Dacă sunt îndeplinite condițiile de aplicabilitate a metodei celor mai mici pătrate, este necesar să se testeze ipoteza despre semnificația statistică a coeficienților de regresie și să se construiască intervale de încredere care să conțină așteptarea matematică și valoarea răspunsului prezis.
Evitați prezicerea valorilor variabilei dependente în afara intervalului variabilei independente.
Rețineți că relațiile statistice nu sunt întotdeauna cauza-efect. Amintiți-vă că corelația dintre variabile nu înseamnă că există o relație cauză-efect între ele.

Rezumat. După cum se arată în diagrama bloc (Figura 27), nota descrie modelul de regresie liniară simplă, condițiile de aplicabilitate a acestuia și modul de testare a acestor condiții. Considerat t-criteriul de testare a semnificaţiei statistice a pantei de regresie. A fost utilizat un model de regresie pentru a prezice valorile variabilei dependente. Un exemplu este considerat legat de alegerea locației pentru un punct de vânzare cu amănuntul, în care se examinează dependența volumului anual de vânzări de suprafața magazinului. Informațiile obținute vă permit să selectați mai precis o locație pentru un magazin și să preziceți volumul anual de vânzări al acestuia. Următoarele note vor continua discuția despre analiza regresiei și vor analiza, de asemenea, modelele de regresie multiple.

Orez. 27. Observați diagrama structurii

Sunt utilizate materiale din cartea Levin et al. – M.: Williams, 2004. – p. 792–872

Dacă variabila dependentă este categorică, trebuie utilizată regresia logistică.

Linia de regresie este o reflectare grafică a relației dintre fenomene. Puteți construi foarte clar o linie de regresie în Excel.

Pentru a face acest lucru aveți nevoie de:

1.Deschideți Excel

2.Creați coloane de date. În exemplul nostru, vom construi o linie de regresie, sau o relație, între agresivitate și îndoială de sine la elevii de clasa întâi. La experiment au participat 30 de copii, datele sunt prezentate în tabelul Excel:

1 coloană - numărul subiectului

2 coloana - agresivitateîn puncte

3 coloana - neîncredereaîn puncte

3. Apoi trebuie să selectați ambele coloane (fără numele coloanei), faceți clic pe tab introduce , alege loc , și alegeți-l pe primul dintre machetele propuse punct cu markere .

4. Deci avem un șablon pentru linia de regresie - așa-numita - diagramă de dispersie. Pentru a merge la linia de regresie, faceți clic pe figura rezultată și apăsați pe tab constructor, găsiți pe panou layout-uri grafice și alegeți M A ket9 , se mai spune f(x)

5. Deci, avem o linie de regresie. Graficul arată, de asemenea, ecuația și pătratul coeficientului de corelație

6. Mai rămâne doar să adăugați numele graficului și numele axelor. De asemenea, dacă doriți, puteți elimina legenda, puteți reduce numărul de linii orizontale ale grilei (tab aspect , apoi net ). Modificările și setările de bază sunt făcute în filă Aspect

Linia de regresie a fost construită în MS Excel. Acum îl puteți adăuga la textul lucrării.

Arată influența unor valori (independente, independente) asupra variabilei dependente. De exemplu, cum depinde numărul populației active din punct de vedere economic de numărul de întreprinderi, salarii și alți parametri. Sau: cum afectează investițiile străine, prețurile la energie etc. nivelul PIB-ului.

Rezultatul analizei vă permite să evidențiați prioritățile. Și pe baza factorilor principali, anticipați, planificați dezvoltarea zonelor prioritare și luați decizii de management.

Are loc regresia:

liniară (y = a + bx);

· parabolic (y = a + bx + cx 2);

· exponenţial (y = a * exp(bx));

· putere (y = a*x^b);

· hiperbolic (y = b/x + a);

logaritmică (y = b * 1n(x) + a);

· exponențial (y = a * b^x).

Să ne uităm la un exemplu de construire a unui model de regresie în Excel și de interpretare a rezultatelor. Să luăm tipul liniar de regresie.

Sarcină. La 6 întreprinderi au fost analizate salariul mediu lunar și numărul de angajați care au demisionat. Este necesar să se determine dependența numărului de angajați care renunță la salariul mediu.

Modelul de regresie liniară arată astfel:

Y = a 0 + a 1 x 1 +…+a k x k.

Unde a sunt coeficienți de regresie, x sunt variabile de influență, k este numărul de factori.

În exemplul nostru, Y este indicatorul renunțării angajaților. Factorul de influență este salariul (x).

Excel are funcții încorporate care vă pot ajuta să calculați parametrii unui model de regresie liniară. Dar suplimentul „Pachet de analiză” va face acest lucru mai repede.

Activăm un instrument analitic puternic:

1. Faceți clic pe butonul „Office” și accesați fila „Opțiuni Excel”. „Suplimente”.

2. În partea de jos, sub lista drop-down, în câmpul „Manage” va apărea o inscripție „Excel Add-ins” (dacă nu este acolo, faceți clic pe caseta de selectare din dreapta și selectați). Și butonul „Du-te”. Clic.

3. Se deschide o listă de suplimente disponibile. Selectați „Pachet de analiză” și faceți clic pe OK.

Odată activat, suplimentul va fi disponibil în fila Date.

Acum să facem însăși analiza de regresie.

1. Deschideți meniul instrumentului „Analiza datelor”. Selectați „Regresie”.

2. Se va deschide un meniu pentru a selecta valorile de intrare și opțiunile de ieșire (unde se afișează rezultatul). În câmpurile pentru datele inițiale, indicăm intervalul parametrului descris (Y) și factorul care îl influențează (X). Restul poate să nu fie completat.

3. După ce faceți clic pe OK, programul va afișa calculele pe o foaie nouă (puteți selecta un interval de afișat pe foaia curentă sau puteți atribui rezultatul unui nou registru de lucru).

În primul rând, acordăm atenție R-pătratului și coeficienților.

R-pătrat este coeficientul de determinare. În exemplul nostru – 0,755 sau 75,5%. Aceasta înseamnă că parametrii calculați ai modelului explică 75,5% din relația dintre parametrii studiați. Cu cât coeficientul de determinare este mai mare, cu atât modelul este mai bun. Bun - peste 0,8. Proastă – mai mică de 0,5 (o astfel de analiză nu poate fi considerată rezonabilă). În exemplul nostru – „nu e rău”.

Coeficientul 64,1428 arată ce va fi Y dacă toate variabilele din modelul luat în considerare sunt egale cu 0. Adică valoarea parametrului analizat este influențată și de alți factori nedescriși în model.

Coeficientul -0,16285 arată ponderea variabilei X pe Y. Adică salariul mediu lunar în cadrul acestui model afectează numărul de renunțați cu o pondere de -0,16285 (acesta este un grad mic de influență). Semnul „-” indică un impact negativ: cu cât salariul este mai mare, cu atât mai puține persoane renunță. Ceea ce este corect.

Pachetul MS Excel vă permite să faceți cea mai mare parte a muncii foarte rapid atunci când construiți o ecuație de regresie liniară. Este important să înțelegeți cum să interpretați rezultatele obținute. Pentru a construi un model de regresie, trebuie să selectați Tools\Data Analysis\Regression (în Excel 2007 acest mod se află în blocul Date/Data/Data Analysis/Regression). Apoi copiați rezultatele într-un bloc pentru analiză.

Analiza regresiei este una dintre cele mai populare metode de cercetare statistică. Poate fi folosit pentru a stabili gradul de influență al variabilelor independente asupra variabilei dependente. Microsoft Excel are instrumente concepute pentru a efectua acest tip de analiză. Să vedem ce sunt și cum să le folosim.

Conectarea pachetului de analiză

Dar, pentru a utiliza funcția care vă permite să efectuați o analiză de regresie, trebuie mai întâi să activați Pachetul de analiză. Abia atunci instrumentele necesare pentru această procedură vor apărea pe panglica Excel.

Treceți la fila „Fișier”.
Accesați secțiunea „Setări”.
Se deschide fereastra Opțiuni Excel. Accesați subsecțiunea „Suplimente”.
În partea de jos a ferestrei care se deschide, mutați comutatorul din blocul „Management” în poziția „Excel Add-ins”, dacă se află într-o poziție diferită. Faceți clic pe butonul „Go”.
Se deschide o fereastră cu programe de completare Excel disponibile. Bifați caseta de lângă „Pachet de analiză”. Faceți clic pe butonul „OK”.

Acum, când mergem la fila „Date”, pe panglica din blocul instrument „Analiză”, vom vedea un buton nou - „Analiza datelor”.

Tipuri de analiză de regresie

Există mai multe tipuri de regresii:

parabolic;
potolit;
logaritmică;
exponențial;
demonstrativ;
hiperbolic;
regresie liniara.

Vom vorbi mai detaliat despre efectuarea ultimului tip de analiză de regresie în Excel mai târziu.

Regresia liniară în Excel

Mai jos, de exemplu, este un tabel care arată temperatura medie zilnică a aerului exterior și numărul de clienți ai magazinului pentru ziua lucrătoare corespunzătoare. Să aflăm folosind analiza de regresie exact cum condițiile meteorologice sub forma temperaturii aerului pot afecta prezența unei unități de vânzare cu amănuntul.

Ecuația generală de regresie liniară este următoarea: Y = a0 + a1x1 +…+ akhk. În această formulă, Y înseamnă variabila asupra căreia încercăm să studiem influența factorilor. În cazul nostru, acesta este numărul de cumpărători. Valoarea lui x reprezintă diferiții factori care influențează variabila. Parametrii a sunt coeficienții de regresie. Adică ei sunt cei care determină semnificația unui anumit factor. Indicele k denotă numărul total al acestor factori.

Analiza rezultatelor analizei

Rezultatele analizei de regresie sunt afișate sub formă de tabel în locul specificat în setări.

Unul dintre principalii indicatori este R-pătrat. Indică calitatea modelului. În cazul nostru, acest coeficient este de 0,705 sau aproximativ 70,5%. Acesta este un nivel acceptabil de calitate. Dependența mai mică de 0,5 este rea.

Un alt indicator important este situat în celula de la intersecția rândului „Intersecție Y” și coloanei „Coeficienți”. Aceasta indică ce valoare va avea Y și, în cazul nostru, acesta este numărul de cumpărători, cu toți ceilalți factori egali cu zero. În acest tabel, această valoare este 58,04.

Valoarea de la intersecția coloanelor „Variabila X1” și „Coeficienți” arată nivelul de dependență al lui Y față de X. În cazul nostru, acesta este nivelul de dependență a numărului de clienți ai magazinului de temperatură. Un coeficient de 1,31 este considerat un indicator de influență destul de ridicat.

După cum puteți vedea, folosind Microsoft Excel este destul de ușor să creați un tabel de analiză de regresie. Dar numai o persoană instruită poate lucra cu datele de ieșire și poate înțelege esența acestora.

Ne bucurăm că am putut să vă ajutăm să rezolvați problema.

Pune-ți întrebarea în comentarii, descriind esența problemei în detaliu. Specialistii nostri vor incerca sa raspunda cat mai repede posibil.

Te-a ajutat acest articol?

Metoda regresiei liniare ne permite să descriem o linie dreaptă care se potrivește cel mai bine unei serii de perechi ordonate (x, y). Ecuația pentru o linie dreaptă, cunoscută sub numele de ecuație liniară, este dată mai jos:

ŷ - valoarea așteptată a lui y pentru o valoare dată a lui x,

x - variabilă independentă,

a - segment pe axa y pentru o linie dreaptă,

b este panta dreptei.

Figura de mai jos ilustrează acest concept grafic:

Figura de mai sus arată linia descrisă de ecuația ŷ =2+0,5x. Intersecția cu y este punctul în care linia intersectează axa y; în cazul nostru, a = 2. Panta dreptei, b, raportul dintre creșterea dreptei și lungimea dreptei, are o valoare de 0,5. O pantă pozitivă înseamnă că linia se ridică de la stânga la dreapta. Dacă b = 0, linia este orizontală, ceea ce înseamnă că nu există nicio relație între variabilele dependente și independente. Cu alte cuvinte, modificarea valorii lui x nu afectează valoarea lui y.

ŷ și y sunt adesea confundate. Graficul prezintă 6 perechi ordonate de puncte și o dreaptă, conform ecuației date

Această figură arată punctul corespunzător perechii ordonate x = 2 și y = 4. Rețineți că valoarea așteptată a lui y conform dreptei de la X= 2 este ŷ. Putem confirma acest lucru cu următoarea ecuație:

ŷ = 2 + 0,5х =2 +0,5(2) =3.

Valoarea y reprezintă punctul real, iar valoarea ŷ este valoarea așteptată a lui y folosind o ecuație liniară pentru o valoare dată a lui x.

Următorul pas este determinarea ecuației liniare care se potrivește cel mai bine cu mulțimea de perechi ordonate, despre asta am vorbit în articolul anterior, unde am determinat forma ecuației folosind metoda celor mai mici pătrate.

Utilizarea Excel pentru a defini regresia liniară

Pentru a utiliza instrumentul de analiză de regresie încorporat în Excel, trebuie să activați programul de completare Pachet de analize. Îl puteți găsi făcând clic pe filă Fișier -> Opțiuni(2007+), în caseta de dialog care apare Opțiuniexcela accesați fila Suplimente.În câmp Control alege Suplimenteexcelași faceți clic Merge.În fereastra care apare, bifați caseta de lângă Pachet de analize, clic BINE.

În fila Date in grup Analiză va apărea un buton nou Analiza datelor.

Pentru a demonstra cum funcționează suplimentul, să folosim datele dintr-un articol anterior, în care un bărbat și o fată împart o masă în baie. Introduceți datele din exemplul nostru de baie în coloanele A și B ale foii goale.

Accesați fila Date, in grup Analiză clic Analiza datelor.În fereastra care apare Analiza datelor Selectați Regresia așa cum se arată în figură și faceți clic pe OK.

Setați parametrii necesari de regresie în fereastră Regresia, așa cum se arată în imagine:

Clic BINE.În figura de mai jos sunt prezentate rezultatele obținute:

Aceste rezultate sunt în concordanță cu cele pe care le-am obținut făcând propriile calcule în articolul anterior.

Analiza regresiei este o metodă de cercetare statistică care vă permite să arătați dependența unui anumit parametru de una sau mai multe variabile independente. În era pre-computer, utilizarea sa era destul de dificilă, mai ales când era vorba de volume mari de date. Astăzi, după ce ați învățat cum să construiți regresia î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 în sine a fost introdus în matematică de Francis Galton în 1886. Are loc regresia:

liniar;
parabolic;
potolit;
exponențial;
hiperbolic;
demonstrativ;
logaritmică.

Exemplul 1

Să luăm în considerare problema determinării dependenței numărului de membri ai echipei care renunță la salariul mediu la 6 întreprinderi industriale.

Sarcină. La șase întreprinderi s-a analizat salariul mediu lunar și numărul de angajați care au părăsit voluntar. În formă tabelară avem:

Pentru sarcina de a determina dependența numărului de lucrători care renunță la salariul mediu la 6 întreprinderi, modelul de regresie are forma ecuației Y = a0 + a1×1 +…+аkxk, unde хi sunt variabilele de influență, ai sunt coeficienții de regresie, iar k este numărul de factori.

Pentru această sarcină, Y este indicatorul angajaților care au demisionat, iar factorul de influență este salariul, pe care îl notăm cu X.

Folosind capacitățile procesorului de foi de calcul Excel

Analiza de regresie în Excel trebuie să fie precedată de aplicarea funcțiilor încorporate la datele tabelare existente. Cu toate acestea, în aceste scopuri este mai bine să utilizați extensia foarte utilă „Analysis Pack”. Pentru a-l activa aveți nevoie de:

din fila „Fișier” accesați secțiunea „Opțiuni”;
în fereastra care se deschide, selectați linia „Suplimente”;
faceți clic pe butonul „Go” situat mai jos, în dreapta liniei „Management”;
bifați caseta de lângă numele „Pachet de analiză” și confirmați acțiunile făcând clic pe „Ok”.

Dacă totul este făcut corect, butonul necesar va apărea în partea dreaptă a filei „Date”, situată deasupra foii de lucru Excel.

Regresia liniară în Excel

Acum că avem la îndemână toate instrumentele virtuale necesare pentru a efectua calcule econometrice, putem începe să ne rezolvăm problema. Pentru aceasta:

faceți clic pe butonul „Analiza datelor”;
în fereastra care se deschide, faceți clic pe butonul „Regresie”;
în fila care apare, introduceți intervalul de valori pentru Y (numărul de angajați care demisionează) și pentru X (salariile acestora);
Confirmăm acțiunile noastre apăsând butonul „Ok”.

Ca rezultat, programul va completa automat o nouă foaie de calcul cu date de analiză de regresie. Notă! Excel vă permite să setați manual locația pe care o preferați în acest scop. De exemplu, aceasta ar putea fi aceeași foaie în care se află valorile Y și X sau chiar un nou registru de lucru special conceput pentru a stoca astfel de date.

Analiza rezultatelor regresiei pentru R-pătrat

În Excel, datele obținute în timpul procesării datelor din exemplul luat în considerare au forma:

În primul rând, ar trebui să acordați atenție valorii R pătrat. Reprezintă coeficientul de determinare. În acest exemplu, R-pătrat = 0,755 (75,5%), adică parametrii calculați ai modelului explică relația dintre parametrii luați în considerare cu 75,5%. Cu cât valoarea coeficientului de determinare este mai mare, cu atât modelul selectat este mai potrivit pentru o anumită sarcină. Se consideră că se descrie corect situația reală când valoarea R-pătratului este peste 0,8. Dacă R-pătrat este tcr, atunci ipoteza despre nesemnificația termenului liber al ecuației liniare este respinsă.

În problema luată în considerare pentru termenul liber, folosind instrumentele Excel, s-a obținut că t = 169,20903 și p = 2,89E-12, adică avem probabilitate zero ca ipoteza corectă despre nesemnificația termenului liber să fie respinsă. . Pentru coeficientul necunoscutului t=5,79405 și p=0,001158. Cu alte cuvinte, probabilitatea ca ipoteza corectă despre nesemnificația coeficientului pentru o necunoscută să fie respinsă este de 0,12%.

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

Problema fezabilității achiziționării unui bloc de acțiuni

Regresia multiplă în Excel este efectuată folosind același instrument de analiză a datelor. Să luăm în considerare o problemă specifică de aplicare.

Conducerea companiei NNN trebuie să decidă oportunitatea achiziționării unui pachet de 20% din MMM JSC. Costul pachetului (SP) este de 70 de milioane de dolari SUA. Specialiștii NNN au colectat date despre tranzacții similare. S-a decis evaluarea valorii acțiunii în funcție de asemenea parametri, exprimați în milioane de dolari SUA, astfel:

conturi de plătit (VK);
volumul anual al cifrei de afaceri (VO);
conturi de încasat (VD);
costul mijloacelor fixe (COF).

În plus, se utilizează parametrul: restanțele salariale ale întreprinderii (V3 P) în mii de dolari SUA.

Soluție folosind procesorul de foi de calcul Excel

În primul rând, trebuie să creați un tabel de date sursă. Arata cam asa:

apelați fereastra „Analiza datelor”;
selectați secțiunea „Regresie”;
în caseta „Interval de intrare Y”, introduceți intervalul de valori ale variabilelor dependente din coloana G;
Faceți clic pe pictograma cu o săgeată roșie din dreapta ferestrei „Interval de intrare X” și evidențiați intervalul tuturor valorilor din coloanele B, C, D, F de pe foaie.

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

Obțineți o analiză de regresie pentru o anumită problemă.

Studiul rezultatelor și concluziilor

„Colectăm” ecuația de regresie din datele rotunjite prezentate mai sus pe foaia de calcul Excel:

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

Într-o formă matematică mai familiară, poate fi scrisă astfel:

y = 0,103*x1 + 0,541*x2 – 0,031*x3 +0,405*x4 +0,691*x5 – 265,844

Datele pentru MMM JSC sunt prezentate în tabel:

Înlocuindu-le în ecuația de regresie, obținem o cifră de 64,72 milioane de dolari SUA. Aceasta înseamnă că acțiunile MMM JSC nu merită cumpărate, deoarece valoarea lor de 70 de milioane de dolari SUA este destul de umflată.

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

Acum știi ce este regresia. Exemplele Excel discutate mai sus vă vor ajuta să rezolvați probleme practice din domeniul econometriei.