Wie erstellt man ein automatisch aktualisiertes Inhaltsverzeichnis für alle Arbeitsblätter?
Angenommen, Sie haben eine Arbeitsmappe mit Hunderten von Arbeitsblättern. Das Auffinden eines bestimmten Blatts unter vielen Blättern kann für die meisten von uns zur Herausforderung werden. In einem solchen Fall hilft Ihnen ein Inhaltsverzeichnis dabei, schnell und einfach zum gewünschten Blatt zu navigieren. In diesem Tutorial wird erläutert, wie Sie ein Inhaltsverzeichnis für alle Blätter erstellen und dieses automatisch aktualisieren, sobald Blätter eingefügt, gelöscht oder umbenannt werden.
Verwenden Sie eine Formel, um ein automatisch aktualisiertes Inhaltsverzeichnis für alle Blätter zu erstellen
In Excel erstellen Sie ein Inhaltsverzeichnis, indem Sie einen Zellennamen definieren und in einer Formel darauf verweisen. Gehen Sie dazu wie folgt vor:
1. Fügen Sie ein neues Blatt vor allen anderen Blättern ein, auf dem das Inhaltsverzeichnis erstellt werden soll, und geben Sie ihm einen passenden Namen.
2. Klicken Sie anschließend 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 beliebig anpassen) und tragen Sie anschließend die folgende Formel in das Feld „Bezieht sich auf“ ein.
=GET.WORKBOOK(1)&T(NOW())
4. Klicken Sie anschließend auf „OK“, um das Dialogfeld zu schließen.
5. Wechseln Sie nun zum neuen Blatt, auf dem das Inhaltsverzeichnis erstellt werden soll, 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 anschließend den Ausfüllkorb nach unten, bis leere Zellen erscheinen. Daraufhin werden alle Blattnamen – einschließlich der ausgeblendeten Blätter – der aktuellen Arbeitsmappe wie im folgenden Screenshot dargestellt aufgelistet:
7. Anschließend erstellen Sie einen Hyperlink für das Inhaltsverzeichnis – verwenden Sie hierfür die folgende Formel:
=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")
8. Klicken Sie nun auf den Hyperlink-Text, gelangen Sie sofort zum entsprechenden Blatt. Zudem aktualisiert sich das Inhaltsverzeichnis automatisch, sobald Sie ein neues Blatt einfügen, ein Blatt löschen oder einen Blattnamen ändern.
- 1. Mit dieser Methode werden auch alle ausgeblendeten Blätter im Inhaltsverzeichnis angezeigt.
- 2. Speichern Sie die Datei im Format „Excel-Arbeitsmappe mit Makros aktiviert“, damit die Formeln beim nächsten Öffnen der Datei ordnungsgemäß funktionieren.
Verwenden Sie Kutools für Excel, um ein automatisch aktualisiertes Inhaltsverzeichnis für alle Blätter zu erstellen
Wenn Sie „Kutools für Excel“ installiert haben, hilft Ihnen die integrierte „Navigation“-Funktion, alle Blattnamen übersichtlich in einem linken Bereich vertikal anzuzeigen und mühelos zu jedem gewünschten Blatt zu wechseln.
Nach der Installation von Kutools für Excelführen Sie bitte Folgendes aus:
1. Klicken Sie auf „Kutools“ > „Navigation“ – siehe Screenshot:
2. Klicken Sie im geöffneten Navigationsbereich auf das Symbol „Arbeitsmappe und Blatt“. Im oberen Listenfeld werden alle geöffneten Arbeitsmappen angezeigt, im unteren alle sichtbaren Blätter der jeweiligen Arbeitsmappe – siehe Screenshot:
3. Ab sofort wechseln Sie mit einem Klick auf den jeweiligen Blattnamen im linken Bereich direkt zu diesem Blatt. Beim Löschen, Einfügen oder Umbenennen eines Blatts aktualisiert sich die Blattliste im Bereich dynamisch.
Verwenden Sie VBA-Code, um ein automatisch aktualisiertes Inhaltsverzeichnis für alle Blätter zu erstellen
Manchmal möchten Sie ausgeblendete Blätter nicht im Inhaltsverzeichnis anzeigen – der folgende VBA-Code hilft Ihnen dabei.
1. Fügen Sie ein neues Blatt vor allen anderen Blättern ein, auf dem das Inhaltsverzeichnis erstellt werden soll, und benennen Sie es passend um. Klicken Sie anschließend mit der rechten Maustaste auf die Blattregisterkarte und wählen Sie im Kontextmenü „Code anzeigen“ (siehe Screenshot).
2. Kopieren Sie im geöffneten Fenster „Microsoft Visual Basic for Applications“ den folgenden Code, und fügen Sie ihn in das Codefenster des entsprechenden Arbeitsblatts ein:
VBA-Code: Erstellen Sie ein automatisch aktualisiertes Inhaltsverzeichnis für alle Blätter
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 anschließend die Taste „F5“, um den Code auszuführen. Das Inhaltsverzeichnis wird sofort in einem neuen Arbeitsblatt erstellt – ausgeblendete Blätter werden dabei nicht berücksichtigt, siehe Screenshot:
4. Ab sofort aktualisiert sich das Inhaltsverzeichnis automatisch, sobald Blätter gelöscht, eingefügt oder umbenannt werden.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins