Exceller

Catégoriser le texte avec des mots-clés

Categorize Text With Keywords

Formule Excel : catégoriser le texte avec des mots-clésFormule générique |_+_| Sommaire

Pour catégoriser le texte à l'aide de mots-clés avec une correspondance « contient », vous pouvez utiliser la fonction RECHERCHE, avec l'aide d'INDEX et de MATCH. Dans l'exemple illustré, la formule en C5 est :





excel convertir le texte au format de date
{= INDEX (categories, MATCH (TRUE, ISNUMBER ( SEARCH (keywords,text)),0))}

mots clés est le plage nommée E5:E14, et catégories est la plage nommée F5:F14.

Remarque : il s'agit d'un formule matricielle et doit être saisi avec control + shift + enter.





Explication

Au fond, c'est un je Fonction NDEX et MATCH .

Dans la fonction MATCH, nous utilisons le Fonction RECHERCHE pour rechercher des cellules dans la colonne B pour chaque mot-clé répertorié dans le plage nommée mots clés (E5:E14):



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

Parce que nous recherchons plusieurs articles (dans la gamme nommée mots clés ), nous obtiendrons plusieurs résultats comme celui-ci :

 
 SEARCH (keywords,B5)

La valeur! erreur se produit lorsque SEARCH ne peut pas trouver le texte. Lorsque SEARCH trouve une correspondance, il renvoie un nombre qui correspond à la position du texte à l'intérieur de la cellule.

Pour changer ces résultats dans un format plus utilisable, nous utilisons le ISNUMBER fonction , qui convertit toutes les valeurs en TRUE/FALSE comme ceci :

 
{#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!24#VALUE!#VALUE!#VALUE!}

Ce tableau entre dans la fonction MATCH en tant que tableau_recherche , avec le valeur_recherche défini comme VRAI. MATCH renvoie la position du premier VRAI qu'il trouve dans le tableau (7 dans ce cas) qui est fourni au fonction INDEX comme le num_ligne :

 
{FALSEFALSEFALSEFALSEFALSEFALSETRUEFALSEFALSEFALSE}

INDEX renvoie le 7e élément dans catégories , 'Auto', comme résultat final.

Avec XLOOKUP

Avec le Fonction XLOOKUP , cette formule peut être quelque peu simplifiée. XLOOKUP peut utiliser la même logique utilisée dans la fonction MATCH ci-dessus, donc la formule équivalente est :

comment soustraire la date et l'heure dans Excel
 
= INDEX (categories,7)

XLOOKUP localise le premier TRUE dans le tableau et renvoie la valeur correspondante de catégories .

Formules de tableaux dynamiques sont disponibles en Bureau 365 seul.

Empêcher les fausses correspondances

Un problème avec cette approche est que vous pouvez obtenir de fausses correspondances à partir de sous-chaînes qui apparaissent à l'intérieur de mots plus longs. Par exemple, si vous essayez de faire correspondre 'dr', vous pouvez également trouver 'Andrea', 'drink', 'dry', etc. puisque 'dr' apparaît à l'intérieur de ces mots. Cela se produit parce que SEARCH effectue automatiquement une correspondance « contient ».

Pour un hack rapide, vous pouvez ajouter de l'espace autour des mots de recherche (c'est-à-dire ' dr ' ou ' dr ') pour éviter d'attraper ' dr ' dans un autre mot. Mais cela échouera si 'dr' apparaît en premier ou en dernier dans une cellule, ou apparaît avec une ponctuation, etc.

Si vous avez besoin d'une solution plus précise, une option consiste à normaliser le texte d'abord dans un colonne d'aide , en prenant soin d'ajouter également un espace de début et de fin. Ensuite, vous pouvez rechercher des mots entiers entourés d'espaces.

Auteur Dave Bruns


^