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

作者Xiaoyang修改日期2025-04-17

假設您有一個包含數百個工作表的活頁簿，在眾多工作表中快速找到特定工作表，對多數人來說可能相當頭痛。此時，為這些工作表建立一份目錄，就能讓您輕鬆又迅速地跳轉至目標工作表。本教學將說明如何為所有工作表建立目錄，並在插入、刪除或變更工作表名稱時自動更新該目錄。

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

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

使用 VBA 程式碼為所有工作表建立目錄並自動更新

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

在 Excel 中，您可以透過定義一個儲存格名稱，並在公式中參照該名稱來建立目錄，請依照下列步驟操作：

1. 在所有工作表前方插入一個新工作表（即您要用來建立目錄的位置），並依需求重新命名。

2. 接著，請點選「公式」>「定義名稱」，詳情請參閱螢幕截圖：

按一下「公式」索引標籤下的「定義名稱」

3. 在「新增名稱」對話方塊中，於「名稱」欄位輸入「Sheetlist」（您可依需求自行修改），並在「參照位置」文字方塊中輸入下列公式。

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

在對話方塊中輸入名稱與公式

4. 接著，點擊「確定」按鈕即可關閉對話方塊。

5. 現在，請前往您要建立目錄的新工作表，在空白儲存格中輸入下列公式：

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

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

6. 接著，向下拖曳填滿控點至儲存格，直到出現空白儲存格為止，此時目前工作簿的所有工作表名稱（包含隱藏的工作表）便會如以下螢幕截圖所示列出：

輸入公式並拖曳以顯示所有工作表名稱

7. 接著，請使用下列公式為目錄內容建立超連結：

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

注意：上述公式中的「A2」是包含工作表名稱的儲存格，而「A1」是您要在該工作表中跳轉至的儲存格。例如，當您按一下超連結文字時，就會定位至該工作表的 A1 儲存格。

套用公式為每個工作表名稱建立超連結

8. 現在，只需點擊超連結文字，即可立即跳轉至對應的工作表。當您在插入新工作表、刪除工作表或變更工作表名稱時，目錄內容也會自動同步更新。

注意事項：

1. 使用此方法，所有隱藏的工作表也會顯示於目錄中。
2. 請務必將檔案儲存為「Excel 啟用巨集活頁簿」格式，以確保下次開啟時公式能正常運作。

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

若您已安裝「Kutools for Excel」，其「導航」功能可將所有工作表名稱垂直列於左側窗格，助您快速輕鬆地切換至指定工作表。

Kutools for Excel 提供超過 300 項進階功能，簡化複雜作業，提升創造力與效率。整合 AI 功能，Kutools 能精準自動化各項任務，讓資料管理輕鬆無負擔。Kutools for Excel 的詳細資訊…… 免費試用……

安裝 Kutools for Excel 後，請依照下列步驟操作：

1. 點選「Kutools」>「導航」，請參閱下方螢幕截圖：

按一下 Kutools > 導覽

2. 在展開的「導航」窗格中，點選「工作簿和表」圖示，所有已開啟的活頁簿將顯示於上方清單方塊，而各活頁簿內的所有可見工作表則會顯示於下方清單方塊，請參閱螢幕截圖：

按一下「活頁簿與工作表」圖示，所有已開啟的活頁簿及所有可見工作表都會顯示在窗格中

3. 現在，只需點擊左側窗格中的工作表名稱，即可立即切換至該工作表。當您在窗格中刪除、插入或重新命名工作表時，工作表清單也會即時動態更新。

提示：預設情況下，隱藏的工作表不會顯示在導航中；若要顯示這些工作表，只需點擊「按下按鈕將顯示隱藏的工作表，彈起時再次隱藏它們」圖示，再次點擊即可立即重新隱藏。

按一下「切換顯示/隱藏所有隱藏工作表」圖示以顯示隱藏的工作表

使用 VBA 程式碼為所有工作表建立目錄並自動更新

有時您可能不希望在目錄中顯示隱藏的工作表。針對此需求，以下 VBA 程式碼可協助您輕鬆達成目標。

1. 在所有工作表前方插入一個新工作表（即您要建立目錄的位置），並依需求重新命名。接著，以滑鼠右鍵點擊該工作表標籤，再從快捷功能表中選擇「檢視程式碼」，請參閱以下螢幕截圖：

在工作表標籤上按右鍵，然後選擇「檢視程式碼」

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

將程式碼複製並貼到模組中

3. 接著，按下「F5」鍵執行此程式碼，目錄內容將立即建立於新工作表中，且所有隱藏的工作表皆不會出現在目錄裡，請參閱螢幕截圖：

執行程式碼以建立目錄

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

注意：您也應將 Excel 檔案儲存為「Excel 啟用巨集活頁簿」格式，以確保下次開啟活頁簿時程式碼能順利執行。

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多