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

2017-07-31技术宅

电脑爱好者 2017年13期

关键词：名称代码公式

技术宅

首先要提取工作表名称，可以使用Excel内置的GET.WORKBOOK（）函数快速进行提取。在Excel中打开包含多个工作表的文件，接着选中第一个工作表，右击选择“插入”，插入一个名为“目录页”的新工作表（图1）。

点击菜单栏的“公式→定义名称”，新建一个名称为“目录”的新名称，引用位置处输入“=get. workbook（1）”，这是Excel内置的宏表公式，它可以自动读取工作表的名称，但是应用之前需要定义名称（图2）。

切换到“目录页”工作表，选中B1，输入公式“=INDEX（目录，ROW（））”（即引用上述宏表公式），将公式下拉（有几个工作表则下拉几行，如果增减工作表则重新填充），这样在B列会自动填充类似“[工作簿名称]+工作表名称”的内容（图3）。

选中B列填充的内容，右击选择“复制”，选中A1，右击选择“粘贴选项→值”，仅复制提取文字值。最后使用查找与替换工具，将其中的“[工作簿名称]”替换为无内容，这样在A列就可以获取到所有工作表的名称了（图4）。

宏表公式提取的是“[工作簿名称]+工作表名称”内容，提取之后还要替换“[工作簿名称]”和选择性粘贴后才能完成操作。熟悉VBA脚本的用户可以直接使用脚本一步到位进行提取。点击菜单栏的“开发工具→查看代码”，然后在打开的窗口中输入下列的代码（图5）。

代码如下：

Sub 提取工作表（）

For i = 1 To Sheets.Count

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

End Sub

代码内容是使用For命令遍历当前工作表，然后提取其名称。完成代码的录入后按F5，这样会自动生成一个名为“提取工作表”的宏。返回“目录页”工作表，定位到A1，点击“宏→提取工作表→执行”，运行这个宏后会将工作表名称自动提取到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）。