Excel Index Match vs. Vlookup - Formel Beispiele

Dieses Tutorial zeigt, wie Index- und Übereinstimmung Funktionen in Excel und warum es besser ist als Vlookup.

In jüngsten Artikeln bemühen wir uns, Anfängern die Grundlagen von Vlookup -Funktionen zu erläutern und komplexere Beispiele für Vlookup -Formeln für fortgeschrittene Benutzer anzugeben. Jetzt werde ich versuchen, Sie nicht nur zu überzeugen, Vlookup nicht zu verwenden, sondern zumindest eine Alternative zu vertikalen Lookups in Excel zu zeigen.

"Warum brauche ich das?" Sie könnten fragen. Da Vlookup viele Einschränkungen hat, kann es Sie daran hindern, in vielen Fällen das gewünschte Ergebnis zu erzielen. Andererseits ist die Index -Match -Kombination flexibler und bietet viele hervorragende Funktionen, die Vlookup in vielerlei Hinsicht überlegen machen.

Excel-Index und Übereinstimmungsfunktionen-Basics ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Da der Zweck dieses Tutorials darin besteht, zu zeigen, wie alternative vertikale Lookups in Excel durch Kombination von Index- und Übereinstimmungsfunktionen ausführt, werden wir ihre Syntax und Verwendung nicht im Detail diskutieren. Wir werden nur das Minimum decken, das erforderlich ist, um das übergeordnete Konzept zu verstehen, und dann in Formelbeispiele eintauchen, die alle Vorteile der Verwendung der Indexübereinstimmung anstelle von Vlookup aufzeigen.

Indexfunktionen - Syntax und Verwendung

Die Indexfunktion von Excel gibt einen Wert aus dem Array basierend auf den angegebenen Zeilen- und Spaltennummern zurück. Die Syntax der Indexfunktion ist sehr einfach:

Index (Array, row_num, [column_num]) Folgendes ist eine einfache Erklärung für jeden Parameter:

• Array - Zellenbereich, aus denen Sie den Wert zurückgeben möchten.
• row_num - Die Zeilennummer im Array, aus dem Sie den Wert zurückgeben möchten. Bei Auslassung ist column_num erforderlich.
• column_num - die Spaltennummer in dem Array, aus dem Sie den Wert zurückgeben möchten. Wenn es weggelassen wird, ist Row_num erforderlich.

Weitere Informationen finden Sie in Excel -Indexfunktionen.

Hier ist das einfachste Beispiel für die Indexformel:

=INDEX(A1:C10,2,3)

Diese Formel durchsucht die Zellen A1 nach C10 und gibt den Zellwert von Zeile 2 und Spalte 3 zurück, d. H. Zelle C2.

Sehr einfach, oder? Wenn Sie jedoch echte Daten verarbeiten, kennen Sie die gewünschten Zeilen- und Spaltennummern fast nie, wobei die Übereinstimmung der Übereinstimmung nützlich ist.

Übereinstimmungsfunktion - Syntax und Verwendung

Die Übereinstimmungsfunktion von Excel sucht nach einem Wert innerhalb eines Zellbereichs und gibt die relative Position des Wertes im Bereich zurück.

Die Syntax der Spielfunktion lautet wie folgt:

Match (Lookup_Value, Lookup_array, [match_type]) - Lookup_value - Die Nummer oder der Textwert, nach dem Sie suchen.

• Lookup_array - Bereich der Zellen, nach denen gesucht wird.
• MATCH_TYPE - Gibt an, ob ein exaktes Match oder das nächste Match zurückgegeben werden soll:
  • 1 oder weglassen (Standard) - ungefähre Übereinstimmung (als nächstes kleiner).
  • 0 - exakte Übereinstimmung. In der Index-/Match -Kombination benötigen Sie fast immer eine genaue Übereinstimmung, sodass Sie den dritten Parameter der Spielfunktion auf 0 festlegen.
  • -1 - ungefähre Übereinstimmung (nächstes größer).

Wenn beispielsweise Bereich B1: B3 die Werte "New York", "Paris", "London" enthält, gibt die folgende Formel die Nummer 3 zurück, da "London" der dritte Eintrag im Bereich ist:

=MATCH("London",B1:B3,0)

Weitere Informationen finden Sie in Excel Match -Funktionen.

Auf den ersten Blick scheint die Verwendung der Spielfunktion fraglich zu sein. Wen kümmert sich um die Position des Wertes im Bereich? Was wir wirklich wissen wollen, ist der Wert selbst.

Lassen Sie mich Sie daran erinnern, dass die relative Position des Suchwerts (d. H. Zeilen- und Spaltennummer) genau das ist, was Sie den Parametern row_num und column_num zur Indexfunktion angeben müssen. Sie erinnern sich, dass der Excel -Index den Wert an der Schnittstelle einer bestimmten Zeile und Spalte finden kann, aber die gewünschte Zeile und Spalte nicht bestimmen kann.

So verwenden Sie die Indexübereinstimmung in Excel

Nachdem Sie die Grundlagen gelernt haben, glaube ich, dass Sie begonnen haben, zu verstehen, wie übereinstimmt und der Index zusammenarbeiten. Kurz gesagt, Index findet Werte durch Spalten- und Zeilennummern -Lookups, während die Übereinstimmung diese Zahlen bereitstellt. Das war's!

Für die vertikale Suche verwenden Sie nur die Übereinstimmungsfunktion, um die Zeilennummer zu bestimmen und direkt den Spaltenbereich für den Index anzugeben:

Index ( Spalte, aus der der Wert zurückgegeben werden soll , übereinstimmt ( Spalte zu finden , 0)) ist immer noch schwer zu verstehen? Es kann einfacher sein, anhand eines Beispiels zu verstehen. Angenommen, Sie haben eine Liste nationaler Hauptstädte und deren Bevölkerungsgruppen:

Verwenden Sie die folgende Index -Match -Formel, um die Bevölkerung eines Kapitals wie die Hauptstadt Japan zu ermitteln:

=INDEX(C2:C10, MATCH("Japan", A2:A10, 0))

Lassen Sie uns nun analysieren, was jede Komponente dieser Formel tatsächlich tut:

• Die Spielfunktion durchsucht den Suchwert "Japan" in Bereich A2: A10 und gibt die Nummer 3 zurück, da "Japan" der dritte im Sucharray ist.
• Die Zeilennummer wird direkt an den Indexparameter row_num übergeben, wodurch er einen Wert aus der Zeile zurücksendet.

Daher wird die obige Formel zu einem einfachen Index (C2: C, 3), was bedeutet, in den Zellen C2 bis C10 zu suchen und den Wert aus der dritten Zelle dieses Bereichs, d. H. C4, zu extrahieren, weil wir aus der zweiten Reihe beginnen.

Willst du keine Städte in Formeln hardcode? Geben Sie es in eine bestimmte Zelle wie F1 ein, um die Zellreferenz zu entsprechen, und Sie erhalten eine dynamische Lookup -Formel:

=INDEX(C2:C10, MATCH(F1,A2:A10,0))

Wichtiger Tipp! Die Anzahl der Zeilen im Array -Parameter des Index sollte mit der Anzahl der Zeilen im Parameter SOOKUP_Array der Übereinstimmung übereinstimmen, andernfalls erzeugt die Formel ein Fehlerergebnis.

Warten Sie ... warum verwenden wir nicht einfach die folgende Vlookup -Formel? Was bringt es, Zeit zu verschwenden, um das Geheimnis des Excel -Match -Index herauszufinden?

=VLOOKUP(F1, A2:C10, 3, FALSE)

In diesem Fall macht es überhaupt keinen Sinn :) Dieses einfache Beispiel dient dem Demonstrationszwecken nur, damit Sie verstehen, wie der Index und die Übereinstimmung Funktionen zusammenarbeiten. Das folgende Beispiel zeigt Ihnen die wahre Kraft dieser Kombination, die sich leicht mit vielen komplexen Szenarien befassen kann, und Vlookup wird Schwierigkeiten haben.

Hinweis:

• In Excel 365 und Excel 2021 können Sie die modernere Index -XMatch -Formel verwenden.
• Für Google Sheets finden Sie in diesem Artikel das Beispiel für die Indexübereinstimmung.

Vergleich zwischen Index Match und Vlookup

Bei der Entscheidung, welche Funktion für die vertikale Suche verwendet werden soll, sind die meisten Excel -Experten der Ansicht, dass diese Indexübereinstimmung Vlookup weit überlegen ist. Viele Menschen halten sich jedoch immer noch an Vlookup, weil es einfacher ist, zweitens, weil sie nicht alle Vorteile der Verwendung der Index -Match -Formel in Excel vollständig verstehen. Ohne dieses Verständnis möchte niemand Zeit damit verbringen, komplexere Grammatiken zu lernen.

Im Folgenden werde ich auf die Hauptvorteile des Match -Index gegenüber Vlookup hinweisen.

4 Hauptgründe für die Verwendung der Indexübereinstimmung anstelle von Vlookup

1. Schauen Sie von rechts nach links nach. Jeder erfahrene Benutzer weiß, dass Vlookup nicht nach links schauen kann, was bedeutet, dass Ihr Suchwert immer in der Spalte der Tabelle links liegen sollte. Die Indexübereinstimmung kann leicht links suchen! Das folgende Beispiel zeigt, wie es tatsächlich funktioniert: So finden Sie den Wert links in Excel.
2. Spalten sicher einfügen oder löschen. Wenn eine neue Spalte aus einer Lookup -Tabelle gelöscht oder hinzugefügt wird, kann die VLOOKUP -Formel beschädigt oder falsche Ergebnisse liefern, da die Syntax von VLOOKUP die Indexnummer der Spalte angeben muss, aus der Sie Daten extrahieren möchten. Wenn Sie Spalten hinzufügen oder löschen, ändert sich die Indexnummer natürlich. Mit der Indexübereinstimmung geben Sie den Spaltenbereich an, nicht die Indexnummer an. Daher können Sie eine beliebige Anzahl von Spalten einfügen und löschen, ohne sich Sorgen darüber zu machen, dass Sie jede verwandte Formel aktualisieren.
3. Die Größe des Suchwerts gibt keine Begrenzung. Bei Verwendung der Vlookup -Funktion darf die Gesamtlänge Ihrer Suchbedingung 255 Zeichen nicht überschreiten, andernfalls erhalten Sie einen #wert! Fehler. Wenn Ihr Datensatz also lange Zeichenfolgen enthält, ist die Indexübereinstimmung die einzige praktikable Lösung.
4. Höhere Verarbeitungsgeschwindigkeit. Wenn Ihre Tabelle relativ gering ist, gibt es wenig signifikante Unterschiede in der Excel -Leistung. Wenn Ihr Blatt jedoch Hunderte oder Tausende von Zeilen und entsprechende Hunderte oder Tausende von Formeln enthält, läuft der Match -Index schneller als Vlookup, da Excel nur die Suche und Rückgabe von Spalten abwickelt, nicht die gesamte Tabellenarchitektur. Wenn Ihre Arbeitsmappe komplexe Array -Formeln wie VLOOKUP und SUM enthält, kann der Einfluss von Vlookup auf die Excel -Leistung besonders auffällig sein. Der Schlüssel besteht darin, jeden Wert im Array zu überprüfen, um die Vlookup -Funktion separat aufzurufen. Je mehr Werte Ihr Array enthält und je mehr Array -Formeln in Ihrer Arbeitsmappe enthalten sind, desto langsamer wird Excel durchführen.

Um die Nuancen zwischen Index Match und Xlookup zu verstehen, untersuchen Sie die eingehende Analyse in diesem Handbuch: Excel Xlookup und Index Match.

Excel Index Match - Formel Beispiel

Wenn Sie die Gründe für das Erlernen der Übereinstimmungsindexfunktion verstehen, gehen wir zum interessantesten Teil und sehen Sie, wie theoretisches Wissen auf die Praxis angewendet werden kann.

Index -Match -Formeln von rechts nach links gefunden

Wie bereits erwähnt, kann Vlookup nicht links nachschlagen. Wenn Ihr Suchwert nicht in der Spalte links liegt, ist es für die Vlookup -Formel fast unmöglich, Ihnen das gewünschte Ergebnis zu erzielen. Die Index -Match -Funktion in Excel ist vielseitiger und kümmert sich nicht wirklich darum, Spaltenpositionen zu finden und zurückzugeben.

In diesem Beispiel werden wir links von der Beispieltabelle eine Rangspalte hinzufügen und versuchen, die Bevölkerungsrangliste von Moskau, der Hauptstadt Russlands, herauszufinden.

Geben Sie den Suchwert in G1 ein, suchen Sie in C2: C10 unter Verwendung der folgenden Formel und geben Sie den entsprechenden Wert von A2: A10 zurück:

=INDEX(A2:A10,MATCH(G1,C2:C10,0))

Hinweis. Wenn Sie vorhaben, die Index -Match -Formel für mehrere Zellen zu verwenden, sollten Sie die beiden Bereiche mit absoluten Zellreferenzen wie $ 2: $ A $ 10 und $ C $ 2: $ C $ 10 sperren, um beim Kopieren der Formel eine Verformung zu vermeiden.

Finden Sie in Zeilen und Spalten mithilfe der Index Match

Im obigen Beispiel verwenden wir die Index -Match als Ersatz für klassisches VLOOKUP, wobei die Werte aus vordefinierten Einzelspaltenbereichen zurückgegeben werden. Aber was ist, wenn Sie in mehreren Zeilen und Spalten nachschlagen müssen? Mit anderen Worten, was ist, wenn Sie eine Matrix oder eine Zwei-Wege -Suche ausführen möchten?

Dies mag schwierig klingen, aber die Formel ist der grundlegenden Excel -Indexübereinstimmung mit nur einem Unterschied sehr ähnlich. Ratet mal, was es ist?

Einfach ausgedrückt, verwenden Sie zwei Übereinstimmungsfunktionen - eine, um die Zeilennummer und die andere zu erhalten, um die Spaltennummer zu erhalten. Ich gratuliere denen, die es richtig erraten haben :)

Index (Array, Match ( Vlookup -Wert , Spalte, um sich gegen 0), Match ( Hlookup -Wert , Zeile zum Überblick , 0)) nun eine Index -Match -Match -Match -Formel aufbauen, um die Bevölkerung eines bestimmten Landes in einem bestimmten Jahr (in Millionen) zu ermitteln.

Geben Sie das Zielland (Vlookup -Wert) in G1 ein und geben Sie das Zieljahr (Hlookup -Wert) in G2 ein. Die Formel lautet wie folgt:

=INDEX(B2:D11, MATCH(G1,A2:A11,0), MATCH(G2,B1:D1,0))

Wie diese Formel funktioniert

Wenn Sie eine komplexe Excel -Formel verstehen müssen, zeugen Sie sie in kleinere Teile und sehen Sie, was jede einzelne Funktion tut:

MATCH(G1,A2:A11,0) - Suche nach dem Wert ("China") in Zelle G1 in A2: A11 und geben seine Position zurück, d. H. 2.

MATCH(G2,B1:D1,0)) - Suche in B1: D1, um die Position des Medianwerts der G2 -Zelle ("2015") zu erhalten, d. H. 3.

Die obigen Zeile und die Spaltenzahlen werden an die entsprechenden Parameter der Indexfunktion übergeben:

INDEX(B2:D11, 2, 3)

Infolgedessen erhalten Sie einen Wert an der Kreuzung von Zeile 2 und Spalte 3 im Bereich B2: D11, d. H. Der Wert in Zelle D3. Ist es einfach? Ja!

Excel Index Match finden Sie mehrere Bedingungen

Wenn Sie die Möglichkeit haben, unser Excel Vlookup -Tutorial zu lesen, haben Sie möglicherweise eine Vlookup -Formel mit mehreren Bedingungen getestet. Eine signifikante Einschränkung dieser Methode ist jedoch die Notwendigkeit, eine Helfersäule hinzuzufügen. Die gute Nachricht ist, dass die Indexübereinstimmung von Excel auch nach zwei oder mehr Bedingungen suchen kann, ohne die Quelldaten zu ändern oder neu zu organisieren!

Hier ist eine allgemeine Indexübereinstimmung mit mehreren Bedingungen:

Oder​​​​ Dies ist eine Array -Formel, die mit der Strg Shift Enter -Verknüpfung erfolgen muss.

Nehmen wir in der folgenden Beispieltabelle an, dass Sie den Betrag basierend auf zwei Bedingungen, Kunden und Produkten finden möchten.

Die folgende Index -Match -Formel funktioniert sehr gut:

=INDEX(C2:C10, MATCH(1, (F1=A2:A10) * (F2=B2:B10), 0))

Wenn C2: C10 der Bereich ist, aus dem der Wert zurückgegeben werden soll, ist F1 Kriterien1, A2: A10 ist der Bereich im Vergleich zu Kriterien1, F2 ist Kriterien2 und B2: B10 ist der Bereich im Vergleich zu den Kriterien2.

Denken Sie daran, die Formel korrekt einzugeben, indem Excel die Formel automatisch in lockige Klammern drückt, wie im Screenshot gezeigt:

Wenn Sie in Ihrem Arbeitsblatt lieber keine Array -Formel verwenden möchten, fügen Sie der Formel eine weitere Indexfunktion hinzu und tun Sie dies mit der üblichen Eingabetaste:

Wie diese Formeln funktionieren

Diese Formeln verwenden dieselbe Methode wie die grundlegende Indexübereinstimmungsfunktion und schauen durch eine einzelne Spalte. Um mehrere Bedingungen zu bewerten, erstellen Sie zwei oder mehr Arrays von übereinstimmenden und nicht anpassen tatsächlichen und falschen Werten, die jede einzelne Bedingung darstellen, und multiplizieren Sie dann die entsprechenden Elemente dieser Arrays. Der Multiplikationsvorgang wandelt True und FALSE auf 1 und 0 um und generiert ein Array, in dem 1 Zeilen entspricht, die alle Bedingungen erfüllen. Die Match -Funktion sucht nach dem ersten "1" im Array, um den Wert 1 zu finden, und übergibt seine Position an den Index, wodurch der Wert in dieser Zeile aus der angegebenen Spalte zurückgegeben wird.

Nicht-Array-Formeln stützen sich auf die native Fähigkeit der Indexfunktion, Arrays zu verarbeiten. Der zweite Index ist mit 0 row_num konfiguriert, sodass das gesamte Array von Spalten übergeht.

Dies ist eine hochrangige Erklärung der Formellogik. Ausführliche Informationen finden Sie unter Excel Index übereinstimmen mit mehreren Bedingungen.

Excel Index Match und Durchschnitt, max, min

Microsoft Excel hat spezielle Funktionen, um die minimalen, maximalen und Durchschnittswerte im Bereich zu ermitteln. Aber was ist, wenn Sie den Wert aus einer anderen Zelle erhalten müssen, die diesen Werten zugeordnet ist? Verwenden Sie in diesem Fall die maximale, min oder die durchschnittliche Funktion in Verbindung mit der Indexübereinstimmung.

Indexübereinstimmung kombiniert mit max

Um den Maximalwert in Spalte D zu finden und einen Wert aus Spalte C in derselben Zeile zurückzugeben, verwenden Sie diese Formel:

=INDEX(C2:C10, MATCH(MAX(D2:D10), D2:D10, 0))

Indexübereinstimmung kombiniert mit min

Verwenden Sie die folgende Formel, um den Mindestwert in Spalte D zu lokalisieren und den zugehörigen Wert aus Spalte C zu extrahieren:

=INDEX(C2:C10, MATCH(MIN(D2:D10), D2:D10, 0))

Indexübereinstimmung kombiniert mit dem Durchschnitt

Verwenden Sie die folgende Formel:

=INDEX(C2:C10, MATCH(AVERAGE(D2:D10), D2:D10, -1 ))

Geben Sie je nachdem, wie Ihre Daten organisiert sind, 1 oder -1 für den dritten Parameter der Match -Funktion (match_type):

• Wenn Ihre Suchspalte (in unserer Fallspalte d) in aufsteigender Reihenfolge sortiert ist, geben Sie 1 ein. Die Formel berechnet den Maximalwert weniger oder gleich dem Durchschnittswert.
• Wenn Ihre Suchspalten in absteigender Reihenfolge sortiert sind, geben Sie -1 ein. Die Formel berechnet den minimalen Wert, der mehr als oder gleich dem Durchschnitt ist.
• Wenn Ihr Lookup -Array einen Wert enthält , der nur dem Durchschnitt entspricht , können Sie 0 für eine genaue Übereinstimmung eingeben. Es ist keine Sortierung erforderlich.

In unserem Beispiel ist die Bevölkerung in Spalte D in absteigender Reihenfolge sortiert, sodass wir -1 als Matching -Typ verwenden. Infolgedessen bekommen wir "Tokio", weil seine Bevölkerung (13.189.000) die am nächsten gelegene Übereinstimmung mit einem überdurchschnittlichen Spielraum (12.269.006) am nächsten steigt.

Sie sind vielleicht neugierig, dass Vlookup auch solche Berechnungen durchführen kann, aber als Array -Formeln: Vlookup vs. Durchschnitt, max, min.

Verwenden der Indexübereinstimmung in Kombination mit IFNA/IFERROR

Wie Sie vielleicht bemerkt haben, erzeugt die Index -Übereinstimmung der Formel in Excel den Suchwert nicht, einen Fehler #n/a. Wenn Sie das Standardfehlersymbol durch etwas Sinnvolleres ersetzen möchten, wickeln Sie Ihre Indexübereinstimmung in einer IFNA -Funktion ein. Zum Beispiel:

=IFNA(INDEX(C2:C10, MATCH(F1,A2:A10,0)), "No match is found")

Wenn nun jemand einen Wert eingibt, der in der Nachschlagtabelle nicht vorhanden ist, informiert die Formel den Benutzer ausdrücklich darüber, dass keine Übereinstimmung gefunden wurde:

Wenn Sie alle Fehler fangen möchten, verwenden Sie nicht nur #N/A die IFerror -Funktion anstelle von IFNA:

=IFERROR(INDEX(C2:C10, MATCH(F1,A2:A10,0)), "Oops, something went wrong!")

Denken Sie daran, dass es in vielen Fällen unklug sein kann, alle Fehler zu maskieren, da sie Sie auf mögliche Fehler in der Formel aufmerksam machen.

So verwenden Sie Index und Übereinstimmung in Excel. Ich hoffe, unsere Beispiele für Formel werden Ihnen helfen und freuen sich darauf, Sie nächste Woche in unserem Blog zu sehen!

Download für Übung Arbeitsbuch

Excel Index -Match -Beispiel (.xlsx -Datei)

Das obige ist der detaillierte Inhalt vonExcel Index Match vs. Vlookup - Formel Beispiele. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!