Sommaire automatique sans code VBA Excel

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


Sujets que vous pourriez aussi aimer :


Sommaire automatique

Dans le volet précédent, grâce notamment à la fonction Lire.Classeur dans un nom de formule, nous avons appris à lister automatiquement tous les noms des feuilles composant un classeur. Pour cela, nous avons imbriqué le nom de formule dans un calcul matriciel. Grâce à ces acquis, nous sommes maintenant en mesure de bâtir un sommaire automatique sur une feuille d'accueil.



Et c'est bien ce qu'illustre le résultat acheminé proposé par la capture. Au clic sur un lien, l'utilisateur est directement conduit sur la feuille dont le nom d'onglet cliquable a été recomposé automatiquement.

Classeur source et présentation
Pour la création de ce sommaire automatique, nous proposons d'appuyer l'étude sur les travaux aboutis à l'occasion du volet précédent. Ainsi, nous allons récupérer la formule dressant la liste automatique des noms des feuilles.

• Télécharger le classeur sommaire-automatique.xlsm 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é,

Nous trouvons un tableau de deux colonnes. La première énumère effectivement les noms des onglets composant le classeur, à l'exception du nom de la première feuille nommée Synthese. Et bien entendu, si vous ajoutiez de nouvelles feuilles à ce classeur, elles seraient intégrées à la suite de l'énumération, moyennant une actualisation du calcul. C'est ce que nous avons démontré dans le volet précédent. La seconde colonne de ce tableau consolide les résultats de synthèse en rapatriant les informations éparpillées dans les différentes feuilles.

Listing des onglets du classeur
Avant de débuter la conception du sommaire cliquable, il est important de faire un état des lieux des travaux déjà en place. La compréhension du processus en dépend.

• Sur la feuille Synthese, cliquer sur la cellule D4 pour la sélectionner,

En consultant la barre de formule, vous pouvez apprécier le calcul en place :

{=SI(LIGNES($1:2)<=NBVAL(nomF); INDEX(STXT(nomF; TROUVE("]"; nomF)+1; 100); LIGNES($1:2)); "")}

Il s'agit d'une formule matricielle. Les accolades encadrant la syntaxe en attestent. Avant d'entrer simplement dans les explications, vous remarquez qu'un nom est répété à trois reprises. Il porte l'intitulé nomF. Sa présence est fondamentale. Sans lui, l'énumération des noms de feuille ne pourrait exister.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,

Une boîte de dialogue apparaît. Un seul nom de formule existe. De fait, il est sélectionné par défaut. Il porte bien l'intitulé nomF. Et si vous consultez la zone Fait référence à en bas de la boîte de dialogue, vous pouvez apprécier sa syntaxe : =TRANSPOSE(LIRE.CLASSEUR(1)).

Avec la valeur 1 en paramètre, la fonction Excel Lire.Classeur renvoie un tableau horizontal constitué des noms des feuilles du classeur. Grâce à la fonction Transpose, nous le transformons en un tableau vertical pour les besoins de la formule matricielle sur laquelle nous allons revenir.

• Cliquer sur le bouton Fermer de la boîte de dialogue pour revenir sur la feuille,

Désormais, il est donc temps de comprendre comment la formule matricielle restitue les noms des onglets, sans celui de la première feuille et sans générer d'erreur lorsque la fin de la liste est atteinte.

Tout d'abord, un test est opéré grâce à la fonction Si : SI(LIGNES($1:2)<=NBVAL(nomF)... Il exploite une matrice virtuelle d'une hauteur de deux lignes au départ (LIGNES($1:2)). Mais comme sa borne inférieure n'est pas figée, le nombre de ses lignes augmente avec la réplication du calcul sur les lignes du dessous. Et la fonction Lignes renvoie le nombre de lignes de cette matrice. Tant que ce nombre est inférieur à celui du nombre de lignes du tableau des noms de feuilles (<=NBVAL(nomF)), nous savons qu'il existe un nom d'onglet à restituer.

C'est la raison pour laquelle, nous exploitons la fonction d'extraction Index dans la branche Alors de la fonction Si. Elle recherche sur le tableau des noms des feuilles. Mais celui-ci est retravaillé avec la fonction Excel Stxt. Son but est de ne conserver que les intitulés des feuilles. En effet, par défaut la fonction Lire.Valeur préfixe chaque feuille du nom du classeur. Et ce nom est marqué entre crochets. C'est la raison de la présence de la fonction Trouve en deuxième paramètre de la fonction Stxt (TROUVE("]";nomF)+1). Elle retourne la position de ce crochet fermant après lequel il est question de prélever le reste de la chaîne pour ne conserver que les noms des feuilles.

Ensuite, en deuxième paramètre de la fonction Index, nous renseignons sur l'indice de ligne pour réaliser l'extraction, toujours grâce à une matrice virtuelle dont le nombre retourné grossit avec le calcul (LIGNES($1:2)). Ainsi, au fil de la réplication et tant que le critère est honoré, donc tant que la fin de la liste n'est pas atteinte, nous extrayons la deuxième feuille, puis la troisième, la quatrième etc...

Enfin, dans la branche Sinon de la fonction Si, nous conservons vide la cellule du résultat (""), lorsque le critère n'est plus honoré, donc lorsque la fin de la liste des noms est dépassée.

Pointer sur une cellule
Désormais donc, il est question de rendre cliquables ces noms d'onglets ainsi restitués. Pour cela et vous le savez, Excel offre la fonction Lien_Hypertexte. Elle ne requiert que deux arguments :

=Lien_Hypertexte(Adresse; Texte_à_afficher)

Le premier correspond à l'adresse à atteindre. Le second représente le texte cliquable à afficher. Excel appelle cet argument nom_convivial.

• Sélectionner par exemple la cellule B5,
• Puis, construire et valider la formule suivante : =LIEN_HYPERTEXTE("Janvier!A1"; "Janvier"),

Elle est donc destinée à atteindre la cellule A1 de la feuille Janvier au clic sur le lien généré. Pour cela, nous respectons bien la syntaxe Excel en premier paramètre. Nous préfixons les coordonnées de la cellule cible du nom de la feuille de destination, suivie d'un point d'exclamation.

• Cliquer sur le lien ainsi créé par la formule,

Comme vous pouvez le voir, une alerte apparaît. Elle n'est pas de bon augure. Et si vous cliquez sur le bouton Continuer, un message d'échec surgit. La cible ne peut être atteinte. La raison est simple est c'est une imposition. Non seulement la cellule cible doit être préfixée du nom de la feuille, mais ce dernier doit lui-même être préfixé du nom du classeur entre crochets.

• Adapter la précédente syntaxe comme suit :

=LIEN_HYPERTEXTE("[sommaire-automatique.xlsm]Janvier!A1"; "Janvier")

• La valider puis cliquer sur le lien généré,

Cette fois, la vue change. Le focus est effectivement donné à la cellule A1 de la feuille Janvier. Or dans la formule de la feuille synthèse, le listing des onglets est fourni avec des fonctions (Stxt et Trouve) s'échinant à supprimer ce préfixe. Cette syntaxe est parfaite pour le nom convivial, soit pour le second argument de la fonction Lien_Hypertexte concernant le texte cliquable à afficher. Mais pour le premier argument de l'adresse à atteindre, nous devons ajuster cette syntaxe en éliminant l'intervention des fonctions Stxt et Trouve.

Remarque : Vous notez que des liens cliquables de retour sur la feuille d'accueil, sont prévus en cellule A1 de chacune des autres feuilles du classeur.

Liens du sommaire
Nous l'avons dit, nous devons ajuster la formule présente en première colonne du tableau de la feuille Synthese. Elle ne doit plus se contenter d'afficher les noms des feuilles. Elle doit les transformer en liens pour les atteindre directement au clic de la souris. Et pour cela, il s'agit donc d'intégrer la fonction Lien_Hypertexte dans la construction.

• En bas de la fenêtre Excel, cliquer sur l'onglet Synthese pour revenir sur la feuille d'accueil,
• Cliquer de nouveau sur la cellule D4 pour la sélectionner,
• Dans la barre de formule, cliquer après le premier point-virgule,

Nous y plaçons ainsi le point d'insertion. Nous sommes donc positionnés dans la branche Alors de la fonction Si. C'est à cet emplacement que nous devons générer les liens cliquables.

• Inscrire la fonction pour le lien cliquable suivie d'une parenthèse, soit : LIEN_HYPERTEXTE(,

La syntaxe qui suit concerne le nom convivial. Elle restitue uniquement les noms des feuilles sans le nom du classeur en préfixe. Nous devons nous en inspirer pour l'alléger et récupérer ce préfixe. Pour cela et nous l'avons dit, nous devons nous débarrasser des traitements opérés par les fonctions Stxt et Trouve.

• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Inscrire le nom de la formule en guise de matrice de recherche, soit : nomF,

Nous indiquons ainsi à la fonction d'extraction de rechercher dans le tableau des noms de feuille.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne de la fonction Index,
• Puis, construire la matrice virtuelle suivante : Lignes($1:2),

Attention de bien respecter la présence du dollar devant l'indice 1. Nous l'avons déjà évoqué, nous passons une matrice de deux lignes à la fonction Lignes. Celle-ci compte les lignes d'un tableau. Elle va donc retourner le chiffre 2 pour le premier calcul. L'extraction du nom de la première feuille se fera donc à partir de la position 2, excluant ainsi la feuille d'accueil nommée synthèse. Et comme la borne inférieure (2) de cette matrice n'est pas figée, au fil de la réplication du calcul sur les lignes du dessous, cette matrice va grandir. Elle va donc retourner le chiffre 3 puis le 4 etc... Ainsi imbriquée dans la fonction Index, elle va donc naturellement permettre l'extraction des noms de feuille suivants, mais rappelez-vous, préfixés du nom du classeur comme nous le souhaitons.

• Fermer la parenthèse de la fonction Index,
• Enfoncer la touche 1 en haut à gauche du clavier pour le symbole de concaténation (&),

En effet, le nom du classeur accompagné du nom de la feuille doit être suivi des coordonnées de la cellule. Et entre ces deux derniers éléments, c'est un point d'exclamation qui doit faire la transition.

• En conséquence, entre guillemets, ajouter la syntaxe suivante : "!A1",
• Puis taper un point-virgule (;) pour passer dans l'argument du nom convivial,

Pour ce second paramètre de la fonction Lien_Hypertexte, nous l'avons annoncé, la syntaxe en place est déjà correcte. Elle purge le nom pour ne restituer que l'intitulé de la feuille.

• Cliquer juste avant le dernier point-virgule, celui de la branche Sinon de la fonction Si,
• Fermer la parenthèse de la fonction Lien_Hypertexte,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

En apparence, rien ne change en cellule D4. C'est en effet le nom convivial qui est restitué, soit l'intitulé de la feuille strictement identique à celui fourni par la précédente formule. Mais si vous déplacez la souris sur la case, vous constatez que l'aspect du pointeur change. Le symbole de la main indique qu'il s'agit effectivement d'un lien cliquable.

• Double cliquer sur la poignée de la cellule pour répandre la formule sur le tableau,

Désormais, au simple clic sur l'un des intitulés, vous atteignez directement la feuille de destination.



Nous sommes donc parvenus à construire un sommaire automatique sur la base des noms des feuilles du classeur. La syntaxe complète de la formule matricielle que nous avons adaptée est la suivante :

{=SI(LIGNES($1:2)<=NBVAL(nomF); LIEN_HYPERTEXTE(INDEX(nomF; LIGNES($1:2))&"!A1"; INDEX(STXT(nomF;TROUVE("]"; nomF)+1; 100); LIGNES($1:2))); "")}