Table des matières
Si vous connaissez au moins la fonction VPR (VLOOKUP) (sinon, exécutez d'abord ici), alors vous devez comprendre que ceci et d'autres fonctions similaires (VIEW, INDEX et SEARCH, SELECT, etc.) donnent toujours comme résultat Plus-value – le numéro, le texte ou la date que nous recherchons dans le tableau donné.
Mais que se passe-t-il si, au lieu d'une valeur, nous voulons obtenir un lien hypertexte en direct, en cliquant sur lequel nous pourrions sauter instantanément à la correspondance trouvée dans une autre table pour la regarder dans un contexte général ?
Disons que nous avons une grande table de commandes pour nos clients en entrée. Pour plus de commodité (bien que ce ne soit pas nécessaire), j'ai converti le tableau en un raccourci clavier dynamique "intelligent". Ctrl+T et a donné sur l'onglet Constructeur (Conception) son nom ongletCommandes:
Sur une feuille séparée Consolidé J'ai construit un tableau croisé dynamique (bien qu'il ne soit pas nécessaire qu'il s'agisse exactement d'un tableau croisé dynamique - n'importe quel tableau convient en principe), où, selon les données initiales, la dynamique des ventes par mois pour chaque client est calculée :
Ajoutons une colonne à la table des commandes avec une formule qui recherche le nom du client pour la commande en cours sur la feuille Consolidé. Pour cela, nous utilisons le bouquet classique de fonctions INDEX (INDICE) и PLUS EXPOSÉ (RENCONTRE):
Enveloppons maintenant notre formule dans une fonction CELLULE (CELLULE), à qui nous demanderons d'afficher l'adresse de la cellule trouvée :
Et enfin, nous mettons tout ce qui s'est avéré dans une fonction HYPERLINK (LIEN HYPERTEXTE), qui dans Microsoft Excel peut créer un lien hypertexte dynamique vers un chemin (adresse) donné. La seule chose qui n'est pas évidente, c'est qu'il faudra coller le signe dièse (#) au début de l'adresse reçue pour que le lien soit correctement perçu par Excel comme interne (de feuille à feuille) :
Maintenant, lorsque vous cliquez sur l'un des liens, nous allons instantanément accéder à la cellule avec le nom de l'entreprise sur la feuille avec le tableau croisé dynamique.
Pour le rendre vraiment bon, améliorons légèrement notre formule afin que la transition se produise non pas vers le nom du client, mais vers une valeur numérique spécifique exactement dans la colonne du mois lorsque la commande correspondante a été complétée. Pour ce faire, rappelons que la fonction INDEX (INDICE) dans Excel est très polyvalent et peut être utilisé, entre autres, au format :
= INDEX ( XNUMXD_plage; Numéro de ligne; Numéro_colonne )
Autrement dit, comme premier argument, nous pouvons spécifier non pas la colonne avec les noms des entreprises dans le pivot, mais toute la zone de données du tableau croisé dynamique, et comme troisième argument, ajouter le numéro de la colonne dont nous avons besoin. Il peut être facilement calculé par la fonction MOIS (MOIS), qui renvoie le numéro de mois de la date de l'offre :
Amélioration 2. Beau symbole de lien
Deuxième argument de la fonction HYPERLINK - le texte affiché dans une cellule avec un lien - peut être rendu plus joli si vous utilisez des caractères non standard des polices Windings, Webdings et autres au lieu des signes banals ">>". Pour cela, vous pouvez utiliser la fonction SYMBOLE (CARBONISER), qui peut afficher les caractères par leur code.
Ainsi, par exemple, le code de caractère 56 dans la police Webdings nous donnera une belle double flèche pour un lien hypertexte :
Amélioration 3. Mettez en surbrillance la ligne actuelle et la cellule active
Eh bien, pour la victoire finale de la beauté sur le bon sens, vous pouvez également joindre à notre fichier une version simplifiée mettant en évidence la ligne actuelle et la cellule vers laquelle nous suivons le lien. Cela nécessitera une macro simple, que nous accrocherons pour gérer l'événement de changement de sélection sur la feuille Consolidé.
Pour cela, faites un clic droit sur l'onglet de la feuille Résumé et sélectionnez la commande Voir code (Voir code). Collez le code suivant dans la fenêtre de l'éditeur Visual Basic qui s'ouvre :
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = -4142 Cells(ActiveCell.Row, 1).Resize(1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.ColorIndex = 44 End Sub
Comme vous pouvez facilement le voir, ici, nous supprimons d'abord le remplissage de toute la feuille, puis nous remplissons toute la ligne du résumé avec du jaune (code de couleur 6), puis de l'orange (code 44) avec la cellule actuelle.
Désormais, lorsqu'une cellule à l'intérieur de la cellule de résumé est sélectionnée (cela n'a pas d'importance - manuellement ou en cliquant sur notre lien hypertexte), la ligne et la cellule entières avec le mois dont nous avons besoin seront mises en surbrillance :
Beauté 🙂
PS N'oubliez pas d'enregistrer le fichier dans un format prenant en charge les macros (xlsm ou xlsb).
- Créer des liens externes et internes avec la fonction HYPERLINK
- Création d'e-mails avec la fonction HYPERLINK