Rigressjoni fl-Excel: eżempji ekwazzjoni. rigressjoni lineari

analiżi ta 'rigressjoni - metodu studju statistiku li juru d-dipendenza ta' parametru ta 'varjabbli waħda jew aktar indipendenti. Fl-era pre-kompjuter, l-użu tiegħu kien pjuttost diffiċli, speċjalment meta ġie biex volumi kbar ta 'data. Illum, it-tagħlim kif jibnu rigressjoni fl-Excel, inti tista 'ssolvi problemi ta' statistika kumplessi ftit minuti. Hawn taħt huma eżempji speċifiċi ta 'l-ekonomija.

tipi ta 'rigressjoni

Dan il-kunċett ġie introdott għall-matematika minn Francis Galton fl-1886. Rigressjoni huwa:

• lineari;
• parabolika;
• enerġija;
• esponenzjali;
• hyperbolic;
• esponenzjali;
• logaritmika.

EŻEMPJU 1

Ikkunsidra l-problema tad-determinazzjoni tal dipendenza tan-numru ta 'riżenji ta' membri tal-persunal tal-paga medja fl-intrapriżi industrijali 6.

Kompitu. Sitt kumpaniji analizzaw is-salarju medju fix-xahar u n-numru ta 'impjegati li nieqaf volontarjament. F'forma ta 'tabella għandna:

	A	B	C
1	X	Numru ta 'riżenji	salarju
2		y	30000 rublu
3	1	60	35000 rublu
4	2	35	40000 rublu
5	3	20	45000 rublu
6	4	20	50,000 rubles
7	5	15	55000 rublu
8	6	15	60000 rublu

Għall-problema tad-determinazzjoni tad-dipendenza tal-ħaddiema separaturi ammont mis-salarju medju għall-mudell tar-rigressjoni 6 intrapriżi għandha l-forma ta 'ekwazzjoni Y =' ₀ + _'1 x ₁ + ... + _k x _k, fejn x _i - fatturi varjabbli li jinfluwenzaw, minn _i - koeffiċjenti ta' rigressjoni, ak - numru ta 'fatturi.

Y għal kompitu partikolari - huwa indikatur għan-nar impjegat, fattur li jikkontribwixxi --salarju, li hija murija minn X.

Utilizzazzjoni tal-qawwa ta ' "Excel" spreadsheet

analiżi ta 'rigressjoni fl-Excel għandha tkun ippreċeduta minn talba għad-dejta eżistenti tabella mibnija fil-funzjonijiet. Madankollu, għal dawn l-iskopijiet huwa aħjar li tuża żid-in "Analiżi pakkett" utli ħafna. Sabiex jippermettu dan, ikollok bżonn li:

• mal-tab "File" mur "Settings";
• fit-tieqa li tiftaħ, agħżel "Żid-ons";
• ikklikkja fuq il-buttuna "Mur", li jinsabu fil-lemin t'isfel tal-linja "ġestjoni";
• tpoġġi marka kontroll li jmiss għal "Analiżi ToolPak" u tikkonferma azzjoni tiegħek billi tagħfas "OK".

Jekk isir b'mod korrett, il-lemin tal-"Data" tab, jinsab fuq il-karta ta 'ħidma "Excel", juri l-buttuna mixtieqa.

Lineari Rigressjoni fl-Excel

Issa li inti għandek fuq naħa l-għodod virtwali meħtieġa għall-kalkoli ekonometriċi, nistgħu tibda tindirizza problema tagħna. Biex tagħmel dan:

• buttuna hija għafast fuq il- "Analiżi tad-Data";
• ikklikkja fuq il-buttuna "rigressjoni" fit-tieqa miftuħa;
• tab li jidher li jintroduċi firxa ta 'valuri Y (in-numru ta' ħaddiema separaturi) u X (salarju tagħhom);
• jaffermaw mill-ġdid l-azzjonijiet tagħhom billi tagħfas il-buttuna "Ok".

Bħala riżultat, il-programm se awtomatikament timla l-folja ispreadsheet analiżi ġdida ta 'rigressjoni tad-data. Oqgħod attent! Fl-Excel, hemm opportunità li jiġi stabbilit il-post li inti tippreferi għal dan il-għan. Per eżempju, jista 'jkun l-istess folja, fejn il-valuri Y u X, jew saħansitra ktieb ġdid, imfassla speċifikament għall-ħżin ta' tali data.

riżultati tal-analiżi ta 'rigressjoni għall-R-kwadru

Id-data Excel miksuba fid-dejta eżempju kkunsidrati jkollha l-forma:

L-ewwelnett, aħna għandhom jagħtu attenzjoni għall-valur tal-R kwadru. Hija tirrappreżenta l-koeffiċjent ta 'determinazzjoni. F'dan l-eżempju, R-kwadru = 0.755 (75.5%), m. E. Il-parametri kalkulati tal-mudell li jispjegaw ir-relazzjoni bejn il-parametri kkunsidrati mill 75.5%. L-ogħla valur tal-koeffiċjent ta 'determinazzjoni, il-mudell magħżul huwa meqjus li jkun aktar utli għall-kompiti partikolari. Huwa maħsub biex jiddeskrivi b'mod korrett is-sitwazzjoni reali fil-valur-R kwadru hawn fuq 0.8. Jekk il-R-kwadru <0.5, imbagħad analiżi ta 'rigressjoni fl-Excel ma jistax jiġi kkunsidrat raġonevoli.

analiżi proporzjon

Numru 64.1428 turi dak li se jkun il-valur tal-Y, jekk l-varjabbli xi fil-mudell tagħna se jkun reset. Fi kliem ieħor, jista 'jiġi argumentat li l-valur tal-parametru analizzat huwa influwenzat minn fatturi oħra apparti dawk deskritti fil-mudell speċifiku.

Il-fattur li jmiss -.16285 jinsabu fil B18 phone, turi l-influwenza importanti ta 'X varjabbli li Y. Dan ifisser li s-salarju medju ta' impjegati fi ħdan l-mudell jaffettwa l-għadd ta 'riżenji mill-piż tal -.16285, t. E. Il-grad tal-impatt tagħha fil-livelli kollha żgħar. Is-sinjal "-" jindika li l-koeffiċjent huwa negattiv. Huwa ovvju, peress li aħna lkoll nafu li s-salarju aktar fl-intrapriża, il-inqas nies esprimew xewqa li jtemm il-kuntratt ta 'impjieg jew miċħuda.

rigressjoni multipla

Taħt dan it-terminu jirreferi għall-ekwazzjoni komunikazzjoni ma 'diversi varjabbli indipendenti tal-formola:

y = f (x ₁ + x ₂ + ... x _m) + ε, fejn y - huwa punteġġ karatteristika (il-varjabbli dipendenti), u x _1, x _2, ... x _m - huma fatturi Sinjali (varjabbli indipendenti).

stima parametru

Għal rigressjoni multipla (MR) li isir permezz ta 'least squares metodu (LSM). Għal ekwazzjonijiet linjari tal-formola Y = a + b ₁ x _{1 + ... + b m × m +} ε bini ta 'sistema ta' ekwazzjonijiet normali (ċm. Hawn taħt)

Biex tifhem il-prinċipju tal-metodu, inqisu li l-każ ta 'żewġ fattur. Imbagħad għandna s-sitwazzjoni deskritta mill-formula

Għalhekk, irridu jiksbu:

fejn σ - huwa l-varjanza tal-karatteristika rispettiv, rifless fl-indiċi.

MNC huwa applikabbli għall-ekwazzjoni MR biex standartiziruemom iskala. F'dan il-każ, irridu jiksbu l-ekwazzjoni:

fejn t _y, t _{x 1, ...} t _xm - standartiziruemye varjabbli li għalihom valuri medji huma 0; ester _i - koeffiċjenti ta 'rigressjoni standardizzati u devjazzjoni standard - 1.

Jekk jogħġbok innota li l-ester ta _i f'dan il-każ definit bħala l-normalizzati u tsentraliziruemye, għalhekk paragun bejn meqjus validu u aċċettabbli. Barra minn hekk, huwa aċċettat li twettaq skrining ta 'fatturi, rimi dawk li għandhom l-aktar valuri baxxi ta βi.

Il-problema bl-użu ekwazzjoni ta 'rigressjoni lineari

Ejja ngħidu ikollok tabella tad-dinamika tal-prezz ta 'prodott N partikolari għall-aħħar 8 xhur. Huwa meħtieġ li tiddeċiedi jekk l-akkwist ta 'partit tiegħu fil-prezz ta 1,850 rubles. / T.

	A	B	C
1	-xahar	isem tax-xahar	prezz N
2	1	Jannar	1750 rublu kull tunnellata
3	2	Frar	1755 rublu kull tunnellata
4	3	Marzu	1767 rublu kull tunnellata
5	4	April	1760 rublu kull tunnellata
6	5	Mejju	1770 rublu kull tunnellata
7	6	Ġunju	1790 rublu kull tunnellata
8	7	lulju	1810 rublu kull tunnellata
9	8	Awissu	1840 rublu kull tunnellata

Biex issolvi din il-problema fil-proċessur tabulari "Excel" meħtieġa li jużaw diġà magħrufa per eżempju għodda "Analiżi tad-Data" ppreżentata hawn fuq. Sussegwentement, agħżel "Rigressjoni" taqsima u parametri stabbiliti. Irridu niftakru li fil-"medda Input Y" għandha tiġi introdotta għal firxa ta 'valuri tal-varjabbli dipendenti (f'dan il-każ il-prezz tal-merkanzija f'xhur speċifiċi tas-sena) u fir- "Input intervall X» - għal indipendenti (il xahar). Aħna nikkonfermaw l-azzjoni billi tikklikkja "Ok". Fil worksheet ġdida (jekk iva indika), irridu jiksbu l-informazzjoni għall-rigressjoni.

Aħna qed jibnu fuqhom ekwazzjoni lineari tal-formola y = mannara + b, fejn bħala parametri aub huma l-koeffiċjenti min-numru linja tax-xahar u l-isem tal-koeffiċjenti u "Y intersezzjoni" linja tal-karta bir-riżultati tal-analiżi ta 'rigressjoni. Għalhekk, l-ekwazzjoni lineari rigressjoni (EQ) 3 għall-problema jista 'jinkiteb bħala:

Il-prezz tal-merkanzija N = 11,714 * 1,727.54 xahar numru +.

jew notazzjoni alġebrija

y = 11,714 x + 1727,54

analiżi tar-riżultati

Biex tiddeċiedi jekk l-irċeviet ekwazzjoni ta 'rigressjoni lineari b'mod adegwat użu tal-koeffiċjenti multipli korrelazzjoni (CMC) u d-determinazzjoni kif ukoll it-test u t-test Fisher. Fit-tabella "Excel" rigressjoni bir-riżultati li jaġixxu taħt l-ismijiet multipli R, R-Pjazza, F-t-istatistika u statistiċi, rispettivament.

KMC R jippermetti li tiġi stmata r-relazzjoni probabilistic qrubija bejn varjabbli indipendenti u dipendenti. valur għoli tagħha tindika konnessjoni biżżejjed qawwija bejn il-varjabbli "Numru tax-xahar" u "prezz tal-prodott N bir-rubli kull 1 tunnellata." Madankollu, in-natura ta 'din ir-relazzjoni mhix magħrufa.

Il-kwadru tal-koeffiċjent ta 'determinazzjoni R ² (RI) hija karatteristika numerika tal-proporzjon ta' dispersjoni totali u juri dispersjoni ta 'porzjon dejta esperimentali, jiġifieri, valuri tal-varjabbli dipendenti li jikkorrispondi għal linear regression equation. F'dan il-problema, dan il-valur huwa 84.8%, mp. E. Istatistika bi grad għoli ta 'preċiżjoni miksub huma deskritti SD.

F-istatistika, magħrufa wkoll bħala kriterju Fisher użata biex tevalwa l-importanza tal-dipendenza lineari jew jikkontradixxu l-ipoteżi li tikkonferma l-eżistenza tagħha.

Il-valur ta 't-istatistika (it-test t Istudenti) jgħin jevalwa l-importanza tal-koeffiċjent fi kwalunkwe membru mhux magħruf ħielsa dipendenza lineari. Jekk il-valur ta 't-test> t _cr, l-ipoteżi ta' importanza żgħira ekwazzjoni lineari ta 'tul ħielsa hija miċħuda.

F'dan il-problema għal terminu b'xejn permezz ta 'strumenti "Excel" instab li t = 169,20903, up =-2,89E 12, t. E. Have probabbiltà żero li l-fidili se tiġi miċħuda l-ipoteżi tal-importanza żgħira tat-terminu ħielsa. Għal koeffiċjent magħruf meta t = 5,79405, up = 0,001158. Fi kliem ieħor, il-probabbiltà li ipotesi korretta miċħuda se insinifikanza tal-koeffiċjent għall-mhux magħruf, huwa 0.12%.

Għalhekk, jista 'jiġi argumentat li l-ekwazzjoni ta' rigressjoni lineari miksuba b'mod adegwat.

Il-problema tal-rakkomandabbiltà ta 'xiri ishma

rigressjoni multipla twettqet fl-Excel bl-użu l-istess "Analiżi tad-Data" għodda. Ikkunsidra l-applikazzjoni speċifika.

kumpanija Gwida «NNN» għandu jiddeċiedi jekk tixtri 20% tal-ishma ta 'JSC "MMM". prezz tal-pakkett (SP) huwa ta '70 miljun dollaru Amerikan. Speċjalisti ta ' "NNN» tinġabar data dwar it-tranżazzjonijiet simili. Ġie deċiż li tevalwa l-valur tal-ishma fuq parametri bħal dawn, espressi f'miljuni ta 'dollari Amerikani, bħal:

• pagabbli (VK);
• volum fatturat annwali (VO);
• riċevibbli (VD);
• valur tal-assi fissi (SOF).

Barra minn hekk, l-użu id-djun tal-pagi ta 'intrapriżi (V3 U) fil eluf ta' dollari Amerikani.

Il proċessur tabella deċiżjoni mezzi Excel

L-ewwel għandek bżonn biex joħolqu tabella tad-dejta. Huwa kif ġej:

Li jmiss:

• kaxxa sejħa "analiżi tad-data";
• magħżula taqsima "Rigressjoni";
• -tieqa "Input intervall valuri varjabbli dipendenti firxa Y» amministrati mill-kolonna G;
• ikklikkja fuq l-ikona bi vleġġa ħamra għad-dritt tat-tieqa "intervall input X» u iżolati fuq firxa folja ta 'valuri kollha ta' kolonna B, Ċ, D, F.

Immarka l-punt "worksheet ġdid" u ikklikkja "Ok".

Get a analiżi tar-rigressjoni għal dan il-kompitu.

Ir-riżultati ta 'studju u l-konklużjonijiet

"Iġbor" tond mid-data ppreżentati hawn fuq fuq il-mejda folja ekwazzjoni proċessur Excel rigressjoni:

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

Fil-forma matematika aktar mis-soltu jista 'jiġi miktub bħala:

y = 0,103 * x1 + 0541 * x2 - 0031 * x3 + 0405 * x4 + 0691 * x5 - 265,844

Id-data għal "MMM" JSC ppreżentati fit-tabella ta 'hawn taħt:

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

Jissostitwixxuhom fil-ekwazzjoni ta 'rigressjoni, kisbet figura ta' 64.72 miljun dollaru Amerikan. Dan ifisser li l-ishma ta 'JSC "MMM" m'għandekx jixtru, minħabba l-ispiża tagħhom hija pjuttost eżaġerat għal 70 miljun dollaru Amerikan.

Kif tistgħu taraw, l-użu ta 'spreadsheet "Excel" u l-ekwazzjoni ta' rigressjoni permess li jagħmel deċiżjoni infurmata dwar ir-rakkomandabbiltà transazzjoni pjuttost speċifiku.

Issa inti taf liema rigressjoni. Eżempji li Excel, imsemmija qabel, se jgħinek biex jissolvew problemi prattiċi ta 'ekonometrika.