如何為所有工作表創(chuàng)建目錄并自動更新？

網(wǎng)友投稿 370 2025-04-02

如何為所有工作表創(chuàng)建目錄并自動更新？

假設(shè)您有一個包含數(shù)百個工作表的工作簿，導(dǎo)航到多個工作表中的特定工作表可能會讓我們大多數(shù)人感到頭疼。在這種情況下，為這些工作表創(chuàng)建目錄可以幫助您快速輕松地轉(zhuǎn)到某個工作表。本教程將討論如何為所有工作表創(chuàng)建目錄，并在插入、刪除或更改工作表名稱時自動更新表格。

為所有工作表創(chuàng)建目錄并使用公式自動更新

為所有工作表創(chuàng)建目錄并使用 VBA 代碼自動更新

使用強大的功能為所有工作表創(chuàng)建目錄并自動更新

為所有工作表創(chuàng)建目錄并使用公式自動更新

在 Excel 中，您可以通過定義范圍名稱并在公式中引用名稱來創(chuàng)建目錄，請執(zhí)行以下步驟：

1. 在要創(chuàng)建目錄的所有工作表之前插入一個新工作表，并根據(jù)需要重命名。

2。然后，點擊公式 > 定義名稱，請參見屏幕截圖：

3。在 新名字 對話框，指定名稱 工作表 到 Name 框，你可以把它改成你自己的，然后輸入這個公式： = GET.WORKBOOK（1）＆T（NOW（）） 到指文本框，請參見屏幕截圖：

4。然后，點擊 OK 按鈕關(guān)閉對話框。

5. 現(xiàn)在，請轉(zhuǎn)到要在其中創(chuàng)建目錄的新工作表，然后在空白單元格中輸入以下公式：

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

備注：在以上公式中， 工作表 是您在步驟 2 中創(chuàng)建的范圍名稱。

6. 然后將填充手柄向下拖動到單元格，直到顯示空白單元格，并且當(dāng)前工作簿的所有工作表名稱（包括隱藏的工作表）都已列出，如下圖所示：

7. 然后，您應(yīng)該為表格內(nèi)容創(chuàng)建超鏈接，請使用以下公式：

=HYPERLINK("#"&A2&"!A1","轉(zhuǎn)到工作表")

備注：在以上公式中， A2 是包含工作表名稱的單元格，并且 A1 是您要在該工作表中跳轉(zhuǎn)到的單元格。例如，如果您單擊超鏈接文本，它將定位該工作表的單元格 A1。

8. 現(xiàn)在，當(dāng)單擊超鏈接文本時，它將立即轉(zhuǎn)到該表。如果您插入新工作表、刪除工作表或更改工作表名稱，表格內(nèi)容將自動更新。

: 1. 使用此方法，所有隱藏的工作表也會顯示到表格內(nèi)容中。 2.您應(yīng)該將文件另存為 Excel啟用宏的工作簿 格式，這樣下次打開這個文件時，公式就可以正常工作了。

為所有工作表創(chuàng)建目錄并使用 VBA 代碼自動更新

有時，您不需要在表格內(nèi)容中顯示隱藏的工作表。為了解決這個問題，下面的 VBA 代碼可以幫你一個忙。

1. 在要創(chuàng)建目錄的所有工作表之前插入一個新工作表，并根據(jù)需要重命名。然后，右鍵單擊工作表選項卡，然后選擇 查看代碼 從上下文菜單中，查看屏幕截圖：

2。在開 Microsoft Visual Basic應(yīng)用程序 窗口，將以下代碼復(fù)制并粘貼到工作表代碼窗口中：

VBA 代碼：為所有工作表創(chuàng)建目錄并自動更新

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

Copy

3。然后按 F5 運行此代碼的關(guān)鍵，并且表格內(nèi)容在新工作表中立即創(chuàng)建，所有隱藏的工作表都不會顯示在表格內(nèi)容中，見截圖：

4. 現(xiàn)在表格，當(dāng)刪除、插入或重命名表格時，表格內(nèi)容將動態(tài)更新。

備注：您還應(yīng)該將 Excel 文件另存為 Excel啟用宏的工作簿 格式以在您下次打開工作簿時保持代碼成功運行。

使用強大的功能為所有工作表創(chuàng)建目錄并自動更新

如果你有 Kutools for Excel，它的 導(dǎo)航窗格 可以幫助您將所有工作表名稱垂直列出到左側(cè)窗格中，并快速輕松地導(dǎo)航到特定工作表。

安裝后 Kutools for Excel，請這樣做：

1. 點擊 Kutools > 旅游導(dǎo)航 ，請參見屏幕截圖：

2。在擴(kuò)展中 旅游導(dǎo)航 窗格中單擊 工作簿和工作表 圖標(biāo)，所有打開的工作簿都列在頂部列表框中，工作簿中所有可見的工作表都顯示在底部列表框中，見截圖：

3. 現(xiàn)在，您只需單擊左側(cè)窗格中的工作表名稱即可轉(zhuǎn)到工作表。并且當(dāng)您刪除、插入或重命名工作表時，窗格中的工作表列表將動態(tài)更新。

Tips：默認(rèn)情況下，隱藏的工作表不顯示在導(dǎo)航窗格中，如果要顯示隱藏的工作表，只需單擊 切換以取消隱藏/隱藏所有隱藏的工作表 圖標(biāo)，再次單擊此圖標(biāo)可立即隱藏隱藏的工作表。

最佳辦公生產(chǎn)力工具

將小時轉(zhuǎn)化為分鐘 Kutools for Excel!

準(zhǔn)備好增強您的 Excel 任務(wù)了嗎？利用的力量 Kutools for Excel - 您終極的節(jié)省時間的工具。簡化復(fù)雜的任務(wù)并像專業(yè)人士一樣瀏覽數(shù)據(jù)。以閃電般的速度體驗 Excel！