Πώς να δημιουργήσετε αυτόματα τον πίνακα περιεχομένων για όλα τα φύλλα και τις ενημερώσεις;

Αν υποθέσουμε ότι έχετε ένα βιβλίο εργασίας που περιέχει εκατοντάδες φύλλα εργασίας, η πλοήγηση σε ένα συγκεκριμένο φύλλο μεταξύ πολλών φύλλων μπορεί να προκαλέσει πονοκέφαλο στους περισσότερους από εμάς. Σε αυτήν την περίπτωση, η δημιουργία πίνακα περιεχομένων για αυτά τα φύλλα εργασίας μπορεί να σας βοηθήσει να μεταβείτε στο συγκεκριμένο φύλλο γρήγορα και εύκολα. Αυτό το σεμινάριο θα μιλήσει για το πώς να δημιουργήσετε έναν πίνακα περιεχομένων για όλα τα φύλλα και να ενημερώσετε τον πίνακα αυτόματα κατά την εισαγωγή, διαγραφή ή αλλαγή των ονομάτων των φύλλων επίσης.

Δημιουργήστε έναν πίνακα περιεχομένων για όλα τα φύλλα και ενημερώστε αυτόματα με τύπους

Δημιουργήστε έναν πίνακα περιεχομένων για όλα τα φύλλα και ενημερώστε αυτόματα με μια ισχυρή λειτουργία

Δημιουργήστε έναν πίνακα περιεχομένων για όλα τα φύλλα και ενημερώστε αυτόματα με έναν κωδικό VBA

Δημιουργήστε έναν πίνακα περιεχομένων για όλα τα φύλλα και ενημερώστε αυτόματα με τύπους

Στο Excel, μπορείτε να δημιουργήσετε έναν πίνακα περιεχομένων ορίζοντας ένα όνομα περιοχής και αναφέροντας το όνομα σε έναν τύπο, ακολουθήστε τα παρακάτω βήματα:

1. Εισαγάγετε ένα νέο φύλλο πριν από όλα τα φύλλα όπου θέλετε να δημιουργήσετε τον πίνακα περιεχομένων και μετονομάστε το όπως θέλετε.

2. Στη συνέχεια, κάντε κλικ στο κουμπί Τύπος > Ορίστε το όνομα, δείτε το στιγμιότυπο οθόνης:

3. Στην Νέο όνομα παράθυρο διαλόγου, καθορίστε ένα όνομα Λίστα φύλλων μέσα στο Όνομα πλαίσιο, μπορείτε να το αλλάξετε στο δικό σας και, στη συνέχεια, εισαγάγετε αυτόν τον τύπο: = GET.WORKBOOK (1) & T (ΤΩΡΑ ()) μέσα στο Αναφέρεται σε πλαίσιο κειμένου, δείτε το στιγμιότυπο οθόνης:

4. Στη συνέχεια, κάντε κλικ στο κουμπί OK για να κλείσετε το παράθυρο διαλόγου.

5. Τώρα, μεταβείτε στο νέο φύλλο όπου θέλετε να δημιουργήσετε έναν πίνακα περιεχομένων και, στη συνέχεια, εισαγάγετε τον ακόλουθο τύπο σε ένα κενό κελί:

6. Στη συνέχεια, σύρετε τη λαβή πλήρωσης προς τα κάτω στα κελιά έως ότου εμφανιστούν κενά κελιά και όλα τα ονόματα φύλλων (συμπεριλαμβανομένων των κρυφών φύλλων) του τρέχοντος βιβλίου εργασίας έχουν παρατεθεί όπως φαίνεται παρακάτω στιγμιότυπο οθόνης:

7. Στη συνέχεια, θα πρέπει να δημιουργήσετε υπερσυνδέσμους για τα περιεχόμενα του πίνακα, χρησιμοποιήστε τον παρακάτω τύπο:

8. Τώρα, όταν κάνετε κλικ στο κείμενο υπερ-σύνδεσης, θα μεταβεί σε αυτό το φύλλο αμέσως. Και εάν εισαγάγετε ένα νέο φύλλο, διαγράψετε ένα φύλλο ή αλλάξετε το όνομα του φύλλου, τα περιεχόμενα του πίνακα θα ενημερωθούν αυτόματα.

Δημιουργήστε έναν πίνακα περιεχομένων για όλα τα φύλλα και ενημερώστε αυτόματα με μια ισχυρή λειτουργία

Εάν έχετε Kutools για Excel, της Παράθυρο πλοήγησης μπορεί να σας βοηθήσει να απαριθμήσετε όλα τα ονόματα φύλλων σε ένα αριστερό παράθυρο κάθετα και να πλοηγηθείτε στο συγκεκριμένο φύλλο γρήγορα και εύκολα.

Μετά την εγκατάσταση Kutools για Excel, κάντε το ως εξής:

1. Πατήστε Kutools > Πλοήγηση, δείτε το στιγμιότυπο οθόνης:

2. Στην επέκταση Πλοήγηση Παράθυρο, κάντε κλικ στο κουμπί Βιβλίο & φύλλο εργασίας εικονίδιο, όλα τα ανοιγμένα βιβλία εργασίας παρατίθενται στο επάνω πλαίσιο λίστας και όλα τα ορατά φύλλα μέσα στο βιβλίο εργασίας εμφανίζονται στο κάτω πλαίσιο λίστας, δείτε στιγμιότυπο οθόνης:

3. Και τώρα, μπορείτε να μεταβείτε στο φύλλο απλώς κάνοντας κλικ στο όνομα του φύλλου από το αριστερό παράθυρο. Και όταν διαγράφετε, εισάγετε ή μετονομάζετε ένα φύλλο, η λίστα φύλλων στο παράθυρο θα ενημερώνεται δυναμικά.

Δημιουργήστε έναν πίνακα περιεχομένων για όλα τα φύλλα και ενημερώστε αυτόματα με έναν κωδικό VBA

Μερικές φορές, δεν χρειάζεται να εμφανίσετε τα κρυφά φύλλα στα περιεχόμενα του πίνακα. Για να λύσετε αυτό το πρόβλημα, ο παρακάτω κώδικας VBA μπορεί να σας κάνει τη χάρη.

1. Εισαγάγετε ένα νέο φύλλο πριν από όλα τα φύλλα όπου θέλετε να δημιουργήσετε τον πίνακα περιεχομένων και μετονομάστε το όπως θέλετε. Στη συνέχεια, κάντε δεξί κλικ στην καρτέλα του φύλλου και επιλέξτε Προβολή κωδικού από το μενού περιβάλλοντος, δείτε το στιγμιότυπο οθόνης:

2. Στο άνοιγμα Microsoft Visual Basic για εφαρμογές παράθυρο, αντιγράψτε και επικολλήστε τον παρακάτω κώδικα στο παράθυρο κώδικα φύλλου:

Κώδικας VBA: Δημιουργήστε αυτόματα έναν πίνακα περιεχομένων για όλα τα φύλλα και τις ενημερώσεις

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. Στη συνέχεια, πατήστε F5 κλειδί για την εκτέλεση αυτού του κώδικα και τα περιεχόμενα του πίνακα δημιουργούνται ταυτόχρονα στο νέο φύλλο εργασίας, όλα τα κρυφά φύλλα δεν θα εμφανίζονται στα περιεχόμενα του πίνακα, δείτε στιγμιότυπο οθόνης:

4. Από τώρα, κατά τη διαγραφή, την εισαγωγή ή τη μετονομασία φύλλου, τα περιεχόμενα του πίνακα θα ενημερώνονται δυναμικά.