Letzte benutzte Zelle in einer Zeile oder Spalte (Frank Kabel in memoriam)

Gruppe: Tabellennavigation

Beitrag: Letzte benutzte Zelle in einer Zeile oder Spalte (Frank Kabel in memoriam)

Aufgabe

Wie kann man die letzte benutzte Zelle in einer Zeile oder Spalte ermitteln ?

	A	B	C	D	E	F	G
1	e		3	4	e		7
2
3	f
4
5	2
6
7	e
8

Lösung

Spalte A bzw. Zeile 1

letzte Spalte einer Zeile:

{=MAX((A1:HH1<>"")*SPALTE(A:HH))}

entsprechender Eintrag:

{=INDEX(A1:HH1;MAX((A1:HH1<>"")*SPALTE(A:HH)))}

letzte Zeile einer Spalte:

{=MAX((A1:A999<>"")*ZEILE(1:999))}

entsprechender Eintrag:

{=INDEX(A1:A999;MAX((A1:A999<>"")*ZEILE(1:999)))}

Folgende Formeln erkennen auch eine Zelle in der als Formelergebnis "" oder eine Fehlermeldung steht:

{=MAX(NICHT(ISTLEER(A1:HH1))*SPALTE(A:HH))}

bzw.

{=MAX(NICHT(ISTLEER(A1:A999))*ZEILE(1:999))}

ohne Array

=MAX(INDEX((A1:HH1<>"")*SPALTE(A1:HH1);0))

=MAX(INDEX((A1:A999<>"")*ZEILE(A1:A999);0))

und dann INDEX(1:1;...) bzw. INDEX(A:A;...) drumrum

wohl eine Gemeinschaftskreation von Frank Kabel, Harlan Grove, Franz Pölt (sieht wie ne Seminar-Gruppenarbeit aus):

=VERWEIS(2;1/(A1:HH1<>"");SPALTE(A1:HH1))

=VERWEIS(2;1/(A1:HH1<>"");1:1)

sowie

=VERWEIS(2;1/(A1:A999<>"");ZEILE(A1:A999))

=VERWEIS(2;1/(A1:A999<>"");A:A)

Diese geniale Formel erhält den Namen: "Frank Kabel - Lösung".
(Frank verunglückte tödlich im Januar 2005.)

Die sechstletzte Zelle (Inhalt) wäre übrigens:

=VERWEIS(2;1/(A6:A999<>"");A:A)

wenn innerhalb der letzten 6 Zellen Leerzellen egal sind.
bzw.

{=INDEX(A1:A999;KGRÖSSTE((A1:A999<>"")*ZEILE(1:999);6))}

wenn es die sechstletzte gefüllte Zelle sein soll.

Erläuterung

Wegen der Abartigkeit der VERGLEICH-Formeln: siehe Nr. 50 und 52

Betr. die VERWEIS-Formeln, die einwandfrei funktionieren, hab ich Bauchschmerzen - in dauerhaften Programmen von mir werde ich sie nicht verwenden - wegen dieses Microsoft-Hinweises:
"Die hier erläuterte Version von VERWEIS steht nur aus Gründen der Kompatibilität mit anderen Tabellenkalkulationsprogrammen zur Verfügung."
Wird diese Funktion irgendwann gekillt; - oder, was soll das ausdrücken?

Erläuterung der VERWEIS-Formel
reduziere erstmal den Bereich auf:

=VERWEIS(2;1/(A1:A20<>"");A:A)

damit Du Dir Teilergebnisse ansehen kannst.
Markiere A1:A20<>"" - drücke F9 und Du siehst 20 mal WAHR bzw. FALSCH je nachdem, ob die Zelle gefüllt oder leer ist.
Markiere jetzt 1/(A1:A20<>"") - drücke F9 und Du siehst 20 mal 1 bzw. #DIV/0!
Und das ist der dirty trick!
In der Beschreibung der Verweisfunktion heißt es: "Die zum Suchvektor gehörenden Werte müssen in aufsteigender Reihenfolge angeordnet sein: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSCH, WAHR"
#DIV/0! wird von der Funktion nicht registriert sondern nur die Einser.
Gesucht wird nach der 2, die nicht vorkommt. Statt der 2 kannst Du auch jede andere Zahl größer 1 nehmen.
Und es heißt:
"Kann die Funktion VERWEIS keinen Wert finden, der mit dem jeweiligen Suchkriterium übereinstimmt, verwendet sie den zum Suchvektor gehörenden Wert, der bezogen auf das Suchkriterium der nächstkleinere ist."
Der nächstkleinere ist die letzte 1.
WF

Das Portal zum Thema Excel-Formeln