Das Portal zum Thema Excel-Formeln

=ZÄHLENWENN(A:A;"") zählt die Leerzellen bis zur letzten gefüllten Zelle.
ergänzt:

=ZÄHLENWENN(A:A;"")+ANZAHL2(A:A)

hätten wir also eine Formel (ohne Array), die Zeile der letzten gefüllten Zelle zu bestimmen.

Löscht man jetzt die letzte(n) Zelle(n), bleibt das Ergebnis unverändert.

=ZÄHLENWENN(A:A;"") zählt also die Leerzellen bis zur letzten Zelle, die jemals gefüllt war.

Schreibt man =ZÄHLENWENN(A:A;"") in eine ansonsten völlig leere Tabelle und in eine andere Spalte als A, wird die Zeilennummer ausgegeben, in der diese Formel steht.

Äußerst seltsam !???



Kommentar von Otto S. 04.08.04:

Garnicht seltsam!

=ZÄHLENWENN(A:A;"") zählt die Leerzellen der Spalte A bis zur letzten gefüllten Zelle des benutzten Bereiches [in VBA-Notation: Intersect(.Range(.Cells(1, 1), .UsedRange), .Columns(1)) ]

Löscht man jetzt die letzte(n) Zelle(n) in Spalte A, verändert sich das Ergebnis! =ZÄHLENWENN(A:A;"") addiert zu seinem alten Ergebnis die Zahl der gelöschten Zellen mit ehemaligem Inhalt hinzu.

=ZÄHLENWENN(A:A;"") zählt also die Leerzellen bis zur letzten Zelle, die in dieser Sitzung gefüllt war. Dies verändert sich auch nicht durch eine 

Neuberechnung (F9). Der Grund: Die Tabelle wird nicht reorganisiert, die Zeilen/Zellen sind weiter vorhanden. Die Reorganisation passiert erst beim Abspeichern der Mappe. Allerdings wird beim Abspeichern der Mappe die Funktion =ZÄHLENWENN(A:A;"") nicht neuberechnet, da Bill Gates nicht ahnt, dass nach dem Speichern die Zeilen tatsächlich weg sind.

Die korrekte Neuberechnung kann auf zwei Wegen erzwungen werden:

1. man füllt eine (obere) leere Zelle und löscht sie wieder
2. man positioniert sich auf der Zelle mit dem =ZÄHLENWENN(A:A;"") und drückt F2 und Enter

Dies erklärt auch, warum bei leerer Spalte A die Nummer der Zeile des =ZÄHLENWENN(A:A;"") geliefert wird, falls dies tatsächlich die letzte beschriebene Zeile ist.

Aber =ZÄHLENWENN(5:5;"") spinnt auf den ersten Blick.

Steht diese Formel in einer ansonsten leeren Mappe in irgendeiner Zelle der Zeilen 1 bis 4, ist das Ergebnis 0, steht die Formel in einer ansonsten leeren Tabelle in einer Zelle der Zeilen 6 bis 65536 dann liefert es die Spaltennummer der Zelle mit dem Zählenwenn.

Und =ZÄHLENWENN(K:K;"") spinnt auf den ersten Blick.

Steht diese Formel in einer ansonsten leeren Mappe in irgendeiner Zelle der Spalten A bis J, ist das Ergebnis 0, steht die Formel in einer ansonsten leeren Tabelle in einer Zelle der Spalten L bis IV dann liefert es die Zeilennummer der Zelle mit dem Zählenwenn.

Am Rande bemerkt: der Formelassistent gaukelt hier immer das Ergebnis 0 vor.

Auch hier hilft der Kollege VBA: =ZÄHLENWENN(5:5;"") zählt die leeren Zellen im Bereich Intersect(.Range(.Cells(1, 1), .UsedRange), .Rows(5)),

=ZÄHLENWENN(K:K;"") zählt die leeren Zellen im Bereich Intersect(.Range(.Cells(1, 1), .UsedRange), .Columns(8)), 

PS: getestet habe ich mit Excel 2000