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

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 :

Excel formation - fonction pour lister le nom des feuilles - 01

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 :

Excel formation - fonction pour lister le nom des feuilles - 02

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

Excel formation - fonction pour lister le nom des feuilles - 03

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 » :

Excel formation - fonction pour lister le nom des feuilles - 04

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

Excel formation - fonction pour lister le nom des feuilles - 05

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 » :

Excel formation - fonction pour lister le nom des feuilles - 06

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 :

Excel formation - fonction pour lister le nom des feuilles - 07

 ={"[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 :

Excel formation - fonction pour lister le nom des feuilles - 08

 =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() :

Excel formation - fonction pour lister le nom des feuilles - 09

 =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() :

Excel formation - fonction pour lister le nom des feuilles - 10

 

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 « "" » :

Excel formation - fonction pour lister le nom des feuilles - 11

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 :

Excel formation - fonction pour lister le nom des feuilles - 12

 

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 :

Excel formation - fonction pour lister le nom des feuilles - 13

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

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

Excel formation - fonction pour lister le nom des feuilles - 14

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

Et :

Excel formation - fonction pour lister le nom des feuilles - 15

  =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 :

Excel formation - fonction pour lister le nom des feuilles - 16

Excel formation - fonction pour lister le nom des feuilles - 17

 



Articles qui pourraient vous intéresser

Comment effectuer des remplacements multiples avec une seule formule magique d'Excel que personne ne vous a jamais montrée !
Les erreurs de concaténation qui vous font perdre du temps sur Excel
Découvrez la nouvelle fonction Excel que 90% des experts ne connaissent pas !
Boostez vos investissements : maîtrisez le calcul du taux de rentabilité avec Excel en 10 minutes !
Les 8 (+1 incroyable) fonctions Excel à maîtriser absolument en 2024
Percez les secrets de la fonction SOMME (SOMME.SI/SOMMEPROD) et des ces déclinaisons pour boostez votre productivité sur Excel ?
TOP3 des méthodes pour extraire des données par date dans Excel
Comment identifier la date la plus ancienne ou la plus récente en fonction de critères sur Excel ?
Comment calculer une moyenne sans tenir compte des zéros sur Excel ?
Comment fusionner des tableaux, les trier et supprimer les doubles avec une seule formule Excel ? (ASSEMB.V et ASSEMB.H)
Arrêtez d'imbriquer des fonctions SI() sur Excel ! Découvrez SI.MULTIPLE() et SI.CONDITIONS()
Comment obtenir efficacement le numéro de semaine d’une date donnée sur Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.