Coordonnées de la cellule cherchée par calcul Excel

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


Sujets que vous pourriez aussi aimer :


Coordonnées de la donnée cherchée

Cette nouvelle astuce Excel montre comment extraire les coordonnées d'une cellule portant la valeur cherchée.



Dans l'exemple finalisé illustré par la capture, l'utilisateur choisit une personne à l'aide d'une liste déroulante, située sur la droite du tableau. Aussitôt, les coordonnées de la cellule concernée sont extraites et affichées. De plus, un clic sur ces coordonnées sélectionne automatiquement la case dans le tableau. Enfin, une mise en forme conditionnelle fait surgir en couleur la ligne ciblée.

Classeur source et présentation
Pour la démonstration de cette astuce, nous proposons de récupérer un classeur relativement abouti.

• Télécharger le classeur coordonnees-valeur-cherchee.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é,

Des candidats ayant reçu le résultat de leur évaluation sont recensés dans un tableau s'étendant de la colonne B à la colonne E.



Les plages des noms et des prénoms ont respectivement été nommées Noms et Prenoms. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel. Ces noms serviront à simplifier la syntaxe de la formule d'extraction. En cellule G4, une liste déroulante permet à l'utilisateur de désigner une personne par l'assemblage de son prénom et de son nom. En fonction de cette information, un calcul doit extraire les coordonnées de la cellule du nom correspondant en cellule G7.

Extraire les coordonnées
C'est la fonction Excel Cellule qui permet, entre autres, de délivrer les coordonnées d'une cellule. Pour cela, nous devons lui indiquer le paramètre Adresse en premier argument. Puis, nous devons pointer sur la case recherchée en second paramètre. Pour trouver cette case, nous devons exploiter la fonction d'extraction Index. Mais pour que cette fonction d'extraction pointe sur la bonne ligne, nous devons lui imbriquer la fonction de recherche Equiv. C'est elle qui isolera la ligne concernée par la demande effectuée par l'utilisateur par le biais de la liste déroulante.

• Sélectionner le résultat à trouver en cliquant sur sa cellule G7,
• Taper le symbole égal (=) pour initier la syntaxe de la formule d'extraction,
• Inscrire la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,

Comme vous le savez, en cas de recherche infructueuse, les fonctions d'extraction répondent par un message d'erreur. Ce phénomène peut notamment survenir lorsqu'aucune information n'est stipulée dans la cellule de la liste déroulante en G4. Nous choisissons donc d'intercepter ces erreurs pour les gérer.

• Inscrire la fonction pour les coordonnées suivie d'une parenthèse, soit : Cellule(,
• Taper l'argument adresse entre guillemets, soit : "adresse",
• Taper un point-virgule (;) pour passer dans l'argument de la référence à trouver,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la plage des noms par son intitulé, soit : Noms,

Nous l'avons dit, nous souhaitons livrer les coordonnées de la cellule du nom en corrélation avec la personne cherchée par l'utilisateur. L'extraction doit donc bien s'effectuer à partir de cette plage.

• Taper un point-virgule (;) pour passer dans l'argument du numéro de ligne,

Ce numéro de ligne, nous ne le connaissons pas. Il dépend de la personne cherchée. Nous pouvons le trouver avec la fonction Equiv.

• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Désigner la personne cherchée en cliquant sur sa cellule G4,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,

La donnée cherchée est un assemblage du nom et du prénom, tous deux séparés d'un espace. Nous devons donc assembler ces deux colonnes des noms et prénoms, elles-mêmes séparées d'un espace.

• Désigner la plage des noms par son intitulé, soit : Noms,
• La concaténer à un espace, soit : & " " ,
• Puis, terminer la concaténation avec la plage des prénoms, soit : & Prenoms,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Fermer la parenthèse de la fonction Index,
• Puis, fermer la parenthèse de la fonction Cellule,
• 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'anomalie,
• Fermer la parenthèse de la fonction SiErreur,
• Enfin, valider nécessairement la formule par le raccourci clavier CTRL + MAJ + Entrée,

Ce sont en effet deux plages de cellules qui sont engagées pour la recherche, soit deux matrices. Toutes leurs valeurs sont scrutées à la recherche d'une concordance avec l'assemblage du nom et du prénom choisis par le biais de la liste déroulante.Il s'agit donc d'un raisonnement matriciel. Et à ce titre, vous pouvez voir, dans la barre de formule de la cellule, les accolades qui encadrent la syntaxe de la formule matricielle.

{ =SIERREUR(CELLULE("adresse"; INDEX(Noms; EQUIV(G4; Noms & " " & Prenoms; 0))); "")}

Si nous n'avions dû engager la recherche que sur la colonne des noms, il se serait agi d'une extraction classique. Et dans ces conditions, nous n'aurions pas été contraints de valider la formule par ce raccourci clavier spécifique. Quoiqu'il en soit, le premier résultat tombe et il s'agit bien des coordonnées de la cellule du nom pour la personne cherchée. Et bien entendu, si vous changez la recherche à l'aide de la liste déroulante en cellule G4, les coordonnées de la cellule ciblée s'actualisent automatiquement.



Nous souhaiterions maintenant pouvoir cliquer sur ces coordonnées extraites pour sélectionner et atteindre dynamiquement la case en question. Pour cela, nous devons exploiter la fonction Excel Lien_Hypertexte. Elle requiert deux arguments. Le premier concerne l'adresse à atteindre. Le second concerne le texte à afficher dans la cellule.

• Pour cela, adapter la formule précédente comme suit :

=SIERREUR(LIEN_HYPERTEXTE("#" & CELLULE("adresse"; INDEX(Noms; EQUIV(G4; Noms & " " & Prenoms; 0))); "Atteindre"); "")

Vous devez absolument veiller à bien revalider la syntaxe par le raccourci clavier CTRL + MAJ + Entrée. Comme vous pouvez le voir, un Hashtag est concaténé en préfixe des coordonnées extraites. C'est grâce à lui que le lien hypertexte peut pointer en interne, soit dans la feuille elle-même. Et à ce titre, vous remarquez que les coordonnées de la cellule sont remplacées par un texte cliquable, en conformité avec le second argument fourni à la fonction Lien_Hypertexte.

• Changer la personne cherchée à l'aide de la liste déroulante en cellule G4,
• Puis, cliquer sur le lien hypertexte (Atteindre) actualisé par le calcul,

Comme vous pouvez le voir, la cellule du nom pour la personne cherchée est aussitôt sélectionnée.

Ligne de la cellule cherchée en couleur
Pour parfaire la solution, nous proposons de surligner automatiquement la ligne de la cellule cherchée. Pour que la mise en forme conditionnelle opère avec succès, elle doit simplement valider la double égalité entre les noms et les prénoms. Mais une astuce permet d'aller droit au but en vérifiant directement l'égalité sur les assemblages.

• Sélectionner toutes les données du tableau, soit la plage de cellules B4:E22,
• 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 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 du dessous pour l'activer,
• Dès lors, taper le symbole égal (=) pour initier la syntaxe de la règle de mise en forme,
• Sélectionner le premier nom du tableau en cliquant sur sa cellule C4, ce qui donne : $C$4,
• Enfoncer deux fois la touche F4 du clavier pour la libérer en ligne, soit : $C4,

L'analyse d'une mise en forme conditionnelle est chronologique. Certes l'étude doit bien porter sur la colonne C pour la comparaison des noms. C'est la raison pour laquelle nous la conservons figée. Mais tous les noms situés sur les lignes du dessous doivent être analysés. C'est la raison pour laquelle, nous libérons son indice de ligne.

• Taper un espace suivi du symbole de concaténation (&), suivi d'un nouvel espace,
• Puis taper cette fois un espace entre guillemets, soit : " ",
• Taper de nouveau un espace suivi du symbole de concaténation (&), suivi d'un nouvel espace,
• Sélectionner alors le premier prénom en cliquant sur sa cellule D4, ce qui donne : $D$4,
• Pour les mêmes raisons que précédemment, enfoncer deux fois la touche F4, soit : $D4,
• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Puis, désigner la personne choisie en cliquant sur sa cellule G4, ce qui donne : $G$4,

Cette fois, nous devons la conserver complètement figée. En effet, malgré la progression de l'analyse de la mise en forme conditionnelle, la comparaison doit toujours être effectuée par rapport à cette cellule. Lorsque la correspondance est trouvée, la ligne de la personne cherchée doit surgir en couleur.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Avec la seconde liste déroulante, choisir un vert assez vif pour la couleur du texte,
• Valider cet attribut de format avec le bouton Ok,
• De retour sur la première boîte de dialogue, valider la règle de mise en forme par Ok,

La ligne de la personne surgit instantanément. Et si vous changez la recherche avec la liste déroulante en cellule G4, la couleur se déplace pour repérer dynamiquement la position de la personne cherchée dans le tableau.

La règle de mise en forme conditionnelle que nous avons construite est donc la suivante :

=$C4 &" " & $D4=$G$4