Excel Statistical Functions: Tutorial nrog Piv txwv, Ntu Peb

Excel muab ntau yam kev txheeb cais ua haujlwm uas ua kev suav los ntawm qhov nruab nrab mus rau ntau txoj kev faib cov lej thiab cov kab sib txuas.

Nyob rau hauv tsab xov xwm no peb yuav delve tob rau hauv Excel cov kev txheeb xyuas cov haujlwm rau kev suav cov kab kev ua haujlwm.

Thov nco ntsoov tias qee qhov kev txheeb cais tau nthuav tawm hauv cov ntawv Excel tsis ntev los no thiab yog li tsis muaj nyob rau hauv cov ntawv qub.

Lub sijhawm nyeem ntawv kwv yees: 12 minuti

Trendline muaj nuj nqi

Forecast

Excel's Forecast muaj nuj nqi kwv yees lub ntsiab lus yav tom ntej ntawm cov kab sib tw ua kom haum rau cov txheej txheem x thiab y qhov tseem ceeb.

syntax

= FORECAST( x, known_y's, known_x's )

yam

• x: Tus lej x-tus nqi uas koj xav twv seb qhov y-tus nqi tshiab.
• known_y's: Ib qho array uas paub y qhov tseem ceeb
• known_x's: Ib qho array uas paub x tus nqi

Nco ntsoov tias qhov ntev ntawm cov array ntawm known_x yuav tsum yog tib yam li ntawm known_y thiab variance ntawm known_x nws tsis tas yuav xoom.

Piv txwv li

Hauv daim ntawv nthuav qhia hauv qab no, ua haujlwm FORECAST Excel yog siv los kwv yees ib qho ntxiv ntawm txoj kab ncaj nraim zoo tshaj plaws los ntawm kev paub x thiab y qhov tseem ceeb ( khaws cia hauv hlwb F2: F7 thiab G2: G7).

Raws li pom nyob rau hauv cell F7 ntawm daim ntawv nthuav qhia, muaj nuj nqi los xam qhov xav tau y tus nqi ntawm x = 7 yog :=FORECAST( 7, G2:G7, F2:F7 )

Qhov no muab qhov tshwm sim 32.666667 .

Intercept

Ntawm Excel cov kev kwv yees ua haujlwm peb pom cov Intercept. Excel's Intercept muaj nuj nqi suav qhov cuam tshuam (tus nqi ntawm qhov kev sib tshuam ntawm y-axis) ntawm kab rov tav kab rov tav hla ib txheej ntawm x thiab y qhov tseem ceeb.

syntax

= INTERCEPT( known_y's, known_x's )

yam

• known_y's: Ib qho array uas paub y qhov tseem ceeb
• known_x's: Ib qho array uas paub x tus nqi

Nco ntsoov tias qhov ntev ntawm cov array ntawm known_x yuav tsum yog tib yam li ntawm known_y thiab variance ntawm known_x nws tsis tas yuav xoom.

Piv txwv li

Daim ntawv nthuav qhia hauv qab no qhia txog qhov piv txwv ntawm kev ua haujlwm Intercept ntawm Excel siv los xam qhov point qhov twg linear regression kab los ntawm tus known_x thiab cov known_y (tso npe hauv cov hlwb F2:F7 thiab G2:G7) cuam tshuam cov y-axis.

Le known_x thiab cov known_y tau npaj rau ntawm daim duab hauv daim ntawv nthuav qhia.

Raws li qhia hauv xov tooj F9 ntawm daim ntawv nthuav qhia, cov qauv rau kev cuam tshuam kev ua haujlwm yog :=INTERCEPT( G2:G7, F2:F7 )

uas muab qhov tshwm sim 2.4 .

Slope

Lwm qhov kev twv ua haujlwm nthuav dav heev yog Slope (Slope) Excel suav qhov nqes hav ntawm kab rov tav kab rov tav los ntawm cov txheej txheem x thiab y.

Lub syntax ntawm lub luag haujlwm yog:

syntax

= SLOPE( known_y's, known_x's )

yam

• known_y's: Ib qho array uas paub y qhov tseem ceeb
• known_x's: Ib qho array uas paub x tus nqi

Nco ntsoov tias qhov ntev ntawm cov array ntawm known_x yuav tsum yog tib yam li ntawm known_y thiab variance ntawm known_x nws tsis tas yuav xoom.

Piv txwv li

Daim ntawv nthuav qhia hauv qab no qhia txog qhov piv txwv ntawm kev ua haujlwm Slope (txoj kab nqes) ntawm Excel siv los xam txoj kab nqes ntawm kab rov tav kab los ntawm known_x thiab cov known_y, hauv cov hlwb F2:F7 thiab G2:G7.

Le known_x thiab cov known_y tau npaj rau ntawm daim duab hauv daim ntawv nthuav qhia.

Raws li qhia hauv xov tooj F9 ntawm daim ntawv nthuav qhia, cov qauv rau kev cuam tshuam kev ua haujlwm yog :=SLOPE( G2:G7, F2:F7 )

uas muab qhov tshwm sim 4.628571429.

Trend

Ib qho nthuav heev Excel forecast muaj nuj nqi yog TSHOOJ Excel (Trend) suav cov kab sib txawv los ntawm cov txheej txheem ntawm y qhov tseem ceeb thiab (optionally), ib qho kev muab ntawm x qhov tseem ceeb.

Txoj haujlwm tom qab ntawd txuas ntxiv cov kab sib txuas los suav cov txiaj ntsig y ntxiv rau ib qho ntxiv ntawm cov txiaj ntsig x tshiab.

Lub syntax ntawm lub luag haujlwm yog:

syntax

= TREND( known_y's, [known_x's], [new_x's], [const] )

yam

• known_y's: Ib qho array uas paub y qhov tseem ceeb
• [known_x's]: Ib lossis ntau qhov arrays paub x tus nqi. Qhov no yog ib qho kev sib cav uas, yog tias muab, yuav tsum yog tib qhov ntev raws li cov txheej txheem known_y's. Yog tias tshem tawm, lub teeb ntawm [known_x's] siv rau tus nqi {1, 2, 3, ...}.
• [new_x's]: Ib qho kev sib cav xaiv, muab ib lossis ntau qhov arrays ntawm tus lej muaj nuj nqis sawv cev rau cov txheej txheem x-tus nqi tshiab, uas koj xav suav cov y-tus nqi tshiab. Txhua array ntawm [new_x os] yuav tsum match ib array ntawm [known_x's]. Yog hais tias qhov kev sib cav [new_x os] raug tshem tawm, nws yog teem kom sib npaug [known_x's].
• [tus nqi]: Ib qho kev xaiv muaj kev sib cav hais txog seb qhov 'b' tas li, hauv kab zauv kab zauv y = m x + b , yuav tsum raug yuam kom sib npaug zos. Tus kheej [nqi] yog TRUE (los yog qhov kev sib cav no raug tshem tawm) qhov b tas li raug kho ib txwm;
• Tus kheej [nqi] yog FALSE qhov tas li b yog teem rau 0 thiab txoj kab ncaj nraim ua y = mx .

Piv txwv li

Nyob rau hauv cov ntaub ntawv nram qab no, Excel Trend muaj nuj nqi yog siv los txuas ntxiv cov x thiab y qhov tseem ceeb uas nyob ntawm txoj kab ncaj nraim y = 2x + 10. Cov paub x thiab y qhov tseem ceeb tau khaws cia hauv hlwb A2-B5 ntawm daim ntawv nthuav dav thiab tseem tso tawm hauv daim ntawv nthuav qhia.

Nco ntsoov tias nws tsis yog qhov tseem ceeb uas cov ntsiab lus muab haum raws nraim ntawm txoj kab ncaj nraim y = 2x + 10 (txawm tias hauv qhov piv txwv no lawv ua). Excel's Trend function yuav pom cov kab zoo tshaj plaws rau txhua qhov txiaj ntsig koj muab.

Txoj haujlwm Trend siv qhov tsawg tshaj plaws squares txoj kev los nrhiav qhov zoo tshaj plaws haum kab thiab ces siv nws los xam cov tshiab y qhov tseem ceeb rau cov tshiab x qhov tseem ceeb muab.

Hauv qhov piv txwv no, cov txiaj ntsig ntawm [new_x os] tau khaws cia rau hauv hlwb A8-A10, thiab Excel's Trend muaj nuj nqi tau siv, hauv hlwb B8-B10, txhawm rau nrhiav cov txiaj ntsig y tshiab. Raws li pom nyob rau hauv daim ntawv qhia bar, tus qauv yog := TREND(B2:B5,A2:A5,A8:A10)

Koj pom tias Txoj Kev Ua Haujlwm hauv cov qauv bar yog nyob rau hauv braces { }. Qhov no qhia tau hais tias muaj nuj nqi tau nkag li array formula .

Growth

Ntawm Excel cov kev kwv yees ua haujlwm peb pom cov Growth. Txoj haujlwm Growth Excel suav cov kev nthuav dav nthuav dav los ntawm cov txheej txheem ntawm y qhov tseem ceeb thiab (xaiv tau), ib lossis ntau pawg ntawm x qhov tseem ceeb. Qhov kev ua haujlwm ces ncua qhov nkhaus los xam cov nqi ntxiv y rau ib qho ntxiv ntawm cov nqi x tshiab.

Lub syntax ntawm lub luag haujlwm yog:

syntax

= GROWTH( known_y's, [known_x's], [new_x's], [const] )

yam

• known_y's: Ib qho array uas paub y qhov tseem ceeb
• [known_x's]: Ib lossis ntau qhov arrays paub x tus nqi. Qhov no yog ib qho kev sib cav uas, yog tias muab, yuav tsum yog tib qhov ntev raws li cov txheej txheem known_y's. Yog tias tshem tawm, lub teeb ntawm [known_x's] siv rau tus nqi {1, 2, 3, ...}.
• [new_x's]: Cov txheej txheem tshiab x qhov tseem ceeb, uas cov haujlwm suav nrog cov txiaj ntsig tshiab y. Yog hais tias tshem tawm, nws yog assumed tias cov txheej ntawm [new_x os] yog sib npaug ntawm [known_x's] thiab qhov ua haujlwm rov qab cov txiaj ntsig y uas nyob ntawm qhov kev suav ntawm kev nthuav dav nthuav dav.
• [tus nqi]: Ib qho kev xaiv muaj kev sib cav hais txog seb qhov 'b' tas li, hauv kab zauv kab zauv y = b * m^x , yuav tsum raug yuam kom sib npaug 1. Yog tias [nqi] yog TRUE (los yog qhov kev sib cav no raug tshem tawm) qhov b tas li raug kho ib txwm; Tus kheej [nqi] yog FALSE qhov tas li b yog teem rau 1 thiab txoj kab ncaj nraim ua y = mx .

Piv txwv li

Nyob rau hauv cov ntaub ntawv nram qab no, Excel kev loj hlob muaj nuj nqi yog siv los mus txuas ntxiv ib tug series ntawm x thiab y qhov tseem ceeb uas nyob rau ntawm lub exponential kev loj hlob nkhaus y = 5 * 2^x. Cov no tau muab khaws cia rau hauv cov hlwb A2-B5 ntawm daim ntawv nthuav qhia thiab kuj tshwm sim hauv daim ntawv teev lus.

Kev loj hlob muaj nuj nqi suav cov kev loj hlob nthuav dav uas zoo tshaj plaws haum rau qhov paub x thiab y qhov tseem ceeb muab. Hauv qhov piv txwv yooj yim no, qhov zoo tshaj plaws haum nkhaus yog exponential nkhaus y = 5 * 2^x.

Thaum Excel xam qhov kev sib npaug ntawm qhov kev loj hlob ntawm qhov nthuav dav, nws tuaj yeem siv los xam cov txiaj ntsig y tshiab rau qhov tshiab x qhov tseem ceeb hauv hlwb A8-A10.

Hauv qhov piv txwv no, cov txiaj ntsig ntawm [new_x's] yog khaws cia hauv hlwb A8-A10 thiab muaj nuj nqi Growth ntawm Excel tau muab tso rau hauv hlwb B8-B10. Raws li qhia hauv daim ntawv qhia bar, tus qauv rau qhov no yog: =Growth(B2:B5,A2:A5,A8:A10)

Koj tuaj yeem pom tias Kev Loj Hlob muaj nuj nqi nyob rau hauv daim ntawv qhia bar yog nyob rau hauv braces { } . Qhov no qhia tau hais tias muaj nuj nqi tau nkag li array formula .

Nco ntsoov tias txawm hais tias cov ntsiab lus hauv qhov piv txwv saum toj no haum raws qhov nkhaus y = 5 * 2^x, qhov no tsis yog qhov tseem ceeb. Txoj haujlwm Growth Excel yuav pom qhov zoo tshaj plaws-fitting nkhaus rau txhua qhov teeb meem uas koj muab.

Nyiaj txiag ua haujlwm

nyhuv

Cov nuj nqi Effect Excel xa rov qab cov paj laum zoo txhua xyoo rau cov paj laum nominal thiab muab cov sijhawm sib xyaw ua ke hauv ib xyoos.

Cov paj laum txhua xyoo zoo

Qhov txiaj ntsig zoo txhua xyoo yog qhov ntsuas ntawm cov paj uas suav nrog cov paj paj paj thiab feem ntau siv los sib piv cov nyiaj qiv nyiaj nrog cov ntsiab lus sib txawv.

Cov paj laum uas muaj txiaj ntsig zoo yog suav nrog cov lej hauv qab no:

nquab nominal_rate yog tus paj laum nominal e npery yog tus naj npawb ntawm cov sij hawm sib xyaw hauv ib xyoos.

Lub syntax ntawm lub luag haujlwm yog:

syntax

= EFFECT( nominal_rate, npery )

yam

• nominal_rate: Cov paj laum nominal (yuav tsum yog tus lej ntawm 0 thiab 1)
• npery: Tus naj npawb ntawm cov sij hawm sib xyaw hauv ib xyoos (yuav tsum yog tus lej zoo).

Piv txwv li

Cov ntawv nthuav qhia hauv qab no qhia peb qhov piv txwv ntawm Excel Effect muaj nuj nqi:

Yog qhov tshwm sim ntawm kev ua haujlwm Effect qhia ua zauv los yog qhia 0%, ob qho tib si ntawm cov teeb meem no feem ntau yog vim muaj cov qauv ntawm cov cell uas muaj cov haujlwm. Effect.

Yog li qhov teeb meem tuaj yeem daws tau los ntawm formatting lub xov tooj ntawm tes hauv feem pua, nrog tus lej lej.

Ua li no:

1. Xaiv cov hlwb los ua qhov feem pua.
2. Qhib "Format Cells" dialog box siv ib qho ntawm cov hauv qab no:
   • Txoj cai-nias lub xov tooj ntawm tes los yog ntau yam thiab xaiv qhov kev xaiv Format Cells… los ntawm cov ntawv qhia zaub mov;
   • Nyem qhov Dialog Box Launcher nyob rau hauv tus lej pab pawg ntawm lub tab Tsev Excel Ribbon;
   • Siv cov keyboard shortcut CTRL-1 (piv txwv li xaiv tus yuam sij CTRL thiab tuav nws, xaiv "1" (ib qho) yuam sij).
3. Hauv "Format Cells" dialog box:
   • Nco ntsoov daim npav Tooj nyob rau sab saum toj ntawm lub dialog box yog xaiv.
   • xaiv Feem pua los ntawm daim ntawv qeb nyob rau sab laug ntawm lub dialog box .Qhov no yuav coj tau cov kev xaiv ntxiv nyob rau sab xis ntawm lub checkbox, tso cai rau koj xaiv tus lej lej ntawm qhov chaw koj xav kom tshwm.
   • Thaum koj tau xaiv tus naj npawb ntawm kaum qhov chaw koj xav tso saib, nyem OK .

Nominal

Cov nuj nqi Nominal Excel rov qab cov paj laum nominal rau tus paj laum uas tau txais txiaj ntsig zoo thiab muab cov sijhawm sib xyaw ua ke hauv ib xyoos.

Lub syntax ntawm lub luag haujlwm yog:

syntax

= NOMINAL( effect_rate, npery )

yam

• effect_rate: Tus nqi paj tau zoo (tus nqi ntawm 0 thiab 1).
• npery: Tus naj npawb ntawm cov sij hawm sib xyaw hauv ib xyoos (yuav tsum yog tus lej zoo).

Piv txwv li

Hauv daim ntawv nthuav qhia hauv qab no, ua haujlwm Nominal ntawm Excel yog siv los xam cov paj laum nominal ntawm peb cov nyiaj qiv nrog cov ntsiab lus sib txawv.

Ercole Palmeri