查找更高效 玩轉工作表的批量操作

技術宅

首先要提取工作表名稱，可以使用Excel內置的GET.WORKBOOK（）函數快速進行提取。在Excel中打開包含多個工作表的文件，接著選中第一個工作表，右擊選擇“插入”，插入一個名為“目錄頁”的新工作表（圖1）。

點擊菜單欄的“公式→定義名稱”，新建一個名稱為“目錄”的新名稱，引用位置處輸入“=get. workbook（1）”，這是Excel內置的宏表公式，它可以自動讀取工作表的名稱，但是應用之前需要定義名稱（圖2）。

切換到“目錄頁”工作表，選中B1，輸入公式“=INDEX（目錄，ROW（））”（即引用上述宏表公式），將公式下拉（有幾個工作表則下拉幾行，如果增減工作表則重新填充），這樣在B列會自動填充類似“[工作簿名稱]+工作表名稱”的內容（圖3）。

選中B列填充的內容，右擊選擇“復制”，選中A1，右擊選擇“粘貼選項→值”，僅復制提取文字值。最后使用查找與替換工具，將其中的“[工作簿名稱]”替換為無內容，這樣在A列就可以獲取到所有工作表的名稱了（圖4）。

宏表公式提取的是“[工作簿名稱]+工作表名稱”內容，提取之后還要替換“[工作簿名稱]”和選擇性粘貼后才能完成操作。熟悉VBA腳本的用戶可以直接使用腳本一步到位進行提取。點擊菜單欄的“開發(fā)工具→查看代碼”，然后在打開的窗口中輸入下列的代碼（圖5）。

代碼如下：

Sub 提取工作表（）

For i = 1 To Sheets.Count

Cells（i， 1） = Sheets（i）.Name

Next

End Sub

代碼內容是使用For命令遍歷當前工作表，然后提取其名稱。完成代碼的錄入后按F5，這樣會自動生成一個名為“提取工作表”的宏。返回“目錄頁”工作表，定位到A1，點擊“宏→提取工作表→執(zhí)行”，運行這個宏后會將工作表名稱自動提取到A列（圖6）。

完成工作表名稱的提取后，接下來就可以使用H Y P E R L I N K函數為其添加鏈接。選中C1，插入公式“=HYPERLINK（"#"&A1&"！A1"，A1）”（即引用A1的名稱并在C1為其添加鏈接）。同上向下填充公式，這樣我們就在C列增加了各個工作表名稱的鏈接，點擊其中的鏈接即可跳轉到指定的工作表（圖7）。

如果工作表眾多，為了方便在任意工作表返回到目錄頁，可以按住Shift鍵依次選中除目錄頁的所有工作表，然后在任意一個工作表的A1單元格（如果原來工作表的A1單元格非空白，可以插入一列空白行）輸入“=HYPERLINK（"#目錄頁！A1"，"返回目錄"）”，這樣所有工作表的A1都會自動輸入“返回目錄”，點擊該鏈接即可快速返回到“目錄頁”工作表（圖8）。

將上述文件內容復制到Excel的A列，選中B1輸入公式=HYPERLINK（A1，A1）添加超鏈接，這樣在這個文件中點擊鏈接即可打開對應工作簿的文件（圖10）。