Excel Contient : Comment Trouver Exactement ce que Vous Cherchez

Comprendre le concept "Excel contient"

L'idée derrière "Excel contient" est de pouvoir identifier les cellules qui contiennent une certaine chaîne de caractères. Par exemple, vous pourriez vouloir trouver toutes les cellules dans une colonne qui contiennent le mot "pomme", ou identifier les adresses e-mail qui incluent "@gmail.com". Excel propose plusieurs fonctions qui, combinées, permettent d'atteindre ce résultat.

Contrairement à d'autres logiciels, Excel n'a pas de fonction native appelée "CONTIENT". Cependant, les fonctions CHERCHE, TROUVE, ESTNUM, ESTERR, SI, et les caractères génériques offrent une puissance considérable pour imiter ce comportement.

Méthode 1 : Utiliser la fonction CHERCHE

La fonction CHERCHE est l'une des plus utilisées pour simuler la fonction "Excel contient". Elle renvoie la position de départ d'une chaîne de caractères à l'intérieur d'une autre chaîne de caractères. Si la chaîne de caractères recherchée n'est pas trouvée, elle renvoie une erreur #VALEUR!.

Syntaxe de la fonction CHERCHE

=CHERCHE(texte_cherché;texte_dans_lequel;[no_départ])

• texte_cherché: La chaîne de caractères que vous recherchez.
• texte_dans_lequel: La cellule ou la chaîne de caractères dans laquelle vous effectuez la recherche.
• [no_départ]: (Facultatif) La position de départ de la recherche dans texte_dans_lequel. S'il est omis, la recherche commence au premier caractère.

Exemple d'utilisation de CHERCHE

Supposons que vous ayez une liste de noms de produits dans la colonne A (A1:A10) et que vous vouliez savoir quels produits contiennent le mot "écran".

1. Dans la cellule B1, entrez la formule suivante : =CHERCHE("écran";A1)

2. Faites glisser la formule vers le bas jusqu'à la cellule B10.

Les cellules de la colonne B afficheront un nombre si le mot "écran" est trouvé dans la cellule correspondante de la colonne A. Si le mot n'est pas trouvé, la cellule affichera l'erreur #VALEUR!.

Gérer l'erreur #VALEUR! avec ESTNUM et SI

Pour rendre les résultats plus lisibles, vous pouvez combiner la fonction CHERCHE avec les fonctions ESTNUM et SI. ESTNUM renvoie VRAI si une valeur est un nombre et FAUX sinon. SI permet d'afficher un résultat différent selon qu'une condition est vraie ou fausse.

1. Dans la cellule B1, entrez la formule suivante : =SI(ESTNUM(CHERCHE("écran";A1));"Oui";"Non")

2. Faites glisser la formule vers le bas jusqu'à la cellule B10.

Cette formule affiche "Oui" si le mot "écran" est trouvé et "Non" dans le cas contraire.

Capture d'écran (description textuelle):

Une feuille Excel avec une colonne A contenant des noms de produits (e.g., "Écran LCD", "Clavier", "Écran tactile", "Souris"). La colonne B, avec la formule =SI(ESTNUM(CHERCHE("écran";A1));"Oui";"Non"), affiche "Oui" pour "Écran LCD" et "Écran tactile", et "Non" pour les autres produits.

Méthode 2 : Utiliser la fonction TROUVE

La fonction TROUVE est similaire à CHERCHE, mais elle est sensible à la casse (majuscules et minuscules). Cela signifie qu'elle fera la distinction entre "écran" et "Écran".

Syntaxe de la fonction TROUVE

=TROUVE(texte_cherché;texte_dans_lequel;[no_départ])

• texte_cherché: La chaîne de caractères que vous recherchez.
• texte_dans_lequel: La cellule ou la chaîne de caractères dans laquelle vous effectuez la recherche.
• [no_départ]: (Facultatif) La position de départ de la recherche dans texte_dans_lequel. S'il est omis, la recherche commence au premier caractère.

Exemple d'utilisation de TROUVE

Si vous voulez trouver uniquement les cellules qui contiennent "Écran" (avec une majuscule), vous pouvez utiliser la formule suivante :

1. Dans la cellule B1, entrez la formule suivante : =SI(ESTNUM(TROUVE("Écran";A1));"Oui";"Non")

2. Faites glisser la formule vers le bas jusqu'à la cellule B10.

Cette formule affichera "Oui" uniquement pour les cellules contenant "Écran" avec une majuscule.

Méthode 3 : Utiliser les caractères génériques

Excel prend en charge les caractères génériques, qui peuvent être utilisés avec certaines fonctions comme NB.SI et SOMME.SI. Les caractères génériques les plus courants sont l'astérisque (*) et le point d'interrogation (?).

• L'astérisque (*) représente n'importe quelle séquence de caractères.
• Le point d'interrogation (?) représente un seul caractère.

Bien que les caractères génériques ne fonctionnent pas directement avec CHERCHE ou TROUVE pour rechercher une sous-chaîne, ils sont très utiles pour compter ou sommer des cellules qui contiennent une certaine combinaison de caractères.

Exemple d'utilisation des caractères génériques avec NB.SI

Supposons que vous ayez une colonne (A1:A10) contenant des descriptions de produits et que vous vouliez compter le nombre de produits qui contiennent le mot "écran".

1. Dans une cellule vide, entrez la formule suivante : =NB.SI(A1:A10;"*écran*")

Cette formule comptera toutes les cellules de la plage A1:A10 qui contiennent le mot "écran", quel que soit ce qui se trouve avant ou après.

Capture d'écran (description textuelle):

Une feuille Excel avec une colonne A contenant des descriptions de produits (e.g., "Écran LCD", "Clavier", "Écran tactile", "Souris"). Une cellule contient la formule =NB.SI(A1:A10;"*écran*") et affiche le nombre 2, car deux cellules contiennent le mot "écran".

Méthode 4 : Créer une fonction personnalisée (UDF) avec VBA

Pour une solution plus directe et réutilisable, vous pouvez créer une fonction personnalisée (User Defined Function - UDF) en utilisant Visual Basic for Applications (VBA).

Création de la fonction VBA

1. Ouvrez l'éditeur VBA (Alt + F11).
2. Insérez un nouveau module (Insertion > Module).
3. Copiez et collez le code VBA suivant :

Function Contient(Plage As Range, Texte As String) As Boolean
    Contient = InStr(1, Plage.Value, Texte, vbTextCompare) > 0
End Function

1. Fermez l'éditeur VBA.

Explication du code VBA

• Function Contient(Plage As Range, Texte As String) As Boolean: Déclare une fonction nommée Contient qui prend deux arguments : une plage de cellules (Plage) et le texte à rechercher (Texte). La fonction renvoie une valeur booléenne (Vrai ou Faux).
• InStr(1, Plage.Value, Texte, vbTextCompare): Utilise la fonction InStr pour rechercher Texte dans la valeur de la cellule Plage. vbTextCompare rend la recherche insensible à la casse.
• Contient = InStr(1, Plage.Value, Texte, vbTextCompare) > 0: Attribue Vrai à la fonction Contient si InStr renvoie une valeur supérieure à 0 (ce qui signifie que le texte a été trouvé), et Faux sinon.

Utilisation de la fonction personnalisée

Maintenant que la fonction Contient est créée, vous pouvez l'utiliser directement dans votre feuille Excel.

1. Dans la cellule B1, entrez la formule suivante : =Contient(A1;"écran")

2. Faites glisser la formule vers le bas jusqu'à la cellule B10.

Cette formule renverra VRAI si la cellule A1 contient le mot "écran" (insensible à la casse) et FAUX sinon.

Capture d'écran (description textuelle):

Une feuille Excel avec une colonne A contenant des noms de produits. La colonne B utilise la fonction =Contient(A1;"écran") et affiche TRUE ou FALSE selon que la cellule A correspondante contient le mot "écran" ou non.

Bonnes pratiques et astuces

• Insensibilité à la casse : Si vous utilisez CHERCHE, la recherche n'est pas sensible à la casse. Si vous avez besoin d'une recherche sensible à la casse, utilisez TROUVE ou la fonction VBA InStr avec l'option appropriée.
• Gestion des erreurs : Utilisez ESTNUM et SI pour gérer les erreurs #VALEUR! et rendre vos formules plus robustes.
• Caractères génériques : Utilisez les caractères génériques avec parcimonie, car ils peuvent ralentir le calcul si vous les utilisez sur de grandes plages de données.
• Fonctions personnalisées : Les fonctions personnalisées VBA offrent une grande flexibilité, mais elles nécessitent une certaine connaissance de VBA.
• Performance : Pour les très grandes feuilles de calcul, les fonctions VBA peuvent être plus performantes que les formules Excel complexes.

Erreurs à éviter

• Oublier la gestion des erreurs : Ne pas gérer l'erreur #VALEUR! peut rendre votre feuille de calcul difficile à lire.
• Confondre CHERCHE et TROUVE : N'oubliez pas que TROUVE est sensible à la casse, tandis que CHERCHE ne l'est pas.
• Utiliser des caractères génériques de manière excessive : Une utilisation excessive des caractères génériques peut affecter les performances d'Excel.
• Ne pas tester vos formules : Testez toujours vos formules avec différents exemples pour vous assurer qu'elles fonctionnent correctement.

En conclusion, bien qu'Excel ne possède pas de fonction "CONTIENT" dédiée, les fonctions CHERCHE, TROUVE, ESTNUM, SI, les caractères génériques et les fonctions personnalisées VBA offrent une flexibilité incroyable pour identifier et extraire des données spécifiques. En comprenant ces différentes méthodes et en appliquant les bonnes pratiques, vous pouvez maîtriser l'art de la recherche de texte dans Excel et gagner un temps précieux.