Sažetak
Da biste konfigurirali COUNTIFS (ili COUNTIF) s promjenjivim rasponom, možete koristiti funkciju OFFSET. U prikazanom primjeru formula u B11 je:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Ova formula broji neprazne stanice u rasponu koji počinje na B5 i završava 2 reda iznad ćelije u kojoj živi formula. Ista formula se kopira i lijepi 2 retka ispod zadnjeg unosa u podatke kao što je prikazano.
Obrazloženje
U prikazanom primjeru formula u B11 je:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Radeći iznutra prema van, posao postavljanja promjenjivog raspona obavlja funkcija OFFSET ovdje:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET ima pet argumenata i konfiguriran je ovako:
- referenca = B $ 5, započinje u ćeliji B5, red zaključan
- redovi = 0, pomak nule redaka od početne ćelije
- cols = 0, offset nula stupaca početna ćelija
- visina = RED () - RED (B $ 5) -1 = 5 redaka visoko
- širina = 1 stupac širok
Da bismo izračunali visinu raspona u redovima, koristimo funkciju ROW ovako:
ROW()-ROW(B$5)-1 // work out height
Budući da ROW () vraća broj retka "trenutne" stanice (tj. Stanice u kojoj živi formula), možemo pojednostaviti ovako:
=ROW()-ROW(B$5)-1 =11-5-1 =5
S gornjom konfiguracijom, OFFSET vraća raspon B5: B9 izravno u COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Primijetite da je referenca na B $ 5 u gornjoj formuli mješovita referenca, s relativnim stupcem i zaključanim retkom. To omogućuje kopiranje formule u drugi stupac i dalje rad. Na primjer, nakon kopiranja u C12, formula je:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Napomena: OFFSET je hlapljiva funkcija i može uzrokovati probleme s performansama na velikim ili složenim radnim listovima.
S INDIREKTNOM i ADRESOM
Drugi je pristup upotreba formule koja se temelji na funkcijama INDIRECT i ADDRESS. U ovom slučaju skupimo raspon kao tekst, a zatim koristimo INDIRECT za procjenu teksta kao reference. Formula u B11 bila bi:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Funkcija ADDRESS koristi se za izgradnju raspona poput ovog:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
U prvom primjeru ADRESE, broj retka dostavljamo kao kodiranu vrijednost 5, a broj stupca dobivamo s funkcijom COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
U drugom slučaju isporučujemo "trenutni" broj retka minus 2, a trenutni stupac s funkcijom COLUMN:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Nakon spajanja ove dvije vrijednosti zajedno, imamo:
"$B$5:$B$9" // as text
Imajte na umu da je ovo tekstualni niz. Da bismo pretvorili u valjanu referencu, trebamo upotrijebiti INDIRECT:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Napokon, formula u B11 postaje:
=COUNTIFS($B$5:$B$9,"") // returns 4
Napomena: INDIRECT je hlapljiva funkcija i može uzrokovati probleme s performansama na velikim ili složenim radnim listovima.
