如何為所有工作表創建目錄並自動更新？

Author Xiaoyang Last modified 2025-08-23

假設您有一個包含數百個工作表的工作簿，要在多個工作表中導航到特定的工作表可能會讓我們大多數人感到頭疼。在這種情況下，為這些工作表創建目錄可以幫助您快速輕鬆地跳轉到某個工作表。本教程將介紹如何為所有工作表創建目錄，並在插入、刪除或更改工作表名稱時自動更新目錄。

使用公式為所有工作表創建目錄並自動更新

使用 Kutools for Excel 為所有工作表創建目錄並自動更新

使用 VBA 代碼為所有工作表創建目錄並自動更新

使用公式為所有工作表創建目錄並自動更新

在 Excel 中，您可以通過定義一個儲存格名稱並在公式中引用該名稱來創建目錄，請按照以下步驟操作：

1. 在所有工作表之前插入一個新工作表作為目錄，並將其重命名為您需要的名稱。

2. 接著，點擊「公式」>「定義名稱」，見截圖：

click Define Name under Formula tab

3. 在「新建名稱」對話框中，在「名稱」框中指定一個名稱「Sheetlist」，您可以根據需要更改它，然後在「引用位置」文本框中輸入以下公式：

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

type a name and formula into the dialog box

4. 然後，點擊「確定」按鈕關閉對話框。

5. 現在，請進入您要創建目錄的新工作表，然後在空白單元格中輸入以下公式：

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

注意：在上述公式中，「Sheetlist」是您在第 2 步中創建的儲存格名稱。

6. 然後向下拖動填充柄直到顯示空白單元格，當前工作簿中的所有工作表名稱（包括隱藏的工作表）都已列出，如下截圖所示：

enter a formula and drag to display all sheet names

7. 接下來，您應該為目錄內容創建超鏈接，請使用以下公式：

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

注意：在上述公式中，「A2」是包含工作表名稱的單元格，而「A1」是您希望在該工作表中跳轉到的單元格。例如，如果您點擊超鏈接文字，它將定位到該工作表的 A1 單元格。

apply a formula to create hyperlinks for each sheet name

8. 現在，當您點擊超鏈接文字時，它會立即跳轉到該工作表。並且如果您插入新工作表、刪除工作表或更改工作表名稱，目錄內容將自動更新。

注意：

1. 使用此方法，所有隱藏的工作表也會顯示在目錄內容中。
2. 您應將文件保存為「Excel 啟用宏的工作簿」格式，這樣下次打開文件時，公式才能正常運行。

使用 Kutools for Excel 為所有工作表創建目錄並自動更新

如果您有「Kutools for Excel」，它的「導航窗格」可以幫助您將所有工作表名稱垂直列在左側窗格中，並快速輕鬆地導航到特定的工作表。

Kutools for Excel 提供超過 300 種進階功能，簡化複雜任務，提升創造力與效率。 結合 AI 能力，Kutools 能夠精準自動化任務，讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

安裝 Kutools for Excel 後，請執行以下操作：

1. 點擊「Kutools」>「導航」，見截圖：

Click Kutools > Navigation

2. 在展開的「導航」窗格中，點擊「工作簿和工作表」圖標，所有打開的工作簿都會列在頂部列表框中，而工作簿內的所有可見工作表則顯示在底部列表框中，見截圖：

click Workbook & Sheet icon, all opened workbooks and all visible sheets are displayed in the pane

3. 現在，您只需點擊左側窗格中的工作表名稱即可跳轉到該工作表。當您刪除、插入或重命名工作表時，窗格中的工作表列表將動態更新。

提示：默認情況下，隱藏的工作表不會顯示在導航窗格中，如果您想顯示隱藏的工作表，只需點擊「按下按鈕將顯示隱藏的工作表。彈起時再次隱藏它們。」圖標，再次點擊該圖標即可立即隱藏工作表。

click the Toggle to unhide / hide all hidden worksheet(s) icon to display hidden sheets

使用 VBA 代碼為所有工作表創建目錄並自動更新

有時候，您不需要在目錄內容中顯示隱藏的工作表。為了解決這個問題，以下 VBA 代碼可以幫您忙。

1. 在所有工作表之前插入一個新工作表作為目錄，並將其重命名為您需要的名稱。然後，右鍵單擊工作表標籤，並從上下文菜單中選擇「查看代碼」，見截圖：

right click the sheet tab, and choose View Code

2. 在打開的「Microsoft Visual Basic for Applications」窗口中，將以下代碼複製並粘貼到工作表代碼窗口中：

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

copy and paste the code into the module

3. 然後，按「F5」鍵運行此代碼，目錄內容將立即在新工作表中創建，所有隱藏的工作表將不會顯示在目錄內容中，見截圖：

run code to create a table contents

4. 從現在開始，當刪除、插入或重命名工作表時，目錄內容將動態更新。

注意：您還應該將 Excel 文件保存為「Excel 啟用宏的工作簿」格式，以便下次打開工作簿時代碼能夠成功運行。

最佳 Office 生產力工具

🤖	Kutools AI 助手：以智能執行為基礎，革新數據分析｜生成程式碼｜創建自訂公式｜分析數據並生成圖表｜調用 Kutools 增強函數…
	熱門功能：查找、選取項目的背景色或標記重複值｜刪除空行｜合併列或單元格且不遺失數據｜四捨五入（免公式）...
	高級 LOOKUP：多條件 VLookup｜多值 VLookup｜多表查找｜模糊查找...
	高級下拉列表：快速創建下拉列表｜依賴型下拉列表｜多選下拉列表...
	列管理器：添加指定數量的列｜移動列｜切換隱藏列的顯示狀態｜比較區域及列...
	精選功能：網格聚焦｜設計檢視｜增強編輯欄｜工作簿及工作表管理器｜資源庫（快捷文本）｜日期提取器｜合併資料｜加密/解密儲存格｜按列表發送電子郵件｜超級篩選｜特殊篩選（篩選粗體/傾斜/刪除線...）...
	15 大工具集：12 項文本工具（添加文本、刪除特定字符…）｜50+ 儀表類型（甘特圖等）｜40+ 實用公式（基於生日計算年齡等）｜19 項插入工具（插入QR码、根據路徑插入圖片等）｜12 項轉換工具（金額轉大寫、匯率轉換等）｜7 項合併與分割工具（高級合併行、分割儲存格等）｜...及更多