Excel Xmatch -Funktion mit Formel -Beispielen

Das Tutorial führt die neue Excel Xmatch -Funktion vor und zeigt, wie besser es ist, als ein paar gängige Aufgaben zu lösen.

In Excel 365 wurde die XMatch -Funktion hinzugefügt, um die Match -Funktion zu ersetzen. Bevor Sie jedoch mit der Aktualisierung Ihrer vorhandenen Formeln beginnen, ist es ratsam, alle Vorteile der neuen Funktion und die Unterscheidung von der alten zu verstehen.

Zusammenfassend ist die XMatch -Funktion gleich, aber flexibler und robuster. Es kann sowohl in vertikalen als auch in horizontalen Arrays nachsehen, erst zuletzte oder zuerst nach oben suchen, genaue, ungefähre und teilweise Übereinstimmungen finden und einen schnelleren binären Suchalgorithmus verwenden.

• XMatch -Funktion in Excel
• Grundlegende Excel Xmatch -Formel
• So verwenden Sie XMatch in Excel -Formel -Beispielen
  • Genaue Übereinstimmung gegen ungefähre Übereinstimmung
  • Teilweise Übereinstimmung mit Wildcards
  • XMatch Reverse Search
  • Vergleichen Sie zwei Spalten für die Übereinstimmung
  • Index XMatch -Formel
  • Index XMatch XMatch -Formel
  • Fallsensitive XMatch-Formel
• XMatch vs. Match in Excel

Excel Xmatch -Funktion

Die XMatch -Funktion in Excel gibt die relative Position eines Wertes in einem Array oder einen Zellbereich zurück.

Es hat die folgende Syntax:

XMatch (Lookup_value, Lookup_array, [match_mode], [search_mode])

Wo:

Lookup_Value (Erforderlich) - Der Wert, nach dem man suchen muss.

Lookup_array (erforderlich) - Das Array oder den Bereich der Zellen, in denen man durchsucht werden soll.

MATCH_MODE (optional) - Gibt an, welcher Übereinstimmentyp verwendet wird:

• 0 oder ausgelassen (Standard) - genau übereinstimmen
• -1 - exakte Übereinstimmung oder der nächst kleinste Wert
• 1 - genaue Übereinstimmung oder der nächstgrößte Wert
• 2 - Wildcard -Match (*,?)

Search_mode (optional) - Gibt die Suchrichtung und den Algorithmus an:

• 1 oder ausgelassen (Standard) - Suche vom ersten bis zuletzt.
• -1 - Suche in umgekehrter Reihenfolge vom letzten bis zuerst.
• 2 - Binäre Suche aufsteigend. Erfordert Lookup_array , in aufsteigender Reihenfolge sortiert zu werden.
• -2 - Binäre Suche absteigend. Erfordert Lookup_array , in absteigender Reihenfolge sortiert zu werden.

Die binäre Suche ist ein schnellerer Algorithmus, der effizient auf sortierten Arrays arbeitet. Weitere Informationen finden Sie auf dem Suchmodus.

Welche Excel -Version hat XMatch?

Die XMatch -Funktion ist nur für Microsoft 365 und Excel 2021 in Excel erhältlich. In Excel 2019, Excel 2016 und früheren Versionen wird diese Funktion nicht unterstützt.

Grundlegende XMatch -Formel in Excel

Um eine allgemeine Vorstellung davon zu erhalten, wozu die Funktion fähig ist, erstellen wir eine XMatch -Formel, die sie einfachste Form hat, nur die ersten beiden erforderlichen Argumente definieren und die optionalen Standards für ihre Standardeinstellungen überlassen.

Angenommen, Sie haben eine Liste von Ozeanen, die nach ihrer Größe (C2: C6) eingestuft werden, und möchten den Rang eines bestimmten Ozeans finden. Um es erledigt zu haben, verwenden Sie einfach den Namen des Ozeans, sagen Sie Indian , als Suchwert und die gesamte Liste der Namen als Such -Array:

=XMATCH("Indian", C2:C6)

Um die Formel flexibler zu gestalten, geben Sie den Interesse des Ozeans in einer Zelle ein, beispielsweise F1:

=XMATCH(F1, C2:C6)

Als Ergebnis erhalten Sie eine XMatch -Formel, um in einem vertikalen Array nachzuschlagen. Die Ausgabe ist die relative Position des Suchwerts im Array, der in unserem Fall dem Rang des Ozeans entspricht:

Eine ähnliche Formel funktioniert auch perfekt für ein horizontales Array . Alles, was Sie tun müssen, ist, die Referenz für Lookup_array anzupassen:

=XMATCH(B5, B1:F1)

Excel Xmatch -Funktion - Dinge, an die man sich erinnern sollte

Um XMatch effektiv in Ihren Arbeitsblättern zu verwenden und unerwartete Ergebnisse zu verhindern, denken Sie bitte an diese 3 einfachen Fakten:

• Wenn im Lookup -Array zwei oder mehr Ereignisse des Lookup -Wertes vorhanden sind, wird die Position der ersten Übereinstimmung zurückgegeben, wenn das Argument von Search_Mode auf 1 gesetzt oder weggelassen wird. Mit Search_Mode auf -1 sucht die Funktion in umgekehrter Reihenfolge und gibt die Position der letzten Übereinstimmung zurück, wie in diesem Beispiel demonstriert.
• Wenn der Suchwert nicht gefunden wird , tritt ein #n/ein Fehler auf.
• Die XMatch-Funktion ist von Natur aus unempfindlich und kann den Brieffall nicht unterscheiden. Verwenden Sie diese Fall-sensitive XMatch-Formel, um Kleinbuchstaben und Großbuchstaben zu unterscheiden.

So verwenden Sie XMatch in Excel -Formel -Beispielen

Die folgenden Beispiele helfen Ihnen dabei, mehr Verständnis für die XMatch -Funktion und ihre praktischen Verwendungszwecke zu erlangen.

Genaue Übereinstimmung gegen ungefähre Übereinstimmung

Das Übereinstimmungsverhalten von XMatch wird durch das optionale Argument für match_mode gesteuert:

• 0 oder ausgelassen (Standard) - Die Formel sucht nur nach genauer Übereinstimmungen. Wenn keine genaue Übereinstimmung gefunden wird, wird ein #n/ein Fehler zurückgegeben.
• -1 - Die Formel sucht zuerst nach exakter Übereinstimmung und dann nach dem nächsten kleineren Element.
• 1 - Die Formel sucht zuerst nach exakter Übereinstimmung und dann nach dem nächsten größeren Element.

Und nun sehen wir, wie unterschiedliche Übereinstimmungsmodi das Ergebnis der Formel beeinflussen. Nehmen wir an, Sie möchten herausfinden, wo ein bestimmtes Gebiet, beispielsweise 80.000.000 km ² , unter allen Ozeanen steht.

Genaue Übereinstimmung

Wenn Sie 0 für match_mode verwenden, erhalten Sie einen #n/a -Fehler, da die Formel keinen Wert genau entspricht, der dem Suchwert entspricht:

=XMATCH(80000000, C2:C6, 0)

Nächster kleinster Artikel

Wenn Sie -1 für match_mode verwenden, wird die Formel 3 zurückgegeben, da der nächstgelegene Match als Suchwert 70.560.000 beträgt, und es ist das 3 ^-RD -Element im Such -Array:

=XMATCH(80000000, C2:C6, -1)

Nächster größter Artikel

Wenn Sie 1 für match_mode verwenden, gibt die Formel 2 aus, da der nächste Match, der größer als der Suchwert ist, 85.133.000 beträgt. Dies ist das ^2. Element im Such -Array:

=XMATCH(80000000, C2:C6, -1)

Das Bild unten zeigt alle Ergebnisse:

Wie man einen Teiltext in Excel mit Platzhalter entspricht

Die XMatch -Funktion verfügt über einen speziellen Match -Modus für Wildcards: das auf 2 gesetzte Argument für match_mode .

Im Wildcard Match -Modus akzeptiert eine XMatch -Formel die folgenden Wildcard -Zeichen:

• Fragezeichen (?), Die ein einzelnes Zeichen entsprechen.
• Sternchen (*), um eine beliebige Abfolge von Zeichen zu entsprechen.

Bitte beachten Sie, dass Wildcards nur mit Text und nicht mit Zahlen arbeiten.

Um beispielsweise die Position des ersten Elements zu finden, der mit "South" beginnt, lautet die Formel:

=XMATCH("south*", B2:B6, 2)

Oder Sie können Ihre Wildcard -Expression in einer Zelle eingeben, beispielsweise F1, und die Zellreferenz für das Argument von Lookup_Value liefern:

=XMATCH(F1, B2:B6, 2)

Bei den meisten Excel -Funktionen würden Sie Tilde (~) verwenden, um den Stern (~*) oder das Fragezeichen (~?) Als wörtliche Merkmale, nicht als Wildcards zu behandeln. Mit XMatch wird Tilde nicht benötigt. Wenn Sie den Wildcard -Match -Modus nicht definieren, wird XMatch das annehmen? und * sind reguläre Charaktere.

Beispielsweise durch die folgende Formel wird der Bereich A2: A7 genau nach dem Sternchenzeichen durchsucht:

=XMATCH("*", A2:A7)

XMatch Reverse Search, um das letzte Spiel zu finden

Falls es im Such -Array mehrere Vorkommen des Lookup -Werts gibt, müssen Sie möglicherweise die Position des letzten Ereignisses erhalten.

Die Richtung der Suche wird kontrolliert sein, das ^4. Argument von XMatch namens Search_Mode . Um in umgekehrter Reihenfolge zu suchen, dh von unten nach oben in einem vertikalen Array und von rechts nach links in einem horizontalen Array sollte Search_Mode auf -1 eingestellt werden.

In diesem Beispiel werden wir die Position des letzten Datensatzes für einen bestimmten Suchwert zurückgeben (siehe den Screenshot unten). Geben Sie dazu die Argumente wie folgt ein:

• Lookup_Value - Der Zielverkäufer in H1
• Lookup_array - Verkäufernamen in C2: C10
• Match_mode ist 0 oder weggelassen (exakte Übereinstimmung)
• Search_mode ist -1 (Last-to-First)

Wenn wir die vier Argumente zusammenfügen, erhalten wir diese Formel:

=XMATCH(H1, C2:C10, 0, -1)

Was die Anzahl des letzten Verkaufs von Laura zurückgibt:

So vergleichen Sie zwei Spalten in Excel für die Übereinstimmung

Um zwei Listen für Übereinstimmungen zu vergleichen, können Sie die XMatch -Funktion zusammen mit if und ISNA verwenden:

If (isna (xmatch ( target_list , search_list , 0)), "no Match", "Match")

Um beispielsweise die Liste 2 in B2: B10 mit der Liste 1 in A2: A10 zu vergleichen, nimmt die Formel das folgende Formular an:

=IF(ISNA(XMATCH(B2:B10, A2:A9)), "", "Match in List 1")

In diesem Beispiel identifizieren wir nur Übereinstimmungen, daher ist das Argument Value_if_strue der IF -Funktion eine leere Zeichenfolge ("").

Geben Sie die obige Formel in die oberste Zelle (C2 in unserem Fall) ein, drücken Sie die Eingabetaste und "verschüttet" automatisch in die anderen Zellen (sie wird als Verschüttungsbereich bezeichnet):

Wie diese Formel funktioniert

Im Zentrum der Formel sucht die XMatch -Funktion nach einem Wert aus Liste 2 in der Liste 1. Wenn ein Wert gefunden wird, wird seine relative Position zurückgegeben, sonst ein #n/a -Fehler. In unserem Fall ist das Ergebnis von XMatch das folgende Array:

{#N/A;#N/A;2;#N/A;4;#N/A;#N/A;8;#N/A}

Dieses Array wird der ISNA -Funktion "gefüttert", um auf #N/A -Fehler zu überprüfen. Für jeden Fehler #N/A gibt ISNA True zurück. Für einen anderen Wert - falsch. Als Ergebnis erzeugt es das folgende Array von logischen Werten, bei denen die True Nichtübereinstimmungen darstellen, und die False stellt die Übereinstimmungen dar:

{TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}

Das obige Array geht zum logischen Test der IF -Funktion. Abhängig davon, wie Sie die letzten beiden Argumente konfiguriert haben, gibt die Formel den entsprechenden Text aus. In unserem Fall handelt es sich um eine leere String ("") für Nichtübereinstimmungen ( value_if_true ) und "Übereinstimmung in der Liste 1" für Übereinstimmungen ( value_if_false ).

Notiz. Diese Formel funktioniert nur in Excel 365 und Excel 2021, die dynamische Arrays unterstützen. Wenn Sie Excel 2019, Excel 2016 oder frühere Version verwenden, lesen Sie bitte andere Lösungen: So vergleichen Sie zwei Spalten in Excel.

Index Xmatch in Excel

XMatch kann in Kombination mit der Indexfunktion verwendet werden, um einen Wert aus einer anderen Spalte abzurufen, die dem Suchwert zugeordnet ist, genau wie die Index -Match -Formel. Der generische Ansatz ist wie folgt:

Index ( return_array , xmatch ( Lookup_value , Lookup_array )

Die Logik ist sehr einfach und leicht zu befolgen:

Die XMatch -Funktion berechnet die relative Position des Suchwerts im Lookup -Array und übergibt sie an das Argument von Row_num des Index. Basierend auf der Zeilennummer gibt die Indexfunktion einen Wert aus jeder von Ihnen angegebenen Spalte zurück.

Um beispielsweise den Bereich des Ozeans in E1 nachzuschlagen, können Sie diese Formel verwenden:

=INDEX(B2:B6, XMATCH(E1, A2:A6))

INDEX XMATCH XMATCH, um eine 2-dimentale Suche durchzuführen

Verwenden Sie den Index zusammen mit zwei XMatch -Funktionen, um in Spalten und Zeilen gleichzeitig nachzuschlagen. Die erste XMatch erhält die Zeilennummer und die zweite wird die Spaltennummer abgerufen:

Index ( Daten , XMatch ( Lookup_Value , Vertical _ Lookup_array ), XMatch ( Suchwert , horizontal _ookup_array )))

Die Formel ähnelt der Index Match Match, außer dass Sie das Argument von Match_Mode weglassen können, da es standardmäßig übereinstimmt.

Zum Beispiel ist die Formel:

=INDEX(B2:D8, XMATCH(G1, A2:A8), XMATCH(G2, B1:D1))

Wobei B2: D8 Datenzellen mit Ausnahme von Zeilen- und Säulenheadern sind, ist A2: A8 eine Liste von Elementen und B1: D1 sind Monatsnamen.

Fallsensitive XMatch-Formel

Wie bereits erwähnt, ist die Excel Xmatch-Funktion von der Entwurfsuntersuchung. Verwenden Sie XMatch in Kombination mit der genauen Funktion, um es zu zwingen, den Textfall zu unterscheiden:

Match (true, exact ( Lookup_array , Lookup_Value ))

Um in umgekehrter Reihenfolge von zuerst zuerst zu suchen:

Match (true, exact ( Lookup_array , Lookup_Value ), 0, -1)

Das folgende Beispiel zeigt diese generische Formel in Aktion. Angenommen, Sie haben eine Liste von Fall-sensitiven Produkt-IDs in B2: B11. Sie möchten die relative Position des Elements in E1 finden. Eine Fall-sensitive Formel in E2 ist so einfach wie folgt:

=XMATCH(TRUE, EXACT(B2:B11, E1))

Wie diese Formel funktioniert:

Die genaue Funktion vergleicht den Lookup -Wert mit jedem Element im Lookup -Array. Wenn die verglichenen Werte genau gleich sind, einschließlich des Zeichens der Zeichen, gibt die Funktion wahr, sonst falsch. Dieses Array von logischen Werten (wobei True's Exact Matches repräsentiert) geht zum Argument von XMatch zum Lookup_array . Und da der Suchwert der Sokenie ist, gibt die XMatch -Funktion die Position der ersten genauen Übereinstimmung oder der letzten exakten Übereinstimmung zurück, abhängig davon, wie Sie das Argument von Search_Mode konfiguriert haben.

XMatch vs. Match in Excel

XMatch wurde als leistungsstärkerer und vielseitigerer Ersatz für Match entworfen. Daher haben diese beiden Funktionen viel gemeinsam. Es gibt jedoch wesentliche Unterschiede.

Verschiedenes Standardverhalten

Die Übereinstimmungsfunktion ist standardmäßig übereinstimmt oder das nächste kleinste Element ( match_type auf 1 oder weggelassen).

Die XMatch -Funktion standardmäßig übereinstimmen ( match_mode auf 0 oder weggelassen).

Anderes Verhalten für ungefähre Übereinstimmung

Wenn das Argument match_mode / match_type auf 1 gesetzt ist:

• Übereinstimmung mit der Suche nach exakter Übereinstimmung oder dem nächsten kleinsten. Erfordert, dass das Lookup -Array in aufsteigender Reihenfolge sortiert werden muss.
• XMatch sucht nach exakter Übereinstimmung oder nächstgrößter. Erfordert keine Sortierung.

Wenn das Argument match_mode / match_type auf -1 gesetzt ist:

• Übereinstimmung mit der Suche nach exakter Übereinstimmung oder dem nächsten größten. Erfordert das Sortieren des Lookup -Arrays in absteigender Reihenfolge.
• XMatch sucht nach exakten Übereinstimmungen oder als nächstes kleinste. Erfordert keine Sortierung.

Wildcard -Suche

Um teilweise Übereinstimmungen mit XMatch zu finden, müssen Sie das Argument match_mode auf 2 festlegen.

Die Match -Funktion hat keine spezielle Option für Wildcard -Match -Modus. In den meisten Fällen konfigurieren Sie es für exakte Übereinstimmungen ( match_type auf 0), das auch für Wildcard -Suchanfragen gearbeitet wird.

Suchmodus

Wie bei der neuen Xlookup -Funktion verfügt XMatch über ein Special Search_Mode -Argument, mit dem Sie die Suchrichtung definieren können:

• 1 oder ausgelassen (Standard)-Suchen Sie zuerst zu länger.
• -1-Reverse Search Lust to-First.

Und wählen Sie einen binären Suchalgorithmus , der bei sortierten Daten sehr schnell und effizient ist.

• 2 - Binäre Suche auf Daten sortiert aufsteigend.
• -2 - Binäre Suche nach Daten sortiert absteigend.

Die binäre Suche , auch Halbinterval-Suche oder logarithmische Suche genannt, ist ein spezieller Algorithmus, der die Position eines Suchwerts in einem Array findet, indem es mit dem mittleren Element des Arrays verglichen wird. Eine binäre Suche ist viel schneller als eine reguläre Suche, funktioniert jedoch nur auf sortierten Listen korrekt. Bei ungeortierten Daten kann es falsche Ergebnisse zurückgeben, die auf den ersten Blick auf den ersten Blick ziemlich normal aussehen.

Die Syntax der Übereinstimmung sieht das Suchmodus -Argument überhaupt nicht vor.

Xmatch verarbeitet Arrays nativ

Im Gegensatz zu seinem Vorgänger wurde die XMatch -Funktion für dynamische Excel entwickelt und verarbeitet Arrays nativ, ohne dass Sie die Strg -Eingabetaste drücken mussten. Dies erleichtert die Erstellung und Bearbeitung von Formeln, insbesondere wenn einige verschiedene Funktionen zusammen verwenden. Vergleichen Sie einfach die folgenden Lösungen:

• Fallempfindlichkeitsformel: Xmatch | ÜBEREINSTIMMEN
• Vergleichen Sie zwei Spalten oder Listen für Übereinstimmungen: Xmatch | ÜBEREINSTIMMEN

XMatch- und Übereinstimmung Verfügbarkeit

XMatch ist eine neue Funktion und ist nur für Microsoft 365 und Excel 2021 in Excel erhältlich.

Die Spielfunktion ist in jeder Version von Excel 365 bis Excel 2007 verfügbar.

So verwenden Sie die XMatch -Funktion in Excel. 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 Xmatch -Formel Beispiele (.xlsx -Datei)

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