Cum se face o analiză de regresie în excel. Konrad Carlberg. Analiza de regresie în Microsoft Excel

Analiza de regresie și corelație sunt metode de cercetare statistică. Acestea sunt cele mai comune moduri de a arăta dependența unui parametru de una sau mai multe variabile independente.

Mai jos, folosind exemple practice specifice, vom lua în considerare aceste două analize foarte populare în rândul economiștilor. Vom da, de asemenea, un exemplu de obținere a rezultatelor atunci când le combinăm.

Analiza de regresie în Excel

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:

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

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

Î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. Rău – mai puțin de 0,5 (o astfel de analiză poate fi considerată cu greu 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.

Analiza corelației în Excel

Analiza corelației ajută la determinarea dacă există o relație între indicatorii din unul sau două eșantioane. De exemplu, între timpul de funcționare al unei mașini și costul reparațiilor, prețul echipamentului și durata de funcționare, înălțimea și greutatea copiilor etc.

Dacă există o conexiune, atunci o creștere a unui parametru duce la o creștere (corelație pozitivă) sau o scădere (negativă) a celuilalt. Analiza corelației ajută analistul să determine dacă valoarea unui indicator poate fi utilizată pentru a prezice valoarea posibilă a altuia.

Coeficientul de corelație se notează cu r. Variază de la +1 la -1. Clasificarea corelațiilor pentru diferite zone va fi diferită. Când coeficientul este 0, nu există o relație liniară între probe.

Să vedem cum să găsim coeficientul de corelație folosind Excel.

Pentru a găsi coeficienți perechi, este utilizată funcția CORREL.

Obiectiv: Determinați dacă există o relație între timpul de funcționare al unui strung și costul întreținerii acestuia.

Plasați cursorul în orice celulă și apăsați butonul fx.

În categoria „Statistică”, selectați funcția CORREL.
Argumentul „Matrice 1” - primul interval de valori – timpul de funcționare al mașinii: A2:A14.
Argumentul „Matrice 2” - al doilea interval de valori – costul reparației: B2:B14. Faceți clic pe OK.

Pentru a determina tipul de conexiune, trebuie să vă uitați la numărul absolut al coeficientului (fiecare domeniu de activitate are propria sa scară).

Pentru analiza corelației mai multor parametri (mai mult de 2), este mai convenabil să utilizați „Analiza datelor” (suplimentul „Pachet de analiză”). Trebuie să selectați corelația din listă și să desemnați matricea. Toate.

Coeficienții rezultați vor fi afișați în matricea de corelație. Ca aceasta:

Analiza corelației și regresiei

În practică, aceste două tehnici sunt adesea folosite împreună.

Exemplu:

Acum datele analizei de regresie au devenit vizibile.

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.

Necesită un supliment pentru a funcționa Pachet de analize, care trebuie activat în elementul de meniu Service\Suplimente

În Excel 2007, pentru a activa pachetul de analiză, trebuie să faceți clic pe Go to block Opțiuni Excel făcând clic pe butonul din colțul din stânga sus și apoi pe " Opțiuni Excel"în partea de jos a ferestrei:

Pentru a construi un model de regresie, trebuie să selectați elementul Serviciu\Analiza datelor\Regresie. (În Excel 2007, acest mod este în bloc Date/Analiza datelor/Regresia). Va apărea o casetă de dialog pe care trebuie să o completați:

1) Intervalul de intrare Y¾ conține o legătură către celulele care conțin valorile caracteristicii rezultate y. Valorile trebuie aranjate într-o coloană;

2) Intervalul de intrare X¾ conține o legătură către celulele care conțin valori ale factorilor. Valorile trebuie aranjate pe coloane;

3) Semnează Etichete setați dacă primele celule conțin text explicativ (etichete de date);

4) Nivel de fiabilitate¾ este nivelul de încredere, care este considerat implicit de 95%. Dacă nu sunteți mulțumit de această valoare, atunci trebuie să activați acest flag și să introduceți valoarea necesară;

5) Semnează Constanta-zero este inclus dacă este necesar să se construiască o ecuație în care variabila liberă este ;

6) Opțiuni de ieșire determina unde trebuie plasate rezultatele. În mod implicit, modul de compilare Foaie de lucru nouă;

7) Blocare Resturi vă permite să includeți rezultatul reziduurilor și construcția graficelor acestora.

Ca urmare, informațiile sunt afișate conținând toate informațiile necesare și grupate în trei blocuri: Statistici de regresie, Analiza variatiei, Retragerea soldului. Să le aruncăm o privire mai atentă.

1. Statistici de regresie:

multiplu R este determinat de formula ( Coeficientul de corelație Pearson);

R (coeficient de determinare);

Normalizat R-patratul se calculeaza prin formula (utilizat pentru regresia multiplă);

Eroare standard S calculate prin formula ;

Observații ¾ este cantitatea de date n.

2. Analiza variatiei, linie Regresia:

Parametru df egală m(numărul de seturi de factori X);

Parametru SS este determinat de formula ;

Parametru DOMNIȘOARĂ este determinat de formula ;

Statistici F este determinat de formula ;

Semnificaţie F. Dacă numărul rezultat depășește , atunci ipoteza este acceptată (nu există o relație liniară), în caz contrar ipoteza este acceptată (există o relație liniară).

3. Analiza variatiei, linie Rest:

Parametru df egal cu ;

Parametru SS este determinat de formula ;

Parametru DOMNIȘOARĂ este determinat de formula.

4. Analiza variatiei, linie Total conţine suma primelor două coloane.

5. Analiza variatiei, linie Intersecția în Y conţine coeficientul, eroarea standard şi t-statistici.

P-valoarea ¾ este valoarea nivelurilor de semnificație corespunzătoare celei calculate t-statisticieni. Determinat de funcția STUDIIST( t-statistici; ). Dacă P-valoarea depășește , atunci variabila corespunzătoare este nesemnificativă statistic și poate fi exclusă din model.

De jos 95%Și Top 95%¾ sunt limitele inferioare și superioare ale intervalelor de încredere de 95 la sută pentru coeficienții ecuației teoretice de regresie liniară. Dacă valoarea probabilității de încredere din blocul de date de intrare a fost lăsată la valoarea implicită, atunci ultimele două coloane le vor duplica pe cele anterioare. Dacă utilizatorul a introdus o valoare de încredere, ultimele două coloane conțin limitele inferioare și superioare pentru nivelul de încredere specificat.

6. Analiza variatiei, liniile conțin valorile coeficienților, erori standard, t-statistician, P-valori și intervale de încredere pentru corespunzătoare.

7. Blocare Retragerea soldului conţine valorile prezise y(în notația noastră aceasta este ) și reziduuri .

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:

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

x este o variabilă independentă,

a este un 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 liniei 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 tipul de ecuație prin .

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 nou buton Analiza datelor.

Pentru a demonstra funcționarea suplimentului, vom folosi date în care un tip și o fată împart o masă în baie. Introduceți datele din exemplul nostru de cadă î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 .

Construcția regresiei liniare, evaluarea parametrilor acesteia și a semnificației acestora pot fi realizate mult mai rapid atunci când se utilizează pachetul de analiză Excel (Regression). Să luăm în considerare interpretarea rezultatelor obținute în cazul general ( k variabile explicative) conform exemplului 3.6.

In masa statistici de regresie se dau urmatoarele valori:

Multiplu R – coeficient de corelație multiplă;

R- pătrat- coeficient de determinare R 2 ;

Normalizat R - pătrat- ajustat R 2 ajustat pentru numărul de grade de libertate;

Eroare standard– eroare standard de regresie S;

Observatii - numărul de observații n.

In masa Analiza variatiei sunt date:

1. Coloana df - număr de grade de libertate egal cu

pentru sfoară Regresia df = k;

pentru sfoară Restdf = n – k – 1;

pentru sfoară Totaldf = n– 1.

2. Coloana SS – suma abaterilor pătrate egală cu

pentru sfoară Regresia ;

pentru sfoară Rest ;

pentru sfoară Total .

3. Coloana DOMNIȘOARĂ varianțe determinate de formulă DOMNIȘOARĂ = SS/df:

pentru sfoară Regresia– dispersia factorilor;

pentru sfoară Rest– variația reziduală.

4. Coloana F – valoarea calculată F-criteriul calculat folosind formula

F = DOMNIȘOARĂ(regresie)/ DOMNIȘOARĂ(rest).

5. Coloana Semnificaţie F – valoarea nivelului de semnificație corespunzătoare celei calculate F-statistici .

Semnificaţie F= FDIST( F- statistici, df(regresie), df(rest)).

Dacă semnificație F < стандартного уровня значимости, то R 2 este semnificativ statistic.

	Cote	Eroare standard	t-statistici	Valoarea P	De jos 95%	Top 95%
Y	65,92	11,74	5,61	0,00080	38,16	93,68
X	0,107	0,014	7,32	0,00016	0,0728	0,142

Acest tabel arată:

1. Cote– valorile coeficientului A, b.

2. Eroare standard– erori standard ale coeficienților de regresie S a, Sb.

3. t- statistici– valori calculate t -criterii calculate prin formula:

t-statistic = Coeficienți/Eroare standard.

4.R-valoare (semnificație t) este valoarea nivelului de semnificație corespunzătoare valorii calculate t- statistici.

R-valoare = STUDIDIST(t-statistici, df(rest)).

Dacă R-sens< стандартного уровня значимости, то соответствующий коэффициент статистически значим.

5. 95% de jos și 95% de sus– limitele inferioare și superioare ale intervalelor de încredere de 95% pentru coeficienții ecuației teoretice de regresie liniară.

RETRAGERE A RESTULUI
Observare	A prezis y	Reziduuri e
	72,70	-29,70
	82,91	-20,91
	94,53	-4,53
	105,72	5,27
	117,56	12,44
	129,70	19,29
	144,22	20,77
	166,49	24,50
	268,13	-27,13

In masa RETRAGERE A RESTULUI indicat:

în coloană Observare– numărul de observație;

în coloană prezis y – valorile calculate ale variabilei dependente;

în coloană Resturi e – diferența dintre valorile observate și calculate ale variabilei dependente.

Exemplul 3.6. Există date (unități convenționale) despre costurile alimentelor yși venitul pe cap de locuitor X pentru nouă grupuri de familii:

X
y

Folosind rezultatele pachetului de analiză Excel (Regresie), vom analiza dependența costurilor alimentare de venitul pe cap de locuitor.

Rezultatele analizei de regresie sunt de obicei scrise sub forma:

unde erorile standard ale coeficienților de regresie sunt indicate în paranteze.

Coeficienți de regresie A = 65,92 și b= 0,107. Direcția de comunicare între yȘi X determină semnul coeficientului de regresie b= 0,107, adică legătura este directă și pozitivă. Coeficient b= 0,107 arată că cu o creștere a venitului pe cap de locuitor cu 1 convențional. unitati costurile cu alimentele cresc cu 0,107 unităţi convenţionale. unitati

Să evaluăm semnificația coeficienților modelului rezultat. Semnificația coeficienților ( a, b) este verificat de t-Test:

Valoarea P ( A) = 0,00080 < 0,01 < 0,05

Valoarea P ( b) = 0,00016 < 0,01 < 0,05,

prin urmare, coeficienții ( a, b) sunt semnificative la nivelul de 1% și cu atât mai mult la nivelul de semnificație de 5%. Astfel, coeficienții de regresie sunt semnificativi și modelul este adecvat datelor originale.

Rezultatele estimării regresiei sunt compatibile nu numai cu valorile obținute ale coeficienților de regresie, ci și cu un anumit set al acestora (interval de încredere). Cu o probabilitate de 95%, intervalele de încredere pentru coeficienți sunt (38,16 – 93,68) pentru Ași (0,0728 – 0,142) pentru b.

Calitatea modelului este evaluată prin coeficientul de determinare R 2 .

Magnitudinea R 2 = 0,884 înseamnă că factorul venit pe cap de locuitor poate explica 88,4% din variația (împrăștierea) cheltuielilor cu alimentele.

Semnificaţie R 2 este verificat de F- test: semnificație F = 0,00016 < 0,01 < 0,05, следовательно, R 2 este semnificativ la nivelul de 1% și cu atât mai mult la nivelul de semnificație de 5%.

În cazul regresiei liniare pe perechi, coeficientul de corelație poate fi definit ca . Valoarea obținută a coeficientului de corelație indică faptul că relația dintre costurile alimentare și venitul pe cap de locuitor este foarte strânsă.

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 „Administrare” în poziția „Suplimente Excel”, 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 acelorași 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?

ŷ - 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:

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

ŷ = 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.

Utilizarea Excel pentru a defini regresia liniară

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

Pentru a demonstra cum funcționează suplimentul, să folosim datele dintr-un articol anterior, în care un tip și o fată împart o masă în baie. Introduceți datele din exemplul nostru de cadă î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 renunțat 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 blocului de acțiuni în funcție de astfel de 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țelor 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.