Bala idatha ukuthi ihambelana nemigomo ecacile nge-Excel's COUNTIFS Function

Umsebenzi we-Excel we-COUNTIFS ungasetshenziswa ukubala inani lamarekhodi wedatha ebangeni elikhethiwe elifanisa izici ezithile.

I-COUNTIFS idlulisela usizo lomsebenzi we-COUNTIF ngokukuvumela ukuthi ucacise kusuka ku-2 kuya ku-127 criteria kunokuba eyodwa kuphela ku-COUNTIF.

Ngokuvamile, COUNTIFS isebenza ngemigqa yedatha ebizwa ngamarekhodi. Emrekhodini, idatha eseliweni ngayinye noma enkambu emgqeni ihlobene - njengegama lenkampani, ikheli kanye nenombolo yocingo.

I-COUNTIFS ibheka izinkomba ezithile ezinkambu ezimbili noma ngaphezulu irekhodi futhi kuphela uma ithola umdlalo kwenkambu ngayinye ecacisiwe irekhodi elibalwe.

01 ka 09

COUNTIFS Umsebenzi Umsebenzi wesinyathelo ngesinyathelo

Esikhathini sesifundo se- COUNTIF ngesinyathelo esilandelayo sifanelana nesilinganiso esisodwa se-agent edayisayo esithengise ama-oda angaphezu kuka-250 ngonyaka.

Kulesi sifundo, sizobeka isimo sesibili besebenzisa i- COUNTIFS - leyo yezentengiselwano ezithengisayo esifundeni seMpumalanga yokuthengisa esenze okungaphezu kuka-250 ukuthengisa ngonyaka odlule.

Ukubeka izimo ezengeziwe kwenziwa ngokucacisa okunye okubhaliwe kwe-Criteria_range kanye neziphakamiso ze-COUNTIFS.

Ukulandela izinyathelo ezihlokweni zokufundisa ngezansi kukuhamba ngokudala nokusebenzisa umsebenzi we-COUNTIFS obonwe kusithombe esingenhla.

Izihloko zezifundo

• Ukufaka idatha yokufundisa
• I-Syntax ye-COUNTIFS Yomsebenzi
• Iqala ukusebenza kwe-COUNTIFS
• I-Criteria_range1 Ukuphikisana
• I-Criteria1 Ukuphikisana
• I-Criteria_range2 Ukuphikisana
• I-Criteria2 Impikiswano
• Futhi Impendulo yile ..

02 ka 09

Ukufaka idatha yokufundisa

Isinyathelo sokuqala sokusebenzisa umsebenzi we-COUNTIFS ku-Excel ukufaka idatha.

Kule tutorial faka idatha ebonwe esithombeni ngenhla kumaseli D1 kuya ku-F11 wephepha lokusebenzela le-Excel.

Kulayini 12 ngezansi kwedatha sizofaka umsebenzi we-COUNTIFS kanye nemigomo emibili yokusesha:

• izithunywa zokuthengisa ezivela esifundeni se-East sokuthengisa
• imiyalo engaphezu kuka-250 eyabekwa unyaka

Imiyalo yokufundisa ayifaki ukufomatha izinyathelo zephepha lokusebenzela.

Lokhu ngeke kuphazamise ukuqedela ukufundisa. Iphepha lakho lokusebenzela lizobonakala lihlukile kunalokhu kubonisiwe, kodwa umsebenzi we-COUNTIFS uzokunika imiphumela efanayo.

03 ka 09

I-Syntax ye-COUNTIFS Yomsebenzi

Ku-Excel, i- syntax yomsebenzi ibhekisela kokuhlelwa komsebenzi futhi ihlanganisa igama lomsebenzi, amabakaki, nokuphikisana .

I-syntax yomsebenzi we-COUNTIFS yile:

= COUNTIFS (Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)

Kuze kube ngu-127 Criteria_range / Izinombolo zokubaluleka zingacaciswa emsebenzini.

Ama-Arguments we-COUNTIFS

Iziphakamiso zomsebenzi zitshela COUNTIFS ukuthi yiziphi izinqubo esizama ukuzifanisa nokuthi yiliphi uhla lwedatha oluzosesho ukuthola lezi zici.

Zonke izingxabano kulo msebenzi zidingeka.

I-Criteria_range - iqembu lamaseli umsebenzi ukucinga umdlalo ku-argument yokuHlola ehambelanayo.

Imigomo - inani esizama ukulifanisa irekhodi lwedatha. Idatha yangempela noma ireferensi yeseli yedatha ingangeniswa kule mpikiswano.

04 ka 09

Iqala ukusebenza kwe-COUNTIFS

Nakuba kungenzeka nje ukuthayipha umsebenzi we-COUNTIFS nezimpikiswano zawo esitokisini kufomashi , abantu abaningi bakuthola kulula ukusebenzisa ibhokisi lengxoxo lomsebenzi ukuze bafake umsebenzi.

Izinyathelo Zokufundisa

1. Chofoza kuseli F12 ukuze wenze iseli esebenzayo . Yilapho sizofaka khona umsebenzi we-COUNTIFS.
2. Chofoza kuthebhu yefomula .
3. Khetha Imisebenzi Engaphezulu> Isitatimende kusuka kubhebhoni ukuvula uhlu lwehla lokusebenza phansi.
4. Chofoza ku- COUNTIFS ohlwini ukuze ulethe ibhokisi lengxoxo lomsebenzi.

Idatha esiyifaka emigqeni engenalutho ebhokisini lengxoxo izokwenza izimpikiswano zomsebenzi we-COUNTIFS.

Njengoba kukhonjisiwe, lezi zimpendulo zitshela umsebenzi ukuthi yiziphi izinqubo esizama ukuzifanisa nokuthi yiliphi uhlangothi lwedatha oluzosesho ukuthola lezi zici.

05 ka 09

Ukufaka i-Criteria_range1 Ukuphikisana

Kulesi sifundo sizama ukufanisa imigomo emibili kwirekhodi ngalinye lemininingwane:

1. Ama-agent okuthengisa avela esifundeni se-East sokuthengisa.
2. Ama-agent okuthengisa anemithetho engaphezu kuka-250 yokuthengisa yonyaka.

I-Criteria_range1 ingxabano ibonisa ububanzi bamaseli angu-COUNTIFS ukuthi afune uma ezama ukulinganisa indlela yokuqala - isifunda sokuthengisa sase-East.

Izinyathelo Zokufundisa

1. Ebhokisini lengxoxo , chofoza ku- Criteria_range1 line.
2. Qinisekisa amaseli D3 kuya ku-D9 kushidi lokusebenzela ukuze ufake lezi zikhombisi zeseli njengebanga lokucwaningwa ngumsebenzi .

06 ka 09

Ukufaka i-Criteria1 Ukungqubuzana

Kulesi sifundo inqubo yokuqala esiyifuna ukuyifanisa uma ngabe idatha ebangeni D3: D9 lilingana ne- East .

Nakuba idatha yangempela - njengegama elithi East - lingangena ebhokisini lebhokisi le ngxabano ngokuvamile kungcono ukufaka ireferensi yeseli endaweni yedatha ekhasini lokusebenzela ebhokisini lengxoxo.

Izinyathelo Zokufundisa

1. Chofoza ku- Criteria1 line ebhokisini lengxoxo .
2. Chofoza kuseli D12 ukufaka lelo referensi yeseli ebhokisini lengxoxo.
3. Igama lokusesha empumalanga lizofakwa kwiseli D12 esiteji sokugcina sesifundo.

Indlela AmaReferensi Ngama-Cell ekhulisa ngayo COUNTIFS Ukungafani

Uma ireferensi yeseli, njenge-D12, ingenwa njenge- Criteria Argument, umsebenzi we-COUNTIFS uzobuka ukufaniswa nanoma iyiphi idatha efakwe kulowo lensi kufomu lokusebenzela.

Ngakho emva kokubala inani lezithunywa ezivela esifundeni saseMpumalanga kuzoba lula ukuthola idatha efanayo kwesinye isifunda sokuthengisa nje ngokushintsha i- East ukuya eNyakatho noma eNtshonalanga kuseli D12. Umsebenzi uzovuselela ngokuzenzakalelayo futhi ubonise umphumela omusha.

07 ka 09

Ukufaka i-Criteria_range2 Ukuphikisana

Njengoba kushiwo ngaphambilini, kule tutorial sizama ukufanisa imigomo emibili kwirekhodi ngalinye lemininingwane

1. Ama-agent okuthengisa avela esifundeni se-East sokuthengisa.
2. Ama-agent ezithengisayo ezenze okungaphezu kuka-250 ukuthengisa kulo nyaka.

I- argument ye- Criteria_range2 ibonisa ububanzi bamaseli i-COUNTIFS ukuthi ifune uma izama ukufanisa ne-criteria yesibili - ama-agent edayisayo athengise ama-oda angaphezu kuka-250 kulo nyaka.

Izinyathelo Zokufundisa

1. Ebhokisini lengxoxo , chofoza ku- Criteria_range2 line.
2. Gqamisa amaseli e-E3 kuya ku-E9 kushidi lokusebenzela ukuze ufake lezi zikhombo zamaseli njengohlu lwesibili oluzosetshenzwa ngumsebenzi .

08 ka 09

Ukufaka i-Criteria2 Ukungqubuzana

Ukufaka i- Criteria2 Ukuphikisana nokuqedela ukusebenza kwe-COUNTIFS

Kulesi sifundo indlela yesibili esibheke ngayo ukufanisa ukuthi ngabe idatha ebangeni E3: E9 ingaphezu kwama-oda okuthengisa angu-250.

Njengengxabano yeCriteria1, sizofaka ireferensi yeseli endaweni kaCriteria2 ebhokisini lezingxoxo kunokuba idatha ngokwayo.

Izinyathelo Zokufundisa

1. Chofoza ku- Criteria2 line ebhokisini lengxoxo .
2. Chofoza kuseli E12 ukuze ufake leyo reference reference. Umsebenzi uzosesha uhla olukhethiwe kwisinyathelo sangaphambilini sedatha efana nale nqubo.
3. Chofoza u-Kulungile ukuqedela umsebenzi we-COUNTIFS bese uvala ibhokisi lengxoxo.
4. Impendulo ye-zero ( 0 ) izovela esitokisini F12 - iseli lapho singene khona umsebenzi - ngoba asikazanga ukufaka idatha emasimini e-Criteria1 noCriteria2 (C12 no-D12). Kuze kube yilapho senza, akukho lutho lwe-COUNTIFS lokubala futhi ngakho-ke inani lihlala ku-zero.
5. I-criteria yokusesha izongezwa kwisinyathelo esilandelayo sokufundisa.

09 ka 09

Ukungeza I-Criteria Yokusesha nokuqedela Isifundo

Isinyathelo sokugcina esifundweni ukufaka idatha kumaseli ekhredithi lokusebenzela elikhonjiswe njengequkethe izingxabano zokunquma .

Izinyathelo Zokufundisa

1. Ngeselula D12 hlobo East bese ucindezela ukhiye we- Enter kukhibhodi.
2. Ngeselula ye-E12 > 250 bese ucindezela ukhiye we- Enter ekhibhodi (i ">" uphawu olukhulu kune- Excel).
3. Impendulo 2 kufanele ivele esitokisini F12.
4. Abameli ababili kuphela - uRalph noSam - basebenze esifundeni seMpumalanga yokuthengisa futhi benza oda ezingaphezu kuka-250 zonyaka, ngakho-ke, la marekhodi amabili kuphela abalwa ngumsebenzi.
5. Nakuba uMarta esebenza esifundeni saseMpumalanga, wayenama-oda angaphansi kuka-250, ngakho-ke, irekhodi lakhe alibalwanga.
6. Ngokufanayo, uJoe noTom bobabili babe nemiyalo engaphezu kuka-250 konyaka, kodwa futhi asebenza endaweni yesifunda saseMpumalanga ngakho amarekhodi abo awabalwa.
7. Uma uchofoza ku-cell F12, umsebenzi ophelele
   = COUNTIFS (F3: F9, D3: D9, D12, E3: E9, E12) ibonakala kwibha yefomula ngaphezulu kwephepha lokusebenzela .