Excel -Match -Funktion mit Formel -Beispielen

In diesem Tutorial wird erläutert, wie die Übereinstimmungsfunktion in Excel mit Formel -Beispielen verwendet wird. Es zeigt auch, wie Sie Ihre Lookup -Formeln verbessern können, indem Sie eine dynamische Formel mit Vlookup und Übereinstimmung erstellen.

In Microsoft Excel gibt es viele verschiedene Such-/Referenzfunktionen, mit denen Sie einen bestimmten Wert in einer Reihe von Zellen finden können, und die Übereinstimmung ist eines davon. Grundsätzlich identifiziert es eine relative Position eines Elements in einer Reihe von Zellen. Die Match -Funktion kann jedoch viel mehr tun als ihre reine Essenz.

• Excel -Match -Funktion - Syntax und grundlegende Verwendungen
• So verwenden Sie Übereinstimmungen in Excel - Formel Beispielen
  • Passen Sie die Formel mit Wildcards an
  • Fallempfindliche Übereinstimmungsformel
  • Vergleichen Sie 2 Spalten für Übereinstimmungen und Unterschiede (ISNA Match)
  • Vlookup und Match
  • Hlookup und Match

Excel -Match -Funktion - Syntax und verwendet

Die Übereinstimmungsfunktion in Excel sucht nach einem bestimmten Wert in einem Zellbereich und gibt die relative Position dieses Wertes zurück.

Die Syntax für die Spielfunktion lautet wie folgt:

Match (Lookup_Value, Lookup_array, [Match_type])

Lookup_Value (Erforderlich) - Der Wert, den Sie finden möchten. Es kann ein numerischer, Text oder einen logischen Wert sowie eine Zellreferenz sein.

Lookup_Array (Erforderlich) - Der Bereich der Zellen, die Sie suchen müssen.

Match_type (optional) - Definiert den Match -Typ. Es kann einer dieser Werte sein: 1, 0, -1. Das auf 0 festgelegte Match_type -Argument gibt nur die genaue Übereinstimmung zurück, während die beiden anderen Typen eine ungefähre Übereinstimmung ermöglichen.

• 1 oder ausgelassen (Standard) - ungefähre Übereinstimmung (am nächsten kleiner). Findet den größten Wert in dem Sucharray, das weniger als oder gleich dem Lookup -Wert ist. Erfordert das Sortieren des Lookup -Arrays in aufsteigender Reihenfolge, vom kleinsten bis zum größten oder von a bis z.
• 0 - exakte Übereinstimmung. Findet den ersten Wert im Array, der genau dem Suchwert entspricht. Es ist keine Sortierung erforderlich.
• -1 - ungefähre Match (am nächsten größer). Findet den kleinsten Wert, der größer oder gleich dem Lookup -Wert ist. Das Lookup -Array sollte in absteigender Reihenfolge sortiert werden, von größten bis kleinsten oder von Z bis A.

Um die Übereinstimmungsfunktion besser zu verstehen, machen wir eine einfache Formel basierend auf diesen Daten: Schülernamen in Spalte A und deren Prüfungswerte in Spalte B, sortiert vom größten zu kleinsten. Um herauszufinden, wo unter anderem ein bestimmter Schüler (z. B. Laura ) steht, verwenden Sie diese einfache Formel:

=MATCH("Laura", A2:A8, 0)

Optional können Sie den Lookup -Wert in einer Zelle (E1 in diesem Beispiel) und Verweis auf diese Zelle in Ihrer Excel -Übereinstimmung einfügen:

=MATCH(E1, A2:A8, 0)

Wie Sie im obigen Screenshot sehen, werden die Schülernamen in einer willkürlichen Reihenfolge eingegeben, und deshalb setzen wir das Argument für match_type auf 0 (exakte Übereinstimmung), da nur für diesen Match -Typ keine Sortierwerte im Such -Array sortiert werden müssen. Technisch gesehen gibt die Match -Formel die relative Position von Laura im Bereich zurück. Aber weil die Punktzahlen vom größten bis kleinsten sortiert sind, sagt es uns auch, dass Laura unter allen Studenten die ^5. beste Punktzahl hat.

Tipp. In Excel 365 und Excel 2021 können Sie die XMatch -Funktion verwenden, die ein moderner und leistungsstärkerer Spielerfolger ist.

4 Dinge, die Sie über die Match -Funktion wissen sollten

Wie Sie gerade gesehen haben, ist die Verwendung von Match in Excel einfach. Wie bei nahezu jeder anderen Funktion gibt es jedoch einige Spezifitäten, die Sie kennen sollten:

1. Die Übereinstimmungsfunktion gibt die relative Position des Suchwerts im Array zurück, nicht den Wert selbst.
2. Die Übereinstimmung ist von Fall unempfindlich , was bedeutet, dass sie beim Umgang mit Textwerten nicht zwischen Kleinbuchstaben und Großbuchstaben unterscheidet.
3. Wenn das Lookup -Array mehrere Vorkommen des Suchwerts enthält, wird die Position des ersten Werts zurückgegeben.
4. Wenn der Suchwert nicht im Lookup -Array gefunden wird, wird der Fehler #N/A -Fehler zurückgegeben.

So verwenden Sie Übereinstimmungen in Excel - Formel Beispielen

Nachdem Sie die grundlegenden Verwendungen der Excel -Match -Funktion kennen, lassen Sie uns einige weitere Formel -Beispiele diskutieren, die über die Grundlagen hinausgehen.

Teilweise Übereinstimmung mit Wildcards

Wie viele andere Funktionen versteht Match die folgenden Wildcard -Charaktere:

• Fragezeichen (?) - Ersetzt ein einzelnes Zeichen
• Sternchen (*) - Ersetzt jede Sequenz von Zeichen

Notiz. Wildcards können nur in Match -Formeln mit match_type auf 0 verwendet werden.

Eine Match -Formel mit Wildcards ist in Situationen nützlich, wenn Sie nicht mit der gesamten Textzeichenfolge übereinstimmen möchten, sondern nur einige Zeichen oder ein Teil der Zeichenfolge. Um den Punkt zu veranschaulichen, betrachten Sie das folgende Beispiel.

Angenommen, Sie haben eine Liste regionaler Wiederverkäufer und ihre Verkaufszahlen für den letzten Monat. Sie möchten in der Liste eine relative Position eines bestimmten Wiederverkäufers finden (sortiert nach den Verkaufsbeträgen in absteigender Reihenfolge), können sich jedoch nicht genau an seinen Namen erinnern, obwohl Sie sich an einige erste Charaktere erinnern.

Unter der Annahme, dass die Wiederverkäufer -Namen im Bereich A2: A11 liegen und Sie nach dem Namen suchen, der mit "Auto" beginnt, lautet die Formel wie folgt:

=MATCH("car*", A2:A11,0)

Um unsere Match -Formel vielseitiger zu gestalten, können Sie den Lookup -Wert in einer Zelle (E1 in diesem Beispiel) eingeben und diese Zelle mit dem Wildcard -Charakter wie folgt verkettet:

=MATCH(E1&"*", A2:A11,0)

Wie im folgenden Screenshot gezeigt, gibt die Formel 2 zurück, was die Position von "Carter" ist:

Verwenden Sie das "?" Wildcard -Operator wie diesen:

=MATCH("ba?er", A2:A11,0)

Die obige Formel stimmt mit dem Namen " Baker " überein und leistet die relative Position, die 5 ist.

Fallempfindliche Übereinstimmungsformel

Wie zu Beginn dieses Tutorials erwähnt, unterscheidet die Spielfunktion Groß- und Kleinbuchstaben nicht. Verwenden Sie die Übereinstimmung in Kombination mit der genauen Funktion, die Zellen genau vergleicht, einschließlich des Zeichenfalles, um eine Fall-sensitive Übereinstimmung zu erstellen.

Hier ist die generische Fall-sensitive Formel, die Daten entspricht:

Übereinstimmung (true, exakt ( Lookup -Array , Lookup -Wert ), 0)

Die Formel funktioniert mit der folgenden Logik:

• Die genaue Funktion vergleicht den Lookup -Wert mit jedem Element des Lookup -Arrays. Wenn die verglichenen Zellen genau gleich sind, gibt die Funktion wahr, sonst falsch.
• Und dann vergleicht die Match -Funktion True (das ist deren Lookup_Value ) mit jedem Wert im Array, der exakt zurückgegeben wird, und gibt die Position des ersten Spiels zurück.

Bitte beachten Sie, dass es sich um eine Array -Formel handelt, die die korrekte Fertigstellung von Strg -Schalteingängen erfordert.

Unter der Annahme, dass Ihr Lookup -Wert in Zelle E1 ist und das Lookup -Array A2: A9 ist, ist die Formel wie folgt:

=MATCH(TRUE, EXACT(A2:A9,E1),0)

Der folgende Screenshot zeigt die Fall-sensitive Match-Formel in Excel:

Vergleichen Sie 2 Spalten für Übereinstimmungen und Unterschiede (ISNA Match)

Die Überprüfung von zwei Listen für Übereinstimmungen und Unterschiede ist eine der häufigsten Aufgaben in Excel und kann auf verschiedene Weise erfolgen. Eine ISNA/Match -Formel ist eine von ihnen:

If (ISNA (Übereinstimmung ( 1. Wert in List1 , List2 , 0)), "nicht in Liste 1", "")

Für einen Wert von Liste 2, der in Liste 1 nicht vorhanden ist, gibt die Formel " nicht in Liste 1 " zurück. Und hier ist: wie:

• Die Übereinstimmungsfunktion sucht nach einem Wert aus Liste 1 in Liste 2. Wenn ein Wert gefunden wird, gibt er seine relative Position zurück, #N/A -Fehler ansonsten.
• Die ISNA -Funktion in Excel macht nur eine Sache - prüft #N/A -Fehler (was "nicht verfügbar" bedeutet). Wenn ein gegebener Wert ein #N/A -Fehler ist, gibt die Funktion true zurück, sonst falsch. In unserem Fall bedeutet True, dass ein Wert aus Liste 1 in Liste 2 nicht gefunden wird (dh ein Fehler #n/ein Fehler wird durch Übereinstimmung zurückgegeben).
• Da es für Ihre Benutzer sehr verwirrend sein kann, für Werte, die nicht in Liste 1 angezeigt werden, die TRUE zu sehen, die Funktionen um ISNA um einwickeln, um " nicht in Liste 1 " anzuzeigen, oder welchen Text Sie auch immer, was Sie möchten.

Um beispielsweise die Werte in Spalte B mit den Werten in Spalte A zu vergleichen, nimmt die Formel die folgende Form (wobei B2 die oberste Zelle ist):

=IF(ISNA(MATCH(B2,A:A,0)), "Not in List 1", "")

Wie Sie sich erinnern, ist die Übereinstimmung in Excel für sich selbst unempfindlich. Um den Charakterfall zu unterscheiden, einbetten Sie die genaue Funktion in das Argument Lookup_array ein und denken Sie daran, die Strg -Eingabetaste zu drücken, um diese Array -Formel zu vervollständigen:

=IF(ISNA(MATCH(TRUE, EXACT(A:A, B2),0)), "Not in List 1", "")

Der folgende Screenshot zeigt beide Formeln in Aktion:

Um andere Möglichkeiten zum Vergleichen von zwei Listen in Excel zu erfahren, finden Sie im folgenden Tutorial: So vergleichen Sie 2 Spalten in Excel.

Excel Vlookup und Match

In diesem Beispiel wird davon ausgegangen, dass Sie bereits über grundlegende Kenntnisse der Excel Vlookup -Funktion verfügen. Und wenn Sie dies tun, können Sie die zahlreichen Einschränkungen (die detaillierte Übersicht darüber, welche, warum Excel Vlookup nicht funktioniert) und nach einer robusteren Alternative suchen.

Einer der ärgerlichsten Nachteile von Vlookup ist, dass er nach dem Einsetzen oder Löschen einer Spalte in einer Nachschlagtabelle aufhört, zu arbeiten. Dies geschieht, weil Vlookup einen Übereinstimmungswert basierend auf der Anzahl der von Ihnen angegebenen Rückgabespalte (Indexnummer) bezieht. Da die Indexnummer in der Formel "hart codiert" ist, kann Excel sie nicht anpassen, wenn eine neue Spalte (n) zu der Tabelle hinzugefügt oder gelöscht wird.

Die Excel -Match -Funktion befasst sich mit einer relativen Position eines Suchwerts, wodurch es perfekt für das Argument von vlookup col_index_num passt. Mit anderen Worten, anstatt die Rücksendespalte als statische Nummer anzugeben, verwenden Sie die Übereinstimmung, um die aktuelle Position dieser Spalte zu erhalten.

Um die Dinge zu verstehen, nutzen wir die Tabelle mit den Prüfungswerten der Schüler erneut (ähnlich wie die, die wir zu Beginn dieses Tutorials verwendet haben), aber diesmal werden wir die wirkliche Punktzahl und nicht die relative Position abrufen.

Unter der Annahme, dass der Suchwert in der Zelle F1 liegt, kostet das Tabellenarray $ 1: $ C $ 2 (es ist eine gute Praxis, es mit absoluten Zellreferenzen zu sperren, wenn Sie vorhaben, die Formel in andere Zellen zu kopieren), lautet die Formel wie folgt:

=VLOOKUP(F1, $A$1:$C$8, 3, FALSE)

Das 3 ^-RD -Argument ( col_index_num ) ist auf 3 gesetzt, da die Mathematikbewertung , die wir ziehen möchten, die 3 ^-RD -Spalte in der Tabelle ist. Wie Sie im Screenshot unten sehen können, funktioniert diese reguläre Vlookup -Formel gut:

Aber nur, bis Sie eine Spalte einfügen oder löschen):

Also, warum der #Ref! Fehler? Da Col_index_num auf 3 eingestellt ist, gibt Excel an, einen Wert aus der dritten Spalte zu erhalten, wohingegen jetzt nur 2 Spalten im Tabellenarray enthalten sind.

Um zu verhindern, dass solche Dinge geschehen, können Sie Ihre Vlookup -Formel dynamischer gestalten, indem Sie die folgende Übereinstimmungsfunktion aufnehmen:

MATCH(E2,A1:C1,0)

Wo:

• E2 ist der Suchwert, der genau dem Namen der Rückgabespalte entspricht , dh die Spalte, aus der Sie einen Wert ziehen möchten ( Math -Score in diesem Beispiel).
• A1: C1 ist das Lookup -Array, das die Tischkopfzeile enthält.

Fügen Sie nun diese Übereinstimmungsfunktion in das Argument Ihrer Vlookup -Formel wie folgt auf:

=VLOOKUP(F1,$A$1:$C$8, MATCH(E2,$A$1:$C$1, 0), FALSE)

Und stellen Sie sicher, dass es tadellos funktioniert, egal wie viele Spalten Sie hinzufügen oder löschen:

Im obigen Screenshot habe ich alle Zellreferenzen gesperrt, damit die Formel korrekt funktioniert, auch wenn meine Benutzer sie an einen anderen Ort im Arbeitsblatt verschieben. Wenn Sie im folgenden Screenshot sehen können, funktioniert die Formel nach dem Löschen einer Spalte gut. Darüber hinaus ist Excel intelligent genug, um die absoluten Referenzen in diesem Fall richtig anzupassen:

Excel Hlookup und Match

In ähnlicher Weise können Sie die Excel -Match -Funktion verwenden, um Ihre Hlookup -Formeln zu verbessern. Das allgemeine Prinzip ist im Wesentlichen das gleiche wie bei Vlookup: Sie verwenden die Übereinstimmungsfunktion, um die relative Position der Rückkehrspalte zu erhalten und diese Nummer dem Argument row_index_num Ihrer Hlookup -Formel zu liefern.

Angesichts der Suchwert in Zelle B5 ist das Tabellenarray B1: H3, der Name der Rückgabezeile (Suchwert für die Übereinstimmung) in Zelle A6 und die Zeilenheader A1: A3, die vollständige Formel lautet wie folgt:

=HLOOKUP(B5, B1:H3, MATCH(A6, A1:A3, 0), FALSE)

Wie Sie gerade gesehen haben, ist die Kombination von Hlookup/Vlookup & Match sicherlich eine Verbesserung gegenüber den regulären Hlookup- und Vlookup -Formeln. Die Match -Funktion beseitigt jedoch nicht alle Einschränkungen. Insbesondere kann eine Vlookup -Match -Formel nach wie vor die Linken nicht schauen, und das Hlookup -Match sucht nicht in einer anderen Zeile als in der obersten.

Um die oben genannten (und einige andere) Einschränkungen zu überwinden, sollten Sie eine Kombination aus Indexübereinstimmung verwenden, die eine wirklich leistungsstarke und vielseitige Möglichkeit bietet, in Excel in vielerlei Hinsicht nachzudenken, in vielerlei Hinsicht überlegen ist. Die detaillierten Anleitungs- und Formel -Beispiele finden Sie in INDEX & Match in Excel - eine bessere Alternative zu Vlookup.

So verwenden Sie Match -Formeln in Excel. Hoffentlich werden die in diesem Tutorial diskutierten Beispiele in Ihrer Arbeit hilfreich sein. Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche in unserem Blog zu sehen!

Üben Sie die Arbeitsmappe zum Download

Excel -Match -Formel -Beispiele (.xlsx -Datei)

Das obige ist der detaillierte Inhalt vonExcel -Match -Funktion mit Formel -Beispielen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!