Surligner les valeurs recherchées avec Excel

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


Sujets que vous pourriez aussi aimer :


Surligner les valeurs cherchées avec Excel

Dans cette formation Excel, nous proposons de développer une astuce permettant de mettre en lumière toutes les lignes d'une base de données, concordant avec le ou les termes de recherche. Il ne s'agit donc pas d'extraire les informations correspondantes, mais bien de les repérer visuellement. Et comme vous le savez, ces identifications visuelles dynamiques s'opèrent par des règles aboutis de mise en forme conditionnelle.



La capture ci-dessus illustre la solution à atteindre. Une base de données référence des véhicules sur trois niveaux : L'immatriculation, la marque et le modèle. Une zone de saisie est placée sur la droite du tableau. En saisissant une information complète ou seulement un fragment, la correspondance doit être évaluée, quelle que soit la colonne de recherche. L'utilisateur peut vouloir identifier une immatriculation ou tous les véhicules d'une marque ou encore plus spécifiquement, un modèle en particulier. Dès que des correspondances sont trouvées, elles doivent surgir dans une mise en forme dynamique explicitement différente.

Classeur source et problématique
Pour développer un tel outil, nous avons besoin de données à manipuler.

• Télécharger le classeur surligner-valeurs-cherchees.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Cliquer ensuite sur le bouton Activer la modification du bandeau de sécurité,

Nous réceptionnons ainsi un classeur constitué d'une seule feuille nommée parc_auto. Les véhicules sont archivés dans la base de données, entre les colonnes B et D et les lignes 4 et 92.



La zone de recherche est placée sur sa droite en cellule F5. Il s'agit en fait d'une cellule fusionnée sur trois colonnes et deux lignes. Elle est destinée à accueillir les termes de recherche. Et comme nous le disions, il peut indifféremment s'agir d'une information sur la marque, le modèle ou l'immatriculation.

Identification multicritère
La colonne de recherche n'est donc pas spécifiée à l'avance. De plus, nous devons autoriser l'opérateur à saisir des fragments d'informations, comme le début d'une immatriculation. Ainsi, nous produirons une solution puissante et ergonomique. L'identification dynamique doit exploiter la mise en forme conditionnelle. Et c'est l'intégralité des lignes portant la valeur trouvée qui doivent être repérées. Pour répondre à un tel besoin, nous ne pouvons pas exploiter une règle toute faite. C'est une formule qui doit régir cette règle spécifique de format dynamique.

Pour savoir si l'information saisie se trouve dans le tableau de recherche, nous devons exploiter la fonction Excel Cherche. Nous l'avions démontrée dans la formation pour réaliser des extractions par fragments de textes. Elle requiert deux paramètres :

=Cherche(Texte_cherché; Texte_de_recherche)

En premier paramètre, le texte cherché n'est autre que la cellule destinée à la saisie en F5. Le texte de recherche concerne toutes les cellules de la base de données. Une mise en forme conditionnelle appliquée sur un tableau le parcourt chronologiquement. C'est ainsi que chaque case est passée en revue, pour l'analyse de la condition formulée. Mais nous allons le comprendre, pour repérer l'intégralité d'une ligne, nous allons devoir exploiter judicieusement les références absolues. Enfin et c'est important, une recherche vide doit être ignorée. Nous devons recouper deux conditions grâce à la fonction Et.

• Sélectionner la première cellule du tableau, soit B5,
• Tout en maintenant la touche Maj (Shift) enfoncée, cliquer sur la dernière, soit D92,

Cette technique permet d'inclure dans la sélection toutes les cellules placées entre la première et la dernière. En l'état, notre tableau est donc intégralement sélectionné.

• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir Nouvelle règle,

• Dans la boîte de dialogue qui suit, choisir le type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie qui se propose juste en-dessous,

Elle est destinée à recevoir la syntaxe du critère. Dans une règle de mise en forme conditionnelle, la condition est implicite. La fonction Excel Si ne doit donc pas être utilisée.

• Taper le symbole égal (=) pour débuter l'écriture de la règle,
• Saisir la fonction Et suivie d'une parenthèse, soit : ET(,

Comme vous le savez, elle permet d'énumérer les conditions à recouper.

• Sur la feuille Excel, cliquer sur la cellule de recherche, soit F5,

Elle apparaît intégralement figée par défaut : $F$5. C'est bien ce que nous souhaitons. Lorsque la règle effectuera son analyse sur l'ensemble des cellules du tableau, chacune d'elles devra être comparée à cette donnée saisie. Les références de la cellule F5 ne doivent donc pas suivre ce déplacement.

• Taper ensuite l'inégalité suivante: <>'',

Nous l'évoquions, l'analyse ne doit pas être déclenchée lorsqu'aucune recherche n'est soumise. Par défaut en effet, tous les résultats concorderaient. La première condition à remplir est spécifiée. Nous devons maintenant exprimer la suivante, celle de la concordance de recherche. Dans un premier temps, nous proposons de raisonner sur les immatriculations.

• Taper un point-virgule (;) pour l'énumération de la seconde condition,
• Taper le nom de la fonction de recherche suivi d'une parenthèse, soit : Cherche(,
• Sélectionner de nouveau la cellule de recherche, ce qui donne : $F$5,
• Taper un point-virgule (;) pour passer dans l'argument du texte de recherche,
• Sélectionner alors la première immatriculation, soit B5,

Comme précédemment, cette cellule apparaît naturellement figée : $B$5. Mais cette fois, ce n'est pas ce que nous souhaitons. Toutes les immatriculations doivent être passées en revue pour être comparées à la recherche formulée. Cette cellule doit donc se déplacer de ligne en ligne. Son dollar devant l'indice 5 doit disparaître. En revanche, pour que la mise en valeur s'applique sur la ligne entière, la condition doit être satisfaite sur une cellule, celle de l'immatriculation. Elle ne doit donc pas bouger en colonne. Son dollar devant l'indice B doit persister.

• Enfoncer deux fois la touche F4 du clavier pour déplacer le dollar, ce qui donne : $B5,

Si la touche F4 est inopérante, il vous suffit de placer les dollars au clavier.

• Fermer la parenthèse de la fonction Cherche puis fermer la parenthèse de la fonction Et,

La syntaxe complète du critère est donc la suivante :

=ET($F$5<>'';CHERCHE($F$5;$B5))

Lorsque la cellule de recherche n'est pas vide et que sa valeur est trouvée dans la colonne des immatriculations, une identification visuelle doit se déclencher pour repérer la ligne.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un jaune pâle puis activer l'onglet Police,
• Choisir un rouge rubis pour le texte et un style gras,
• Valider ces réglages en cliquant sur le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue qui résume l'alerte visuelle susceptible de se déclencher lorsque la double condition est vérifiée.

• Valider cette règle en cliquant sur le bouton Ok de la boîte de dialogue,
• Taper une immatriculation existante ou seulement son début en cellule F5, puis valider la saisie,

Aussitôt, les lignes portant le terme de recherche dans l'immatriculation surgissent. La mise en valeur définie s'applique bien sur l'intégralité des lignes concernées. Elles sont instantanément repérées.

Nous devons répliquer le même processus pour produire l'identification quelle que soit la nature du terme de recherche. Nous devons créer une règle d'équivalence sur la marque et une autre sur le modèle. Nous pourrions réaliser une seule formule pour inclure toutes ces conditions, grâce à la fonction Excel Ou. Mais nous pouvons aussi cumuler ces règles. C'est l'avantage de la mise en forme conditionnelle par rapport à un calcul. Une seule formule peut être bâtie en lieu et place alors que des règles peuvent s'empiler sur des mêmes plages.

Pour ajouter ces deux nouvelles règles, le tableau (B5:D92) doit bien sûr être préalablement sélectionné. Il s'agit ensuite de créer une nouvelle règle utilisant une formule. La première des deux concerne la marque :

=ET($F$5<>'';CHERCHE($F$5;$C5))



Il peut être intéressant de modifier les attributs de format pour sensibiliser sur le fait que la reconnaissance s'est adaptée à la nature de la recherche.

La deuxième de ces règles doit agir sur le modèle :

=ET($F$5<>'';CHERCHE($F$5;$D5))



En modifiant la nature des termes de recherche, vous avez le plaisir de constater le repérage dynamique instantané, dans des jeux de couleurs qui s'adaptent.