萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 辦公室軟體 >  >> Excel 教學 >> Excel 綜合

Excel 2007 三秒建立工作表目錄:定義名稱 + 超連結 + 模板儲存

活頁簿工作表一多,切換靠捲動索引標籤太慢。利用 Excel 4.0 巨集函數 GET.WORKBOOK(1) 結合動態命名範圍,可建立自動更新的工作表目錄,並另存為巨集模板,未來新檔案三秒內即可插入現成目錄。

核心原理

  • GET.WORKBOOK(1):取得所有工作表名稱(含檔名),屬 Excel 4.0 巨集函數,需啟用巨集才能運算。
  • INDEX(..., $A1):依 A 欄流水號取第 N 個工作表名。
  • T(NOW()):NOW() 為易失性函數,任何變動觸發重算,T() 轉為空字串不影響結果,解決 GET.WORKBOOK 不自動重算的問題。
  • HYPERLINK 建立超連結,點擊即跳轉。

建立步驟

  1. 重新命名第一張工作表為「目錄」。
  2. B1 儲存格 → 公式 → 定義名稱:
    • 名稱:工作表名
    • 引用位置:=INDEX(GET.WORKBOOK(1),$A1)&T(NOW())
  3. A1 輸入 1,B1 輸入超連結公式:
    =IFERROR(HYPERLINK(工作表名&"!A1",RIGHT(工作表名,LEN(工作表名)-FIND("]",工作表名))),"")
  4. 選取 A1:B1 向下拖曳填充 300 列(視最大工作表數而定)。
  5. 美化格式(字體、欄寬),刪除其他工作表,僅保留「目錄」表。
  6. Office 按鈕 → 另存為 → 類型選 Excel 啟用巨集的模板 (*.xltm) → 檔名 目錄.xltm → 存至 XLSTART 啟動資料夾(通常 C:\Program Files\Microsoft Office\Office12\XLSTART)。

使用方法(三秒完成)

  1. 開啟需建立目錄的活頁簿。
  2. 第一張工作表索引標籤右鍵 → 「插入」 → 選擇「目錄」。
  3. 自動插入「目錄」工作表並列出所有工作表超連結。

注意事項

  • 檔案必須另存為 .xlsm(啟用巨集活頁簿),否則 GET.WORKBOOK 失效。
  • 開啟時若出現「巨集已停用」警告,需點「選項」→「啟用此內容」。
  • 工作表超過 300 個需再向下複製公式。
  • 新增/刪除/重命名工作表後,目錄自動更新(需重算 F9 或儲存重開)。

優缺點權衡

優點缺點
零 VBA 代碼、純公式、自動更新需啟用巨集、需存為 .xlsm/.xltm
模板化後新檔三秒可用舊版 Excel 相容性需測試
copyright © 萬盛學電腦網 all rights reserved