瀏覽更方便 自己制作Excel多工作表目錄

平淡

○利用“檢查兼容性”功能生成目錄

在Excel 2003中最大的列數(shù)是256（即IV列，2的8次方），而在2007及以后的版本中最大的列數(shù)是16384（即XFD列，2的14次方），所以我們只要在自己所使用版本的任意單元格中輸入超過列數(shù)限制的公式，在保存時(shí)就會(huì)產(chǎn)生兼容性問題，而在檢查出兼容性問題后Excel就會(huì)在當(dāng)前工作簿的工作表里建立所有工作表的鏈接，把該鏈接作為目錄即可。

這里以Excel 2019操作為例。打開需要建立目錄的工作簿，用鼠標(biāo)右鍵點(diǎn)擊某個(gè)工作表（如10月31日）并選擇“選定全部工作表”，接著在A 2單元格中輸入公式“=XFD1”（XFD1大于XFD列），這樣在每個(gè)工作表的A2單元格中都會(huì)自動(dòng)添加該公式（圖1）。

繼續(xù)切換到“10月31日”工作表，然后點(diǎn)擊“文件→信息→檢查工作簿→檢查問題→檢查兼容性”，在彈出的窗口中會(huì)提示遇到兼容性問題，點(diǎn)擊“復(fù)制到新表”，并將新表的名稱改為“目錄”。接著在“目錄”工作表中復(fù)制E9：E39區(qū)域的內(nèi)容到H1：H31區(qū)域（圖2），然后按下“Ctrl+F”組合鍵啟動(dòng)“查找和替換”功能，將H列單元格中的“'！A 2”替換為空，并將其他的無關(guān)內(nèi)容刪除，之后將其復(fù)制到當(dāng)前工作表的B列備用。繼續(xù)右擊“目錄”工作表并選擇“選定全部工作表”，接著在A1單元格中輸入公式“=HYPERLINK（"#目錄！A1"，"返回"）”，同上也會(huì)在每個(gè)工作表的A1單元格中自動(dòng)添加該公式（圖3）。

完成上述操作后，只要在“目錄”工作表中點(diǎn)擊B 列里的文件名稱，即可跳轉(zhuǎn)到對(duì)應(yīng)的工作表（圖4）。同時(shí)點(diǎn)擊任意打開工作表A1單元格中的“返回”，又會(huì)重新跳轉(zhuǎn)回“目錄”工作表。

○批量生成目錄

上述方法操作簡(jiǎn)單，但是步驟比較多，而通過函數(shù)則能快速生成目錄，更適合有規(guī)律、有計(jì)劃的多工作表文件。打開工作簿后新建一個(gè)名為“目錄”的工作表，點(diǎn)擊“公式→定義名稱”，在彈出的對(duì)話框中，“名稱”處輸入“ 提取表名”，“引用位置”處輸入“=GET.WORKBOOK（1）”（表示使用宏表數(shù)依次提取工作表的名稱），然后點(diǎn)擊“確定”（圖5）。

在A2單元格中輸入公式“=INDEX（提取表名，ROW（A1））”（表示先使用ROW函數(shù)提取行號(hào)，然后將其作為INDEX函數(shù)引用的行號(hào)，引用區(qū)域?yàn)楹瓯砗瘮?shù)提取的表名），然后下拉填充公式，即可提取所有的表名到A列。繼續(xù)在B2單元格中輸入“10月1日”，接著按下“Ctrl +E”組合鍵提取不帶工作簿名稱的表名，在C2單元格中輸入公式“=HYPERLINK（"#"&A2&"！C2"，B2）”，下拉公式后就可以生成所有工作表的鏈接。同上，在其他日期的工作表中再添加一個(gè)返回“目錄”工作表的鏈接即可（圖6）。

公式解釋：在HYPERLINK函數(shù)中，"#"&A2&"！C2"表示鏈接地址，在A2單元格中顯示的是“[目錄.xlsm]10月1日”，通過和“！C2”連接后鏈接到“10月1日”這個(gè)工作表，而鏈接顯示的名稱則是B2單元格中的內(nèi)容。

○建立自動(dòng)更新目錄

如果經(jīng)常增加或者刪除工作表，那么利用VBA腳本生成自動(dòng)更新目錄是最方便的。我們需要先訪問“http：//dwz.date/cgzc”鏈接（提取碼：c79t）下載所需的代碼文件，接著在Excel中按“Alt+F11”快捷鍵打開代碼編輯窗口，點(diǎn)擊左側(cè)窗格的“ThisWorkbook”，將下載到的代碼粘貼到右側(cè)的窗格中，然后按下F5鍵運(yùn)行（圖7）。

之后它就會(huì)新建一個(gè)名為“目錄”的新工作表，表中的內(nèi)容就是生成的目錄，并且在每個(gè)工作表的K1單元格中會(huì)自動(dòng)添加一個(gè)“返回目錄”的跳轉(zhuǎn)鏈接。這樣以后當(dāng)我們?cè)诠ぷ鞑局性黾踊蛘邉h除工作表后，只要重新運(yùn)行上述代碼即可自動(dòng)同步目錄內(nèi)容（圖8）。