Wenn Sie Informationen aus einer Tabelle abrufen möchten, ist die Excel-Funktion SVERWEIS eine großartige Lösung. Die Möglichkeit, dynamisch Informationen aus einer Tabelle zu suchen und abzurufen, ist für viele Benutzer ein Wendepunkt, und Sie werden SVERWEIS überall finden.
Obwohl SVERWEIS relativ einfach zu bedienen ist, kann viel schief gehen. Ein Grund dafür ist, dass SVERWEIS einen großen Designfehler hat – standardmäßig geht es davon aus, dass Sie mit einer ungefähren Übereinstimmung einverstanden sind. Was Sie wahrscheinlich nicht sind.
Dies kann zu Ergebnissen führen, die sehen ganz normal aus , obwohl sie es sind völlig falsch . Vertrauen Sie mir, dies ist NICHT etwas, das Sie Ihrem Chef erklären möchten, nachdem er Ihre Tabelle bereits an das Management gesendet hat :)
Lesen Sie unten, wie Sie diese Herausforderung meistern können, und entdecken Sie weitere Tipps zum Meistern der Excel SVERWEIS-Funktion .
1. So funktioniert SVERWEIS
SVERWEIS ist eine Funktion zum Nachschlagen und Abrufen von Daten in einer Tabelle. Das 'V' in SVERWEIS steht für vertikal, was bedeutet, dass die Daten in der Tabelle vertikal angeordnet sein müssen, mit Daten in Zeilen. (Für horizontal strukturierte Daten siehe HVERWEIS ).
Wenn Sie eine gut strukturierte Tabelle mit vertikal angeordneten Informationen und einer Spalte auf der linken Seite haben, die Sie verwenden können, um eine Zeile zuzuordnen, können Sie wahrscheinlich SVERWEIS verwenden.
SVERWEIS erfordert, dass die Tabelle so strukturiert ist, dass Nachschlagewerte in der Spalte ganz links angezeigt werden. Die abzurufenden Daten (Ergebniswerte) können in einer beliebigen Spalte rechts angezeigt werden. Stellen Sie sich bei der Verwendung von SVERWEIS vor, dass jede Spalte in der Tabelle von links beginnend nummeriert ist. Um einen Wert aus einer bestimmten Spalte zu erhalten, geben Sie einfach die entsprechende Zahl als 'Spaltenindex' an. Im folgenden Beispiel möchten wir die E-Mail-Adresse nachschlagen, daher verwenden wir die Zahl 4 für den Spaltenindex:
In der obigen Tabelle befinden sich die Mitarbeiter-IDs in Spalte 1 links und die E-Mail-Adressen in Spalte 4 rechts.
Um SVERWEIS zu verwenden, geben Sie 4 Informationen oder 'Argumente' an:
- Der gesuchte Wert ( Lookup-Wert )
- Der Zellbereich, aus dem die Tabelle besteht ( table_array )
- Die Nummer der Spalte, aus der ein Ergebnis abgerufen werden soll ( Spaltenindex )
- Der Match-Modus ( range_lookup , TRUE = ungefähr, FALSE = genau)
Video: So verwenden Sie SVERWEIS (3 Minuten)
Wenn Sie immer noch nicht die Grundidee von SVERWEIS verstehen, hat Jon Acampora auf dem Excel Campus eine tolle erklärung basierend auf der Starbucks-Kaffeekarte.
2. SVERWEIS sieht nur richtig aus
Die vielleicht größte Einschränkung von SVERWEIS besteht darin, dass es nur nach rechts suchen kann, um Daten abzurufen.
Dies bedeutet, dass SVERWEIS nur Daten aus Spalten rechts von der ersten Spalte in der Tabelle abrufen kann. Wenn Nachschlagewerte in der ersten Spalte (ganz links) erscheinen, bedeutet diese Einschränkung nicht viel, da alle anderen Spalten bereits rechts liegen. Wenn die Nachschlagespalte jedoch irgendwo in der Tabelle angezeigt wird, können Sie nur Werte aus den Spalten rechts von dieser Spalte nachschlagen. Außerdem müssen Sie SVERWEIS eine kleinere Tabelle bereitstellen, die mit der Nachschlagespalte beginnt.
Sie können diese Einschränkung umgehen, indem Sie INDEX und MATCH anstelle von SVERWEIS verwenden.
3. SVERWEIS findet die erste Übereinstimmung
Wenn eine Nachschlagespalte im exakten Übereinstimmungsmodus doppelte Werte enthält, stimmt SVERWEIS nur mit dem ersten Wert überein. Im folgenden Beispiel verwenden wir SVERWEIS, um einen Vornamen zu finden, und SVERWEIS ist auf eine genaue Übereinstimmung eingestellt. Obwohl es zwei 'Janet's in der Liste gibt, stimmt SVERWEIS nur mit dem ersten überein:
Hinweis: Das Verhalten kann sich ändern, wenn SVERWEIS im ungefähren Übereinstimmungsmodus verwendet wird. Dieser Artikel erklärt das Thema im Detail.
4. Bei SVERWEIS wird die Groß-/Kleinschreibung nicht beachtet
Beim Nachschlagen eines Werts verarbeitet SVERWEIS Groß- und Kleinschreibung nicht unterschiedlich. Für SVERWEIS ist ein Produktcode wie 'PQRF' identisch mit 'pqrf'. Im folgenden Beispiel suchen wir nach Großbuchstaben 'JANET', aber SVERWEIS unterscheidet nicht zwischen Groß- und Kleinschreibung, so dass es einfach mit 'Janet' übereinstimmt, da dies die erste Übereinstimmung ist, die es findet:
5. SVERWEIS hat zwei passende Modi
SVERWEIS hat zwei Betriebsmodi: genaue Übereinstimmung und ungefähre Übereinstimmung. In den meisten Fällen möchten Sie SVERWEIS wahrscheinlich im genauen Übereinstimmungsmodus verwenden. Dies ist sinnvoll, wenn Sie Informationen basierend auf einem eindeutigen Schlüssel suchen möchten, beispielsweise Produktinformationen basierend auf einem Produktcode oder Filmdaten basierend auf einem Filmtitel:
Die Formel in H6 zum Nachschlagen des Jahres basierend auf einer genauen Übereinstimmung des Filmtitels lautet:
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match
Sie sollten jedoch den Näherungsmodus in Fällen verwenden, in denen Sie nicht nach einer eindeutigen ID übereinstimmen, sondern nach der 'besten Übereinstimmung' oder der 'besten Kategorie' suchen. Vielleicht suchen Sie beispielsweise nach dem Porto nach Gewicht, nach dem Steuersatz nach dem Einkommen oder nach einem Provisionssatz basierend auf einer monatlichen Verkaufszahl. In diesen Fällen finden Sie wahrscheinlich nicht den genauen Nachschlagewert in der Tabelle. Stattdessen möchten Sie, dass SVERWEIS Ihnen die beste Übereinstimmung für einen bestimmten Nachschlagewert liefert.
Die Formel in D5 führt eine ungefähre Übereinstimmung durch, um die richtige Provision abzurufen:
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match
6. Achtung: SVERWEIS verwendet standardmäßig ungefähre Übereinstimmungen
Die genaue und ungefähre Übereinstimmung in SVERWEIS wird durch das vierte Argument gesteuert, das als 'Bereichssuche' bezeichnet wird. Dieser Name ist nicht intuitiv, Sie müssen sich also nur merken, wie er funktioniert.
Verwenden Sie für eine genaue Übereinstimmung FALSE oder 0. Für eine ungefähre Übereinstimmung setzen Sie range_lookup auf TRUE oder 1:
= VLOOKUP (value,table,column,TRUE) // approximate match = VLOOKUP (value,table,column,FALSE) // exact match
Leider ist das vierte Argument, range_lookup, optional und standardmäßig auf TRUE eingestellt, was bedeutet, dass SVERWEIS standardmäßig eine ungefähre Übereinstimmung vornimmt. Bei einer ungefähren Übereinstimmung geht SVERWEIS davon aus, dass die Tabelle sortiert ist und führt eine binäre Suche durch. Wenn SVERWEIS während einer binären Suche einen genauen Übereinstimmungswert findet, gibt es einen Wert aus dieser Zeile zurück. Wenn SVERWEIS jedoch einen Wert findet, der größer als der Nachschlagewert ist, gibt es einen Wert aus der vorherigen Zeile zurück.
Dies ist eine gefährliche Standardeinstellung, da viele Leute SVERWEIS unwissentlich im Standardmodus belassen, was zu einem falsches Ergebnis wenn die Tabelle nicht sortiert ist.
Um dieses Problem zu vermeiden, stellen Sie sicher, dass Sie FALSE oder null als viertes Argument verwenden, wenn Sie eine genaue Übereinstimmung wünschen.
7. Sie können SVERWEIS zwingen, eine genaue Übereinstimmung durchzuführen
Um SVERWEIS zu zwingen, eine genaue Übereinstimmung zu finden, stellen Sie sicher, dass das Argument 4 (range_lookup) auf FALSE oder Null gesetzt ist. Diese beiden Formeln sind äquivalent:
= VLOOKUP (value, data, column, FALSE) = VLOOKUP (value, data, column, 0)
Wenn SVERWEIS im genauen Übereinstimmungsmodus keinen Wert finden kann, gibt es #N/A zurück. Dies ist ein deutlicher Hinweis darauf, dass der Wert nicht in der Tabelle enthalten ist.
8. Sie können SVERWEIS anweisen, eine ungefähre Übereinstimmung durchzuführen
Um SVERWEIS im ungefähren Übereinstimmungsmodus zu verwenden, lassen Sie entweder das vierte Argument (range_lookup) weg oder geben Sie es als TRUE oder 1 an. Diese 3 Formeln sind äquivalent:
= VLOOKUP (value, data, column) = VLOOKUP (value, data, column, 1) = VLOOKUP (value, data, column, TRUE)
Wir empfehlen, dass Sie das Argument range_lookup immer explizit festlegen, auch wenn SVERWEIS dies nicht erfordert. Auf diese Weise haben Sie immer eine visuelle Erinnerung an den erwarteten Match-Modus.
Video: So verwenden Sie SVERWEIS für ungefähre Übereinstimmungen
9. Für ungefähre Übereinstimmungen müssen die Daten sortiert werden
Wenn Sie den Näherungsmodus-Abgleich verwenden, werden Ihre Daten muss sortiert werden in aufsteigender Reihenfolge nach Lookup-Wert. Andernfalls erhalten Sie möglicherweise eine falsche Ergebnisse . Beachten Sie auch, dass manchmal Textdaten aussehen sortiert, auch wenn es nicht so ist.
Felienne Hermans hat ein tolles Beispiel für dieses Problem hier , aus einer coolen Analyse, die sie tatsächlich gemacht hat Enron Tabellenkalkulationen!
10. SVERWEIS kann Daten in verschiedenen Tabellen zusammenführen
Ein häufiger Anwendungsfall für SVERWEIS besteht darin, Daten aus zwei oder mehr Tabellen zu verknüpfen. Vielleicht haben Sie beispielsweise Bestelldaten in einer Tabelle und Kundendaten in einer anderen und möchten einige Kundendaten zur Analyse in die Bestelltabelle übernehmen:
Da die Kunden-ID in beiden Tabellen vorhanden ist, können Sie diesen Wert verwenden, um die gewünschten Daten mit SVERWEIS abzurufen. Konfigurieren Sie SVERWEIS einfach so, dass der ID-Wert in Tabelle 1 und die Daten in Tabelle 2 mit dem erforderlichen Spaltenindex verwendet werden. Im folgenden Beispiel verwenden wir zwei SVERWEIS-Formeln. Eine zum Abrufen des Kundennamens und die andere zum Abrufen des Kundenstatus.
Verknüpfung: Beispiel für die Zusammenführung mit SVERWEIS .
Video: So verwenden Sie SVERWEIS zum Zusammenführen von Tabellen .
11. SVERWEIS kann Daten klassifizieren oder kategorisieren
Wenn Sie Datensätze mit beliebigen Kategorien versehen müssen, können Sie dies mit SVERWEIS ganz einfach tun, indem Sie eine Tabelle verwenden, die als 'Schlüssel' für die Zuordnung von Kategorien dient.
Ein klassisches Beispiel sind Noten, bei denen Sie eine Note basierend auf einer Punktzahl vergeben müssen:
In diesem Fall ist SVERWEIS für eine ungefähre Übereinstimmung konfiguriert, daher ist es wichtig, dass die Tabelle in aufsteigender Reihenfolge sortiert wird.
Sie können aber auch SVERWEIS verwenden, um beliebige Kategorien zuzuweisen. Im folgenden Beispiel verwenden wir SVERWEIS, um eine Gruppe für jede Abteilung mithilfe einer kleinen Tabelle (mit dem Namen 'Schlüssel') zu berechnen, die die Gruppierung definiert.
12. Absolute Referenzen machen SVERWEIS tragbarer
In Situationen, in denen Sie Informationen aus mehr als einer Spalte in einer Tabelle abrufen möchten oder SVERWEIS kopieren und einfügen müssen, können Sie Zeit und Ärger sparen, indem Sie absolute Referenzen für den Nachschlagewert und das Tabellenarray verwenden. Auf diese Weise können Sie die Formel kopieren und dann nur die Spaltenindexnummer ändern, um dieselbe Suche zu verwenden, um einen Wert aus einer anderen Spalte abzurufen.
Da beispielsweise der Nachschlagewert und das Tabellenarray absolut sind, können wir die Formel über die Spalten kopieren, dann zurückkehren und den Spaltenindex nach Bedarf ändern.
13. Benannte Bereiche machen SVERWEIS leichter lesbar (und tragbarer)
Absolute Bereiche sehen ziemlich hässlich aus. Sie können Ihre SVERWEIS-Formeln also viel sauberer und leichter lesbar machen, indem Sie absolute Referenzen durch benannte Bereiche ersetzen, die automatisch absolut sind.
Im obigen Beispiel für Mitarbeiterdaten können Sie beispielsweise die Eingabezelle 'id' benennen und dann die Daten in der Tabelle 'data' benennen. Sie können Ihre Formel wie folgt schreiben:
Diese Formel ist nicht nur einfacher zu lesen, sondern auch portierbarer, da benannte Bereiche automatisch absolut sind.
14. Das Einfügen einer Spalte kann bestehende SVERWEIS-Formeln zerstören
Wenn Sie in einem Arbeitsblatt über vorhandene SVERWEIS-Formeln verfügen, können Formeln beschädigt werden, wenn Sie eine Spalte in die Tabelle einfügen. Dies liegt daran, dass sich hartcodierte Spaltenindexwerte nicht automatisch ändern, wenn Spalten eingefügt oder gelöscht werden.
In diesem Beispiel wurden die Lookups für Rang und Verkäufe unterbrochen, als eine neue Spalte zwischen Jahr und Rang eingefügt wurde. Jahr funktioniert weiterhin, da es links von der eingefügten Spalte steht:
Um dieses Problem zu vermeiden, können Sie einen Spaltenindex berechnen, wie in den nächsten beiden Tipps beschrieben.
15. Sie können ROW oder COLUMN verwenden, um einen Spaltenindex zu berechnen
Wenn Sie der Typ sind, der sich nach dem Kopieren einer Formel durch jede Menge Bearbeitungen stört, können Sie entweder ROW oder COLUMN verwenden, um dynamische Spaltenindizes zu generieren. Wenn Sie Daten aus aufeinanderfolgenden Spalten abrufen, können Sie mit diesem Trick eine SVERWEIS-Formel einrichten und sie dann ohne Änderungen kopieren.
Mit den folgenden Mitarbeiterdaten können wir beispielsweise die COLUMN-Funktion verwenden, um einen dynamischen Spaltenindex zu generieren. Für die erste Formel in Zelle C3 gibt COLUMN selbst 3 zurück (da Spalte C die dritte im Arbeitsblatt ist), also müssen wir nur eine subtrahieren und die Formel kopieren:
Alle Formeln sind identisch, ohne dass eine Nachbearbeitung erforderlich ist.
Die Formel, die wir verwenden, ist diese:
= VLOOKUP (id,data, COLUMN ()-1,0)
16. Verwenden Sie SVERWEIS + VERGLEICH für einen vollständig dynamischen Spaltenindex
Wenn Sie den obigen Tipp noch einen Schritt weiterführen, können Sie mit MATCH die Position einer Spalte in einer Tabelle nachschlagen und einen vollständig dynamischen Spaltenindex zurückgeben.
Dies wird manchmal als bidirektionale Suche bezeichnet, da Sie sowohl die Zeile als auch die Spalte nachschlagen.
Ein Beispiel wäre das Nachschlagen der Verkäufe eines Verkäufers in einem bestimmten Monat oder das Nachschlagen des Preises für ein bestimmtes Produkt eines bestimmten Lieferanten.
Angenommen, Sie haben Verkäufe pro Monat, aufgeschlüsselt nach Verkäufer:
SVERWEIS kann den Verkäufer leicht finden, hat jedoch keine Möglichkeit, den Monatsnamen automatisch zu verarbeiten. Der Trick besteht darin, die MATCH-Funktion anstelle eines statischen Spaltenindex zu verwenden.
Beachten Sie, dass wir match einen Bereich angeben, der alle Spalten in der Tabelle umfasst, um die von SVERWEIS verwendeten Spaltennummern zu 'synchronisieren'.
= VLOOKUP (H2,data, MATCH (H3,months,0),0)
Hinweis: Sie werden häufig Zwei-Wege-Suchen mit INDEX und MATCH sehen, einem Ansatz, der mehr Flexibilität und bessere Leistung bei großen Datensätzen bietet. Sehen Sie in diesem kurzen Video, wie: So führen Sie eine bidirektionale Suche mit INDEX und MATCH durch .
17. SVERWEIS erlaubt Platzhalter für teilweise Übereinstimmungen
Jedes Mal, wenn Sie SVERWEIS im exakten Übereinstimmungsmodus verwenden, haben Sie die Möglichkeit, Platzhalter im Nachschlagewert zu verwenden. Es mag kontraintuitiv erscheinen, aber mit Platzhaltern können Sie eine genaue Übereinstimmung basierend auf einer teilweisen Übereinstimmung durchführen :)
Excel bietet zwei Platzhalterzeichen: Ein Sternchen (*) entspricht einem oder mehreren Zeichen und ein Fragezeichen (?) entspricht einem Zeichen.
Sie können beispielsweise ein Sternchen direkt in eine Zelle eingeben und mit SVERWEIS als Nachschlagewert darauf verweisen. Im folgenden Bildschirm haben wir 'Mon*' in H3 eingegeben, das ist ein benannter Bereich namens 'val'. Dies führt dazu, dass SVERWEIS dem Namen 'Monet' entspricht.
Die Formel in diesem Fall ist einfach:
= VLOOKUP (val,data,1,0)
Wenn Sie möchten, können Sie die SVERWEIS-Formel so anpassen, dass ein integrierter Platzhalter verwendet wird, wie im folgenden Beispiel, in dem wir den Wert in H3 einfach mit einem Sternchen verketten.
In diesem Fall verketten wir das Sternchen mit dem Nachschlagewert in der SVERWEIS-Funktion:
= VLOOKUP (val&'*',data,1,0)
Hinweis: Seien Sie vorsichtig mit Platzhaltern und SVERWEIS. Sie bieten Ihnen eine einfache Möglichkeit, eine 'faule Übereinstimmung' zu erstellen, aber sie machen es auch leicht, die falsche Übereinstimmung zu finden.
18. Sie können #N/A-Fehler abfangen und eine freundliche Nachricht anzeigen
Im exakten Übereinstimmungsmodus zeigt SVERWEIS den Fehler #N/A an, wenn keine Übereinstimmung gefunden wird. Dies ist einerseits nützlich, da es Ihnen definitiv sagt, dass es keine Übereinstimmung in der Nachschlagetabelle gibt. Es macht jedoch keinen großen Spaß, sich #N/A-Fehler anzusehen, daher gibt es mehrere Möglichkeiten, diesen Fehler abzufangen und stattdessen etwas anderes anzuzeigen.
Sobald Sie mit der Verwendung von SVERWEIS beginnen, werden Sie zwangsläufig auf den Fehler #N/A stoßen, der auftritt, wenn SVERWEIS keine Übereinstimmung finden kann.
wie man eine zweite Zeile in Excel hinzufügt
Dies ist ein nützlicher Fehler, da SVERWEIS Ihnen deutlich mitteilt, dass der Nachschlagewert nicht gefunden werden kann. In diesem Beispiel existiert 'Latte' nicht als Getränk in der Tabelle, daher gibt SVERWEIS einen #N/A-Fehler aus
Die Formel in diesem Fall ist eine völlig standardmäßige exakte Übereinstimmung:
= VLOOKUP (E6,data,2,0)
Es macht jedoch keinen großen Spaß, sich #N/A-Fehler anzusehen, daher sollten Sie diesen Fehler möglicherweise abfangen und eine freundlichere Nachricht anzeigen.
Der einfachste Weg, Fehler mit SVERWEIS abzufangen, besteht darin, SVERWEIS in die Funktion IFERROR einzuschließen. IFERROR ermöglicht es Ihnen, jeden Fehler zu 'fangen' und ein Ergebnis Ihrer Wahl zurückzugeben.
Um diesen Fehler abzufangen und anstelle des Fehlers eine 'nicht gefunden'-Meldung anzuzeigen, können Sie einfach die ursprüngliche Formel in IFERROR einschließen und das gewünschte Ergebnis festlegen:
Wenn der Nachschlagewert gefunden wird, tritt kein Fehler auf und die SVERWEIS-Funktion gibt ein normales Ergebnis zurück. Hier ist die Formel:
= IFERROR ( VLOOKUP (E6,data,2,0),'Not found')
19. Zahlen als Text können einen Übereinstimmungsfehler verursachen
Manchmal kann die Tabelle, mit der Sie in SVERWEIS arbeiten, Zahlen enthalten, die als Text eingegeben wurden. Wenn Sie Zahlen einfach als Text aus einer Spalte in einer Tabelle abrufen, spielt dies keine Rolle. Wenn die erste Spalte der Tabelle jedoch Zahlen enthält, die als Text eingegeben wurden, erhalten Sie einen #N/A-Fehler, wenn der Nachschlagewert nicht auch Text ist.
Im folgenden Beispiel sind die IDs für die Planetentabelle Zahlen als Text eingegeben , was dazu führt, dass SVERWEIS einen Fehler zurückgibt, da der Nachschlagewert der . ist Nummer 3:
Um dieses Problem zu lösen, müssen Sie sicherstellen, dass sowohl der Nachschlagewert als auch die erste Spalte der Tabelle denselben Datentyp aufweisen (entweder beide Zahlen oder beide Texte).
Eine Möglichkeit besteht darin, die Werte in der Nachschlagespalte in Zahlen umzuwandeln. Eine einfache Möglichkeit, dies zu tun, besteht darin, Null mit Paste Special hinzuzufügen.
Wenn Sie keine einfache Kontrolle über die Quelltabelle haben, können Sie auch die SVERWEIS-Formel anpassen, um den Nachschlagewert in Text umzuwandeln, indem Sie '' wie folgt mit dem Wert verketten:
= VLOOKUP (id&'',planets,2,0)
Wenn Sie nicht sicher sind, wann Sie Zahlen und wann Text haben, können Sie beide Optionen berücksichtigen, indem Sie SVERWEIS in IFERROR einschließen und eine Formel schreiben, die beide Fälle behandelt:
= IFERROR ( VLOOKUP (id,planets,3,0), VLOOKUP (id&'',planets,3,0))
20. Sie können SVERWEIS verwenden, um verschachtelte IF-Anweisungen zu ersetzen
Eine der interessanteren Verwendungen von SVERWEIS besteht darin, verschachtelte IF-Anweisungen zu ersetzen. Wenn Sie jemals eine Reihe von verschachtelten IFs erstellt haben, wissen Sie, dass sie gut funktionieren, aber sie erfordern ein wenig Klammern. Sie müssen auch auf die Reihenfolge achten, in der Sie arbeiten, um keinen logischen Fehler einzuführen.
Verschachtelte IFs werden beispielsweise häufig verwendet, um Noten basierend auf einer bestimmten Punktzahl zuzuweisen. Im folgenden Beispiel sehen Sie, dass eine Formel mit verschachtelten IFs erstellt wurde, um genau dies zu tun, wobei der Notenschlüssel rechts als Leitfaden verwendet wird.
Die vollständig verschachtelte IF-Formel sieht wie folgt aus:
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))
Dies funktioniert gut, aber beachten Sie, dass sowohl die Logik als auch die tatsächlichen Ergebnisse direkt in die Formel integriert sind. Wenn sich die Bewertung aus irgendeinem Grund ändert, müssen Sie eine Formel sorgfältig aktualisieren und dann in die gesamte Tabelle kopieren.
Im Gegensatz dazu kann SVERWEIS die gleichen Noten mit einer einfachen Formel vergeben. Sie müssen lediglich sicherstellen, dass die Notenschlüsseltabelle für SVERWEIS eingerichtet ist (d. h. sie ist meistens nach Punktzahl sortiert und enthält Klammern, um alle Punktzahlen zu verarbeiten).
Nachdem Sie einen benannten Bereichsschlüssel für die Notenschlüsseltabelle definiert haben, ist die SVERWEIS-Formel sehr einfach und generiert dieselben Noten wie die ursprüngliche Formel für verschachtelte IFs:
Mit der Notenschlüsseltabelle namens 'key' haben wir eine sehr einfache SVERWEIS-Formel:
= VLOOKUP (C5,key,2,TRUE)
Ein schöner Bonus dieses Ansatzes ist, dass sowohl die Logik als auch die Punktzahlen direkt in die Notenschlüsseltabelle integriert sind. Wenn sich etwas ändert, können Sie die Tabelle einfach direkt aktualisieren und die SVERWEIS-Formeln werden automatisch aktualisiert - keine Bearbeitung erforderlich.
Video: So ersetzen Sie verschachtelte IFs durch SVERWEIS
21. SVERWEIS kann nur ein einziges Kriterium verarbeiten
SVERWEIS kann standardmäßig nur Werte basierend auf einem einzigen Kriterium finden, das als Nachschlagewert bereitgestellt wird, um in der ersten Spalte der Tabelle (der Nachschlagespalte) zu suchen.
Dies bedeutet, dass Sie nicht einfach einen Mitarbeiter mit dem Nachnamen 'Schmied' in 'Buchhaltung' oder einen Mitarbeiter anhand von Vor- und Nachnamen in separaten Spalten suchen können.
Es gibt jedoch Möglichkeiten, diese Einschränkung zu überwinden. Eine Problemumgehung besteht darin, eine Hilfsspalte zu erstellen, die Werte aus verschiedenen Spalten verkettet, um Nachschlagewerte zu erstellen, die sich wie mehrere Bedingungen verhalten. Hier möchten wir beispielsweise die Abteilung und Gruppe für einen Mitarbeiter suchen, aber Vor- und Nachname erscheinen in separaten Spalten. Wie können wir beides gleichzeitig nachschlagen?
Fügen Sie zunächst eine Hilfsspalte hinzu, die Vor- und Nachnamen einfach miteinander verkettet:
Konfigurieren Sie dann SVERWEIS so, dass eine Tabelle verwendet wird, die diese neue Spalte enthält, und verbinden Sie Vor- und Nachnamen für den Nachschlagewert:
Die endgültige SVERWEIS-Formel sucht gemeinsam nach Vor- und Nachnamen, wobei die Hilfsspalte als Schlüssel verwendet wird:
= VLOOKUP (C3&D3,data,4,0)
22. Zwei SVERWEIS sind schneller als ein SVERWEIS
Es mag völlig verrückt erscheinen, aber wenn Sie eine große Menge an Daten haben und eine genaue Übereinstimmung durchführen müssen, können Sie SVERWEIS erheblich beschleunigen, indem Sie der Formel einen weiteren SVERWEIS hinzufügen!
Der Hintergrund: Stellen Sie sich vor, Sie haben viele Auftragsdaten, beispielsweise mehr als 10.000 Datensätze, und verwenden SVERWEIS, um die Auftragssumme anhand der Auftrags-ID abzurufen. Sie verwenden also so etwas:
= VLOOKUP (order_id,order_data, 5, FALSE)
Das FALSE am Ende zwingt SVERWEIS zu einer exakten Übereinstimmung. Sie möchten eine genaue Übereinstimmung, da die Möglichkeit besteht, dass eine Bestellnummer nicht gefunden wird. In diesem Fall führt die Einstellung für genaue Übereinstimmung dazu, dass SVERWEIS den Fehler #N/A zurückgibt.
Das Problem ist, dass genaue Übereinstimmungen sehr langsam sind, da Excel linear durch alle Werte gehen muss, bis es eine Übereinstimmung findet oder nicht.
Umgekehrt sind ungefähre Übereinstimmungen blitzschnell, da Excel in der Lage ist, das zu tun, was als a . bezeichnet wird binäre Suche .
Das Problem bei binären Suchen (d. h. SVERWEIS im ungefähren Übereinstimmungsmodus) besteht jedoch darin, dass SVERWEIS das falsche Ergebnis zurückgeben kann, wenn ein Wert nicht gefunden wird. Schlimmer noch, das Ergebnis sieht möglicherweise völlig normal aus und kann daher sehr schwer zu erkennen sein.
Die Lösung besteht darin, SVERWEIS zweimal zu verwenden, beide Male im ungefähren Übereinstimmungsmodus. Die erste Instanz prüft einfach, ob der Wert wirklich existiert. Wenn dies der Fall ist, wird ein weiterer SVERWEIS (wieder im ungefähren Übereinstimmungsmodus) ausgeführt, um die gewünschten Daten abzurufen. Wenn nicht, können Sie einen beliebigen Wert zurückgeben, um anzuzeigen, dass kein Ergebnis gefunden wurde.
Die endgültige Formel sieht so aus:
= IF ( VLOOKUP (order_id,order_data,1,TRUE)=order_id, VLOOKUP (order_id,order_data,5,TRUE), 'Missing')
Ich habe diesen Ansatz von Charles Williams von FastExcel gelernt, der hier einen fantastischen, detaillierten Artikel veröffentlicht: Warum 2 SVERWEIS besser sind als 1 SVERWEIS .
Hinweis: Ihre Daten müssen sortiert werden, um diesen Trick zu verwenden. Es ist einfach eine Möglichkeit, sich vor einem fehlenden Lookup-Wert zu schützen und gleichzeitig eine schnelle Suche aufrechtzuerhalten.
23. INDEX und MATCH zusammen können alles, was SVERWEIS kann und noch mehr
Wenn Sie Excel online folgen, werden Sie wahrscheinlich auf die Debatte VLOOKUP vs. INDEX/MATCH. Der Streit kann überraschend hitzig werden :)
Das Wesentliche ist: INDEX + MATCH kann alles tun, was SVERWEIS (und HVERWEIS) kann, mit viel mehr Flexibilität, auf Kosten einer etwas höheren Komplexität. Die Befürworter von INDEX + MATCH werden also (sehr vernünftig) argumentieren, dass Sie genauso gut mit dem Erlernen von INDEX und MATCH beginnen können, da Sie dadurch am Ende ein besseres Toolset erhalten.
Das Argument gegen INDEX + MATCH ist, dass es zwei Funktionen statt einer erfordert, sodass es für Benutzer (insbesondere neue Benutzer) von Natur aus komplexer zu erlernen und zu beherrschen ist.
Meine zwei Cent ist, dass Sie, wenn Sie Excel häufig verwenden, lernen möchten, wie man INDEX und MATCH verwendet. Es ist eine sehr starke Kombination.
Aber ich denke auch, dass Sie SVERWEIS lernen sollten, auf das Sie überall stoßen werden, oft in Arbeitsblättern, die Sie von anderen erben. In einfachen Situationen erledigt SVERWEIS die Arbeit problemlos.
Um mehr über INDEX und MATCH zu erfahren, siehe diesen Artikel .
Autor Dave Bruns
