Das grosse Archiv mit 621
VBA-freien Formellösungen und Tips
 
   
 
 ÜBER UNS   NEWS   FORMELN   TIPPS   KONTAKT   GäSTEBUCH   FORUM   LINKS 
   
Übersicht   Zauberbuch   Rätselbuch   Sparbuch   Bücher-Downloads / Errata  
 
Tabellennavigation (8) ->

Letzte benutzte Zelle in einer Zeile oder Spalte (Frank Kabel in memoriam) (48)
 
 
Aufgabe
Wie kann man die letzte benutzte Zelle in einer Zeile oder Spalte ermitteln ?

       A               B               C               D               E               F               G       
1     7  
2              
3            
4              
5            
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(WENN(ISTZAHL(VERGLEICH({"".-1E+307};A:A;-1));VERGLEICH({"".-1E+307};A:A;-1)))
=INDEX(A:A;MAX(WENN(ISTZAHL(VERGLEICH({"".-1E+307};A:A;-1));VERGLEICH({"".-1E+307};A:A;-1))))

wohl eine Gemeinschaftskreation von Frank Kabel, Harlan Grove, Franz Pölt (sieht bald wie ne Seminar-Gruppenarbeit aus):
=VERWEIS(2;1/(1:1<>"");SPALTE(1:1))
=VERWEIS(2;1/(1:1<>"");1:1)
sowie
=VERWEIS(2;1/(A1:A65535<>"");ZEILE(A:A))
=VERWEIS(2;1/(A1:A65535<>"");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?
WF

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.


 
    zurück


 
powered by telltarget