Comment créer une table des matières regroupant toutes les feuilles et la faire mettre à jour automatiquement ?
Supposons que vous disposiez d’un classeur contenant des centaines de feuilles de calcul : naviguer jusqu’à une feuille spécifique parmi tant d’autres peut s’avérer fastidieux pour la plupart d’entre nous. Dans ce cas, créer une table des matières pour ces feuilles vous permettrait d’accéder rapidement et facilement à la feuille souhaitée. Ce tutoriel explique comment créer une table des matières pour toutes les feuilles et la mettre à jour automatiquement lors de l’insertion, de la suppression ou de la modification des noms de feuilles.
Utiliser une formule pour créer une table des matières pour toutes les feuilles et la mettre à jour automatiquement
Dans Excel, créez facilement une table des matières en attribuant un nom à une cellule, puis en y faisant référence via une formule. Voici la marche à suivre :
1. Insérez une nouvelle feuille avant toutes les autres à l’endroit où vous souhaitez créer la table des matières, puis donnez-lui le nom de votre choix.
2. Cliquez ensuite sur « Formules » > « Définir un nom », comme illustré dans la capture d’écran :
3. Dans la boîte de dialogue « Nouveau nom », saisissez « Sheetlist » dans le champ « Nom » (ou tout autre nom de votre choix), puis entrez la formule suivante dans la zone « Se réfère à » :
=GET.WORKBOOK(1)&T(NOW())
4. Cliquez ensuite sur le bouton « OK » pour fermer la boîte de dialogue.
5. Accédez maintenant à la nouvelle feuille où vous souhaitez créer la table des matières, puis saisissez la formule suivante dans une cellule vide :
=IFERROR(INDEX(MID(Sheetlist,FIND("]",Sheetlist)+1,255),ROWS($A$2:A2)),"")6. Faites ensuite glisser la poignée de recopie vers le bas jusqu’à ce que des cellules vides apparaissent. Tous les noms des feuilles du classeur actuel, y compris celles qui sont masquées, seront alors listés, comme illustré dans la capture d’écran ci-dessous :
7. Ensuite, créez un lien hypertexte vers la table des matières à l’aide de la formule suivante :
=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")
8. Désormais, en cliquant sur le texte du lien hypertexte, vous accéderez immédiatement à la feuille correspondante. De plus, si vous insérez une nouvelle feuille, en supprimez une ou modifiez un nom de feuille, la table des matières sera mise à jour automatiquement.
- 1. Avec cette méthode, toutes les feuilles masquées apparaissent également dans la table des matières.
- 2. Vous devez enregistrer le fichier au format « Classeur Excel prenant en charge les macros » afin que les formules fonctionnent correctement lorsque vous ouvrirez ce fichier ultérieurement.
Utiliser Kutools pour Excel pour créer une table des matières pour toutes les feuilles et la mettre à jour automatiquement
Si vous disposez de « Kutools pour Excel », sa fonction « Navigation » vous permet d’afficher tous les noms de feuilles verticalement dans un volet à gauche, pour une navigation rapide et facile vers la feuille de votre choix.
Après avoir installé Kutools pour Excel, procédez comme suit :
1. Cliquez sur « Kutools » > « Navigation », comme illustré dans la capture d’écran :
2. Dans le volet « Navigation » qui s’ouvre, cliquez sur l’icône « Classeur et Feuille de calcul ». Tous les classeurs ouverts s’affichent dans la liste supérieure, et toutes les feuilles visibles du classeur sélectionné apparaissent dans la liste inférieure — voir la capture d’écran :
3. Il vous suffit désormais de cliquer sur le nom d’une feuille dans le volet gauche pour y accéder. La liste des feuilles se met à jour automatiquement dès que vous supprimez, insérez ou renommez une feuille.
Utiliser un code VBA pour créer une table des matières pour toutes les feuilles et la mettre à jour automatiquement
Parfois, vous préférez ne pas afficher les feuilles masquées dans la table des matières. Le code VBA suivant résout précisément ce problème.
1. Insérez une nouvelle feuille avant toutes les autres à l’endroit où vous souhaitez créer la table des matières, puis donnez-lui le nom de votre choix. Cliquez ensuite avec le bouton droit sur l’onglet de cette feuille et sélectionnez « Afficher le code » dans le menu contextuel (voir la capture d’écran).
2. Dans la fenêtre « Microsoft Visual Basic pour Applications » qui s’ouvre, copiez et collez le code ci-dessous dans la fenêtre de code associée à la feuille :
Code VBA : Créer une table des matières pour toutes les feuilles et la mettre à 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. Appuyez ensuite sur la touche « F5 » pour exécuter ce code. La table des matières sera immédiatement générée dans une nouvelle feuille de calcul, sans inclure les feuilles masquées, comme illustré dans la capture d’écran :
4. Désormais, la table des matières sera mise à jour automatiquement à chaque suppression, insertion ou renommage de feuille.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre 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 rend votre travail bien plus facile
- Activez 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 vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels