Excel表格中目錄怎麼生成（excel表格怎樣做目錄）

2023-07-25 11:42:40

在Excel表格插入工作表目錄，是一個老生常談的技巧。但大數多使用者只會套用，並不懂其中的原理，畢竟製作過程太過複雜。所以，離開了教程也做不出來了。

為了讓大家可以隨時隨地做自已做目錄，今天小編就剖析一下製作Excel目錄的過程。

製作過程：

1、公式 - 定義名稱：shname

=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1)) 1,99)&T(NOW())

乖乖，好多陌生的函式....新手看了這個公式估計要暈掉。別急，小編一步步幫大家分析。

在Excel中有一類函式叫巨集表函式，功能非常強大，可以提取Excel或電腦的資訊，比如提取單元格顏色，提取檔案目錄。今天要用到的是一個可以提取所有工作表名稱的函式：Get.Workbook

由於巨集表函式只能在定義名稱中使用，所以必須先定義名稱：

公式 - 定義名稱 - 輸入自定義的名稱 - 在引用位置輸入公式:

=get.workbook(1)

注: get.workbook的引數是1時，可以提取所有工作表名稱

定義的名稱可以在單元格公式中直接使用，比如輸入 =Shname即可返回所有工作表名稱。（選中公式按F9可以檢視所有值）

由於返回的工作表名稱前含工作簿名稱“[抖音Excel技巧集.xlsm]”，所以下一步把用函式它刪除：

用Find函式查詢"]'的位置，然後用MID函式擷取。

=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1)) 1,99)

怎麼把工作表名稱顯示到一列中？

可以用Index函式 row函式提取：index可以根據位置提取資料，Row函式可以在向下複製時生成1，2，3，4...序數

=INDEX(shname,ROW(A1))

名稱有了，下一步是給工作表名稱新增連結，這一步要用

=Hyperlink（#工作表名稱!單元格地址,工作表名稱）

即：

=HYPERLINK("#"&INDEX(shname,ROW(A1))&"!a1",INDEX(shname,ROW(A1)))

當公式超出工作表個數時，再複製公式會返回錯誤值，所以需要再外套IFerror函式。

=IFERROR(HYPERLINK("#"&INDEX(shname,ROW(A1))&"!a1",INDEX(shname,ROW(A1))),"")

好像很完美了? No...當你修改、刪除工作表時，目錄並不會自動更新：

問題出在哪？原來我們在定義名稱時少了兩個函式：T和Now

所以T(Now())即可以讓公式強制重新整理（雙擊或其他單元格內容更新），又不影響單元格的值。

接下來修改定義的名稱：

至此，目錄的公式設定完成。無論新增、刪除工作表或修改工作表名稱，雙擊任一個單元格或任一單元格內容發生修改，目錄都會自動更新。

完成了嗎？No! 我們還少最後一步，把工作簿另存為啟用巨集的工作簿。

注：盤點了一下，製作目錄共用了兩大類9個函式，對新手真的有點難度，所以要想隨時隨地製作目錄，你還真的需要理解這些函式的用法。