Regresia în Excel: exemple de ecuații. regresie liniară

Analiza de regresie - o metodă de studiu statistic pentru a arăta dependența unui parametru al uneia sau mai multor variabile independente. În epoca pre-calculator, utilizarea sa a fost destul de dificil, mai ales atunci când a ajuns la volume mari de date. Astăzi, a învăța cum să construiască o regresie în Excel, puteți rezolva problemele statistice complexe în doar câteva minute. Mai jos sunt exemple specifice de economie.

tipuri de regresie

Acest concept a fost introdus la matematică de Francis Galton în 1886. Regresie este:

• liniar;
• parabolic;
• putere;
• exponențială;
• hiperbolică;
• exponențială;
• logaritmică.

EXEMPLUL 1

Luați în considerare problema de a determina dependența numărului de demisii ale membrilor personalului din salariul mediu din cele 6 întreprinderi industriale.

Sarcină. Șase companii au analizat salariul mediu lunar și numărul de angajați care au renuntat in mod voluntar. În formă de tabel, avem:

	A	B	C
1	X	Numărul de demisii	salariu
2		y	30000 ruble
3	1	60	35000 ruble
4	2	35	40000 ruble
5	3	20	45000 ruble
6	4	20	50.000 ruble
7	5	15	55000 ruble
8	6	15	60000 ruble

Pentru problema determinării dependenței muncitorilor separații suma din salariul mediu pentru 6 întreprinderi model de regresie are forma ecuației Y = a ₀ + ₁ x ₁ + ... + _k x _k, unde x _i - variabile care influențează, o _i - coeficienții de regresie, ak - număr de factori.

Y pentru o anumită sarcină - este un indicator la foc un angajat, un factor care contribuie - salariul, care este notat cu X.

Valorificând puterea de „Excel“ foaie de calcul

Analiza de regresie în Excel trebuie să fie precedată de o cerere la datele de tabelă existente funcții predefinite. Cu toate acestea, în acest scop, este mai bine să utilizați un foarte util add-in „analiza de pachete“. Pentru al activa, trebuie să:

• cu tab-ul „File“ du-te la „Settings“;
• în fereastra care se deschide, selectați „Add-ons“;
• click pe butonul „Go“, situat în partea din dreapta jos a liniei de „gestionare“;
• pune un semn de selectare de lângă „Analiza TOOLPAK“ și confirmați acțiunea prin apăsarea butonului „OK“.

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

Linear Regression în Excel

Acum, că aveți la îndemână toate instrumentele virtuale necesare pentru calcule econometrice, putem începe să abordeze problema noastră. Pentru a face acest lucru:

• buton este apasat pe „analiza datelor“;
• click pe butonul „regresie“ în fereastra deschisă;
• o filă care pare să introducă o serie de valori Y (numărul de lucrători separații) și X (salariu);
• reafirmăm acțiunile lor prin apăsarea butonului «OK».

Ca urmare, programul va completa automat noua foaie de calcul tabelar analiza de regresie a datelor. Acorde o atenție! În Excel, există o oportunitate de a stabili locul pe care o preferați pentru acest scop. De exemplu, poate fi aceeași foaie, unde valorile Y și X, sau chiar o carte nouă, proiectată special pentru stocarea acestor date.

Rezultatele analizei de regresie pentru R-pătrat

Datele obținute în Excel datele exemplu, considerate au forma:

În primul rând, ar trebui să acorde o atenție la valoarea R-pătrat. Reprezintă 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%. Cât este mai mare valoarea coeficientului de determinare, modelul selectat este considerat a fi mai util pentru anumite sarcini. Se crede că descrie corect situația reală la valoarea R pătrat peste 0,8. În cazul în care R-pătrat <0,5, apoi o analiză de regresie în Excel nu poate fi considerată rezonabilă.

analiza raportului

Numărul 64.1428 arată ce va fi valoarea Y, în cazul în care toate variabilele xi în modelul nostru va fi resetat. Cu alte cuvinte, se poate argumenta că valoarea parametrului analizat este influențat și de alți factori decât cele descrise în modelul specific.

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ă

Sub acest termen se referă la ecuația comunicării cu mai multe variabile independente de forma:

y = f (x ₁ + x ₂ + ... x _m) + ε, unde y - este un scor caracteristică (variabila dependentă), și x _1, x _2, ... x _m - sunt factori de semne (variabile independente).

estimarea parametrilor

Pentru regresie multiplă (MR) se realizează cu ajutorul unei metode mai mici pătrate (LSM). Pentru ecuații liniare de forma Y = a + b ₁ x _{1 + ... + b m x m +} ε construirea unui sistem de ecuații normale (cm. Mai jos)

Pentru a înțelege principiul metodei, considerăm cazul de doi factori. Apoi ne-am situația descrisă de formula

Prin urmare, obținem:

în cazul în care σ - este variația caracteristicii respective, reflectate în index.

MNC se aplică ecuația MR la standartiziruemom scară. În acest caz, obținem ecuația:

în care t _y, t _{x 1, ...} t _xm - standartiziruemye variabile pentru care valorile medii sunt 0; p _i - coeficienții de regresie standardizați și deviația standard - 1.

Vă rugăm să rețineți că toate p _i în acest caz, definit ca normalizate și tsentraliziruemye, prin urmare, o comparație între o considerată validă și acceptabilă. În plus, este acceptat pentru a efectua screening-ul de factori, îndepărtând cele care au cele mai mici valori ale βi.

Problema cu ajutorul ecuației de regresie liniară

Să presupunem că aveți un tabel al dinamicii prețului unui anumit produs N pentru ultimele 8 luni. Este necesar să se decidă dacă achiziționarea partidului său la prețul de 1850 de ruble. / T.

	A	B	C
1	luna	Numele lunii	Preț N
2	1	ianuarie	1750 ruble per tonă
3	2	februarie	1755 ruble per tonă
4	3	martie	1767 ruble per tonă
5	4	aprilie	1760 ruble per tonă
6	5	mai	1770 ruble per tonă
7	6	iunie	1790 ruble per tonă
8	7	iulie	1810 ruble per tonă
9	8	august	1840 ruble per tonă

Pentru a rezolva această problemă în procesorul tabelară „Excel“ necesar pentru a utiliza deja cunoscut, de exemplu, instrument de „Analiza datelor“ prezentate mai sus. Apoi, alegeți secțiunea „Regression“ și setaț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). Confirmăm acțiunea făcând clic pe «OK». Într-o nouă foaie de lucru (în cazul în care acest lucru este indicat), 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ă (EQ) 3 pentru problema poate fi scrisă ca:

Prețul mărfurilor N = 11714 * 1727.54 luni + număr.

sau în notație algebrică

y = 11,714 x + 1727,54

Analiza rezultatelor

Pentru a decide dacă a primit ecuația de regresie liniară în mod adecvat cu ajutorul coeficienților de corelație multiple (CMC) și determinare, precum și testul t-testul lui Fisher. În tabelul de regresie „Excel“ cu rezultatele pe care le acționează sub nume multiple R, R-Square, F-t-statistici și statistici, respectiv.

KMC R permite estimarea relației probabilistic apropiere între variabile independente și 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 nu este cunoscută.

Pătratul coeficientului de determinare ^R2 (RI) este o caracteristică numerică a proporției de dispersie totală și prezintă o împrăștiere a porțiunii de date experimentale, adică, valori ale variabilei dependente corespunzătoare unei ecuații de regresie liniară. În această problemă, această valoare este de 84,8%, punct de topire. E. Statistică cu un grad ridicat de precizie obținute sunt descrise SD.

F-statistici, cunoscute și ca criteriu Fisher utilizat pentru a evalua semnificația dependenței liniare sau disproving ipoteză care confirmă existența.

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, ipoteza unei insignificance ecuație liniară a termenului liber 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 coeficient necunoscut la t = 5,79405, ș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ă obținut în mod adecvat.

Problema a oportunității de cumpărare de acțiuni

regresie multiplă a fost realizată în Excel folosind același instrument „Analiza datelor“. Luați în considerare aplicația specifică.

Ghid de companie «NNN» trebuie să decidă dacă să achiziționeze 20% din acțiunile SA «MMM». Prețul pachetului (SP) este de 70 de milioane de dolari SUA. Specialiștii de «NNN» colectat date cu privire la tranzacții similare. Sa decis să evalueze valoarea acțiunilor pe astfel de parametri, exprimate în milioane de dolari SUA, cum ar fi:

• datorii (VK);
• Volumul anual cifra de afaceri (VO);
• creanțe (VD);
• Valoarea mijloacelor fixe (SOF).

În plus, utilizarea datoriile salariale ale întreprinderilor (V3 U) în mii de dolari SUA.

Procesorul de masă de decizie mijloace Excel

În primul rând, trebuie să creați un tabel de date de intrare. Acesta este după cum urmează:

următor:

• caseta de apel „analiza datelor“;
• selectat secțiunea „Regression“;
• fereastra „Input range Y» administrate valori variabile dependente de interval din coloana G;
• clic pe pictograma cu o săgeată roșie spre dreapta ferestrei „Input interval X» și izolate pe o gamă foaie de toate valorile din coloana B, C, D, F.

Marcați punctul „Noua foaie de lucru“ și faceți clic pe „OK“.

Ia-o analiză de regresie pentru această sarcină.

Rezultatele studiului și concluziile

„Collect“ rotunjit din datele prezentate mai sus în ecuația de regresie Excel procesor tabel foaie:

SD = 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265844.

În forma matematică mai obișnuită poate fi scris ca:

y = 0,103 * x1 + 0541 * x2 - 0,031 * x3 + 0,405 * x4 + 0,691 * x5 - 265844

Date pentru «MMM» SA prezentate în tabelul de mai jos:

FPS, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102.5	535.5	45.2	41.5	21.55	64.72

Înlocuind-le în ecuația de regresie, a obținut o cifră de 64,72 milioane de dolari SUA. Acest lucru înseamnă că acțiunile SA «MMM» nu ar trebui să cumpere, deoarece costul lor este destul de prea scumpe la 70 de milioane de dolari SUA.

După cum puteți vedea, utilizarea de calcul tabelar „Excel“ și ecuația de regresie a permis să ia o decizie informată cu privire la oportunitatea de tranzacție destul de specifice.

Acum știi ce o regresie. Exemplele Excel, discutate mai sus, vă va ajuta în rezolvarea problemelor practice ale econometrie.