Hur skapar man innehållsförteckning för alla blad och uppdateringar automatiskt?

Om du antar att du har en arbetsbok som innehåller hundratals kalkylblad, att navigera till ett specifikt blad bland flera ark kan orsaka huvudvärk för de flesta av oss. I det här fallet kan att skapa en innehållsförteckning för dessa kalkylblad hjälpa dig att snabbt och enkelt gå till det specifika bladet. Den här handledningen kommer att prata om hur man skapar en innehållsförteckning för alla ark och uppdaterar tabellen automatiskt när man infogar, tar bort eller ändrar arknamnen också.

Skapa en innehållsförteckning för alla blad och uppdatera automatiskt med formler

Skapa en innehållsförteckning för alla blad och uppdatera automatiskt med en kraftfull funktion

Skapa en innehållsförteckning för alla blad och uppdatera automatiskt med en VBA-kod

Skapa en innehållsförteckning för alla blad och uppdatera automatiskt med formler

I Excel kan du skapa en innehållsförteckning genom att definiera ett intervallnamn och hänvisa till namnet i en formel. Gör så här:

1. Infoga ett nytt ark före alla ark där du vill skapa innehållsförteckningen och döp om det efter behov.

2. Klicka sedan Formel > Definiera namn, se skärmdump:

3. I Nytt namn dialogrutan, ange ett namn Bladlista i Namn rutan kan du ändra den till din egen och sedan ange den här formeln: = GET.WORKBOOK (1) & T (NU ()) i Refererar till textruta, se skärmdump:

4. Klicka sedan OK knappen för att stänga dialogrutan.

5. Gå nu till det nya arket där du vill skapa en innehållsförteckning och skriv sedan in följande formel i en tom cell:

6. Och dra sedan fyllningshandtaget ner till cellerna tills tomma celler visas och alla arknamn (inklusive de dolda arken) i den aktuella arbetsboken har listats som nedanstående skärmbild:

7. Sedan bör du skapa hyperlänkar för tabellinnehållet, använd formeln nedan:

8. Nu, när du klickar på hyperlänkstexten, kommer den att gå till det arket på en gång. Och om du infogar ett nytt ark, tar bort ett ark eller ändrar arknamnet uppdateras tabellinnehållet automatiskt.

Skapa en innehållsförteckning för alla blad och uppdatera automatiskt med en kraftfull funktion

Om du Kutools för Excel, dess Navigeringsfönstret kan hjälpa dig att lista alla arknamn i en vänster ruta vertikalt och snabbt och enkelt navigera till det specifika arket.

När du har installerat Kutools för Excel, gör så här:

1. Klicka Kutools > Navigering, se skärmdump:

2. I den utökade Navigering fönster, klicka Arbetsbok och ark ikon, alla öppnade arbetsböcker listas i den översta listrutan, och alla synliga ark i arbetsboken visas i den nedre listrutan, se skärmdump:

3. Och nu kan du gå till arket bara genom att klicka på arknamnet från den vänstra rutan. Och när du tar bort, infogar eller byter namn på ett arbetsblad kommer arklistan i rutan att uppdateras dynamiskt.

Skapa en innehållsförteckning för alla blad och uppdatera automatiskt med en VBA-kod

Ibland behöver du inte visa de dolda arken i tabellinnehållet. För att lösa detta problem kan följande VBA-kod göra dig en tjänst.

1. Infoga ett nytt ark före alla ark där du vill skapa innehållsförteckningen och döp om det efter behov. Högerklicka sedan på arkfliken och välj Visa kod från snabbmenyn, se skärmdump:

2. I det öppnade Microsoft Visual Basic för applikationer fönster, kopiera och klistra in nedanstående kod i arkkodsfönstret:

VBA-kod: Skapa en innehållsförteckning för alla blad och uppdatera automatiskt

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. Tryck sedan på F5 nyckel för att köra den här koden, och tabellinnehållet skapas på en gång i det nya kalkylbladet, alla dolda ark kommer inte att visas i tabellinnehållet, se skärmdump:

4. Formulär nu på, när du tar bort, infogar eller byter namn på ark, kommer tabellinnehållet att uppdateras dynamiskt.