Regression muna Excel: mukana wokuti vanhu vanga mienzaniso. Linear regression

Regression ongororo - a Statistical kudzidza nzira kuratidza kuvimba mumwe parameter mumwe kana kupfuura akazvimirira variables. Mugore pre-kombiyuta nguva, kushandiswa kwayo kwave nani zvakaoma, kunyanya kana asvika guru zvemaBhaibheri mashoko. Nhasi, kudzidza kuvaka regression muna Excel, unogona kugadzirisa yakaoma Statistical matambudziko mumaminitsi mashomanana. Pazasi mienzaniso chaiyo dzezvoupfumi.

regression mhando

Pfungwa iyi akatanga kuita masvomhu kubudikidza Francis Galton muna 1886. Regression ndiko:

• Linear;
• parabolic;
• simba;
• exponential;
• hyperbolic;
• exponential;
• theorem.

MUENZANISO 1

Funga dambudziko kuziva nedoro iri nhamba resignations dzinotambirwa mudonzvo avhareji mubayiro muna 6 maindasitiri mabhizimisi.

Task. Makambani zvitanhatu vakaongorora paavhareji pamwedzi muhoro uye uwandu vashandi vakanga kusiya nokuzvidira. In tabular chimiro tine:

	A	B	C
1	X	Uwandu resignations	muhoro
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

Nokuti dambudziko kuziva nedoro uwandu yokuparadzana vashandi kubva avhareji muhoro nokuda 6 emakambani regression womuenzaniso ane chimiro mukana wokuti vanhu vanga Y = ane ₀ + ₁ x ₁ + ... + _{munhu, k} X _K, apo X _Ini - achifurira variables, mumwe _ini - regression coefficients, AK - zvinhu zvinoverengeka.

Y nokuti vakapiwa basa - ndiko nechiidzo pamoto mushandi, tive chinhu - the muhoro, izvo runotaurwa X.

Harnessing simba "Excel" spreadsheet

Regression kuongorora muna Excel anofanira pakurukurwa kushanda kuti aivapo tafura Data akavaka-mu mabasa. Zvisinei, zvinangwa izvi zviri nani kushandisa chaizvo kuwedzera-mu "packet kuongorora". Kuti ukwanise izvozvo, unofanira:

• pamwe Tab "File" endai "Settings";
• pahwindo kuti anovhura, sarudza 'Wedzerai-Ons';
• tinya bhatani "Enda", riri pazasi rworudyi mutsetse "utariri";
• isa maki padivi "Analysis ToolPak" uye musimbise kuita kwako nokutsikirira "OK".

Kana waita zvakarurama, kurutivi rworudyi rweimba "Data" Tab, riri pamusoro basa jira "Excel", inoratidza achida bhatani.

Linear Regression muna Excel

Zvino kuti paruoko ose anodiwa rakapotsa zvishandiso kuti econometric Masvomhu, tinogona kutanga kugadzirisa dambudziko redu. Kuti aite izvi,

• bhatani iri rarira pamusoro "Data Analysis";
• tinya bhatani "regression" pakachena hwindo;
• mumwe Tab sokuti ataure zvakawanda tsika Y (nhamba yokuparadzana vashandi) uye X (mari yavo);
• mumutsiridze zviito zvavo vachisundira «Ok» bhatani.

Somugumisiro, Chirongwa vachangoerekana kuzadza idzva jira spreadsheet Data regression kuongorora. Teererai! In Excel, pane mukana kuisa kunzvimbo Unosarudza kuti chinangwa ichi. Somuenzaniso, zvingava zvakafanana jira, apo tsika Y uye X, kana kunyange idzva bhuku, akanyorerwa kuchengetera mashoko akadaro.

Regression kuongorora zvawanikwa R akaenzana

The Excel date vakawana muna vaiona muenzaniso Data vane chimiro:

Chokutanga, tinofanira kuteerera kukosha R-mativi mana akaenzana. Zvinomirira coefficient pakutsunga. In muenzaniso uyu, R-mativi = 0.755 (75,5%), m. E. The vakaverenga parameters ari muenzaniso kutsanangura hukama pakati parameters runoonekwa 75,5%. The yakakwirira kukosha kwechibayiro coefficient pakutsunga, muenzaniso vakasarudzwa runoonekwa kuti zvikuru kuti zvikuru mabasa. Zvinonzi vakatenda kuti zvakarurama kurondedzera mamiriro ezvinhu chaiwo panguva R-mativi ukoshi pamusoro 0,8. Kana R-mativi <0.5, zvadaro regression kuongorora muna Excel haagoni kunzi musoro.

reshiyo ongororo

Number 64,1428 zvinoratidza chii chichava kukosha Y, kana vose variables XI mu watinotevedzera kuchava Reset. Mune mamwe manzwi, zvinganzi nharo kuti kukosha vakaongorora parameter zvinokonzerwa zvimwe zvinhu kupfuura avo inorondedzerwa chaiyo womuenzaniso.

Akatevera chinhu -0,16285 iri muchitokisi B18, inoratidza kukosha kwomudzimu shanduka X kuti Y. Izvi zvinoreva kuti paavhareji muhoro revashandi mukati muenzaniso kunokanganisa uwandu resignations kubva uremu -0,16285, t. E. Mwero ayo aaiitawo zvachose diki. The chiratidzo "-" rinoratidza kuti coefficient akaipa. Zviri pachena, sezvo tose tinoziva kuti zvakawanda muhoro iri zvebhizimisi, kashoma vanhu vakaratidza chido kugumisa chibvumirano basa kana yapera.

vakawanda regression

Pasi rino izwi rinoreva kukurukurirana mukana wokuti vanhu vanga vanoverengeka akazvimirira variables ari fomu:

Y = d (x ₁ + X ₂ + ... X _m) + ε, apo, y - chinhu chawo mamakisi (the zvaanoda shanduka), uye x _1, X _2, ... X _M - tiri zviratidzo zvipi (akazvimirira variables).

parameter sezvaunotara iwe

Nokuti vakawanda regression (MR) zviri kwakaita kushandisa zvinenge dzinoungana nzira (LSM). For Linear equations ari fomu Y = munhu + b ₁ x _{1 + ... + b M X M +} ε kuvaka kwegadziriro kwomuzvarirwo equations (cm. Below)

Kuti tinzwisise nheyo nzira, isu funga vaviri-chinhu nyaya. Zvadaro hatina mamiriro ezvinhu anotsanangurwa nomunyori fomura

Nokudaro, tinowana:

apo σ - ndiye makakava pakati nhengo chikabviswa, mumatauriro indekisi.

MNC ndiye kushandiswawo mukana wokuti vanhu vanga MR kuti standartiziruemom akamboona. Muchiitiko chino, tinowana mukana wokuti vanhu vanga:

makuri t _Y, t _{X 1, ...} t _xm - standartiziruemye variables iyo avhareji tsika dziri 0; β _Ini - kwezvipimo regression coefficients uye mwero kutsauka - 1.

Tapota cherechedza kuti zvose β _Ini iyi nyaya rinorondedzerwa so normalized uye tsentraliziruemye, saka nokuenzanisa pakati vaiona musoro uye zvinogamuchirwa. Uyezve, chipo chinogamuchirwa kuita kuongorora zvinhu, kurasa avo vane yakadereresa netsika βi.

Dambudziko vachishandisa Linear regression mukana wokuti vanhu vanga

Ngatitii une patafura simba pamusoro mutengo chigadzirwa N nezveimwe yokupedzisira 8 months. Zvakakodzera kusarudza kuzviwanira bato wake mutengo 1850 rubles. / T.

	A	B	C
1	mwedzi	zita mwedzi	Price N
2	1	Ndira	1750 rubles por tani
3	2	Kukadzi	1755 rubles por tani
4	3	Kurume	1767 rubles por tani
5	4	Kubvumbi	1760 rubles por tani
6	5	Chivabvu	1770 rubles por tani
7	6	Chikumi	1790 rubles por tani
8	7	Chikunguru	1810 rubles por tani
9	8	Nyamavhuvhu	1840 rubles por tani

Kugadzirisa dambudziko iri riri tabular processor "Excel" kunodiwa kushandisa kare kuzivikanwa somuenzaniso mudziyo "Data Analysis" kukurukurwa pamusoro. Next, sarudza "Regression" chikamu uye akaisa parameters. Tinofanira kurangarira kuti mu "Inzwa Zvinotaurwa siyana Y» inofanira kuziviswa kuti siyana netsika zviri zvaanoda shanduka (mu iyi mutengo nhumbi dziri zvakananga pamwedzi yegore) uye "Inzwa Zvinotaurwa penguva X» - nokuti yakazvimirira (mwedzi). We asimbise chakaitwa nekudzvanya «Ok». Mune pepa itsva (kana akaratidza saka), tinowana mashoko anenge ari regression.

Tiri kuvaka pamusoro pavo Linear mukana wokuti vanhu vanga vari fomu and = demo + b, apo sezvakaita parameters ane uye, b ndizvo coefficients kubva mutsetse nhamba romwedzi uye zita coefficients uye «Y-mharadzano" mutsara jira nemigumisiro yacho regression kuongorora. Saka Linear regression mukana wokuti vanhu vanga (EQ) 3 nokuti dambudziko anogona kunyorwa se:

The mutengo kwenhumbi N = 11,714 * 1727.54 mwedzi nhamba +.

kana mune algebraic notation

Y = 11.714 X + 1727,54

Kuongororwa kuti zvabuda

Kuti vasarudze kana kugamuchira zvakakwana Linear regression mukana wokuti vanhu vanga uchishandisa vakawanda kuwirirana coefficients (CMC) uye kutsunga uyewo bvunzo uye Fisher kuti t-bvunzo. In patafura "Excel" regression nemigumisiro vanoita pasi mazita akawanda R, R-Square, F-t-nhamba uye nhamba, ukuwo.

KMC R inoita kuti kufungidzira pedyo probabilistic ukama pakati akazvimirira uye nemataundi variables. hwaikosha Its rinoratidza simba kubatana zvakakwana pakati shanduka "Nhamba romwedzi" uye "N Product mutengo rubles muzana 1 tani." Zvisinei, hunhu ukama ichi harizivikanwi.

The mativi ose coefficient pakutsunga R ² (anul) iri Numeric hunhu nhamba yose uparadzire uye inoratidza uparadzire pakati experimental date mugove, i.e., zvawanikwa zvaanoda shanduka unoenderana munhu Linear regression mukana wokuti vanhu vanga. In dambudziko iri, ukoshi iri 84,8%, MP. E. Statistics ane anokudzwa zvakarurama vakawana vanorondedzerwa SD.

F-nhamba, aizivikanwawo Fisher chokuratidza inoshandiswa kuongorora zvinorehwa Linear nedoro kana kwepfungwa pfungwa achisimbisa kuvapo kwayo.

Kukosha t-nhamba (Wemudzidzi T bvunzo) inobatsira kuyera zvinomirirwa coefficient chero vakasununguka vasingazivikanwi Linear kuyanzva nhengo. Kana kukosha t-bvunzo> t _Kr, pfungwa imwe Linear mukana wokuti vanhu vanga chisingakoshi rusununguko izwi parinorambwa.

In dambudziko iri kuti vakasununguka izwi kuburikidza zviridzwa "Excel" zvakanzi vakawana kuti t = 169,20903, uye p = 2,89E-12, t. E. Ivai razero mukana kuti vakatendeka kucharambwa pfungwa yacho chisingakoshi wakasununguka izwi. For isingazivikanwi coefficient pa t = 5,79405, uye p = 0,001158. Nemamwe mashoko, mukana kuti akarambwa akarurama pfungwa kuchaita chisingakoshi pamusoro coefficient nokuda isingazivikanwi, ndiyo 0,12%.

Saka, zvinogona vakati vakawana Linear regression mukana wokuti vanhu vanga zvakakwana.

The dambudziko rinenge ratangwa rinofanira kupera kutenga mugove

Multiple regression kwakaitwa muna Excel vachishandisa chete "Data Analysis" mudziyo. Funga chaicho kushanda.

Guide kambani «NNN» anofanira kusarudza kuti vatenge 20% migove kuna JSC «MMM». Pasuru mutengo (SP) ndiyo mamiriyoni 70 US emadhora. Vanachiremba kuti «NNN» dzakaunganidza mashoko zvakafanana kwema. Kwakasarudzwa kuongorora kukosha anogoverana pamusoro parameters zvakadaro, chinoratidzwa mamiriyoni US emadhora, yakadai se:

• payables (VK);
• pagore turnover vhoriyamu (Võ);
• receivables (VD);
• kukosha pazvinhu yesangano (SOF).

Uyezve, kushandisa mubayiro zvikwereti akaitwa (V3 U) mune zviuru US zvemadhora.

Chisarudzo tafura processor Excel zvinoreva

First unofanira tafura chiyamuro Data kusika. Ndizvo sezvinotevera:

Next:

• kudanwa bhokisi "mashoko ongororo";
• vakasarudzwa "Regression" chikamu;
• hwindo "Inzwa Zvinotaurwa penguva Y» raitongwa Range zvaanoda shanduka tsika kubva chomupepanhau G;
• tinya icon ane dzvuku museve kurudyi nepahwindo "Inzwa Zvinotaurwa penguva X» uye on jira siyana netsika dzose mbiru B, C, D, F.

Mark pfungwa "New pepa" uye baya "Ok".

Kutsvaga regression kuongorora kuti basa iri.

Kudzidza zvabuda uye mhedziso

"Unganidzai" vakagumisa kubva mashoko akauya pamusoro jira patafura Excel processor regression mukana wokuti vanhu vanga:

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

In zvikuru dzose kwemasvomhu chimiro inogona zvakanyorwa se:

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

Data nokuti «MMM» JSC dzaitaurwa tafura pazasi:

SOF, USD	Võ, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102.5	535,5	45,2	41,5	21,55	64,72

Dzinomiririra muchivigiro regression mukana wokuti vanhu vanga, vakawana munhu mamiriyoni 64,72 US emadhora. Izvi zvinoreva kuti migove kuna JSC «MMM» haafaniri kutenga, nekuti mutengo kwavo zvikuru overpriced mamiriyoni 70 US emadhora.

Sezvaunogona kuona, kushandiswa spreadsheet "Excel" uye regression mukana wokuti vanhu vanga kubvumirwa kuita chisarudzo pamusoro rinenge ratangwa rinofanira kupera zvikuru chaizvo mutengapwe.

Zvino munoziva kuti regression. Mienzaniso kuna Excel, kunokurukurwa kumusoro, achakubatsira kugadzirisa matambudziko hunobatsira econometrics.