利用Excel的宏功能实现多工作簿指定数据提取

禄小颖

摘 要 本文结合学生提交的多人健康档案表，提出了使用Excel提取多个工作簿中的指定信息到一个Excel文件的方法。阐述了数据的初始化和使用宏命令汇总多个工作簿中内容到一个Excel文件，介绍了使用INDIRECT函数提取指定单元格的方法。

关键词 Excel 宏编程 数据提取

中图分类号：G642文献标识码：A

1初始化

因工作需要，学校通过微信、QQ等在线方式发放了面向学生的健康档案表，要求学生本人填写并回传给班主任，以便于班主任老师详细掌握本班学生状况。如图1所示。

在健康档案表中，包含了学生本人电话、家庭住址、父母电话等多项联系方式。每个学生提交给老师一个以自己学号和姓名命名的文件。如果把全班学生的关键信息，汇总到一个表格中，则非常便于班主任老师工作的展开。

2思路

总体思路是，首先对学生提交的文件进行梳理。其次使用宏命令，将全班学生每人一个工作簿中的sheet1工作表汇总到一个新建工作簿中的不同工作表。最后，在新建工作簿中，依次提取每一个工作表中指定位置的数据到新建的“总揽”工作表。

3具体实现

3.1批处理重命名

学生的文件是以学号和姓名来命名的，由于姓名各不相同，不利于编程进行自动执行，所以先进行文件名的整理优化。

使用批命名软件，将学生的文件名改为“S+学号”。需要注意的是，由于系统的排序特点，最好将10号之内的学生学号改为两位数，即1号写为01号。如图2所示。

3.2使用宏命令汇总到一个工作簿

新建一个Excel文件，在其工作表标签上点击右键，选择“查看代码“。在打开的VBA编辑窗口中粘贴以下代码。关闭VBA编辑窗口。在Excel文件中，点击“工具”---“宏”---“学生汇总”，然后“执行”。在打开的对话窗口中，选择要的全部个工作薄。稍微等待一下，就合并汇总完成了。代码如图3所示。

3.3提取不同工作表中同一位置数据

学生信息中，所需提取的是学生姓名、学生本人手机号、父母手机号、家庭住址。最终效果如图4所示。

为达到最终效果，需要使用INDIRECT函数，该函数含义为引用并显示其内容。INDIRECT函数的语法格式是=INDIRECT（ref_text，[a1]）。INDIRECT函数的引用的两中形式，一种加引号，一种不加引号。比如，=INDIRECT（"A1"）——加引号，文本引用。=INDIRECT（A1）——不加引号，地址引用。

下面以“姓名”列为例介绍提取的方法。学生电话和其他列操作类似。由于在上一步汇总完毕的文件中，每一张工作表里面，b2单元格中的数据是学生姓名，所以，在最终的工作表中，“学生”列下面的第一个单元格，输入函数=INDIRECT（"s"&ROW（B1）&"！b2"）。

其含义是，取“s”（每一个工作表都是s开头，如s1、s2等），连接ROW（B1），再连接“b2”，结果就是：s1！b2，也就是获取列s1工作表的b2单元格的数据。

因为ROW（B1）=1，所以两个&之间连接的是数字1。用鼠标下拉时，双引号内的内容不会发生变化，而ROW（B1）下拉一行就变成了B2，而ROW（B2）=2。所以下拉后，就变成了s2！b2，也就是获取s2工作表的b2單元格的数据。

经过以上步骤，可以获得全体学生的姓名，再用类似方法，可以获取电话和住址信息。

4总结

经历了初始化，宏命令汇总和指定单元格提取，可以把多个文件中散乱的学生情况，挑选重要信息，集中显示在一个工作表中，从而提高了工作效率，也方便了班主任老师的查阅。

参考文献

[1] 钱力涛.使用Excel实现测评得分统计[J].科技风，2019（11）：69.

[2] 高楠.利用Excel的宏功能实现多数据类型单元格中纯数字的提取[J].计算机产品与流通，2017（09）：197.