Inenga i roto i Excel: tauira whārite. rārangi inenga

tātari Hokinga - he tikanga ako tauanga ki te whakaatu i te fakafalala o te tawhā o tetahi ranei ake motuhake taurangi. I roto i te wā mua rorohiko-, kua ona whakamahi i kaua uaua, tautautefito ki he taimi haere mai te reira ki pukapuka nui o ngā raraunga. I teie mahana, te ako pehea ki te hanga i te inenga i roto i Excel, ka taea e koe te whakaoti rapanga tatauranga matatini i roto i te torutoru noa meneti. Kei raro he tauira motuhake o te ohaoha.

momo inenga

whakaurua tenei ariā i ki te pāngarau e Francis Galton i 1886. Hokinga ko:

• rārangi;
• unahi;
• mana;
• taupūtanga;
• pūwerewere;
• taupūtanga;
• pākoki.

TAUIRA 1

A feruri i te raruraru o te whakatau i te ti'aturiraa o te maha o ngā whakawāteatanga o ngā mema o te kapa i te utu toharite mō te 6 hinonga ahumahi.

Tūmahi. Kua tātarihia e ono kamupene te utu marama toharite me te tokomaha o ngā kaimahi e whakamutu noa. I roto i te puka ripa to tatou:

	He	B	C
1	X	Tau o ngā whakawāteatanga	utu
2		y	30000 moni
3	1	60	35000 moni
4	2	35	40000 moni
5	3	20	45000 moni
6	4	20	50,000 moni
7	5	15	55000 moni
8	6	15	60000 moni

Hoki te raruraru o te whakatau i te ti'aturiraa o te kaimahi māvaé nui i te utu toharite mō te 6 hinonga tauira inenga o e te puka o te whārite Y = te ₀ + he x _{tetahi 1} + ... + he _k x _k, te wahi x _i - awe taurangi, he _i - whakarea inenga, AK - maha o ngā āhuatanga.

Y mo te mahi i homai - ko reira he tohu ki te ahi i te kaimahi, he take whai wāhi - te utu, denoted nei e X.

Te whakamahinga o te mana o "Excel" ripanga

kia tātaritanga Hokinga i Excel kia mua e te tono ki te raraunga ripanga tīariari hanga-i roto i te mahi. Heoi, hoki enei take he reira pai ki te whakamahi i te tāpiri-i roto i "tātari pākete" tino whai hua. Hei whakahohe i te reira, e hiahia ana koe ki te:

• ki te ripa "Kōnae" haere ki "Ngā Tautuhinga";
• i roto i te matapihi e oroko, tīpako 'Tāpiri-ake';
• pāwhiri i runga i te pātene "Haere", kei i te matau raro o te aho "whakahaere";
• hoatu he tohu taki i muri ki "MōkīUtauta Tātaritanga" me te whakaū i tō mahi mā te pēhi i "OK".

Ki te mahi tika, te taha ki matau o te ripa "Raraunga", kei runga ake i te whārangi mahi "Excel", whakaatu ana te pātene hiahiatia.

Linear Hokinga i Excel

Na e whai koutou i runga i te ringa i te taputapu e tika ana katoa mariko mō tātaitanga econometric, ka taea e tatou e haamata i ki te whakatutuki i to tatou raruraru. Ki te mahi i tenei:

• pāwhiri pātene kei i runga i te "Analysis Raraunga";
• pāwhiri i runga i te pātene "inenga o" i roto i te matapihi tuwhera;
• he ripa e puta ki te whakauru i te whānuitanga o ngā uara Y (te tokomaha o māvaé kaimahi) me X (ratou utu);
• whakaū ratou mahi mā te pēhi i te pātene «Āe».

Ka rite ki te hua, te hōtaka ka whakakiia aunoa i te whārangi ripanga tātari inenga raraunga hou. Kia whai whakaaro! I roto i te Excel, i reira he he faingamālie ki whakaturia te wahi e hiahia koe mo tenei whakaaro. Hei tauira, kia mau ai ki te whārangi taua, te wahi i nga uara Y ko X, ara he pukapuka hou ranei, äta hangaia mo te rokiroki o taua raraunga.

hua tātari Hokinga mō R-tapawhā

Ko te raraunga Excel whiwhi i roto i te raraunga tauira whakaaro i te puka:

Tuatahi o te katoa, kia tatou e whakarongo ki te uara o te R-porowhā. tohu te reira i te tau whakarea o te faaotiraa. I roto i tenei tauira, R-tapawha = 0.755 (75.5%), m. E. Ko te tawhā tātai o te tauira ki te whakamārama i te hononga i waenganui i te tawhā whakaaro e 75.5%. Ko te teitei te uara o te tau whakarea o te faaotiraa, whakaaro te tauira tīpakohia te ki kia atu whai hua mō te ngā mahi. Kei te whakapono te reira ki te whakaahua tika te āhuatanga tūturu i te uara R-tapawha i runga 0.8. Ki te, ka taea e kore te whakaaro whaitake te R-tapawha <0.5 he tātaritanga inenga i roto i Excel.

tātari ōwehenga

E whakaatu ana Tau 64,1428 aha e waiho te uara o te Y, ki te ka tautuhi anō katoa te xi taurangi i roto i to tatou tauira. I roto i te mau parau te tahi atu, e taea te tohe i te reira e te awe te uara o te tawhā tātarihia e te tahi atu āhuatanga atu te hunga whakaahuatia i roto i te tauira motuhake.

Ko te take i muri -0,16285 kei roto i B18 pūtau, whakaatu te mana nui o te X tāupe ki Y. tenei tikanga e te utu toharite o kaimahi i roto i te tauira pānga te maha o ngā whakawāteatanga i te taimaha o te -0,16285, e ko. E. te tohu o tona pānga i te katoa iti. Ko te tohu "-" tohu e he tōraro te whakarea. He kitea reira, mai mohio tatou katoa e te atu utu i roto i te hinonga, iti iho te iwi i whakahuatia he hiahia ki te whakamutu i te kirimana o te mahi ka tonoa atu ranei.

regression maha

Na roto i tenei wā e pā ana ki te whārite kōrero ki te maha taurangi motuhake o te puka:

y = f (x ₁ + x ₂ + ... x _m) + ε, kei hea y - ko te kaute āhuatanga (te tāupe pararau), me x _1, x _2, ... x _m - he tohu āhuatanga (taurangi motuhake).

i whakarite tawhā

Hoki regression maha (MR) meatia e te reira te whakamahi i te tikanga tapawhā iti rawa (Manatū). Hoki whārite rārangi o te puka Y = te + b ₁ x _{1 + ... + b m x m +} ε hanga he pūnaha o ngā whārite noa (cm. Raro)

Hei mahino te parau tumu o te tikanga, whakaaro tatou te take e rua-tauwehe. Na kua tatou i te āhuatanga whakaahuatia e te tātai

No reira, whiwhi tatou:

te wahi σ - ko te rerekētanga o te āhuatanga tēnā, kitea i roto i te taupū.

He hāngai MNC ki ki te whārite MR standartiziruemom tauine. I roto i tenei take, whiwhi tatou i te whārite:

ai t _y, t _{x 1, ...} t _xm - standartiziruemye taurangi hoki e e 0 uara toharite; β _i - whakarea regression paerewa, me te ine mahora - 1.

Kia mōhio e β katoa _i i roto i tenei take tautuhia rite te tonu o me tsentraliziruemye, koia te rite i waenganui i te whakaaro tika, me te manakohia. I tua atu, kua manakohia reira ki te kawe i roto i ngā āhuatanga āta, whakareretanga te hunga e whai nga uara i raro o te βi.

Ko te raruraru ki te whakamahi i te whārite regression rārangi

Whakaaro whai koe i te tepu o nga akiakinga o te utu o te N ngā hua mo te 8 marama whakamutunga. Ko reira e tika ana ki te whakatau ranei te rironga o tona ope i te utu o 1850 moni. / T.

	He	B	C
1	te marama	ingoa koe o te marama	Price N
2	1	January	1750 moni ia ton
3	2	Hui-tanguru	1755 moni ia ton
4	3	Maehe	1767 moni ia ton
5	4	April	1760 moni ia ton
6	5	kia	1770 moni ia ton
7	6	Pipiri	1790 moni ia ton
8	7	Hōngongoi	1810 moni ia ton
9	8	August	1840 moni ia ton

Hei whakaoti i tēnei raruraru i roto i te pūtukatuka ripa "Excel" hiahiatia ki te whakamahi i mohiotia kua hoki tauira taputapu "Analysis Raraunga" aroaro i runga ake. Next, whiriwhiri "Hokinga" wāhanga me whakaturia tawhā. Me mahara tatou e i roto i te "ngā Tāuru kia Y» kia whakaurua ki te whānuitanga o ngā uara o te tāupe pararau (i roto i tenei take i te utu o nga taonga i roto i te marama motuhake o te tau) me i roto i te "Tāuru wā X» - mo te motuhake (te marama). whakau tatou i te mahi mā te pāwhiri «Āe». I roto i te raumahi hou (ki te pera tohua), whiwhi tatou i te raraunga mo te inenga.

Kei te hanga matou ki runga ki a ratou whārite rārangi o te tauira y = ax + b, te wahi i rite te tawhā he me b ko nga tau whakarea i te tau raina o te marama, me te ingoa o nga tau whakarea me Y-pūtahitanga aho «" o te whārangi ki te hua o te tātaritanga inenga o. Ko te kupu, e taea te tuhituhi i te whārite rārangi inenga o (EQ) 3 mo te raruraru rite:

Ko te utu o taonga N = 11,714 * 1727.54 marama tau +.

ranei i roto i te reo tohu taurangi

y = 11.714 x + 1727,54

tātaritanga o ngā hua

Hei whakatau mehemea te riro whārite inenga o āta rārangi whakamahi i te tau whakarea maha faatanoraa (CMC) me te faaotiraa me te whakamātau, me te t-whakamātautau o Fisher. I roto i te tepu "Excel" inenga ki te hua mahi ratou i raro i nga ingoa maha R, R-Square, F-t-tatauranga me tatauranga, aua.

KMC R āhei ki te whakatau tata i te taairaa piri hononga probabilistic i waenganui i ngā taurangi motuhake, me te ririki. Ko tōna uara nui tohu te nui hononga kaha i waenganui i te tāupe "Tau o te marama" me "N Hua utu i roto i moni ia 1 ton." Heoi, he mohiotia te āhua o tenei hononga.

Ko te tapawha o te tau whakarea o faaotiraa R ² (RI) ko te huru tatau o te rahi o te whakamararatia katoa, me te whakaatu he whakamararatia o wahi raraunga whakamātautau, i.e., uara o te tāupe ti'aturi e ōrite ana ki te whārite inenga rārangi. I roto i tenei raruraru, i tenei uara ko 84,8%, MP. E. Tatauranga ki te tohu nui o te tika whiwhi e whakaahuatia SD.

F-tatauranga, hoki e mohiotia ana ko Fisher paearu whakamahia ki te aromatawai i te hiranga o te fakafalala rārangi Te whakahē whakapae whakau tona oraraa ranei.

Ko te uara o te t-tauanga (t whakamātautau o te ākonga) tauturu arotake te hiranga o te tau whakarea i tetahi noa unknown mema fakafalala rārangi. Ki te te uara o te t-whakamātautau> t _cr, paopao te whakapae o te faufaa whārite rārangi o te wā free te.

I roto i tenei raruraru mo te wā kore utu i roto i mea "Excel" i kitea ai e t = 169,20903, me p = 2,89E-12, t. E. Kua he kore tūponotanga e ka paopao te pono te whakapae o te faufaa o te wā noa. Hoki whakarea unknown i t = 5,79405, me p = 0,001158. I roto i te mau parau te tahi atu, te tūponotanga e ka faufaa ore i te whakapae tika paopao o te tau whakarea mō te unknown, he 0.12%.

Ko te kupu, e taea te tohe i te reira e te whiwhi whārite inenga o rārangi tōtika.

Ko te raruraru o te paitanga o hoko hea

whakamana regression maha i roto i Excel te whakamahi i te taua "Analysis Raraunga" taputapu. A feruri i te tono motuhake.

Guide kamupene «NNN» Me whakatau mēnā ki te hoko 20% o hea o JSC «MMM». utu Mōkī (SP) he 70 miriona US tāra. Tohunga o «NNN» kohia raraunga i runga i ngā whakawhitinga ōrite. I whakaritea e te reira ki te aromatawai i te uara o hea i runga i taua tawhā, faaite i roto i mirioni o US tara, pēnei i:

• nama (VK);
• rōrahi rerekē ā-tau (whakapukepu);
• nama mai (VD);
• uara o ngā rawa pūmau (SOF).

I tua atu, te whakamahi i te nama utu o hinonga (w3 U) i roto i nga mano o US tāra.

Ko te tikanga tepu whakatau pūtukatuka Excel

Tuatahi e hiahia ana koe ki te waihanga i tētahi ripanga o ngā raraunga tāuru. Ko reira e whai ake:

Tō muri mai:

• pouaka karanga "tātaritanga raraunga";
• tīpakohia wāhanga "Hokinga";
• te matapihi "Tāuru wā ngā Y» whakahaere ti'aturi uara tāupe i tīwae G;
• pāwhiri i runga i te icon ki te pere whero ki te matau o te matapihi "Tāuru wā X», ka taratahi i runga i te whānuitanga pepa o uara katoa o tīwae B, C, D, F.

Mark te wāhi "pepamahi New" ka pāwhiri "Āe".

Tikina he tātaritanga inenga mō tenei mahi.

Ko te hua ako, me whakatau

"Kohikohi" häunga i te raraunga aroaro i runga i runga i te tepu whārangi whārite Excel pūtukatuka inenga:

SD = 0,103 * SOF + 0,541 * whakapukepu - 0.031 * VK + 0,405 * VD + 0,691 * VZP - 265.844.

I roto i te atu mua puka pāngarau taea te tuhituhi i te reira rite:

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

Raraunga mō «MMM» JSC aroaro i roto i te ripanga i raro nei:

SOF, USD	Whakapukepu, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102.5	535,5	45.2	41.5	21,55	64,72

Whakakapi ratou ki te whārite inenga o, i whiwhi i te ahua o 64,72 miriona US tāra. tikanga o tēnei e kore e hoko i te hea o JSC «MMM», no te mea kua overpriced rawa ratou utu i 70 miriona US tāra.

Ka rite ki a koutou e taea te kite, te whakamahi o te ripanga "Excel" me te whārite inenga o whakaaetia ki te hanga i te whakatau ngā pā ana i te ahua paitanga tauwhitinga tino motuhake.

Na e matau ana koutou ki ta te inenga. Tauira ki Excel, kōrero i runga ake, ka tauturu ia koe i roto i hei whakaoti rapanga whaitake o Econometrics.