Excel Formel Letzter Wert In Spalte
Manchmal möchte human being den jeweils letzten Eintrag seiner Excel-Tabelle zusätzlich noch an einer anderen Stelle ausgeben. Zum Beispiel ganz am Anfang einer sehr langen Tabelle, um den Eintrag jederzeit im Blick zu haben. Oder um ihn vielleicht in einem Formular ausgeben zu können.
Da die Tabelle aber ständig länger wird, sich das Ende also dynamisch nach unten verschiebt, kann human being nicht einfach eine Verknüpfung zu einer festen Zeile erstellen, denn homo kennt ja das Ende der Tabelle noch nicht.
Mit der geschickten Kombination einiger Excel-Standardfunktionen gibt es auch für dieses Trouble ein Happy End. Und zwar ganz ohne VBA-Programmierung bemühen zu müssen.
Und so geht'due south:
Durchgängige Tabelle
Am leichtesten ist es, wenn man eine fortlaufende Tabelle chapeau, in der in jeder Zeile ein Eintrag steht, so wie in meinem kleinen Beispiel. Hier habe ich eine Adressenliste und ich möchte den jeweils letzten Eintrag oben rechts in dem hervorgehobenen Bereich ausgeben lassen:
Beispiel: Adressenliste
Wie gelingt uns das nun?
Dazu muss ich eigentlich nur wissen, in welcher Zeile der letzte Eintrag steht, sprich: wieviele Zeilen meine Tabelle hat. Da meine Adressenliste keine Zahlen, sondern alphanumerische Werte enthält, verwende ich dazu dice Excel-Funktion ANZAHL2. Und das Ergebnis davon benutze ich in der Alphabetize-Funktion, um den Inhalt auszugeben:
ANZAHL2 und INDEX in Kombination
Zur Erläuterung:
Die ANZAHL2-Funktion gibt dice Anzahl der nichtleeren Zellen in dem angegebenen Bereich wieder. Da ich ja nicht weiß, wie lange meine Tabelle irgendwann sein wird, habe ich in meinem Beispiel die komplette Spalte A angegeben („A:A)" und erhalte damit den Wert 17 zurück (die Überschrift wird mitgezählt).
Und an die INDEX-Funktion übergibt man in der einfachsten Version zwei Parameter. Nämlich einen Tabellenbereich und eine Zeilennummer. Zurückgeliefert wird dann der Inhalt der betreffenden Zelle.
Auch hier gebe ich die komplette Spalte A an („A:A"), der zweite Parameter wird durch dice ANZAHL2-Funktion geliefert (= 17). Damit gibt dice INDEX-Funktion den Inhalt der 17. Zeile in Spalte A aus.
Tipp:
Sollte meine Tabelle nicht wie hier in der ersten Zeile beginnen, sondern z.B. erst in der vierten, dann muss ich einfach noch dice Anzahl der Leerzeilen addieren. Und meine Funktion würde dann eben and then aussehen:
=INDEX(A:A;ANZAHL2(A:A)+three)
Zusätzliche Leerzeilen am Anfang
Was aber, wenn es innerhalb meiner Tabelle leere Zeilen gibt?
Tabelle mit Leerzeilen
Sollten sich innerhalb meiner Tabelle Leerzeilen befinden, funktioniert die oben vorgestellte Lösung leider nicht. Dice ANZAHL2-Funktion ermittelt wieder die Zahl 17, denn es gibt ja einschließlich der Überschrift 17 gefüllte Zeilen. Diese Zahl entspricht aber nicht mehr unserer letzten Zeilennummer, denn durch die Leerzeilen ist meine letzte Zeile in der Tabelle nach unten gerutscht:
Problem: Leere Zeilen
Aber natürlich gibt es auch hierfür eine Lösung!
Da die ANZAHL2-Funktion nicht zum gewünschten Ergebnis führt, verwende ich stattdessen dice VERGLEICH-Funktion, um nach der letzten Zeile in meiner Tabelle zu suchen.
Zur Erinnerung:
Die VERGLEICH-Funktion sucht in einem Zellbereich (Parameter two) nach dem angegebenen Suchkriterium (Parameter i) und gibt dessen Position zurück. Dabei kann human being über einen optionalen dritten Parameter noch dice Art und Weise bestimmten, wie gesucht werden soll:
=VERGLEICH(Suchkriterium; Bereich; Vergleichstyp)
Der Vergleichstyp kann entweder 1, 0 oder -ane sein. Wenn er weggelassen wird, unterstellt Excel den Wert 1. Die Excel-Hilfe sagt dazu folgendes:
Excel-Hilfe zum Vergleichstyp
Und genau diese dritte Option -1 machen wir uns hier auf eine zugegebenermaßen etwas ungewöhnliche Weise zunutze:
=VERGLEICH("";A:A;-one)
Als Suchkriterium werden zwei doppelte Anführungszeichen übergeben, was bedeutet, ich suche in Spalte A (= Paramter ii) nach einer leeren Zelle. Und jetzt kommt der Play a trick on mit dem Vergleichstyp -ane (= Parameter 3). Excel geht laut Hilfefunktion bei der Suche davon aus, dass es sich um eine absteigend sortierte Liste handelt.
Das ist für uns völlig irrelevant, führt aber dazu, dass dice Suche nicht beim ersten gefundenen Treffer aufhört, sondern bis zum Ende der Tabelle weitergeht. Denn Excel nimmt ja an, dass die Liste absteigend sortiert ist und immer „kleinere" Werte kommen, je weiter human being in der Tabelle nach unten geht. Und da laut Hilfefunktion der kleinste Wert gesucht wird, der mit dem Suchkriterium („") übereinstimmt oder größer ist, trifft dies immer auf den letzten Wert in der Tabelle zu:
Liefert auch bei leeren Zeilen das gewünschte Ergebnis
Die then ermittelte Position wird wieder an die INDEX-Funktion übergeben und liefert damit den gewünschten Zelleninhalt, wie man im Bild oben sieht.
Ich gebe zu, dass diese Lösung wahrscheinlich nicht auf auf Anhieb für jedermann nachvollziehbar ist. Manchmal muss man in Excel etwas um die Ecke denken und kommt dafür auf Lösungen, die vielleicht auch von den Excel-Schöpfern nicht unbedingt vorgesehen waren.
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Trouble sitzt meistens vor dem Computer.
Source: https://www.tabellenexperte.de/letzte-zeile-in-excel-finden/
Posted by: stoneboad1945.blogspot.com
0 Response to "Excel Formel Letzter Wert In Spalte"
Post a Comment