Fonction Excel Xmatch avec des exemples de formule

Le tutoriel présente la nouvelle fonction Excel XMatch et montre comment il est meilleur que la correspondance pour résoudre quelques tâches courantes.

Dans Excel 365, la fonction XMatch a été ajoutée pour remplacer la fonction de correspondance. Mais avant de commencer à mettre à niveau vos formules existantes, il serait sage de comprendre tous les avantages de la nouvelle fonction et comment il diffère de l'ancien.

En résumé, la fonction XMatch est la même que la correspondance mais plus flexible et robuste. Il peut rechercher à la fois dans des tableaux verticaux et horizontaux, rechercher le premier au dernier ou le dernier, trouver des correspondances exactes, approximatives et partielles et utiliser un algorithme de recherche binaire plus rapide.

• Fonction XMatch dans Excel
• Formule de base Excel Xmatch
• Comment utiliser XMatch dans Excel - Exemples de formule
  • Match exact vs match approximatif
  • Match partiel avec les faunes
  • Recherche inversée XMatch
  • Comparez deux colonnes pour le match
  • Index la formule XMatch
  • Index la formule XMatch XMatch
  • Formule XMatch sensible à la casse
• Xmatch vs match dans Excel

Fonction Excel Xmatch

La fonction XMatch dans Excel renvoie la position relative d'une valeur dans un tableau ou une gamme de cellules.

Il a la syntaxe suivante:

Xmatch (lookup_value, lookup_array, [match_mode], [search_mode])

Où:

Lookup_value (requis) - la valeur à rechercher.

Lookup_Array (requis) - le tableau ou la plage de cellules où rechercher.

Match_Mode (facultatif) - Spécifie le type de correspondance à utiliser:

• 0 ou omis (par défaut) - correspondance exacte
• -1 - correspondance exacte ou la plus petite valeur suivante
• 1 - correspondance exacte ou la plus grande valeur la plus importante
• 2 - Match des jilèges (* ,?)

Search_Mode (facultatif) - Spécifie la direction et l'algorithme de recherche:

• 1 ou omis (par défaut) - Recherche du premier au dernier.
• -1 - Recherche dans l'ordre inverse du dernier au dernier.
• 2 - Recherche binaire ascendante. Exige que Lookup_Array soit trié par ordre croissant.
• -2 - Recherche binaire descendant. Exige que Lookup_Array soit trié par ordre décroissant.

La recherche binaire est un algorithme plus rapide qui fonctionne efficacement sur les tableaux triés. Pour plus d'informations, veuillez consulter le mode de recherche.

Quelle version Excel a XMatch?

La fonction XMatch n'est disponible que dans Excel pour Microsoft 365 et Excel 2021. Dans Excel 2019, Excel 2016 et les versions antérieures, cette fonction n'est pas prise en charge.

Formule XMatch de base dans Excel

Pour obtenir une idée générale de ce que la fonction est capable, construisons une formule XMatch, il est sa forme la plus simple, ne définissant que les deux premiers arguments requis et laissant les facultatifs à leurs défaillances.

En supposant que vous avez une liste d'océans classés par leur taille (C2: C6) et vous souhaitez trouver le rang d'un océan particulier. Pour le faire, utilisez simplement le nom de l'océan, disons Indian , comme valeur de recherche et la liste complète des noms comme tableau de recherche:

=XMATCH("Indian", C2:C6)

Pour rendre la formule plus flexible, saisissez l'océan d'intérêt dans une cellule, dites F1:

=XMATCH(F1, C2:C6)

En conséquence, vous obtenez une formule XMatch à rechercher dans un tableau vertical . La sortie est la position relative de la valeur de recherche dans le tableau, qui dans notre cas correspond au rang de l'océan:

Une formule similaire fonctionne parfaitement pour un tableau horizontal . Tout ce que vous avez à faire est d'ajuster la référence Lookup_Array :

=XMATCH(B5, B1:F1)

Fonction Excel Xmatch - choses à retenir

Pour utiliser efficacement XMatch dans vos feuilles de travail et prévenir les résultats inattendus, n'oubliez pas ces 3 faits simples:

• S'il y a deux occurrences ou plus de la valeur de recherche dans le tableau de recherche, la position de la première correspondance est renvoyée si l'argument Search_Mode est défini sur 1 ou omis. Avec Search_Mode défini sur -1, la fonction recherche dans l'ordre inverse et renvoie la position de la dernière correspondance comme démontré dans cet exemple.
• Si la valeur de recherche n'est pas trouvée , une erreur # n / a se produit.
• La fonction XMatch est insensible à la casse par nature et ne peut pas distinguer le cas de lettre. Pour distinguer les caractères minuscules et majuscules, utilisez cette formule XMatch sensible à la casse.

Comment utiliser XMatch dans Excel - Exemples de formule

Les exemples suivants vous aideront à mieux comprendre la fonction XMatch et ses utilisations pratiques.

Match exact vs match approximatif

Le comportement correspondant de XMatch est contrôlé par l'argument Match_Mode en option:

• 0 ou omis (par défaut) - La formule recherche uniquement la correspondance exacte. Si une correspondance exacte n'est pas trouvée, une erreur # N / A est renvoyée.
• -1 - La formule recherche d'abord la correspondance exacte, puis pour l'élément plus petit suivant.
• 1 - La formule recherche d'abord la correspondance exacte, puis pour le prochain élément plus grand.

Et maintenant, voyons comment différents modes de correspondance affectent le résultat de la formule. Supposons que vous souhaitiez savoir où une certaine zone, disons 80 000 000 km ² , se situe parmi tous les océans.

Match exact

Si vous utilisez 0 pour Match_Mode , vous obtiendrez une erreur # N / A, car la formule ne peut pas trouver une valeur exactement égale à la valeur de recherche:

=XMATCH(80000000, C2:C6, 0)

Prochain plus petit article

Si vous utilisez -1 pour Match_Mode , la formule reviendra 3, car la correspondance la plus proche plus petite que la valeur de recherche est de 70 560 000, et c'est le 3 ^e élément du tableau de recherche:

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

Prochain plus grand article

Si vous utilisez 1 pour Match_Mode , la formule sortira 2, car la correspondance la plus proche plus grande que la valeur de recherche est de 85 133 000, qui est le 2 ^ème élément dans le tableau de recherche:

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

L'image ci-dessous montre tous les résultats:

Comment faire correspondre le texte partiel dans Excel avec les faunes

La fonction XMatch a un mode de correspondance spécial pour les wildcards: l'argument Match_Mode défini sur 2.

En mode de correspondance générique, une formule XMatch accepte les caractères génériques suivants:

• Marque de questions (?) Pour correspondre à un seul caractère.
• Astérisque (*) pour correspondre à n'importe quelle séquence de caractères.

Veuillez garder à l'esprit que les jilèges ne fonctionnent qu'avec du texte, pas des chiffres.

Par exemple, pour trouver la position du premier élément qui commence par "Sud", la formule est:

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

Ou vous pouvez taper votre expression de joker dans une cellule, par exemple F1, et fournir la référence cellulaire pour l'argument Lookup_value :

=XMATCH(F1, B2:B6, 2)

Avec la plupart des fonctions Excel, vous utiliseriez Tilde (~) pour traiter l'astérisque (~ *) ou le point d'interrogation (~?) Comme des caractères littéraux, pas des caractères génériques. Avec XMatch, Tilde n'est pas nécessaire. Si vous ne définissez pas le mode de correspondance des jilèges, XMatch le supposera? et * sont des personnages réguliers.

Par exemple, la formule ci-dessous recherchera la plage A2: A7 exactement pour le caractère astérisque:

=XMATCH("*", A2:A7)

Xmatch Reverse Recherche pour trouver le dernier match

Dans le cas où il y a plusieurs occurrences de la valeur de recherche dans le tableau de recherche, vous pouvez parfois avoir besoin d'obtenir la position de la dernière occurrence .

La direction de la recherche est contrôlée être le 4 ^e argument de Xmatch nommé Search_Mode . Pour rechercher dans l'ordre inverse, c'est-à-dire du bas en haut dans un tableau vertical et de droite à gauche dans un tableau horizontal, Search_Mode doit être réglé sur -1.

Dans cet exemple, nous retournerons la position du dernier enregistrement pour une valeur de recherche spécifique (veuillez consulter la capture d'écran ci-dessous). Pour cela, configurez les arguments comme suit:

• Lookup_value - Le vendeur cible dans H1
• Lookup_Array - Noms de vendeur en C2: C10
• Match_Mode est 0 ou omis (match exact)
• Search_Mode est -1 (dernier au premier)

En rassemblant les quatre arguments, nous obtenons cette formule:

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

Qui renvoie le numéro de la dernière vente faite par Laura:

Comment comparer deux colonnes dans Excel pour le match

Pour comparer deux listes pour les correspondances, vous pouvez utiliser la fonction XMatch avec IF et ISNA:

If (isna (xmatch ( target_list , search_list , 0)), "pas de correspondance", "match")

Par exemple, pour comparer la liste 2 dans B2: B10 contre la liste 1 dans A2: A10, la formule prend le formulaire suivant:

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

Dans cet exemple, nous identifions uniquement des correspondances, donc l'argument Value_IF_TRUE de la fonction IF est une chaîne vide ("").

Entrez la formule ci-dessus dans la cellule la plus supérieure (C2 dans notre cas), appuyez automatiquement sur Entrée , et elle "déversera automatiquement" dans les autres cellules (elle s'appelle une plage de déversement):

Comment fonctionne cette formule

Au cœur de la formule, la fonction XMatch recherche une valeur de la liste 2 dans la liste 1. Si une valeur est trouvée, sa position relative est renvoyée, sinon une erreur # n / a. Dans notre cas, le résultat de XMatch est le tableau suivant:

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

Ce tableau est "nourri" à la fonction ISNA à vérifier pour les erreurs # n / a. Pour chaque erreur # n / a, ISNA renvoie true; Pour toute autre valeur - FAUX. En conséquence, il produit le tableau suivant de valeurs logiques, où les TRUE représentent les non-matchs, et les correspondances de Faux représentent:

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

Le tableau ci-dessus va au test logique de la fonction IF. Selon la façon dont vous avez configuré les deux derniers arguments, la formule sortira le texte correspondant. Dans notre cas, il s'agit d'une chaîne vide ("") pour les non-matchs ( Value_IF_TRUE ) et "Match in List 1" pour les correspondances ( valeur_if_false ).

Note. Cette formule fonctionne uniquement dans Excel 365 et Excel 2021 qui prennent en charge les tableaux dynamiques. Si vous utilisez Excel 2019, Excel 2016 ou une version antérieure, veuillez consulter d'autres solutions: comment comparer deux colonnes dans Excel.

Indexer xmatch dans Excel

XMatch peut être utilisé en combinaison avec la fonction d'index pour récupérer une valeur à partir d'une autre colonne associée à la valeur de recherche, tout comme la formule de correspondance d'index. L'approche générique est la suivante:

Index ( return_array , xmatch ( lookup_value , lookup_array )

La logique est très simple et facile à suivre:

La fonction XMatch calcule la position relative de la valeur de recherche dans le tableau de recherche et la transmet à l'argument ROW_NUM de l'index. En fonction du numéro de ligne, la fonction d'index renvoie une valeur à partir de n'importe quelle colonne que vous spécifiez.

Par exemple, pour rechercher la zone de l'océan en E1, vous pouvez utiliser cette formule:

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

Indexer xmatch xmatch pour effectuer une recherche à 2 dimensions

Pour consulter simultanément dans les colonnes et les lignes, utilisez l'index avec deux fonctions XMatch. Le premier XMatch obtiendra le numéro de ligne et le second récupérera le numéro de colonne:

Index ( données , xmatch ( lookup_value , vertical _ lookup_array ), xmatch ( valeur de recherche , horizontal _ lookup_array )))

La formule est similaire à l'index Match Match, sauf que vous pouvez omettre l'argument Match_Mode car il est par défaut à la correspondance exacte.

Par exemple, pour récupérer un numéro de vente pour un article donné (G1) dans un mois spécifique (G2), la formule est:

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

Lorsque B2: D8 sont des cellules de données excluant les en-têtes de lignes et de colonnes, A2: A8 est une liste des éléments et B1: D1 sont des noms de mois.

Formule XMatch sensible à la casse

Comme déjà mentionné, la fonction Excel XMatch est insensible à la conception. Pour le forcer à distinguer le boîtier de texte, utilisez XMatch en combinaison avec la fonction exacte:

Match (true, exact ( lookup_array , lookup_value )))

Pour rechercher dans l'ordre inverse du dernier au premier:

Match (true, exact ( lookup_array , lookup_value ), 0, -1)

L'exemple suivant montre cette formule générique en action. En supposant que vous disposez d'une liste d'identifices de produit sensibles à la casse dans B2: B11. Vous cherchez à trouver la position relative de l'élément dans E1. Une formule sensible à la casse dans E2 est aussi simple que celle-ci:

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

Comment fonctionne cette formule:

La fonction exacte compare la valeur de recherche à chaque élément du tableau de recherche. Si les valeurs comparées sont exactement égales, y compris le cas des caractères, la fonction renvoie true, fausse autrement. Ce tableau de valeurs logiques (où TRUE représente les correspondances exactes) va à l'argument Lookup_Array de XMatch. Et parce que la valeur de recherche est vraie, la fonction XMatch renvoie la position de la première correspondance exacte trouvée ou de la dernière correspondance exacte, selon la façon dont vous avez configuré l'argument Search_Mode .

Xmatch vs match dans Excel

XMatch a été conçu comme un remplacement plus puissant et polyvalent pour Match, et ces deux fonctions ont donc beaucoup de choses en commun. Cependant, il existe des différences essentielles.

Comportement par défaut différent

La fonction de correspondance par défaut est exact MATCH ou le plus petit élément suivant ( match_type défini sur 1 ou omis).

La fonction XMatch par défaut est exact MATCH ( Match_Mode se définit sur 0 ou omis).

Comportement différent pour une correspondance approximative

Lorsque l'argument Match_Mode / Match_Type est défini sur 1:

• Les recherches de correspondance pour la correspondance exacte ou la plus petite. Exige que le tableau de recherche soit trié par ordre croissant.
• XMatch recherche une correspondance exacte ou la plus grande. Ne nécessite aucun tri.

Lorsque l'argument Match_Mode / Match_Type est défini sur -1:

• Les recherches de match pour le match exact ou le prochain plus grand. Nécessite de trier le tableau de recherche par ordre décroissant.
• XMatch recherche une correspondance exacte ou la plus petite. Ne nécessite aucun tri.

Recherche de cartes génériques

Pour trouver des correspondances partielles avec XMatch, vous devez définir l'argument Match_Mode sur 2.

La fonction de correspondance n'a pas d'option spéciale de mode de correspondance générique. Dans la plupart des cas, vous le configurerez pour la correspondance exacte ( match_type défini sur 0), ce qui fonctionne également pour les recherches de jacquier.

Mode de recherche

Comme la nouvelle fonction XLookup, XMatch a un argument Search_Mode spécial qui vous permet de définir la direction de la recherche :

• 1 ou omis (par défaut) - Recherchez le premier à la dernière.
• -1 - Recherche inversée en dernier à première vue.

Et choisissez un algorithme de recherche binaire , qui est très rapide et efficace sur les données triées .

• 2 - Recherche binaire sur les données triées ascendantes.
• -2 - Recherche binaire sur les données triées descendant.

La recherche binaire , également appelée recherche à demi-intervals ou recherche logarithmique , est un algorithme spécial qui trouve la position d'une valeur de recherche dans un tableau en le comparant à l'élément central du tableau. Une recherche binaire est beaucoup plus rapide qu'une recherche régulière mais fonctionne correctement uniquement sur les listes triées. Sur les données non triées, il peut renvoyer de mauvais résultats qui peuvent sembler assez normaux à première vue.

La syntaxe de la correspondance ne prévoit pas du tout l'argument du mode de recherche.

Xmatch gère les tableaux nativement

Contrairement à son prédécesseur, la fonction XMatch a été conçue pour Excel dynamique et gère les tableaux nativement, sans que vous ayez à appuyer sur Ctrl Shift Entrée . Cela rend les formules beaucoup plus faciles à construire et à modifier, en particulier lors de l'utilisation de quelques fonctions différentes ensemble. Comparez simplement les solutions suivantes:

• Formule sensible à la casse: XMatch | CORRESPONDRE
• Comparez deux colonnes ou listes pour les matchs: XMatch | CORRESPONDRE

Disponibilité XMatch et correspondant

XMatch est une nouvelle fonction et il n'est disponible que dans Excel pour Microsoft 365 et Excel 2021.

La fonction de correspondance est disponible dans n'importe quelle version d'Excel 365 à Excel 2007.

C'est comment utiliser la fonction XMatch dans Excel. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Classeur de pratique pour télécharger

Exemples de formule XMatch Excel (fichier .xlsx)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!