Összegzés
A COUNTIFS (vagy COUNTIF) változó tartományú konfigurálásához használhatja az OFFSET funkciót. A bemutatott példában a B11 képlete a következő:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Ez a képlet megszámolja a nem üres cellákat egy olyan tartományban, amely B5-től kezdődik és 2 sorral végződik a cella felett, ahol a képlet él. Ugyanezt a képletet másoljuk és beillesztjük 2 sorral az adatok utolsó bejegyzése alá, az ábra szerint.
Magyarázat
A bemutatott példában a B11 képlete a következő:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Belülről kifelé haladva a változó tartomány beállításának munkáját az OFFSET funkció végzi itt:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
Az OFFSET öt argumentummal rendelkezik, és a következőképpen van konfigurálva:
- referencia = B $ 5, a B5 cellánál kezdődik, a sor lezárva
- sor = 0, nulla sort eltol a kezdő cellától
- cols = 0, nulla oszlop eltolása indítja a cellát
- magasság = ROW () - ROW (B $ 5) -1 = 5 sor magas
- szélesség = 1 oszlop széles
A tartomány magasságának sorokban történő meghatározásához a ROW függvényt használjuk így:
ROW()-ROW(B$5)-1 // work out height
Mivel a ROW () visszaadja az "aktuális" cella sorszámát (vagyis azt a cellát, amelyben a képlet él), így egyszerűsíthetjük:
=ROW()-ROW(B$5)-1 =11-5-1 =5
A fenti konfigurációval az OFFSET visszaadja a B5: B9 tartományt közvetlenül a COUNTIFS-nek:
=COUNTIFS(B5:B9,"") // returns 4
Vegye figyelembe, hogy a fenti képlet B $ 5-re történő hivatkozása vegyes hivatkozás, az oszlop relatív és a sor zárva van. Ez lehetővé teszi a képlet más oszlopba másolását és továbbra is működik. Például a C12-re másolás után a képlet:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Megjegyzés: Az OFFSET egy ingatag függvény, amely teljesítményproblémákat okozhat nagy vagy összetett munkalapokon.
KÖZVETLEN és CÍM
Egy másik megközelítés az INDIRECT és a CÍM függvényeken alapuló képlet használata. Ebben az esetben egy tartományt állítunk össze szövegként, majd az INDIRECT segítségével értékeljük a szöveget referenciaként. A B11 képlete a következő lenne:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Az ADDRESS függvény egy ilyen tartomány összeállításához használható:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
Az ADDRESS első példányában megadjuk a sorszámot 5-ös keménykódolású értékként, és az oszlopszámot megadjuk a COLUMN funkcióval:
=ADDRESS(5,COLUMN()) // returns "$B$5"
Második esetben az "aktuális" sor számát mínusz 2-vel adjuk meg, az aktuális oszlopot pedig a COLUMN funkcióval:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Miután összefogtuk ezt a két értéket, megvan:
"$B$5:$B$9" // as text
Vegye figyelembe, hogy ez egy szöveges karakterlánc. Érvényes referenciává konvertáláshoz a INDIRECT-et kell használnunk:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Végül a B11 képlete a következővé válik:
=COUNTIFS($B$5:$B$9,"") // returns 4
Megjegyzés: A INDIRECT egy ingatag függvény, amely teljesítményproblémákat okozhat nagy vagy összetett munkalapokon.
