Excel Statistical Functions: Isifundo esineMizekeliso, iCandelo lesiThathu

I-Excel ibonelela ngoluhlu olubanzi lwemisebenzi yeenkcukacha-manani eyenza izibalo ukusuka kwintsingiselo ukuya kulwabiwo lwamanani oluntsonkothileyo kunye nemisebenzi yomgca wentsingiselo.

Kweli nqaku siza kuphonononga nzulu kwimisebenzi yeenkcukacha-manani ye-Excel yokubala imisebenzi yomgca wentsingiselo.

Nceda uqaphele ukuba eminye imisebenzi yamanani yaziswa kwiinguqulelo zamva nje ze-Excel kwaye ke ayifumaneki kwiinguqulelo ezindala.

Ixesha lokufunda eliqikelelweyo: 12 imizuzu

Imisebenzi ye-trendline

Forecast

Excel's Forecast umsebenzi uqikelela inqaku lexesha elizayo kumgca wetrendi ofanelekileyo kwiseti enikiweyo ka x kunye no y amaxabiso.

syntax

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

iingxoxo

• x: Inani elingu-x lamanani ofuna ukuqikelela ixabiso lika-y elitsha.
• known_y's: Uluhlu lwamaxabiso awaziwayo y
• known_x's: Uluhlu lwamaxabiso awaziwayo x

Qaphela ukuba ubude boluhlu lwe known_x kufuneka ifane naleyo ye known_y kunye nokwahluka kwe known_x akunyanzelekanga ukuba ibe nguziro.

umzekelo

Kwi-spreadsheet elandelayo, umsebenzi FORECAST I-Excel isetyenziselwa ukuqikelela inqaku elongezelelweyo ecaleni komgca othe ngqo ofanelekileyo ngothotho lwamaxabiso aziwayo x kunye no-y (agcinwe kwiiseli F2:F7 kunye neG2:G7).

Njengoko kubonisiwe kwiseli F7 yespreadsheet, umsebenzi wokubala elindelekileyo ixabiso lika-y ku-x=7 ngu :=FORECAST( 7, G2:G7, F2:F7 )

Oku kunika umphumo 32.666667 .

Intercept

Phakathi kwemisebenzi yoqikelelo lwe-Excel sifumana Intercept. Excel's Intercept umsebenzi ubala ukhawuko (ixabiso ekudibaneni komgca ongu-y-axis) womgca wobuyiselo lomgca ngapha kweseti enikiweyo ka x kunye no y amaxabiso.

syntax

= INTERCEPT( known_y's, known_x's )

iingxoxo

• known_y's: Uluhlu lwamaxabiso awaziwayo y
• known_x's: Uluhlu lwamaxabiso awaziwayo x

Qaphela ukuba ubude boluhlu lwe known_x kufuneka ifane naleyo ye known_y kunye nokwahluka kwe known_x akunyanzelekanga ukuba ibe nguziro.

umzekelo

I-spreadsheet elandelayo ibonisa umzekelo womsebenzi Intercept ye-Excel isetyenziselwe ukubala indawo apho umgca wobuyiselo lomgca kwi known_x kunye known_y (ezidweliswe kwiiseli F2:F7 kunye neG2:G7) inqumla umgca ongu-y.

Le known_x kunye known_y zicwangciswe kwigrafu kwispreadsheet.

Njengoko kubonisiwe kwiseli F9 yespreadsheet, ifomula yomsebenzi we-Intercept yile :=INTERCEPT( G2:G7, F2:F7 )

enika isiphumo 2.4 .

Slope

Omnye umsebenzi woqikelelo onomdla kakhulu yi-Slope (Slope) I-Excel ibala ukuthambeka komgca wobuyiselo-mngeni ngeseti enikiweyo ka-x no-y amaxabiso.

Isivakalisi somsebenzi sithi:

syntax

= SLOPE( known_y's, known_x's )

iingxoxo

• known_y's: Uluhlu lwamaxabiso awaziwayo y
• known_x's: Uluhlu lwamaxabiso awaziwayo x

Qaphela ukuba ubude boluhlu lwe known_x kufuneka ifane naleyo ye known_y kunye nokwahluka kwe known_x akunyanzelekanga ukuba ibe nguziro.

umzekelo

I-spreadsheet elandelayo ibonisa umzekelo womsebenzi Slope (i-slope) ye-Excel esetyenziselwa ukubala ithambeka lomgca wobuyiselo lomgca nge known_x kwaye i known_y, kwiiseli F2:F7 kunye neG2:G7.

Le known_x kunye known_y zicwangciswe kwigrafu kwispreadsheet.

Njengoko kubonisiwe kwiseli F9 yespreadsheet, ifomula yomsebenzi we-Intercept yile :=SLOPE( G2:G7, F2:F7 )

enika isiphumo 4.628571429.

Trend

Umsebenzi woqikelelo onomdla kakhulu we-Excel ngu TYENDA U-Excel (uMkhwa) ubala umgca wentsingiselo elandelelanayo ngeseti enikiweyo yamaxabiso y kunye (ukhetho), uluhlu olunikiweyo lwamaxabiso x.

Umsebenzi emva koko wandisa umgca wentsingiselo yomgca ukubala amaxabiso awongezelelweyo ka-y kwiseti eyongezelelweyo yamaxabiso amatsha ka-x.

Isivakalisi somsebenzi sithi:

syntax

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

iingxoxo

• known_y's: Uluhlu lwamaxabiso awaziwayo y
• [known_x's]: Uluhlu olunye okanye ngaphezulu lwamaxabiso awaziwayo x. Le yingxoxo ekhethwayo yokuba, ukuba unikiwe, kufuneka bulingane ubude njengeseti ye known_y's. Ukuba ayiqukwanga, iqela le [known_x's] ithatha ixabiso {1, 2, 3, ...}.
• [entsha_x's]: Ingxoxo ekhethwayo, inikezela ngoluhlu olunye nangaphezulu lwamaxabiso amanani amele uluhlu olutsha lwamaxabiso- x, apho ufuna ukubala ahambelanayo amaxabiso kay-entsha. Uluhlu ngalunye lwe [ezintsha_x] kufuneka ithelekise uluhlu lwe [known_x's]. Ukuba ingxabano [ezintsha_x] ishiyiwe, imiselwe ukulingana [known_x's] .
• [ixabiso]: Ingxoxo ekhethiweyo enengqiqo echaza ukuba ngaba u-'b' ongaguqukiyo, kwinxaki yomgca y = m x + b , kufuneka inyanzeliswe ukuba ilingane no-zero. Ubuqu [indleko] YINYANISO (okanye ukuba le mpikiswano iye yashiywa) u-b ongatshintshiyo uphathwa ngesiqhelo;
• Ubuqu [indleko] YI-FALSE i-b eqhubekayo imiselwe ku-0 kwaye i-equation yomgca othe ngqo iba y = mx .

umzekelo

Kwispredishithi esilandelayo, umsebenzi we-Excel Trend usetyenziselwa ukwandisa uluhlu lwamaxabiso x kunye no-y abekwe kumgca othe ngqo y = 2x + 10. Amaxabiso aziwayo x kunye no-y agcinwa kwiiseli A2-B5 ze i-spreadsheet kwaye zikwaboniswa kwigrafu ye-spreadsheet.

Qaphela ukuba akukho mfuneko yokuba amanqaku anikiweyo alungelelane ngokuthe ngqo kumgca othe ngqo y = 2x + 10 (nangona kulo mzekelo enza njalo). Umsebenzi we-Excel's Trend uya kufumana owona mgca ufanelekileyo kuyo nayiphi na isethi yamaxabiso obonelela ngawo.

Umsebenzi we-Trend usebenzisa eyona ndlela incinci yezikwere ukufumana owona mgca ufanelekileyo kwaye emva koko uyisebenzise ukubala amanani amatsha ka-y kumaxabiso amatsha ka-x anikezelweyo.

Kulo mzekelo, amaxabiso e [ezintsha_x] zigcinwe kwiiseli A8-A10, kunye neExcel's Trend function isetyenzisiwe, kwiiseli B8-B10, ukufumana amanani ahambelanayo y. Njengoko kubonisiwe kwibha yefomula, ifomula i := TREND( B2:B5, A2:A5, A8:A10 )

Uyabona ukuba umsebenzi weNtengiso kwibha yefomula uvalelwe kwiibrayisi {}. Oku kubonisa ukuba umsebenzi wangeniswa njenge uluhlu lwefomula .

Growth

Phakathi kwemisebenzi yoqikelelo lwe-Excel sifumana Growth. Umsebenzi Growth I-Expons ibala ijiko lokukhula kolwazelelo ngeseti enikiweyo yamaxabiso y kunye (ukhetho), enye okanye ezininzi iiseti zamaxabiso ka-x. Umsebenzi ke wandisa ijika ukubala amaxabiso awongezelelweyo ka-y kwiseti eyongezelelweyo yamaxabiso amatsha ka-x.

Isivakalisi somsebenzi sithi:

syntax

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

iingxoxo

• known_y's: Uluhlu lwamaxabiso awaziwayo y
• [known_x's]: Uluhlu olunye okanye ngaphezulu lwamaxabiso awaziwayo x. Le yingxoxo ekhethwayo yokuba, ukuba unikiwe, kufuneka bulingane ubude njengeseti ye known_y's. Ukuba ayiqukwanga, iqela le [known_x's] ithatha ixabiso {1, 2, 3, ...}.
• [entsha_x's]: Iseti yamaxabiso amatsha ka x, apho umsebenzi ubala amanani ahambelanayo amatsha kay. Ukuba ithe yashiyiwa, kucingelwa ukuba iqela le [ezintsha_x] ilingana naleyo ye [known_x's] kwaye umsebenzi ubuyisela amaxabiso y alele kwijika elibaliweyo lokukhula kolwazelelo.
• [ixabiso]: Ingxoxo ekhethiweyo enengqiqo echaza ukuba ngaba u-'b' ongaguqukiyo, kwinxaki yomgca y = b * m^x , kufuneka inyanzeliswe ukuba ilingane no-1. Ukuba [indleko] YINYANISO (okanye ukuba le mpikiswano iye yashiywa) u-b ongatshintshiyo uphathwa ngesiqhelo; Ubuqu [indleko] YI-FALSE i-b eqhubekayo imiselwe ku-1 kwaye i-equation yomgca othe ngqo iba y = mx .

umzekelo

Kwi-spreadsheet elandelayo, umsebenzi wokukhula we-Excel usetyenziselwa ukwandisa uthotho lwexabiso lika-x kunye no-y elele kwi-exponential growth y = 5 * 2^x. Ezi zigcinwe kwiiseli A2-B5 ze-spreadsheet kwaye zikwavela kwi-spreadsheet chart.

Umsebenzi woHlumo ubala igophe lokukhula elibonisa ngokungcono amaxabiso aziwayo x kunye no-y anikiweyo. Kulo mzekelo ulula, elona gophe lifanelekileyo ligophe elichazayo y = 5 * 2^x.

Nje ukuba i-Excel ibale i-exponential growth curve equation, inokuyisebenzisa ukubala amanani amatsha ka-y kumaxabiso amatsha ka-x anikezelwe kwiiseli A8-A10.

Kulo mzekelo, amaxabiso e [new_x's] zigcinwe kwiiseli A8-A10 kunye nomsebenzi Growth ye-Excel ifakwe kwiiseli B8-B10. Njengoko kubonisiwe kwibha yefomula, ifomula yoku:=Growth( B2:B5, A2:A5, A8:A10 )

Uyakwazi ukubona ukuba umsebenzi woHlumo kwibha yefomula uvalelwe kwiibrayisi {}. Oku kubonisa ukuba umsebenzi wangeniswa njenge uluhlu lwefomula .

Qaphela ukuba nangona amanqaku akulo mzekelo ungasentla ehambelana ngqo negophe y = 5 * 2^x, oku akuyomfuneko. Umsebenzi Growth I-Excel iya kufumana eyona curve ifanelekileyo kuyo nayiphi na isethi yamaxabiso obonelela ngawo.

Imisebenzi yezemali

isiphumo

Umsebenzi Effect I-Excel ibuyisela iqondo lenzala elisebenzayo lomlinganiselo wenzala onikiweyo kunye nenani elinikiweyo lamathuba okudibanisa ngonyaka.

Umlinganiselo wenzala wonyaka osebenzayo

Umyinge wenzala wonyaka osebenzayo ngumlinganiselo wenzala odibanisa inzala eyinkunzi kwaye isoloko isetyenziselwa ukuthelekisa iimali-mboleko zemali kunye nemigaqo eyahlukeneyo yokwenza imali.

Izinga lenzala elisebenzayo lonyaka libalwa kusetyenziswa le nxaki ilandelayo:

ihobe nominal_rate lizinga lenzala eliqhelekileyo e npery linani lamathuba okudibanisa ngonyaka.

Isivakalisi somsebenzi sithi:

syntax

= EFFECT( nominal_rate, npery )

iingxoxo

• nominal_rate: Umlinganiselo wenzala oqhelekileyo (kufuneka ube lixabiso lamanani phakathi ko-0 no-1)
• npery: Inani lamathuba okudibanisa ngonyaka (kufuneka ibe yi-positive integer).

umzekelo

I-spreadsheet elandelayo ibonisa imizekelo emithathu yomsebenzi we-Excel Effect:

Ukuba isiphumo somsebenzi Effect ibonisa njengedesimali okanye ibonisa i-0%, yomibini le miba inokwenzeka ngenxa yokufomathwa kweseli equlathe umsebenzi Effect.

Ngoko ke ingxaki ingasonjululwa ngokufomatha iseli ngokwepesenti, eneendawo zedesimali.

Ukwenza oku:

1. Khetha iiseli ukufomatha njengepesenti.
2. Vula ibhokisi yencoko “yeFomathi yeeseli” usebenzisa enye yezi ndlela zilandelayo:
   • Cofa ekunene iseli ekhethiweyo okanye uluhlu kwaye ukhethe ukhetho Iiseli zokuFomatha... ukusuka kwimenyu yomxholo;
   • Cofa uMqalisi webhokisi yencoko yababini kwiqela lamanani kwisithuba Ikhaya IRibhoni ye-Excel;
   • Sebenzisa isinqumli se-keyboard I-CTRL-1 (okt khetha iqhosha le-CTRL kwaye, uyibambe phantsi, khetha iqhosha elithi "1" (enye)).
3. Kwibhokisi yencoko “yeFomathi yeeseli”:
   • Qinisekisa ikhadi INumero phezulu kwebhokisi yencoko yababini ikhethiwe.
   • khetha Ipesenti kuluhlu Udidi kwicala lasekhohlo lebhokisi yencoko yababini .Oku kuya kuzisa iinketho ezongezelelweyo kwicala lasekunene lebhokisi yokukhangela, ikuvumela ukuba ukhethe inani leendawo ofuna ukuvela kuzo.
   • Nje ukuba ukhethe inani leendawo ofuna ukuzibonisa, cofa OK .

Nominal

Umsebenzi Nominal I-Excel ibuyisela ireyithi yenzala yexabiso lenzala elinikiweyo elisebenzayo kunye nenani elinikiweyo lamathuba okudibanisa ngonyaka.

Isivakalisi somsebenzi sithi:

syntax

= NOMINAL( effect_rate, npery )

iingxoxo

• effect_rate: Izinga lenzala elisebenzayo (ixabiso lamanani phakathi ko-0 no-1).
• npery: Inani lamathuba okudibanisa ngonyaka (kufuneka ibe yi-positive integer).

umzekelo

Kwi-spreadsheet elandelayo, umsebenzi Nominal I-Excel isetyenziselwa ukubala umlinganiselo wenzala wemali-mboleko emithathu enemiqathango eyahlukeneyo.

Ercole Palmeri