很多工作簿都包含多個工作表,比如公司的日報彙總文件裡包含多個以日期為名的工作表,這樣在查看不同日期的數據時就要來回地切換,多有不便。其實我們可以在工作表中建立一個新目錄并将其鍊接到其他工作表,這樣隻要點擊對應的鍊接,即可快速跳轉到指定的工作表。
○利用“檢查兼容性”功能生成目錄
在Excel 2003中最大的列數是256(即IV列,2的8次方),而在2007及以後的版本中最大的列數是16384(即XFD列,2的14次方),所以我們隻要在自己所使用版本的任意單元格中輸入超過列數限制的公式,在保存時就會産生兼容性問題,而在檢查出兼容性問題後Excel就會在當前工作簿的工作表裡建立所有工作表的鍊接,把該鍊接作為目錄即可。
這裡以Excel 2019操作為例。打開需要建立目錄的工作簿,用鼠标右鍵點擊某個工作表(如10月31日)并選擇“選定全部工作表”,接着在A2單元格中輸入公式“=XFD1”(XFD1大于XFD列),這樣在每個工作表的A2單元格中都會自動添加該公式(圖1)。
繼續切換到“10月31日”工作表,然後點擊“文件→信息→檢查工作簿→檢查問題→檢查兼容性”,在彈出的窗口中會提示遇到兼容性問題,點擊“複制到新表”,并将新表的名稱改為“目錄”。接着在“目錄”工作表中複制E9:E39區域的内容到H1:H31區域(圖2),然後按下“Ctrl+F”組合鍵啟動“查找和替換”功能,将H列單元格中的“'!A2”替換為空,并将其他的無關内容删除,之後将其複制到當前工作表的B列備用。繼續右擊“目錄”工作表并選擇“選定全部工作表”,接着在A1單元格中輸入公式“=HYPERLINK("#目錄!A1","返回")”,同上也會在每個工作表的A1單元格中自動添加該公式(圖3)。
完成上述操作後,隻要在“目錄”工作表中點擊B列裡的文件名稱,即可跳轉到對應的工作表(圖4)。同時點擊任意打開工作表A1單元格中的“返回”,又會重新跳轉回“目錄”工作表。
○批量生成目錄
上述方法操作簡單,但是步驟比較多,而通過函數則能快速生成目錄,更适合有規律、有計劃的多工作表文件。打開工作簿後新建一個名為“目錄”的工作表,點擊“公式→定義名稱”,在彈出的對話框中,“名稱”處輸入“提取表名”,“引用位置”處輸入“=GET.WORKBOOK(1)”(表示使用宏表數依次提取工作表的名稱),然後點擊“确定”(圖5)。
在A2單元格中輸入公式“=INDEX(提取表名,ROW(A1))”(表示先使用ROW函數提取行号,然後将其作為INDEX函數引用的行号,引用區域為宏表函數提取的表名),然後下拉填充公式,即可提取所有的表名到A列。繼續在B2單元格中輸入“10月1日”,接着按下“Ctrl+E”組合鍵提取不帶工作簿名稱的表名,在C2單元格中輸入公式“=HYPERLINK("#"&A2&"!C2",B2)”,下拉公式後就可以生成所有工作表的鍊接。同上,在其他日期的工作表中再添加一個返回“目錄”工作表的鍊接即可(圖6)。
公式解釋:
在HYPERLINK函數中,"#"&A2&"!C2"表示鍊接地址,在A2單元格中顯示的是“[目錄.xlsm]10月1日”,通過和“!C2”連接後鍊接到“10月1日”這個工作表,而鍊接顯示的名稱則是B2單元格中的内容。
○建立自動更新目錄
如果經常增加或者删除工作表,那麼利用VBA腳本生成自動更新目錄是最方便的。
之後它就會新建一個名為“目錄”的新工作表,表中的内容就是生成的目錄,并且在每個工作表的K1單元格中會自動添加一個“返回目錄”的跳轉鍊接。這樣以後當我們在工作簿中增加或者删除工作表後,隻要重新運行上述代碼即可自動同步目錄内容(圖8)。
有話要說...