Atteindre la cellule de la valeur cherchée 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 :


Atteindre au clic sur une valeur cherchée

Dans des bases de données volumineuses dont la hauteur dépasse allègrement l'affichage des premières informations de la fenêtre Excel, il est intéressant de pouvoir déplacer automatiquement le scroll au clic sur une valeur cherchée, pour rejoindre la cellule concernée dans cette masse d'informations. Et nous allons le voir, c'est un petit calcul matriciel très simple qui opère pour recomposer les coordonnées de cette information convoitée.



Dans l'exemple illustré par la capture, des chiffres d'affaires réalisés par des commerciaux sont relatés. Les noms des commerciaux ainsi énumérés apparaissent en colonnes B et D. Cette énumération se poursuit sur quelques lignes en dessous de l'affichage autorisé par la fenêtre dans cette version zoomée. Sur la droite de ce tableau, l'utilisateur désigne l'un de ces commerciaux avec une liste déroulante. Deux cellules en-dessous, un calcul matriciel reconstruit les coordonnées de la cellule à atteindre pour les fournir à la fonction Lien_hypertexte et ainsi livrer une ancre cliquable. Et effectivement, dès que l'utilisateur clique sur ce lien, il est redirigé sur la cellule correspondante, quitte à déplacer l'affichage automatiquement, lorsque la cellule cible est hors écran.

Classeur Excel à télécharger
Pour la découverte de cette nouvelle astuce Excel, nous suggérons d'appuyer l'étude sur un classeur existant et offrant ce tableau ainsi que cette liste déroulante de choix.

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

Nous retrouvons bien le tableau des commerciaux avec leurs chiffres et la liste déroulante en cellule G4. La reconstruction des coordonnées de la valeur cherchée quant à elle, doit se faire en cellule G7, pour finalement fabriquer le lien cliquable d'accès direct. Le tableau des commerciaux est reconnu sous le nom tab. La zone Nom en haut à gauche de la feuille le confirme.

Coordonnées de la cellule cherchée
Nous le savons, pour recomposer les coordonnées d'une cellule, Excel propose la fonction Adresse. Nous devons lui indiquer l'indice de ligne en premier argument et l'indice de colonne en second. Ces indices doivent être trouvés par raisonnement matriciel conditionnel. Il est question de recouper la position trouvée dans une matrice résultante en fonction du commercial désigné avec l'indice de ligne et de colonne correspondants pour reformuler ces coordonnées dans les paramètres de la fonction Adresse.

• Avec la liste déroulante en G4, choisir le deuxième commercial (Doeuf John),
• Sélectionner la cellule de l'adresse à trouver en cliquant sur la case G7,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction pour les coordonnées suivie d'une parenthèse, soit : Adresse(,

En premier paramètre, nous devons lui indiquer le numéro de ligne de la cellule et en second, il s'agit bien entendu du numéro de colonne. Pour trouver ces indices, l'idée consiste à croiser une matrice conditionnelle, fonction du nom du commercial avec une matrice des numéros de ligne. Au croisement, nous devrions ne conserver effectivement que l'indice convoité.

• Inscrire la fonction du Maximum suivie d'une parenthèse, soit : Max(,

La matrice conditionnelle va répondre par des booléens. Seul l'un d'entre eux va repérer la position en ligne de la cellule. Pour l'extraire du lot, nous demandons donc la valeur la plus grande.

• A gauche de la barre de formule, cliquer sur le bouton Insérer une fonction (fx),

Nous activons ainsi l'assistant fonction. Il va nous aider à mieux appréhender le mécanisme à entreprendre.

• Dans la zone Nombre1, ouvrir une parenthèse pour accueillir la matrice conditionnelle,
• Désigner le tableau des commerciaux par son nom, soit : tab,
• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Puis, cliquer sur la cellule G4 du commercial choisi par l'utilisateur,
• Fermer alors la parenthèse de la matrice conditionnelle,

Aussitôt et comme vous le constatez, l'assistant répond par une matrice de booléens. Les quatre premières réponses concernent la non-concordance (Faux) avec les quatre cellules de la première ligne. Puis, la première cellule de la deuxième ligne répond favorablement avec le booléen Vrai. Il s'agit effectivement de l'emplacement du commercial cherché.

En recoupant cette matrice de résultats avec une matrice verticale des indices de ligne du tableau, nous allons multiplier chaque numéro respectif avec son booléen en regard. Donc, seule la multiplication par le booléen Vrai (1) va retourner une valeur. Cette valeur n'est autre que le numéro de ligne de la cellule cherchée.

• Taper le symbole de l'étoile (*) pour engager la multiplication,
• Ouvrir une parenthèse pour accueillir la matrice des numéros de ligne,
• Inscrire la fonction donnant l'indice de ligne, suivie d'une parenthèse, soit : Ligne(,
• Désigner le tableau des commerciaux par son nom, soit : tab,

Ainsi et grâce à ce raisonnement matriciel, ce sont tous les indices de ligne du tableau que nous engageons sous forme de matrice verticale en regard de la matrice des booléens.

• Fermer la parenthèse de la fonction Ligne,
• Puis, fermer la parenthèse de cette deuxième matrice,

Cette fois, les booléens sont bien transformés en chiffres dans la matrice résultante, rendant compte de la situation. Et seule une cellule concorde. Elle correspond à la première cellule de la deuxième ligne et indique ainsi que le commercial cherché est situé sur la cinquième ligne de la feuille. Comme ce calcul est intégré dans la fonction Max, c'est ce numéro qui va être isolé et utilisé comme indice de ligne de la fonction Adresse.

• Cliquer sur le bouton Ok de l'assistant fonction,

Comme le calcul n'est pas terminé, une alerte se déclenche.

• Cliquer sur le bouton Ok pour l'ignorer,

Dans la barre de formule, le point d'insertion clignote juste avant la parenthèse fermante de la fonction Max.

• Cliquer après cette parenthèse, soit à la toute fin de la syntaxe,

Nous sommes ainsi de retour dans les arguments de la fonction Adresse. Il nous reste encore à trouver le numéro de colonne de la cellule cherchée, à passer en second paramètre. Le principe est identique. Mais cette fois, nous allons recouper la même matrice conditionnelle que précédemment avec une matrice horizontale des numéros de colonne, grâce à la fonction Colonne.

• Taper un point-virgule (;) pour passer dans le deuxième argument de la fonction Adresse,
• Puis, construire l'expression suivante : Max((tab=G4)*colonne(tab)),
• Fermer la parenthèse de la fonction Adresse,
• Puis, valider la formule par le raccourci clavier CTRL + MAJ + Entrée,

Comme vous le constatez, lescoordonnées de la cellule cherchée sont parfaitement recomposées. Et bien entendu, si vous changez de commercial avec la liste déroulante, son adresseest automatiquement actualisée en cellule G7.

Remarque : A partir de la version 2019, il n'est pas nécessaire de valider le calcul par le raccourci CTRL + MAJ + Entrée. Excel comprend qu'il s'agit d'un raisonnement matriciel et le transforme naturellement.

Lien d'accès à la cellule cherchée
Il ne nous reste plus qu'à rendre cette adresse cliquable pour que l'utilisateur soit automatiquement dirigé sur la cellule cible. Pour cela, nous devons exploiter la fonction Excel Lien_hypertexte. En premier paramètre, nous devons lui passer les coordonnées de la cellule à atteindre. Certes, nous les avons déjà calculées. Mais souvenez-vous, ces coordonnées doivent être préfixées du nom du classeur entre crochets et du nom de la feuille suivi d'un point d'exclamation. C'est ainsi dans la syntaxe Excel que nous descendons des objets parents pour atteindre les objets enfants en cascade. En deuxième paramètre, nous devons indiquer le texte qui doit servir de lien, comme par exemple le texte Atteindre.

• En cellule G7, adapter la syntaxe comme suit, et la valider par CTRL + MAJ + Entrée :

=LIEN_HYPERTEXTE("[adresse-clic.xlsx]chercherClic!" & ADRESSE(MAX((tab=G4)*(LIGNE(tab))); MAX((tab=G4)*COLONNE(tab))); "Atteindre")

Désormais, si vous choisissez un commercial placé hors écran comme Houda Barrack et que vous cliquez sur le lien Atteindre, la cellule de son nom est automatiquement sélectionnée. De plus, l'affichage est calé sur cette dernière qui n'était pas visible jusqu'alors.