Recherches avec des termes recoupés et des mots exclus

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Recherches avec inclusions et exclusions

Sur des bases de données, il est forcément utile de pouvoir connaître les enregistrements correspondant avec des termes de recherche. Mais pour des résultats affinés, on peut vouloir exclure du décompte ceux portant certains mots à bannir. Cette astuce Excel montre comment réaliser ces tests.



Dans l'exemple illustré par la capture, nous travaillons sur un petit tableau de données. Il fera parfaitement l'affaire pour la démonstration de la technique. Il est constitué de quelques petites phrases. Sur sa droite, deux petites listes énumèrent des mots. La première représente les termes que chaque phrase doit proposer. La seconde mentionne ceux qu'elles ne doivent surtout pas porter. Toutes les phrases qui concordent avec ces critères recoupés et opposés sont marquées par l'indicateur Vrai. Toutes les autres sont estampillées avec l'indicateur Faux.

Source et procédure
Pour la mise en place de cette astuce, nous proposons de récupérer tout d'abord ce tableau.

• Télécharger le classeur compter-inclus-exclus.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous retrouvons donc le petit tableau avec les deux listes sur sa droite. Bien sûr, ces listes peuvent s'enrichir de nouveaux termes à inclure ou à exclure. Et dans l'idée, nous travaillons sur une grande base de données constituée de nombreux enregistrements. Quoiqu'il en soit, la technique reste la même. La deuxième colonne du tableau d'expressions est nommée Tests. Ce sont ses cellules qui doivent recevoir le calcul de recherche aux conditions croisées.

Comme il s'agit de vérifier à la fois la liste des termes présents et à la fois ceux qui ne doivent pas l'être, deux recherches doivent être engagées. La première doit réussir. La seconde ne doit pas aboutir. Pour croiser ces conditions, nous devons les englober dans la fonction de recoupement ET. Et bien sûr la fonction Excel Cherche est nécessaire à deux reprises. Cependant, cette dernière répond par une erreur lorsque la recherche est un échec. Cette erreur compromettrait le calcul global du test. L'astuce consiste à englober chaque recherche dans la fonction Excel Nb. Son rôle est de compter les résultats. Une erreur équivaut à zéro, indiquant que les termes interdits n'ont pas été trouvés.

• Sélectionner la case du premier test, soit la cellule C4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction de recoupement suivie d'une parenthèse, soit : Et(,
• Inscrire la fonction de dénombrement suivie d'une parenthèse, soit : Nb(,
• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Cherche(,
• En guise de texte cherché, désigner les termes inclus, soit la plage de cellules E4:E5,

Vous l'avez compris, nous entrons dans un raisonnement matriciel. Pour chaque cellule du tableau, nous allons partir à la recherche de tous les termes proposés dans la liste désignée.

• Enfoncer la touche F4 du clavier pour figer la plage de cellules, ce qui donne : $E$4:$E$5,

En effet, nous allons répliquer ce calcul sur les lignes du dessous. Et pour chaque cellule analysée, les termes de recherche à trouver sont immuablement inscrits dans cette plage qui ne doit donc pas bouger.

• Taper un point-virgule (;) pour passer dans l'argument du texte de recherche,
• Désigner la première expression en cliquant sur sa cellule B4,
• Fermer la parenthèse de la fonction Cherche,
• Puis, fermer la parenthèse de la fonction Nb,
• Inscrire alors le critère suivant : >0,

En effet, tous les mots de cette petite matrice doivent se trouver dans l'expression analysée. Si la fonction Cherche ne renvoie pas d'erreur, la fonction Nb va comptabiliser les retours concluants. Nous saurons donc que tous les termes demandés sont bien trouvés. Mais dans le même temps, nous devons nous assurer que les termes bannis ne sont pas présents. La technique est la même, mais le dénombrement de la recherche doit répondre par zéro, indiquant une recherche infructeuse.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Saisir de nouveau la fonction de dénombrement suivie d'une parenthèse, soit : Nb(,
• Saisir la fonction de recherche suivie d'une parenthèse, soit : Cherche(,
• Sélectionner les termes à exclure, soit la plage de cellules G4:G5,
• Pour les mêmes raisons que précédemment, figer la plage avec la touche F4 du clavier,
• Taper un point-virgule (;) pour passer dans l'argument du texte de recherche,
• Sélectionner de nouveau la première expression en cliquant sur sa cellule B4,
• Fermer la parenthèse de la fonction Cherche,
• Puis, fermer la parenthèse de la fonction Nb,
• Taper alors l'égalité suivante : =0,

En effet, les termes à exclure doivent générer une erreur transformée en dénombrement nul par la fonction Excel Nb.

• Fermer la parenthèse de la fonction Et,
• Puis, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

En effet, nous enclenchons un raisonnement qui engage toutes les cellules des plages sélectionnées pour les rechercher dans les expressions respectives. Le premier résultat tombe. Il est sanctionné par la mention Faux. Effectivement, la première expression contient bien les mots Jaune et Noir, mais elle contient aussi le mot Rouge. Celui-ci est à exclure. Donc, l'enregistrement n'est pas concordant.

• Double cliquer sur la poignée du résultat pour répliquer la logique sur tout le tableau,

Seules deux phrases recoupent les critères. Elles contiennent bien les mots cherchés sans les termes interdits. Ce résultat est tout à fait bluffant. C'est un petit calcul, certes matriciel, qui a permis cette prouesse. Il pourrait s'appliquer à une immense base de données et considérer une multitude de termes et exceptions. Sa syntaxe est la suivante :

{=ET(NB(CHERCHE($E$4:$E$5;B4))>0; NB(CHERCHE($G$4:$G$5; B4))=0)}

Notez néanmoins que si les termes refusés ne sont pas admis, il suffit que seul l'un des termes demandés soit trouvé pour que l'enregistrement soit considéré comme concordant. Par exemple, si vous remplacez le mot Jaune par le mot Gris, les deux mêmes enregistrements sont toujours marqués. Pourtant, ni l'un ni l'autre ne contient ce nouveau terme. L'astuce consiste à établir le critère de dénombrement par rapport au nombre de lignes de la matrice de recherche, comme suit :

{=ET(NB(CHERCHE($E$4:$E$5;B4))>LIGNES($E$4:$E$5)-1; NB(CHERCHE($G$4:$G$5; B4))=0)}



Dès lors, si vous remplacez le mot Jaune par le mot Abeille, seul un seul enregistrement subsiste. Il contient bien les deux termes cherchés sans les deux mots interdits.

Vous remarquez que les indicateurs booléens se parent d'une couleur en cohérence avec celles des listes. Ce sont deux petites règles de mise en forme conditionnelle qui régissent ces aspects. Pour les consulter, vous devez tout d'abord sélectionner l'un de ces résultats. Ensuite, vous devez cliquer sur le bouton Mise en forme conditionnelle dans la section Styles du ruban Accueil. Puis, en bas des propositions, vous devez opter pour la commande Gérer les règles. Dès lors, le gestionnaire de règles apparaît.



Comme vous pouvez le voir, ces règles sont toutes simples. Elles consistent à tester l'équivalence avec les résultats.

Enfin, si dans les termes à exclure, vous remplacez le terme Rouge par le mot Gris, vous remarquez que tous les résultats s'actualisent parfaitement et dynamiquement.



Le terme Rouge n'étant plus proscrit, plus d'enregistrements deviennent concordants.