Atchweliad yn Excel: Enghreifftiau hafaliad. llinol atchweliad

dadansoddiad atchweliad - dull astudiaeth ystadegol i ddangos dibyniaeth paramedr o un neu fwy annibynnol newidynnau. Yn y cyfnod cyn-gyfrifiadur, ei ddefnydd wedi bod braidd yn anodd, yn enwedig pan ddaeth i symiau mawr o ddata. Heddiw, dysgu sut i adeiladu atchweliad yn Excel, gallwch ddatrys problemau ystadegol cymhleth mewn dim ond ychydig funudau. Isod ceir enghreifftiau penodol o economeg.

mathau o atchweliad

Cyflwynwyd y cysyniad hwn i mathemateg gan Francis Galton yn 1886. Atchweliad yw:

• llinol;
• parabolig;
• pŵer;
• esbonyddol;
• hyperbolig;
• esbonyddol;
• logarithmig.

ENGHRAIFFT 1

Ystyriwch y broblem o benderfynu ar y dibyniaeth y nifer o ymddiswyddiadau o aelodau staff y cyflog cyfartalog yn y 6 mentrau diwydiannol.

Tasg. Chwe chwmni wedi dadansoddi'r cyflog misol ar gyfartaledd a nifer y gweithwyr sy'n rhoi'r gorau iddi yn wirfoddol. Ar ffurf tabl rydym wedi:

	A	B	C
1	X	Nifer o ymddiswyddiadau	cyflog
2		y	30000 rubles
3	1	60	35000 rubles
4	2	35	40000 rubles
5	3	20	45000 rubles
6	4	20	50,000 rubles
7	5	15	55,000 rubles
8	6	15	60000 rubles

Ar gyfer y broblem o benderfynu dibyniaeth y gweithwyr swm gwahaniadau gan y cyflog cyfartalog ar gyfer model atchweliad 6 o fentrau sydd ar ffurf hafaliad Y = ₀ + ₁ x ₁ + ... + _k x _k, lle mae x _i - newidynnau dylanwadu, a _i - cyfernodau atchweliad, ak - nifer o ffactorau.

Y ar gyfer tasg benodol - mae'n ddangosydd i dân yn weithiwr, yn ffactor sy'n cyfrannu - y cyflog, sy'n cael ei ddynodi gan X.

Harneisio pŵer "Excel" taenlen

Dylai dadansoddiad atchweliad yn Excel yn cael eu rhagflaenu gan gais i'r data tabl yn bresennol adeiledig yn swyddogaethau. Fodd bynnag, at y dibenion hyn, mae'n well defnyddio "dadansoddiad pecyn" ychwanegu i mewn yn ddefnyddiol iawn. Er mwyn galluogi hyn, bydd angen i chi:

• gyda'r tab "File" mynd i "Gosodiadau";
• yn y ffenestr sy'n agor, dewiswch 'Add-ons';
• cliciwch ar y botwm "Mynd", a leolir yn y dde waelod y llinell "rheoli";
• rhoi tic gyferbyn â'r "Dadansoddiad Toolpak" a chadarnhau eich camau gweithredu drwy wasgu'r "OK".

Os wneud yn gywir, yr ochr dde o'r tab "Data", sydd wedi'i leoli uwchben y daflen waith "Excel", yn dangos y botwm a ddymunir.

Linear Atchweliad yn Excel

Nawr eich bod wedi wrth law yr holl offer angenrheidiol ar gyfer rhithwir cyfrifiadau econometrig, gallwn ddechrau mynd i'r afael â'n problem. Er mwyn gwneud hyn:

• botwm ei glicio ar y "Dadansoddi Data";
• cliciwch ar y botwm "atchweliad" yn y ffenestr agored;
• tab sy'n ymddangos i gyflwyno ystod o werthoedd Y (nifer y gweithwyr gwahaniadau) ac X (eu cyflog);
• gadarnhau eu gweithredoedd drwy bwyso'r botwm «Iawn».

O ganlyniad, bydd y rhaglen yn llenwi'r daflen daenlen dadansoddiad atchweliad data newydd yn awtomatig. Talu sylw! Yn Excel, mae cyfle i osod y lle sy'n well gennych at y diben hwn. Er enghraifft, efallai y bydd yr un ddalen, lle mae gwerthoedd Y a X, neu hyd yn oed llyfr newydd, a gynlluniwyd yn benodol ar gyfer storio data o'r fath.

Canlyniadau dadansoddiad atchweliad am R-sgwâr

Mae'r data Excel a gafwyd yn y data enghraifft ystyrir yn cael y ffurflen:

Yn gyntaf oll, dylem dalu sylw at werth R-sgwâr. Mae'n cynrychioli cyfernod penderfyniad. Yn yr enghraifft hon, R-sgwâr = 0.755 (75.5%), m. E. Mae paramedrau cyfrifo y model i egluro'r berthynas rhwng y paramedrau a ystyriwyd gan 75.5%. Po uchaf yw gwerth y cyfernod penderfyniad, y model a ddewiswyd yn cael ei ystyried i fod yn fwy defnyddiol ar gyfer tasgau penodol. Credir i ddisgrifio'r sefyllfa go iawn yn gywir ar y gwerth R-sgwâr yn uwch 0.8. Os bydd y R-sgwâr <0.5, yna dadansoddiad atchweliad yn Excel ni ellir ei ystyried yn rhesymol.

dadansoddiad cymarebau

Rhif 64.1428 dangos beth fydd gwerth y Y, os bydd yr holl xi newidynnau yn ein model ei ailosod. Mewn geiriau eraill, gellir dadlau bod gwerth y paramedr a ddadansoddwyd yn cael ei ddylanwadu gan ffactorau ar wahân i'r rhai a ddisgrifir yn y model penodol.

Y ffactor nesaf -.16285 lleoli yn B18 gell, yn dangos dylanwad pwysig o amrywiol X i Y. Mae hyn yn golygu bod y cyflog cyfartalog y gweithwyr o fewn y model yn effeithio ar y nifer o ymddiswyddiadau o bwysau'r -.16285, t. E. Mae rhywfaint o effaith o gwbl fach. Mae'r arwydd "-" yn nodi bod y cyfernod yn negyddol. Mae'n amlwg, ers i ni i gyd yn gwybod bod y cyflog yn fwy yn y fenter, y lleiaf o bobl wedi mynegi awydd i derfynu'r contract cyflogaeth neu ddiswyddo.

atchweliad lluosog

O dan y term hwn yn cyfeirio at yr hafaliad gyfathrebu gyda nifer o newidynnau annibynnol o'r ffurflen:

y = f (x ₁ + x ₂ + ... x _m) + ε, lle y - yn sgôr nodwedd (y newidyn dibynnol), ac x _1, x _2, ... x _m - yn ffactorau arwyddion (newidynnau annibynnol).

amcangyfrif paramedr

Ar gyfer atchweliad lluosog (MR) ei fod yn perfformio gan ddefnyddio dull sgwariau lleiaf (RhDG). Ar gyfer hafaliadau llinol o'r ffurflen Y = a + b ₁ x _{1 + ... + b m x m +} ε adeiladu system o hafaliadau arferol (cm. Isod)

Er mwyn deall yr egwyddor y dull, rydym yn ystyried yr achos dau-ffactor. Yna rydym yn cael y sefyllfa a ddisgrifir gan y fformiwla

Felly, rydym yn cael:

lle mae σ - yw'r amrywiant y nodwedd dan sylw, a adlewyrchir yn y mynegai.

MNC yn berthnasol i hafaliad MR i standartiziruemom raddfa. Yn yr achos hwn, rydym yn cael yr hafaliad:

wherein t _y, t _{x 1, ...} t _xm - standartiziruemye newidynnau y mae gwerthoedd cyfartalog yn 0; β _i - cyfernodau atchweliad safonol a gwyriad safonol - 1.

Nodwch fod pob β _i yn yr achos hwn ddiffinio fel y normaleiddio a tsentraliziruemye, felly cymhariaeth rhwng ei ystyried yn ddilys ac yn dderbyniol. Yn ogystal â hyn, mae'n cael ei dderbyn i gynnal sgrinio o ffactorau, taflu y rhai sydd yn cael y gwerth isaf o βi.

Y broblem gyda defnyddio hafaliad atchweliad llinol

Tybiwch fod gennych tabl o ddeinameg pris cynnyrch penodol N ar gyfer yr 8 mis diwethaf. Mae'n angenrheidiol i benderfynu a chaffael ei blaid am y pris o 1850 rubles. / T.

	A	B	C
1	y mis	enwi y mis	pris N
2	1	Ionawr	1750 rubles y dunnell
3	2	Chwefror	1755 rubles y dunnell
4	3	Mawrth	1767 rubles y dunnell
5	4	Ebrill	1760 rubles y dunnell
6	5	Mai	1770 rubles y dunnell
7	6	Mehefin	1790 rubles y dunnell
8	7	Gorffennaf	1810 rubles y dunnell
9	8	Awst	1840 rubles y dunnell

I ddatrys y broblem yn y prosesydd tabl "Excel" ofynnol i ddefnyddio eisoes yn hysbys, er enghraifft arfau "Dadansoddi Data" a gyflwynir uchod. Nesaf, dewiswch yr adran "Atchweliad" a gosod paramedrau. Rhaid inni gofio bod yn y "amrediad Mewnbwn Dylai Y» eu cyflwyno i ystod o werthoedd y newidyn dibynnol (yn yr achos hwn y pris y nwyddau yn ystod y misoedd penodol o'r flwyddyn) ac yn y "Mewnbwn egwyl X» - ar gyfer annibynnol (y mis). Rydym yn cadarnhau y camau gweithredu drwy glicio «Iawn». Mewn taflen waith newydd (os nodir hynny), rydym yn cael y data ar gyfer y atchweliad.

Rydym yn adeiladu arnynt hafaliad llinol y ffurf y = ax + b, lle fel y paramedrau a a b yn y cyfernodau oddi wrth y nifer linell y mis ac enw'r cyfernodau a llinell «Y-groesffordd" y daflen gyda chanlyniadau'r dadansoddiad atchweliad. Felly, gall y hafaliad llinol atchweliad (EQ) 3 ar gyfer y broblem yn cael ei ysgrifennu fel:

Mae pris nwyddau N = 11,714 * 1727.54 mis rhif +.

neu mewn nodiant algebraidd

y = x + 11,714 1727,54

dadansoddiad o ganlyniadau

Er mwyn penderfynu a yw'r dderbyniwyd hafaliad atchweliad llinol ddigonol gan ddefnyddio'r cyfernodau cydberthyniad lluosog (CMC) a phenderfyniad yn ogystal â prawf a-prawf t Fisher. Yn y tabl "Excel" atchweliad gyda'r canlyniadau eu bod yn gweithredu o dan yr enwau lluosog R, R-Square, F-t-ystadegau ac ystadegau, yn y drefn honno.

KMC R galluogi i amcangyfrif y berthynas agosrwydd tebygol rhwng newidynnau annibynnol a dibynnol. Mae ei werth uchel yn dangos cysylltiad digon cryf rhwng y newidyn "Nifer y mis" a "pris N Cynnyrch mewn rubles fesul 1 dunnell." Fodd bynnag, mae natur y berthynas hon yn hysbys.

Mae'r sgwâr y cyfernod penderfyniad R ² (RI) yn nodwedd rhifol o gyfran o gyfanswm gwasgariad ac yn dangos gwasgariad o cyfran data arbrofol, hy, gwerthoedd y newidyn dibynnol sy'n cyfateb i hafaliad atchweliad llinol. Yn y broblem, y gwerth hwn yn 84.8%, AS. E. Ystadegau gyda lefel uchel o gywirdeb a gafwyd yn cael eu disgrifio DC.

F-ystadegau, a elwir hefyd yn maen prawf Fisher a ddefnyddiwyd i asesu arwyddocâd y ddibyniaeth llinol neu wrthbrofi rhagdybiaeth yn cadarnhau ei bodolaeth.

Mae gwerth t-ystadegyn (prawf t Myfyrwyr) yn helpu gwerthuso arwyddocâd y cyfernod ar unrhyw aelod dibyniaeth llinellol anhysbys rhad ac am ddim. Os yw gwerth prawf-t> t _cr, y ddamcaniaeth o hafaliad llinol o insignificance tymor rhad ac am ddim yn cael ei wrthod.

Yn y broblem hon am dymor am ddim drwy offerynnau "Excel" gwelwyd bod t = 169,20903, ac p = 2,89E-12, t. E. Cael tebygolrwydd sero y bydd y ffyddlon yn cael ei wrthod y ddamcaniaeth o insignificance o'r term rhad ac am ddim. Ar gyfer cyfernod hysbys ar t = 5,79405, ac p = 0,001158. Mewn geiriau eraill, y tebygolrwydd y bydd rhagdybiaeth yn gywir a wrthodwyd insignificance o'r cyfernod ar gyfer yr anhysbys, yn 0.12%.

Felly, gellir dadlau fod i'r hafaliad atchweliad llinol a gafwyd yn ddigonol.

Mae'r broblem o mor ddoeth fyddai prynu cyfranddaliadau

atchweliad lluosog ei pherfformio yn Excel ddefnyddio'r un "Dadansoddi Data" offeryn. Ystyried y cais penodol.

Canllaw cwmni «NNN» Mae'n rhaid i benderfynu p'un ai i brynu 20% o gyfranddaliadau o JSC «MMM». Pris Pecyn (SP) yw 70 miliwn doler yr Unol Daleithiau. Arbenigwyr o «NNN» casglu data ar drafodion tebyg. Penderfynwyd i asesu gwerth y cyfranddaliadau ar paramedrau megis, a fynegir mewn miliynau o ddoleri yr Unol Daleithiau, megis:

• taladwy (VK);
• cyfrol trosiant blynyddol (VO);
• derbyniadwy (VD);
• gwerth asedau sefydlog (SOF).

Yn ogystal, defnyddiwch y dyledion cyflog mentrau (V3 U) mewn miloedd o ddoleri yr Unol Daleithiau.

Mae'r prosesydd tabl penderfyniad modd Excel

Yn gyntaf bydd angen i chi greu tabl o ddata mewnbwn. Mae fel a ganlyn:

nesaf:

• blwch ffôn "dadansoddi data";
• dewis adran "Atchweliad";
• y ffenestr "Input egwyl amrediad Y» weinyddir gwerthoedd newidyn dibynnol o golofn G;
• cliciwch ar yr eicon gyda saeth goch ar y dde o'r ffenestr "Input egwyl X» ac ynysu ar ystod ddalen o holl werthoedd colofn B, C, D, F

Mark pwynt "daflen waith Newydd" a chlicio "Iawn".

Cael dadansoddiad atchweliad gyfer y dasg hon.

Mae canlyniadau'r astudiaeth a chasgliadau

"Casglu" talgrynnu o'r data a gyflwynir uchod ar y bwrdd ddalen hafaliad Excel prosesydd atchweliad:

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

Yn y chweched mathemategol fwy arferol y gellir ei ysgrifennu fel:

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

Mae data ar gyfer «MMM» JSC a gyflwynir yn y tabl isod:

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

eu amnewid i hafaliad atchweliad, a gafwyd ffigur o 64,720,000 doler yr Unol Daleithiau. Mae hyn yn golygu na ddylai y cyfrannau o JSC «MMM» brynu, oherwydd eu cost yn cael ei overpriced eithaf ar 70 miliwn o ddoleri yr Unol Daleithiau.

Fel y gwelwch, y defnydd o daenlen "Excel" a'r hafaliad atchweliad ganiateir i wneud penderfyniad gwybodus ynghylch pa mor ddoeth fyddai trafodiad eithaf penodol.

Nawr eich bod yn gwybod beth yw atchweliad. Enghreifftiau i Excel, a drafodwyd uchod, yn eich helpu i ddatrys problemau ymarferol o econometreg.