Zusammenfassung
Um COUNTIFS (oder COUNTIF) mit einem variablen Bereich zu konfigurieren, können Sie die OFFSET-Funktion verwenden. In dem gezeigten Beispiel lautet die Formel in B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Diese Formel zählt nicht leere Zellen in einem Bereich, der bei B5 beginnt und 2 Zeilen über der Zelle endet, in der die Formel lebt. Dieselbe Formel wird wie gezeigt 2 Zeilen unter dem letzten Eintrag in den Daten kopiert und eingefügt.
Erläuterung
In dem gezeigten Beispiel lautet die Formel in B11:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Die Einrichtung eines variablen Bereichs erfolgt von innen nach außen durch die OFFSET-Funktion:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET hat fünf Argumente und ist folgendermaßen konfiguriert:
- Referenz = B $ 5, beginnen Sie in Zelle B5, Zeile gesperrt
- Zeilen = 0, Versatz von Nullzeilen von der Startzelle
- Spalten = 0, Versatz Null Spalten Startzelle
- Höhe = REIHE () - REIHE (B $ 5) -1 = 5 Zeilen hoch
- Breite = 1 Spalte breit
Um die Höhe des Bereichs in Zeilen zu berechnen, verwenden wir die ROW-Funktion wie folgt:
ROW()-ROW(B$5)-1 // work out height
Da ROW () die Zeilennummer der "aktuellen" Zelle zurückgibt (dh die Zelle, in der die Formel lebt), können wir dies folgendermaßen vereinfachen:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Mit der obigen Konfiguration gibt OFFSET den Bereich B5: B9 direkt an COUNTIFS zurück:
=COUNTIFS(B5:B9,"") // returns 4
Beachten Sie, dass der Verweis auf B $ 5 in der obigen Formel eine gemischte Referenz ist, bei der die Spalte relativ und die Zeile gesperrt ist. Dadurch kann die Formel in eine andere Spalte kopiert werden und funktioniert weiterhin. Nach dem Kopieren nach C12 lautet die Formel beispielsweise:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Hinweis: OFFSET ist eine flüchtige Funktion und kann in großen oder komplexen Arbeitsblättern zu Leistungsproblemen führen.
Mit INDIREKT und ADRESSE
Ein anderer Ansatz besteht darin, eine Formel zu verwenden, die auf den Funktionen INDIREKT und ADRESSE basiert. In diesem Fall stellen wir einen Bereich als Text zusammen und verwenden dann INDIRECT, um den Text als Referenz auszuwerten. Die Formel in B11 wäre:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Mit der Funktion ADRESSE wird ein Bereich wie folgt erstellt:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
In der ersten Instanz von ADDRESS geben wir die Zeilennummer als fest codierten Wert 5 an und geben die Spaltennummer mit der Funktion COLUMN an:
=ADDRESS(5,COLUMN()) // returns "$B$5"
In der zweiten Instanz liefern wir die "aktuelle" Zeilennummer minus 2 und die aktuelle Spalte mit der Funktion COLUMN:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Nachdem wir diese beiden Werte miteinander verknüpft haben, haben wir:
"$B$5:$B$9" // as text
Beachten Sie, dass dies eine Textzeichenfolge ist. Um in eine gültige Referenz zu konvertieren, müssen wir INDIRECT verwenden:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Schließlich lautet die Formel in B11:
=COUNTIFS($B$5:$B$9,"") // returns 4
Hinweis: INDIRECT ist eine flüchtige Funktion und kann in großen oder komplexen Arbeitsblättern zu Leistungsproblemen führen.
