Même si le monde des affaires s’oriente de plus en plus vers les tableaux de bord BI et les outils Web, Excel reste un outil indispensable pour les petites et grandes entreprises.
Au cours de mon travail, j’ai également été dans plusieurs situations où j’ai dû utiliser Excel et faire le travail. Lorsque nous travaillons sur Excel, nous devons souvent organiser les données en sections logiques. C’est là que le concept [Sheets] d’Excel est utile. Par exemple, prenons l’exemple où nous avons différents départements dont nous avons séparé les données des employés sous différentes feuilles ou onglets Excel. Les onglets peuvent être Finances, RH, Installations, Administration, Support informatique, Service Web, etc. Bien qu’il soit facile de se référer aux données de chaque onglet en cliquant sur l’onglet respectif, imaginez ce que vous feriez si vous aviez plus de 20 services différents. Faire défiler sans fin vers la droite est pénible, n’est-ce pas ? Que se passe-t-il si vous travaillez sur un ordinateur portable à écran assez petit ou sur un iPad ou une tablette, où il n’y a pas beaucoup d’écran ?
Dans un tel scénario, il est toujours pratique d’avoir une table des matières dans laquelle vous pouvez cliquer sur le nom de la feuille pour accéder directement à la feuille spécifique. N’est-ce pas chouette ? Alors que le document MS Word contient une table des matières [TOC] pratique ou « Insérer une table des figures » dans la section [Référence]. Excel et PowerPoint n’ont pas ces fonctionnalités.
Cet article montrera comment nous pouvons générer la table des matières pour une feuille Excel. Il existe 2 approches que nous pouvons adopter pour créer une table des matières.
1. Une macro que nous pouvons créer via l’éditeur VB intégré à MS Excel
2. Utilisation des formules intégrées fournies par MS Excel
Nous utiliserons l’approche (2) dans ce didacticiel car (1) est un peu plus complexe et, de plus, de nombreuses organisations désactivent les classeurs prenant en charge les macros pour des raisons de sécurité.
Il existe une fonction VB intégrée GET.WORKBOOK() qui nous donne la liste des noms de feuilles sur un classeur donné. Cependant, pour les raisons mentionnées ci-dessus et aussi, parce que nous ne pouvons pas utiliser la fonction ci-dessus directement dans la barre de formule, nous allons utiliser une petite solution de contournement et y parvenir en utilisant les 3 fonctions suivantes d’Excel. INDEX(), REMPLACER(), TROUVER() .
Écrivons la recette pour obtenir tous les noms de feuilles et listons-les dans une colonne.
1. Obtenez la liste de tous les noms de feuilles dans le classeur donné à l’aide de GET.WORKBOOK() .
Le résultat inclut le nom du classeur suivi du nom de la feuille dans le format. [Nom du classeur]NomFeuille . Par exemple, si le nom de mon classeur est Deepak.xlsx, il comporte 4 feuilles – Sheet1 , Sheet2 , Sheet3 et Sheet4 . La fonction ci-dessus renvoie un tableau comme [Deepak]Sheet1, [Deepak]Sheet2 et ainsi de suite.
2. Traitez le résultat du tableau pour supprimer le nom du classeur de chaque élément du tableau de résultats
3. Afficher les noms de feuille de manière séquentielle en accédant au tableau par index
4. Ajoutez des hyperliens à chacun des noms de feuille, afin qu’il nous amène directement à la feuille vers laquelle nous voulons naviguer
Maintenant, mettons en œuvre la recette ci-dessus dans notre feuille Excel. Je montrerai le résultat de chaque étape, puis nous intégrerons la formule en une seule vers la fin. Alors ouvrez un nouveau classeur et ensuite,
1. Ouvrez un nouveau classeur Excel et créez 4 nouveaux onglets. Feuil1, Feuil2, Feuil3 et Feuil4 et enregistrez-le sous <VotreNom.xlsx>. Dans mon cas, il s’agit de Deepak.xlsx.
2. L’étape 1 est simple. Cependant, nous devons faire un petit contournement pour l’utiliser dans la barre de formule. Accédez à l’onglet Formule de votre ruban Excel et choisissez Définir le nom .
Barre de formule avec le gestionnaire de noms ouvert
Vous obtiendrez une petite fenêtre qui vous demandera le nom de cette formule, la portée et une case « Se réfère à ». Spécifiez le nom de la formule sous la forme d’un nom significatif. Il sera utilisé pour référencer ce nom de formule plus tard dans le classeur. Choisissez donc quelque chose de significatif. Je préfère, GetSheetNames. Entrez la formule =GET.WORKBOOK(1) dans la zone de texte « Refers to : », appuyez sur OK.
Vous pouvez maintenant vérifier si la formule fonctionne comme prévu en choisissant une cellule vide sur votre feuille, puis en tapant =GetSheetNames . Dès que vous tapez =Get…, vous devriez voir notre formule prédéfinie dans le menu déroulant. Appuyez sur la touche Tab pour terminer la formule (si vous devez encore la saisir), puis appuyez sur Entrée.
Vous devriez voir que 4 colonnes seraient remplies avec les valeurs suivantes.
Maintenant, l’étape 1 de notre recette est terminée. Nous avons répertorié tous les noms de feuilles comme nous le souhaitions.
3. Maintenant, nous devons effectuer un traitement pour supprimer les noms de fichiers du tableau résultant de l’étape précédente. Nous devons donc trouver les noms de fichiers de chaque élément du tableau, puis les remplacer par « . C’est donc un processus en 2 étapes. Tout d’abord, nous devons trouver le nom du fichier dans le tableau. Comme vous pouvez le voir, chaque élément du tableau a un motif spécifique, le nom du fichier entre [] suivi du nom de la feuille. Donc, si nous recherchons le crochet de fin « ] », puis remplaçons tous les caractères, y compris et avant l’occurrence de « ] ». Nous utiliserons la fonction FIND() pour rechercher le « ] ». Je détaillerai le fonctionnement de la partie FIND() dans un autre post ; pour l’instant, utilisons le processus FIND() pour trouver « ] » dans la formule GetSheetNames.
Dans une nouvelle cellule, tapez la formule suivante et appuyez sur Entrée.
=TROUVER ( "]" , GetSheetNames)
Vous devriez voir 4 colonnes remplies avec la position du caractère « ] » dans le tableau. Dans mon cas, c’était
13 13 13 13
En effet, le caractère « ] » se trouve à la 13ème position de la chaîne donnée.
D’accord, maintenant nous devons avoir l’emplacement du personnage jusqu’à lequel nous devons remplacer tous les personnages avant cela. Nous utilisons donc la fonction REPLACE(), qui prend 4 arguments – l’ancien texte, la position de départ à partir de laquelle nous devons remplacer, le nombre de caractères à remplacer et le texte de remplacement.
La formule ci-dessus devrait nous donner le tableau suivant, qui ne donne que les noms de feuille de la formule donnée.
Feuille1 Feuille2 Feuille3 Feuille4
La dernière étape consiste à référencer la liste par index et à afficher le nom de feuille donné à un emplacement donné dans le tableau. Nous allons utiliser la fonction INDEX() pour renvoyer le nom de la feuille à une zone donnée. Il prend 2 arguments – un tableau et l’élément d’emplacement du tableau que nous voulons.
En matraquant tout ce que nous avons appris jusqu’à présent, la formule finale devrait ressembler à ceci.
Où ‘ 1 ‘ ferait référence au premier élément du tableau et ‘ 2 ‘ au deuxième élément, et ainsi de suite.
Nous pouvons maintenant faire une étape supplémentaire pour simplifier la formule que nous entrons dans la barre de formule en définissant la partie mentionnée dans la fonction INDEX() en tant que formule nommée. Appelons la formule SheetNamesArray, qui contient uniquement le tableau des noms de feuilles du classeur donné. Suivez les étapes précédentes et ajoutez une fonction nommée appelée SheetNamesArray, qui doit pointer vers la formule.
Après l’étape ci-dessus, nous pouvons réécrire la fonction INDEX() pour qu’elle soit plus compacte et soignée.
= INDEX (TableNomsFeuilles, 1 )
Maintenant, nous avons la dernière étape consistant à créer un lien hypertexte pour chaque nom de feuille afin que nous puissions directement y accéder. J’utilise la formule suivante pour le faire. Cette formule suppose que le nom de votre feuille figure dans la colonne A2.
=HYPERLIEN( "#'" &A2& "'!A1" , "Aller à " &A2)
Sortie finale Table des matières d’un classeur
J’espère que vous avez trouvé cet article utile et amusant, comme je l’ai fait lors de la création de cet article.
