Regression Excel: izibonelo equation. komugqa ekuhlehleni

Regression ukuhlaziywa - isifundo indlela kwezibalo ukukhombisa ukwencika ipharamitha eyodwa noma ukuzimela eziguquguqukayo. Ngo zangenkathi yangaphambi ikhompyutha, ukusetshenziswa kwalo bekulokhu kunalokho nzima, ikakhulukazi uma kuziwa amavolumu enkulu idatha. Namuhla, ukufunda indlela yokwakha ukuhlehla ku-Excel, ungakwazi ukuxazulula izinkinga ezinzima kwezibalo ngemizuzu embalwa nje. Ngezansi izibonelo ezicacisiwe ezomnotho.

ekuhlehleni izinhlobo

Lo mqondo wethulwa Mathematics ngu Galton ngo-1886. Regression kuyinto:

• komugqa;
• parabolic;
• amandla;
• lomchazi;
• ephambene;
• lomchazi;
• logarithmic.

ISIBONELO 1

Cabangela le nkinga yokunquma ukwencika inani ezijwayelekile njengokusula emsebenzini amalungu abasebenzi amaholo isilinganiso 6 amabhizinisi ezimbonini.

Umsebenti. izinkampani eziyisithupha baye bacubungula Isilinganiso sanyanga zonke emholweni futhi nenombolo abasebenzi ukuyeka ngokuzithandela. In ifomu tabular esinalo:

	A	B	C
1	X	Inombolo ezijwayelekile njengokusula emsebenzini	emholweni
2		y	ruble 30000
3	1	60	ruble 35000
4	2	35	ruble 40000
5	3	20	ruble 45000
6	4	20	ruble 50,000
7	5	15	ruble 55000
8	6	15	ruble 60000

Ukuze inkinga yokunquma ukwencika abasebenzi isamba abahlukanisayo kusukela komholo for 6 amabhizinisi imodeli ekuhlehleni ine uhlobo kwesibalo Y = ₀ + _i-1 x ₁ + ... + a _k x _k, lapho x _i - eziguquguqukayo ezinomthelela, a _i - ekuhlehleni okuza, ak - eziningi.

Y ngokuba umsebenzi inikezwe - kuba inkomba nomlilo isisebenzi, kunomthelela - iholo, okuyinto okhonjiswe X.

Wasebenzisa amandla "Excel"-spreadsheet

Regression ukuhlaziywa Excel kufanele kube nenkulumo yamaphuzu isicelo idatha ekhona ithebula esakhelwe imisebenzi. Nokho, ngalezi zinhloso kungcono ukusebenzisa iyasiza isengezo e "iphakethe ukuhlaziywa". Ukuze uyinike amandla, udinga:

• nge ithebhu "Ifayela" yiya engxenyeni ethi "Izilungiselelo";
• ewindini esivula, khetha 'Engeza-Ons';
• chofoza inkinobho "Hamba", sise ngezansi kwesokudla emgqeni "ukuphathwa";
• ubeke uphawu lokuhlola eduze "Ukuhlaziywa ToolPak" bese uqinisekisa isenzo sakho ngokucindezela "KULUNGILE".

Uma kwenziwe ngendlela efanele, ohlangothini kwesokudla "Idatha" ithebhu, engaphezulu umsebenzi ishidi "Excel", ubonisa inkinobho oyifunayo.

Ukuthuthukiswa Regression Excel

Manje njengoba nginazo zonke kunesidingo amathuluzi virtual izibalo econometric, singaqala ukubhekana inkinga yethu. Ukuze wenze lokhu:

• inkinobho uchofoze "Idatha Ukuhlaziywa";
• chofoza inkinobho "ukuhlehla 'ewindini evulekile;
• ithebhu elivela ukwethula ububanzi bamanani Y (inani abahlukanisayo abasebenzi) kanye X (umholo wabo);
• igcizelele izenzo zabo ngokucindezela inkinobho «Ok».

Ngenxa yalokho, uhlelo uyogcwalisa ngokuzenzakalelayo elisha leshidi-spreadsheet idatha ekuhlehleni ukuhlaziywa. Nakani! Ku-Excel, kukhona ithuba ukusetha indawo uthanda le njongo. Ngokwesibonelo, kungase kube ishidi efanayo, lapho amagugu Y no X, noma ngisho incwadi entsha, lenzelwa isitoreji idatha efana.

Imiphumela Regression kokuhlahlela R-square

Idatha Excel etholwe isibonelo idatha kubhekwe isimo:

Okokuqala, kufanele silinake ngokungathí ukubaluleka R-squared. Limelela Coefficient ukuzimisela. Kulesi sibonelo, R-square = 0.755 (75,5%), m. E. Amapharamitha ibalwa imodeli ukuchaza ubudlelwane phakathi kwemigomo zicatshangelwa 75,5%. Ayanda ukubaluleka Coefficient ukuzimisela, imodeli akhethiwe ubhekwa usizo nakakhulu imisebenzi ethile. Kukholakala ukuthi kahle ukuchaza isimo sangempela ngaso inani R-square ngenhla 0.8. Uma R-square <0.5 ke ukuhlaziya ukuhlehla ku-Excel ayikwazi kubhekwe ezinengqondo.

isilinganiso ukuhlaziywa

Inombolo 64,1428 libonisa lokho kuyoba ukubaluleka Y, uma wonke eziguquguqukayo xi kakhulu endleleni yethu yethu izohlelwa futhi. Ngamanye amazwi, kungashiwo wagomela ukuthi ukubaluleka ipharamitha ihlaziye kuthonywa nezinye izinto kunalezo ezichazwe kule imodeli ethize.

Lesi sici esilandelayo -0,16285 elise cell B18, kubonisa ebalulekile X variable ukuba Y. Lokhu kusho ukuthi isilinganiso somholo abasebenzi ngaphakathi imodeli kuthinta inani ezijwayelekile njengokusula emsebenzini kusukela isisindo -0,16285, t. E. degree nomthelela nhlobo ezincane. Isibonakaliso "-" ibonisa ukuthi Coefficient alivumi. Kusobala, njengoba sonke siyazi ukuthi iholo ngaphezulu ebhizinisini, abantu ngaphansi baye bazwakalisa isifiso sokuqeda ikontraga noma sichithiwe.

ekuhlehleni amaningi

Ngaphansi leli gama libhekisela ukuxhumana kwesibalo nge eziningana eziguquguqukayo esizimele ifomu:

y = f (x + ₁ x ₂ + ... x _m) + ε, lapho y - kuyinto amaphuzu sici (variable engaphansi), futhi x _1, x _2, ... x _m - izimpawu izici (eziguquguqukayo ezimele).

ukulinganisa ipharamitha

Ukuze ekuhlehleni amaningi (MR) it is ezenziwe usebenzisa okungenani indlela yezikwele (LSM). Ukuze zibalo lwento yefomu Y = a + b ₁ x _{1 + ... + b m x m +} ε ukwakha isimiso of zibalo evamile (cm. Ngezansi)

Ukuze uqonde isimiso indlela, sicabanga kunjalo kwezici ezimbili. Khona-ke siye isimo esachazwa ifomula

Ngakho, senza ukuthola:

lapho σ - iyona ukuhluka kwesici abafanele, njengoba kubonakala kula Inkomba.

MNC kusebenza ezothando MR ukuze standartiziruemom esikalini. Kulokhu, sithola kwesibalo:

lapho t _y, t _{x 1, ...} t _XM - standartiziruemye eziguquguqukayo okuyiwona kumanani esilinganiso kukhona 0; β _i - zifane ekuhlehleni okuza kanye nokusuka emgomeni - 1.

Sicela wazi ukuthi konke β _i kulokhu kuchazwa ngokuthi kwesejwayelekile futhi tsentraliziruemye, ngakho ukuqhathanisa phakathi kubhekwe evumelekile futhi eyamukelekayo. Ngaphezu kwalokho, samukelwa ukuba afeze Ukuhlolwa yezinto, ukulahla lezo aphansi of βi.

Inkinga nge usebenzisa komugqa ekuhlehleni kwesibalo

Ake sithi ithebula Dynamics intengo ethile umkhiqizo N for the last 8 izinyanga. Kuyadingeka ukuba unqume ukuthi yokufuna iqembu lakhe ngesikhathi intengo ruble 1850. / T.

	A	B	C
1	ngenyanga	igama lenyanga	Inani N
2	1	Januwari	1750 ruble ngethani
3	2	Februwari	1755 ruble ngethani
4	3	Mashi	1767 ruble ngethani
5	4	April	1760 ruble ngethani
6	5	May	1770 ruble ngethani
7	6	Juni	1790 ruble ngethani
8	7	Julayi	1810 ruble ngethani
9	8	Agasti	1840 ruble ngethani

Ukuxazulula le nkinga esikhathini processor tabular "Excel" iyadingeka ukusebenzisa kakade eyaziwa ngokuba ithuluzi isibonelo "Idatha Ukuhlaziywa" ethulwa ngenhla. Okulandelayo, khetha "Regression" kwesigaba bese usetha nemingcele. Kumele sikhumbule ukuthi "wokufaka ububanzi Y» kufanele kwetfulwe uhla abakuzuzile variable engaphansi (kulesi simo intengo izimpahla e izinyanga ezithile zonyaka) nasesikhathini "wokufaka isikhawu X» - I-ezimele (ngenyanga). Thina ukuqinisekisa isenzo ngokuchofoza «Ok». Ekhasini lokusebenza elisha (uma kunjalo wabonisa), thina ukuthola datha ekuhlehleni.

Sakhela phezu kwabo kwesibalo komugqa ifomu y = imbazo + isibekiwe b, lapho njengoba imingcele a futhi b kukhona okuza kusukela Inambalayini lwenyanga futhi igama okuza futhi «Y-empambana" umugqa ishidi ne imiphumela yokuhlaziya ekuhlehleni. Ngakho, komugqa ekuhlehleni kwesibalo (EQ) 3 inkinga singatlolwa bunjesi:

Intengo kwezimpahla N = inombolo 11,714 * 1727.54 ngenyanga +.

noma ngento algebraic

y = 11.714 x + 1727,54

ukuhlaziya Imiphumela

Ukuze anqume ukuthi wathola ngokwanele komugqa ekuhlehleni kwesibalo usebenzisa amaningi ukuhlanganisa okuza (CMC) nokuzimisela kanye test futhi Fisher sika-t-test. Ethebuleni "Excel" ukuhlehla ngemiphumela benza ngaphansi amagama amaningi R, R-Square, F-t-izibalo nezibalo, ngokulandelana.

KMC R kwenza ukulinganisa ukusondela ubuhlobo probabilistic emkhatsini wetinombolo ezimele futhi wadinga usizo. Inani layo eliphezulu kubonisa ukuthi kunokuhlobana okukhulu ngokwanele phakathi variable "Inombolo lwenyanga" futhi "N intengo yomkhiqizo in ruble ngayinye 1 ton." Nokho, isimo sale ubuhlobo alwaziwa.

Isikwele we Coefficient ukuzimisela R ² (RI) kuwuphawu yezinombolo Ingxenye chitha inani sikhombisa chitha ka zokuhlola idatha ingxenye, ngamanye amazwi, abakuzuzile variable engaphansi elihambisana komugqa ekuhlehleni equation. Kulesi nkinga, lesi silinganiso kuyinto 84,8%, mp. E. Izibalo nge izinga eliphakeme lokunemba etholwe bachazwa le-SD.

F-izibalo, eyaziwa nangokuthi i-Fisher umbandela zisetshenziselwe ukuhlola ukubaluleka kokwethembela komugqa noma disproving hypothesis eqinisekisa ukhona.

Inani-t wesibalo (t test komfundi) kusiza ukuhlola ukubaluleka Coefficient ngasiphi khulula engaziwa komugqa kwencika ilungu. Uma ukubaluleka-t> t _cr, umbono we komugqa kwesibalo akalutho uma eqhathaniswa naye eside khulula sinqatshelwe.

Kulesi nkinga ihlandla khulula ngokusebenzisa amathuluzi "Excel" kwatholakala ukuthi t = 169,20903, futhi p = 2,89E-12, t. E. Yiba zero okungenzeka ukuthi ethembekileyo kuzonqatshelwa umbono we akalutho uma eqhathaniswa naye ethemini khulula. Ukuze Coefficient ezazingaziwa ngaleso t = 5,79405, futhi p = 0,001158. Ngamanye amazwi, kunamathuba khona okuthi sinqatshiwe hypothesis elungile kuzokwenza akalutho uma eqhathaniswa naye Coefficient ngenxa engaziwa, kuba 0.12%.

Ngakho, kungashiwo wagomela ukuthi etholakale komugqa ekuhlehleni kwesibalo ngokwanele.

Inkinga sekufanele awushiye ukuthenga ukwabelana

ekuhlehleni Multiple wawenziwa Excel usebenzisa okufanayo "Idatha Ukuhlaziywa" ithuluzi. Cabanga isicelo ethize.

Umhlahlandlela inkampani «NNN» kufanele unqume ukuthi okanye ukuthenga u-20% ukwabelana JSC «MMM». Intengo Iphakheji (SP) kuyinto ezigidini ezingu-70 US dollars. Ochwepheshe of «NNN» eqoqwe idatha ku ukuthengiselana efanayo. Kwanqunywa ukuba ukuhlola ukubaluleka akwabelanayo imingcele olunjalo oluboniswa izigidi aseMelika, njenge:

• okumele zikhokhelwe (VK);
• imali yonyaka ivolumu (vơ);
• Receivables (vd);
• ukubaluleka ezimpahleni ezingasuki phansi (SOF).

Ngaphezu kwalokho, ukusebenzisa izikweletu amaholo wamabhizinisi (V3 U) e izinkulungwane aseMelika.

Ithebula isinqumo processor Excel izindlela

Okokuqala udinga ukudala ithebula yedatha input. Kuyinto kanje:

Okulandelayo:

• ucingo ibhokisi elithi "ukuhlaziywa idatha";
• akhethiwe "Regression" kwesigaba;
• iwindi "wokufaka isikhawu Y» iqondiswa ububanzi engaphansi amagugu variable ukusuka kukholomu G;
• chofoza uphawu nge umcibisholo ebomvu kwesokudla sewindi "wokufaka isikhawu X» futhi wahlukaniswa ebangeni ishidi sawo wonke amanani kukhalamu B, C, D, uF

Dwebela iphuzu "lokusebenzela okusha" bese uchofoza "Kulungile".

Thola ukuhlaziywa ekuhlehleni ngoba lo msebenzi.

Imiphumela cwaningo futhi kufinyelelwe iziphetho ezinembile

"Qoqa" babanjwa kusukela imininingwane eyethulwa ngenhla etafuleni ishidi Excel processor ekuhlehleni kwesibalo:

SD = 0,103 * SOF + 0,541 * vơ - 0.031 * VK + 0,405 * vd + 0,691 * VZP - 265.844.

Esikhathini ngaphezulu kokujwayelekile ifomu zezibalo ke singatlolwa bunjesi:

y = 0.103 * x1 + 0,541 * X2 - 0.031 * X3 + 0,405 * x4 + 0,691 * x5 - 265.844

Idatha ye-«MMM» JSC ethulwa kulelithebula lelingentasi:

SOF, USD	Vơ, USD	VK, USD	Vd, USD	VZP, USD	JV, USD
102,5	535,5	45,2	41,5	21,55	64,72

Uma kufakwa kwabo phakathi kwesibalo ekuhlehleni, uthole sibalo eziyizigidi ezingu-64,72 aseMelika. Lokhu kusho ukuthi ukwabelana JSC «MMM» akufanele ukuthenga, ngoba izindleko yabo ngempela overpriced million 70 aseMelika.

Njengoba ubona, ukusetshenziswa i-spreadsheet "Excel" futhi ezothando ekuhlehleni avunyelwe ukuba wenze isinqumo unolwazi mayelana sekufanele awushiye kuthengiselana likubeka ngokucacile.

Manje uyazi ukuthi kwaba nokushiywa. Izibonelo ku-Excel, ezichazwe ngenhla, kuzokusiza ekuxazululeni izinkinga esiwusizo sokuba nezinto eziqondile Econometrics.