Wie erstelle ich Inhaltsverzeichnisse für alle Blätter und Updates automatisch?
Angenommen, Sie haben eine Arbeitsmappe, die Hunderte von Arbeitsblättern enthält, und das Navigieren zu einem bestimmten Blatt unter mehreren Blättern kann den meisten von uns Kopfschmerzen bereiten. In diesem Fall kann es Ihnen helfen, ein Inhaltsverzeichnis für diese Arbeitsblätter zu erstellen, um schnell und einfach zu dem bestimmten Blatt zu gelangen. In diesem Tutorial erfahren Sie, wie Sie ein Inhaltsverzeichnis für alle Blätter erstellen und die Tabelle automatisch aktualisieren, wenn Sie die Blattnamen einfügen, löschen oder ändern.
Erstellen Sie ein Inhaltsverzeichnis für alle Blätter und Updates automatisch mit einem VBA-Code
Erstellen Sie ein Inhaltsverzeichnis für alle Blätter und aktualisieren Sie es automatisch mit Formeln
In Excel können Sie ein Inhaltsverzeichnis erstellen, indem Sie einen Bereichsnamen definieren und den Namen in einer Formel referenzieren. Führen Sie dazu bitte die folgenden Schritte aus:
1. Fügen Sie vor allen Blättern, in denen Sie das Inhaltsverzeichnis erstellen möchten, ein neues Blatt ein und benennen Sie es nach Bedarf um.
2. Dann klick Formel > Name definieren, siehe Screenshot:
3. In dem Neuer Name Geben Sie im Dialogfeld einen Namen an Blattliste in die Name und Vorname Sie können es in Ihr eigenes Feld ändern und dann diese Formel eingeben: = GET.WORKBOOK (1) & T (JETZT ()) in die Bezieht sich auf Textfeld, siehe Screenshot:
4. Dann klick OK Schaltfläche, um das Dialogfeld zu schließen.
5. Gehen Sie nun bitte zu dem neuen Blatt, in dem Sie ein Inhaltsverzeichnis erstellen möchten, und geben Sie dann die folgende Formel in eine leere Zelle ein:
6. Ziehen Sie dann den Ausfüllgriff nach unten zu den Zellen, bis leere Zellen angezeigt werden und alle Blattnamen (einschließlich der ausgeblendeten Blätter) der aktuellen Arbeitsmappe wie im folgenden Screenshot aufgeführt sind:
7. Dann sollten Sie Hyperlinks für die Tabelleninhalte erstellen, verwenden Sie bitte die folgende Formel:
8. Wenn Sie jetzt auf den Hyperlink-Text klicken, wird er sofort zu diesem Blatt weitergeleitet. Und wenn Sie ein neues Blatt einfügen, ein Blatt löschen oder den Blattnamen ändern, werden die Tabelleninhalte automatisch aktualisiert.
- 1. Bei dieser Methode werden alle ausgeblendeten Blätter auch im Tabelleninhalt angezeigt.
- 2. Sie sollten die Datei als speichern Excel Makroaktivierte Arbeitsmappe formatieren, auf diese Weise können die Formeln beim nächsten Öffnen dieser Datei gut funktionieren.
Erstellen Sie ein Inhaltsverzeichnis für alle Blätter und Updates automatisch mit einem VBA-Code
Manchmal müssen Sie die ausgeblendeten Blätter nicht im Tabelleninhalt anzeigen. Um dieses Problem zu lösen, kann Ihnen der folgende VBA-Code einen Gefallen tun.
1. Fügen Sie vor allen Blättern, in denen Sie das Inhaltsverzeichnis erstellen möchten, ein neues Blatt ein und benennen Sie es nach Bedarf um. Klicken Sie dann mit der rechten Maustaste auf die Blattregisterkarte und wählen Sie aus Code anzeigen Siehe im Kontextmenü Screenshot:
2. In der geöffneten Microsoft Visual Basic für Applikationen kopieren Sie den folgenden Code und fügen Sie ihn in das Blattcodefenster ein:
VBA-Code: Erstellen Sie automatisch ein Inhaltsverzeichnis für alle Blätter und Updates
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. Dann drücken F5 Schlüssel zum Ausführen dieses Codes, und der Tabelleninhalt wird sofort im neuen Arbeitsblatt erstellt, alle ausgeblendeten Blätter werden nicht im Tabelleninhalt angezeigt, siehe Screenshot:
4. Ab sofort wird beim Löschen, Einfügen oder Umbenennen von Blättern der Tabelleninhalt dynamisch aktualisiert.
Erstellen Sie mit einer leistungsstarken Funktion automatisch ein Inhaltsverzeichnis für alle Blätter und Updates
Wenn Sie Kutools for Excel, seine Navigationsbereich kann Ihnen dabei helfen, alle Blattnamen vertikal in einem linken Bereich aufzulisten und schnell und einfach zu einem bestimmten Blatt zu navigieren.
Nach der Installation Kutools for ExcelBitte machen Sie Folgendes:
1. Klicken Sie auf Kostenlos erhalten und dann auf Installieren. Kutoolen > Navigation, siehe Screenshot:
2. In der erweiterten Navigation Bereich, klicken Sie auf Arbeitsmappe & Blatt Symbol, alle geöffneten Arbeitsmappen werden im oberen Listenfeld aufgelistet, und alle sichtbaren Blätter innerhalb der Arbeitsmappe werden im unteren Listenfeld angezeigt, siehe Screenshot:
3. Und jetzt können Sie zum Blatt gehen, indem Sie einfach auf den Blattnamen im linken Bereich klicken. Und wenn Sie ein Blatt löschen, einfügen oder umbenennen, wird die Blattliste im Bereich dynamisch aktualisiert.
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, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen ...
Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich
- Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
- Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
