Regres në Excel: shembujt ekuacion. linear regres

Analiza e regresionit - një metodë statistikore studim për të treguar varësinë e një parametri të një ose më shumë të pavarur variablave. Në epokën para-kompjuter, përdorimi i saj ka qenë mjaft e vështirë, sidomos kur bëhej fjalë për vëllime të mëdha të të dhënave. Sot, të mësuarit se si për të ndërtuar një regres në Excel, ju mund të zgjidhin problemet komplekse statistikore në vetëm disa minuta. Më poshtë janë shembuj të veçantë të ekonomisë.

llojet e regresionit

Ky koncept u prezantua në matematikë nga Francis Galton në 1886. Regresionit është:

• linear;
• parabolik;
• të energjisë;
• eksponenciale;
• ekzagjeruar;
• eksponenciale;
• logaritmike.

SHEMBULL 1

Konsiderojnë problemin e përcaktimit varësinë e numrit të dorëheqjeve të anëtarëve të stafit të pagës mesatare në 6 ndërmarrjet industriale.

Task. Gjashtë kompani kanë analizuar paga mesatare mujore dhe numrin e të punësuarve që e lë vullnetarisht. Në formë tabelare kemi:

	A	B	C
1	X	Numri i dorëheqjeve	rrogë
2		y	30.000 rubla
3	1	60	35000 rubla
4	2	35	40000 rubla
5	3	20	45000 rubla
6	4	20	50.000 rubla
7	5	15	55000 rubla
8	6	15	60000 rubla

Për problemin e përcaktimit varësinë e punëtorëve shumës ndarjet nga paga mesatare për 6 ndërmarrjet e modelit të regresionit ka formën e ekuacionit Y = a ₀ + a ₁ x ₁ + ... + a _k x _k, ku x _i - variabla që ndikojnë, një _i - koeficientët e regresionit, ak - numër faktorësh.

Y për një detyrë të caktuar - kjo është një tregues për të zjarrit një punonjës, një faktor kontribues - paga, e cila është pėrcaktuara nga X.

Përdorur fuqinë e spreadsheet "Excel"

Analiza e regresionit në Excel duhet të paraprihet nga një aplikim të dhënave ekzistuese tryezë ndërtuar në funksionet. Megjithatë, për këto qëllime është më mirë të përdorni një add-në "analizë pako" shumë të dobishme. Për të mundësuar këtë, ju duhet të:

• me "File" kartela të shkojnë në "Settings";
• në dritaren që hapet, zgjidhni "Add-ons ';
• klikoni në butonin "shkoni", e vendosur në të djathtë fund të linjës "menaxhimit";
• vënë një shenjë tjetër për "Analiza ToolPak" dhe konfirmoni veprimin tuaj duke shtypur "OK".

Nëse bëhet si duhet, në anën e djathtë të tab "të dhënave", e vendosur mbi fletën e punës "Excel", tregon butonin e dëshiruar.

Regresionit linear në Excel

Tani që ju keni në dorë të gjitha mjetet e nevojshme virtuale për llogaritje ekonometrike, ne mund të fillojnë për të adresuar problemin tonë. Për ta bërë këtë:

• klikohet butoni në "Analiza e të dhënave";
• klikoni mbi butonin "regres" në dritare të hapur;
• një tab që duket për të futur një gamë e vlerave Y (numri i ndarjeve punëtorëve) dhe X (paga e tyre);
• riafirmojnë veprimet e tyre duke shtypur butonin «Ok».

Si rezultat i kësaj, programi automatikisht do të mbushë fletë spreadsheet analizë të re të dhënat e regresionit. I kushtoj vëmendje! Në Excel, ekziston një mundësi për të vendosur vendin që ju preferoni për këtë qëllim. Për shembull, ajo mund të jetë e njëjta fletë, ku vlerat Y dhe X, ose edhe një libër i ri, i projektuar posaçërisht për ruajtjen e të dhënave të tilla.

Rezultatet Analiza e regresionit për R-katror

Të dhënat Excel fituara në shembullin e të dhënave konsiderohet të ketë formën:

Para së gjithash, ne duhet të i kushtoj vëmendje për vlerën e R në katror. Ajo përfaqëson koeficientin e përcaktimit. Në këtë shembull, R-square = 0,755 (75.5%), m. E. Parametrat e llogaritura të modelit të shpjeguar marrëdhënien midis parametrave të konsiderohen me 75.5%. Sa më e lartë se vlera e koeficientit të vendosmërisë, modeli i zgjedhur është konsideruar të jetë më e dobishme për detyra të veçanta. Besohet për të përshkruar saktë situatën reale në vlerën R-katror sipër 0.8. Nëse R-katror <0.5, atëherë një analizë regresioni në Excel nuk mund të konsiderohet e arsyeshme.

Analiza e raportit

Numri 64,1428 tregon se çfarë do të jetë në vlerën e Y, nëse të gjithë variablat XI në modelin tonë do të rishkruhet. Me fjalë të tjera, mund të thuhet se vlera e parametrit analizuar është e ndikuar nga faktorë të tjerë se sa ato të përshkruara në modelin specifik.

Faktor tjetër -,16285 vendosur në B18 qelizë, tregon ndikimin e rëndësishme e ndryshueshme X në Y. Kjo do të thotë se paga mesatare e punonjësve brenda modelit ndikon në numrin e dorëheqjeve nga pesha e -0.16285, t. E. Shkalla e ndikimit të tij në të gjitha të vogla. Shenja "-" tregon se koeficienti është negative. Është e qartë, që ne të gjithë e dimë se sa më shumë paga në ndërmarrje, aq më pak njerëz kanë shprehur dëshirën për të ndërprerë kontratën e punës ose të shkarkohet.

regresionit të shumëfishtë

Sipas këtij term i referohet ekuacionit komunikimit me disa variablave të pavarura të formës:

y = f (x ₁ + x ₂ + x ... _m) + ε, ku y - është një rezultat tipar (variabli i varur), dhe x _1, x _2, ... x _m - janë shenja faktorë (ndryshore të pavarura).

vlerësimi parametër

Për regresionit të shumëfishtë (MR) është kryer duke përdorur një metodë më pak shesheve (LSM). Për ekuacioneve lineare të formës Y = a + b ₁ x _{1 + ... + b m x m +} ε ndërtimit të një sistemi ekuacionesh normale (cm. Poshtë)

Për të kuptuar parimin e metodës, ne e konsiderojmë rastin dy-faktor. Pastaj ne kemi situata e përshkruar nga formula

Për këtë arsye, ne marrim:

ku σ - është varianca e funksion përkatës, pasqyrohet në indeksin.

MNC është i zbatueshëm për ekuacion MR për standartiziruemom shkallë. Në këtë rast, ne kemi marrë ekuacionin:

ku t _y, t _{x 1, ...} t _XM - standartiziruemye variablave për të cilat vlerat mesatare janë 0; ß _I - koeficienteve standardizuara regresionit dhe deviacionin standard - 1.

Ju lutem vini re se të gjitha ß _I në këtë rast është përcaktuar si normalizuar dhe tsentraliziruemye, prandaj një krahasim në mes të një konsiderohen të vlefshme dhe e pranueshme. Përveç kësaj, ajo është pranuar për të kryer monitorimin e faktorëve, discarding ato që kanë vlerat më të ulëta të βi.

Problemi me përdorimin linear ekuacionin e regresionit

Supozoni se ju keni një tabelë të dinamikës së çmimit të një produkti N caktuar për 8 muajt e fundit. Është e nevojshme për të vendosur nëse blerjen e partisë së tij në çmimin e 1850 rubla. / T.

	A	B	C
1	muaji	emri i muajit	Çmimi N
2	1	janar	1750 rubla për ton
3	2	shkurt	1755 rubla për ton
4	3	mars	1767 rubla për ton
5	4	prill	1760 rubla për ton
6	5	maj	1770 rubla për ton
7	6	qershor	1790 rubla për ton
8	7	korrik	1810 rubla për ton
9	8	gusht	1840 rubla për ton

Për të zgjidhur këtë problem në procesor tabelare "Excel" e nevojshme për të përdorur e njohur tashmë për shembull mjet "Analiza e të dhënave" të paraqitur më sipër. Tjetra, zgjidhni "regres" seksionin dhe parametrat e vendosur. Ne duhet të kujtojmë se në "varg Input Y» duhet të futet në një varg të vlerave të variablit të varur (në këtë rast e çmimit të mallrave në muajt e veçanta të vitit) dhe në "Input interval X» - për të pavarur (muaji). Ne konfirmojmë veprimin duke klikuar «Ok». Në një pune të re (nëse tregohet kështu), ne marrim të dhënat për të regresionit.

Ne jemi duke ndërtuar mbi to ekuacion linear të formës y = sëpatë + b, ku si parametra të A dhe B janë koeficientët nga numri i linjës të muajit dhe emri i koeficienteve dhe «Y-kryqëzimin" vijës së fletës me rezultatet e analizës së regresionit. Kështu, ekuacioni linear regresionit (EQ) 3 për problemin mund të shkruhet si:

Çmimi i mallrave N = 11714 * 1727,54 muaj Numri +.

ose në simbol algjebrike

y = 11.714 x + 1727,54

Analiza e rezultateve

Për të vendosur nëse ka marrë në mënyrë adekuate linear ekuacionin e regresionit duke përdorur koeficientët e shumta korrelacion (CMC) dhe vendosmëri, si dhe provë dhe T-testin Fisher. Në tabelën regresionit e "Excel" me rezultatet që ata veprojnë sipas emrave të shumta R, R-katrore, F-t-statistikat dhe statistikave, respektivisht.

KMC R mundëson për të vlerësuar marrëdhëniet afërsinë probabilistic ndërmjet variablave të pavarur dhe të varur. vlera e tij e lartë tregon një lidhje të fortë të mjaftueshme në mes të ndryshueshme "Numri i muajit" dhe "N çmimi i produktit në rubla në 1 ton." Megjithatë, natyra e kësaj marrëdhënieje është i panjohur.

Katrore e koeficienti i përcaktimit ^R2 (RI) është karakteristikë numerike të përqindjes së shpërndaj totale dhe tregon një shpërndaj e pjesës eksperimental të të dhënave, dmth, vlerat e variablit të varur korrespondon ekuacionit linear regresionit. Në këtë problem, kjo vlerë është 84.8%, mp. E. Statistikat me një shkallë të lartë të saktësisë të marra janë përshkruar SD.

F-statistics, i njohur gjithashtu si kriter Fisher përdorur për të vlerësuar rëndësinë e varësisë lineare ose hedhur poshtë hipotezën që konfirmon ekzistencën e tij.

Vlera e t-statistike (testit t nxënësit) ndihmon të vlerësojë rëndësinë e koeficientit në çdo anëtar të lirë të panjohur varësisë lineare. Nëse vlera e t-testit> t _CR, hipoteza e një parëndësi linear Ekuacioni i mandatit të lirë është refuzuar.

Në këtë problem për një mandat të lirë nëpërmjet instrumenteve "Excel", u konstatua se t = 169,20903, dhe p = 2,89E-12, t. E. kanë një probabilitet zero se besimtarët do të refuzohet hipotezën e parëndësishme të afatit të lirë. Për koeficient të panjohur në t = 5,79405, dhe p = 0,001158. Me fjalë të tjera, probabiliteti që një hipotezë refuzuar saktë do të parëndësi e koeficientit për të panjohurën, është 0.12%.

Kështu, mund të thuhet se merret linear ekuacionin e regresionit në mënyrë adekuate.

Problemi i arsyeshmërinë e blerjes së aksioneve

regresionit të shumëfishtë është kryer në Excel duke përdorur të njëjtën mjet "analizën e të dhënave". Konsideroni aplikim të veçantë.

Kompania Guide «NNN» duhet të vendosë nëse për të blerë 20% të aksioneve të SHA «MMM». Çmimi Paketa (PS) është 70 milionë dollarë amerikanë. Specialistët e «NNN» mbledhur të dhëna mbi transaksione të ngjashme. Ai ishte vendosur për të vlerësuar vlerën e aksioneve në parametra të tillë, e shprehur në milionë dollarë amerikanë, të tilla si:

• pagueshmet (VK);
• vëllimi qarkullimi vjetor (VO);
• arkëtueshmet (VD);
• Vlera e aseteve fikse (SOF).

Përveç kësaj, përdorni borxhet e pagave të ndërmarrjeve (V3 U) në mijëra dollarë amerikanë.

Procesori Tabela vendim Excel do të thotë

Së pari ju duhet të krijoni një tabelë të të dhënave të dhëna. Ajo është si vijon:

Next:

• thirrje box "Analiza e të dhënave";
• perzgjedhur seksion "Regres";
• dritarja "hyrës interval Y» administrohet varg vlerat e varura variabël nga kolonë G;
• klikoni në ikonën me një shigjetë të kuqe në të djathtë të dritares "Input interval X» dhe i izoluar në një varg fletë e të gjitha vlerave të kolonën B, C, D, F.

Mark pika "pune e Re" dhe klikoni "Ok".

Merrni një analizë të regresionit për këtë detyrë.

Rezultatet e studimit dhe konkluzionet

"Mblidhni" rrumbullakosura nga të dhënat e paraqitura më lart në fletë tryezë Excel processor regresionit ekuacionit:

SD = 0.103 * SOF + 0541 * VO - 0031 * VK + 0405 * VD + 0691 * VZP - 265.844.

Në formën më të zakonshme matematikore ajo mund të shkruhet si:

y = 0103 * X1 + 0541 * X2 - 0031 * X3 + 0405 * X4 + 0691 * X5 - 265.844

Të dhënat për «MMM» SHA paraqitura në tabelën e mëposhtme:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102.5	535,5	45.2	41.5	21.55	64,72

Zëvendësuar ato në ekuacionin e regresionit, mori një shifër prej 64.72 milionë dollarë amerikanë. Kjo do të thotë se aksionet e Sh.A. «MMM» nuk duhet të blejnë, për shkak se kostoja e tyre është mbiçmuara mjaft në 70 milionë dollarë amerikanë.

Siç mund ta shikoni, përdorimi i spreadsheet "Excel" dhe ekuacioni i regresit lejohet të bëjë një vendim të informuar në lidhje me arsyeshmërinë transaksion mjaft të veçantë.

Tani ju e dini se çfarë është një regres. Shembuj në Excel, të diskutuara më sipër, do t'ju ndihmojnë në zgjidhjen e problemeve praktike të Ekonometria.