Bagaimana cara membuat daftar isi untuk semua lembar dan memperbarui secara otomatis?
Misalkan, Anda memiliki buku kerja yang berisi ratusan lembar kerja, untuk menavigasi ke lembar tertentu di antara beberapa lembar dapat menyebabkan sakit kepala bagi kebanyakan dari kita. Dalam hal ini, membuat daftar isi untuk lembar kerja ini dapat membantu Anda membuka lembar tertentu dengan cepat dan mudah. Tutorial ini akan berbicara tentang cara membuat daftar isi untuk semua lembar dan memperbarui tabel secara otomatis saat memasukkan, menghapus, atau mengubah nama lembar juga.
Buat daftar isi untuk semua lembar dan perbarui secara otomatis dengan rumus
Buat daftar isi untuk semua lembar dan perbarui secara otomatis dengan kode VBA
Buat daftar isi untuk semua lembar dan perbarui secara otomatis dengan fitur canggih
Buat daftar isi untuk semua lembar dan perbarui secara otomatis dengan rumus
Di Excel, Anda dapat membuat daftar isi dengan menentukan nama rentang, dan mereferensikan nama dalam rumus, lakukan dengan langkah-langkah berikut:
1. Sisipkan lembar baru sebelum semua lembar tempat Anda ingin membuat daftar isi dan ganti namanya sesuai kebutuhan.
2. Lalu klik Rumus > Tentukan Nama, lihat tangkapan layar:
3. di Nama baru kotak dialog, tentukan nama Daftar Lembar ke dalam Nama kotak, Anda dapat mengubahnya sendiri, lalu masukkan rumus ini: = GET.WORKBOOK (1) & T (SEKARANG ()) ke dalam Mengacu pada kotak teks, lihat tangkapan layar:
4. Lalu klik OK tombol untuk menutup kotak dialog.
5. Sekarang, silakan masuk ke lembar baru tempat Anda ingin membuat daftar isi, lalu masukkan rumus berikut ke dalam sel kosong:
6. Dan kemudian seret gagang isian ke sel hingga sel kosong ditampilkan, dan semua nama lembar (termasuk lembar tersembunyi) dari buku kerja saat ini telah terdaftar seperti gambar di bawah ini yang diperlihatkan:
7. Kemudian, Anda harus membuat hyperlink untuk isi tabel, silakan gunakan rumus di bawah ini:
8. Sekarang, ketika mengklik teks hyperlink, itu akan pergi ke lembar itu sekaligus. Dan jika Anda memasukkan lembar baru, menghapus lembar atau mengubah nama lembar, isi tabel akan diperbarui secara otomatis.
- 1. Dengan metode ini, semua lembar tersembunyi ditampilkan ke dalam isi tabel juga.
- 2. Anda harus menyimpan file sebagai Buku Kerja Excel dengan Makro Aktif format, dengan cara ini, ketika Anda membuka file ini lain kali, rumus dapat bekerja dengan baik.
Buat daftar isi untuk semua lembar dan perbarui secara otomatis dengan kode VBA
Terkadang, Anda tidak perlu menampilkan lembar tersembunyi di konten tabel. Untuk mengatasi masalah ini, kode VBA berikut dapat membantu Anda.
1. Sisipkan lembar baru sebelum semua lembar tempat Anda ingin membuat daftar isi dan ganti namanya sesuai kebutuhan. Kemudian, klik kanan tab lembar, dan pilih Lihat kode dari menu konteks, lihat tangkapan layar:
2. Di tempat terbuka Microsoft Visual Basic untuk Aplikasi jendela, salin dan tempel kode di bawah ini ke dalam jendela kode lembar:
Kode VBA: Buat daftar isi untuk semua lembar dan perbarui secara otomatis
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. Lalu tekan F5 kunci untuk menjalankan kode ini, dan isi tabel dibuat sekaligus di lembar kerja baru, semua lembar tersembunyi tidak akan ditampilkan dalam isi tabel, lihat tangkapan layar:
4. Formulir sekarang, saat menghapus, menyisipkan atau mengganti nama lembar, isi tabel akan diperbarui secara dinamis.
Buat daftar isi untuk semua lembar dan perbarui secara otomatis dengan fitur canggih
Jika Anda memiliki Kutools untuk Excel, nya Panel navigasi dapat membantu Anda membuat daftar semua nama sheet ke panel kiri secara vertikal, dan menavigasi ke sheet tertentu dengan cepat dan mudah.
Setelah menginstal Kutools untuk Excel, lakukan seperti ini:
1. Klik Kutools > Navigasi, lihat tangkapan layar:
2. Di diperluas Navigasi pane, klik Buku Kerja & Lembar ikon, semua buku kerja yang dibuka terdaftar di kotak daftar atas, dan semua lembar yang terlihat di dalam buku kerja ditampilkan di kotak daftar bawah, lihat tangkapan layar:
3. Dan sekarang, Anda dapat pergi ke lembar hanya dengan mengklik nama lembar dari panel kiri. Dan saat Anda menghapus, menyisipkan, atau mengganti nama lembar, daftar lembar di panel akan diperbarui secara dinamis.
Alat Produktivitas Kantor Terbaik
Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu. Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...
Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah
- Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
- Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
- Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
