Du bist hier::--Excel Formeln – X letzten Einträge einer Spalte anzeigen

Excel Formeln – X letzten Einträge einer Spalte anzeigen

In diesem Excel Artikel werde ich ihnen heute zeigen, wie Sie es schaffen, dass die letzten x Einträge einer Spalte angezeigt werden. X Steht dabei natürlich für eine Zahl, die Sie beliebig auswählen können. Wie das alles funktioniert, erfahren Sie nun.

Im Alltag können Sie diese Funktion zum Beispiel dafür benutzen, dass nur die neuesten Werte einer Auflistung separat angezeigt werden sollen, da Sie aktuell relevanter oder aussagekräftiger sind. Natürlich können Sie die Werte auch alle manuell abschreiben, jedoch wird das bei großen Datenmengen lästig und außerdem können sich auch Fehler einschleichen. Wie Sie das alles in Sekundenschnelle mit einer einzigen Formel lösen können, erfahren Sie jetzt.

Beginn:

Bevor sie damit anfangen, die letzten Werte auszulesen, müssen natürlich erst einmal Werte vorhanden sein. Am besten verwenden Sie dazu die Spalte A, da Sie dann anschließend die weiter unten aufgeführte Formel nicht noch extra verändern müssen. In der Spalte dürfen Sie aber wirklich alles eintragen, was ihnen einfällt. Egal ob Zahlen, Texte oder Kombinationen aus Sonderzeichen und ähnlichem, die Formel hat damit kein Problem und geht mit allen Varianten fehlerfrei um. Selbst leere Zellen dürfen in der Spalte vorkommen, auch wenn danach noch weitere Werte folgen. Wie Sie sehen können, habe ich in meinem Beispiel zur Verdeutlichung Zahlen und Texte, sowie auch eine leere Zelle gewählt. Möchten Sei die Formel erst einmal zu Demonstrationszwecken benutzen, empfehle ich ihnen einfach ausgefallene Strings zu wählen, um zu testen, was die Formel alles kann. Haben Sie ihre Daten nun eingegeben und ihre Tabelle sieht ungefähr so aus wie meine im Screenshot, so gehen Sie nun zum nächten Abschnitt, in dem ihnen erklärt wird, wie Sie nun mithilfe einer Formel die letzen x Einträge aus der Spalte angezeigt bekommen.

(Zur Darstellung in Originalgröße Abbildungen anklicken)
01-excel-formeln-x-letze-eintraege-einer-spalte-anzeigen-beginn-200.png?nocache=1309813316024

Letzte x Einträge anzeigen:

Nachdem die Werte eingetragen wurden, können Sie jetzt die letzten ausgeben. Dabei steht es ihnen frei, wie viele der letzten Werte angezeigt werden sollen, in meinem Beispiel habe ich die Formel so gewählt, dass die letzten fünf Werte angezeigt werden. Wie Sie das verändern können, erfahren Sie weiter unten. Nun aber zu der Formel. Sollte es ihnen möglich sein, dass Sie die Formel nicht in eine Zelle der Spalte A schreiben müssen, so empfehle ich ihnen dies, da die Formel die Zellen A1 bis A1000 benutzt. Geben Sie die Formel dann in eine dieser Zellen ein, gibt es einen Fehler bezüglich des Zirkelverweises. Nehmen Sie deshalb am besten die Zelle B1 und schreiben Sie in sie folgendes hinein:

=INDEX(A$1:A$1000;KGRÖSSTE(WENN(A$1:A$1000<>"";ZEILE($1:$1000));6-ZEILE()))

Haben Sie die Formel eingegeben, so werden Sie merken, dass die Formel einen Fehler oder ein falsches Ergebnis liefert. Haben Sie sich schon mit Excel befasst, werden Sie wahrscheinlich wissen, woran das liegt, diese Formel ist nämlich eine Matrixformel. Das bedeutet für Sie konkret nun, dass Sie zuerst noch einmal in die Zelle klicken müssen, in die Sie die Formel geschrieben haben, in meinem Beispiel also die Zelle B1. Danach klicken Sie oben in die Bearbeitungszeile der Formel. Haben Sie auch das gemacht, so drücken Sie nun auf ihrer Tastatur abschließend die Tasten STRG+SHIFT+Enter. Nun erscheint in der Zelle der richtige Ausgabewert. In meinem Fall also der fünftletzte String „920“. Aber auch die Formel hat sich verändert, sie sieht, wie es für eine Matrixformel typisch ist, nun so aus:

{=INDEX(A$1:A$1000;KGRÖSSTE(WENN(A$1:A$1000<>"";ZEILE($1:$1000));6-ZEILE()))}

Diese Formel wird auch für die anderen Zellen benutzt, in denen nun die anderen Begriffe eingetragen werden sollen. Das heißt für sie, dass Sie die Formel nach unten kopieren müssen. Klicken Sie dazu nochmals in die Zelle, in der die Formel steht, also die Zelle B1 in meinem Beispiel. Danach fahren Sie mit der Maus über die rechte untere Ecke der Zelle woraufhin sich der Mauszeiger in ein Kreuz verwandelt. Ist das passiert, so drücken Sie nun die linke Maustaste und halten Sie sie gedrückt. Nun fahren Sie mit der Maus einige Zellen nach unten. Die genaue Anzahl der Zellen, die Sie nach unten fahren müssen, ist die Anzahl an den letzten Begriffen, die angezeigt werden sollen, in meinem Beispiel sind es also 5 Zellen, d.h. bis B5. Schließen Sie noch weitere Zellen mit ein, so bekommen Sie in ihnen den Fehler „#ZAHL!“ angezeigt.
Diese Formel funktioniert aber nur, wenn die letzten fünf Strings angezeigt werden sollen. Möchten Sie eine andere Zahl, so benötigen sie auch eine andere Formel. Das ist aber keine komplett andere Formel, sondern einfach nur die von oben veränderte Formel. Aktuell gibt die „6“ in der Formel die Anzahl an den letzten Strings an, die ausgegeben werden sollen. Verändern sie diese Zahl einfach so, dass sie um 1 größer ist als die Anzahl der letzten Begriffe, die ausgegeben werden sollen. Möchten Sie also, dass die letzten drei Strings angezeigt werden, so schreiben Sie in der Formel anstelle einer „6“ eine „4“.

02-excel-formeln-x-letze-eintraege-einer-spalte-anzeigen-formel-470.png?nocache=1309813461401

Nun wissen Sie, wie man mit einer einzigen kleinen Formel die x letzten Begriffe einer Spalte ausgeben kann. Auch das Verändern der Anzahl der auszugebenden Begriffe ist ganz einfach.

Von |2018-07-25T14:16:54+00:00August 8th, 2011|Kategorien: Tabellenkalkulation|4 Kommentare

Über den Autor:

4 Comments

  1. Klaus.2 8. August 2011 um 19:13 Uhr

    Hallo Supportnet-Admins, hallo yannick_b!

    Zuerst das Gute, da kann ich mich kurz fassen: Gegenüber der Vorlage http://www.excelformeln.de/formeln.html?welcher=54, bei der der entsprechende Hinweis fehlt, wird hier zumindest – wenn auch unnötig umständlich und mit viel zu vielen Worten, WEGEN DES WORT-HONORARS – erklärt, dass diese Formel als Matrixformel (mit STRG + UMSCHALT + EINGABEund bitte kein Wort mehr!) einzugeben ist.

    Ansonsten ist dieser Artikel, wie auch alle anderen dieses Autors, unerträglich umständlich, verwirrend, weitschweifig und unverschämt egoistisch! Hier kann man nachlesen, was Menschen für einen Vierteleuro (- das offizielle Wort-Honorar von Supportnet) alles tun, wenn man sie lässt.

    Die Spitze der Unverschämtheit ist meiner Meinung nach allerdings, dass – um der VIELEN SCHÖNEN WORTE, die man darum ZUSÄTZLICH machen kann, UND NUR DARUM GEHT ES DEM AUTOR – die Original-Lösung verschlechtert wurde. Die Anzahl X der letzten Einträge, die angezeigt werden sollen, wird nicht mehr als variabler Bestandteil der Formel in der Weise deutlich erkennbar gemacht, dass sie als Zellbezug in die Formel eingeht, sondern sie wird als Konstante hineingewurschtelt. Außerdem wurde die durchaus sehr sinnvolle WENN-Bedingung am Anfang der Formel weggelassen:

    Ursprünglich hieß es bei http://www.excelformeln.de/formeln.html?welcher=54:
    =WENN(ZEILE(A1)>C$1;"";INDEX(… C$1+1-ZEILE(A1))))

    Bei yannick_b wurde daraus: =INDEX(… 6-ZEILE()))

    Wer erfahren will, wieso gerade 6, wird genötigt, folgenden, nicht zufällig so umfangreichen, Text zu lesen:

    … Die genaue Anzahl der Zellen, die Sie nach unten fahren müssen, ist die Anzahl an den letzten Begriffen, die angezeigt werden sollen, in meinem Beispiel sind es also 5 Zellen, d.h. bis B5. Schließen Sie noch weitere Zellen mit ein, so bekommen Sie in ihnen den Fehler „#ZAHL!“ angezeigt.
    Diese Formel funktioniert aber nur, wenn die letzten fünf Strings angezeigt werden sollen. Möchten Sie eine andere Zahl, so benötigen sie auch eine andere Formel. Das ist aber keine komplett andere Formel, sondern einfach nur die von oben veränderte Formel. Aktuell gibt die „6“ in der Formel die Anzahl an den letzten Strings an, die ausgegeben werden sollen. Verändern sie diese Zahl einfach so, dass sie um 1 größer ist als die Anzahl der letzten Begriffe, die ausgegeben werden sollen. Möchten Sie also, dass die letzten drei Strings angezeigt werden, so schreiben Sie in der Formel anstelle einer „6“ eine „4“.

    Das sind genau 150 Worte mehr als bei originalgetreuer Wiedergabe der Original-Formel, also 37,50 unredlich "verdiente" Euro.

    Leute, macht die Augen auf und lasst Euch solche Unverschämtheiten nicht länger bieten!

    Mit freundlichen Grüßen
    Klaus

    (E-Mail-Adresse auf Anfrage)

  2. halfstone 8. August 2011 um 19:17 Uhr

    Hi Klaus,

    willst du das jetzt bei jeder Formel dazu als Kommentar schreiben?
    Bitte nicht, melde dich einfach per e-mail oder PM bei mir.

    Gruß Fabian

  3. Klaus.2 8. August 2011 um 19:49 Uhr

    Hallo Fabian,

    freut mich, dass jemand meine Kritik wahrgenommen hat. Wie die Sache ausgeht, werden wir ja sehen. Ich bin jedenfalls der Meinung, dass es mehrere gute Gründe dafür gibt, dass es so nicht weitergehen sollte.

    Qualität ist auf jeden Fall auch ein Wert. Manche sagen, Qualität geht vor Masse. Wenn aber weniger Masse gleichzeitig mehr Qualität ist, sollte die Entscheidung nicht so schwer fallen.

    Gut, vorerst werde ich meinen Senf nicht weiter zu jedem Elaborat
    des hier kritisierten Autoren dazugeben, Hinweise auf Fehler ausgenommen.

    MfG
    Klaus

  4. KID24 11. Oktober 2016 um 20:32 Uhr

    Ich möchte gern die Formel in Zeile 4 beginnen, also habe ich aus allen 1 eine 4 gemacht, und sie ist nicht in Spalte A sondern in SpalteDR und bezieht sich auf Spalte BR, wo die Daten drinstehen.
    Zeile habe ich auch auf 4 gesetzt, aber Excel meldet einen Bezugsfehler.
    Wie geht es richtig?

Hinterlassen Sie einen Kommentar