Ukubala amaseli angenalutho noma angenalutho ku-Excel

Umsebenzi we-Excel COUNTBLANK

I-Excel inemisebenzi eminingi yokubala engasetshenziswa ukubala inani lamaseli ebangeni elikhethiwe eliqukethe uhlobo oluthile lwedatha.

Umsebenzi we- COUNTBLANK umsebenzi wokubala inani lamaseli ebangeni elikhethiwe ukuthi:

• Okungenalutho - amaseli angenayo idatha
• Ama-cell - angenalutho aphethe ifomula ebuyisela okungenalutho noma okungenalutho

I-Syntax ne-Arguments

I- syntax yomsebenzi ibhekisela kokuhlelwa komsebenzi futhi ihlanganisa igama lomsebenzi, amabakaki, ukwahlukanisa kwama-comma, nokuphikisana .

I-syntax yomsebenzi we-COUNTBLANK yile:

= COUNTBLANK (Range)

Ububanzi (okudingekayo) yiqembu lama cell ukuthi umsebenzi ukucinga.

Amanothi:

• I-argument ye- Range kumele ibe yiqembu elinamatheli lamaseli.
• Njengoba i-COUNTBLANK ingavumeli amanani amaningi ukuba afakwe kwi- Range argument, izimo eziningi zomsebenzi zingangeniswa kwifomula elilodwa ukuthola inombolo yamangqamuzana angenalutho noma angenalutho emigqeni emibili noma engaphenduki, njengoba kuboniswe kulayini 3 kwesibonelo esingenhla:
  • = COUNTBLANK (A2: A10) + COUNTBLANK (B2: B10)

Isibonelo

Esikhathini esingenhla, amafomu amaningana aqukethe umsebenzi we-COUNTBLANK asetshenziselwa ukubala inani lamangqamuzana angenalutho noma angenalutho emigqeni emibili yedatha: A2 kuya ku-A10 no-B2 kuya ku-B10.

Ukufaka umsebenzi we-COUNTBLANK

Izinketho zokufaka umsebenzi kanye neziphakamiso zalo zifaka:

1. Ukuthayipha umsebenzi ophelele oboniswe ngenhla kwiseli lokusebenzela;
2. Ukukhetha umsebenzi kanye neziphakamiso zayo usebenzisa ibhokisi lebhokisi lengxoxo ye- COUNTBLANK

Nakuba kungenzeka nje ukuthayipha umsebenzi ophelele ngesandla, abantu abaningi bakuthola kulula ukusebenzisa ibhokisi lebhokisi elibheka ukufaka i-syntax efanele yomsebenzi.

Qaphela: Amafomula aqukethe izimo eziningi ze-COUNTBLANK, njengalezo ezibonwe emigqeni yesithathu nesine sesithombe, angeke angene ngokusebenzisa ibhokisi lengxoxo lomsebenzi, kodwa kufanele afakwe ngesandla.

Izinyathelo ezingezansi kwekhava zingena emsebenzini we-COUNTBLANK oboniswe kuseli D2 esithombeni ngenhla usebenzisa ibhokisi lengxoxo lomsebenzi.

Ukuvula ibhokisi le-COUNTBLANK lokusebenza lomsebenzi

1. Chofoza kuseli D2 ukuze wenze iseli elisebenzayo - yilapho imiphumela yomsebenzi izoboniswa khona;
2. Chofoza kuthebhu yefomu yebhebhoni ;
3. Chofoza kwimisebenzi eminingi> Isitatimende ukuvula uhlu lwehla lokusebenza;
4. Chofoza ku- COUNTBLANK ohlwini ukuze ulethe ibhokisi lengxoxo lomsebenzi;
5. Chofoza ku- Range line ebhokisini lengxoxo;
6. Qinisekisa amaseli A2 kuya ku-A10 kuphepha lokusebenzela ukuze ufake lezi zikhombisi njenge- Range argument;
7. Chofoza OK ukuze uqedele umsebenzi bese ubuyela ekhasini lokusebenzela;
8. Impendulo "3" ivela esitokisini C3 ngoba kunezingqamuzana ezintathu ezingenalutho (A5, A7, no-A9) ebangeni A ukuya ku-A10.
9. Uma uchofoza ku-cell E1 umsebenzi ophelele = COUNTBLANK (A2: A10) ibonakala kwibha yefomula ngaphezulu kwephepha lokusebenzela.

COUNTBLANK Amafomu Okungalingani

Okunye okunye ku-COUNTBLANK okungasetshenziswa kufaka phakathi labo aboniswe emigqeni emihlanu kuya kweziyisikhombisa esithombeni ngenhla.

Isibonelo, ifomula emgqeni wesihlanu, = COUNTIF (A2: A10, "") , isebenzisa umsebenzi we- COUNTIF ukuthola inombolo yamangqamuzana angenalutho noma angenalutho ku-A2 kuya ku-A10 futhi inikeza imiphumela efanayo njengo-COUNTBLANK.

Ngakolunye uhlangothi, amafomula emigqeni eyisithupha nesikhombisa, athole amangqamuzana angenalutho noma angenalutho emigqeni eminingi futhi abale kuphela lawo maseli ahlangabezana nazo zombili izimo. Lezifomula zinikeza ukuguquguquka okungaphezulu kwezilingo ezingenalutho noma ezingenalutho ebangeni libalwa.

Isibonelo, ifomula emgqeni wesithupha, = COUNTIFS (A2: A10, "", B2: B10, "") , isebenzisa i-COUNTIFS ukuthola amangqamuzana angenalutho noma angenalutho emazingeni amaningi futhi ibala kuphela lawo maseli angenawo amangqamuzana umugqa ofanayo womabili omabili-irowu ayisikhombisa.

Ifomula emgqeni wesikhombisa, = I-SUMPRODUCT ((A2: A10 = "ubhanana") * (B2: B10 = "")) , isebenzisa umsebenzi we-SUMPRODUCT ukubala kuphela lawo maseli ezinhlangothini eziningi ezihlangabezana nezimo zombili ebangeni lokuqala (A2 kuya ku-A10) futhi engenalutho noma engenalutho ebangeni lesibili (B2 kuya ku-B10).