Einführung in die SVERWEIS-Funktion
Die SVERWEIS-Funktion in Excel ist ohne Zweifel die am häufigsten verwendete und gesprochene Funktion. In jedem Vorstellungsgespräch, das Excel-Kenntnisse erfordert, ist dies die am häufigsten gestellte Frage, wenn Sie wissen, wie man die SVERWEIS-Funktion verwendet.
Die SVERWEIS-Funktion fällt in die Nachschlagekategorie der Excel-Funktionen. Das V in SVERWEIS steht für vertikal. Die Funktion dient zum Nachschlagen von Daten, die vertikal strukturiert sind. Für die horizontale Suche gibt es eine Funktion namens HLOOKUP.
Die Excel SVERWEIS-Funktion sucht in einer Datentabelle nach der ersten Übereinstimmung eines bestimmten Werts namens Nachschlagewert und gibt den Wert aus dem angegebenen Spaltenindex zurück. Die Übereinstimmung kann ungefähr oder genau sein.
Obwohl SVERWEIS einfach zu verwenden ist, hat es seine eigenen Einschränkungen und Hintergründe. Wir werden alles besprechen, einschließlich der Stärken und Schwächen der Funktion.
Syntax:
=SVERWEIS(lookup_value, table_array, col_index_number, [range_lookup])
Was ist der Lookup-Wert?
Lookup-Wert: Dies ist der Wert, nach dem Sie in einem Tabellenarray suchen möchten.
Wenn Sie beispielsweise die Rolle Nr. 110 in der Tabelle des Schülers, dann ist es 110 ist der Nachschlagewert.
Tabellenarray: Die Tabelle, aus der Sie nach dem Lookup-Wert suchen möchten.
Wenn Sie beispielsweise im Tabellen-Array nach 110 suchen B5:E17. Dann B5:E17 ist Ihr Tabellen-Array.
spaltenindex_nummer: Die Spaltennummer im Tabellen-Array, aus der Sie Ergebnisse abrufen möchten.
Zum Beispiel, wenn im Tabellen-Array B5:E17 Sie möchten den Wert aus der Spalte D erhalten dann ist dein Spaltenindex 3 (Zählung von B bis D (B,C,D)).
[range_lookup]: FALSE, wenn Sie nach einem genauen Wert suchen möchten, TRUE, wenn Sie eine ungefähre Übereinstimmung wünschen.
So verwenden Sie die SVERWEIS-Funktion
Wenn Sie über strukturierte Daten verfügen und in diesen Daten nach Daten suchen möchten, ist die SVERWEIS-Funktion die Lösung.
SVERWEIS-Übereinstimmungsmodi
SVERWEIS hat zwei Übereinstimmungsmodi, ungefähre Übereinstimmung und genaue Übereinstimmung. Standardmäßig führt SVERWEIS eine ungefähre Übereinstimmung durch. Aber wenn Sie die SVERWEIS-Funktion zwingen möchten, eine genaue Übereinstimmung durchzuführen, können Sie dies auch tun. Meistens versuchen ich und ich auch andere stark, mit der SVERWEIS-Funktion eine genaue Übereinstimmung zu erzielen. Ich verstehe nicht, warum Excel-Entwickler denken, dass Benutzer hauptsächlich die ungefähre Übereinstimmung verwenden möchten.
1. SVERWEIS Ungefähre Übereinstimmung
=SVERWEIS(Suchwert, Tabellenarray, Spalte_Indexnummer, 1)
In SVERWEIS ungefähre Übereinstimmung, wenn ein Wert nicht gefunden wird, dann wird die Der letzte Wert, der kleiner als der Nachschlagewert ist, wird abgeglichen und der Wert aus dem angegebenen Spaltenindex wird zurückgegeben.
SVERWEIS stimmt standardmäßig ungefähr überein, wenn wir die Bereichssuchvariable weglassen. Eine ungefähre Übereinstimmung ist nützlich, wenn Sie eine ungefähre Übereinstimmung durchführen möchten und wenn Sie Ihr Tabellenarray in aufsteigender Reihenfolge sortiert haben.
Vlookup sucht nach dem Wert und wenn es den Wert im Tabellenarray nicht findet, dann stimmt es mit dem Wert überein, der kleiner als dieser Wert im Tabellenarray ist. Lassen Sie es uns anhand eines Beispiels verstehen.
Beispiel 1
Wir haben hier eine Schülerliste. Jeder Schüler hat in einem Test einige Punkte erreicht. Jetzt möchte ich die Noten nach ihren Noten vergeben.
Jeder Schüler, der weniger als 40 Punkte erzielt hat, sollte mit F bewertet werden, Schüler, die zwischen 40 und 60 Punkte erzielen, sollten mit C bewertet werden, und so weiter, wie in der folgenden Abbildung gezeigt.
Wir würden diese SVERWEIS-Formel in E2 schreiben und nach unten ziehen.
=SVERWEIS(D2,$H$2:$I$6,2,WAHR)
Wie es funktioniert?
Im obigen Beispiel befinden sich unsere Lookup-Werte in Spalte D, beginnend mit D2. Das Tabellen-Array ist H2:I6 (beachten Sie, dass es in aufsteigender Reihenfolge und absolut sortiert ist). Die Spalte, aus der wir Daten erhalten, ist 2. Und den Übereinstimmungstyp, den wir ungefähr definiert haben, indem wir TRUE übergeben (nichts und 1 bedeutet dasselbe).
Lassen Sie uns also den ersten Nachschlagewert D2 untersuchen, der 40 enthält.
- SVERWEIS sucht nach 40 in der ersten Spalte (H) des Tabellenarrays H2:I6.
- Es findet 40 an zweiter Position in Zelle H3.
- Jetzt geht es in die zweite Spalte von H3 nach I3 und gibt D zurück.
In diesem Fall hat vlookup die genaue Übereinstimmung gefunden. Sehen wir uns den nächsten Fall an.
Die zweiten Nachschlagewerte D3, die 36 enthalten.
- SVERWEIS sucht nach 36 in der ersten Spalte des Tabellenarrays H2:I6.
- SVERWEIS konnte in der ersten Spalte nirgendwo 36 finden.
- Da SVERWEIS 36 nicht finden konnte, es stimmt mit dem zuletzt gefundenen Wert überein, der kleiner als der Nachschlagewert ist.
- Der erste Wert, der kleiner als 36 ist, ist 0, daher wird F zurückgegeben, das sich auf 0 bezieht.
Dies geschieht hier für jeden Fall. Für 92 ist der letzte Wert, der kleiner als 92 ist, 90. Daher wird A für ungefähre Übereinstimmung zurückgegeben.
2. SVERWEIS Genaue Übereinstimmung
Diese am häufigsten verwendete Form von SVERWEIS und möglicherweise auch sehr nützlich. Wenn Sie in der ersten Spalte des Tabellenarrays nach einem genauen Wert suchen möchten, verwenden Sie die genaue Übereinstimmung. Wenn Sie beispielsweise eine ID-Suche durchführen, möchten Sie am liebsten eine genaue Übereinstimmung anstelle eines Werts, der kleiner als dieser Wert ist.
Um SVERWEIS zu einer exakten Übereinstimmung in Excel zu erzwingen, übergeben wir 0 oder FALSE als range_lookup-Parameter.
=SVERWEIS(Suchwert, Tabellenarray, Spalte_Indexnummer, 0)
Wenn der Wert in der ersten Spalte des Tabellenarrays nicht gefunden wird, gibt die Formel den Fehler #N/A zurück.
Sehen wir uns ein Beispiel an.
Beispiel 2
In diesem Beispiel möchte ich nur die Rollennummer des Schülers in A2 schreiben und in B2 möchte ich die holen Name des Schülers und in C2 seine Grad. Einfach. Tabellenarray ist B5: D17. Wieso den?
Schreiben Sie dazu diese beiden Formeln in die Zelle in Zelle B2 bzw. C2.
=SVERWEIS(A2,$B$5:$E$17,2,0)
=SVERWEIS(A2,$B$5:$E$17,3,0)
Unabhängig davon, welche ID Sie in Zelle A2 schreiben, sucht die SVERWEIS-Funktion nach einer genauen Übereinstimmung in der Spalte B und gibt den Wert aus dem angegebenen col_index zurück.
Wie funktioniert Exact Match SVERWEIS?
Das Exact Match SVERWEIS in Excel ist einfach. Es sucht nach dem Lookup-Wert in der ersten Spalte des angegebenen table_array und gibt den Wert aus dem Wert der angegebenen Spaltenindexnummer zurück. Wenn der Wert nicht gefunden wird, gibt SVERWEIS den Fehler #N/A zurück.
Beste Verwendung der SVERWEIS-Funktion
Vlookup hat viele Verwendungsmöglichkeiten. Ich werde einige der nützlichsten Anwendungsfälle diskutieren.
3. Verwenden Sie Excel SVERWEIS für die Benotung anstelle einer komplexen verschachtelten IF-Funktion
Verwenden Sie SVERWEIS ungefähre Übereinstimmung, um die verschachtelte IF-Funktion zu ersetzen. In Beispiel 1 haben wir gesehen, wie wir mit vlookup Schüler nach ihren Noten benoten können.
Die ungefähre Übereinstimmung von SVERWEIS ist schneller als verschachtelt, da sie intern die binäre Suche verwendet.
4. Verwenden Sie SVERWEIS, um Daten abzurufen
Diese häufigste Aufgabe kann einfach mit der SVERWEIS-Funktion in Excel ausgeführt werden. In Beispiel 2 haben wir dies getan, um Schülerdaten mit ihrer Rollennummer abzurufen.
5. Prüfen Sie mit Vlookup, ob sich ein Wert in einem Datensatz befindet.
Der SVERWEIS kann verwendet werden, um zu überprüfen, ob ein Wert in einer Liste enthalten ist oder nicht.
Wenn wir in Beispiel 2 152 schreiben, wird #N/A error zurückgegeben. Wenn SVERWEIS #N/A zurückgibt, bedeutet dies, dass der angegebene Wert nirgendwo in den angegebenen Daten gefunden wurde.
Dazu habe ich einen kurzen Artikel vorbereitet, den Sie hier lesen können.
Verwenden einer SVERWEIS-Formel, um zu überprüfen, ob ein Wert vorhanden ist
Spaltenindex automatisch abrufen
Ich gehöre zu dieser Gruppe von Menschen, die nicht immer wieder dieselbe Aufgabe wiederholen wollen. Daher nervt mich immer wieder das Ändern der col_index-Nummer und ich vermeide es immer. Eigentlich vermeide ich jede Art von Bearbeitung in meinen Formeln, wenn ich sie einmal geschrieben habe, es sei denn, es gibt keine andere Möglichkeit.
Der Spaltenindex kann in Excel automatisch berechnet werden. Es gibt mehrere Möglichkeiten, dies zu tun. Sehen wir uns hier einige von ihnen an.
6. Verwenden Sie die SVERWEIS-Funktion mit der MATCH-Funktion
Wie wir wissen, gibt die MATCH-Funktion den gefundenen Index des übereinstimmenden Werts zurück.
Wenn wir also genau die gleichen Überschriften am Nachschlageort wie die Quelldaten haben, können wir col_index verwenden. Wie? Lass uns sehen…
In die folgenden Daten möchten wir nur die Rollennummer in G2 schreiben und den Schülernamen, die Noten und die Note in H2 abrufen, egal welche Überschrift wir dort schreiben.
Schreiben Sie diese Formel in Zelle H2
=SVERWEIS(G2,B2:E14,VERGLEICH(H1,B1:E1,0),0)
Immer wenn Sie die Überschrift in H1 ändern, wird dieser Wert in H2 aus dieser Spalte angezeigt.
Sie können ein Dropdown-Menü mit Datenvalidierung verwenden, das alle Überschriften aus der Tabelle enthält, um die Benutzerfreundlichkeit zu erhöhen.
Hier macht SVERWEIS genaue Übereinstimmung. Jetzt macht SVERWEIS seine üblichen Daten zum Abrufen von Daten. Die Magie wird durch die MATCH-Funktion an der Position col_index ausgeführt.
Hier sucht SVERWEIS nach einem beliebigen Wert in G2 in der ersten Spalte des Tabellenarrays B2:E14. An der Stelle von col_index haben wir nun ÜBEREINSTIMMUNG(H1,B1:E1,0).
Die MATCH-Funktion sucht nach jedem Wert in H1 im Bereich B1: E1 und gibt den Index des Übereinstimmungswerts zurück.
Zum Beispiel, wenn wir tippen Student in H1 sucht es danach in Reichweite B1:E1. Es ist in C2 zu finden. daher gibt es 2 zurück. Wenn wir eingeben Grad es wird die Note des Schülers zurückgeben.
Beachten Sie, dass bei der Eingabe von Region #N/A zurückgegeben wird. Weil es außerhalb des Geltungsbereichs der Tabelle liegt. Wir werden später im Artikel darüber sprechen.
Dies ist der beste Weg, um SVERWEIS automatisch zu machen. Es gibt andere Methoden, aber sie sind nicht so flexibel.
7. Verwenden Sie die SVERWEIS-Funktion mit der COLUMN-Funktion
Die Spaltenfunktion gibt die Spaltennummer der bereitgestellten Referenz zurück. Und was brauchen wir zum Abrufen von Daten mit SVERWEIS? Spalte_index. Mit der COLUMN-Funktion können wir natürlich Daten aus jedem Datensatz abrufen.
Nehmen wir das obige Beispiel. Aber jetzt müssen wir ganze Daten abrufen. Nicht nur eine Spalte.
Schreiben Sie diese Formel in H2 und kopieren Sie sie in I2 und J2.
=SVERWEIS($G2,$B$2:$E$14,SPALTE(B1),0)
Dadurch erhalten Sie ein dynamisches Ergebnis. Wie? Mal sehen.
Der SVERWEIS funktioniert wie gewohnt. Bei column_index haben wir geschrieben SPALTE (B1), was in 2 übersetzt wird, da wir den Schülernamen in der 2. Spalte von der B-Spalte haben, gibt es den Namen des Schülers zurück.
Wichtig: Dies funktioniert, weil wir unsere Tabelle ab Spalte B haben. Wenn sich Ihre Tabelle in der Mitte des Blatts befindet, müssen Sie einige Zahlen subtrahieren oder hinzufügen, damit sie Ihren Anforderungen entspricht.
Wenn die obige Tabelle beispielsweise von C1 in derselben Struktur ausging, funktionierten alle folgenden Formeln einwandfrei.
=SVERWEIS($G2,$C$2:$F$14,SPALTE(B1),0)
=SVERWEIS($G2,$C$2:$F$14,SPALTE(C1)-1,0)
=SVERWEIS($G2,$C$2:$F$14,SPALTE(A1)+1,0)
8. SVERWEIS zum Zusammenführen von Daten
In den meisten Datenbanken gibt es eine Abfrage, die zu Tabellen zusammengeführt wird, meist als Join-Abfrage bezeichnet, aber MS Excel in keiner Datenbank, daher gibt es keine Join-Abfrage. Dies bedeutet jedoch nicht, dass wir nicht zwei Tabellen verknüpfen können, wenn wir einige gemeinsame Spalten in zwei Tabellen haben.
Wenn Sie also Daten von zwei Abteilungen desselben Unternehmens erhalten, möchten Sie diese zusammenführen, um diese Daten zu analysieren.
Wenn Sie beispielsweise die Noten der Schüler von den Lehrern und dann die Anwesenheitsliste von der Verwaltungsabteilung erhalten, möchten Sie an einer Stelle sehen, welcher Schüler welche Note und wie hoch seine Anwesenheitsquote ist.
Wir müssen sie in der folgenden Tabelle zusammenführen.
Markierungen müssen wir aus der Markierungstabelle und die Anwesenheit aus den Anwesenheitsdaten abrufen.
Schreiben Sie unter Formeln in Zelle D19, um Markierungen zu erhalten, und ziehen Sie sie nach unten.
=SVERWEIS(B19,$B$2:$D$15,3,0)
Schreiben Sie unter die Formel in Zelle E19
=SVERWEIS(B19,$G$3:$I$15,3,0)
Und wir haben zwei Tabellen zu einer Tabelle zusammengeführt. Sie können natürlich mehr als zwei Tabellen hinzufügen. Sie müssen nur mit SVERWEIS Daten aus allen gewünschten Tabellen an einem Ort abrufen.
Best Practices von SVERWEIS
In allen obigen Beispielen haben wir also nur vlookup mit Rohdaten verwendet. In allen oben genannten Beispielen waren wir vorsichtig mit den von uns verwendeten Daten. Wir mussten bei der Angabe von Referenzen von Tabellenarrays vorsichtig sein. Aber es gibt bestimmte Möglichkeiten, die diesen Aufwand stark reduzieren können.
9. Verwenden Sie absolute Referenzen mit SVERWEIS
Sie haben vielleicht bemerkt, dass ich in einigen Artikeln absolute Bereiche (Bereich $-Zeichen) verwendet habe. Nun, absolute Werte werden verwendet, wenn der Bereich nicht geändert werden soll, während die Formelzelle in andere Zellen kopiert wird.
Wenn ich beispielsweise =SVERWEIS(G2,B2: E14,2,0),0) in Zelle H2, und wenn ich es in Zelle H3 kopiere oder nach unten ziehe, ändert sich die Formel in =SVERWEIS(G3,B3:E15,2,0). Alle Verweise sind relativ geändert. Hier möchten wir möglicherweise die Referenz des Nachschlagewerts ändern, aber nicht das Tabellenarray. Weil B2 rauskommt oder reicht und wir es vielleicht immer in unseren Tabellenarrays haben wollen.
Um dies zu verhindern, verwenden wir absolute Referenzen. Wir machen die Bereiche absolut, die wir nicht ändern möchten, wie table_array in H2, =SVERWEIS(G2,$B$2:$E$14,2,0),0). Wenn Sie H2 in H3 kopieren, lautet die Formel =SVERWEIS(G3,$B$2:$E$14,2,0),0). Beachten Sie, dass der Lookup-Wert geändert wird, nicht jedoch table_array.
10. Verwenden Sie benannte Bereiche mit SVERWEIS
Im obigen Beispiel haben wir absolute Referenzierung verwendet, um das table_array zu reparieren. Es sieht ziemlich unscharf aus und ist schwer zu lesen. Wie wäre es, wenn du einfach schreiben kannst 'Daten' am Ort von $B$2:$E$14 und es würde verweisen auf $B$2:$E$14 immer. Es wird das Lesen und Schreiben der Formel noch einfacher machen.
Wählen Sie also einfach den Bereich aus und nennen Sie ihn „Daten“.
Um einen Bereich zu benennen, wählen Sie den Bereich aus. Klicken Sie mit der rechten Maustaste, klicken Sie auf Name definieren und benennen Sie den gewünschten Bereich. Jetzt nur =SVERWEIS(G3,Daten,2,0),0) Formel funktioniert genauso wie zuvor.
Sie können mehr über benannte Bereiche in Excel lesen. Ich habe hier jeden einzelnen Aspekt des benannten Bereichs erklärt.
11. Platzhalter für partielle Übereinstimmung mit SVERWEIS
SVERWEIS ermöglicht eine teilweise Übereinstimmung. Ja, während Sie eine genaue Übereinstimmung von SVERWEIS durchführen, können Sie Platzhalteroperatoren verwenden, um eine teilweise Übereinstimmung durchzuführen. Wenn Sie beispielsweise einen Wert suchen möchten, der mit „Gil“ beginnt, können Sie den Platzhalteroperator * mit „Gil“ als „Gil*“ verwenden. Sehen wir uns ein Beispiel an.
Hier möchte ich Noten von Studenten bekommen, deren Name mit Gil beginnt. Um dies zu erreichen, schreibe ich die folgende Formel.
=SVERWEIS("*Gill",C2:D14,2,0)
In Excel stehen zwei Platzhalter zur Verfügung, die mit der SVERWEIS-Funktion verwendet werden können.
Sternchen (*) Platzhalter. Dies wird verwendet, wenn der Benutzer die Anzahl der vorhandenen Zeichen nicht kennt und nur einige übereinstimmende Zeichen kennt. Kennt der Benutzer beispielsweise einen Namen, der mit „Sm“ beginnt, schreibt er „Sm*“. Wenn der Benutzer weiß, dass ein Nachschlagewert mit „123“ endet, schreibt er „*123“. (Platzhalter funktioniert nur mit Texten). Und wenn er weiß, dass „se“ in einem Text vorkommt, wird er „*se*“ schreiben.
Fragezeichen ("?"). Dies wird verwendet, wenn der Benutzer die Anzahl der fehlenden Zeichen kennt. Wenn ich beispielsweise einen Wert mit 4 Zeichen nachschlagen möchte, aber nicht weiß, was das sind, schreibe ich einfach "????" an der Stelle des Nachschlagewerts.
Vlookup gibt den jeweiligen Wert des zuerst gefundenen Werts zurück, der genau vier Zeichen enthält.
Wichtig: Platzhalter funktionieren nur mit Textwerten. Konvertieren Sie Zahlen in Text, wenn Sie dafür Platzhalter verwenden möchten.
Die Schwächen der SVERWEIS-Funktion
SVERWEIS ist in der Tat eine mächtige und einfache Funktion zum Abrufen von Werten, aber es gibt viele Bereiche, in denen SVERWEIS einfach nicht so hilfreich ist. Wenn Sie an Excel arbeiten, müssen Sie sie auch kennen und wissen, wie Sie Aufgaben ausführen, die SVERWEIS nicht ausführen kann.
12. Kann keinen Wert von der linken Seite von Table_Array abrufen
Der größte Fehler der SVERWEIS-Funktion besteht darin, dass sie keinen Wert von der linken Seite des Tabellenarrays abrufen kann. SVERWEIS sucht nur rechts vom Suchwert.
Dies liegt daran, dass SVERWEIS nach bestimmten sucht Lookup-Wert in der ersten Spalte von gegeben Tabellen-Array. Es wird nie einen Wert von außerhalb der Tabelle zurückgeben. Und wenn Sie versuchen, die linke Spalte im Tabellen-Array beizubehalten, wird sie zur ersten Spalte des Tabellen-Arrays, daher wird der Nachschlagewert in diesem Bereich durchsucht. Was wahrscheinlich zu Fehlern führt.
Um Werte links von einem Lookup-Wert zu suchen, verwenden wir stattdessen INDEX-MATCH. Die Funktion INDEX-MATCH kann Werte aus jeder Spalte auf dem Blatt nachschlagen. Tatsächlich sind der Nachschlagebereich und der Bereich, aus dem Sie nach Werten suchen möchten, völlig unabhängig, was die Indexübereinstimmung hochgradig anpassbar macht.
SVERWEIS gibt #N/A zurück, auch wenn ein Nachschlagewert vorhanden ist.
#N/A Fehler wird von der SVERWEIS-Funktion zurückgegeben, wenn der Nachschlagewert nicht gefunden wird. Sie können es ärgerlich finden, wenn ein Wert im Bereich vorhanden ist, aber SVERWEIS den Fehler #N/A zurückgibt.
14. Wenn Zahlen als Text formatiert sind
Dies geschieht meistens, wenn Zahlen als Text formatiert sind. Wenn Sie den Befehl STRG+F verwenden, wird Excel ihn finden, aber wenn Sie versuchen, SVERWEIS zu verwenden, gibt es #N/A zurück. Diese Aufgabe wird in den meisten Interviews als Fangfrage gestellt.
Im obigen Bild sehen Sie, dass 101 genau dort ist, aber die Formel gibt einen Fehler zurück. Wie gehen Sie damit um? Nun, es gibt zwei Methoden.
1. Text in Daten in Zahl umwandeln
Sie können dieses grüne Tag in der linken oberen Ecke in der Spalte rollno sehen. Sie weisen darauf hin, dass der Wert der Zelle ungewöhnlich ist. Wenn Sie die Zelle auswählen, wird angezeigt, dass die Zahl als Text formatiert ist.
Wenn Sie auf das Ausrufezeichen (!) klicken, wird die Option „In Zahl konvertieren“ angezeigt. Wählen Sie alle Zellen aus und klicken Sie auf diese Option. Alle Werte werden in Text umgewandelt.
2. Konvertieren Sie Ihre Nachschlagenummer in Text in der Formel
Im obigen Beispiel haben wir Originaldaten mutiert. Möglicherweise möchten Sie nichts an den Originaldaten ändern. Sie möchten dies stattdessen in der Formel tun. Um die Zahl in Text in der SVERWEIS-Formel zu ändern, schreiben Sie dies.
=SVERWEIS(TEXT(G2,"0"),B2:D14,2,0)
Hier ändert die Formel die Zahl dynamisch in Text und gleicht sie dann mit dem angegebenen Bereich ab.
15. Text mit führenden und nachgestellten Leerzeichen
Einige Daten, die von Dateneingabebetreibern bereitgestellt werden, und Daten aus dem Internet sind nicht sauber. Sie können nachgestellte Leerzeichen oder einige nicht druckbare Zeichen enthalten. Bei der Suche mit diesen Werten wird möglicherweise ein #N/A-Fehler angezeigt. Um dies zu vermeiden, bereinigen Sie zuerst Ihre Daten. Um führende Leerzeichen zu entfernen, verwenden Sie die TRIM-Funktion. TRIM entfernt eine beliebige Anzahl von führenden oder abschließenden Leerzeichen aus dem Text.
Ich schlage also vor, dass Sie eine neue Spalte hinzufügen und Ihre bereinigten Daten dort abrufen. Dann Wert einfügen. Wenn Sie möchten, können Sie jetzt die ursprüngliche Spalte loswerden.
=TRIM(REINIGEN(Text))
Umgang mit Fehlern von SVERWEIS
Wie Sie wissen, findet SVERWEIS keinen Wert und gibt einen #N/A-Fehler zurück. Nun, es ist in Ordnung, den Fehler #N/A zu erhalten, vielleicht beabsichtigen Sie manchmal, dies zu erhalten, wenn Sie beispielsweise überprüfen möchten, ob der Wert bereits in der Liste vorhanden ist oder nicht. Dort bedeutet #N/A, dass der Wert nicht vorhanden ist.
Aber #N/A sieht hässlich aus. Wie wäre es mit einer benutzerfreundlichen Ausgabe wie „Id nicht gefunden“ oder „Kunde nicht gefunden“. Wir können IFERROR mit der SVERWEIS-Funktion verwenden, um #N/A zu vermeiden.
Verwenden Sie die folgende Formel, um Fehler in SVERWEIS abzufangen.
=IFERROR(SVERWEIS(150,B2:E14,2,0),"Roll nein nicht gefunden")
16. SVERWEIS mit MEHREREN Kriterien
SVERWEIS kann nicht mit mehreren Kriterien arbeiten.Ja, Sie können nur ein Kriterium haben, um Werte mit der SVERWEIS-Formel zu suchen.
Zum Beispiel, wenn Sie den Vor- und Nachnamen in zwei separaten Spalten haben.
Und wenn Sie jetzt einen Wert suchen möchten, dessen Vorname John und Nachname Dave ist, können Sie dies normalerweise nicht tun.
Aber dafür gibt es einen Workaround. Sie können eine separate Spalte erstellen, indem Sie Vorname und Nachname verketten und dann diese Vor- und Nachnamenspalte nachschlagen.
Es gibt eine INDEX-MATCH-Alternative, die nach mehreren Kriterien suchen kann ohne eine helfende Spalte.
17. SVERWEIS ruft nur den ersten gefundenen Wert ab
Stellen Sie sich vor, Sie haben einige Daten in der zentralen Region. Nun möchten Sie die ersten 5 Datensätze der Zentralregion abrufen. Der SVERWEIS gibt nur den ersten zentralen Wert in allen fünf Datensätzen zurück. Dies ist ein wichtiger Hintergrund.
Aber das bedeutet nicht, dass wir dies nicht erreichen können. Wir können eine komplexe Array-Formel wie folgt verwenden.
=INDEX($C$2:$C$14,KLEIN(WENN(G2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1: 1)))
Schreiben Sie diese Formel und drücken Sie STRG+UMSCHALT+EINGABETASTE.
Und es ist fertig.
Ich habe es im Artikel erklärt, wie man mehrere Werte in Excel nachschlägt.
Also ja, Leute, in diesem Artikel habe ich alle möglichen Aspekte der SVERWEIS-Funktion nach meinem Wissen behandelt. Wenn Sie denken, dass ich etwas verpasst habe, lassen Sie es mich bitte in den Kommentaren unten wissen.
Vlookup Top 5 Werte mit doppelten Werten mit INDEX-MATCH in Excel
SVERWEIS Mehrere Werte
SVERWEIS mit dynamischem Col-Index
Teilweise Übereinstimmung mit SVERWEIS-Funktion
Verwenden Sie SVERWEIS aus zwei oder mehr Nachschlagetabellen
Vlookup nach Datum in Excel
Verwenden einer SVERWEIS-Formel, um zu überprüfen, ob ein Wert vorhanden ist
Wie man SVERWEIS von verschiedenen Excel-Tabellen aus macht
SVERWEIS mit Zahlen und Text
IF-, ISNA- und SVERWEIS-Funktion
ISNA- und SVERWEIS-Funktion
IFERROR und SVERWEIS-Funktion