Lister les noms des feuilles 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 :


Lister les noms des feuilles par calcul

Pour consolider les données d'un classeur sur une feuille de synthèse, il est opportun de pouvoir énumérer automatiquement les noms de ces dernières dans un tableau. Ce sont en effet ces noms qui permettent de recomposer les adresses des cellules à atteindre dans chacun des onglets du classeur.



Dans l'exemple illustré par la capture, sur la base d'une formule, nous parvenons à récupérer et à lister le nom de toutes les feuilles composant le classeur. Et sur la base de cette information dynamique, nous consolidons tous les chiffres d'affaires réalisés en colonne voisine. En d'autres termes, nous parvenons à réunir dynamiquement toutes les données de synthèse dispersées.

Classeur source
Pour la mise en place de cette nouvelle astuce Excel, nous suggérons d'appuyer les travaux sur un classeur offrant déjà un certain nombre de feuilles avec des informations à consolider.

• Télécharger le classeur liste-noms-feuilles.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 débouchons sur la feuille d'accueil de ce classeur. Elle est nommée Synthese. Elle présente un tableau vide. Sa première colonne, la colonne D, attend l'énumération des noms des feuilles composant ce classeur. Sa seconde colonne, la colonne E, doit rapatrier chaque chiffre d'affaires de chacune de ces feuilles respectives.

• En bas de la fenêtre Excel, cliquer par exemple sur l'onglet intitulé Mars,

Nous affichons ainsi le contenu de sa feuille. Comme vous pouvez le voir, le chiffre d'affaires consolidé pour ce mois de Mars est calculé en cellule C2. C'est le cas aussi pour toutes les autres feuilles, soit pour tous les autres mois. Vous pouvez facilement le constater en basculant d'un onglet à un autre. Cette organisation nous permettra de réunir plus facilement les informations de synthèse, une fois que nous aurons réussi à récupérer le nom detoutes les feuilles et ce, sans l'appui du code VBA.

Fonction d'information
Excel offre une fonction de macro méconnue. Il s'agit de la fonction Lire.Classeur. Avec en paramètre le chiffre 1, elle renvoie un tableau horizontal des noms des feuilles. Et comme il s'agit d'une fonction de macro, le classeur est enregistré au format xlsm, sans quoi il ne pourrait gérer les macros. Par contre, nous ne pouvons pas exploiter une fonction aussi spécifique de façon conventionnelle. C'est pourquoi nous allons passer par un nom de formule.

• 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,
• En haut à gauche de la boîte de dialogue qui suit, cliquer sur le bouton Nouveau,
• Dans la zone Nom de la nouvelle boîte de dialogue, taper l'intitulé nomF,
• Dans la zone Fait référence à, remplacer la syntaxe présente par la suivante :

=TRANSPOSE(LIRE.CLASSEUR(1))

Grâce à la fonction Excel Transpose, nous transformons le tableau horizontal des noms des feuilles en tableau vertical pour pouvoir les répliquer ligne à ligne sur la feuille de synthèse. Il est temps d'exploiter ce nom.

• Cliquer sur le bouton Ok pour valider la création du nom,
• De retour sur le gestionnaire de noms, cliquer sur le bouton Fermer pour revenir sur la feuille,
• Dès lors, en bas de la fenêtre Excel, cliquer sur l'onglet Synthese pour afficher sa feuille,
• Sélectionner la plage de cellules D4:D15 des noms à extraire,

La première cellule d'une plage est toujours active par défaut. C'est pourquoi nous pouvons y écrire la formule à répandre sur les autres.

• Taper le symbole égal (=) pour initier le calcul,
• Puis, inscrire le nom de formule que nous venons de construire, soit : nomF,
• Dès lors, valider la formule par le raccourci clavier CTRL + MAJ + Entrée,

Un raisonnement matriciel est effectivement nécessaire. Il permet de parcourir et restituer toutes les lignes du tableau vertical, donc tous les noms de feuille. Et comme vous pouvez le voir, le nom de chacune des feuilles du classeur est effectivement restitué. Mais trois problèmes sont identifiés. Tout d'abord, la feuille d'accueil (synthèse) fait partie de l'énumération. Les noms des feuilles sont préfixés du nom du classeur entre crochets. Enfin, lorsque la fin de la liste est dépassée, une erreur est générée. Elle ne peut être corrigée par la fonction SiErreur dans ce raisonnement matriciel. Or nous souhaitons prévoir large en répandant plus bas la formule, si d'aventure de nouvelles feuilles étaient ajoutées dans le classeur.

Isoler les noms des feuilles
Tout d'abord, nous souhaitons purger les chaînes restituées pour ne conserver que le nom de la feuille, lorsqu'aucune erreur n'est générée. Pour cela, il existe la fonction Excel Stxt. Elle permet de prélever une portion de chaîne à partir d'une position à définir. Cette position est celle de l'emplacement du crochet fermant, après le nom du classeur. Et pour trouver cette position de départ, nous pouvons exploiter la fonction Excel Trouve.

• Sélectionner de nouveau la plage de cellules D4:D15,
• Dans la barre de formules, cliquer juste après le symbole égal (=),
• Inscrire la fonction de découpe suivie d'une parenthèse, soit : Stxt(,

Ainsi, la chaîne à découper est d'ores et déjà fournie en premier paramètre par le nom de formule nomF. Il est question du nom de la feuille dont il s'agit de supprimer le nom du classeur en préfixe.

• Cliquer à la fin de la syntaxe, soit après nomF pour y placer le point d'insertion,
• Taper un point-virgule (;) pour passer dans l'argument du caractère de départ pour la découpe,

Nous l'avons dit, il s'agit du caractère situé juste après le crochet fermant. Cette position doit être décelée grâce à la fonction Trouve.

• Taper le nom de cette fonction suivi d'une parenthèse, soit : Trouve(,
• Inscrire un crochet fermant entre guillemets, soit : "]",

Nous indiquons ainsi quel est l'élément dont nous souhaitons trouver la position dans la chaîne.

• Taper un point-virgule (;) pour passer dans l'argument du texte dans lequel cherché,
• Inscrire de nouveau le nom de la formule, soit : nomF,

C'est effectivement dans le nom complet de la feuille que nous souhaitons trouver la position de ce crochet fermant.

• Fermer la parenthèse de la fonction Trouve,
• Puis, ajouter une unité, soit : +1,

En effet, le nom de la feuille commence après le crochet fermant. C'est la raison pour laquelle nous incrémentons la position trouvée.

• Taper un point-virgule (;) pour passer dans l'argument de la longueur de découpe pour Stxt,
• Dès lors, inscrire le nombre 100,

Cette valeur est arbitraire mais nous prévoyons large. Selon le nom du classeur et les noms des feuilles, nous ne connaissons pas par avance les longueurs des chaînes restituées. Avec une telle dimension, nous nous assurons de parcourir chaque chaîne sur un nombre de caractères suffisant. Si la fin est dépassée, la fonction Stxt s'adapte sur le dernier caractère de la chaîne.

• Fermer la parenthèse de la fonction Stxt,
• Enfin, valider de nouveau la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Nous obtenons bien tous les noms de feuilles purgés du nom du classeur en préfixe. Naturellement à ce stade, une fois la fin de la liste atteinte, des erreurs sont encore générées. Nous n'avons pas encore choisi de les gérer, mais nous y venons.

Gérer les erreurs
Pour une restitution sans anomalie, nous devons poursuivre le raisonnement matriciel mais dans une configuration un peu particulière. Cette fois, il est question de poser le raisonnement à partir de la première cellule en engageant une matrice virtuelle. En répliquant la formule sur les lignes du dessous, si la dimension de cette matrice n'excède pas la dimension du tableau des noms de feuilles, nous saurons que nous pouvons les extraire. Le cas échéant, nous serons en mesure de stopper la restitution.

• Sélectionner le premier résultat en cliquant sur sa cellule D4,
• Dans la barre de formule, copier la syntaxe (CTRL + C) sans le symbole égal,
• Quitter la barre de formule avec la touche Echap du clavier,
• Sélectionner la plage de cellules D4:D15,
• Puis enfoncer la touche Suppr pour effacer tous les résultats,
• Sélectionner de nouveau uniquement la cellule D4,
• Taper le symbole égal (=) pour initier la nouvelle formule matricielle,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Dès lors construire la matrice virtuelle suivante : Lignes($1:2),

Le dollar devant le chiffre 1 est important. La fonction Excel Lignes retourne la quantité de lignes d'un tableau. Avec cette notation, nous construisons un tableau virtuel de deux lignes. Mais attention, la borne supérieure est figée ($1) tandis que la borne inférieure ne l'est pas (2). Donc, au fil de la réplication de la formule sur les lignes du dessous, la hauteur de ce tableau virtuel progressera (2 puis 3, 4, 5...).

• Ensuite, ajouter le critère suivant : <=NBVAL(nomF),

En d'autres termes, tant que ce nombre de lignes qui progresse est inférieur au nombre de lignes contenues dans le tableau des noms de feuilles, nous devons restituer chacune d'elles au coup par coup. Pour cela :

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Dès lors, coller la précédente syntaxe, soit : STXT(nomF; TROUVE("]"; nomF)+1; 100),

Nous définissons ainsi la matrice à partir de laquelle il est question de réaliser l'extraction incrémentée.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne de la fonction Index,
• Y inscrire de nouveau la matrice virtuelle, soit : LIGNES($1:2),

Rappelons-le, au gré de la réplication, cette fonction renverra la valeur 2 puis la 3 et les suivantes. Donc, dans le processus d'extraction, nous allons ignorer la première feuille (Synthese) et prélever les suivantes.

• Fermer la parenthèse de la fonction Index,

Maintenant, lorsque le critère de la fonction Si n'est plus honoré, donc lorsque la fin de la liste est atteinte, nous devons stopper l'extraction.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Inscrire deux guillemets ("") pour conserver la cellule vide lorsque le critère n'est pas vérifié,
• Fermer la parenthèse de la fonction Si,
• Valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,
• Puis, tirer la poignée du résultat jusqu'en cellule D15,

Comme vous pouvez l'apprécier, l'énumération se stoppe cette fois sur le nom de la dernière feuille. Et cette restitution est parfaitement dynamique. Donc cette formule s'adapte parfaitement à n'importe quel classeur, quelque soit le nombre de ses feuilles. La syntaxe de la formule matricielle que nous avons construite est la suivante : =SI(LIGNES($1:2)<=NBVAL(nomF); INDEX(STXT(nomF; TROUVE("]"; nomF)+1; 100); LIGNES($1:2)); "")

Consolider les données des feuilles listées
Pour parachever la solution, nous proposons de consolider tous les chiffres d'affaires des feuilles ainsi rapatriées, en colonne 2 de ce tableau. Nous l'avons dit, cette information de synthèse est située en cellule C2 de chaque feuille. Nous allons donc pouvoir répliquer la formule de l'astuce du volet précédent. Elle se nourrit du nom de la feuille pour pointer sur la cellule désignée et détenant l'information.

• En cellule E4, construire la formule suivante : =SIERREUR(INDIRECT(D4 & "!C2"); ""),
• Puis la valider avec le raccourci CTRL + Entrée pour garder la cellule active,

Nous englobons la syntaxe dans une fonction de gestion des anomalies. Ainsi, lorsque la fin de la liste est atteinte, aucune erreur n'est retournée.

• Double cliquer sur la poignée du résultat pour répliquer la logique sur le tableau complet,

Comme vous pouvez le voir, tous les chiffres d'affaires sont parfaitement et dynamiquement réunis pour une consolidation on ne peut plus explicite et facile à exploiter.

Pour finir, il est important de démontrer l'adaptation de la formule au contexte.

• En bas de la fenêtre Excel, cliquer droit sur l'onglet Septembre,
• Dans le menu contextuel, choisir la commande Déplacer ou copier,
• Dans la boîte de dialogue qui suit, cocher la case Créer une copie,
• En bas de la liste, sélectionner la ligne (en dernier),
• Puis, valider la copie en cliquant sur le bouton Ok,

La feuille dupliquée apparaît bien à la fin des onglets.

• Double cliquer sur son onglet pour activer la saisie de son nom,
• Taper l'intitulé Octobre et valider par la touche Entrée,

Pour l'exemple, en colonne C il conviendrait de modifier quelques chiffres afin de faire varier le total calculé en cellule C2.

• En bas de la fenêtre Excel, cliquer sur l'onglet Synthese pour revenir sur la feuille d'accueil,

Une surprise nous attend. L'actualisation s'est arrêtée au nom de la feuille au moment de la duplication. Cette fonction de macro n'est pas une fonction volatile. En d'autres termes, elle ne s'actualise pas systématiquement comme le font les fonctions naturelles d'Excel. Qu'à cela ne tienne, nous allons forcer le recalcul.

• Double cliquer sur la cellule D4 du premier résultat,

De fait, nous entrons en mode saisie de la formule.

• Puis, la valider de nouveau avec le raccourci clavier CTRL + MAJ + Entrée,

Cette fois, tout rentre dans l'ordre. La nouvelle feuille est parfaitement incorporée dans l'énumération. Et comme son nom est reconnu, son total est lui aussi consolidé à la suite des autres.