Wie sucht man mit SVERWEIS und gibt den letzten übereinstimmenden Wert in Excel zurück?
In Excel wird die SVERWEIS-Funktion häufig verwendet, um Daten aus einer Tabelle zu suchen und abzurufen. Standardmäßig gibt „SVERWEIS“ jedoch nur den ersten übereinstimmenden Wert zurück, den er findet. Was aber, wenn Sie stattdessen den letzten übereinstimmenden Wert zurückgeben müssen? Um dies zu erreichen, verwenden wir alternative Formeln mit Funktionen wie „SUCHEN“, „XLOOKUP“, „INDEX“, „VERGLEICH“ oder „Kutools“. Wir werden auch untersuchen, wie diese Methoden für eine bessere Leistung und Benutzerfreundlichkeit optimiert werden können.
SVERWEIS und Rückgabe des letzten übereinstimmenden Werts in Excel
SVERWEIS und Rückgabe des letzten übereinstimmenden Werts mit der SUCHEN-Funktion
Die „SUCHEN“-Funktion ist ein leistungsstarkes Werkzeug in Excel, das verwendet werden kann, um den letzten übereinstimmenden Wert in einem Datensatz zu finden.
Bitte geben Sie die folgende Formel in Ihre angegebene Zelle ein und ziehen Sie dann den Ausfüllkästchen nach unten, um die letzten entsprechenden Werte wie folgt zu erhalten:
=LOOKUP(2,1/($A$2:$A$12=E2),$C$2:$C$12)
- „A2:A12“ ist der Bereich, der die Suchspalte enthält;
- „E2“ ist die Zelle, die den zu suchenden Wert enthält;
- „C2:C12“ ist der Bereich, der die Rückgabewerte enthält.
- „1/($A$2:$A$12=E2)“ erstellt ein Array von #DIV/0! Fehlern und 1, wo die Bedingung erfüllt ist.
- „SUCHEN(2,...)“ durchsucht das Array nach der letzten 1 und findet effektiv die letzte Übereinstimmung.
SVERWEIS und Rückgabe des letzten übereinstimmenden Werts mit Kutools für Excel
„Kutools für Excel“ bietet eine einfache und effiziente Möglichkeit, fortgeschrittene Suchvorgänge durchzuführen, einschließlich der Rückgabe des letzten übereinstimmenden Werts aus einem Datensatz. Folgen Sie diesen Schritten, um dies ohne komplexe Formeln zu erreichen.
Nach der Installation von Kutools für Excel gehen Sie bitte wie folgt vor:
1. Klicken Sie auf „Kutools“ > „Erweiterte SVERWEIS“ > „Von unten nach oben suchen“, siehe Screenshot:
2. Im Dialogfeld „Von unten nach oben suchen“ führen Sie bitte die folgenden Aktionen aus:
- Wählen Sie die Zellen mit den Suchwerten und Ausgabebereich aus den Abschnitten „Platzierungsbereich der Liste und Bereich der zu durchsuchenden Werte“ aus;
- Geben Sie dann die entsprechenden Elemente im Abschnitt „Datenbereich“ an.
- Klicken Sie abschließend auf die Schaltfläche „OK“.
Dann wurden alle letzten übereinstimmenden Elemente auf einmal zurückgegeben, siehe Screenshot:
Wenn Sie den #NV-Fehlerwert durch einen anderen Textwert ersetzen möchten, klicken Sie einfach auf die Schaltfläche „Optionen“ und aktivieren Sie die Option „Ersetze nicht gefundene Werte (#N/A) durch angegebenen Wert“, und geben Sie dann den benötigten Text ein.
SVERWEIS und Rückgabe des letzten übereinstimmenden Werts mit INDEX- und VERGLEICH-Funktionen
Während die traditionelle SVERWEIS-Funktion diese Funktionalität nicht unterstützt, können Sie die leistungsstarken „INDEX“- und „VERGLEICH“-Funktionen kombinieren, um dies zu erreichen. Diese Methode ist dynamisch, effizient und funktioniert in allen Excel-Versionen.
Bitte geben Sie die folgende Formel in Ihre angegebene Zelle ein, drücken Sie in Excel 2019 und früheren Versionen die Tastenkombination „Strg“ + „Umschalt“ + „Enter“, drücken Sie in Excel 365, Excel 2021 und späteren Versionen direkt die „Enter“-Taste.
=INDEX($C$2:$C$12,MATCH(2,1/($A$2:$A$12=E2)))Ziehen Sie dann das Ausfüllkästchen nach unten, um die letzten entsprechenden Werte wie folgt zu erhalten:
- „A2:A12“ ist der Bereich, der die Suchspalte enthält;
- „E2“ ist die Zelle, die den zu suchenden Wert enthält;
- „C2:C12“ ist der Bereich, der die Rückgabewerte enthält.
- „1/($A$2:$A$12=E2)“ erstellt ein Array von #DIV/0! Fehlern und 1, wo die Bedingung erfüllt ist.
- „VERGLEICH(2, 1/($A$2:$A$12=E2))“ sucht nach der Zahl 2 im Array, das durch 1/($A$2:$A$12=E2) erstellt wurde. Da 2 nicht im Array ist, findet VERGLEICH die Position der letzten gültigen 1, was der letzten übereinstimmenden Zeile in $A$2:$A$12 entspricht.
- „INDEX($C$2:$C$12,...)“ verwendet die Zeilennummer aus VERGLEICH, um den entsprechenden Wert aus $C$2:$C$12 abzurufen.
SVERWEIS und Rückgabe des letzten übereinstimmenden Werts mit der XLOOKUP-Funktion
Die XLOOKUP-Funktion, die in Excel 365, 2021 und späteren Versionen eingeführt wurde, ist ein leistungsstarkes und vielseitiges Ersatz für „SVERWEIS“ und „HLOOKUP“. Eine ihrer Hauptfunktionen ist die Möglichkeit, Suchvorgänge in umgekehrter Reihenfolge durchzuführen, was sie perfekt macht, um den letzten übereinstimmenden Wert in einem Datensatz zu finden.
Bitte geben Sie die folgende Formel in Ihre angegebene Zelle ein und ziehen Sie dann das Ausfüllkästchen nach unten, um die letzten entsprechenden Werte wie folgt zu erhalten:
=XLOOKUP(E2, $A$2:$A$12, $C$2:$C$12, , , -1)
- „E2“: Der Suchwert;
- „A2:A12“: Das Sucharray, d.h., der Bereich, in dem die Funktion nach dem Suchwert sucht;
- „C2:C12“: Das Rückgabearray, d.h., der Bereich, aus dem der entsprechende Wert zurückgegeben wird;
- ,,: Diese beiden Kommas stehen für optionale Argumente für if_not_found und match_mode. In diesem Fall lassen wir sie leer.
- „-1“: Gibt den Suchmodus an, um die Suche vom unteren Ende des Bereichs zu starten.
Das Zurückgeben des letzten übereinstimmenden Werts in Excel kann mit einer Vielzahl von Methoden erreicht werden, je nach Ihren Anforderungen und der verwendeten Excel-Version. Jede Methode hat ihre eigenen Stärken, und Ihre Wahl sollte von der Excel-Version abhängen, die Sie haben. Indem Sie eine oder mehrere dieser Techniken beherrschen, verbessern Sie Ihre Datenverwaltungsfähigkeiten erheblich und optimieren Ihren Arbeitsablauf in Excel. Wenn Sie daran interessiert sind, weitere Excel-Tipps und -Tricks zu erkunden, bietet unsere Website Tausende von Tutorials.
Weitere relevante Artikel:
- SVERWEIS-Werte über mehrere Arbeitsblätter hinweg
- In Excel können wir leicht die SVERWEIS-Funktion anwenden, um die übereinstimmenden Werte in einer einzigen Tabelle eines Arbeitsblatts zurückzugeben. Aber haben Sie sich jemals überlegt, wie Sie einen Wert über mehrere Arbeitsblätter hinweg mit SVERWEIS suchen können? Angenommen, ich habe die folgenden drei Arbeitsblätter mit einem Datenbereich, und jetzt möchte ich basierend auf den Kriterien einen Teil der entsprechenden Werte aus diesen drei Arbeitsblättern erhalten.
- Verwenden Sie SVERWEIS mit exakter und näherungsweiser Übereinstimmung in Excel
- In Excel ist SVERWEIS eine der wichtigsten Funktionen für uns, um einen Wert in der linken Spalte einer Tabelle zu suchen und den Wert in derselben Zeile des Bereichs zurückzugeben. Aber haben Sie die SVERWEIS-Funktion erfolgreich in Excel angewendet? In diesem Artikel werde ich darüber sprechen, wie man die SVERWEIS-Funktion in Excel verwendet.
- SVERWEIS, um Leerzeichen oder spezifische Werte statt 0 oder #NV zurückzugeben
- Normalerweise, wenn Sie die SVERWEIS-Funktion verwenden, um den entsprechenden Wert zurückzugeben, wird bei einer passenden leeren Zelle eine 0 zurückgegeben, und wenn Ihr passender Wert nicht gefunden wird, erhalten Sie einen Fehlerwert #NV, wie im folgenden Screenshot gezeigt. Anstatt die 0 oder den #NV-Wert anzuzeigen, wie können Sie es so einstellen, dass eine leere Zelle oder ein anderer spezifischer Textwert angezeigt wird?
- SVERWEIS und Rückgabe der gesamten Zeile eines übereinstimmenden Werts in Excel
- Normalerweise können Sie mit SVERWEIS einen übereinstimmenden Wert aus einem Datenbereich zurückgeben. Haben Sie aber schon einmal versucht, die gesamte Zeile der Daten basierend auf bestimmten Kriterien zu finden und zurückzugeben, wie im folgenden Screenshot gezeigt?
- SVERWEIS und Verketten mehrerer entsprechender Werte in Excel
- Wie wir alle wissen, kann die SVERWEIS-Funktion in Excel helfen, einen Wert zu suchen und den entsprechenden Wert in einer anderen Spalte zurückzugeben. Normalerweise kann sie jedoch nur den ersten relativen Wert abrufen, wenn es mehrere übereinstimmende Daten gibt. In diesem Artikel werde ich darüber sprechen, wie man mit SVERWEIS mehrere entsprechende Werte in einer einzigen Zelle oder einer vertikalen Liste verkettet.
Beste Büroproduktivitätswerkzeuge
Verbessern Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor. Kutools für Excel bietet über300 erweiterte Funktionen zur Steigerung der Produktivität und Zeitersparnis. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt eine Registerkartenoberfläche zu Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie die Bearbeitung und das Lesen mit Registerkarten in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters, anstatt in neuen Fenstern.
- Steigert Ihre Produktivität um50 % und reduziert täglich hunderte von Mausklicks für Sie!