Die Funktion =ANZAHL2(A:A) würde eigentlich die letzte Zeile zurückgeben, wenn alle Werte in Spalte A durchgängig beschrieben wären und sich unter den sichtbaren Werten nicht noch Formeln oder Funktionen befinden würden, die das Ergebnis „“ liefern.

Sollten Sich aber Formeln in Spalte A befinden, welche das Ergebnis =““ liefern, werden diese ebenso mitgezählt.

Und leere Zellen machen das Ergebnis von =ANZAHL2(A:A) direkt unbrauchbar. Man kann zwar auch die relative neue Funktion =ANZAHLLEEREZELLEN(A:A) verwenden, stolpert hier aber auch über die „“-Zellen.

Es muss also eine Funktion her, die folgende Punkte abdeckt:
– Es soll die letzte beschriebene Zelle einer Spalte ermittelt werden.
– Die letzte Zelle ist die Zeile mit der größten Zeilennummer.
– Leere Zellen sollen nicht berücksichtigt werden.
– Zellen mit dem Formelergebnis =““ sollen auch nicht berücksichtigt werden.

Seit Excel 2010 steht die Funktion AGGREGAT zur Verfügung. AGGREGAT(14;…) ermittelt wie KGRÖSSTE den k-größten Wert eines Bereichs – also den größten, zweitgrößten etc.

AGGREGAT(14) ist zudem eine Matrix- bzw. Arrayfunktion. Das bedeutet, es wird nicht generell über einen Bereich geprüft, sondern es wird jeder Wert des Bereichs einzeln betrachtet und geschaut, ob dieser spezielle Bedingungen erfüllt.

=AGGREGAT(14;4;(A:A<>"")*ZEILE(A:A);1)

Hier wird also geprüft, ob ein Wert in Spalte A ungleich leer ist. Falls ja, wird hier mit WAHR, also mit 1 multipliziert. Zusätzlich wird von jedem Wert die Zeilennummer ermittelt. Als Ergebnis von (A:A<>"")*ZEILE(A:A) resultiert eine Liste von Zeilennummern, der Zellen auch tatsächlich Werte beinhalten.
AGGREGAT(14;..;..;1) sorgt nun nur noch dafür, aus der resultierenden Liste den 1-größten Wert zu ermitteln.

Sollte eine ältere Version von Excel vorliegen, kann man auch eine Matrix-Formel mit {}-Klammern erzeugen. Das Verfahren wird in der Reihe Matrix-Funktion Teil 09 in Video Excel # 281 beschrieben.

Zum YouTube-Video Excel # 309:
Videolink: http://youtu.be/V8gne7jQp0w