I-Excel COUNT - I-FORIRECT Ifomula

Izinombolo zokubala, Izinsuku, noma Umbhalo ku-Excel

Ukusebenzisa umsebenzi we- INDIRECT emafomulini we-Excel kwenza kube lula ukushintsha ububanzi bezinkomba ezisetshenziselwa ifomula ngaphandle kokuthi uhlele ifomula ngokwayo.

I-INDIRECT ingasetshenziswa ngemininingwana yemisebenzi eyamukela isithenjwa seseli njengengxabano njengemisebenzi ye- SUM kanye ne-COUNT.

Esikhathini esilandelayo, ukusebenzisa i-INDIRECT njengengxabano ye-COUNT kudala ububanzi obunamandla bezindinganiso zamaseli ezingabalwa ngomsebenzi.

I-INDIRECT ingabe lokhu kuphendulela idatha yombhalo - ngezinye izikhathi ibizwa ngokuthi yochungechunge lombhalo - ibe yereferensi yeseli.

Isibonelo: Ukusebenzisa i-Dynamic Range ne-COUNT - INDIRECT Formula

Lesi sibonelo sisekelwe kudatha eboniswe esithombeni ngenhla.

I- formula engu- COUNT - INDIRECT edalwe kusifundo ngu:

= COUNT (INDIRECT (E1 & ":" & E2))

Kule fomula, impikiswano yomsebenzi we-INDIRECT iqukethe:

• izingqikithi zeseli E1 ne-E2 - equkethe idatha yombhalo D1 no-D5
• umqhubi wobubanzi - ikholoni (:) elizungezwe ngamamaki okucaphuna kabili ( "" ), okuguqula ikoloni ibe yichungechunge lombhalo
• Ama-ampersands amabili ( & ) asetshenziselwa ukuncintisana noma ukujoyina ndawonye ikholoni enezikhombo ze-cell E1 no-E2

Umphumela wukuthi i-INDIRECT iguqula isicingo sezinhlamvu D1: D5 esitokisini sefoni bese sidlulisela ku-COUNT umsebenzi okumele uhlanganiswe.

Ukushintsha kakhulu iFormu & # 39; s Range

Khumbula, umgomo wukuthi udale ifomula ngebanga eliguqukayo - elilodwa elingashintshwa ngaphandle kokuhlela ifomula ngokwayo.

Ngokushintsha idatha yombhalo ekhona kumaseli E1 no-E2, ukusuka ku-D1 no-D5 kuya ku-D3 no-D6 njengesibonelo, ububanzi obuphelele ngomsebenzi bungashintshwa kalula kusuka ku-D1: D5 kuya ku-D3: D6.

Lokhu kuqeda isidingo sokuhlela ifomu ku-cell G1.

Ukufaka idatha yokufundisa

1. Faka idatha elandelayo kumaseli D1 kuya ku-E2
2. Cell Data D1 - 1 D2 - amabili D3 - 3 D5 - 5 D6 - ayisithupha E1 - D1 E2 - D5 F1 - Bala:

Ukufaka i-COUNT - INDIRECT Formula

1. Chofoza kuseli G1 - yilapho imiphumela yalesi sibonelo izoboniswa khona
2. Faka ifomula: = COUNT (INDIRECT (E1 & ":" & E2))
3. Cindezela ukhiye wokungena kwikhibhodi ukuqedela umsebenzi
4. Iseli G1 kufanele libe nempendulo ye-3

Qaphela ukuthi umsebenzi we-COUNT ubala kuphela amaseli aqukethe izinombolo, ngakho-ke noma amane amangqamuzana amahlanu ebubanzi be-D1: D5 aqukethe idatha, amaseli amathathu kuphela aqukethe izinombolo.

Amaseli angenalutho noma aqukethe idatha yombhalo ayakwaziswa ngomsebenzi.

Ukushintsha i-Formula & # 39; s Range

1. Chofoza kuseli E1
2. Faka ireferensi yeseli D3
3. Cindezela ukhiye we-Enter ekhibhodi ukuze uthuthele ku-cell E2
4. Kule seli engena ireferensi yeselula D6
5. Cindezela ukhiye wokungena kwikhibhodi
6. Impendulo esitokisini G1 kufanele ishintshele ku-2 kusukela amaseli amabili kuphela ebangeni elisha D3: D6 iqukethe izinombolo

I-COUNTA, COUNTBLANK ne-INDIRECT

Iminye imisebenzi emibili ye-Excel yi- COUNTA - ebala amaseli aqukethe noma yiluphi uhlobo lwemininingwane - elishiya kuphela amangqamuzana angenalutho noma angenalutho, kanye ne- COUNTBLANK , elibala kuphela amaseli angenalutho noma angenalutho ebangeni.

Njengoba kokubili le misebenzi ine- syntax efanayo nomsebenzi we-COUNT, ingafakwa endaweni engenhla nge-INDIRECT ukuze udale amafomula alandelayo:

= COUNTA (INDIRECT (E1 & ":" & E2))

= COUNTBLANK (INDIRECT (E1 & ":" & E2))

Ukuze uhla lwe-D1: D5, i-COUNTA izobuyisa impendulo ye-4 - ngoba amane amangqamuzana ayisihlanu aqukethe idatha, futhi i-OUNTBLANK iphinde iphendule ngo-1 - ngoba kukhona kuphela iseli elingenalutho ebangeni.