Comment vérifier si une cellule contient l'une des plusieurs valeurs dans Excel?
Dans de nombreux contextes professionnels, analytiques ou lors de la révision de données, vous pouvez avoir une liste de chaînes de texte dans la colonne A et souhaiter vérifier si chaque cellule contient l'une des valeurs spécifiées, telles que celles listées dans la plage D2:D7. Par exemple, dans les données d’enquête, les journaux ou les listes de produits, il est essentiel d’identifier si les entrées incluent des mots-clés pertinents, des codes produits ou des termes interdits. Si une cellule contient l'un des éléments de votre liste, Excel doit afficher Oui ; sinon, Non, comme le montre la capture d'écran ci-dessous. Cet article explique des méthodes pratiques pour vérifier si une cellule contient l’une de plusieurs valeurs présentes dans une autre plage, en proposant diverses approches selon la version d’Excel et les besoins de l’utilisateur.
Vérifier si une cellule contient l'une des plusieurs valeurs d'une liste avec des formules
Pour savoir si une cellule contient l'une des valeurs textuelles d'une autre plage, vous pouvez utiliser une formule matricielle efficace, même sur de grands jeux de données. Cette méthode est particulièrement utile lorsque vous désirez obtenir des résultats sous forme de valeurs logiques (Oui/Non) pour une utilisation dans d'autres formules, filtres ou tests logiques. Cette technique fonctionne dans la plupart des versions modernes d’Excel.
Saisissez la formule suivante dans une cellule vide (par exemple, B2, à côté de vos données d’origine), puis faites glisser la poignée de recopie vers le bas pour l’appliquer aux autres cellules selon vos besoins. Si la cellule contient l’une des valeurs textuelles de votre plage spécifiée, elle renvoie Oui ; sinon, Non. Voir la capture d’écran :
Astuces et remarques :
- Cette formule effectue une vérification non sensible à la casse. Si vous avez besoin d’une vérification sensible à la casse, envisagez d’utiliser une colonne d’aide ou de combiner des formules plus avancées.
- Pour obtenir un résultat « Oui » ou « Non » au lieu de Oui/Non, utilisez la formule adaptée suivante :
- D2:D7 correspond à votre plage de valeurs (la « liste »); A2 est la cellule à tester.
- Soyez attentif avec les cellules vides ou les données non textuelles, car la fonction RECHERCHE exige une entrée textuelle valide et les valeurs vides peuvent produire des résultats inattendus (« Oui »).
Afficher les correspondances si la cellule contient l'une des plusieurs valeurs d'une liste avec des formules
Il peut parfois être plus pertinent d’afficher quelles valeurs de la liste apparaissent réellement dans chaque cellule plutôt que de se limiter à un résultat Oui/Non. Par exemple, lors du balayage des descriptions de produit ou des commentaires pour des mots-clés spécifiques, il peut être utile de renvoyer toutes les valeurs trouvées pour une analyse ou un rapport approfondis. Utilisez la formule suivante pour afficher toutes les valeurs correspondantes, séparées par des virgules, comme illustré ici:
Saisissez cette formule dans une cellule vide (par exemple B2), et elle listera toutes les valeurs de D2:D7 retrouvées dans A2, séparées par des virgules:
Remarque : ici, D2:D7 est la collection de valeurs à rechercher, et A2 est la cellule à examiner.
Après avoir saisi la formule, appuyez sur Ctrl + Maj + Entrée. Ensuite, faites glisser la poignée de recopie vers le bas pour appliquer la formule aux autres lignes, comme le montre la capture de résultat:
- La fonction TEXTJOIN est disponible uniquement dans Excel2019 et Office365. Dans les versions antérieures d’Excel, utilisez la formule matricielle suivante: entrez-la dans une cellule vide, puis appuyez sur Ctrl + Maj + Entrée:
Faites glisser la formule vers la droite afin de couvrir suffisamment de colonnes pour capturer toutes les correspondances possibles, puis vers le bas pour chaque ligne. Les colonnes en surplus resteront vides s’il y a moins de correspondances. Ce format est particulièrement utile lorsque vous souhaitez que les correspondances soient listées dans des colonnes séparées:
En cas d’erreur, vérifiez soigneusement vos plages, assurez-vous que la plage D2:D7 est correcte et confirmez que vous utilisez le bon séparateur selon votre paramètre régional (virgule ou point-virgule).
Mise en évidence des correspondances si la cellule contient l'une des plusieurs valeurs d'une liste grâce à une fonctionnalité pratique
Si vous souhaitez mettre visuellement en évidence les mots-clés ou expressions qui correspondent à une valeur de votre liste dans chaque cellule, cela peut faciliter la reconnaissance des données importantes pour la revue ou l’action. La fonctionnalité Marquer les mots-clés de Kutools pour Excel est conçue pour ce scénario, vous permettant de mettre rapidement en évidence chaque occurrence de vos mots spécifiés dans la zone de données, sans écrire de formules ni de code VBA. Sur de grandes feuilles ou dans des jeux de données complexes, cette fonctionnalité est idéale lorsque l’inspection manuelle s’avère impraticable.
Après avoir installé Kutools pour Excel, suivez les étapes suivantes:
1. Allez dans Kutools > Texte > Marquer les mots-clés pour ouvrir la boîte de dialogue, comme illustré:
2. Dans la boîte de dialogue Marquer les mots-clés, procédez comme suit:
- Sélectionnez la plage de données cible via la zone Plage.
- Choisissez ou saisissez directement les mots-clés dans la zone Mot-clé (séparés par des virgules).
- Spécifiez une couleur de mise en évidence avec l’option Couleur de police des mots clés.
3. Cliquez sur OK. Tous les mots identiques dans la plage sélectionnée seront mis en évidence avec la couleur de police choisie:
- Cette fonctionnalité modifie directement la mise en forme d’affichage des mots-clés repérés, facilitant la revue ou le partage des résultats, y compris auprès d’équipes qui ne souhaitent pas lire des résultats de formule.
- Elle est particulièrement utile lorsque la liste de mots-clés ou la plage textuelle est très grande, ou si vous devez mettre en évidence plusieurs critères simultanément.
- Vérifiez toujours votre plage sélectionnée et les mots-clés saisis avant de confirmer pour éviter toute mise en évidence involontaire.
Vérifier si une cellule contient l'une des plusieurs valeurs à l'aide de la mise en forme conditionnelle
La mise en forme conditionnelle intégrée d’Excel est une autre méthode efficace pour mettre en évidence les cellules qui contiennent une valeur de votre liste spécifiée. Cette solution est idéale pour repérer rapidement les lignes pertinentes, notamment lors de la revue des données, de la vérification d’erreur ou dans les tâches de conformité. Contrairement à la fonctionnalité Marquer les mots-clés de Kutools, cette approche ne nécessite aucun complément; elle est accessible dans toutes les installations standards d’Excel.
Voici comment utiliser la mise en forme conditionnelle avec une formule:
- Sélectionnez les cellules de votre zone de données (par exemple, A2:A20) que vous souhaitez surveiller.
- Allez à l’onglet Accueil, cliquez sur Mise en forme conditionnelle > Nouvelle règle.
- Dans la boîte de dialogue Nouvelle règle de mise en forme, choisissez Utiliser une formule pour déterminer les cellules à mettre en forme.
- Saisissez cette formule, sachant que D2:D7 contient vos valeurs et que A2 est la première cellule de données:
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$7,A2)))>0- Cliquez sur Format, définissez la mise en forme désirée (par exemple, couleur de remplissage), puis appuyez sur OK.
Désormais, toutes les cellules contenant un élément de la liste D2:D7 seront automatiquement mises en évidence.
- Cette méthode est dynamique: si vous modifiez la plage D2:D7, la mise en forme s’adapte instantanément.
- La mise en forme conditionnelle est uniquement visuelle: elle marque les cellules pour l’affichage mais ne fournit pas de résultat dans une colonne séparée ni pour des calculs supplémentaires.
- La mise en forme conditionnelle basée sur une formule est puissante, mais avec des ensembles de données très importants, les performances peuvent ralentir à cause des calculs répétés.
- Attention, la fonction RECHERCHE n’est pas sensible à la casse. Pour rendre cette approche sensible à la casse, des méthodes plus avancées ou des colonnes d’aide peuvent être nécessaires.
Plus d’articles relatifs:
- Comparer deux ou plusieurs chaînes de texte dans Excel
- Si vous souhaitez comparer deux ou plusieurs chaînes de texte dans une feuille de calcul, avec ou sans distinction de la casse, comme le montre la capture d’écran suivante, cet article propose des formules utiles pour accomplir cette tâche dans Excel.
- Afficher le contenu d’une cellule si elle contient du texte dans Excel
- Si vous disposez d’une liste de chaînes de texte dans la colonne A et d’une ligne de mots-clés, vous devez vérifier si les mots-clés apparaissent dans la chaîne textuelle. Si les mots-clés apparaissent dans la cellule, ils sont affichés; sinon, la cellule reste vide, comme illustré ici.
- Compter le nombre de mots-clés contenus dans une cellule à partir d’une liste
- Pour compter le nombre de mots-clés présents dans une cellule en fonction d’une liste de cellules, la combinaison des fonctions SOMPROD, ESTNUM et RECHERCHE peut vous aider à résoudre ce problème dans Excel.
- Rechercher et Remplacer plusieurs valeurs dans Excel
- Habituellement, la fonctionnalité Rechercher et Remplacer vous permet de rechercher un texte spécifique et de le remplacer par un autre. Toutefois, il arrive parfois que vous deviez rechercher et remplacer plusieurs valeurs en même temps. Par exemple, remplacer tous les textes « Excel » par « Excel2019 », « Outlook » par « Outlook2019 », etc., comme montré ci-dessous. Cet article introduit une formule pour effectuer cette opération dans Excel.
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels