Come creare una tabella dei contenuti per tutti i fogli e aggiornarla automaticamente?

Supponiamo che tu abbia una cartella di lavoro che contiene centinaia di fogli di lavoro, navigare verso un foglio specifico tra più fogli potrebbe essere un problema per la maggior parte di noi. In questo caso, creare una tabella dei contenuti per questi fogli di lavoro potrebbe aiutarti a raggiungere rapidamente e facilmente il foglio desiderato. Questa guida parlerà di come creare una tabella dei contenuti per tutti i fogli e aggiornare automaticamente la tabella quando si inseriscono, eliminano o cambiano i nomi dei fogli.

Usa una formula per creare una tabella dei contenuti per tutti i fogli e aggiornarla automaticamente

Usa Kutools per Excel per creare una tabella dei contenuti per tutti i fogli e aggiornarla automaticamente

Usa il codice VBA per creare una tabella dei contenuti per tutti i fogli e aggiornarla automaticamente

Usa una formula per creare una tabella dei contenuti per tutti i fogli e aggiornarla automaticamente

In Excel, puoi creare una tabella dei contenuti definendo un nome di intervallo e facendo riferimento al nome in una formula; procedi con i seguenti passaggi:

1. Inserisci un nuovo foglio prima di tutti i fogli dove vuoi creare la tabella dei contenuti e rinominalo come necessario.

2. Quindi, clicca su "Formula" > "Definisci Nome", vedi screenshot:

3. Nella finestra di dialogo "Nuovo Nome", specifica un nome "Sheetlist" nel campo "Nome", puoi cambiarlo come preferisci, quindi inserisci la seguente formula nel campo "Si riferisce a":

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

4. Poi, clicca sul pulsante "OK" per chiudere la finestra di dialogo.

5. Ora, vai al nuovo foglio dove vuoi creare una tabella dei contenuti, quindi inserisci la seguente formula in una cella vuota:

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

6. Dopodiché trascina la maniglia di riempimento verso il basso fino a quando non vengono visualizzate celle vuote, e tutti i nomi dei fogli (compresi i fogli nascosti) della cartella di lavoro corrente saranno elencati come mostrato nello screenshot qui sotto:

7. Successivamente, dovresti creare collegamenti ipertestuali per i contenuti della tabella, utilizza la seguente formula:

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

8. Ora, quando fai clic sul testo del collegamento ipertestuale, andrà immediatamente a quel foglio. E se inserisci un nuovo foglio, elimini un foglio o cambi il nome del foglio, i contenuti della tabella verranno aggiornati automaticamente.

Usa Kutools per Excel per creare una tabella dei contenuti per tutti i fogli e aggiornarla automaticamente

Se hai "Kutools per Excel", il suo "Riquadro di Navigazione" può aiutarti a elencare tutti i nomi dei fogli in un riquadro sinistro verticalmente, e navigare rapidamente ed facilmente verso il foglio specifico.

Dopo aver installato Kutools per Excel, procedi come segue:

1. Clicca su "Kutools" > "Navigazione", vedi screenshot:

2. Nel riquadro "Navigazione" espanso, clicca sull'icona "Cartella di lavoro e Foglio", tutte le cartelle di lavoro aperte sono elencate nella casella di selezione in alto, e tutti i fogli visibili all'interno della cartella di lavoro sono visualizzati nella casella di selezione in basso, vedi screenshot:

3. E ora, puoi andare al foglio semplicemente cliccando sul nome del foglio dal riquadro sinistro. E quando elimini, inserisci o rinomini un foglio, l'elenco dei fogli nel riquadro verrà aggiornato dinamicamente.

Usa il codice VBA per creare una tabella dei contenuti per tutti i fogli e aggiornarla automaticamente

A volte, non è necessario visualizzare i fogli nascosti nei contenuti della tabella. Per risolvere questo problema, il seguente codice VBA può esserti utile.

1. Inserisci un nuovo foglio prima di tutti i fogli dove vuoi creare la tabella dei contenuti e rinominalo come necessario. Poi, fai clic con il tasto destro sulla scheda del foglio e scegli "Visualizza Codice" dal menu contestuale, vedi screenshot:

2. Nella finestra aperta "Microsoft Visual Basic for Applications", copia e incolla il codice sottostante nella finestra del codice del foglio:

Codice VBA: Crea una tabella dei contenuti per tutti i fogli e aggiorna automaticamente

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. Quindi, premi il tasto "F5" per eseguire questo codice, e i contenuti della tabella vengono creati immediatamente nel nuovo foglio di lavoro, tutti i fogli nascosti non verranno visualizzati nei contenuti della tabella, vedi screenshot:

4. Da adesso in poi, quando elimini, inserisci o rinomini un foglio, i contenuti della tabella verranno aggiornati dinamicamente.