Komawa da baya a Excel: lissafi misalai. Ƙirgar komawa da baya

Komawa da baya analysis - mai ilimin kididdiga nazari Hanyar nuna da dogara da wani siga da daya ko fiye m canji. A cikin pre-kwamfuta zamanin, ta yin amfani ya kasance wajen wuya, musamman a lokacin da ta je babban kundin bayanai. Yau, koyo yadda za a gina wani komawa da baya a Excel, za ka iya shirya hadaddun ilimin kididdiga matsaloli a kawai 'yan mintoci. Kasa ne takamaiman misalai na tattalin arziki.

komawa da baya iri

Wannan ra'ayi da aka gabatar lissafi da Francis Galton a 1886. Komawa da baya ne:

• Ƙirgar.
• parabolic.
• ikon;
• karuwa da sauri.
• hyperbolic.
• karuwa da sauri.
• logarithmic.

MISALI 1

Ka yi la'akari da matsalar da kayyade dogara da yawan resignations na membobin ma'aikatan na talakawan ijara a cikin 6 masana'antu Enterprises.

Task. Shida kamfanoni sun bincikar da talakawan albashi da kuma yawan ma'aikata wanda sallama aikin. A tabular form muna da:

	A	B	C
1	X	Number of resignations	albashi
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	55000 rubles
8	6	15	60000 rubles

Ga matsalar da kayyade dogara da adadin separations ma'aikata daga cikin talakawan albashi ga 6 Enterprises komawa da baya model yana da nau'i na lissafi Y = wani ₀ + wani ₁ x ₁ + ... + mai _k x _k, inda x _i - influencing canji, a _i - komawa da baya coefficients, AK - yawan abubuwan.

Y ga wani ba aiki - shi ne mai nuna alama fire wani ma'aikaci, a bayar da tasu gudunmuwar factor - albashi, wanda aka denoted da X.

Harnessing ikon "Excel" falle

Komawa da baya analysis a Excel ya kamata a wadda ta gabãta daga wani aikace-aikace zuwa data kasance tebur data gina-in ayyuka. Duk da haka, domin wadannan dalilai shi ne mafi alhẽri amfani da wani amfani sosai add-a "fakiti bincike". Don a kunna shi, kana bukatar ka:

• tare da tab "File" je "Settings".
• a cikin taga cewa ya buɗe, zaɓi 'Add-kan'.
• click a kan button "Ku tafi", dake a kasa dama daga cikin layin "management".
• sa rajistan alamar kusa da "Analysis ToolPak" da kuma tabbatar da mataki ta hanyar latsa "Ok".

Idan yi daidai, gefen dama na "Data" tab, located sama da aikin takardar "Excel" zai zama wani button.

Ƙirgar komawa da baya a Excel

Yanzu cewa kana da a hannunka duk da dole mai rumfa kayayyakin aiki, don econometric lissafin, za mu iya fara don magance mu da matsala. Don yin wannan:

• button ne ya danna kan "Data Analysis".
• click a kan button "komawa da baya" a cikin bude taga.
• wani shafin da ya bayyana don gabatar da wani fanni na dabi'u Y (yawan separations ma'aikata) da kuma X (su albashi).
• jaddada ayyukansu ta latsa «Ok» button.

A sakamakon, wannan shirin za ta atomatik cika sabon takardar falle data komawa da baya analysis. Kula! A Excel, akwai wata damar kafa wurin da ka fi son don wannan manufa. Alal misali, yana iya zama wannan takardar, inda dabi'u Y da kuma X, ko ma wani sabon littafi, musamman tsara don ajiya na wannan bayanai.

Komawa da baya analysis results for R-square

A Excel data samu a dauke misali data da siffar:

Da farko, ya kamata mu kula da darajar R-Squared. Yana wakiltar coefficient na shiba. A wannan misali, R-square = 0.755 (75,5%), m. E. The lasafta sigogi na model bayyana dangantaka tsakanin sigogi dauke da 75,5%. A mafi girma da darajar da coefficient na tabbatar da dalilin, da aka zaɓa model an dauke su more da amfani ga musamman ayyuka. An yi imani da su daidai bayyana hakikanin halin da ake ciki a R-square darajar sama 0.8. Idan R-square <0.5, sa'an nan a komawa da baya analysis a Excel ba za a dauke m.

rabo analysis

Number 64,1428 ya nuna abin da zai zama da darajar da Y, idan duk da canji Xi a mu koyi zai zama sake saiti. A wasu kalmomin, shi za a iya bayar da hujjar cewa darajar da bincikar siga ne ya rinjayi wasu dalilai fiye da wadanda aka bayyana a cikin takamaiman model.

A gaba factor -0,16285 located in cell B18, ya nuna muhimmanci da tasiri na m X zuwa Y. Wannan yana nufin cewa talakawan albashi na ma'aikatan a cikin model rinjayar da yawan resignations daga nauyi na -0,16285, t. E. A mataki na da tasiri a duk kananan. The alamar "-" na nuna cewa coefficient ne korau. Babu shakka, tun da muka san cewa mafi albashi a cikin sha'anin, da kasa mutane sun bayyana kwadayin karbi kwangila na aiki, ko sallame.

mahara komawa da baya

A karkashin wannan lokaci tana nufin sadarwar lissafi da dama m canji na form:

y = f (x ₁ + x ₂ + ... x _m) + ε, inda y - ne mai alama ci (da dogara da m), da kuma x _1, x _2, ... x _m - akwai ãyõyi dalilai (m canji).

siga hakkin

Domin mahara komawa da baya (MR) shi aka yi amfani da wani kalla murabba'ai Hanyar (LSM). Domin mikakke lissafai na form Y = wani + b ₁ x _{1 + ... + b m x m +} ε gina tsarin na al'ada lissafai (cm. Ƙasa)

Don gane da manufa na hanyar, mun yi la'akari da biyu-factor hali. Sa'an nan mun halin da ake ciki da aka bayyana ta da dabara

Saboda haka, da muka samu:

inda σ - ne sãɓã wa jũna, na Game da alama, nuna a cikin index.

MNC ne m zuwa lissafi MR zuwa standartiziruemom sikelin. A wannan yanayin, muna samun da lissafi:

a cikinsa t _y, t _{x 1, ...} t _xm - standartiziruemye canji ga wanda talakawan dabi'u ne 0. β _i - daidaita komawa da baya coefficients da kuma misali sabawa - 1.

Lura cewa duk β _i a wannan harka da aka ayyana a matsayin bisa al'ada da kuma tsentraliziruemye, saboda haka a kwatanta tsakanin mai dauke da inganci da kuma m. Bugu da kari, shi ne yarda da a gudanar da nunawa dalilai, yin watsi da wadanda suke da mafi ƙasƙanci dabi'u na βi.

Matsalar da yin amfani da mikakke komawa da baya lissafi

Misali kana da wani tebur na ƙarfafa muhimmancin farashin wani samfurin N for karshe 8 months. Wajibi ne a yanke shawarar ko da saye da jam'iyyar a farashin 1850 rubles. / T.

	A	B	C
1	watan	sunan watan	price N
2	1	Janairu	1750 rubles da ton
3	2	Fabrairu	1755 rubles da ton
4	3	Maris	1767 rubles da ton
5	4	Afrilu	1760 rubles da ton
6	5	Mayu	1770 rubles da ton
7	6	Yuni	1790 rubles da ton
8	7	Yuli	1810 rubles da ton
9	8	Agusta	1840 rubles da ton

Don warware wannan matsala a cikin tabular processor "Excel" bukata don amfani riga aka sani misali kayan aiki "Data Analysis" ya gabatar a sama. Next, zabi "komawa da baya" sashe da kuma kafa sigogi. Dole ne mu tuna cewa a cikin "Input kewayon Y» kamata a gabatar da wani kewayon dabi'u na dogara m (a cikin wannan harka da farashin da kayayyaki a cikin takamaiman watanni na shekara) da kuma a cikin "Input tazara X» - domin mai zaman kanta (watan). Mun tabbatar da mataki ta danna «Ok». A wani sabon worksheet (idan haka ya nuna), za mu samu bayanai ga komawa da baya.

Muna gina a kan su mikakke lissafi na form y = gatari + b, inda kamar yadda sigogi da kuma b ne coefficients daga line yawan watan da sunan coefficients da kuma «Y-mahada" line na takardar da sakamakon da komawa da baya analysis. Saboda haka, mikakke komawa da baya lissafi (Eq) 3 ga matsalar za a iya rubuta kamar:

The farashin kaya N = 11,714 * 1727.54 wata lambar +.

ko a algebraic tsarin rubutu

y = 11.714 x + 1727,54

bincike na da sakamakon

Don shirya ko samu mayalwaci mikakke komawa da baya lissafi amfani da mahara hulda coefficients (CMC) da kuma tabbatar da dalilin da gwajin da Fisher ta t-gwajin. A tebur "Excel" komawa da baya tare da sakamakon da suka yi aiki a karkashin sunayen mahara R, R-Square, F-t-statistics da kuma statistics, bi da bi.

KMC R damar zuwa kimanta da kusanci probabilistic dangantaka tsakanin zaman kanta da kuma dogara da canji. Its high darajar nuna wani karfi isa alaka tsakanin m "Number na watan" da "N Product farashin a rubles da 1 ton." Duk da haka, yanayin wannan dangantaka shi ne ba a sani ba.

A square na coefficient na tabbatar da dalilin R ² (RI) ne a Tazarar halayyar da rabo daga total watsa da kuma nuna wani watsa daga gwaji data rabo, Ina nufin, dabi'u na dogara m m zuwa mikakke komawa da baya lissafi. A wannan matsala, wannan darajar ne 84,8%, MP. E. Statistics da wani babban mataki na daidaito samu suna bayyana SD.

F-statistics, kuma aka sani da Fisher rarrabẽwa amfani tantance muhimmancin da mikakke dogara ko inkarin jarrabawa mai gaskatãwa ta zama.

The darajar t-wannan hasashen (Student ta t gwajin) taimaka kimanta da muhimmancin da coefficient a wani free unknown mikakke dogara memba. Idan darajar t-gwajin> t _cr, da jarrabawa na wani mikakke lissafi insignificance na free lokaci yana qaryata.

A wannan matsala domin a free lokaci ta hanyar kida "Excel" an gano cewa, t = 169,20903, kuma p = 2,89E-12, t. E. Da sifili yiwuwa cewa, masu aminci za a yi watsi da jarrabawa na insignificance na free lokaci. Domin ba a sani ba coefficient a t = 5,79405, kuma p = 0,001158. A wasu kalmomin, da yiwuwar cewa wani ƙaryata daidai bane zai insignificance na coefficient ga unknown, shi ne 0,12%.

Saboda haka, ana iya jãyayya da cewa samu mikakke komawa da baya lissafi mayalwaci.

Matsalar da advisability na sayen hannun jari

Mahara komawa da baya da aka yi a Excel yin amfani da wannan "Data Analysis" kayan aiki. Ka yi la'akari da takamaiman aikace-aikace.

Guide kamfanin «NNN» dole ne su yanke shawara ko sayan 20% na hannun jari na JSC «Mmm». Kunshin farashin (SP) ne 70 da dala miliyan. Kwararru daga «NNN» tattara bayanai a kan irin wannan ma'amaloli. Aka yanke shawarar tantance darajar hannun jari a kan wannan sigogi, bayyana, a miliyoyin dolar Amirka, kamar:

• payables (VK).
• shekara-shekara yawa girma (VO).
• receivables (VD).
• darajar gyarawa dukiya (SOF).

Bugu da kari, amfani da ijara basusuka na Enterprises (V3 U) a cikin dubban dalar Amurka.

A yanke shawara tebur processor Excel nufin

Da farko kana bukatar ka ƙirƙiri wani ginshiƙi tushen bayanan. Shi ne kamar haka:

Next:

• kira akwatin "data analysis".
• zaba "komawa da baya" sashe.
• da taga "Input tazara Y» gudanar da kewayon dogara m dabi'u daga shafi G;
• click a kan icon tare da ja kibiya da dama na window "Input tazara X» da kuma ware a kan takardar kewayon duk dabi'u na shafi B, C, D, F.

Mark batu "New worksheet" da kuma danna "Ok".

Samun wani komawa da baya analysis domin wannan aiki.

A binciken da sakamakon da karshe

"Tattara" taso keya daga data gabatar a sama a kan takardar tebur Excel processor komawa da baya lissafi:

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

A cikin mafi saba ilmin lissafi form shi za a iya rubuta kamar:

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

Data ga «Mmm» JSC gabatar a wadannan tebur:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	Jb, USD
102,5	535,5	45.2	41,5	21,55	64,72

Musanya su a cikin komawa da baya lissafi, samu wani adadi na 64,72 dalar Amurka miliyan. Wannan yana nufin cewa da hannun jari na JSC «Mmm» kamata ba saya, saboda su kudin da aka quite overpriced a 70 na dalar Amurka miliyan.

Kamar yadda ka gani, da yin amfani da falle "Excel" da kuma komawa da baya lissafi a yarda a yi wani sanar da yanke shawara game da advisability quite takamaiman ma'amala.

Yanzu ka san abin da a komawa da baya. Misalai ga Excel, tattauna a sama, zai taimake ku a warware m matsaloli na econometrics.