Idatha yokubala kumaseli akhethiwe nge-Excel COUNTIF

Umsebenzi we-COUNTIF uhlanganisa umsebenzi we-IF kanye no-COUNT umsebenzi ku-Excel. Le nhlanganisela ikuvumela ukuthi ubale inani lezinombolo ezithile zedatha elitholakala eqenjini elikhethiwe lamaseli.

Ingxenye ye-IF yomsebenzi inqume ukuthi iyiphi idatha ehlangabezana nezimiso ezicacisiwe futhi ingxenye engu-COUNT iyabalwa.

COUNTIF Umsebenzi Umsebenzi wesinyathelo ngesinyathelo

Le tutorial isebenzisa amarekhodi wedatha kanye nomsebenzi we-COUNTIF ukuthola inombolo ye-Sales Reps enamakholi angaphezu kwama-250 omnyaka.

Ukulandela izinyathelo ezihlokweni zokufundisa ngezansi kukuhamba ngokudala nokusebenzisa umsebenzi we-COUNTIF obonwe esithombeni ngenhla ukuze ubale inani lokuthengisa kabusha nge-oda ezingaphezu kwama-250.

01 ngo-07

Izihloko zezifundo

• Ukufaka idatha yokufundisa
• I-Syntax ye-COUNTIF yomsebenzi
• Iqala ukusebenza kwe-COUNTIF
• Ukufaka i-Range Argument
• Ukufaka Ukungqubuzana Okubalulekile
• Futhi Impendulo yile ..

02 ngo-07

Ukufaka idatha yokufundisa

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

Faka idatha kumaseli C1 kuya ku-E11 wephepha lokusebenzela le-Excel njengoba kuboniswa esithombeni ngenhla.

Umsebenzi we-COUNTIF kanye nemigomo yokusesha (imiyalo engaphezu kwama-250) izongezwa kulayini 12 ngezansi kwedatha.

Qaphela: Imiyalo yokufundisa ayifaki ukufomatha izinyathelo zephepha lokusebenzela.

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

03 ka-07

I-Syntax ye-COUNTIF yomsebenzi

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

I-syntax yomsebenzi we-COUNTIF yi:

= COUNTIF (Ibanga, Isihlonzi)

Ama-Arguments we-COUNTIF

Iziphakamiso zomsebenzi zitshela umsebenzi ukuthi yisiphi isimo esizihlolisayo nokuthi yiyiphi inamba yedatha okufanele ibale uma isimo sifinyelelwe.

Ububanzi - iqembu lama cell the function is to search.

Imigomo - leli xabiso lifaniswa nedatha kumaseli weRange . Uma umdlalo utholakala bese iseli ku- Range sibalwa. Idatha yangempela noma ireferensi yeseli yedatha ingangeniswa kule mpikiswano.

04 ka 07

Iqala ukusebenza kwe-COUNTIF

Nakuba kungenzeka nje ukuthayipha umsebenzi we-COUNTIF esitokisini kushidi lokusebenzela , abantu abaningi bakuthola kulula ukusebenzisa ibhokisi lengxoxo lomsebenzi ukufaka umsebenzi.

Izinyathelo Zokufundisa

1. Chofoza kuseli E12 ukuze wenze iseli esebenzayo . Yilapho sizofaka khona umsebenzi we-COUNTIF.
2. Chofoza kuthebhu yefomathi yebhande .
3. Khetha Imisebenzi Engaphezulu> Isitatimende kusuka kubhebhoni ukuvula uhlu lwehla lokusebenza phansi.
4. Chofoza ku- COUNTIF ohlwini ukuze ulethe ibhokisi lengxoxo ye-COUNTIF yomsebenzi.

Idatha esiyifaka emigqeni emibili engenalutho ebhokisini lengxoxo izokwenza izingxabano zomsebenzi we-COUNTIF.

Lezi zimpikiswano zitshela umsebenzi ukuthi yisiphi isimo esizihlolisayo nokuthi yiziphi amaseli azobala uma isimo sifinyelelwe.

05 ka-07

Ukufaka i-Range Argument

Kulesi sifundo sifuna ukuthola inombolo ye-Sales Reps esithengise ama-oda angaphezu kwama-250 omnyaka.

Ingxabano ye- Range isitshela umsebenzi we-COUNTIF ukuthi iyiphi iqembu lamaseli elingahlola lapho lizama ukuthola imigomo ecacisiwe ye- "> 250" .

Izinyathelo Zokufundisa

1. Ebhokisini lebhokisi , chofoza ku- Range line.
2. Gqamisa amaseli e-E3 kuya ku-E9 ekhasini lokusebenzela ukuze ufake lezi zikhombisi zeseli njengebanga lokufundwa ngumsebenzi.
3. Shiya ibhokisi lengxoxo evulekile isinyathelo esilandelayo ekufundiseni.

06 ka-07

Ukufaka Ukungqubuzana Okubalulekile

Ukuphikisana okubalulekile kulandisa ku-COUNTIF ukuthi iyiphi idatha okufanele izame ukuyithola ku- Range ingxabano.

Nakuba idatha yangempela - njengombhalo noma izinombolo ezinjenge- "> 250" zingangena ebhokisini lebhokisi le ngxabano kungcono kakhulu ukufaka ireferensi yeseli ebhokisini lebhokisi, njenge-D12 bese ufaka idatha esifuna ukuyifanisa singene kulelo sese ekhasini lokusebenzela.

Izinyathelo Zokufundisa

1. Chofoza kulayini Wokuqondisa ebhokisini lengxoxo.
2. Chofoza kuseli D12 ukuze ufake leyo reference reference. Umsebenzi uzosesha uhla olukhethiwe kwisinyathelo sangaphambilini sedatha efana nanyana yini idatha engena kule seli.
3. Chofoza OK ukuvala ibhokisi lengxoxo bese uqedela umsebenzi we-COUNTIF.
4. Impendulo ye- zero kufanele ivele esitokisini E12 - iseli lapho singene khona umsebenzi - ngoba asikazanga ukufaka idatha emkhakheni Wokunqunywa (D12).

07 ka-07

Ukungeza i-Criteria Yokusesha

Isinyathelo sokugcina esifundweni ukufaka izindlela esizifuna ukuthi umsebenzi ufanane nazo.

Kulesi simo sifuna inombolo ye-Sales Reps ngama-oda angaphezu kwama-250 omnyaka.

Ukwenza lokhu singena > 250 singena ku-D12 - iseli ekhonjisiwe emsebenzini njengokuqukethe ukuphikisana .

Izinyathelo Zokufundisa

1. Ngeselula D12 uhlobo > 250 bese ucindezela ukhiye wokungena kwikhibhodi.
2. Inombolo 4 kufanele ivele esitokisini E12.
3. Umgomo we->> 250 " uhlangene emaseli amane kukholomu E: E4, E5, E8, E9. Ngakho-ke lezi yizona kuphela amaseli ezibalwa ngomsebenzi.
4. Uma uchofoza ku-cell E12, umsebenzi ophelele
   = COUNTIF (E3: E9, D12) ibonakala kwibha yefomula ngaphezulu kwephepha lokusebenzela .