Comment créer une table des matières pour toutes les feuilles et mises à jour automatiquement ?
Supposons que vous disposiez d'un classeur contenant des centaines de feuilles de calcul. Naviguer vers une feuille spécifique parmi plusieurs feuilles peut causer des maux de tête à la plupart d'entre nous. Dans ce cas, créer une table des matières pour ces feuilles de calcul peut vous aider à accéder rapidement et facilement à la feuille en question. Ce didacticiel explique comment créer une table des matières pour toutes les feuilles et mettre à jour automatiquement la table lors de l'insertion, de la suppression ou de la modification des noms de feuilles.
Créez une table des matières pour toutes les feuilles et les mises à jour automatiquement avec des formules
Dans Excel, vous pouvez créer une table des matières en définissant un nom de plage et en référençant le nom dans une formule, veuillez suivre les étapes suivantes :
1. Insérez une nouvelle feuille avant toutes les feuilles où vous souhaitez créer la table des matières et renommez-la selon vos besoins.
2. Puis clique Laits en poudre > Définir le nom, voir capture d'écran:
3. Dans le Nouveau nom boîte de dialogue, spécifiez un nom Liste des feuilles into the Nom case, vous pouvez la remplacer par la vôtre, puis entrer cette formule : = GET.WORKBOOK (1) & T (MAINTENANT ()) into the Se réfère à zone de texte, voir capture d'écran:
4. Puis clique OK bouton pour fermer la boîte de dialogue.
5. Maintenant, allez sur la nouvelle feuille où vous voulez créer une table des matières, puis entrez la formule suivante dans une cellule vide :
6. Et puis faites glisser la poignée de remplissage vers les cellules jusqu'à ce que les cellules vides soient affichées et que tous les noms de feuille (y compris les feuilles masquées) du classeur en cours aient été répertoriés comme illustré ci-dessous :
7. Ensuite, vous devez créer des hyperliens pour le contenu du tableau, veuillez utiliser la formule ci-dessous :
8. Maintenant, lorsque vous cliquez sur le texte du lien hypertexte, il ira immédiatement à cette feuille. Et si vous insérez une nouvelle feuille, supprimez une feuille ou modifiez le nom de la feuille, le contenu du tableau sera automatiquement mis à jour.
- 1. Avec cette méthode, toutes les feuilles masquées sont également affichées dans le contenu du tableau.
- 2. Vous devez enregistrer le fichier sous Cahier de travail Excel avec macro-activé format, de cette façon, lorsque vous ouvrez ce fichier la prochaine fois, les formules peuvent bien fonctionner.
Créez une table des matières pour toutes les feuilles et les mises à jour automatiquement avec un code VBA
Parfois, vous n'avez pas besoin d'afficher les feuilles masquées dans le contenu du tableau. Pour résoudre ce problème, le code VBA suivant peut vous rendre service.
1. Insérez une nouvelle feuille avant toutes les feuilles où vous souhaitez créer la table des matières et renommez-la selon vos besoins. Ensuite, faites un clic droit sur l'onglet de la feuille et choisissez Voir le code depuis le menu contextuel, voir capture d'écran:
2. Dans le ouvert Microsoft Visual Basic pour applications fenêtre, copiez et collez le code ci-dessous dans la fenêtre de code de la feuille :
Code VBA : créer une table des matières pour toutes les feuilles et les mises à jour automatiquement
Private Sub Worksheet_Activate()
‘Updateby ExtendOffice
Dim xWsh As Worksheet
Dim xWshs As Worksheets
Dim xShowHinddenWorkSheet As Boolean
Dim xI As Long
Dim xRg As Range
Dim xStrTitle, xStrTCHeader, xStrWShName As String
xShowHinddenWorkSheet = False 'Change this to True to display the hidden sheets as you need
xStrTitle = "A1"
xStrTCHeader = "A3"
On Error Resume Next
Application.ScreenUpdating = False
Me.Cells.Clear
Me.Range(xStrTitle).Font.Bold = True
Me.Range(xStrTitle).Font.Size = Me.Range(xStrTitle).Font.Size + 2
Me.Range(xStrTitle).Value = "Table of Contents"
Me.Range(xStrTCHeader).Value = "No."
Me.Range(xStrTCHeader).Offset(0, 1).Value = "Sheet Name"
Me.Range(xStrTCHeader).Resize(1, 2).Font.Bold = True
xStrWShName = Me.Name
xI = 1
For Each xWsh In Application.ActiveWorkbook.Worksheets
If xWsh.Name <> xStrWShName Then
If (xWsh.Visible = xlSheetVisible) Or xShowHinddenWorkSheet Then
Me.Hyperlinks.Add Anchor:=Me.Range(xStrTCHeader).Offset(xI, 1), Address:="", SubAddress:="'" & xWsh.Name & "'!A1", TextToDisplay:=xWsh.Name
Me.Range(xStrTCHeader).Offset(xI).Value = xI
xI = xI + 1
End If
End If
Next
Application.ScreenUpdating = True
End Sub
3. Puis appuyez F5 clé pour exécuter ce code, et le contenu du tableau est créé immédiatement dans la nouvelle feuille de calcul, toutes les feuilles masquées ne s'afficheront pas dans le contenu du tableau, voir capture d'écran :
4. Formulaire maintenant, lors de la suppression, de l'insertion ou du changement de nom de feuille, le contenu du tableau sera mis à jour dynamiquement.
Créez une table des matières pour toutes les feuilles et les mises à jour automatiquement avec une fonctionnalité puissante
Si vous avez Kutools for Excel, il est Volet de navigation peut vous aider à répertorier verticalement tous les noms de feuilles dans un volet de gauche et à accéder rapidement et facilement à la feuille spécifique.
Après l'installation de Kutools for Excel, veuillez faire comme ceci:
1. Cliquez Kutools > Navigation, voir capture d'écran:
2. Dans le grand Navigation Cliquez sur Cahier et feuille , tous les classeurs ouverts sont répertoriés dans la zone de liste supérieure et toutes les feuilles visibles dans le classeur sont affichées dans la zone de liste inférieure, voir capture d'écran :
3. Et maintenant, vous pouvez accéder à la feuille en cliquant simplement sur le nom de la feuille dans le volet de gauche. Et lorsque vous supprimez, insérez ou renommez une feuille, la liste des feuilles dans le volet sera mise à jour dynamiquement.
Meilleurs outils de productivité bureautique
Améliorez vos compétences Excel avec Kutools for Excel et faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel offre plus de 300 fonctionnalités avancées pour augmenter la productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et facilite grandement votre travail
- Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
