Recherches Excel sur des colonnes non définies

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


Sujets que vous pourriez aussi aimer :


Critères de recherche indéfinis

Dans la formation Excel précédente, nous avons appris à pointer et à sélectionner la ligne d'un élément recherché, au choix d'une référence dans une liste déroulante. Pour cela, nous avons exploité la fonction Excel Lien_Hypertexte. Grâce aux noms donnés aux plages de cellules, elle permet notamment de rediriger vers une adresse ainsi définie.



Fort de ces acquis, nous souhaitons aller plus loin. Désormais, la recherche doit pourvoir s'effectuer sur n'importe quelle colonne du tableau Excel. L'utilisateur tape un mot clé dans la zone de recherche. S'il est trouvé dans la colonne identifiant, celle du nom ou encore du prénom, la ligne de l'enregistrement doit être repérée et sélectionnée. Bref, nous souhaitons bâtir un système de recherche automatique, ergonomique et puissant.

Source et problématique
Pour la mise en oeuvre de la solution, nous proposons tout d'abord de réceptionner des données structurées dans un tableau Excel existant.

• Télécharger le classeur recherches-multiples-champs.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous retrouvons le même tableau que celui de la formation précédente. Des clients sont recensés sur sept champs, entre les colonnes B et H. La cellule G3 doit permettre à l'utilisateur de cliquer sur le lien proposé afin d'atteindre la ligne correspondant au terme de recherche.

Mais ici, il n'est plus question de liste déroulante des identifiants. La recherche est ouverte et donc plus complexe à solutionner. En cellule E3, l'utilisateur émet un critère. Il peut par exemple s'agir du nom du client, de son prénom ou encore de son identifiant. La formule doit s'en accommoder. Elle doit déceler la colonne concernée, trouver l'information et proposer le lien interne de redirection vers sa ligne.

Les fonctions de recherche et les calculs matriciels sont à l'honneur.

Identifier la colonne de recherche
La solution consiste à décomposer le calcul en plusieurs étapes. Nous proposons tout d'abord de réaliser un repérage de la colonne potentielle de recherche, en fonction du terme tapé par l'utilisateur. La fonction Excel Equiv est dédiée :

=Equiv(Valeur_cherchée; Colonne_de_recherche; Mode_de_correspondance)

En fonction d'une valeur cherchée à fournir en premier paramètre, dans une colonne de recherche à indiquer en deuxième paramètre, elle retourne l'indice de ligne de la correspondance trouvée. La valeur cherchée est le terme saisi par l'utilisateur. La colonne de recherche correspond à chacune des colonnes du tableau. Il va donc falloir répliquer la formule autant de fois qu'il y a de colonnes. Lorsque le terme n'est pas trouvé, la fonction Equiv retourne une erreur : #N/A. Nous l'utiliserons à bon escient. Nous saurons ainsi que la colonne en question n'est pas celle à considérer.

Nous proposons de bâtir ce calcul intermédiaire au-dessus du tableau, en ligne 6.

• Sélectionner la cellule B6 pour établir la recherche sur la première colonne,
• Taper le symbole égal (=) pour initier le calcul,
• Saisir la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Cliquer sur la cellule E3 pour spécifier la valeur cherchée,
• Enfoncer la touche F4 du clavier pour figer ses références,

En effet, cette formule doit ensuite être répliquée sur les autres colonnes du tableau. La recherche devra bien être réalisée dans ces nouvelles rangées. Mais la valeur cherchée est toujours la même. Elle ne change pas de place, elle doit donc être figée.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Saisir les références ou sélectionner la plage de cellules B8:B45,
• Taper un point-virgule (;) pour passer dans le dernier argument de la fonction Equiv,
• Saisir le chiffre 0 pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Ajouter sept unités à ce résultat, soit : + 7,

En effet, nous désignons des colonnes de recherche dont l'énumération débute à partir de la ligne 8. La fonction Equiv va retourner un indice de ligne relatif à ce tableau. Pour raisonner en valeurs absolues, soit sur les vraies coordonnées des cellules, nous ajoutons les 7 unités correspondant aux 7 lignes manquantes du dessus.

• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Comme vous le savez, cette technique permet de conserver active la cellule du résultat. Ainsi et dans l'enchaînement, nous allons pouvoir répliquer sa logique sans devoir la resélectionner.

Pour l'instant, la recherche retourne une erreur : #N/A. La recherche n'est pas fructueuse dans cette colonne et pour cause, aucun mot clé n' a encore été tapé en E3.

• Cliquer et glisser la poignée du résultat à l'horizontale jusqu'en colonne H,
• En cellule E3, taper par exemple la référence C6 puis valider,

Instantanément, le repérage en première colonne se déclenche. Il indique explicitement que cet identifiant est trouvé dans la colonne et plus précisément en ligne 13.

• En cellule E3, taper désormais le nom d'un client, par exemple : Rouana et le valider,

Aussitôt le repérage en première colonne est neutralisé tandis que celui de la troisième colonne se déclenche. Il indique que le nom cherché est trouvé dans cette colonne, en ligne 10.



Ce calcul intermédiaire retourne donc deux indications précieuses. Il renseigne sur la colonne qu'il convient de considérer. Il retourne l'indice de ligne de la valeur cherchée. La syntaxe de la formule construite est la suivante :

=EQUIV($E$3;B8:B45;0) + 7

Identifier l'indice de colonne
Comme vous le savez très bien, une cellule à pointer par un lien est définie par ses références. Ses références sont représentées par un indice de ligne et un indice de colonne. L'indice de ligne est connu. Il est désormais nécessaire, dans ce flot de résultats d'erreurs, d'identifier explicitement le numéro de colonne du seul calcul qui retourne une valeur avec succès.

Pour cette mise en oeuvre, il s'agit de réaliser un calcul matriciel avec la fonction Equiv sur la ligne 6 des résultats. L'objectif est de repérer l'indice de colonne de la valeur numérique au milieu des erreurs. Cette fois, il ne s'agit pas de chercher une valeur mais le résultat d'un test. Est-ce que la valeur numérique est trouvée ? Donc, en premier argument de la fonction Equiv, nous devons taper le chiffre 1. Il signifie Vrai. Il s'agit du résultat attendu sur le calcul matriciel à honorer en deuxième argument.

Nous proposons de bâtir ce second calcul intermédiaire en cellule I6.

• En I6, taper la formule suivante : =EQUIV(1;(B6:H6>0)*1;0)+1,
• Puis, valider nécessairement le calcul par le raccourci clavier CTRL + MAJ + Entrée,

C'est cette combinaison de touches qui le transforme en calcul matriciel. Et à ce titre, vous notez l'apparition de crochets dans la syntaxe, affichée dans la barre de formule de la cellule :

{=EQUIV(1;(B6:H6>0)*1;0)+1}

En deuxième paramètre de la fonction Equiv, nous transmettons un critère sur une matrice. Le calcul matriciel raisonne sur chaque colonne respective qu'il compare à la condition émise. La multiplication par un (*1), consiste à forcer le résultat en valeur numérique. Lorsque la condition est satisfaite, la fonction Equiv retourne l'indice relatif de la colonne repérée. Comme la première colonne de la feuille est exclue de la sélection, nous ajoutons une unité au résultat renvoyé. Ainsi et comme précédemment, nous continuons de raisonner sur des coordonnées absolues.

Le résultat retourné est le chiffre 4, marquant la colonne D des noms.

• En cellule E3, taper désormais le prénom Anne, puis le valider,

La colonne repérée par le premier calcul se déplace, actualisant au passage l'indice de ligne. Le second calcul intermédiaire réagit à ce changement pour marquer la cinquième colonne, soit la colonne E des prénoms.

Lien de redirection vers la cellule cherchée
Comme vous le savez, c'est la fonction Excel Lien_Hypertexte qui permet de pointer en interne sur une cellule, dont les références préfixées du symbole diése (#) lui sont passées en premier paramètre. Ces références, nous pouvons les reconstruire grâce à la fonction Adresse :

=Adresse(Numéro_de_ligne; Numéro_de_colonne)

Le numéro de ligne doit être indiqué en premier paramètre. Le numéro de colonne doit être renseigné en second paramètre. Et ces indices, nous les connaissons grâce aux deux calculs intermédiaires précédents.

• Sélectionner la cellule G3,
• Puis, taper la formule suivante :

=LIEN_HYPERTEXTE('#' & ADRESSE(INDIRECT(ADRESSE(6;I6));I6); 'Rechercher')

Explications : En premier argument de la fonction Lien_Hypertexte, nous préfixons tout d'abord les références à construire du symbole dièse pour pointer en interne. Puis, nous reconstruisons les coordonnées de la cellule à pointer grâce à la fonction Adresse et aux calculs intermédiaires. Nous exploitons fort logiquement cette fonction Adresse à deux reprises.

La fonction Adresse imbriquée est la suivante : ADRESSE(6;I6). Nous lui passons la ligne 6 du premier calcul intermédiaire et la cellule I6 du second qui renvoie l'indice de colonne. Il en résulte la cellule du premier résultat, par exemple D6. Celle-ci renseigne sur l'indice de ligne de la correspondance trouvée. Nous l'imbriquons dans la fonction Indirect pour interpréter non pas ses coordonnées mais son résultat, soit l'indice de ligne. De fait, nous transmettons à la première fonction Adresse le numéro de ligne de la cellule à sélectionner. Il ne reste plus qu'à lui transmettre la colonne, ce que nous faisons en deuxième argument avec la cellule I6. Enfin, en second argument de la fonction Lien_Hypertexte, nous indiquons entre guillemets, le texte cliquable à afficher dans la cellule ('Rechercher').



A validation de la formule, vous notez effectivement l'apparition du lien. Que vous tapiez, un nom, un prénom ou un identifiant, au clic, c'est toujours la bonne cellule qui est sélectionnée dans sa colonne.

Pour des raisons de clarté, nous avons décomposé le raisonnement en deux calculs intermédiaires. Mais nous pourrions très bien intégrer le second, directement dans la formule finale. Pour cela, il suffit simplement de remplacer les références à sa cellule I6, par son calcul, à deux reprises :

{=SIERREUR(LIEN_HYPERTEXTE('#' & ADRESSE(INDIRECT(ADRESSE(6;EQUIV(1;(B6:H6>0)*1; 0)+1)); EQUIV(1;(B6:H6>0)*1;0)+1); 'Rechercher');'Rechercher')}

Par contre, il est bien entendu impératif de valider cette formule globale par le raccourci clavier CTRL + MAJ + Entrée. En effet, elle fait intervenir les calculs matriciels prévus à l'origine en I6. Nous en avons même profité pour intégrer la syntaxe dans la fonction SiErreur. De fait, nous gérons les anomalies en cas de non-correspondance ou de critère absent.



Au travers de quelques essais, vous constatez la puissance de la solution fournie, uniquement par calculs. N'importe quelle colonne est potentiellement concernée comme pour la recherche d'une ville.

Repérer la ligne de la cellule sélectionnée
Contrairement à la formation précédente, nous ne raisonnons pas sur des plages de cellules nommées. De fait, l'intégralité de la ligne du résultat concordant n'est pas sélectionnée. Seule la cellule du critère renseigné est repérée. C'est pourquoi nous proposons d'adjoindre une mise en forme conditionnelle à cette application. L'objectif est de repérer visuellement et dynamiquement, l'intégralité de la ligne pour la cellule sélectionnée. Mais comme la colonne n'est pas connue à l'avance, nous devons vérifier les cas potentiels. Il peut s'agir d'une correspondance sur le nom, le prénom, l'identifiant ou encore la ville et pourquoi pas le mail.

La fonction Excel OU permet d'énumérer les critères à combiner. Si l'un d'entre eux est vrai, la condition globale est considérée comme satisfaite. C'est donc elle que nous devons exploiter pour construire la règle de mise en forme conditionnelle.

• Cliquer sur le premier identifiant du tableau pour sélectionner sa cellule B8,
• Tout en maintenant la touche MAJ enfoncée, cliquer sur le dernier mail, soit la cellule H45,

Grâce à cette technique, nous englobons dans la sélection, toutes les cellules comprises entre la première et la dernière. Il s'agit de l'intégralité du tableau sans les en-têtes. En effet, à l'instar d'une mise en forme classique, une mise en forme conditionnelle requiert de désigner les cellules sur lesquelles les formats dynamiques doivent être enclenchés.

• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie qui s'offre juste en-dessous, pour l'activer,
• Saisir alors la règle suivante :

=OU($B8=$E$3; $D8=$E$3; $E8=$E$3; $G8=$E$3)

Grâce à la fonction OU, nous énumérons donc quatre critères d'égalité. Il suffit que l'un d'entre eux soit vérifié pour que le format dynamique, à définir, se déclenche. Dans l'ordre, nous vérifions si le terme tapé correspond : à l'identifiant ($B8), au nom ($D8), au prénom ($E8)ou encore à la ville ($G8).

Comme une mise en forme conditionnelle raisonne chronologiquement, de la première cellule sélectionnée à la dernière, nous posons chacun de ces critères sur la première ligne (8). Ainsi, elles seront toutes passées en revue. Et pour chaque ligne, la condition doit être posée sur la cellule de la colonne concernée. Nous devons donc la figer en colonne et la laisser se déplacer vers le bas, donc en ligne. Le terme tapé en E3 quant à lui ne doit pas bouger. Il n'existe que dans cette cellule. C'est la raison pour laquelle nous conservons les deux dollars.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un jaune pâle par exemple,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Dans la liste déroulante des couleurs, choisir un rouge foncé,
• Dans la zone Style, choisir le gras,
• Puis, cliquer sur le bouton Ok pour valider ces réglages d'attributs de format,

Nous sommes ainsi de retour sur la première boîte de dialogue qui résume parfaitement le contexte. Lorsque l'un des quatre critères énoncés est vérifié, la ligne repérée doit se parer des attributs de mise en forme définis.



Ici, nous avons choisi un remplissage en dégradé. Ce réglage peut s'opérer dans l'onglet Remplissage de la seconde boîte de dialogue, en cliquant sur le bouton Motifs et textures.

• Cliquer sur le bouton Ok de la première boîte de dialogue pour valider ces réglages,
• En E3, taper un nom placé en dehors des bornes de l'écran, comme : Dagin,
• Puis, cliquer sur le lien Rechercher,

En même temps que l'affichage se décale sur la ligne sélectionnée, cette dernière se pare automatiquement d'une mise en forme qu'on ne peut pas manquer.



Bien sûr, pour parfaire l'application, il convient de masquer la ligne 6 par clic droit sur son étiquette et masquer dans le menu contextuel.