Genau oder ungenau?
Zunächst sollten wir uns über den Unterschied zwischen genauen und ungenauen Übereinstimmungen im Klaren sein.
Genaue Übereinstimmungen
Wenn Sie eine Suche mit VERGLEICH, XVERWEIS oder SVERWEIS durchführen, möchten Sie normalerweise eine exakte Übereinstimmung in Ihrer Nachschlagetabelle finden. Sie wollen zum Beispiel den Skontoprozentsatz für einen bestimmten Kunden. Wenn Sie die Kontonummer des Kunden in Ihrer Nachschlagetabelle suchen, möchten Sie das Ergebnis für den bestimmten Kunden finden und nicht für einen anderen Kunden, der zufällig eine ähnliche Kontonummer hat.
In solchen Fällen müssen Sie das optionale Argument 0 für VERGLEICH oder XVERWEIS bzw. FALSCH für SVERWEIS verwenden. Die Daten der Lookup-Tabelle können in beliebiger Reihenfolge stehen, Excel sucht so lange, bis es die erste Übereinstimmung findet, oder meldet einen Fehler, wenn es das gesuchte Element überhaupt nicht findet.
Ungenaue Übereinstimmungen
In einigen Fällen kann es jedoch notwendig sein, eine ungenaue Übereinstimmung zu verwenden, wenn das gesuchte Element evtl. nicht genau in der Nachschlagetabelle zu finden ist, z. B. eine Liste von Verkaufsrabatten auf der Grundlage der verkauften Menge (siehe Beispiel 1 unten).
In solchen Fällen benötigen Sie das optionale Argument 1 oder -1 für VERGLEICH und XVERWEIS oder TRUE für SVERWEIS. Die Daten der Nachschlagetabelle müssen dann in aufsteigender oder absteigender Reihenfolge vorliegen, wie in der QuickInfo angegeben (beachten Sie, dass SVERWEIS nur mit Nachschlagedaten in aufsteigender Reihenfolge umgehen kann). Excel sucht dann so lange, bis es entweder eine exakte Übereinstimmung findet oder das Ergebnis zurückgibt, das "nicht zu weit" entfernt ist. Dies lässt sich am besten anhand einiger Beispiele veranschaulichen.
In allen vier Beispielen gibt es eine Eingabezelle (grau hinterlegt) und eine Output-Zelle (grün hinterlegt). Die Output-Zelle verwendet die leistungsstarke Kombination der Funktionen INDEX und VERGLEICH, um das richtige Ergebnis aus der Nachschlagetabelle mit einer ungenauen Übereinstimmung zu erhalten. Sie könnten stattdessen XVERWEIS verwenden (oder vielleicht sogar SVERWEIS, aber ich empfehle diese Funktion nicht, da sie einige Schwächen und Risiken aufweist).
Sie können die Excel-Datei mit allen vier Beispielen hier herunterladen: Ungenaue-Übereinstimmung_Download
- Umsatzrabatt
- Aufgabe: Geben Sie die verkaufte Menge ein, um den Rabattprozentsatz zu erhalten.
- Lösung: Wir müssen die größte "verkaufte Menge" in der Lookup-Tabelle finden, die kleiner oder gleich der Eingabemenge ist, um den entsprechenden Rabattprozentsatz zu erhalten. Wir verwenden daher das Argument der ungenauen Übereinstimmung 1.
- Nachschlagetabelle: Dies erfordert, dass die Nachschlagetabelle in aufsteigender Reihenfolge sortiert ist.
- Prüfungsnote
- Aufgabe: Geben Sie das Prüfungsergebnis in Prozent ein, um die Prüfungsnote zu erhalten.
- Lösung: Ähnlich wie in Fall 1. Das Argument der ungenauen Übereinstimmung ist ebenfalls 1
- Nachschlagetabelle: Per Fall 1, aufsteigend sortiert.
- Zahlungsdatum
- Aufgabe: Geben Sie das Fälligkeitsdatum der Rechnung ein, um das nächste Zahlungsdatum zu erhalten.
Dies kann für Liquiditätsplanungstools sehr nützlich sein: verwenden Sie einen Kreditorenbericht, um Zahlungstermine zu ermitteln.
- Lösung: Wenn wir eine Tabelle in absteigender Reihenfolge nachschlagen müssen, ist die Lösung relativ einfach, aber in der Realität wird eine Liste von Daten wahrscheinlich in aufsteigender Reihenfolge sein. Das macht die Lösung ein wenig komplizierter, aber mit ein wenig Probieren können Sie den richtigen Ansatz finden.
> Suchdatum: Wir müssen 1 vom Eingabedatum abziehen, um das richtige Ergebnis auch zu erhalten, wenn das Eingabedatum genau mit einem Zahlungsdatum übereinstimmt, sonst erhalten wir das folgende Zahlungsdatum.
> VERGLEICH findet das größte (d.h. letzte) Zahlungsdatum, das kleiner oder gleich dem Suchdatum ist, d.h. es liefert uns das vorherige Wir müssen also zum Ergebnis von VERGLEICH eins addieren, um das nächste Zahlungsdatum zu erhalten. - Nachschlagetabelle: Die Nachschlagetabelle ist in aufsteigender Reihenfolge sortiert (weil sie wahrscheinlich so sein wird), hat aber nur eine Dimension. Die Formel sucht nach dem Fälligkeitsdatum (Eingabe) in der Liste der Zahlungstermine und gibt das nächste Zahlungsdatum aus derselben Spalte zurück.
- Obstsortierer
- Aufgabe: Zum Schluss haben wir noch einen Obstsortierer 😊.
Geben Sie die Größe des Obstes in Zentimetern ein, um die kleinste Kiste zu finden, die groß genug ist, um es aufzunehmen.
- Lösung: Da wir nach der kleinsten Kiste suchen, die größer oder gleich der Obstgröße ist, müssen wir das Argument der ungenauen Übereinstimmung -1 verwenden.
- Nachschlagetabelle: In diesem Fall muss die Nachschlagetabelle in absteigender Reihenfolge sortiert sein. Hier gibt es eine zusätzliche Spalte, um deutlich zu machen, in welcher Kiste das Obst landet. Dies wird durch eine einfache WENN-Formel mit einem Obstsymbol bestimmt... dies ist ein Emoji, das Sie durch Drücken der Windows-Taste und . auswählen können. Geben Sie dann das Wort Obst ein, um eine Auswahl von Obstsymbolen zu sehen. Nach meiner Auswahl habe ich die Textfarbe der Spalte "Obst" in ein dunkles Orange geändert.
- Und Aktion! Hier ist die Lösung in Aktion...
Sie wollen mehr Ergebnisse?
Diese Ansätze sind nicht auf eine einzelne Output-Zelle beschränkt, sondern können in einer großen Tabelle mit Berechnungen verwendet werden, bei denen die Ergebnisse für jede Zeile berechnet werden, wie unten für den Obstsortierer mit einem Diagramm der Ergebnisse zur Analyse gezeigt.
Ich hoffe, dieser Blog hilft Ihnen bei Ihrer Arbeit!
Mehr Hilfe
Weitere Erläuterungen zur Verwendung von INDEX und VERGLEICH oder XVERWEIS finden Sie in meinen Videos. Hier erfahren Sie auch, welche Probleme und Risiken mit SVERWEIS verbunden sind.
