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
{= INDEX (things, MATCH (TRUE, ISNUMBER ( SEARCH (things,A1)),0))}
Résumé

Pour vérifier une cellule pour l'une des nombreuses 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,B5)),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 entré avec Ctrl + Maj + Entrée.



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 de la colonne B pour voir s'il contient l'une de ces couleurs. Si c'est le cas, nous voulons renvoyer le nom de la première couleur trouvée.

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



 
 ISNUMBER ( SEARCH (things,B5)

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 TRUE. Sinon, l'expression renvoie FALSE.

Lorsque nous donnons à cette RECHERCHE un liste de choses (au lieu d'une chose) nous rendra 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:

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

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



 
{FALSEFALSETRUEFALSEFALSE}

Ce tableau est renvoyé au Fonction MATCH comme argument de 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 suit:

comment obtenir Excel pour calculer
 
{= INDEX ({'red','green','blue'}, MATCH (TRUE, ISNUMBER ( SEARCH ({'red','green','blue'},B5)),0))}

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 souhaitez renvoyer la première correspondance trouvée dans la cellule testée , vous pouvez essayer une formule comme celle-ci:

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

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

 
 AGGREGATE (15,6, SEARCH (things,A1),1) // get min value

qui utilise le Fonction AGGREGATE pour obtenir la valeur minimale dans les résultats renvoyés par SEARCH. Nous avons besoin d'AGGREGATE ici, car le tableau entrant contiendra probablement des erreurs (renvoyées par SEARCH lorsque des éléments ne sont pas trouvés), 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


^