Exceller

Obtenir la première cellule de correspondance contient

Get First Match Cell Contains

Formule Excel : Obtenir la première cellule de correspondance contientFormule générique |_+_| Sommaire

Pour rechercher une cellule parmi plusieurs choses et renvoyer la première correspondance trouvée dans la liste, vous pouvez utiliser une formule INDEX / MATCH qui utilise SEARCH ou FIND pour localiser une correspondance. Dans l'exemple illustré, la formule en C5 est :



{= INDEX (things, MATCH (TRUE, ISNUMBER ( SEARCH (things,A1)),0))}

où « choses » est le plage nommée E5:E9.

exceller montrant la formule au lieu de la valeur

Remarque : il s'agit d'un formule matricielle et doit être saisi avec Control + Shift + Enter.





Explication

Dans cet exemple, nous avons une liste de couleurs dans une plage nommée appelée « choses » (E5 : E9). Nous voulons vérifier le texte dans la colonne B pour voir s'il contient l'une de ces couleurs. Si c'est le cas, nous voulons retourner le nom de la première couleur trouvée.

Travaillant de l'intérieur vers l'extérieur, cette formule utilise le ISNUMBER fonction et Fonction RECHERCHE pour rechercher le texte dans B5 pour chaque couleur répertoriée dans « choses », comme ceci :



 
{= INDEX (things, MATCH (TRUE, ISNUMBER ( SEARCH (things,B5)),0))}

Cette expression est basée sur une formule ( expliqué en détail ici ) qui vérifie une cellule pour une seule sous-chaîne. Si la cellule contient la sous-chaîne, l'expression renvoie VRAI. Sinon, l'expression renvoie FALSE.

Lorsque nous donnons à cette RECHERCHE un liste de choses (au lieu d'une chose) nous renverra un tableau de résultats. Chaque couleur trouvée générera une position numérique, et les couleurs non trouvées généreront une erreur :

 
 ISNUMBER ( SEARCH (things,B5)

La fonction ISNUMBER convertit ensuite les résultats en valeurs VRAI / FAUX. Tout nombre devient VRAI et toute erreur (non trouvée) devient FAUX. Le résultat est un tableau comme celui-ci :

 
{#VALUE!#VALUE!20#VALUE!#VALUE!}

Ce tableau est renvoyé au Fonction MATCH comme argument du tableau. La valeur de recherche est TRUE et le type de correspondance est défini sur zéro pour forcer une correspondance exacte. Lorsqu'il y a une couleur correspondante, MATCH renvoie la première position TRUE trouvée. Cette valeur est introduite dans le fonction INDEX comme numéro de ligne, avec la plage nommée 'choses' fournie comme tableau. Lorsqu'il y a au moins une correspondance, INDEX renvoie la couleur à cette position. Lorsqu'aucune correspondance n'est trouvée, cette formule renvoie l'erreur #N/A.

Avec des valeurs codées en dur

Si vous ne souhaitez pas configurer une plage nommée externe comme « choses » dans cet exemple, vous pouvez coder en dur les valeurs dans la formule en tant que « constantes de tableau » comme ceci :

comment obtenir Excel pour calculer
 
{FALSEFALSETRUEFALSEFALSE}

Obtenez la première correspondance dans la cellule

Le langage ici est assez déroutant, mais la formule ci-dessus renverra la première correspondance trouvée dans le liste de choses à rechercher . Si à la place vous voulez retourner la première correspondance trouvée dans la cellule testée , vous pouvez essayer une formule comme celle-ci :

 
{= INDEX ({'red','green','blue'}, MATCH (TRUE, ISNUMBER ( SEARCH ({'red','green','blue'},B5)),0))}

Dans cette version de la formule, la fonction MATCH est configurée pour rechercher le résultat de cet extrait :

 
= INDEX (things, MATCH ( AGGREGATE (15,6, SEARCH (things,A1),1), SEARCH (things,A1),0))

qui utilise le Fonction AGRÉGATION pour obtenir la valeur minimale dans les résultats renvoyés par SEARCH. Nous avons besoin de AGGREGATE ici, car le tableau entrant contiendra probablement des erreurs (renvoyées par SEARCH lorsque les choses ne sont pas trouvées), et nous avons besoin d'une fonction qui ignorera ces erreurs et nous donnera toujours la valeur numérique minimale.

Le résultat de AGGREGATE est renvoyé directement à MATCH en tant que valeur de recherche, avec le même tableau renvoyé par SEARCH. Le résultat final est la première correspondance trouvée dans la cellule, pas la première correspondance trouvée dans la liste des choses.

Auteur Dave Bruns


^