Izinombolo zokubala, iiDates, okanye i-Text kwi-Excel
Ukusebenzisa umsebenzi we- INDIRECT kwiifomula ze-Excel kwenza kube lula ukutshintsha uluhlu lweeferensi zesetyenzisiweyo ezisetyenzisiweyo ngaphandle kokuba uhlele ifom ngokwayo.
I-INDIRECT ingasetyenziselwa nemisebenzi emininzi eyamkela iselferensi yeseli njengengxabano efana nemisebenzi ye- SUM kunye ne-COUNT.
Kwimeko yokugqibela, ukusebenzisa i-INDIRECT njengokuba ingxabano ye-COUNT idala uluhlu olunamandla olubhekiselele kwimizi yeselenki ekwazi ukuzaliswa ngumsebenzi.
I-INDIRECT ngaba oku kuphendulela idatha yombhalo - ngamanye amaxesha kubhekiswe njengomgca wombhalo - kwisithenjwa seeseli.
Umzekelo: Ukusebenzisa iRynamic Range neFomula ye-COUNT - INDIRECT
Lo mzekelo usekelwe kwidatha eboniswe kumfanekiso ongentla.
Ifomula ye- COUNT - INDIRECT eyenziwe kwikhosi yile:
= COUNT (INDIRECT (E1 & ":" & E2))
Kule ndlela, imbambano yomsebenzi we-INDIRECT iqulethe:
- Iingxelo zeeseli E1 kunye ne-E2 - eziqulethe idatha yombhalo D1 ne-D5
- umqhubi webala - ikholoni (:) ejikelezwe ngamanqaku okucaphuna aphindwe kabini ( "" ), eguqula ikoloni ibe yintambo yomxholo
- Amacers ampersands amabili ( & ) asetshenziselwa ukuxubungula okanye ukudibanisa kunye nekholoni kunye neenkcukacha zeeseli E1 no-E2
Isiphumo kukuba i-INDIRECT iguqula umtya wesicatshulwa D1: D5 kwisalathisi yesellu kwaye uyidlulisela kunye nomsebenzi we-COUNT ukuba uzaliswe.
Ukutshintsha ngamandla iFormu & # 39; s Range
Khumbula, injongo kukudala ifom ngebala elitshintshayo-enye engatshintshwa ngaphandle kokulungisa ifom ngokwayo.
Ngokutshintsha idatha yombhalo ekhoyo kwiiseli E1 no-E2, ukusuka kwi-D1 kunye no-D5 ukuya kwi-D3 kunye no-D6, umzekelo, ububanzi obuthelwe ngumsebenzi bunokutshintshwa lula ukusuka kwi-D1: D5 ukuya kwi-D3: D6.
Oku kususa isidingo sokuhlela ifom kwifom yeG1.
Ukufaka i-Tutorial Data
- Faka idatha elandelayo kwiiseli D1 ukuya ku-E2
- Idatha yedatha D1 - 1 D2 - ezimbini D3 - 3 D5 - 5 D6 - ezithandathu E1 - D1 E2 - D5 F1 - Bala:
Ukufaka i-Formula ye-COUNT-INDIRECT
- Cofa kwiseli G1 - yilapho iziphumo zalo mzekelo ziza kuboniswa
- Faka ifom: = COUNT (INDIRECT (E1 & ":" & E2))
- Cinezela ukhiye wokungena kwikhibhodi ukugqiba umsebenzi
- Iseli G1 kufuneka iqulethe impendulo ye-3
Qaphela ukuba umsebenzi we-COUNT ubala kuphela iiseli ezinamanani, ngoko nangona ezine zeeseli ezinhlanu kwi-D1: D5 ziqulethe idatha, iiseli ezintathu kuphela ziqulethe iinombolo.
Iiseli ezingenanto okanye eziqulethwe ngedata yombhalo zinyanzelwa ngumsebenzi.
Ukuguqula iFormula & # 39; s Range
- Cofa kwiseli E1
- Faka isalathisi yesalathisi D3
- Cinezela ukhiye wokungena kwibhodibhodi ukuya kwi cell E2
- Kulesi selesi faka isalathisi yesalathisi D6
- Cinezela ukhiye wokungena kwikhibhodi
- Impendulo kwiseli G1 kufuneka ishintshele kwi-2 kuba iiseli ezimbini kuphela kwi-D3 entsha: I-D6 iqulethe amanani
I-COUNTA, COUNTBLANK ne-INDIRECT
Iminye imisebenzi emibili ye-Excel yi- COUNTA - ebala iiseli equle naluphi uhlobo lweedatha - lugxothisa kuphela iinguli ezingenanto okanye ezingenanto, kunye ne- COUNTBLANK , ebalala kuphela iinguli ezingenanto okanye ezingenanto kwibala.
Ekubeni kokubili le mi sebenzi inemisebenzi efana neye-COUNT, ingafakwa endaweni engentla apha nge-INDIRECT ukwenza ezi fomula zilandelayo:
= COUNTA (INDIRECT (E1 & ":" & & E2))
= COUNTBLANK (INDIRECT (E1 & ":" & E2))
Kuluhlu D1: D5, COUNTA ubuya kubuya impendulo ye-4 - kuba ezine zeetyeli ezinhlanu ziqulethe idatha, kwaye i-OUNTBLANK iphinde iphendule ngo-1 - kuba kukho enye inqwelwana engenanto ebaleni.