Comment lister les noms de toutes les feuilles dans un classeur Excel

Une pratique facile avec Integraal-MD

Il m’arrive souvent de vouloir répertorier tous les noms de feuilles dans un classeur Excel.

Cela me permet notamment de constituer un sommaire, afin de naviguer plous facilement.

Dans la feuille Semaine, qui servira de sommaire, nous voulons répertorier la liste de toutes les feuilles. Nous cherchons à atteindre ceci :

Ce n’est pas si facile à faire que ça, et il faut d’abord valider un certain nombre de prérequis.

Prérequis

Votre classeur doit être enregistré au format xlsm, afin d’accepter les macros.

En effet, nous allons utiliser une fonction qui n’existe que sous forme de macro.

Dans cet exemple, vous allez apprendre à combiner plusieurs techniques :

• Le gestionnaire de noms
• Intégrer une fonction macro dans un nom défini
• Reporter cette fonction et ce nom défini dans une formule de calcul
• Utiliser des fonctions de propagation

Et nous allons voir les fonctions en français et en anglais.

Commençons par « définir un nom » :

Nous allons définir un nom qui sera, en fait, une forrmule de calcul relativement complexe.

Cliquez sur l’onglet Formules du ruban supérieur, puis « Définir le nom » dans le groupe « Noms définis » :

Dans la fenêtre qui apparaît, définissez le nouveau nom, par exemple « ListerFeuilles » (ou GetSheets en anglais), à saisir dans la zone Nom.

Dans la zone « Fait référence à » ou « Refers to », saisissez la formule suivante :

=REMPLACER(LIRE.CLASSEUR(1);1;TROUVE(« ] »;LIRE.CLASSEUR(1)); » »)

Ou en anglais

=REPLACE(GET.WORKBOOK(1),1,FIND(« ] »,GET.WORKBOOK(1)), » »)

Les formules Lire.Classeur et Get.workbook ne sont accessibles que par une macro. N’essayez pas de les appliquer dans votgre formule de calcul telles quelles, cela donnerait une valeur d’erreur #nom?.

Cliquez ensuite sur OK .

Explications :

Cette formule affecte au nom ListerFeuille la liste des feuilles du classeurs, avec les fonctions :

=REMPLACER(LIRE.CLASSEUR(1);1;TROUVE(« ] »;LIRE.CLASSEUR(1)); » »)

• La fonction macro LIRE.CLASSEUR(1) va fournir une liste d’éléments de type « [nom du classeur.xlsm]Nom de chaque Onglet »
• La fonction TROUVE(« ] »;LIRE.CLASSEUR(1)) fournit la position du caractère « ] » (crochet fermé à droite) dans chaque résultat de cette liste
• La fonction REMPLACER va remplacer, dans chaque élément de la liste issue de la fonction ci-dessus, à partir du 1er caractère (2ème argument, la valeur « 1 »), tous les caractères qui précèdent le ] par un « vide » : il ne restera donc que « Nom de chaque Onglet »

Voici la construction de cette fonction et ses arguments ci-dessous :

Ensuite :

Méthode 1

Dans la cellule A1 de la feuille Semaine, il ne reste qu’à saisir la formule qui fait appel à ce nom défini, adapté à la ligne en cours.

Tapez la formule suivante dans la cellule A1 de l’onglet « Semaine » :

=INDEX(ListerFeuilles;LIGNE())

Ou en anglais :

=INDEX(GetSheets, ROW())

Ces fonctions reprennent, comme vous le comprenez, les noms définis ci-dessus.

La fonction va fournir le croisement entre la liste des noms de feuilles et le numéro de ligne : ici, la ligne 1 donc le nom de la feuille 1.

Sélectionnez et faites glisser cette formule vers le bas de la colonne A jusqu’à ce que vous rencontriez #REF ! pour indiquer que tous les noms de feuilles ont été répertoriés.

Vous obtenez la liste des onglets existants.

Tous les noms de feuilles du classeur sont désormais affichés dans la colonne A.

Méthode 2

Vous pouvez aussi positionner la formule ailleurs dans votre feuille de sommaire, et appeler un numéro de feuille.

Astuce :

Vous pouvez lister vos feuilles de classeur dans une zone de votre feuille en 2 temps. La liste des numéros de feuilles présentes dans votre classeur, puis le nom de ces feuilles.

Avec la formule :

=SEQUENCE(FEUILLES();1)

par exemple en B5.

Cette formule est dite « à propagation », c’est-à-dire qu’elle s’étend automatiquement sur une plage de la taille adéquate.

Vous récupérez ainsi tous les numéros de feuilles.

Puis utilisez la formule :

=INDEX(ListerFeuilles;B5)

Et faites-la glisser avec la poignée de recopie vers le bas de la colonne (double-clic sur la poignée de recopie pour aller plus vite)

Méthode 3

Vous pouvez maintenant aller un cran plus loin, en intégrant la formule qui séquence les numéros de feuilles dans celle qui en extrait les noms :

=INDEX(ListerFeuilles;SEQUENCE(FEUILLES();1))

Cette fonction va à la fois utiliser la fonction définie dans le gestionnaire de noms, « ListerFeuilles », et l’appliquer à la séquence de numéro de feuilles extraits avec la fonction SEQUENCE(FEUILLES();1)

Plus besoin de numéro d’onglet, ni même d’être positionné en cellule A1 pour que ça fonctionne.

Remarque

Dans cet exemple, nous avons utilisé un classeur constitué de 6 feuilles, mais ce même traitement fonctionnera avec n’importe quel nombre de feuilles.