Povzetek
Če želite konfigurirati COUNTIFS (ali COUNTIF) s spremenljivim obsegom, lahko uporabite funkcijo OFFSET. V prikazanem primeru je formula v B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Ta formula šteje neprazne celice v obsegu, ki se začne pri B5 in konča 2 vrstici nad celico, v kateri živi formula. Ista formula se kopira in prilepi 2 vrstici pod zadnjim vnosom v podatke, kot je prikazano.
Pojasnilo
V prikazanem primeru je formula v B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Delo od znotraj navzven deluje pri nastavitvi spremenljivega obsega s funkcijo OFFSET tukaj:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET ima pet argumentov in je konfiguriran tako:
- reference = B $ 5, začnite pri celici B5, vrstica zaklenjena
- vrstice = 0, odmik nič vrstic od začetne celice
- cols = 0, odmik nič stolpcev začetna celica
- višina = ROW () - ROW (B $ 5) -1 = 5 vrstic visoko
- širina = 1 stolpec širok
Za določitev višine obsega v vrsticah uporabimo funkcijo ROW, kot je ta:
ROW()-ROW(B$5)-1 // work out height
Ker ROW () vrne številko vrstice "trenutne" celice (tj. Celice, v kateri živi formula), lahko poenostavimo takole:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Z zgornjo konfiguracijo OFFSET vrne obseg B5: B9 neposredno na COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Upoštevajte, da je sklic na B $ 5 v zgornji formuli mešani sklic s sorodnikom stolpca in vrstico zaklenjeno. To omogoča kopiranje formule v drug stolpec in še vedno deluje. Na primer, ko je kopija kopirana v C12, je formula:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Opomba: OFFSET je spremenljiva funkcija in lahko povzroči težave z zmogljivostjo na velikih ali zapletenih delovnih listih.
Z POSREDNO in NASLOV
Drug pristop je uporaba formule, ki temelji na funkcijah POSREDNO in NASLOV. V tem primeru obseg sestavimo kot besedilo, nato pa z INDIRECT ocenimo besedilo kot referenco. Formula v B11 bi bila:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Funkcija ADDRESS se uporablja za izdelavo obsega, kot je ta:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
V prvem primeru ADDRESS vnesemo številko vrstice kot trdno kodirano vrednost 5 in številko stolpca s funkcijo COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
V drugem primeru vnesemo "trenutno" številko vrstice minus 2 in trenutni stolpec s funkcijo COLUMN:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Po združitvi teh dveh vrednosti skupaj imamo:
"$B$5:$B$9" // as text
Upoštevajte, da gre za besedilni niz. Za pretvorbo v veljavno referenco moramo uporabiti INDIRECT:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Končno formula v B11 postane:
=COUNTIFS($B$5:$B$9,"") // returns 4
Opomba: INDIRECT je spremenljiva funkcija in lahko povzroči težave z zmogljivostjo na velikih ali zapletenih delovnih listih.
