Santrauka
Norėdami sukonfigūruoti COUNTIFS (arba COUNTIF) su kintamu diapazonu, galite naudoti funkciją OFFSET. Parodytame pavyzdyje formulė B11 yra:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Ši formulė skaičiuoja ne tuščias langelius diapazone, kuris prasideda nuo B5 ir baigiasi 2 eilutėmis virš langelio, kuriame formulė gyvena. Ta pati formulė nukopijuojama ir įklijuojama 2 eilutėmis žemiau paskutinio duomenų įrašo, kaip parodyta.
Paaiškinimas
Parodytame pavyzdyje formulė B11 yra:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Dirbant iš vidaus, kintamo diapazono nustatymo darbą atlieka funkcija OFFSET:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET turi penkis argumentus ir yra sukonfigūruotas taip:
- nuoroda = B $ 5, prasideda langelyje B5, eilutė užrakinta
- eilučių = 0, nulio eilučių atstatymas nuo pradžios langelio
- cols = 0, nulio stulpelių poslinkis pradedant langelį
- aukštis = ROW () - ROW (B $ 5) -1 = 5 eilutės aukštai
- plotis = 1 stulpelio pločio
Norėdami nustatyti diapazono aukštį eilutėse, mes naudojame funkciją ROW taip:
ROW()-ROW(B$5)-1 // work out height
Kadangi ROW () pateikia „dabartinės“ langelio (ty langelio, kuriame formulė gyvena), eilutės numerį, galime supaprastinti taip:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Naudodamas aukščiau pateiktą konfigūraciją, OFFSET grąžina diapazoną B5: B9 tiesiai į COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Atkreipkite dėmesį, kad aukščiau pateiktoje formulėje nuoroda į B $ 5 yra mišri nuoroda, o stulpelio santykis ir eilutė užrakinti. Tai leidžia formulę nukopijuoti į kitą stulpelį ir vis tiek veikti. Pvz., Nukopijavus į C12, formulė yra:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Pastaba: OFFSET yra nepastovi funkcija ir gali sukelti didelių ar sudėtingų darbalapių našumo problemų.
Su NETIESIOGINIU ir ADRESU
Kitas būdas yra naudoti formulę, pagrįstą funkcijomis NETIESIOGINIS ir ADRESAS. Šiuo atveju mes surenkame diapazoną kaip tekstą, tada naudokite „INDIRECT“, kad įvertintume tekstą kaip nuorodą. B11 formulė būtų:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Funkcija ADDRESS naudojama kuriant tokį diapazoną:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
Pirmuoju ADDRESS egzemplioriumi mes pateikiame eilutės numerį kaip 5 koduotą reikšmę ir stulpelio numerį pateikiame su funkcija COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
Antruoju atveju „dabartinio“ eilutės numerį atimame 2, o dabartinį stulpelį - funkcija COLUMN:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Sujungę šias dvi vertybes kartu, turime:
"$B$5:$B$9" // as text
Atkreipkite dėmesį, kad tai yra teksto eilutė. Norėdami konvertuoti į galiojančią nuorodą, turime naudoti „INDIRECT“:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Galiausiai formulė B11 tampa:
=COUNTIFS($B$5:$B$9,"") // returns 4
Pastaba: netiesioginė funkcija yra nepastovi ir gali sukelti didelių ar sudėtingų darbalapių našumo problemų.
