Viele Menschen kennen und nutzen es oft. Gleichzeitig weist es jedoch zwei erhebliche Nachteile auf, z. B. die Art und Weise, wie „Linker VLOOKUP“ erstellt wird. Ich selbst nutze SVERWEIS nur, wenn ich schnell etwas erledigen muss. In Dateien für den „normalen“ Gebrauch erstelle ich die Konstrukte INDEX und SEARCH. Wie ist es besser?

Wenn Sie einer Datentabelle eine Spalte hinzufügen, müssen Sie die Spaltennummer in der Formel selbst nicht ändern (wie bei einem VLOOKUP). Die Spalte wird automatisch verschoben
Sie können einen VLOOKUP auch andersherum durchführen, d. h. Treffen Sie eine Auswahl aus der Tabelle von rechts nach links. In einem SVERWEIS sollte die erste Spalte immer durchsuchbar sein.

Wie das alles geht, lesen Sie weiter unten :)

Ich werde ein Beispiel aus verwenden. Um die Anwendung dieses Designs im Vergleich zu VLOOKUP besser zu verstehen:

Wie einfach ist das Ausfüllen, wenn Sie die Formel zum ersten Mal sehen?

Entscheiden Sie zunächst, wo sich die Ausgangstabelle befindet und wo Daten empfangen werden sollen! Schritt für Schritt, was auszufüllen ist.

Fügen Sie die Formel in den gewünschten Bereich der Tabelle ein
Anstelle von $G:$G setzen Sie die Zellen ein, in denen der Wert zu finden sein soll und dementsprechend als Ergebnis erscheinen soll. Wir suchen nach einem Nachnamen. Suchen Sie daher in der Anfangstabelle nach der Spalte mit den Nachnamen.
Ersetzen Sie $J:$J durch je nachdem, welche Werte an die Zelle zurückgegeben werden sollen. Wir benötigen Nachnamen je nach Fahrzeug – fügen Sie die Fahrzeuge ein, neben denen die Werte erscheinen sollen.
Geben Sie anstelle von $H:$H die Spalte ein, in der Sie den entsprechenden Wert finden möchten. Diese. Wir suchen nach Nachnamen nach Fahrzeug, was bedeutet, dass wir eine Spalte mit Fahrzeug in die Originaltabelle einfügen.

Wenn Sie sich fragen, wozu die $-Zeichen in der Formel dienen, lesen Sie

INDEX und SUCHE. Was sind diese Funktionen?

INDEX und MATCH sind sehr leistungsstarke Funktionen, die in Kombination mit anderen hervorragende Ergebnisse liefern.

INDEX(Array; Zeilennummer; Spaltennummer)

Gibt den Wert am Schnittpunkt einer angegebenen Zeile und einer Spalte in einem angegebenen Bereich zurück. Diese. es funktioniert zunächst mit zweidimensionalen Arrays.

Durch Auswahl des Datenarrays J1:K4 und Setzen der Zeilen- und Spaltennummern auf zwei haben wir den entsprechenden Wert erhalten.

Schauen Sie sich die Originalformel an

INDEX($G:$G,MATCH($J:$J,$H:$H,0),1)

Wir werden sehen, dass wir anstelle des zweiten Arguments (Zeilennummer) die Formel MATCH haben. Was macht sie hier?

MATCH ist eine Suche nach Wert. Die Funktion sucht nach einem bestimmten Wert in einer Zeile oder Spalte und gibt seine Ordnungszahl (vom Anfang des Bereichs) zurück. Diese. Im zweiten Argument der INDEX-Funktion finden wir die benötigte Fahrzeugnummer, wir erhalten deren Nummer, zum Beispiel 2.

Und bereits im eindimensionalen Array $G:$G finden wir eine Zelle mit der Zeilennummer = 2. Dies funktioniert für jede Zelle in Spalte J.

Es scheint also kein sehr kompliziertes Design zu sein, aber wie ich oben geschrieben habe, ist es sehr effektiv. Weil Sie müssen nicht wie beim VLOOKUP ständig die Nummer des gewünschten Werts ändern, sondern können sowohl rechts als auch links suchen :)

Schreiben Sie Kommentare, wenn Sie Fragen haben.

Wie immer!

Teilen Sie unseren Artikel in Ihren sozialen Netzwerken:

In Excel erstellte Tabellen zeichnen sich nicht immer dadurch aus, dass die Namen von Datenkategorien nur in den Spaltenüberschriften definiert werden müssen. Manchmal haben wir bei der Analyse von Tabellendaten die Möglichkeit, sowohl Spaltenüberschriften als auch Zeilennamen zu verwenden, die sich in der ersten Spalte befinden.

Beispiel einer Formel mit VLOOKUP und MATCH

Ein Beispiel für eine Bonus-Zeugnistabelle ist unten in der Abbildung dargestellt:

Der Zweck dieser Tabelle besteht darin, geeignete Prämienwerte im Bereich B5:K11 basierend auf einem bestimmten Umsatz und Geschäften mit Mindest- oder Mindestumsatz zu finden maximale Größen Bonuszahlungen. Die Schwierigkeit entsteht, wenn automatische Erkennung die Höhe des Bonus, mit dem ein Mitarbeiter rechnen kann, wenn er eine bestimmte Umsatzgrenze überschreitet. Denn es gibt keinen klar definierten Prämienzahlungsbetrag für jeden voraussichtlichen Erlösbetrag. Es gibt lediglich Unter- und Obergrenzen für die Prämienbeträge je Filiale.

Beispielsweise benötigen wir das Programm, um automatisch die mögliche Mindestprämie für einen Verkäufer aus der 3. Filiale zu ermitteln, dessen Umsatz die Grenze von 370.000 überschritten hat.

Dafür:

Geben Sie in Zelle B14 den Umsatzbetrag ein: 370.000.
Geben Sie in Zelle B15 die Filialnummer ein: 3.
Geben Sie in Zelle B16 die folgende Formel ein:

Infolgedessen wurde die untere Grenze der Prämie für das Geschäft Nr. 3 mit einem Umsatz von mehr als 370.000, aber weniger festgelegt<400 000.

Hallo lieber Leser!

In diesem Artikel möchte ich eine der wohl nützlichsten Funktionen in Excel beschreiben – die SVERWEIS-Funktion. Über die Funktion kann viel gesagt werden, aber das allererste, was erwähnenswert ist, ist, dass die Funktion eine der komplexesten und am wenigsten verstandenen ist.

In diesem Artikel werde ich versuchen, in einfacher und verständlicher Sprache zu beschreiben, wie die SVERWEIS-Funktion funktioniert, und ihre Funktionen, Beschreibung und Syntax anhand von Beispielen zu zeigen.

Also, was genau ist diese Funktion, was macht sie und wie ist sie geschrieben? Die einfachste Erklärung für die Funktionsweise der VLOOKUP-Funktion besteht darin, dass sie in der Werteliste entsprechend dem von Ihnen angegebenen Kriterium einen eindeutigen Bezeichner nach bestimmten damit verbundenen Informationen durchsucht.

Wenn Sie den Namen der VLOOKUP-Funktion entschlüsseln, wird anhand des ersten Buchstabens klar, wie diese Funktion funktioniert, B – bedeutet "Vertikale", das heißt, es sucht nach Werten in , aber für horizontale Listen haben wir . Die SVERWEIS-Funktion stand uns erstmals mit Excel 2000 zur Verfügung.
Die SVERWEIS-Funktion in Excel hat die folgende Syntax:

=SVERWEIS(_Suchwert_;_Tabelle_; _Spaltennummer_; _[Intervallansicht]_), Wo:

Lookup-Wert– Dies ist genau der Wert, nach dem wir suchen müssen, und es kann jeder Wert sein: eine Zahl, ein Datum, ein Text, ein Link zu einer Zelle, die den gewünschten Wert enthält, oder ein Wert, der durch eine andere Formel erhalten wird;
Tisch– es handelt sich um zwei oder mehr Spalten mit unterschiedlichen Daten; die Groß-/Kleinschreibung von Zeichen wird bei der Suche übrigens von der Funktion nicht berücksichtigt;
Spaltennummer– Dies ist die Nummer der Spalte im angegebenen Bereich, aus der der Wert in der gefundenen Zeile ermittelt wird.
Intervall_Ansicht– Dieser Parameter bestimmt, wonach wir genau suchen; für eine genaue Übereinstimmung ist das Argument gleich "FALSCH" oder eine ungefähre Übereinstimmung, das Argument wird gleich WAHR. Dieser Parameter ist optional, aber dennoch wichtig. In den folgenden Beispielen zeige ich, wie man exakte und ungefähre Übereinstimmungsformeln erstellt.

Schauen wir uns nun anhand eines Beispiels an, wie man auf einem anderen Blatt nach beliebigen Daten sucht, da die SVERWEIS-Funktion in der Praxis sehr selten für die Suche auf dem aktuellen Blatt verwendet wird:

VLOOKUP("GM";$A$5:$B$10;2)

Die Formel sucht nach dem Text „GM“ in Spalte A im aktuellen Blatt.
Beratung! Bei Verwendung des Arguments „Tabelle“ empfiehlt es sich, eine Option wie (das ist die Zellenadresse mit dem $-Zeichen) zu verwenden. In diesem Fall wird der Suchbereich festgelegt und ändert sich beim Kopieren der Formel nicht.

Schauen wir uns ein Beispiel für die Suche nach Werten an, wie die SVERWEIS-Funktion in einer anderen Arbeitsmappe funktioniert:

VLOOKUP("GM";[Dateipfad]Base!A2:B10;2)

Wie Sie sehen, gibt es nichts Kompliziertes, die Argumentation ist nur komplizierter geworden "Tisch", es hat jetzt einen Link zu einer Datei, an die der Name angehängt ist.

Beratung! Es ist besser, die Formel mit zwei aktivierten Dateien einzugeben, in einer geben Sie die VLOOKUP-Funktion ein, und wenn Sie zum Argument „Tabelle“ gelangen, gehen Sie zur zweiten Datei und wählen Sie mit der Maus den Bereich aus, den Sie durchsuchen möchten.

Sie können auch ein Beispiel betrachten, bei dem wir den genauen Wert nicht kennen; wir werden Platzhalterzeichen in der SVERWEIS-Formel verwenden. Manchmal müssen wir nicht etwas Bestimmtes, sondern etwas mit gemeinsamen Eigenschaften finden; alternativ haben wir viele Sofas verschiedener Modelle sowie andere Möbel, aber wir müssen sie nur auswählen.

Die SVERWEIS-Funktion kann beim Ersetzen die folgenden Zeichen verwenden:

„?“ (Fragezeichen) – ermöglicht es Ihnen, ein beliebiges Zeichen zu ersetzen;
„*“ (Sternchen)– ersetzt eine beliebige Anzahl und Folge von Zeichen.

VLOOKUP(„A*“,$A$2:$B$10,1,FALSE)

Beratung! Damit die VLOOKUP-Funktion ordnungsgemäß funktioniert, müssen Sie den Parameter „FALSE“ als viertes Argument verwenden.
Wenn wir das Thema der genauen oder ungefähren Übereinstimmung in der Syntax der SVERWEIS-Funktion bereits angesprochen haben, schauen wir uns das genauer an:

wenn Argument „Zeitrafferbetrachtung“ gleicht "LÜGE" In diesem Fall sucht die Formel nach einer genauen Übereinstimmung mit dem Argument „Suchwert“. Wenn die Formel auf zwei oder mehr Werte stößt, die mit dem Argument „Suchwert“ übereinstimmen, wird der erste aus der Liste ausgewählt. Wenn keine Übereinstimmung gefunden wird, gibt die Formel zurück.
wenn das Argument „Zeitrafferbetrachtung“ hat die Bedeutung "WAHR" Die Formel sucht nach einer ungefähren Übereinstimmung, oder genauer gesagt, die Funktion sucht zunächst nach einer genauen Übereinstimmung und wählt erst dann eine ungefähre Übereinstimmung aus, da sie keine findet.

Beratung!Für den Fall, dass das Argument „Intervallscan“ gleich „TRUE“ ist oder nicht angegeben ist, wird der Wert in der ersten Spalte benötigt, vom kleinsten zum größten. Andernfalls besteht die Möglichkeit eines fehlerhaften Ergebnisses der SVERWEIS-Funktion.

Sehen wir uns an, wie die SVERWEIS-Funktion bei der Suche nach exakten Kriterien funktioniert. Versuchen wir zum Beispiel herauszufinden, welches Auto sich mit einer Geschwindigkeit von 200 km/h bewegt. Ich denke, diese Formel wird Ihnen nicht schwer fallen:

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

Trotz der Tatsache, dass wir mehrere Werte von 200 haben, haben wir nur einen erhalten, denn wenn die VLOOKUP-Funktion genau übereinstimmt, verwendet das System nur den ersten Wert, der im angegebenen Bereich gefunden wurde.
Lassen Sie uns nun die Funktionsweise der VLOOKUP-Funktion auf eine ungefähre Übereinstimmung der Werte testen. Finden wir heraus, welches Auto mit einer Geschwindigkeit von 260 km/h fährt. Das Erste, was Sie tun, wenn „Zeitrafferbetrachtung“ gleicht "WAHR"— Sie sortieren Ihren Wertebereich nach der ersten Spalte in aufsteigender Reihenfolge. Dies ist notwendig und wichtig, da die VLOOKUP-Funktion den nächstgrößten Wert aus einer bestimmten Bedingung findet und die Suche dann stoppt. Wenn Sie die Sortierhinweise nicht befolgen, kommt es zu einer Fehlermeldung oder anderen seltsamen Ergebnissen.

Zur Suche verwenden wir einen VLOOKUP der folgenden Form:

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

Unsere Formel hat also den gefundenen Wert von 240 zurückgegeben, obwohl wir auch einen Wert von 270 haben und dieser Wert näher zu liegen scheint, aber die Besonderheit der Funktionsweise der VLOOKUP-Funktion besteht darin, dass sie bei Verwendung einer genauen Übereinstimmung nach dem größten sucht Wert in der Liste, übersteigt jedoch nicht seinen Wert.
Ich denke, die Beispiele, die ich in dem Artikel besprochen habe, werden Ihnen helfen, alles über die SVERWEIS-Funktion zu verstehen, wie sie verwendet wird und wofür sie verwendet werden sollte.

Allerdings gibt es viele fortgeschrittenere Beispiele, die wir uns in anderen Artikeln ansehen werden.

Und das ist alles für mich! Ich hoffe wirklich, dass Ihnen das alles klar ist. Für Ihre Kommentare wäre ich sehr dankbar, da dies ein Indikator für die Lesbarkeit ist und mich dazu inspiriert, neue Artikel zu schreiben! Teilen Sie das Gelesene mit Ihren Freunden und liken Sie es!

Heute starten wir eine Artikelserie, die eine der nützlichsten Funktionen von Excel beschreibt – SVERWEIS(VLOOKUP). Diese Funktion ist gleichzeitig eine der komplexesten und am wenigsten verstandenen.

In diesem Tutorial zu SVERWEIS Ich werde versuchen, die Grundlagen in einer möglichst einfachen Sprache darzustellen, um den Lernprozess für unerfahrene Benutzer so verständlich wie möglich zu machen. Darüber hinaus werden wir mehrere Beispiele mit Excel-Formeln untersuchen, die die häufigsten Verwendungsmöglichkeiten der Funktion veranschaulichen SVERWEIS.

SVERWEIS-Funktion in Excel – allgemeine Beschreibung und Syntax

Also, was ist es SVERWEIS? Zunächst einmal handelt es sich um eine Excel-Funktion. Was macht sie? Es sucht nach dem von Ihnen angegebenen Wert und gibt den entsprechenden Wert aus einer anderen Spalte zurück. In technischer Hinsicht, SVERWEIS sucht den Wert in der ersten Spalte eines bestimmten Bereichs und gibt das Ergebnis aus einer anderen Spalte in derselben Zeile zurück.

In der häufigsten Anwendung ist die Funktion SVERWEIS Durchsucht die Datenbank nach einer bestimmten eindeutigen Kennung und ruft einige damit verbundene Informationen aus der Datenbank ab.

Erster Buchstabe im Funktionsnamen SVERWEIS(SVERWEIS) bedeutet IN vertikal ( V Vertikale). Daran kann man unterscheiden SVERWEIS aus GPR(HLOOKUP), das nach dem Wert in der obersten Zeile des Bereichs sucht - G horizontal ( H horizontal).

Funktion SVERWEIS Verfügbar in Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP und Excel 2000.

Syntax der SVERWEIS-Funktion

Funktion SVERWEIS(SVERWEIS) hat die folgende Syntax:

VLOOKUP(lookup_value,table_array,col_index_num,)
VLOOKUP(lookup_value, table, columns_number, [interval_lookup])

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

Lookup-Wert(lookup_value) – der Wert, nach dem gesucht werden soll. Dies kann ein Wert (Zahl, Datum, Text) oder ein Zellbezug (der den Suchwert enthält) oder ein von einer anderen Excel-Funktion zurückgegebener Wert sein. Diese Formel sucht beispielsweise nach dem Wert 40 :
VLOOKUP(40,A2:B15,2)
=SVERWEIS(40,A2:B15,2)

Wenn der gesuchte Wert kleiner als der kleinste Wert in der ersten Spalte des von Ihnen betrachteten Bereichs ist, wird die Funktion SVERWEIS wird einen Fehler melden #N / A(#N / A).

table_array(Tabelle) – zwei oder mehr Datenspalten. Denken Sie daran, Funktion SVERWEIS sucht immer nach dem Wert in der ersten Spalte des im Argument angegebenen Bereichs table_array(Tisch). Der angezeigte Bereich kann verschiedene Daten enthalten, beispielsweise Text, Datumsangaben, Zahlen und logische Werte. Die Funktion unterscheidet nicht zwischen Groß- und Kleinschreibung, d. h. Groß- und Kleinschreibung werden als gleich betrachtet. Daher sucht unsere Formel nach dem Wert 40 in Zellen aus A2 Vor A15 weil A die erste Spalte des im Argument angegebenen Bereichs A2:B15 ist table_array(Tisch):
VLOOKUP(40,A2:B15,2)
=SVERWEIS(40,A2:B15,2)

Wenn der Argumentwert col_index_num(Spaltennummer) weniger 1 , Das SVERWEIS wird einen Fehler melden #WERT!(#WERT!). Und wenn es größer als die Anzahl der Spalten im Bereich ist table_array(Tabelle) gibt die Funktion einen Fehler zurück #REF!(#VERKNÜPFUNG!).

range_lookup(interval_view) – definiert, wonach gesucht werden soll:
- Genaue Übereinstimmung, Argument muss gleich sein FALSCH(LÜGE);
- ungefähre Übereinstimmung, Argument ist gleich WAHR(WAHR) oder gar nicht angegeben.
Dieser Parameter ist optional, aber sehr wichtig. Später in diesem Tutorial SVERWEIS Ich zeige Ihnen einige Beispiele, die erklären, wie Sie Formeln richtig zusammenstellen, um genaue und ungefähre Übereinstimmungen zu finden.

Beispiele mit der VLOOKUP-Funktion

Ich hoffe die Funktion SVERWEIS ist für dich etwas klarer geworden. Schauen wir uns nun einige Anwendungsfälle an SVERWEIS in Formeln mit realen Daten.

So verwenden Sie VLOOKUP, um eine Suche in einer anderen Excel-Tabelle durchzuführen

In der Praxis Formeln mit Funktion SVERWEIS Wird selten für die Suche nach Daten auf demselben Blatt verwendet. In den meisten Fällen werden Sie entsprechende Werte aus einem anderen Blatt suchen und abrufen.

Zu, verwenden SVERWEIS Um eine Suche in einer anderen Microsoft Excel-Tabelle durchzuführen, müssen Sie das Argument eingeben table_array(Tabelle) Geben Sie den Blattnamen mit einem Ausrufezeichen an, gefolgt von einem Zellbereich. Die folgende Formel zeigt beispielsweise den Bereich A2:B15 steht auf dem Blatt mit dem Namen Blatt2.

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

Natürlich muss der Blattname nicht manuell eingegeben werden. Beginnen Sie einfach mit der Eingabe der Formel und dem Argument table_array(Tabelle), wechseln Sie zum gewünschten Blatt und wählen Sie mit der Maus den gewünschten Zellbereich aus.

Die im Screenshot unten gezeigte Formel sucht nach dem Text „Produkt 1“ in Spalte A (dies ist die erste Spalte des Bereichs A2:B9) im Arbeitsblatt Preise.

VLOOKUP("Produkt 1",Preise!$A$2:$B$9,2,FALSE)
=VLOOKUP("Produkt 1";Preise!$A$2:$B$9,2;FALSE)

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

Zur Argumentation table_array(Tabelle) Es empfiehlt sich, immer absolute Links (mit dem $-Zeichen) zu verwenden. In diesem Fall bleibt der Suchbereich unverändert, wenn Sie die Formel in andere Zellen kopieren.

Suchen Sie mit SVERWEIS in einer anderen Arbeitsmappe

Funktionieren SVERWEIS Wenn Sie zwischen zwei Excel-Arbeitsmappen arbeiten, 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 einem Blatt Blatt2 im Buch Numbers.xlsx:

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

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

Öffnen Sie beide Bücher. Dies ist nicht notwendig, erleichtert aber die Erstellung der Formel. Sie möchten den Namen der Arbeitsmappe nicht manuell eingeben, oder? Darüber hinaus sind Sie dadurch vor versehentlichen Tippfehlern geschützt.
Beginnen Sie mit der Eingabe der Funktion SVERWEIS, und wenn es um den Streit geht table_array(Tabelle), wechseln Sie zu einer anderen Arbeitsmappe und wählen Sie darin den gewünschten Suchbereich aus.

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

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

Wenn der Name einer Arbeitsmappe oder eines Blattes Leerzeichen enthält, muss er in Apostrophe eingeschlossen werden:

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

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

Wenn Sie planen, einen Suchbereich in mehreren Funktionen zu verwenden SVERWEIS können Sie einen benannten Bereich erstellen und seinen Namen als Argument in die Formel eingeben table_array(Tisch).

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

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

VLOOKUP("Produkt 1",Produkte,2)
=VLOOKUP("Produkt 1";Produkte;2)

Die meisten Bereichsnamen funktionieren für die gesamte Excel-Arbeitsmappe, daher ist es nicht erforderlich, den Blattnamen für das Argument anzugeben table_array(Tabelle), auch wenn sich Formel und Suchbereich auf unterschiedlichen Blättern der Arbeitsmappe befinden. Wenn sie sich in verschiedenen Büchern befinden, müssen Sie vor dem Namen des Bereichs den Namen der Arbeitsmappe angeben, beispielsweise so:

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

Dadurch sieht die Formel viel klarer aus, finden Sie nicht auch? Darüber hinaus 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. Dadurch können Sie sicher sein, dass der Suchbereich in der Formel immer korrekt bleibt.

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

Die fertige Formel sieht etwa so aus:

VLOOKUP("Produkt 1",Tabelle46[:],2)
=SVERWEIS("Produkt 1";Tabelle46[:];2)

Oder vielleicht sogar so:

VLOOKUP("Produkt 1",Tabelle46,2)
=SVERWEIS("Produkt 1";Tabelle46;2)

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

Verwendung von Platzhaltern in SVERWEIS-Formeln

Wie bei vielen anderen Funktionen auch SVERWEIS Sie können die folgenden Platzhalter verwenden:

Fragezeichen (?) – ersetzt ein beliebiges Zeichen.
Sternchen (*) – ersetzt eine beliebige Zeichenfolge.

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

Wenn Sie sich nicht genau an den Text erinnern, den Sie suchen müssen.
Wenn Sie ein Wort finden möchten, das Teil des Inhalts einer Zelle ist. Wisse das SVERWEIS Durchsucht den gesamten Inhalt der Zelle, als ob die Option aktiviert wäre Den gesamten Zellinhalt abgleichen(Gesamte Zelle) in der Excel-Standardsuche.
Wenn eine Zelle am Anfang oder Ende des Inhalts zusätzliche Leerzeichen enthält. In einer solchen Situation zerbrechen Sie sich möglicherweise lange den Kopf und versuchen zu verstehen, warum die Formel nicht funktioniert.

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

Nehmen wir an, Sie möchten einen bestimmten Kunden in der unten angezeigten Datenbank finden. Sie erinnern sich nicht an seinen Nachnamen, aber Sie wissen, dass er mit „ack“ beginnt. Diese Formel wird die Aufgabe perfekt erfüllen:

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

Nachdem Sie nun sicher sind, dass Sie den richtigen Namen gefunden haben, können Sie mit derselben Formel den von diesem Kunden gezahlten Betrag ermitteln. Ändern Sie dazu einfach das dritte Argument der Funktion SVERWEIS auf die Nummer der gewünschten Spalte. In unserem Fall ist dies Spalte C (3. im Bereich):

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

Hier sind einige weitere Beispiele mit Platzhaltern:

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

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

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

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

~ Suchen Sie den Vornamen in der Liste, bestehend aus 5 Zeichen:

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

Funktionieren SVERWEIS Funktioniert korrekt mit Platzhaltern, die Sie immer verwenden sollten FALSCH(LÜGE). Wenn der Suchbereich mehr als einen Wert enthält, der den Wildcard-Suchkriterien entspricht, wird der erste gefundene Wert zurückgegeben.

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

Schauen wir uns nun ein etwas komplexeres Beispiel für die Suche mit dieser Funktion an SVERWEIS nach Wert in einer Zelle. Stellen Sie sich vor, dass Spalte A eine Liste von Lizenzschlüsseln enthält und Spalte B eine Liste von Namen, denen die Lizenz gehört. Darüber hinaus befindet sich in Zelle C1 ein Teil (mehrere Zeichen) eines Lizenzschlüssels und Sie möchten den Namen des Eigentümers ermitteln.

Dies kann mit dieser Formel erfolgen:

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

Diese Formel sucht den Wert aus Zelle C1 in einem bestimmten Bereich und gibt den entsprechenden Wert aus Spalte B zurück. Beachten Sie, dass wir im ersten Argument das kaufmännische Und-Zeichen (&) vor und nach dem Zellbezug verwenden, um die Textzeichenfolge zuzuordnen.

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

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

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

Und zum Schluss schauen wir uns das letzte Argument, das für die Funktion angegeben wird, genauer an SVERWEIS – range_lookup(Zeitrafferansicht). 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 erzielen WAHR(Wahr oder FALSCH(LÜGE).

Lassen Sie uns zunächst herausfinden, was Microsoft Excel unter exakter und ungefährer Übereinstimmung versteht.

Wenn das Argument range_lookup FALSCH(FALSE), die Formel sucht nach einer genauen Übereinstimmung, d. h. genau der gleiche Wert wie im Argument angegeben Lookup-Wert(Suchwert). Wenn in der ersten Spalte des Bereichs t able_array(Tabelle) Es gibt zwei oder mehr Werte, die mit dem Argument übereinstimmen Lookup-Wert(search_value), dann wird der erste ausgewählt. Wenn keine Übereinstimmungen gefunden werden, meldet die Funktion einen Fehler #N / A(#N/A). Die folgende Formel meldet beispielsweise einen Fehler #N / A(#N/A), wenn im Bereich A2:A15 kein Wert vorhanden ist 4 :
VLOOKUP(4,A2:B15,2,FALSE)
=VLOOKUP(4,A2:B15,2,FALSE)
Wenn das Argument range_lookup(Zeitraffer) ist gleich WAHR(WAHR), die Formel sucht nach einer ungefähren Übereinstimmung. Genauer gesagt, zuerst die Funktion SVERWEIS sucht nach einer genauen Übereinstimmung und wählt eine ungefähre Übereinstimmung aus, wenn keine gefunden wird. Eine ungefähre Übereinstimmung ist der größte Wert, der den im Argument angegebenen Wert nicht überschreitet Lookup-Wert(Suchwert).

Wenn das Argument range_lookup(Zeitraffer) ist gleich WAHR(TRUE) oder nicht angegeben, dann müssen die Werte in der ersten Spalte des Bereichs in aufsteigender Reihenfolge, also vom kleinsten zum größten, sortiert werden. Ansonsten Funktion SVERWEIS kann ein fehlerhaftes Ergebnis zurückgeben.

Um die Bedeutung der Wahl besser zu verstehen WAHR(Wahr oder FALSCH(FALSCH), schauen wir uns noch ein paar Formeln mit der Funktion an SVERWEIS und schauen Sie sich die Ergebnisse an.

Beispiel 1: Mit VLOOKUP eine exakte Übereinstimmung finden

Wie Sie sich erinnern, ist zum Finden einer genauen Übereinstimmung das vierte Argument der Funktion erforderlich SVERWEIS sollte wichtig sein FALSCH(LÜGE).

Schauen wir uns noch einmal die Tabelle vom allerersten Beispiel an und finden wir heraus, welches Tier sich mit dieser Geschwindigkeit bewegen kann 50 Meilen pro Stunde. Ich glaube, dass Ihnen diese Formel keine Schwierigkeiten bereiten wird:

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

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

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

Wenn Sie die Funktion verwenden SVERWEIS um eine ungefähre Übereinstimmung zu finden, d. h. wenn Argument range_lookup(Zeitraffer) ist gleich WAHR(WAHR) oder fehlt, sollten Sie zunächst den Bereich nach der ersten Spalte in aufsteigender Reihenfolge sortieren.

Das ist wegen der Funktion sehr wichtig SVERWEIS gibt den nächstgrößten Wert nach dem angegebenen Wert zurück und die Suche stoppt. Wenn Sie es versäumen, richtig zu sortieren, erhalten Sie sehr seltsame Ergebnisse oder eine Fehlermeldung. #N / A(#N / A).

Jetzt können Sie eine der folgenden Formeln verwenden:

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

Wie Sie sehen, möchte ich herausfinden, welches Tier die Geschwindigkeit am nächsten hat 69 Meilen pro Stunde. Und das ist das Ergebnis, das mir die Funktion zurückgegeben hat SVERWEIS:

Wie Sie sehen, hat die Formel das Ergebnis zurückgegeben Antilope(Antilope), deren Geschwindigkeit 61 Meilen pro Stunde, obwohl die Liste auch enthält Gepard(Gepard), der schnell rennt 70 Meilen pro Stunde, und 70 ist eher 69 als 61, oder? Warum passiert das? Weil Funktion SVERWEIS Gibt bei der Suche nach einer ungefähren Übereinstimmung den größten Wert zurück, der nicht größer als der Suchwert ist.

Ich hoffe, dass diese Beispiele etwas Licht auf die Arbeit mit der Funktion werfen SVERWEIS in Excel, und Sie betrachten sie nicht mehr als Fremde. Jetzt kann es nicht schaden, die wichtigsten Punkte des untersuchten Materials kurz zu wiederholen, um es besser im Gedächtnis zu festigen.

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

Funktion SVERWEIS Excel kann nicht nach links schauen. Es sucht immer nach dem Wert in der Spalte ganz links des durch das Argument angegebenen Bereichs table_array(Tisch).
In Funktion SVERWEIS Bei allen Werten wird die Groß-/Kleinschreibung nicht beachtet, d. h. Klein- und Großbuchstaben sind gleichwertig.
Wenn der gesuchte Wert kleiner als der Mindestwert in der ersten Spalte des von Ihnen betrachteten Bereichs ist, wird die Funktion SVERWEIS wird einen Fehler melden #N / A(#N / A).
Wenn 3. Argument col_index_num(Spaltennummer) weniger 1 , Funktion SVERWEIS wird einen Fehler melden #WERT!(#WERT!). Wenn es größer als die Anzahl der Spalten im Bereich ist table_array(Tabelle) meldet die Funktion einen Fehler #REF!(#VERKNÜPFUNG!).
Verwenden Sie im Argument absolute Zellbezüge table_array(Tabelle), sodass beim Kopieren der Formel der korrekte Suchbereich erhalten bleibt. Versuchen Sie alternativ, benannte Bereiche oder Tabellen in Excel zu verwenden.
Denken Sie bei der Durchführung einer ungefähren Übereinstimmungssuche daran, dass die erste Spalte im durchsuchten Bereich in aufsteigender Reihenfolge sortiert werden muss.
Denken Sie abschließend an die Bedeutung des vierten Arguments. Nutzen Sie Werte WAHR(Wahr oder FALSCH(FALSCH) nachdenklich und Sie werden sich eine Menge Kopfschmerzen ersparen.

In den folgenden Artikeln unseres Tutorials zur Funktion SVERWEIS In Excel lernen wir fortgeschrittenere Beispiele wie die Durchführung verschiedener Berechnungen mit SVERWEIS, Extrahieren von Werten aus mehreren Spalten und anderen. Ich danke Ihnen für das Lesen dieses Tutorials und hoffe, Sie nächste Woche wiederzusehen!

Hallo Freunde. Wie oft müssen Sie in einer Excel-Tabelle nach einer Übereinstimmung für einen Wert suchen? Sie müssen beispielsweise die Adresse einer Person in einem Verzeichnis oder den Preis eines Produkts in einer Preisliste finden. Wenn solche Aufgaben auftreten, ist dieser Beitrag genau das Richtige für Sie!

Ich führe täglich ähnliche Eingriffe durch und ohne die unten beschriebenen Funktionen würde es mir wirklich schwer fallen. Beachten Sie sie und wenden Sie sie in Ihrer Arbeit an!

Suchen Sie in Excel-Tabellen sowie in den VLOOKUP- und GLOOKUP-Funktionen

Die Rolle dieser Funktionen im Leben eines normalen Benutzers kann kaum überschätzt werden. Jetzt können Sie ganz einfach einen passenden Eintrag in der Datentabelle finden und den entsprechenden Wert zurückgeben.

Die Syntax der VLOOKUP-Funktion lautet: =VLOOKUP( Lookup-Wert; Nachschlagwerk; Ausgabespaltennummer; [mapping_type]). Schauen wir uns die Argumente an:

Suchwert– der Wert, den wir suchen werden. Dies ist ein erforderliches Argument.
Suchtabelle– das Array von Zellen, in denen die Suche stattfinden soll. Die Spalte mit den gesuchten Werten muss die erste in diesem Array sein. Dies ist auch ein erforderliches Argument;
Spaltennummer, die angezeigt werden soll– die Seriennummer der Spalte (beginnend mit der ersten im Array), aus der die Funktion Daten anzeigt, wenn die erforderlichen Werte übereinstimmen. Erforderliches Argument;
Passender Typ– Wählen Sie „1“ (oder „TRUE“) für eine schwache Übereinstimmung, „0“ („FALSE“) für eine vollständige Übereinstimmung. Das Argument ist optional; wenn es weggelassen wird, wird eine Suche durchgeführt nicht strikte Übereinstimmung.

Mit SVERWEIS eine exakte Übereinstimmung finden

Schauen wir uns ein Beispiel dafür an, wie die VLOOKUP-Funktion funktioniert, wenn der Übereinstimmungstyp FALSE ist und nach einer genauen Übereinstimmung sucht. Das Array B5:E10 gibt das Anlagevermögen eines bestimmten Unternehmens, seinen Buchwert, seine Inventarnummer und seinen Standort an. Zelle B2 enthält den Namen, für den Sie die Inventarnummer in der Tabelle suchen und in Zelle C2 einfügen müssen.

SVERWEIS-Funktion in Excel

Schreiben wir die Formel: =VLOOKUP(B2,B5:E10,3,FALSE) .

Hier gibt das erste Argument an, dass Sie in der Tabelle nach dem Wert aus Zelle B2 suchen müssen, d. h. das Wort „Fax“. Das zweite Argument besagt, dass die zu durchsuchende Tabelle im Bereich B5:E10 liegt und Sie in der ersten Spalte nach dem Wort „Fax“ suchen müssen, d. h. im Array B5:B10. Das dritte Argument teilt dem Programm mit, dass das Ergebnis der Berechnung in der dritten Spalte des Arrays enthalten ist, d. h. D5:D10. Das vierte Argument ist FALSE, d.h. Es ist eine vollständige Übereinstimmung erforderlich.

Daher empfängt die Funktion die Zeichenfolge „Fax“ aus Zelle B2 und sucht sie im Array B5:B10 von oben nach unten. Sobald eine Übereinstimmung gefunden wird (Zeile 8), gibt die Funktion den entsprechenden Wert aus Spalte D zurück, d. h. D8-Inhalt. Das ist genau das, was wir brauchten, das Problem ist gelöst.

Wenn der gesuchte Wert nicht gefunden wird, gibt die Funktion zurück.

Mit VLOOKUP eine ungenaue Übereinstimmung finden

Dank dieser Option in VLOOKUP können wir komplexe Formeln vermeiden, um das gewünschte Ergebnis zu finden.

Array B5:C12 zeigt Zinssätze für Kredite in Abhängigkeit von der Kreditsumme. In Zelle B2 geben wir den Kreditbetrag an und möchten in C2 den Zinssatz für eine solche Transaktion erhalten. Die Aufgabe ist insofern schwierig, als die Menge beliebig sein kann und wahrscheinlich nicht mit den im Array angegebenen Werten übereinstimmt; die Suche nach einer genauen Übereinstimmung ist nicht geeignet:

Dann schreiben wir die Formel für eine nicht strikte Suche: =VLOOKUP(B2,B5:C12,2,TRUE). Nun sucht das Programm aus allen in der Spalte B angezeigten Daten nach dem nächstkleineren. Das heißt, für den Betrag von 8.000 wird der Wert 5.000 ausgewählt und der entsprechende Prozentsatz angezeigt.

Nicht strikte VLOOKUP-Suche in Excel

Damit die Funktion ordnungsgemäß funktioniert, müssen Sie die erste Spalte der Tabelle in aufsteigender Reihenfolge sortieren. Andernfalls kann es zu einem fehlerhaften Ergebnis kommen.

Die GLOOKUP-Funktion hat dieselbe Syntax wie VLOOKUP, sucht das Ergebnis jedoch in Zeilen und nicht in Spalten. Das heißt, es durchsucht Tabellen nicht von oben nach unten, sondern von links nach rechts und zeigt die angegebene Zeilennummer und nicht die Spalte an.

Suchen nach Daten mit der VIEW-Funktion

Die LOOKUP-Funktion funktioniert ähnlich wie VLOOKUP, hat jedoch eine andere Syntax. Ich verwende es, wenn die Datentabelle mehrere Dutzend Spalten enthält und um VLOOKUP zu verwenden, müssen Sie zusätzlich die Nummer der Ausgabespalte berechnen. In solchen Fällen erleichtert die VIEW-Funktion die Aufgabe. Und so die Syntax: =ANSICHT( Lookup-Wert; array_to_search; Array_to_display) :

Suchwert– Daten oder ein Link zu den zu durchsuchenden Daten;
Zu durchsuchendes Array– eine Zeile oder Spalte, in der wir nach einem ähnlichen Wert suchen. Wir müssen dieses Array in aufsteigender Reihenfolge sortieren;
Array zur Anzeige– ein Bereich, der Daten zur Anzeige von Ergebnissen enthält. Natürlich muss es die gleiche Größe wie das Sucharray haben.

Wenn Sie auf diese Weise schreiben, geben Sie einen nicht relativen Verweis auf das Ergebnisarray an. Und man zeigt direkt darauf, d.h. Es ist nicht erforderlich, zunächst die Nummer der Ausgabespalte zu berechnen. Wir verwenden die VIEW-Funktion im ersten Beispiel für die VLOOKUP-Funktion (Anlagevermögen, Inventarnummern): =ANSICHT(B2,B5:B10,D5:D10). Das Problem wurde erfolgreich gelöst!

VIEW-Funktion in Microsoft Excel

Suche nach relativen Koordinaten. MATCH- und INDEX-Funktionen

Eine andere Möglichkeit, nach Daten zu suchen, besteht darin, die Funktionen MATCH und INDEX zu kombinieren.

Der erste von ihnen wird verwendet, um nach einem Wert in einem Array zu suchen und seine Seriennummer zu erhalten: SEARCH( Lookup-Wert; angesehenes_array; [Passender Typ] ). Funktionsargumente:

Suchwert– erforderliches Argument
Array, das angezeigt werden soll– eine Zeile oder Spalte, in der wir nach einer Übereinstimmung suchen. Erforderliches Argument
Passender Typ– Geben Sie „0“ an, um nach einer genauen Übereinstimmung zu suchen, „1“ für die nächstkleinere Übereinstimmung und „-1“ für die nächstgrößere Übereinstimmung. Da die Funktion vom Anfang bis zum Ende der Liste sucht, sortieren Sie bei der Suche nach der nächstkleineren die Suchspalte in absteigender Reihenfolge. Und wenn Sie mehr suchen, sortieren Sie es in aufsteigender Reihenfolge.

Die Position des gesuchten Wertes wurde gefunden, nun können Sie ihn mit der Funktion auf dem Bildschirm anzeigen INDEX( Array; Zeilennummer; [Spaltennummer]) :

Array– Das Argument gibt an, aus welchem Zellenarray der Wert ausgewählt werden soll
Zeilennummer– Geben Sie die Seriennummer der Zeile an (beginnend mit der ersten Zelle des Arrays), die Sie anzeigen möchten. Hier können Sie den Wert manuell eingeben oder das Ergebnis einer anderen Funktionsberechnung verwenden. Zum Beispiel SUCHEN.
Spaltennummer– optionales Argument, angegeben, wenn das Array aus mehreren Spalten besteht. Wenn ein Argument weggelassen wird, verwendet die Formel die erste Spalte der Tabelle.

Nun kombinieren wir diese Funktionen, um das Ergebnis zu erhalten:

MATCH- und INDEX-Funktionen in Excel

Dies sind die Methoden zum Suchen und Anzeigen von Daten, die in Excel vorhanden sind. Darüber hinaus können Sie sie in Berechnungen verwenden, in Präsentationen verwenden, Operationen mit ihnen durchführen, sie als Argumente für andere Funktionen angeben usw.

Spüren Sie, wie Ihr Wissen und Ihre Fähigkeiten wachsen und sich stärken? Dann hören Sie nicht auf, sondern lesen Sie weiter! Im nächsten Beitrag schauen wir uns an: Es wird schwierig und interessant!