Wie erstellt man ein Inhaltsverzeichnis für alle Arbeitsblätter, das sich automatisch aktualisiert?

Angenommen, Sie haben eine Arbeitsmappe mit Hunderten von Arbeitsblättern. Das Navigieren zu einem bestimmten Blatt unter vielen kann für die meisten von uns Kopfschmerzen bereiten. In diesem Fall kann die Erstellung eines Inhaltsverzeichnisses für diese Arbeitsblätter Ihnen helfen, schnell und einfach zu einem bestimmten Blatt zu gelangen. Dieses Tutorial erklärt, wie Sie ein Inhaltsverzeichnis für alle Arbeitsblätter erstellen und das Verzeichnis automatisch aktualisieren, wenn Sie Blätter einfügen, löschen oder deren Namen ändern.

Verwenden Sie eine Formel, um ein Inhaltsverzeichnis für alle Arbeitsblätter zu erstellen, das sich automatisch aktualisiert

Verwenden Sie Kutools für Excel, um ein Inhaltsverzeichnis für alle Arbeitsblätter zu erstellen, das sich automatisch aktualisiert

Verwenden Sie VBA-Code, um ein Inhaltsverzeichnis für alle Arbeitsblätter zu erstellen, das sich automatisch aktualisiert

Verwenden Sie eine Formel, um ein Inhaltsverzeichnis für alle Arbeitsblätter zu erstellen, das sich automatisch aktualisiert

In Excel können Sie ein Inhaltsverzeichnis erstellen, indem Sie einen Zellennamen definieren und den Namen in einer Formel referenzieren. Bitte folgen Sie den folgenden Schritten:

1. Fügen Sie ein neues Arbeitsblatt vor allen anderen Blättern ein, wo Sie das Inhaltsverzeichnis erstellen möchten, und benennen Sie es nach Bedarf um.

2. Klicken Sie dann auf "Formeln" > "Namen definieren", siehe Screenshot:

3. Geben Sie im Dialogfeld "Neuer Name" den Namen "Sheetlist" in das Feld "Name" ein (Sie können ihn nach Belieben ändern) und geben Sie dann die folgende Formel in das Textfeld "Bezieht sich auf" ein.

=GET.WORKBOOK(1)&T(NOW())

4. Klicken Sie dann auf die Schaltfläche "OK", um das Dialogfeld zu schließen.

5. Gehen Sie nun zum neuen Arbeitsblatt, wo Sie das Inhaltsverzeichnis erstellen möchten, und geben Sie die folgende Formel in eine leere Zelle ein:

=IFERROR(INDEX(MID(Sheetlist,FIND("]",Sheetlist)+1,255),ROWS($A$2:A2)),"")

6. Ziehen Sie den Ausfüllkursor nach unten, bis leere Zellen angezeigt werden, und alle Blattnamen (einschließlich der ausgeblendeten Blätter) der aktuellen Arbeitsmappe werden wie im folgenden Screenshot aufgelistet:

7. Erstellen Sie nun Hyperlinks für die Inhalte des Verzeichnisses. Verwenden Sie dazu die folgende Formel:

=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")

8. Wenn Sie jetzt auf den Hyperlinktext klicken, gelangen Sie sofort zu diesem Blatt. Und wenn Sie ein neues Blatt einfügen, ein Blatt löschen oder den Namen eines Blatts ändern, wird das Inhaltsverzeichnis automatisch aktualisiert.

Verwenden Sie Kutools für Excel, um ein Inhaltsverzeichnis für alle Arbeitsblätter zu erstellen, das sich automatisch aktualisiert

Wenn Sie "Kutools für Excel" haben, kann die "Navigation" Ihnen helfen, alle Blattnamen vertikal in einem linken Bereich aufzulisten und schnell und einfach zu einem bestimmten Blatt zu navigieren.

Nach der Installation von Kutools für Excel gehen Sie bitte wie folgt vor:

1. Klicken Sie auf "Kutools" > "Navigation", siehe Screenshot:

2. Im erweiterten "Navigation"-Bereich klicken Sie auf das Symbol "Arbeitsmappe & Blatt". Alle geöffneten Arbeitsmappen werden in der oberen Liste angezeigt, und alle sichtbaren Blätter innerhalb der Arbeitsmappe werden in der unteren Liste angezeigt, siehe Screenshot:

3. Nun können Sie durch Klicken auf den Blattnamen im linken Bereich zu dem Blatt navigieren. Wenn Sie ein Blatt löschen, einfügen oder umbenennen, wird die Blattliste im Bereich dynamisch aktualisiert.

Verwenden Sie VBA-Code, um ein Inhaltsverzeichnis für alle Arbeitsblätter zu erstellen, das sich automatisch aktualisiert

Manchmal müssen die ausgeblendeten Blätter nicht im Inhaltsverzeichnis angezeigt werden. Um dieses Problem zu lösen, kann der folgende VBA-Code helfen.

1. Fügen Sie ein neues Arbeitsblatt vor allen anderen Blättern ein, wo Sie das Inhaltsverzeichnis erstellen möchten, und benennen Sie es nach Bedarf um. Klicken Sie dann mit der rechten Maustaste auf die Blattregisterkarte und wählen Sie "Code anzeigen" aus dem Kontextmenü, siehe Screenshot:

2. Kopieren und fügen Sie im geöffneten Fenster "Microsoft Visual Basic for Applications" den folgenden Code in das Codefenster des Blatts ein:

VBA-Code: Erstellen Sie ein Inhaltsverzeichnis für alle Arbeitsblätter, das sich automatisch aktualisiert

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. Drücken Sie dann die Taste "F5", um diesen Code auszuführen, und das Inhaltsverzeichnis wird sofort im neuen Arbeitsblatt erstellt. Alle ausgeblendeten Blätter werden nicht im Inhaltsverzeichnis angezeigt, siehe Screenshot:

4. Ab sofort wird das Inhaltsverzeichnis dynamisch aktualisiert, wenn Sie Blätter löschen, einfügen oder umbenennen.