Quelle fonction permet de lister le nom des feuilles de calcul Excel sans VBA ?

Dans ce tutoriel, nous allons voir comment créer une formule Excel complète qui va nous permettre de lister les noms de l’ensemble des feuilles de calcul disponibles sur un classeur Excel.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Obtenir le nom de toutes les feuilles de calcul d’un classeur

Comme nous venons de le voir dans l’introduction de ce tutoriel, je vais vous montrer comment créer une formule Excel qui va nous permet de lister l’ensemble des feuilles de calcul contenues dans un classeur.

Cela nous permettra par exemple d’effectuer une synthèse des données présentes dans une série de feuilles de manière dynamique en allant puiser les informations utiles sur les bonnes feuilles, de manière dynamique (comprenez par là qu’il sera possible d’ajouter une nouvelle feuille sur le classeur pour que le résultat de celle-ci soit intégré dans la bonne case).

Dans cet exemple, nous avons donc un classeur composé de quatre feuilles de calcul :

Dans la première, nous avons la feuille de synthèse, sur laquelle nous avons souhaité obtenir les résultats des ventes annuelles d’une entreprise auprès de ces clients :

Ensuite, nous retrouvons les trois feuilles de calcul qui correspondent aux trois principaux clients :

Dans chacune de ces feuilles du calcul, nous retrouvons l’ensemble des ventes détaillées à la journée.

Maintenant, nous allons souhaiter lister dans la feuille de calcul principale, le nom des trois feuilles de données, afin d’aller puiser les montants correspondant avec des formules.

2. La fonction secrète LIRE.CLASSEUR()

Pour pouvoir effectuer cette énumération des feuilles de calcul présentes sur un classeur Excel, nous allons pouvoir nous reposer sur l’utilisation d’une fonction secrète d’Excel, il s’agit de la fonction à LIRE.CLASSEUR().

Il s’agit effectivement d’une fonction secrète, car celle-ci n’est pas disponible directement à l’utilisation.

Pour l’utiliser, nous allons devoir passer par l’intermédiaire d’un nom, comme nous l’avons déjà vu dans un tutoriel précédent.

Nous allons donc commencer par nous rendre dans le gestionnaire des noms, en cliquant sur le bouton qui porte le même nom depuis le menu « Formules » :

Ici, nous allons insérer un nouveau nom en cliquant sur le bouton « Nouveau » :

Nous utiliserons les paramètres suivants :

• Dans le champ « Nom » nous allons taper « matriceFeuilles » .
• Nous allons conserver la zone d’utilisation de ces deux noms sur l’ensemble du classeur, puis dans le champ « Fait référence à », nous allons taper la fonction LIRE.CLASSEUR(1)

 =LIRE.CLASSEUR(1) 

Pour information, cette fonction LIRE.CLASSEUR(1) est une fonction de macro héritée d’ « Excel 4 ». C’est la raison pour laquelle elle ne s’utilise pas comme les fonctions classiques.

Le paramètre « 1 » que nous utilisons ici permet d’obtenir le nom de toutes les feuilles.

Ensuite, nous validons l’insertion du nom pour que nous puissions utiliser le résultat de cette fonction.

De retour sur la feuille de calcul, nous allons pouvoir appeler la fonction LIRE.CLASSEUR() en appelant le nom « matriceFeuilles » :

Comme nous pouvons le constater, cela ne nous permet de récupérer que le nom de la première feuille, car le résultat nous est retourné sous la forme d’une matrice.

Pour nous en rendre compte, il suffit de nous rendre dans la barre des formules, puis d’appuyer sur la touche [F9] pour qu’Excel nous présente le résultat total de la matrice :

 ={"[Excelformation.fr - Fonction pour lister  le nom des feuilles de calcul Excel.xlsx]Synthèse"."[Excelformation.fr  - Fonction pour lister le nom des feuilles de calcul Excel.xlsx]Auchan"."[Excelformation.fr  - Fonction pour lister le nom des feuilles de calcul Excel.xlsx]Carrefour"."[Excelformation.fr  - Fonction pour lister le nom des feuilles de calcul Excel.xlsx]Leclerc"}  

Avant d’aller plus loin, nous allons modifier légèrement la fonction insérée dans le nom « matriceFeuilles » pour ne conserver que le nom des feuilles et supprimer le nom du classeur.

Pour cela, il va nous suffire de ne conserver que la partie qui se trouve sur la droite du crochet fermant en utilisant l’une des fonctions de manipulation des textes.

Nous pourrions utiliser la fonction DROITE(), mais il sera encore plus simple et plus rapide d’utiliser la fonction STXT() de la manière suivante :

 =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;20)  

Ici, nous demandons d’extraire les 20 caractères situés après le crochet fermant.

Maintenant, nous nous retrouvons effectivement avec une matrice d’une ligne constituée uniquement des noms des feuilles de calcul.

Pour récupérer les données de cette matrice nous pourrons utiliser la fonction INDEX() :

 =INDEX(matriceFeuilles;;A9+1) 

Le « +1 » inséré à la fin de la fonction permet de passer directement sur la deuxième feuille et de ne pas tenir compte de la feuille de synthèse.

Et pour éviter de générer des erreurs, nous pouvons encapsuler ce résultat dans la fonction SIERREUR() :

3. Rendre la fonction dynamique

Attention, l’un des inconvénients de cette fonction LIRE.CLASSEUR(), c’est que celle-ci n’est pas dynamique… C’est pourquoi si nous créons une nouvelle feuille sur le classeur, celle-ci ne sera pas prise en compte de manière automatique.

L’astuce va alors consister à ajouter au nom que nous venons de créer une fonction qui s’actualise elle-même automatiquement, comme la fonction ALEA().

En effet, comme nous l’avons déjà vu à plusieurs reprises, cette fonction ALEA() permet d’obtenir un nombre à virgule aléatoire compris entre 0 et 1, ce résultat étant actualisé automatiquement lors de chaque recalcul.

Et pour éviter que ce nombre soit affiché, nous allons l’encapsuler dans la fonction TEXTE(), qui va nous permettre de le rendre invisible en utilisant le format d’affichage « "" » :

Maintenant, dès qu’une nouvelle feuille est insérée ou renommée sur le classeur, nous appuierons sur la touche [F9] pour la faire apparaitre dans la liste :

4. Récupérer les valeurs de synthèses

Maintenant, il ne reste plus qu’à compléter notre tableau en allant chercher les quantités vendues et le montant du chiffre d’affaires que cela représente.

Pour cela, nous allons pouvoir utiliser la fonction INDIRECT() qui permet de générer une référence de cellules à partir d’une chaîne de caractères :

 =SOMME(INDIRECT(B9&"!B4:B100")) 

Comme tout à l’heure, pour éviter les erreurs, nous pourrons encapsuler ce résultat dans la fonction SIERREUR() :

  =SIERREUR(SOMME(INDIRECT(B9&"!B4:B100"));0) 

Et :

  =SIERREUR(SOMME(INDIRECT(B9&"!D4:D100"));0) 

Ici la première ligne ne nous retourne aucune valeur, mais il suffit de renommer la feuille et de saisir des données :