Repérer les éléments sur une recherche multicritère

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


Sujets que vous pourriez aussi aimer :


Trouver sur une recherche multicritère

A l'occasion d'une précédente astuce Excel, nous avons vu comment repérer dans un tableau les lignes des éléments répondant favorablement à au moins l'un des critères émis, sur la base de plusieurs mots-clés de recherche formulés. Et pour cela, nous avions mis en place une matrice de critères à l'intérieur de la fonction Nb.Si : =SOMME(NB.SI(C4; {"* Lin *"; "* Cuir *"; "*Lacet *"}))>0.

Mais nous l'avions évoqué, cette technique bien qu'épatante, présente un handicap de taille. Les critères émis ne peuvent être formulés sous forme de variables, soit de cellules à désigner. Les termes cherchés doivent nécessairement être inscrits en dur, donc en version statique. Ici, nous proposons une nouvelle astuce permettant d'aboutir le même résultat mais sur la base de critères variables cette fois et au nombre évolutif à souhait.

Classeur source
Pour la démonstration de cette nouvelle technique de repérage, nous proposons d'appuyer l'étude sur un classeur offrant un tableau volumineux d'articles vestimentaires.

• Télécharger le classeur recherche-multicritere.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 découvrons un tableau relativement long qui s'étend jusqu'à la ligne 247. Les désignations des articles en vente sont énoncées en colonne C. C'est donc dans cette colonne que nous devons repérer en couleur les habits contenant au moins l'un des mots clés énumérés en colonne G, potentiellement de G4 jusqu'à G8.



Et pour que cette zone de critères puisse s'ajuster en hauteur en fonction de la quantité de termes de recherche inscrits, nous avons créé une plage nommée exploitant la fonction Excel Decaler.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,
• Dans la boîte de dialogue qui suit, cliquer sur le nom Criteres pour le sélectionner,

C'est ainsi que nous pouvons consulter la syntaxe de cette plage de hauteur variable, en bas de la boîte de dialogue, plus précisément dans la zone Fait référence à :

=DECALER(Articles!$G$4; 0; 0; NBVAL(Articles!$G$4:$G$8))

Cette plage débute donc à partir de la cellule G4 là où le premier critère de recherche est nécessairement mentionné. Et puis très simplement, nous faisons varier sa hauteur grâce à la fonction NbVal exploitée dans le troisième argument de la fonction Decaler. En comptant les éléments réellement inscrits entre les cellules G4 et G8, elle permet d'ajuster la hauteur de cette zone de critères en fonction du nombre de termes de recherche inscrits.

Identifier les lignes concordantes
C'est un raisonnement matriciel que nous devons enclencher pour considérer une matrice de termes de recherche. Ils doivent en effet tous être passés en revue tour à tour, comme le ferait un traitement récursif avec les boucles en VBA, pour trouver leurs potentielles présences dans chacune des désignations. Et c'est dans la colonne E de la rangée intitulée Réponses que nous devons repérer ces lignes concordantes.

• Sélectionner la première cellule du calcul à construire en cliquant sur la case E4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule de recherche,
• Puis, inscrire la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,

En effet, nous allons exploiter la fonction Equiv. Comme vous le savez, une fonction de recherche, lorsqu'elle ne trouve pas le ou les éléments demandés, répond par un message d'erreur. Nous préférons donc les intercepter pour ne pas les restituer.

• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Puis, taper le booléen Vrai,

C'est dans une utilisation détournée que nous engageons en effet la fonction de recherche. En deuxième argument, nous allons lui passer la matrice des critères à rechercher sur chaque désignation du tableau d'articles vestimentaires. Dès lors qu'au moins l'un des termes demandés sera trouvé, une réponse positive viendra recouper ce booléen. Et pour cela, nous avons besoin d'utiliser une fonction logique.

• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Inscrire la fonction pour tester les valeurs numériques, suivie d'une parenthèse, soit : EstNum(,

Ce que nous devons tester, c'est la recherche de chaque terme de la matrice de critères dans chaque désignation. Lorsqu'au moins l'un d'entre eux est trouvé, sa position est sanctionnée par un numéro, donc une valeur numérique, dans la matrice de retour.

• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Cherche(,

En guise de texte cherché dans ce premier argument de la fonction, c'est la matrice des mots clés que nous allons renseigner dans ce raisonnement matriciel.

• Désigner cette matrice de hauteur variable par son nom, soit : Criteres,
• Puis, taper un point-virgule (;) pour passer dans l'argument du texte dans lequel cherché,
• Cliquer alors sur la première des désignations du tableau d'articles vestimentaires, soit : C4,
• Fermer la parenthèse de la fonction Cherche,
• Puis, fermer la parenthèse de la fonction EstNum,

Nous sommes ainsi de retour dans les bornes de la fonction Equiv.

• Taper un point-virgule suivi du chiffre zéro : ;0, pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets pour garder la cellule vide en cas d'erreur,
• Fermer la parenthèse de la fonction SiErreur,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

La cellule du résultat reste vide et pour cause, la désignation du premier article ne comporte aucun des mots clés cherchés et énoncés dans la zone de critères.

• Cliquer et glisser la poignée du résultat jusqu'en cellule E247,

Aussitôt, vous voyez des lignes surgir en couleur verte. C'est une règle de mise en forme conditionnelle prédéfinie qui les fait réagir lorsque la désignation propose au moins l'un des termes cherchés.



Dans les premiers enregistrements effectivement, les mots clés Veste et Robe sont mentionnés. Cette règle est toute simple.

• Cliquer sur l'une des désignations du tableau, par exemple sur la cellule C5,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Gérer les règles,
• Dans la boîte de dialogue qui suit, cliquer sur le bouton Modifier la règle,

Vous pouvez ainsi consulter la syntaxe, fort simple et évidente : =$E4<>"". Cette règle est appliquée sur toutes les lignes du tableau des articles vestimentaires. Lorsque la cellule de la colonne E en regard n'est pas vide, cela signifie que la formule matricielle a trouvé la présence d'au moins l'un des mots clés dans la désignation. Dès lors, une couleur de texte verte est appliquée pour faciliter le repérage des enregistrements concordants.

Comprendre le raisonnement matriciel
Pour bien comprendre le raisonnement de cette formule matricielle, nous proposons de nous appuyer sur l'assistant fonction d'Excel.

• Cliquer par exemple sur le premier résultat concordant, soit sur la cellule E8,
• Dans sa barre de formule, cliquer dans le premier argument (VRAI) de la fonction Equiv,

C'est ainsi que nous allons pouvoir aiguiller l'assistant fonction que nous allons maintenant appeler.

• A gauche de la barre de formule, cliquer sur l'icône de l'assistant fonction (fx),

C'est ainsi qu'apparaît une boîte de dialogue dans laquelle les arguments de la fonction Equiv sont clairement inscrits dans des zones explicitement différentes. Ce sont les indications en regard de ces zones qui sont importantes et explicatives.



C'est une matrice qui est fournie en retour du tableau de recherche. Forcément, elle est de même dimension que la matrice de recherche. Et dans ce raisonnement matriciel, elle indique explicitement que l'un des termes est trouvé. Il s'agit du troisième ici en l'occurrence pour le mot clé veste. Et ce test numérique (Vrai) retourné par la fonction EstNum, permet à la fonction Equiv de déceler sa position en troisième rangée.

• Cliquer sur le bouton Annuler de la boîte de dialogue pour revenir sur la feuille Excel,

Il est temps maintenant de prouver la souplesse de la solution que nous avons montée, notamment grâce à la hauteur variable de notre zone de critères.

• Dans la zone de critères, ajouter le nombre 40 en cellule G7,

A validation, vous constatez aussitôt que toutes les lignes des articles proposant cette taille sont surlignées dans la base de données.

• En cellule G6, remplacer le terme Veste par le mot clé Chemisier,

En même temps que les vestes disparaissent des radars, hormis celle en taille 40, les chemisiers surgissent en couleur et sont donc automatiquement repérés. Nous avons donc bâti une solution efficace et souple pour faire ressortir tous les éléments d'une base de données croisant un nombre variable de critères.

Pour rappel et sans oublier l'emploi de la fonction decaler dans le nom défini, la syntaxe complète de la formule matricielle que nous avons construite est la suivante :

{=SIERREUR(EQUIV(VRAI; ESTNUM(CHERCHE(Criteres ; C4)) ; 0) ; "")}