Omnye itafile yepivot sisixhobo sohlalutyo kunye nengxelo esisetyenziselwa ukuyila iitheyibhile zesishwankathelo eziqala kwiseti yedatha. Ngokusebenza, ikuvumela ukuba dibanisa, Hlalutya e jonga idatha ngamandla kwaye ngokukhawuleza

amanqaku

Imisebenzi yezibalo ze-Excel: Isifundo esinemizekelo, inxalenye yokuqala

I-Excel ibonelela ngoluhlu olubanzi lwemisebenzi yeenkcukacha-manani eyenza izibalo ukusuka kwintsingiselo esisiseko, i-median, kunye nemowudi ukuya kulwabiwo lwamanani oluntsonkothileyo kunye novavanyo olunokwenzeka.

Kweli nqaku siza kuhlolisisa imisebenzi yezibalo ze-Excel, ukubala, ukuphindaphinda kunye nokukhangela.

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

Isiqulatho

Ixesha lokufunda eliqikelelweyo: 12 imizuzu

`COUNT`

Umsebenzi COUNT di Excel idweliswe kudidi lweMisebenzi yeNkcazo ye-Microsoft Excel. Ibuyisela inani lamanani ukusuka kumaxabiso achaziweyo. Ngamagama alula, ithathela ingqalelo kuphela amaxabiso elo nani kwaye ibuyise ukubala kwayo kwisiphumo.

syntax

= COUNT(valore1, [valore2], …)

iingxoxo

valore1: ireferensi yeseli, uluhlu, okanye inani elifakwe ngqo kumsebenzi.
[valore2]: Ireferensi yeseli, uluhlu, okanye inombolo efakwe ngqo kumsebenzi.

umzekelo

Ngoku makhe sibone umzekelo wosetyenziso lomsebenzi COUNT

Sisebenzise lo msebenzi ukubala iiseli zoluhlu B1:B10 kwaye ubuyisele i-8 kwisiphumo.

Kwiseli B3 sinexabiso elisengqiqweni nakwiseli B7 sinombhalo. COUNT akazihoyanga zombini izisele. Kodwa ukuba ungenisa ixabiso elisengqiqweni ngokuthe ngqo kumsebenzi, liyakulibala. Kulo mzekelo ulandelayo, sifake ixabiso elisengqiqweni kunye nenani sisebenzisa ukucaphula kabini.

`COUNTA`

Umsebenzi COUNTA di Excel idweliswe kudidi lweMisebenzi yeNkcazo ye-Microsoft Excel. Ibuyisela ubalo lwamaxabiso akhankanyiweyo . Ngokungafaniyo COUNT, ithathela ingqalelo zonke iindidi zamaxabiso kodwa ayihoyi (Iiseli) ezingenanto. Ngamagama alula, zonke iiseli azinanto.

syntax

= COUNTA(valore1, [valore2], …)

iingxoxo

valore1: ixabiso, ireferensi yeseli, uluhlu lweeseli, okanye uluhlu.
[valore2]: ixabiso, ireferensi yeseli, uluhlu lweeseli, okanye uluhlu

umzekelo

Ngoku makhe sibone umzekelo wosetyenziso lomsebenzi COUNTA:

Kulo mzekelo ulandelayo, sisebenzise umsebenzi COUNTA ukubala iiseli kuluhlu B1:B11.

Kukho iiseli ezili-11 kuluhlu kwaye umsebenzi ubuyisela i-10. Kukho iseli engenanto kuluhlu olungahoywayo ngumsebenzi. Kwezinye iiseli sinamanani, umbhalo, amaxabiso asengqiqweni kunye nesimboli.

`COUNTBLANK`

Umsebenzi COUNTBLANK ye-Excel idweliswe kudidi lweMisebenzi yeNkcazo ye-Microsoft Excel. Ibuyisela inani leeseli ezingenanto okanye ezingenaxabiso. Ngamagama alula, ayiyi kubala iiseli eziqulethe isicatshulwa, amanani okanye iimpazamo, kodwa iya kubala iifomyula ezibuyisela ixabiso elingenanto.

syntax

= COUNTBLANK(intervallo)

iingxoxo

isithuba: uluhlu lweeseli ofuna ukubala kuzo iiseli ezingenanto.

umzekelo

Ukuvavanya umsebenzi COUNTBLANK kufuneka sibone umzekelo, kwaye ngezantsi kukho onokuthi uzame:

Kulo mzekelo ulandelayo, sisebenzise umsebenzi COUNTBLANK ukubala iiseli ezingenanto kuluhlu B2:B8.

Kolu luhlu, sineeseli ezi-3 ezingenanto, kodwa iseli B7 iqulathe ifomula ekhokelela kwiseli engenanto.

Umsebenzi ubuyile 2 ukusukela kwiiseli B4 e B5 zizo kuphela iiseli ezingenanto ezingenaxabiso.

`COUNTIF`

Umsebenzi COUNTIF ye-Excel idweliswe kudidi lweMisebenzi yeNkcazo ye-Microsoft Excel. Ibuyisela inani lamanani anelisa imeko ekhankanyiweyo. Ukubeka ngokulula, ijonga kuphela kwaye ibale ukubala kwamaxabiso owanelisayo imeko.

syntax

= COUNTIF(range, criteria)

iingxoxo

range: Uluhlu lweeseli ofuna ukubala kuzo iiseli ezihlangabezana nemilinganiselo.
criteria: ikhrayitheriya (ubuntununtunu bemeko) ukujonga iiseli zokubala.

umzekelo

Ukuze ubone indlela COUNTIF masibone lo mzekelo ulandelayo:

Ukusebenzisa abaqhubi abanengqondo njengeenqobo zokugweba

Kulo mzekelo ulandelayo, sisebenzise "> 2500" (njengomsebenzi onengqondo) ukubala inani labathengi abathenge ngaphezu kwe-€ 2.500,00.

Ukuba ufuna ukusebenzisa umsebenzisi onengqondo kufuneka uyibeke kwizicaphulo eziphindiweyo.

Ukusebenzisa imihla njengeenqobo zokugweba

Kulo mzekelo ungezantsi, sisebenzise umhla kwiikhrayitheriya zokufumanisa ukuba bangaphi abathengi esibafumeneyo ukusukela ngoJanuwari 2022.

Xa ufaka umhla ngqo kumsebenzi, COUNTIF iguqula ngokuzenzekelayo okubhaliweyo kube ngumhla.

Kumzekelo ongezantsi, sifake umhla ofanayo njengenombolo, kwaye njengoko uyazi, i-Excel igcina umhla njengenombolo.

Emva koko ungafaka inombolo emele umhla ngokwenkqubo yomhla ye-Excel.

`COUNTIFS`

Umsebenzi COUNTIFS ye-Excel idweliswe kudidi lweMisebenzi yeNkcazo ye-Microsoft Excel. Ibuyisela inani lamanani anelisa iimeko ezininzi ezikhankanyiweyo. Ngokungafaniyo COUNTIF, ungacwangcisa iimeko ezininzi kwaye ubale amanani ahlangabezana nazo zonke ezo meko.

Ileta yeendaba entsha

Ungaphoswa zezona ndaba zibalulekileyo kwi-Innovation. Bhalisa ukuze uzifumane nge-imeyile.

syntax

= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

iingxoxo

criteria_range1: uluhlu lweeseli ofuna ukuzivavanya usebenzisa criteria1.
criteria1: indlela ofuna ukuyivavanya criteria_range1.
[criteria_range2]: uluhlu lweeseli ofuna ukuzivavanya usebenzisa criteria1.
[criteria2]: indlela ofuna ukuyivavanya criteria_range1.

umzekelo

Ukuqonda umsebenzi COUNTIFS kufuneka sizame kumzekelo kwaye ngezantsi kukho onokuthi uzame:

Kulo mzekelo ulandelayo, sisebenzise COUNTIFS ukubala abafazi abangaphezu kweminyaka engama-25 ubudala.

Sichaze iikhrayitheriya ezimbini zovavanyo, enye “nguMfazi” kwaye enye inkulu kunomsebenzi wokubala iiseli ezinenani elikhulu kuno “>25”.

Kulo mzekelo ulandelayo, sisebenzise iinkwenkwezi kwenye ikhrayitheriya kunye > nomsebenzisi komnye ukubala inani lomntu ogama lakhe liqala ngonobumba A kwaye ubudala bakhe bungaphezu kweminyaka engama-25.

`FREQUENCY`

Kuluhlu olunikiweyo lwamaxabiso amanani, Excel's Frequency function ibuyisela inani lamaxabiso awela phakathi koluhlu oluxeliweyo.

Umzekelo, ukuba unedatha yobudala beqela labantwana, ungasebenzisa i-Excel's Frequency function ukubala ukuba bangaphi abantwana abawela kuluhlu lobudala obahlukeneyo.

syntax

= FREQUENCY( data_array, bins_array )

iingxoxo

uluhlu_lwedatha: Uluhlu lwentsusa lwamaxabiso apho ifrequency ibalelwa khona.
imigqomo_yoluhlu: Uluhlu lwamaxabiso oluxela imida yoluhlu apho uluhlu_ lwedatha kufuneka lohlulwe khona.

Ukususela umsebenzi Frequency ibuyisela uluhlu lwamaxabiso (eliqulathe ubalo loluhlu ngalunye olukhankanyiweyo), kufuneka ifakwe njengefomula yoluhlu.

Ukufaka uluhlu lweefomula

Ukufaka uluhlu lwefomula kwi-Excel, kufuneka uqale uqaqambise uluhlu lweeseli zesiphumo somsebenzi. Chwetheza umsebenzi wakho kwiseli yokuqala yoluhlu kwaye ucinezele CTRL-SHIFT-Enter.

umzekelo

Uluhlu lubuyiselwe ngumsebenzi Frequency ye-Excel iya kuba nongeniso olunye ngaphezu kwe bins_array kubonelelwe. Makhe sijonge le mizekelo ilandelayo.

Imizekelo yoMsebenzi we-Excel Frequency

Umzekelo 1

Iiseli A2 - A11 yespreadsheet iqulethe iminyaka yeqela labantwana.

Excel's Frequency function (ifakwe kwiiseli C2-C4 yespreadsheet) yasetyenziswa ukubala inani labantwana abawela kuluhlu lweminyaka emithathu eyahlukeneyo, echazwe ngu bins_array (igcinwe kwiiseli B2 -B3 yespreadsheet).

Nceda uqaphele ukuba amaxabiso bins_array khankanya amaxabiso aphezulu kumaqela amabini okuqala obudala. Ngoko ke, kulo mzekelo, iminyaka kufuneka ihlulwe kwiindidi ze-0-4 iminyaka, iminyaka emi-5-8 kunye neminyaka eyi-9 +.

Njengoko kubonisiwe kwibha yefomula, ifomula yomsebenzi wokuphindaphinda kulo mzekelo yile: =FREQUENCY( A2:A11, B2:B3 )

Qaphela ukuba iibrayisi ezigobileyo ezijikeleze umsebenzi zibonisa ukuba ifakwe njengefomula yoluhlu.

Umzekelo 2

Umsebenzi Frequency ingasetyenziswa namaxabiso edesimali.

Iiseli A2-A11 kwi-spreadsheet ngakwesokudla bonisa ukuphakama (kwiimitha) zeqela labantwana abali-10 (bajikelezwe kwi-cm ekufutshane).

Umsebenzi Frequency (yangeniswa kwiiseli C2-C5) isetyenziselwa ukubonisa inani labantwana abanobude obungena phakathi koluhlu ngalunye: 0,0 – 1,0 iimitha 1,01 – 1,2 iimitha 1,21 – 1,4 yeemitha nangaphezulu kwe-1,4 yeemitha

Kuba sifuna ukuba idatha yahlulwe ibe ziindidi ezi-4, umsebenzi unikezelwe ngexabiso le-3 bins_array 1.0, 1.2 kunye ne-1.4 (igcinwe kwiiseli B2-B4).

Njengoko kubonisiwe kwibha yefomula, ifomula yomsebenzi Frequency kwaye: =FREQUENCY( A2:A11, B2:B4 )

Kwakhona, iibrayisi ezigobileyo ezijikeleze umsebenzi zibonisa ukuba ifakwe njengefomula yoluhlu.

Ngeminye imizekelo ye-Excel's Frequency function, bona Iwebhusayithi yeMicrosoft Office .

Impazamo yokusebenza frequency

Ukuba umsebenzi frequency ye-Excel ibuyisela impazamo, kusenokwenzeka ukuba le yimpazamo #N/A. Impazamo yenzeka ukuba uluhlu lwefomula ifakwe kuluhlu olukhulu kakhulu lweeseli. Yimpazamo leyo #N/A ivela kuzo zonke iiseli emva kweseli nth (apho n bubude be bins_array + 1).

Ufundo olunxulumeneyo

Yintoni iPivotTable?

Omnye itafile yepivot sisixhobo sokuhlalutya kunye nengxelo esisetyenziselwa ukuyila iitheyibhile zesishwankathelo ukuqala kwiseti yedatha. Ngokusebenza, ikuvumela ukuba dibanisa, ukuhlalutya e umbono data ngamandla kwaye ngokukhawuleza

Uyisebenzisa nini iPivot Table?

Le iitafile zepivot ziluncedo kwiimeko ezininzi xa kuziwa ekuhlalutyeni nasekuhlanganiseni amanani amakhulu edatha. Nazi ezinye iimeko apho unokufuna ukusebenzisa itafile yepivot:
Uhlalutyo lwedatha yentengiso:
Ukuba unoluhlu lwentengiso olunolwazi olufana nemveliso, iarhente yokuthengisa, umhla, kunye nemali, iPivotTable inokukunceda ukuba ufumane isishwankathelo sentengiso epheleleyo yemveliso nganye okanye iarhente.
Ungaqokelela idatha ngenyanga, ikota, okanye unyaka kwaye ujonge iitotali okanye i-avareji.
Isishwankathelo sedatha yezemali:
Ukuba unedatha yezemali njengengeniso, iindleko, iindidi zeendleko, kunye namaxesha, iPivotTable inokukunceda ukubala iindleko zizonke zodidi ngalunye okanye ujonge iintsingiselo ngokuhamba kwexesha.
Uhlalutyo lwabasebenzi:
Ukuba unedatha yabasebenzi, efana nesebe, indima, umvuzo, kunye neminyaka yenkonzo, iPivotTable inokukunceda ukuba ufumane izibalo ezifana nemivuzo ephakathi ngokwesebe okanye ukubalwa kwabasebenzi ngendima.
Ukusetyenzwa kwedatha yentengiso:
Ukuba unedatha yokuthengisa enje ngeekhankaso zentengiso, amajelo okuthengisa, kunye neemetrics zempumelelo, itheyibhile yepivot inokukunceda ukuba uchonge ukuba zeziphi iziteshi ezivelisa eyona nzuzo inkulu kutyalo-mali.
Uhlalutyo lwedatha yoluhlu:
Ukuba ulawula indawo yokugcina impahla okanye ivenkile, iPivotTable inokukunceda ukulandelela ubungakanani bemveliso, iindidi zemveliso, kunye neentengiso.
Ngokubanzi, sebenzisa itafile yepivot xa ufuna dibanisa e umbono idatha ngempumelelo ukwenza izigqibo ezinolwazi