Regression katika Excel: mifano equation. mwelekezo wa mstari

Regression uchambuzi - takwimu utafiti mbinu kuonyesha utegemezi wa parameter ya moja au zaidi wa kujitegemea variables. Katika zama za kabla ya kompyuta, matumizi yake imekuwa badala vigumu, hasa linapokuja suala la kiasi kikubwa cha data. Leo, kujifunza jinsi ya kujenga kurudi nyuma katika Excel, unaweza kutatua matatizo magumu takwimu katika dakika chache tu. Chini ni mifano maalum ya uchumi.

aina kurudi nyuma

Dhana hii ilianzishwa hisabati na Francis Galton katika 1886. Regression ni:

• linear,
• kimfano,
• nguvu;
• kielelezo;
• piambano;
• kielelezo;
• logarithmic.

MFANO 1

Fikiria tatizo la kuamua utegemezi wa idadi ya kujiuzulu ya wanachama wa wafanyakazi wa mshahara wa wastani katika 6 makampuni ya viwanda.

Kazi. Six makampuni kuchambuliwa wastani mshahara kila mwezi na idadi ya wafanyakazi ambao kuacha kwa hiari. Katika fomu tabular tuna:

	A	B	C
1	X	Idadi ya kujiuzulu	mshahara
2		y	30000 rubles
3	1	60	35000 rubles
4	2	35	40000 rubles
5	3	20	45000 rubles
6	4	20	rubles 50,000
7	5	15	55000 rubles
8	6	15	60000 rubles

Kwa tatizo la kuamua utegemezi wa wafanyakazi kiasi utenganishaji kutoka wastani wa mshahara kwa 6 makampuni kurudi nyuma mfano ina mfumo wa equation Y = ₀ + ₁ x ₁ + ... + _k x _k, ambapo x _i - kushawishi vigezo, _i - kurudi nyuma coefficients, ak - sababu kadhaa.

Y kwa ajili ya kazi fulani - ni kiashiria kwa moto mfanyakazi, inayochangia - mshahara, ambayo ni imeonyeshwa kwa X.

Kuimarisha nguvu ya "Excel" spreadsheet

Regression uchambuzi katika Excel lazima kutanguliwa na maombi ya data zilizopo meza kujengwa katika kazi. Hata hivyo, kwa madhumuni haya ni vizuri kutumia muhimu sana kuongeza katika "pakiti uchambuzi". Kuiwezesha, unahitaji:

• na tab "Picha" kwenda "Settings";
• katika dirisha kwamba kuufungua, kuchagua 'Viongezo';
• bonyeza kifungo "Go", iliyoko upande wa kulia chini ya mstari "usimamizi";
• kuweka alama ya kuangalia karibu na "Uchambuzi ToolPak" na kuthibitisha hatua yako kwa kubonyeza "OK".

Kama kufanyika kwa usahihi, upande wa kulia wa "Data" tab, iko juu chati inayosema "Excel", inaonyesha kifungo taka.

Linear Regression katika Excel

Sasa kwa kuwa una upande muhimu zana zote virtual kwa ajili ya hesabu econometric, tunaweza kuanza kushughulikia tatizo letu. Ili kufanya hivyo:

• kifungo ni clicked juu ya "Data Analysis";
• bonyeza kifungo "kurudi nyuma" katika dirisha wazi,
• tab kwamba inaonekana kuanzisha mbalimbali ya maadili Y (idadi ya wafanyakazi utenganishaji) na X (mshahara wao);
• kuthibitisha matendo yao kwa kubonyeza «Ok» button.

Matokeo yake, mpango moja kwa moja kujaza mpya ya jedwali spreadsheet data kurudi nyuma uchambuzi. Kuwa makini! Katika Excel, kuna fursa ya kuweka mahali kwamba unapendelea kwa kusudi hili. Kwa mfano, inaweza kuwa karatasi hiyo hiyo, ambapo maadili Y na X, au hata kitabu kipya, hasa iliyoundwa kwa ajili ya uhifadhi wa data hizo.

matokeo Regression uchambuzi kwa R-mraba

data Excel kupatikana katika data kuchukuliwa mfano na aina:

Kwanza kabisa, sisi inapaswa kuangalia kwa thamani ya R-squared. Inawakilisha mgawo wa uamuzi. Katika mfano huu, R-mraba = 0.755 (75.5%), m. E. vigezo mahesabu ya mfano kueleza uhusiano kati ya vigezo inafikiriwa na 75.5%. juu thamani ya mgawo wa uamuzi, mfano kuchaguliwa inachukuliwa kuwa muhimu zaidi kwa ajili ya kazi fulani. Inaaminika usahihi kuelezea hali halisi katika thamani R-mraba juu 0.8. Kama R-mraba <0.5, basi uchambuzi wa kurudi nyuma katika Excel hawezi kuwa ni ya kuridhisha.

uchambuzi uwiano

Idadi 64.1428 inaonyesha nini itakuwa thamani ya Y, kama vigezo vyote xi katika mfano wetu kuwekwa upya. Kwa maneno mengine, inaweza kuwa alisema kuwa thamani ya parameter kuchambuliwa inaathiriwa na mambo mengine kuliko wale ilivyoelezwa katika mfano maalum.

Sababu ya pili -.16285 iliyoko B18 mkononi, inaonyesha ushawishi muhimu ya kutofautiana X Y. Hii ina maana kwamba mshahara wa wastani wa wafanyakazi ndani ya mfano huathiri idadi ya kujiuzulu kutoka uzito wa -.16285, t. E. kiasi cha madhara yake wakati wote ndogo. ishara "-" inaonyesha kwamba mgawo ni hasi. Ni dhahiri, kwa kuwa sisi wote tunajua kwamba mshahara zaidi katika biashara, watu chini kuwa walionyesha hamu ya kusitisha mkataba wa ajira au kufukuzwa kazi.

kurudi nyuma mbalimbali

Chini ya muda huu inahusu equation mawasiliano na vigezo kadhaa wa kujitegemea wa fomu:

y = f (x ₁ + x ₂ + ... x _m) + ε, ambapo y - ni kipengele alama (tegemezi variable), na x _1, x _2, ... x _m - dalili ya mambo (huru vigezo).

parameter makadirio

Kwa mbalimbali kurudi nyuma (MR) ni kazi kwa kutumia mbinu ya miraba ndogo (LSM). Kwa milinganyo mstari wa fomu Y = a + b ₁ x _{1 + ... + b m x m +} ε kujenga mfumo wa equations kawaida (cm. Hapa chini)

Ili kuelewa kanuni ya njia, tunaona kesi mbili sababu. Kisha sisi hali ilivyoelezwa na formula

Kwa hiyo, sisi kupata:

ambapo σ - ni ugomvi wa kipengele husika, yalijitokeza katika ripoti.

MNC zinatumika kwa equation MR kwa standartiziruemom wadogo. Katika hali hii, sisi kupata equation:

ambayo t _y, t _{x 1, ...} t _XM - standartiziruemye vigezo ambayo wastani wa thamani ni 0, β _i - sanifu kurudi nyuma coefficients na mkengeuko wa kawaida - 1.

Tafadhali kumbuka kuwa kila β _i katika kesi hii hufafanuliwa kama kawaida na tsentraliziruemye, kwa hiyo kulinganisha kati ya kuchukuliwa halali na kukubalika. Aidha, ni limekubaliwa kufanya uchunguzi wa sababu, na kutupa wale ambao thamani za chini kabisa za βi.

tatizo na kutumia mwelekezo wa mstari equation

Tuseme una meza wa mienendo ya bei ya bidhaa fulani N miezi 8 iliyopita. Ni muhimu kuamua kama upatikanaji wa chama chake kwa bei ya 1850 rubles. / T.

	A	B	C
1	mwezi	jina ya mwezi	bei N
2	1	Januari	1750 rubles kwa tani
3	2	Februari	1755 rubles kwa tani
4	3	Machi	1767 rubles kwa tani
5	4	April	1760 rubles kwa tani
6	5	Mei	1770 rubles kwa tani
7	6	Juni	1790 rubles kwa tani
8	7	Julai	1810 rubles kwa tani
9	8	Agosti	1840 rubles kwa tani

Ili kutatua tatizo hili katika tabular processor "Excel" inahitajika kutumia tayari anajulikana kwa mfano chombo "Data Analysis" iliyotolewa hapo juu. Kisha, kuchagua "Regression" sehemu na kuweka vigezo. Ni lazima tukumbuke kwamba katika "Input mbalimbali Y» lazima kuletwa kwa masafa ya kutofautiana tegemezi (katika kesi hii bei ya bidhaa katika miezi maalum ya mwaka) na katika "Input muda X» - kwa huru (mwezi). Sisi kuthibitisha hatua kwa kubonyeza «Ok». Katika karatasi mpya (kama ni hivyo imeelezwa), sisi kupata data kwa ajili ya kurudi nyuma.

Sisi ni kujenga juu yao linear equation ya fomu y = Shoka + b, ambapo kama vigezo na b ni coefficients kutoka kwenye mstari wa idadi ya mwezi na jina la coefficients na «Y-makutano" mstari wa karatasi na matokeo ya uchambuzi wa kurudi nyuma. Hivyo, mwelekezo wa mstari equation (EQ) 3 kwa ajili ya tatizo inaweza kuandikwa kama:

bei ya bidhaa N = 11,714 * 1,727.54 mwezi idadi +.

au katika nukuu algebraic

y = 11,714 x + 1727,54

uchambuzi wa matokeo

Kuamua kama kupokea kutosha mwelekezo wa mstari mlingano wa coefficients mbalimbali uwiano (CMC) na uamuzi na pia mtihani na Fisher wa t mtihani. Katika jedwali "Excel" kurudi nyuma na matokeo nao wanafanya chini ya majina mbalimbali R, R-Square, F-t-takwimu na takwimu, kwa mtiririko huo.

KMC R itawezesha kukadiria ukaribu na uhusiano kubahatisha kati ya vigezo huru na tegemezi. thamani yake ya juu inaonyesha nguvu ya uhusiano wa kutosha kati ya tofauti tofauti "Idadi ya mwezi" na "N bei za bidhaa katika rubles kwa tani 1". Hata hivyo, hali ya uhusiano huu ni haijulikani.

mraba wa mgawo wa uamuzi R ² (RI) ni tabia kinambari wa uwiano wa jumla kutawanya na inaonyesha kutawanya wa sehemu ya majaribio data, yaani, thamani za kutofautiana tegemezi sambamba na mwelekezo wa mstari equation. Katika tatizo hili, thamani hii ni 84.8%, Mbunge. E. Takwimu na shahada ya juu ya usahihi kupatikana yameelezwa SD.

F-takwimu, pia inajulikana kama Fisher kigezo kutumika kupima umuhimu wa utegemezi linear au kukanusha nadharia ya kuthibitisha kuwepo kwake.

Thamani ya t-statistic (Mwanafunzi t mtihani) husaidia kutathmini umuhimu wa mgawo katika bure haijulikani linear yeyote utegemezi. Kama thamani ya t-test> t _cr, nadharia tete ya linear equation udogo ya bure mrefu limekataliwa.

Katika tatizo hili kwa ajili ya bure mrefu kupitia vyombo "Excel" ilibainika kuwa t = 169,20903, na p = 2,89E-12, t. E. Kuwa na sifuri uwezekano kwamba waamini itakataliwa nadharia tete ya udogo ya bure mrefu. Kwa mgawo haijulikani katika t = 5,79405, na p = 0,001158. Kwa maneno mengine, uwezekano kwamba kukataliwa sahihi nadharia itakuwa udogo wa mgawo kwa haijulikani, ni 0.12%.

Hivyo, inaweza kuwa alisema kuwa kupatikana mwelekezo wa mstari equation vya kutosha.

tatizo la intresset ya kununua hisa

Nyingi kurudi nyuma alikuwa akifanya katika Excel anayetumia "Data Analysis" chombo. Fikiria maombi maalum.

Guide kampuni «NNN» lazima kuamua kama kununua 20% ya hisa ya JSC «MMM». Mfuko bei (SP) ni milioni 70 dola za Marekani. Wataalamu wa «NNN» zilizokusanywa data juu ya shughuli hiyo. Iliamuliwa kutathmini thamani ya hisa katika vigezo vile, walionyesha katika mamilioni ya dola za Marekani, kwa mfano:

• Madeni ya kibiashara (VK);
• mauzo ya kila mwaka kiasi (VO);
• receivables (VD);
• thamani ya mali ya kudumu (SOF).

Aidha, matumizi ya madeni mshahara wa makampuni (V3 U) katika maelfu ya dola za Marekani.

uamuzi meza processor Excel njia

Kwanza unahitaji kujenga meza ya data ya ingizo. Ni kama ifuatavyo:

next:

• wito sanduku "Uchambuzi data",
• kuchaguliwa "Regression" sehemu,
• dirisha "Input muda Y» kusimamiwa mbalimbali tegemezi variable maadili kutoka safu G;
• bonyeza icon na mshale nyekundu na haki ya dirisha "Input muda X» na kutengwa kwenye karatasi ya thamani za wote wa safu B, C, D, F.

Mark uhakika "New karatasi ya" na bonyeza "Ok".

Kupata kurudi nyuma uchambuzi kwa kazi hii.

matokeo ya utafiti na hitimisho

"Kusanya" mviringo kutoka data iliyotolewa hapo juu kwenye karatasi meza Excel processor kurudi nyuma equation:

SD = 0.103 * SOF + 0541 * Vo - 0031 * VK + 0405 * VD + 0691 * VZP - 265844.

Katika zaidi ya kawaida aina ya hisabati inaweza kuandikwa kama:

y = 0103 * x1 + 0541 * x2 - 0031 * x3 + 0405 * x4 + 0691 * x5 - 265844

Data kwa ajili ya «MMM» JSC iliyotolewa katika jedwali hapa chini:

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

Kugeuza yao katika kurudi nyuma equation, kupatikana takwimu ya dola za Marekani milioni 64.72. Hii ina maana kwamba hisa ya JSC «MMM» haipaswi kununua, kwa sababu gharama zao ni kabisa overpriced katika dola za Marekani milioni 70.

Kama unavyoona, matumizi ya spreadsheet "Excel" na kurudi nyuma equation kuruhusiwa kufanya maamuzi sahihi kuhusu intresset shughuli maalum kabisa.

Sasa unajua nini kurudi nyuma. Mifano kwa Excel, kujadiliwa hapo juu, itasaidia katika kutatua matatizo ya vitendo uchumukihisabati.