SVERWEIS-Funktion in Excel – Anfängerleitfaden: Syntax und Beispiele

Inhalte

SVERWEIS-Funktion in Excel – allgemeine Beschreibung und Syntax
- Syntax der SVERWEIS-Funktion
SVERWEIS-Beispiele
Exakte oder ungefähre Übereinstimmung in der SVERWEIS-Funktion
- Beispiel 1: Finden einer genauen Übereinstimmung mit SVERWEIS
- Beispiel 2: Verwenden von SVERWEIS, um eine ungefähre Übereinstimmung zu finden
SVERWEIS in Excel – das müssen Sie sich merken!

Heute beginnen wir mit einer Reihe von Artikeln, die eine der nützlichsten Funktionen von Excel beschreiben − VPR (VLOOKUP). Diese Funktion ist gleichzeitig eine der komplexesten und am wenigsten verstandenen.

In diesem Tutorial auf VPR Ich werde versuchen, die Grundlagen so einfach wie möglich zu gestalten, um den Lernprozess für unerfahrene Benutzer so klar wie möglich zu gestalten. Darüber hinaus werden wir mehrere Beispiele mit Excel-Formeln untersuchen, die die häufigsten Anwendungsfälle für die Funktion demonstrieren VPR.

SVERWEIS-Funktion in Excel – allgemeine Beschreibung und Syntax

Also, was ist es VPR? Nun, zunächst einmal ist es eine Excel-Funktion. Was sie macht? Es sucht nach dem von Ihnen angegebenen Wert und gibt den entsprechenden Wert aus der anderen Spalte zurück. Technisch gesehen, VPR sucht den Wert in der ersten Spalte des angegebenen Bereichs und gibt das Ergebnis aus einer anderen Spalte in derselben Zeile zurück.

In der häufigsten Anwendung ist die Funktion VPR durchsucht die Datenbank nach einer gegebenen eindeutigen Kennung und extrahiert einige damit zusammenhängende Informationen aus der Datenbank.

Erster Buchstabe im Funktionsnamen VPR (SVERWEIS) bedeutet Вvertikal (Vvertikal). Daran kann man unterscheiden VPR für GPR (HLOOKUP), das nach einem Wert in der obersten Zeile eines Bereichs sucht − Гhorizontal (Hhorizontal).

Funktion VPR verfügbar in Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP und Excel 2000.

Syntax der SVERWEIS-Funktion

Funktion VPR (SVERWEIS) hat die folgende Syntax:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Wie Sie sehen können, eine Funktion VPR in Microsoft Excel hat 4 Optionen (oder Argumente). Die ersten drei sind obligatorisch, die letzten sind optional.

Lookup-Wert (lookup_value) – Der zu suchende Wert. Dies kann ein Wert (Zahl, Datum, Text) oder ein Zellbezug (der den Nachschlagewert enthält) oder ein Wert sein, der von einer anderen Excel-Funktion zurückgegeben wird. Diese Formel sucht beispielsweise nach dem Wert 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Wenn der Nachschlagewert kleiner als der kleinste Wert in der ersten Spalte des nachgeschlagenen Bereichs ist, wird die Funktion VPR wird einen Fehler melden #at (#N / A).

Tabellen_Array (Tabelle) – zwei oder mehr Datenspalten. Denken Sie daran, die Funktion VPR sucht immer nach dem Wert in der ersten Spalte des im Argument angegebenen Bereichs Tabellen_Array (Tisch). Der sichtbare Bereich kann verschiedene Daten enthalten, z. B. Text, Datumsangaben, Zahlen, Boolesche Werte. Bei der Funktion wird die Groß-/Kleinschreibung nicht beachtet, was bedeutet, dass Groß- und Kleinbuchstaben gleich behandelt werden. Unsere Formel sucht also nach dem Wert 40 in Zellen aus A2 zu A15, da A die erste Spalte des im Argument angegebenen Bereichs A2:B15 ist Tabellen_Array (Tisch):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

col_index_num (column_number) ist die Nummer der Spalte im angegebenen Bereich, aus der der Wert in der gefundenen Zeile zurückgegeben wird. Die Spalte ganz links im angegebenen Bereich ist 1, die zweite Spalte ist 2, die dritte Spalte ist 3 usw. Jetzt können Sie die ganze Formel lesen:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Formel auf der Suche nach Wert 40 im Bereich A2: A15 und gibt den entsprechenden Wert aus Spalte B zurück (weil B die zweite Spalte im Bereich A2:B15 ist).

Wenn der Wert des Arguments col_index_num (column_number) kleiner als 1dann VPR wird einen Fehler melden #WERT! (#WERT!). Und wenn es mehr als die Anzahl der Spalten im Bereich ist Tabellen_Array (Tabelle), gibt die Funktion einen Fehler zurück BEZUG! (#VERKNÜPFUNG!).

range_lookup (range_lookup) – bestimmt, wonach gesucht werden soll:
- exakte Übereinstimmung, Argument muss gleich sein FALSCH (FALSCH);
- ungefähre Übereinstimmung, Argument gleich ECHTER CODE (TRUE) oder gar nicht angegeben.
Dieser Parameter ist optional, aber sehr wichtig. Später in diesem Tutorial VPR Ich zeige Ihnen einige Beispiele, die erklären, wie Sie Formeln schreiben, um exakte und ungefähre Übereinstimmungen zu finden.

SVERWEIS-Beispiele

Ich hoffe auf die Funktion VPR etwas klarer für dich werden. Sehen wir uns nun einige Anwendungsfälle an VPR in Formeln mit echten Daten.

So verwenden Sie SVERWEIS, um in einer anderen Excel-Tabelle zu suchen

In der Praxis Formeln mit einer Funktion VPR werden selten verwendet, um nach Daten auf demselben Arbeitsblatt zu suchen. Meistens werden Sie entsprechende Werte aus einem anderen Blatt nachschlagen und abrufen.

Zu verwenden, um VPR, in einem anderen Microsoft Excel-Blatt suchen, müssen Sie im Argument Tabellen_Array (Tabelle) Geben Sie den Blattnamen mit einem Ausrufezeichen gefolgt von einem Zellbereich an. Die folgende Formel zeigt beispielsweise, dass der Bereich A2: B15 befindet sich auf einem Blatt mit dem Namen Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Der Blattname muss natürlich nicht manuell eingegeben werden. Beginnen Sie einfach mit der Eingabe der Formel, und wenn es um das Argument geht Tabellen_Array (Tabelle), wechseln Sie auf das gewünschte Blatt und markieren Sie den gewünschten Zellbereich mit der Maus.

Die im folgenden Screenshot gezeigte Formel sucht nach dem Text „Produkt 1“ in Spalte A (es ist die erste Spalte des Bereichs A1:B2) auf einem Arbeitsblatt Preise.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Bitte denken Sie daran, dass Sie bei der Suche nach einem Textwert diesen in Anführungszeichen („“) einschließen müssen, wie dies normalerweise in Excel-Formeln der Fall ist.

Zur Argumentation Tabellen_Array (Tabelle) ist es wünschenswert, immer absolute Referenzen (mit dem $-Zeichen) zu verwenden. In diesem Fall bleibt der Suchbereich beim Kopieren der Formel in andere Zellen unverändert.

Suchen Sie mit SVERWEIS in einer anderen Arbeitsmappe

Funktionieren VPR Wenn zwischen zwei Excel-Arbeitsmappen gearbeitet wird, müssen Sie den Arbeitsmappennamen in eckigen Klammern vor dem Blattnamen angeben.

Unten sehen Sie beispielsweise eine Formel, die nach dem Wert sucht 40 auf dem Blatt Sheet2 im Buch Zahlen.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Hier ist der einfachste Weg, um eine Formel in Excel zu erstellen VPRdie auf eine andere Arbeitsmappe verweist:

Öffnen Sie beide Bücher. Dies ist nicht erforderlich, aber es ist einfacher, auf diese Weise eine Formel zu erstellen. Sie möchten den Arbeitsmappennamen nicht manuell eingeben, oder? Darüber hinaus schützt es Sie vor versehentlichen Tippfehlern.
Beginnen Sie mit der Eingabe einer Funktion VPRund wenn es um den Streit geht Tabellen_Array (Tabelle), wechseln Sie in eine andere Arbeitsmappe und wählen Sie darin den gewünschten Suchbereich aus.

Der folgende Screenshot zeigt die Formel, bei der die Suche auf einen Bereich in der Arbeitsmappe festgelegt ist Preisliste.xlsx auf dem Blatt Preise.

Funktion VPR funktioniert auch dann, wenn Sie die gesuchte Arbeitsmappe schließen und der vollständige Pfad zur Arbeitsmappendatei in der Formelleiste angezeigt wird, wie unten gezeigt:

Wenn der Name der Arbeitsmappe oder des Blatts Leerzeichen enthält, muss er in Apostrophe eingeschlossen werden:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

So verwenden Sie einen benannten Bereich oder eine Tabelle in Formeln mit SVERWEIS

Wenn Sie denselben Suchbereich in mehreren Funktionen verwenden möchten VPRkönnen Sie einen benannten Bereich erstellen und seinen Namen als Argument in die Formel eingeben Tabellen_Array (Tabelle).

Um einen benannten Bereich zu erstellen, wählen Sie einfach die Zellen aus und geben Sie einen geeigneten Namen in das Feld ein Vorname, links neben der Bearbeitungsleiste.

Jetzt können Sie die folgende Formel aufschreiben, um den Preis eines Produkts zu ermitteln Produkt 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

Die meisten Bereichsnamen funktionieren für die gesamte Excel-Arbeitsmappe, sodass der Blattname für das Argument nicht angegeben werden muss Tabellen_Array (Tabelle), auch wenn sich die Formel und der Suchbereich auf unterschiedlichen Arbeitsblättern befinden. Wenn sie sich in verschiedenen Arbeitsmappen befinden, müssen Sie vor dem Namen des Bereichs den Namen der Arbeitsmappe angeben, z. B. wie folgt:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Die Formel sieht also viel klarer aus, einverstanden? Außerdem ist die Verwendung benannter Bereiche eine gute Alternative zu absoluten Bezügen, da sich der benannte Bereich nicht ändert, wenn Sie die Formel in andere Zellen kopieren. So können Sie sicher sein, dass der Suchbereich in der Formel immer korrekt bleibt.

Wenn Sie mit dem Befehl einen Zellbereich in eine vollwertige Excel-Tabelle umwandeln Tisch Registerkarte (Tabelle). Einfügen (Einfügen) und wenn Sie dann einen Bereich mit der Maus auswählen, fügt Microsoft Excel automatisch die Spaltennamen (oder den Tabellennamen, wenn Sie die gesamte Tabelle auswählen) zur Formel hinzu.

Die fertige Formel sieht in etwa so aus:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Oder vielleicht sogar so:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Wenn Sie benannte Bereiche verwenden, verweisen die Links auf dieselben Zellen, unabhängig davon, wohin Sie die Funktion kopieren VPR innerhalb der Arbeitsmappe.

Verwenden von Platzhaltern in VLOOKUP-Formeln

Wie bei vielen anderen Funktionen auch, VPR Sie können die folgenden Platzhalterzeichen verwenden:

Fragezeichen (?) – ersetzt jedes einzelne Zeichen.
Asterisk (*) – ersetzt eine beliebige Zeichenfolge.

Verwenden von Platzhaltern in Funktionen VPR kann in vielen Fällen nützlich sein, zum Beispiel:

Wenn Sie sich nicht genau an den Text erinnern, den Sie finden müssen.
Wenn Sie ein Wort finden möchten, das Teil des Inhalts einer Zelle ist. Wisse das VPR sucht nach dem Inhalt der Zelle als Ganzes, als ob die Option aktiviert wäre Passen Sie den gesamten Zelleninhalt an (Ganze Zelle) in der Standard-Excel-Suche.
Wenn eine Zelle am Anfang oder Ende des Inhalts zusätzliche Leerzeichen enthält. In einer solchen Situation können Sie sich lange den Kopf zerbrechen und versuchen herauszufinden, warum die Formel nicht funktioniert.

Beispiel 1: Suche nach Text, der mit bestimmten Zeichen beginnt oder endet

Angenommen, Sie möchten in der unten gezeigten Datenbank nach einem bestimmten Kunden suchen. Du erinnerst dich nicht an seinen Nachnamen, aber du weißt, dass er mit „ack“ beginnt. Hier ist eine Formel, die den Job gut machen wird:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Da Sie nun sicher sind, dass Sie den richtigen Namen gefunden haben, können Sie dieselbe Formel verwenden, um den von diesem Kunden gezahlten Betrag zu ermitteln. Ändern Sie dazu einfach das dritte Argument der Funktion VPR auf die gewünschte Spaltennummer. In unserem Fall ist dies Spalte C (3. im Bereich):

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Hier sind einige weitere Beispiele mit Platzhaltern:

~ Finden Sie einen Namen, der auf „man“ endet:

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Finden Sie einen Namen, der mit „ad“ beginnt und mit „son“ endet:

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Wir finden den Vornamen in der Liste, bestehend aus 5 Zeichen:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Funktionieren VPR mit Platzhaltern funktionierte korrekt, als viertes Argument sollte man immer verwenden FALSCH (FALSCH). Wenn der Suchbereich mehr als einen Wert enthält, der mit den Suchbegriffen mit Platzhaltern übereinstimmt, wird der erste gefundene Wert zurückgegeben.

Beispiel 2: Kombinieren Sie Platzhalter und Zellbezüge in VLOOKUP-Formeln

Sehen wir uns nun ein etwas komplexeres Beispiel für die Suche mit der Funktion an VPR nach Wert in einer Zelle. Stellen Sie sich vor, dass Spalte A eine Liste mit Lizenzschlüsseln und Spalte B eine Liste mit Namen ist, die eine Lizenz besitzen. Außerdem haben Sie in Zelle C1 einen Teil (mehrere Zeichen) einer Art Lizenzschlüssel und möchten den Namen des Eigentümers finden.

Dies kann mit der folgenden Formel erfolgen:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Diese Formel sucht den Wert aus Zelle C1 im angegebenen Bereich und gibt den entsprechenden Wert aus Spalte B zurück. Beachten Sie, dass wir im ersten Argument vor und nach dem Zellbezug ein kaufmännisches Und-Zeichen (&) verwenden, um die Textzeichenfolge zu verknüpfen.

Wie Sie in der folgenden Abbildung sehen können, ist die Funktion VPR gibt „Jeremy Hill“ zurück, da sein Lizenzschlüssel die Zeichenfolge aus Zelle C1 enthält.

Beachten Sie, dass das Argument Tabellen_Array (table) im obigen Screenshot enthält den Namen der Tabelle (Table7), anstatt einen Zellbereich anzugeben. Dies haben wir im vorherigen Beispiel getan.

Exakte oder ungefähre Übereinstimmung in der SVERWEIS-Funktion

Und schließlich wollen wir uns das letzte Argument genauer ansehen, das für die Funktion angegeben ist VPR - range_lookup (Intervallansicht). Wie zu Beginn der Lektion erwähnt, ist dieses Argument sehr wichtig. Sie können in derselben Formel mit ihrem Wert völlig unterschiedliche Ergebnisse erhalten ECHTER CODE (Wahr oder FALSCH (FALSCH).

Lassen Sie uns zunächst herausfinden, was Microsoft Excel unter genauen und ungefähren Übereinstimmungen versteht.

Wenn die Argumentation range_lookup (range_lookup) ist gleich FALSCH (FALSCH) sucht die Formel nach einer exakten Übereinstimmung, dh genau dem gleichen Wert wie im Argument angegeben Lookup-Wert (Lookup-Wert). Wenn in der ersten Spalte des Bereichs table_array (Tabelle) trifft auf zwei oder mehr Werte, die mit dem Argument übereinstimmen Lookup-Wert (Suchwert), dann wird der erste ausgewählt. Wenn keine Übereinstimmungen gefunden werden, meldet die Funktion einen Fehler #at (#N / A). Die folgende Formel meldet beispielsweise einen Fehler #at (#N/A) wenn es keinen Wert im Bereich A2:A15 gibt 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Wenn die Argumentation range_lookup (range_lookup) ist gleich ECHTER CODE (WAHR) sucht die Formel nach einer ungefähren Übereinstimmung. Genauer gesagt zuerst die Funktion VPR sucht nach einer genauen Übereinstimmung, und wenn keine gefunden wird, wählt sie eine ungefähre aus. Eine ungefähre Übereinstimmung ist der größte Wert, der den im Argument angegebenen Wert nicht überschreitet. Lookup-Wert (Lookup-Wert).

Wenn die Argumentation range_lookup (range_lookup) ist gleich ECHTER CODE (TRUE) oder nicht angegeben, dann sollten die Werte in der ersten Spalte des Bereichs aufsteigend sortiert werden, also vom kleinsten zum größten. Ansonsten die Funktion VPR kann ein fehlerhaftes Ergebnis zurückgeben.

Um die Bedeutung der Wahl besser zu verstehen ECHTER CODE (WAHRHEIT) bzw FALSCH (FALSCH), schauen wir uns einige weitere Formeln mit der Funktion an VPR und schau dir die Ergebnisse an.

Beispiel 1: Finden einer genauen Übereinstimmung mit SVERWEIS

Wie Sie sich erinnern, ist die Suche nach einer exakten Übereinstimmung das vierte Argument der Funktion VPR sollte egal sein FALSCH (FALSCH).

Gehen wir zurück zum Tisch aus dem allerersten Beispiel und finden heraus, welches Tier sich mit hoher Geschwindigkeit bewegen kann 50 Meilen pro Stunde. Ich glaube, dass diese Formel Ihnen keine Schwierigkeiten bereiten wird:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Beachten Sie, dass unser Suchbereich (Spalte A) zwei Werte enthält 50 – in Zellen A5 и A6. Formel gibt Wert aus Zelle zurück B5. Wieso den? Denn bei der Suche nach einer exakten Übereinstimmung wird die Funktion VPR verwendet den ersten gefundenen Wert, der mit dem gesuchten übereinstimmt.

Beispiel 2: Verwenden von SVERWEIS, um eine ungefähre Übereinstimmung zu finden

Wenn Sie die Funktion verwenden VPR um nach einer ungefähren Übereinstimmung zu suchen, dh wenn das Argument range_lookup (range_lookup) ist gleich ECHTER CODE (TRUE) oder ausgelassen haben, müssen Sie zuerst den Bereich nach der ersten Spalte in aufsteigender Reihenfolge sortieren.

Dies ist sehr wichtig, da die Funktion VPR gibt den nächstgrößten Wert nach dem gegebenen zurück, und dann stoppt die Suche. Wenn Sie die richtige Sortierung vernachlässigen, erhalten Sie am Ende sehr seltsame Ergebnisse oder eine Fehlermeldung. #at (#N / A).

Jetzt können Sie eine der folgenden Formeln verwenden:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Wie Sie sehen, möchte ich herausfinden, welches der Tiere am schnellsten ist 69 Meilen pro Stunde. Und hier ist das Ergebnis, das mir die Funktion zurückgegeben hat VPR:

Wie Sie sehen können, hat die Formel ein Ergebnis zurückgegeben Antilope (Antilope), deren Geschwindigkeit 61 Meilen pro Stunde, obwohl die Liste auch enthält Cheetah (Gepard), der schnell läuft 70 Meilen pro Stunde, und 70 ist näher an 69 als 61, nicht wahr? Warum passiert das? Denn die Funktion VPR gibt bei der Suche nach einer ungefähren Übereinstimmung den größten Wert zurück, der nicht größer als der gesuchte Wert ist.

Ich hoffe, dass diese Beispiele etwas Licht in die Arbeit mit der Funktion bringen VPR in Excel, und Sie sehen sie nicht mehr als Außenseiterin an. Nun schadet es nicht, die Kernpunkte des gelernten Stoffes kurz zu wiederholen, um ihn besser im Gedächtnis zu fixieren.

SVERWEIS in Excel – das müssen Sie sich merken!

Funktion VPR Excel kann nicht nach links schauen. Es sucht immer nach dem Wert in der linken Spalte des durch das Argument angegebenen Bereichs Tabellen_Array (Tabelle).
In Funktion VPR alle Werte sind case-insensitive, d.h. kleine und große Buchstaben sind gleichwertig.
Wenn der gesuchte Wert kleiner als der Mindestwert in der ersten Spalte des gesuchten Bereichs ist, wird die Funktion VPR wird einen Fehler melden #at (#N / A).
Wenn 3. Argument col_index_num (column_number) kleiner als 1Funktion VPR wird einen Fehler melden #WERT! (#WERT!). Wenn es größer als die Anzahl der Spalten im Bereich ist Tabellen_Array (Tabelle), meldet die Funktion einen Fehler BEZUG! (#VERKNÜPFUNG!).
Verwenden Sie im Argument absolute Zellbezüge Tabellen_Array (Tabelle), damit beim Kopieren der Formel der richtige Suchbereich erhalten bleibt. Versuchen Sie alternativ, benannte Bereiche oder Tabellen in Excel zu verwenden.
Denken Sie bei der ungefähren Übereinstimmungssuche daran, dass die erste Spalte im gesuchten Bereich in aufsteigender Reihenfolge sortiert werden muss.
Erinnern Sie sich schließlich an die Bedeutung des vierten Arguments. Werte verwenden ECHTER CODE (WAHRHEIT) bzw FALSCH (FALSCH) absichtlich und Sie werden viele Kopfschmerzen los.

In den folgenden Artikeln unseres Funktions-Tutorials VPR In Excel lernen wir fortgeschrittenere Beispiele kennen, z. B. das Durchführen verschiedener Berechnungen mit VPR, Extrahieren von Werten aus mehreren Spalten und mehr. Vielen Dank für das Lesen dieses Tutorials und ich hoffe, wir sehen uns nächste Woche wieder!