Regression nyob rau hauv Excel: kab zauv piv txwv. linear regression

Regression tsom xam - ib tug statistical txoj kev tshawb no txoj kev los qhia cov dependence ntawm ib tug parameter ntawm ib los yog ntau tshaj ywj siab ntau yam. Nyob rau hauv lub pre-computer era, nws siv tau es tsis yooj yim, tshwj xeeb tshaj yog thaum nws tuaj kom loj tagnrho ntawm cov ntaub ntawv. Niaj hnub no, kev kawm yuav ua li cas los tsim kom tau ib tug regression nyob rau hauv Excel, koj yuav kom daws tau txoj statistical teeb meem nyob rau hauv cia li ib tug ob peb feeb. Hauv qab no yog ib qho piv txwv ntawm economics.

regression hom

Cov tswvyim no twb nkag tau mus rau kev kawm txog zauv los ntawm Francis Galton nyob rau hauv 1886. Regression yog:

• linear;
• Parabolic;
• hwj chim;
• exponential;
• hyperbolic;
• exponential;
• logarithmic.

QAUV 1

Xav txog qhov teeb meem ntawm kev txiav txim nyob tos ntawm tus xov tooj ntawm resignations ntawm cov tswv cuab neeg ua hauj lwm ntawm qhov nruab nrab nyiaj nyob rau hauv lub 6 muaj qhauj.

Ua hauj lwm. Rau tuam txhab uas muag tau analyzed qhov nruab nrab txhua hli cov nyiaj hli thiab tus naj npawb ntawm cov neeg ua haujlwm uas tawm yeem. Nyob rau hauv tabular daim ntawv peb muaj:

	A	B	C
1	X	Number of resignations	cov nyiaj hli
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

Rau cov teeb meem ntawm kev txiav txim nyob tos ntawm tus nqi separations neeg ua hauj lwm los ntawm qhov nruab nrab cov nyiaj hli rau 6 qhauj regression qauv muaj rau hauv daim ntawv ntawm kab zauv Y = ₀ + ₁ x ₁ + ... + ib _k x _k, qhov twg x _i - influencing variables, ib tug _kuv - regression coefficients, ak - ntawm ntau yam.

Y rau ib tug muab neeg ua hauj lwm - nws yog ib qho taw qhia rau hluav taws ib tug neeg ua hauj lwm, ib tug pab tau - lub nyiaj hli, uas yog denoted los ntawm X.

Harnessing lub hwj chim ntawm "Excel" spreadsheet

Regression tsom xam nyob rau hauv Excel yuav tsum tau preceded by ib daim ntawv thov mus rau lub uas twb muaj lawm rooj ntaub ntawv ua-nyob rau hauv kev khiav dej num. Txawm li cas los, rau cov hom phiaj nws yog zoo dua rau siv ib tug tseem ceeb add-nyob rau hauv "pob ntawv tsom xam". Yuav kom pab nws, koj yuav tsum:

• nrog rau cov tab "Cov ntaub ntawv" mus rau "tej chaw";
• nyob rau hauv lub qhov rais uas qhib, xaiv 'Ntxiv-ons';
• nias rau lub khawm "Mus", nyob rau hauv qab txoj cai ntawm txoj kab "tswj";
• muab tso rau ib tug kos cim tom ntej no mus rau "Analysis ToolPak" thiab paub meej tias koj txiav txim los ntawm nias "OK".

Yog hais tias ua kom raug, rau sab xis ntawm lub "Cov ntaub ntawv" tab, nyob saum toj no ua hauj lwm daim ntawv "Excel", qhia tau hais tias cov kev kawm khawm.

Tawm Regression nyob rau hauv Excel

Tam sim no uas koj muaj nyob ntawm tes tag nrho cov tsim nyog virtual cov cuab yeej rau econometric suav, peb yuav tau pib mus rau qhov chaw peb teeb meem. Yuav kom ua li no:

• khawm yog clicked rau lub "Cov ntaub ntawv Analysis";
• nias rau lub khawm "regression" nyob rau hauv lub qhov rais qhib;
• ib tug tab uas zoo nkaus li yuav ua kom paub ib tug ntau ntawm qhov tseem ceeb Y (tus xov tooj ntawm separations neeg ua hauj lwm) thiab X (lawv cov nyiaj hli);
• rov lees lawv ua los ntawm nias lub «ua li» khawm.

Raws li ib tug tshwm sim, cov kev pab cuam yuav sau cov tshiab daim ntawv spreadsheet cov ntaub ntawv regression tsom. Xyuam xim! Nyob rau hauv Excel, muaj ib lub sij hawm teem rau qhov chaw uas koj xav rau lub hom phiaj no. Piv txwv li, tej zaum nws yuav tsum yog tib yam ntawv, qhov chaw uas lub qhov tseem ceeb Y thiab X, los sis txawm ib phau ntawv tshiab, yeej tsim los rau cov cia ntawm tej ntaub ntawv.

Regression tsom xam tau rau R-square

Lub Excel ntaub ntawv tau nyob rau hauv qhov suav hais tias piv txwv li cov ntaub ntawv muaj rau hauv daim ntawv:

Ua ntej ntawm tag nrho cov, peb yuav tsum xyuam xim rau cov nqi ntawm R-squared. Nws nruab nrab yog cov coefficient ntawm kev txiav txim. Nyob rau hauv qhov kev piv txwv, R-square = 0.755 (75,5%), m. E. Lub xam tsis ntawm tus qauv los mus piav qhia cov kev sib raug zoo ntawm cov tsis xam los ntawm 75,5%. Lub siab dua tus nqi ntawm cov coefficient ntawm kev txiav txim, lub xaiv qauv yog pom tias yuav pab tau ntau rau kev pab raws qib. Nws yog ntseeg hais kom raug piav qhia txog qhov tiag tiag qhov teeb meem no nyob rau hauv cov R-square nqi saum toj no 0.8. Yog hais tias lub R-square <0.5, ces ib tug regression tsom xam nyob rau hauv Excel yuav tsis muab suav hais tias tsim nyog.

piv tsom xam

Number 64,1428 qhia tau hais tias dab tsi yuav tsum yog tus nqi ntawm cov Y, hais tias tag nrho lub zog xi nyob rau hauv peb cov qauv yuav rov pib dua. Nyob rau hauv lwm yam lus, nws yuav tau sib cav hais tias tus nqi ntawm cov analyzed parameter yog los ntawm lwm yam tshaj li cov neeg piav nyob rau hauv cov kev qauv.

Cov tom ntej no qhov zoo tshaj -0,16285 nyob rau hauv cell B18, qhia tau hais tias qhov tseem ceeb tus ntawm nce mus nce los X rau Y. Qhov no txhais tau tias qhov nruab nrab cov nyiaj hli ntawm neeg ua hauj lwm rau hauv tus qauv muaj feem xyuam rau tus xov tooj ntawm resignations los ntawm tus luj ntawm -0,16285, t. E. Lub degree ntawm nws feem ntawm tag nrho cov me me. Qhov kos npe rau "-" qhia hais tias tus coefficient yog tsis zoo. Nws yog ib cuab kev, vim peb yeej paub tias qhov ntau cov nyiaj hli nyob rau hauv lub enterprise, lub tsawg tus neeg tau qhia ib tug lub siab xav mus txiav daim ntawv cog lus ntawm kev ua hauj lwm los yog lawb tawm mus.

ntau regression

Nyob rau hauv no lub sij hawm yog hais txog kev sib txuas lus equation uas muaj ob peb ywj siab ntau yam ntawm daim ntawv no:

y = f (x ₁ + x ₂ + ... x _m) + ε, qhov twg y - yog ib tug feature tau (cov nyob nce mus nce los), thiab x _1, x _2, ... x _m - yog tej yam tshwm sim yam (independent variables).

parameter kwv yees

Rau ntau yam regression (MR) nws yog tau siv ib tug tsawg kawg yog squares qauv (LSM). Rau linear equations ntawm daim ntawv Y = ib + b ₁ x _{1 + ... + b m x m +} ε tsim tau ib tug system ntawm ib txwm sib npaug (cm. Hauv qab no)

Yuav kom nkag siab txog cov hauv paus ntsiab lus ntawm txoj kev, peb xav txog ob-tshaj cov ntaub ntawv. Tom qab ntawd peb tau qhov teeb meem no piav los ntawm cov mis

Li no, peb muab:

qhov twg σ - yog lub variance ntawm lub duas paub feature, thaws rov los nyob rau hauv lub Performance index.

MNC yog muaj feem xyuam rau cov kab zauv MR rau standartiziruemom scale. Nyob rau hauv cov ntaub ntawv no, peb tau txais lub equation:

nyob rau t _y, t _{x 1, ...} t _XM - standartiziruemye variables uas nruab nrab qhov tseem ceeb yog 0; β _kuv - standardized regression coefficients thiab txheem sib txawv - 1.

Thov nco ntsoov tias tag nrho cov β _kuv nyob rau hauv cov ntaub ntawv no txhais tias yog tus normalized thiab tsentraliziruemye, yog li ntawd ib tug sib piv ntawm ib tug xam tau tias yog siv tau thiab siv tau. Nyob rau hauv tas li ntawd, nws yog txais mus nqa tawm kev soj ntsuam kuaj yam, muab pov tseg cov neeg uas muaj tus nqi qis tshaj qhov tseem ceeb ntawm βi.

Qhov teeb meem uas siv linear regression kab zauv

Xav tias koj muaj ib lub rooj ntawm lub zog ntawm tus nqi ntawm ib tug khoom N rau lub 8 lub hlis. Nws yog tsim nyog los txiav txim siab seb tus nrhiav tau ntawm nws tog nyob rau tus nqi ntawm 1850 rubles. / T.

	A	B	C
1	lub hlis	lub npe ntawm lub hli	nqe N
2	1	Lub ib hlis ntuj	1750 rubles ib tuj
3	2	Lub ob hlis ntuj	1755 rubles ib tuj
4	3	Lub peb hlis ntuj	1767 rubles ib tuj
5	4	Plaub Hlis Ntuj	1760 rubles ib tuj
6	5	Tej zaum	1770 rubles ib tuj
7	6	Lub rau hli ntuj	1790 rubles ib tuj
8	7	Lub Xya hli ntuj	1810 rubles ib tuj
9	8	Lub yim hli ntuj	1840 rubles ib tuj

Yuav kom daws tau qhov teeb meem no nyob rau hauv lub tabular processor "Excel" yuav tsum tau siv twb paub piv txwv li cov cuab tam "Cov ntaub ntawv Analysis" hais saum toj no. Tom ntej no, xaiv "Regression" seem thiab teem tsis. Peb yuav tsum nco ntsoov tias nyob rau hauv lub "tawm tswv yim ntau yam Y» yuav tsum tau nkag mus rau ib tug ntau yam ntawm qhov tseem ceeb ntawm cov tub nce mus nce los (nyob rau hauv rooj plaub no tus nqi ntawm cov khoom nyob rau hauv ib lub hlis ntawm lub xyoo) thiab nyob rau hauv lub "tawm tswv yim luv X» - rau ib tug neeg sab nraud (lub hli). Peb paub meej tias qhov kev txiav txim los ntawm txhaj «ua li». Nyob rau hauv ib tug tshiab tsab ntawv khom nqi (yog li ntawd, hais), peb muab tau cov ntaub ntawv rau cov regression.

Peb lub tsev nyob rau lawv linear kab zauv ntawm daim ntawv no y = taus + b, qhov twg raws li cov tsis muaj ib tug thiab b yog cov coefficients los ntawm cov kab xov tooj ntawm lub hlis thiab lub npe ntawm tus coefficients thiab «Y-kev tshuam" kab ntawm daim ntawv nrog rau cov kev tshwm sim ntawm lub regression tsom. Yog li, lub linear regression kab zauv (EQ) 3 rau cov teeb meem yuav tsum sau raws li:

Tus nqi ntawm cov khoom N = 11,714 * 1727.54 hlis tooj +.

los yog nyob rau hauv algebraic cim

y = 11.714 x + 1727,54

tsom xam ntawm tau

Yuav kom txiav txim siab seb tus tau txais txaus linear regression kab zauv siv cov ntau correlation coefficients (CMC) thiab kev txiav txim raws li zoo raws li kev kuaj thiab Fisher lub t-xeem. Nyob rau hauv lub rooj "Excel" regression nrog soj ntsuam lawv ua nyob rau hauv lub npe ntau R, R-Square, F-t-statistics thiab txheeb cais, ntsig txog.

KMC R enables los laij rau tus txav los nyob ze probabilistic kev sib raug zoo ntawm sab nraud thiab nyob zog. Nws tus nqi uas siab qhia ib tug muaj zog txaus kev twb kev txuas ntawm lub nce mus nce los "Number ntawm lub hli" thiab "N khoom nqi nyob rau hauv rubles ib 1 ton." Txawm li cas los, qhov xwm ntawm no kev sib raug zoo yog tsis paub.

Lub square ntawm lub coefficient ntawm kev txiav txim R ² (RI) yog ib tug numeric cov yam ntxwv ntawm cov kev faib ua feem ntawm tag nrho cov nphoo thiab qhia tau hais tias ib tug nphoo ntawm cov ntaub ntawv feem, i.e., qhov tseem ceeb ntawm cov tub nce mus nce los coj mus rau ib tug linear regression kab zauv. Nyob rau hauv qhov teeb meem no, qhov no tus nqi yog 84,8%, mp. E. Statistics nrog ib tug high degree ntawm cov neeg tau raug piav SD.

F-statistics, kuj hu ua Fisher kauj siv rau kev ntsuam xyuas qhov tseem ceeb ntawm cov linear dependence los yog disproving hypothesis lees tias nws lub neej.

Tus nqi ntawm t-statistic (Student lub t test) yuav pab soj ntsuam qhov tseem ceeb ntawm cov coefficient ntawm yam dawb tsis paub hais tias linear dependence neeg. Yog hais tias tus nqi ntawm t-xeem> t _cr, lub hypothesis ntawm ib tug linear equation tsis tseem ceeb dawb lub sij hawm no tso tseg.

Nyob rau hauv qhov teeb meem no rau ib tug dawb lub sij hawm los ntawm lub twj paj nruag "Excel" nws pom hais tias t = 169,20903, thiab p = 2,89E-12, t. E. Muaj ib tug pes tsawg yuav qhov muaj tseeb hais tias cov ncaj ncees yuav tau tso tseg lub hypothesis ntawm lub tsis tseem ceeb ntawm cov dawb lub sij hawm. Rau cov tsis paub coefficient ntawm t = 5,79405, thiab p = 0,001158. Nyob rau hauv lwm yam lus, qhov yuav tshwm hais tias ib tug tsis lees txais yog hypothesis yuav tsis tseem ceeb ntawm lub coefficient rau cov tsis paub, yog 0,12%.

Yog li, nws yuav sib cav hais tias lub tau linear regression kab zauv txaus.

Qhov teeb meem ntawm lub advisability muas shares

Ntau regression twb ua nyob rau hauv Excel siv tib lub "Cov ntaub ntawv Analysis" cuab tam. Xav txog cov kev daim ntawv thov.

Phau Ntawv Qhia lub tuam txhab «NNN» yuav tsum txiav txim siab seb puas yuav yuav 20% ntawm shares ntawm JSC «mmm». Pob nqi (SP) yog 70 lab US dollars. Tshwj xeeb ntawm «NNN» sau cov ntaub ntawv rau zoo xws li cov muas. Nws tau txiav txim siab mus ntsuam xyuas cov nqi ntawm shares rau tej tsis, qhia nyob rau hauv lab ntawm US dollars, xws li:

• payables (VK);
• txhua xyoo turnover volume (VO);
• receivables (VD);
• tus nqi ntawm tsau cov cuab tam (SOF).

Nyob rau hauv tas li ntawd, siv lub nyiaj nuj nqis ntawm Enterprises (V3 U) nyob txhiab ntawm US dollars.

Qhov kev txiav txim rooj processor Excel txhais tau tias

Ua ntej koj yuav tsum tau tsim ib lub rooj ntawm cov tswv yim ntaub ntawv. Nws yog raws li nram no:

Tom ntej:

• hu box "cov ntaub ntawv tsom xam";
• xaiv "Regression" seem;
• lub qhov rais "tswv yim luv Y» xeem ntau yam nyob nce mus nce los qhov tseem ceeb ntawm kem G;
• nias rau ntawm lub icon nrog ib tug liab xub mus rau sab xis ntawm lub qhov rais "tswv yim luv X» thiab cais rau ib tug ntawv ntau ntawm tag nrho cov qhov tseem ceeb ntawm kem B, C, D, F.

Mark lub point "Tshiab tsab ntawv khom nqi" thiab nias "Aws".

Tau ib tug regression tsom xam rau qhov hauj lwm no.

Txoj kev tshawb no tau thiab cov lus xaus

"Sau" npawv los ntawm cov ntaub ntawv hais saum toj no rau ntawm daim ntawv rooj Excel processor regression kab zauv:

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

Nyob rau hauv qhov ntau li ib txwm zauv daim ntawv nws muaj peev xwm yuav sau li:

y = 0.103 * x1 + 0,541 * x 2 - 0.031 * x 3 + 0,405 * x4 + 0,691 * X5 - 265.844

Cov ntaub ntawv rau «mmm» JSC nyob rau hauv lub rooj hauv qab no:

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

Pauv lawv mus rau hauv lub regression kab zauv, tau ib daim duab ntawm 64,72 lab US dollars. Qhov no txhais tau tias cov shares ntawm JSC «mmm» yuav tsum tsis txhob yuav, vim hais tias lawv tus nqi yog heev overpriced ntawm 70 lab US dollars.

Raws li koj tau saib, siv cov spreadsheet "Excel" thiab cov regression kab zauv tso cai los mus ua kom ib tug paub kev txiav txim siab hais txog lub advisability heev kev lw.

Tam sim no koj paub dab tsi ib tug regression. Piv txwv rau Excel, sib tham saum toj no, yuav pab tau koj nyob rau hauv kev daws cov tswv yim muaj teeb meem ntawm econometrics.