Zählen von Werten in Excel - und finde die Letzte Zeile, die einen Wert enthält

Werte zählen in Excel

Anzahl Werte in einer Spalte

Wenn die Anzahl Werte in einer Spalte gefragt ist, so bietet Excel die Funktion '=ANZAHL()', die die Anzahl an Zahlenwerten liefert. Dies funktioniert auch bei Datumswerten, jedoch nicht bei alphanumerischen Werten:

Für das Zählen alphanumerischer Werte kann man folgenden Kniff anwenden. Man gibt die grösste Grösse des Datenbereichs an (z.B. 40000 Zeilen) und zählt dann die Anzahl leerer Zellen:

Wie das Beispiel zeigt, zählt die Formel  '=D3-ANZAHLLEEREZELLEN(BEREICH.VERSCHIEBEN(D6;0;0;D3))' die Anzahl nichtleerer Zellen in den 40000 Zellen ab D6, auch wenn zwischen den Zellen mit Werten leere Zellen liegen.

Letzte Zeile mit Werten

Wenn aber nicht die Anzahl von Werten gefragt ist, sondern die letzte Zeile, die einen Wert enthält, so bietet uns Excel keine direkte Lösung an. Die Nummer der Zeile mit dem letzten Wert kann z.B. bei Matrizenformeln praktisch sein, die nur gerade den Datenbereich umfassen sollen, auch wenn die Daten Lücken haben können. Auf den nächsten Zeilen verwenden wir zunehmend kompaktere Formulierung was die Dateigrösse der Mappe anbetrifft.

Variante A

Wir möchten im obigen Beispiel die letzte Zeile der Salte "D" finden, die noch einen nicht-leeren Wert enthält. Dazu führen wir eine Spalte mit einer Hilfsberechnung ein, die die Zeilennummer ausgibt, wenn in Spalte "D" auf der gleichen Zeile ein Wert steht, und sonst 0. In Zelle "E6", d.h. der obersten Zelle der Hilfsspalte geben wir '=ZEILE()*(1-(D6=""))' ein und ziehen die Formel danach bis zur Zelle "E15" nach unten.

Die Verwendung der Subtraktion '(1-())' veranlasst die Konversion des logischen Ergebnisses aus dem Vergleich '(D6="")' und entspricht einem 'NICHT()'. Der Ausdruck '(1-(D6=""))' ist gleich 0 für leere Zellen und andernfalls gleich 1. Damit können wir die Funktion 'ZEILE()' direkt anmultiplizieren.

Der Maximalwert aus Spalte "E" ist unsere gesuchte Zeilennummer.

Als Vorbereitung auf eine Variante, die ohne Hilfsspalte auskommt, formulieren wir die Berechnungen in Spalte "E" als Matrixformeln um. Dabei verwenden wir der Einfachheit halber feste Bereiche:

'{=ZEILE(D6:D15)*(1-(D6:D15=""))}'

Die Eingabe der Formel als Matrixformel erfolgt, indem man zunächst den Bereich markiert, dann die Formel eingibt (ohne die geschweiften Klammern) und mit "Strg"+"Umschalt"+"Eingabe". Dann erscheinen die geschweiften Klammern. Wie man sieht, liefern die beiden Formuliereungen die selben Werte, unterscheiden sich aber wesentlich in der Tatsache, dass 'ZEILE()' einen ganzen Bereich verarbeitet und für jede Zeile im Bereich die Nummer als Element einer Matrix liefert. Der Ausdruck '(D6:D15="")' wird ebenfalls für alle Zellen im angegebenen Bereich ausgewertet und liefert eine Matrix mit WAHR und FALSCH, die gleich dimensioniert ist, wie die Matrix, die von ZEILE(D6:D15) erzeugt wird.

Das Ergebnis des gesamten Ausdrucks ist wiederum eine Matrix, die im Bereich "G6:G15" angezeigt wird.

Variante B

Für die Bestimmung der letzten Zeile in Spalte "D", die einen nichtleeren Wert enthält, sind wir nicht darauf angewiesen, die Matrix mit den Zeilennummern aus dem Ausdruck '{=ZEILE(D6:D15)*(1-(D6:D15=""))}' anzuzeigen. Wir können diese Matrix direkt in die Funktion 'MAX()' einfügen, und dieses Ergebnis ist eine einzelne Zahl. Somit können wir in einer Zelle, im aktuellen Beispiel "E4" folgende Formel als Matrix-Formel eingeben:

'{=MAX(ZEILE(BEREICH.VERSCHIEBEN(D6;0;0;D3))*(1-(BEREICH.VERSCHIEBEN(D6;0;0;D3)="")))}'

liefert uns die gesuchte Zeilennummer, und zwar ohne Verwendung einer Hilfsspalte. Die Verwendung der Formel 'BEREICH.VERSCHIEBEN(D6;0;0;D3)' entspricht dem Bereich "D6:D40005", lässt sich aber durch Änderung des Wertes in D3 leicht an verschiedene Maximalgrössen anpassen.