怎樣快速找到Excel工作表？利用Excel宏表函數GET.WORKBOOK自動創建工作表管理目錄案...

撥絲留其產 2016-04-19

展開全文

?學好excel，提高職業素養，提升職場競爭力，讓老板喊著為你漲工資！

當前瀏覽器不支持播放音樂或語音，請在微信或其他瀏覽器中播放友情歲月許志安 - 中國之星第3期

宏表函數GET.WORKBOOK自動創建工作表管理目錄案例教程

當一個EXCEl里包含很多張工作表時，很多小伙伴都會制作一個工作表目錄，并創建超鏈接實現快速跳轉到指定的工作表，小編excel小課堂(ID：excel-xiaoketang 長按復制)在這里有一個問題要問，當新增或刪除工作表時，你的工作表目錄是自動更新，還是手動更新呢？

案例數據表格如圖中所示，每個產品工作表里存放了該產品的銷售情況明細，想要創建一個產品目錄，單機產品名稱時能自動跳轉到相應的工作表，最好能實現當產品工作表時，目錄能自動更新。

StepA首先，我們來創建一個基礎的超鏈接目錄吧！在A列手動錄入產品名稱，在B2輸入公式“=HYPERLINK('#'&A2&'!A1',A2)”，算是HYPERLINK超鏈接函數的一個基礎用法，'#'&A2&'!A1'表示鏈接的地址為本工作薄中花架工作表的A1單元格，而A2則表示鏈接名稱為單元格A2的內容，今天我們沒有采用“插入超鏈接”功能來操作，因為批量操作實在是有點辛苦。

StepB接下來的問題就是怎樣做到完美，也就是在增減產品工作表時，自動更新產品目錄。第一個解決的問題就是如何獲取工作表名稱，介紹我們今天的主角——GET.WORKBOOK，獲取當前工作薄的所有工作表名稱，并將結果存儲在數組中，注意的是，該函數為宏表函數，是無法在Excel中直接使用的，必須通過定義名稱的方式使用。工具欄-公式-定義名稱，在彈出的對話框中，名稱文本區輸入“functionillustration”，引用位置輸入“=GET.WORKBOOK(1)”，在任一單元格輸入公式“=functionillustration”，按F9，可以看到公式執行結果為一維數組，聲明一下，這個例子只是用來演示GET.WORKBOOK功能的，跟實現目標沒啥關系。

StepC再次定義名稱，在彈出的對話框中，名稱文本區輸入“productname”，引用位置輸入“=MID(GET.WORKBOOK(1),FIND(']',GET.WORKBOOK(1))+1,99)”，該公式的結果是返回一個由工作表名稱組成的一維數組，公式原理為利用find函數查找']'在字符串中的位置，再利用MID截取從']'符號往后的字符串內容。

StepD在A2輸入公式”=HYPERLINK('#'&INDEX(productname,ROW(A2))&'!A1',INDEX(productname,ROW(A2)))“，HYPERLINK超鏈接函數的使用方法同可參考第一步說明，INDEX(productname,ROW(A2))則是從工作表名稱數組中依次提取數組數值，由于第一個工作表為產品目錄工作表，第二個工作表才是產品表，所以我們使用ROW(A2)，也就是從數組的第二個數值開始。此時如果工作表有增減，只需刷新公式就可以了。

StepE雖然完成了目錄的自動更新，可還有一個小瑕疵，就是上圖中的無效的單元格引用錯誤”#REF!“，原因就是INDEX返回值超出了工作表名稱數組上限，我們使用IFERROR公式來修改錯誤提示。修改A2公式為”=IFERROR(HYPERLINK('#'&INDEX(productname,ROW(A2))&'!A1',INDEX(productname,ROW(A2))),'')“，當出現錯誤值時，顯示為空。