有時,一個工作薄裡會有很多工作表,翻看工作表很麻煩。為了方便查找工作表,需要給若干個工作表建立目錄。
如下:
方法實現
鼠標放在A1單元格,【公式】——【定義名稱】,在新建名稱對話框中輸入名稱“目錄”,引用位置輸入公式:
=INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW())。
如下圖:
在A1單元格輸入公式:
=IFERROR(HYPERLINK(目錄&"!A1",MID(目錄,FIND("]",目錄)+1,99)),"")
公式向下填充,即得所有工作表的目錄。
如下圖:
在目錄工作表以外的表中合適位置,輸入公式:
=HYPERLINK("#目錄!A1","返回目錄"),即可得到返回“目錄”的鏈接。
公式解析
名稱公式=INDEX(GET.WORKBOOK(1),ROW(目錄!A1))&T(NOW()):
- GET.WORKBOOK(1)用於提取當前工作簿中所有工作表名稱。
- INDEX函數則按ROW(A1)返回的數字決定要顯示第幾張工作表的名稱。
- 宏表函數GET.WORKBOOK(1)在數據變動時不會自動重算,而NOW()是易失性函數,因此在公式中加上NOW()函數讓公式自動重算。
- 函數T()則是將NOW()產生的數值轉為空文本,也就是相當於在工作表名稱後加上 &""。
目錄公式=IFERROR(HYPERLINK(目錄&"!A1",MID(目錄,FIND("]",目錄)+1,99)),""):
- FIND("]",目錄):用於查找符號"]"在自定義名稱“目錄”計算結果中的位置。
- MID(目錄,FIND("]",目錄)+1,99):從“目錄”中的"]"符號後一個字符處取值,取值長度為比較大的字符,這裡設置99,也可自行設置其他長度。
- HYPERLINK函數:是EXCEL超級鏈接的函數實現方法。當單擊函數HYPERLINK 所在的單元格時,Excel將打開鏈接的文件或跳轉到指定的工作表的單元格。
- IFERROR函數:用於屏蔽錯誤。
返回目錄=HYPERLINK("#目錄!A1","返回目錄"):
HYPERLINK(link_location,[friendly_name])
參數:
- Link_location 必需。可以作為文本打開的文檔的路徑和文件名。
- Friendly_name 可選。單元格中顯示的跳轉文本或數字值。Friendly_name 顯示為藍色並帶有下劃線。如果省略 Friendly_name,單元格會將 link_location顯示為跳轉文本。Friendly_name 可以為數值、文本字符串、名稱或包含跳轉文本或數值的單元格。
特別注意:
因為引用了宏表函數,所以文件保存時要保存成“啟用宏的工作簿.xlsm”。
閱讀更多 韓老師講office 的文章