Regression analysis yog ib qho ntawm feem ntau cov kev xav tau ntawm kev tshawb nrhiav. Nrog nws, koj tuaj yeem teev qhov kev hloov ntawm cov neeg hloov ntawm nws tus kheej rau ntawm tus neeg sib txawv. Microsoft Excel muaj cov cuab yeej rau kev ua hom kev tshuaj xyuas no. Cia saib seb lawv yog leej twg thiab siv lawv li cas.
Kev Tshawb Fawb Cov Kev Npaj
Tab sis, yuav kom siv lub zog uas tso cai rau kev ntsuam xyuas regression, ua ntej ntawm tag nrho cov, koj yuav tsum tau qhib lub Txheeb Xyuas Pob. Tsuas yog tom qab ntawd cov cuab yeej tsim nyog rau txoj kev ua no yuav tshwm sim nyob rau hauv Excel daim kab xev.
- Txav mus rau tab "Cov Ntaub Ntawv".
- Mus rau seem "Kev xaiv".
- Lub Excel xaiv qhib qhov rai. Mus rau subsection Ntxiv-ons.
- Nyob hauv qab ntawm lub qhov rais qhib, rearrange tus mus rau hauv lub nris "Kev Tswj" nyob hauv txoj hauj lwm Excel Ntxiv-insyog tias nws nyob hauv lwm txoj haujlwm. Peb nias lub khawm "Mus".
- Lub qhov rais qhib lub qhov rais Excel opens. Muab tus zuam ze rau ntawm qhov khoom "Cov Qauv Ntsuas". Nias rau ntawm "OK" khawm.
Tam sim no thaum peb mus rau ntawm tab "Cov ntaub ntawv", rau ib daim kab xev hauv lub nras cov cuab yeej "Kev Txheeb Xyuas" peb yuav pom ib lub pob tshiab - "Kev Txheeb Xyuas Cov Ntsiab Lus".
Hom kev hloov ntawm regression
Nws muaj ntau hom kev hloov regressions:
- parabolic;
- hwj chim;
- logarithmic;
- exponential;
- qhia;
- hyperbolic;
- tawm regression.
Peb yuav tham ntxiv txog qhov kev siv qhov kawg ntawm regression tsom hauv Excel.
Tawm regression hauv Excel
Hauv qab no, ua piv txwv, lub rooj nthuav qhia yog nthuav qhia tias qhov nruab nrab txhua hnub huab cua sov sab nraud, thiab cov neeg muas khoom hauv khw muag khoom rau hnub ua haujlwm. Wb paub nrog kev pab los ntawm kev ntsuas kev hloov regression, npaum li cas hauv cov huab cua hauv cov huab cua hauv daim ntawv ntawm cov huab cua kub tuaj yeem cuam tshuam kev koom nrog ib lub lag luam lag luam.
Qhov regression equation ntawm ib hom kab yog raws li nram no:Y = a0 + a1x1 + ... + akhk. Hauv daim ntawv no Y txhais tau hais tias muaj qhov txawv txav, qhov feem ntawm yam uas peb tab tom sim kawm. Nyob rau hauv peb cov ntaub ntawv, qhov no yog lub xov tooj ntawm cov neeg yuav khoom. Lub ntsiab lus x - Cov no yog ntau yam cuam tshuam rau qhov sib txawv. Cov lus qhia a yog cov kev hloov coefficients. Ntawd yog, lawv txiav txim siab qhov tseem ceeb ntawm qhov kev xav tau. Index k suav cov naj npawb ntawm cov teeb meem no.
- Ndliv lub khawm "Kev Txheeb Xyuas Cov Ntsiab Lus". Nws muab tso rau hauv tab. "Tsev" nyob rau hauv lub block ntawm cov cuab yeej "Kev Txheeb Xyuas".
- Lub qhov rais qhib me me. Hauv nws, xaiv qhov khoom "Regression". Peb nias lub khawm "OK".
- Lub qhov rai regression opens. Hauv nws, cov cheeb tsam uas yuav tsum tau muaj "Tswv yim pib Y" thiab "Tswv yim pib X". Tag nrho lwm qhov chaw tuaj yeem tawm ua neej.
Hauv teb "Tswv yim pib Y" peb tau hais qhia qhov chaw nyob ntawm cov kabmob ntawm cov hlwb uas cov ntaub ntawv uas tsis paub meej nyob rau hauv, tus cwj pwm ntawm yam uas peb tabtom sim tsim. Nyob rau hauv peb cov ntaub ntawv, cov no yuav yog hlwb nyob rau hauv "Number of Buyers" column. Qhov chaw nyob yuav nkag los ntawm kev ntaus ntawv los ntawm cov keyboard, los yog koj tuaj yeem xaiv xaiv yam. Qhov kev xaiv tom kawg yuav yooj yim dua thiab yooj yim dua.
Hauv teb "Tswv yim pib X" nkag mus rau qhov chaw nyob ntawm cov xov tooj ntawm cov hlwb uas cov ntaub ntawv ntawm qhov zoo tshaj plaws, tus cwj pwm ntawm qhov uas peb xav muab teeb, qhov chaw nyob. Raws li tau hais los saum toj no, peb yuav tsum tau txiav txim seb qhov kub nyhiab ntawm cov neeg muas zaub hauv khw, thiab yog li ntawd nkag mus rau qhov chaw nyob ntawm lub hlwb hauv "Kub". Qhov no yuav ua tau ib yam nkaus li hauv "Cov Naj Npawb ntawm Cov Neeg Tuav".
Nrog kev pab ntawm lwm qhov chaw, koj tuaj yeem tsim cov ntawv lo, cov qib kev cia siab, tas li-xoom, tso ib daim duab ntawm qhov muaj tseeb, thiab ua lwm yam. Tab sis, feem ntau, cov kev hloov no tsis tas yuav hloov. Tsuas yog qhov koj yuav tsum tau them siab rau yog qhov tsis tau tso tawm. Los ntawm kev ua neej, qhov kev soj ntsuam tau muab tso rau ntawm lwm cov ntawv, tab sis yog los hloov txoj kev hloov, koj tuaj yeem tsim qhov tso zis rau ntawm daim ntawv teev lus nyob rau tib daim ntawv uas cov ntaub ntawv nrog cov thawj cov ntaub ntawv nyob hauv, los yog hauv ib phau ntawv sib txawv, uas yog, nyob rau hauv ib daim ntawv tshiab.
Tom qab tag nrho cov chaw teev, nyem rau ntawm lub pob. "OK".
Kev soj ntsuam ntawm kev soj ntsuam
Cov txiaj ntsig ntawm kev tshuaj ntsuam regression yog tshwm sim nyob rau hauv daim ntawv ntawm ib lub rooj nyob rau hauv qhov chaw qhia nyob rau hauv cov chaw.
Ib ntawm cov cim tseem ceeb yog R-squared. Nws qhia tau hais tias qhov zoo ntawm tus qauv. Nyob rau hauv peb cov ntaub ntawv, qhov sib piv no yog 0.705, los yog kwv yees li 70.5%. Qhov no yog qhov tsim nyog tau zoo. Qhov tsawg dua 0.5 feem pua yog qhov phem.
Lwm qhov cim tseem ceeb nyob hauv lub xovtooj ntawm kev sib tshuam ntawm txoj kab. "Y-kev sib tshuam" thiab kem Tsi. Nws qhia tau hais tias tus nqi dab tsi hauv Y, thiab nyob rau hauv peb cov ntaub ntawv, qhov no yog tus naj npawb ntawm cov neeg yuav khoom, nrog rau lwm yam teeb meem sib npaug rau xoom. Hauv cov lus no, qhov no yog 58.04.
Tus nqi ntawm qhov kev sib tshuam ntawm daim duab "Hloov X1" thiab Tsi qhia txog qib ntawm Y ntawm X. Nyob rau hauv peb cov ntaub ntawv, qhov no yog theem kev vam khom ntawm tus naj npawb ntawm cov neeg muas zaub ntawm lub khw muag khoom kub. Ib co coefficient ntawm 1.31 yog suav tias yog qhov qhia tau zoo ntawm kev ua haujlwm.
Raws li koj tau pom, siv Microsoft Excel yog qhov yooj yim los ua ib lub rooj sib thooj hauv kev hloov siab. Tab sis, tsuas yog ib tus neeg tau txais kev pab tuaj yeem ua haujlwm nrog cov ntaub ntawv tso tawm, thiab nkag siab lawv cov essence.
