Samenvatting
Om COUNTIFS (of COUNTIF) te configureren met een variabel bereik, kunt u de OFFSET-functie gebruiken. In het getoonde voorbeeld is de formule in B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Deze formule telt niet-lege cellen in een bereik dat begint bij B5 en eindigt 2 rijen boven de cel waarin de formule voorkomt. Dezelfde formule wordt gekopieerd en 2 rijen onder de laatste invoer in de gegevens geplakt, zoals weergegeven.
Uitleg
In het getoonde voorbeeld is de formule in B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Als u van binnen naar buiten werkt, wordt het instellen van een variabel bereik hier gedaan door de OFFSET-functie:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET heeft vijf argumenten en is als volgt geconfigureerd:
- reference = B $ 5, begin bij cel B5, rij vergrendeld
- rijen = 0, nul rijen verschoven vanaf de startcel
- cols = 0, offset nul kolommen startcel
- hoogte = RIJ () - RIJ (B $ 5) -1 = 5 rijen hoog
- breedte = 1 kolom breed
Om de hoogte van het bereik in rijen te berekenen, gebruiken we de RIJ-functie als volgt:
ROW()-ROW(B$5)-1 // work out height
Aangezien ROW () het rijnummer van de "huidige" cel retourneert (dwz de cel waarin de formule leeft), kunnen we het als volgt vereenvoudigen:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Met de bovenstaande configuratie retourneert OFFSET het bereik B5: B9 rechtstreeks naar COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Merk op dat de verwijzing naar B $ 5 in de bovenstaande formule een gemengde verwijzing is, waarbij de kolom relatief is en de rij vergrendeld. Hierdoor kan de formule naar een andere kolom worden gekopieerd en toch werken. Eenmaal gekopieerd naar C12 is de formule bijvoorbeeld:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Opmerking: OFFSET is een vluchtige functie en kan prestatieproblemen veroorzaken in grote of complexe werkbladen.
Met INDIRECT en ADRES
Een andere benadering is om een formule te gebruiken die is gebaseerd op de functies INDIRECT en ADDRESS. In dit geval stellen we een bereik samen als tekst en gebruiken we INDIRECT om de tekst als referentie te evalueren. De formule in B11 zou zijn:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
De ADDRESS-functie wordt gebruikt om een bereik als volgt te construeren:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
In het eerste geval van ADDRESS leveren we rijnummer als de hardgecodeerde waarde 5 en geven we het kolomnummer met de COLUMN-functie:
=ADDRESS(5,COLUMN()) // returns "$B$5"
In het tweede geval leveren we het "huidige" rijnummer min 2, en de huidige kolom met de COLUMN-functie:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Na het samenvoegen van deze twee waarden hebben we:
"$B$5:$B$9" // as text
Let op: dit is een tekstreeks. Om naar een geldige referentie te converteren, moeten we INDIRECT gebruiken:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Ten slotte wordt de formule in B11:
=COUNTIFS($B$5:$B$9,"") // returns 4
Opmerking: INDIRECT is een vluchtige functie en kan prestatieproblemen veroorzaken in grote of complexe werkbladen.
