［摘要］ 本文介绍如何使用VBA开发通用型Excel数据抽取和汇总工具，从格式相同内容不同的多份Excel表单中抽取部分或者全部数据并自动汇总到Excel文件中，以方便后续统计分析工作。
［关键词］ Excel； VBA； 表单； 数据抽取； 汇总
１背景介绍
在日常工作中，经常会遇到从多份格式相同但内容不同的Ｅｘｃｅｌ表单中抽取部分或者全部数据到一个Ｅｘｃｅｌ文件中，以便进行统计分析工作，如调查表汇总统计、实验报告汇总统计和监测数据汇总统计等。如果表单份数和内容较少、格式简单、时间富裕、准确率要求不高，可以采用人工抽取数据并汇总的方式完成工作。但如果表单份数和内容非常多、格式复杂、时间紧迫、准确率要求高，采用人工抽取数据并汇总的方式将无法在短时间内准确完成工作。因此，如何准确、快速地从Ｅｘｃｅｌ表单中抽取数据并汇总是数据处理工作中的一个难题。
ＶＢＡ（Ｖｉｓｕａｌ Ｂａｓｉｃ Ｆｏｒ Ａｐｐｌｉｃａｔｉｏｎ）是Ｏｆｆｉｃｅ内嵌的应用开发工具，基于ＶＢＡ开发的应用程序可以实现一些有规律的、重复性的工作，以替代手工工作方式，提高工作效率和准确率。本文将介绍如何使用ＶＢＡ开发数据抽取与汇总工具，实现从格式相同、内容不同的多份Ｅｘｃｅｌ表单中自动抽取部分或全部数据并汇总到一个Ｅｘｃｅｌ文件中。
２设计思想
使用ＶＢＡ开发的Ｅｘｃｅｌ数据抽取与汇总工具的目标是将多份格式相同、内容不同的Ｅｘｃｅｌ表单中的部分或者全部数据自动抽取并汇总到一个Ｅｘｃｅｌ文件中，以方便后续的统计分析工作。该工具具有通用性和灵活性两个主要特点。通用性：该工具适用于所有格式相同内容不同的Ｅｘｃｅｌ表单，使用者通过前端展示页面配置数据汇总模板中的参数，使该工具适应新的数据抽取和汇总要求，而不需要改动程序。灵活性：该工具可以让使用者根据工作需要自由抽取表单中的数据项，可以部分抽取也可以全部抽取，灵活自由。为此，程序需要解决以下３方面问题：① 判断需要抽取数据的Ｅｘｃｅｌ目标文件。② 判断从每个Ｅｘｃｅｌ文件的Ｓｈｅｅｔ表单中需要抽取哪些数据。③ 设置怎样的循环才能将目标文件所有Ｓｈｅｅｔ表单中的数据抽取并汇总到一个Ｅｘｃｅｌ文件中。
为了解决上述问题，该工具使用Ｅｘｃｅｌ作为前端展示页面，通过可配置的程序参数，实现操作简便、自由灵活、可重复使用的数据抽取与汇总功能。其中，程序参数包括：
（１） 目标文件的存储路径：程序根据该参数查找目标文件，并将目标文件列表保存在前端展示页面的指定位置。
（２） 待抽取数据的存储位置：程序根据该参数在目标文件中定位，并从中抽取数据。
（３） 结果文件的保存路径和名称：程序根据该参数将结果文件存储在指定路径下，并以约定的方式为其命名。
３功能实现
基于ＶＢＡ的Ｅｘｃｅｌ 数据抽取与汇总工具主要由３个功能模块组成，即目标文件列表读取功能模块、数据抽取与汇总功能模块和生成结果文件功能模块。
３．１目标文件列表读取功能模块
目标文件列表读取功能是根据设定的路径将所有Ｅｘｃｅｌ目标文件读取出来，并将文件名显示在前端展示页面的指定位置，形成目标文件列表，以方便使用者检查核对。该功能模块的关键是设置正确的循环关系，以便将路径参数下所有目录中的Ｅｘｃｅｌ目标文件读取出来。该功能的核心代码如下：
Ｐｒｉｖａｔｅ Ｓｕｂ ＲｅａｄＦｉｌｅＬｉｓｔ＿Ｃｌｉｃｋ（）
Ｄｉｍ ｍＦｉｌｅＰａｔｈ Ａｓ Ｓｔｒｉｎｇ
ｍＦｉｌｅＰａｔｈ ＝ Ｒａｎｇｅ（＂用于存储路径位置＂）．Ｖａｌｕｅ
ＣｈＤｉｒ （ｍＦｉｌｅＰａｔｈ）
ＣｈＤｒｉｖｅ Ｌｅｆｔ（ｍＦｉｌｅＰａｔｈ， １）
Ｉｆ Ｒｉｇｈｔ（ｍＦｉｌｅＰａｔｈ， １） ＜＞ ＂＼＂ Ｔｈｅｎ
ｍＦｉｌｅＰａｔｈ ＝ ｍＦｉｌｅＰａｔｈ ＆ ＂＼*．ｘｌｓ＂
Ｅｌｓｅ
ｍＦｉｌｅＰａｔｈ ＝ ｍＦｉｌｅＰａｔｈ ＆ ＂*．ｘｌｓ＂
Ｅｎｄ Ｉｆ
Ｄｉｍ ｍＮｅｘｔＲｏｗ Ａｓ Ｉｎｔｅｇｅｒ
ｍＮｅｘｔＲｏｗ ＝ 目标文件列表所在的行数
Ｄｉｍ ｍＦｉｌｅＮａｍｅ Ａｓ Ｓｔｒｉｎｇ
ｍＦｉｌｅＮａｍｅ ＝ Ｄｉｒ（ｍＦｉｌｅＰａｔｈ， ｖｂＮｏｒｍａｌ）
Ｄｏ Ｗｈｉｌｅ ｍＦｉｌｅＮａｍｅ ＜＞ ＂＂
Ｒａｎｇｅ（＂目标文件列表所在列＂ ＆ ｍＮｅｘｔＲｏｗ）．Ｖａｌｕｅ ＝ ｍＦｉｌｅＮａｍｅ
ｍＦｉｌｅＮａｍｅ ＝ Ｄｉｒ
ｍＮｅｘｔＲｏｗ ＝ ｍＮｅｘｔＲｏｗ ＋ １
Ｌｏｏｐ
Ｅｎｄ Ｓｕｂ
３．２数据抽取与汇总功能模块
数据抽取功能模块是根据配置参数在Ｅｘｃｅｌ目标文件的指定位置抽取数据，并以行记录的形式将数据保存到结果文件中。该功能的关键有两个部分：① 首先建立两个数组，分别用于存储数据汇总模板中的数据项目名称和数据存储位置，然后通过循环将配置参数依次读取到数组中，并将数据项目名称保存到结果文件对应Ｓｈｅｅｔ表单中的第一行。② 设计３层嵌套循环，将所有Ｅｘｃｅｌ目标文件的所有Ｓｈｅｅｔ表单中的待抽取数据项依次抽取并汇总出来。第１层是目标文件列表循环，逐项打开目标文件列表中的文件；第２层是文件内的Ｓｈｅｅｔ表单循环，逐项打开目标文件中的每个Ｓｈｅｅｔ表单；第３层是待抽取的数据循环，根据配置参数逐项读取目标数据，并将其保存到结果文件对应的Ｓｈｅｅｔ表单中。该功能的核心代码如下：
（１） 建立两个数组，保存数据汇总模板中的数据项目名称和数据存储位置。
Ｄｏ Ｗｈｉｌｅ ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ ＜＝ （ｍＣｏｌｕｍｎＮｕｍｂｅｒ － ２）
ｍＩｎｆｏ（ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ － １） ＝ Ｃｅｌｌｓ（ＲｏｗＩｎｄｅｘ：＝ｍＲｏｗＮｕｍｂｅｒ， ｃｏｌｕｍｎｉｎｄｅｘ：＝ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ ＋ ２）．Ｖａｌｕｅ
ｍＳｏｕｒｃｅＩｎｆｏ（ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ － １） ＝ Ｃｅｌｌｓ（ＲｏｗＩｎｄｅｘ：＝ｍＲｏｗＮｕｍｂｅｒ ＋ １， ｃｏｌｕｍｎｉｎｄｅｘ：＝ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ ＋ ２）．Ｖａｌｕｅ
Ｗｏｒｋｂｏｏｋｓ（ｍＳａｖｅＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ（ｍＴｉｍｅ）． Ｃｅｌｌｓ（ＲｏｗＩｎｄｅｘ：＝１， ｃｏｌｕｍｎｉｎｄｅｘ：＝ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ） ＝ ｍＩｎｆｏ（ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ － １）
ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ ＝ ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ ＋ １
Ｌｏｏｐ
（２） 建立３层嵌套循环，抽取目标数据并保存。
Ｄｏ Ｗｈｉｌｅ ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１ ＜＞ ＂＂
Ｗｏｒｋｂｏｏｋｓ．Ｏｐｅｎ ＦｉｌｅＮａｍｅ：＝ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１
ｍＳｈｅｅｔＮｕｍｂｅｒ ＝ Ｗｏｒｋｂｏｏｋｓ（ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１）．Ｗｏｒｋｓｈｅｅｔｓ．Ｃｏｕｎｔ
Ｄｏ Ｗｈｉｌｅ ｍＳｈｅｅｔＴｅｍｐＮｕｍｂｅｒ ＜＝ ｍＳｈｅｅｔＮｕｍｂｅｒ
ｍＩｎｆｏＮｕｍｂｅｒ ＝ ０
Ｄｏ Ｗｈｉｌｅ ｍＩｎｆｏＮｕｍｂｅｒ ＜ ｍＣｏｌｕｍｎＮｕｍｂｅｒ － ２
ｍＩｎｆｏ（ｍＩｎｆｏＮｕｍｂｅｒ） ＝ Ｗｏｒｋｂｏｏｋｓ（ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１）．Ｗｏｒｋｓｈｅｅｔｓ（ｍＳｈｅｅｔＴｅｍｐＮｕｍｂｅｒ）． Ｒａｎｇｅ（ｍＳｏｕｒｃｅＩｎｆｏ（ｍＩｎｆｏＮｕｍｂｅｒ））
Ｗｏｒｋｂｏｏｋｓ（ｍＳａｖｅＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ（ｍＴｉｍｅ）．Ｃｅｌｌｓ（ＲｏｗＩｎｄｅｘ：＝ｍＳａｖｅＲｏｗＮｕｍｂｅｒ， ｃｏｌｕｍｎｉｎｄｅｘ：＝ｍＩｎｆｏＮｕｍｂｅｒ ＋ １） ＝ ｍＩｎｆｏ（ｍＩｎｆｏＮｕｍｂｅｒ）
ｍＩｎｆｏＮｕｍｂｅｒ ＝ ｍＩｎｆｏＮｕｍｂｅｒ ＋ １
Ｌｏｏｐ
ｍＩｎｆｏ（ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ） ＝ Ｗｏｒｋｂｏｏｋｓ（ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１）．Ｗｏｒｋｓｈｅｅｔｓ（ｍＳｈｅｅｔＴｅｍｐＮｕｍｂｅｒ）．Ｎａｍｅ
ｍＩｎｆｏ（ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ － １） ＝ Ｗｏｒｋｂｏｏｋｓ（ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１）．Ｎａｍｅ
ｍＳａｖｅＲｏｗＮｕｍｂｅｒ ＝ ｍＳａｖｅＲｏｗＮｕｍｂｅｒ ＋ １
ｍＳｈｅｅｔＴｅｍｐＮｕｍｂｅｒ ＝ ｍＳｈｅｅｔＴｅｍｐＮｕｍｂｅｒ ＋ １
Ｌｏｏｐ
Ｗｏｒｋｂｏｏｋｓ（ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１）．Ｃｌｏｓｅ
ｍＲｏｗＮｕｍｂｅｒ ＝ ｍＲｏｗＮｕｍｂｅｒ ＋ １
ｍＳｈｅｅｔＴｅｍｐＮｕｍｂｅｒ ＝ １
ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１ ＝ Ｗｏｒｋｂｏｏｋｓ（ｍＭａｉｎＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ（ｍＭａｉｎＳｈｅｅｔＮａｍｅ）．Ｃｅｌｌｓ（ＲｏｗＩｎｄｅｘ：＝ｍＲｏｗＮｕｍｂｅｒ， ｃｏｌｕｍｎｉｎｄｅｘ：＝ｍＳｏｕｒｃｅＬｉｎｋＣｏｌｕｍｎＮｕｍｂｅｒ）．Ｖａｌｕｅ
Ｌｏｏｐ
３．３生成结果文件功能模块
生成结果文件功能模块在指定的路径下生成保存数据抽取与汇总结果的Ｅｘｃｅｌ文件或Ｓｈｅｅｔ表单，并按照约定的方式为其命名。该功能的关键是判断指定路径下是否存在以当前日期命名的Ｅｘｃｅｌ文件，如果不存在，则新建一个Ｅｘｃｅｌ文件；如果存在则打开该文件，同时增加一个Ｓｈｅｅｔ表单存储结果。为了在不修改程序的情况下可以同时存储多次抽取数据的文件而不相互干扰，程序规定以当前日期（ｙｙ－ｍｍ－ｄｄ）作为Ｅｘｃｅｌ文件的名称，以程序运行的时间（ｈｈ－ｍｍ－ｓｓ）作为存储结果的Ｓｈｅｅｔ表单名称。该功能的核心代码如下：
Ｉｆ Ｄｉｒ（ｍＦｉｌｅＰａｔｈ ＆ ｍＳａｖｅＦｉｌｅＮａｍｅ） ＜＞ ＂＂ Ｔｈｅｎ
Ｗｏｒｋｂｏｏｋｓ．Ｏｐｅｎ ＦｉｌｅＮａｍｅ：＝ｍＳａｖｅＦｉｌｅＮａｍｅ
Ｗｏｒｋｂｏｏｋｓ（ｍＳａｖｅＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ．Ａｄｄ
Ｗｏｒｋｂｏｏｋｓ（ｍＳａｖｅＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ（１）．Ｎａｍｅ ＝ ｍＴｉｍｅ
Ｅｌｓｅ
ｍＴｅｍｐ ＝ Ｗｏｒｋｂｏｏｋｓ．Ｃｏｕｎｔ
Ｗｏｒｋｂｏｏｋｓ．Ａｄｄ
Ｗｏｒｋｂｏｏｋｓ（ｍＴｅｍｐ ＋ １）．ＳａｖｅＡｓ ＦｉｌｅＮａｍｅ：＝ｍＳａｖｅＦｉｌｅＮａｍｅ， ＣｏｎｆｌｉｃｔＲｅｓｏｌｕｔｉｏｎ：＝ｘｌＬｏｃａｌＳｅｓｓｉｏｎＣｈａｎｇｅｓ
Ｗｏｒｋｂｏｏｋｓ（ｍＳａｖｅＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ（１）．Ｎａｍｅ ＝ ｍＴｉｍｅ
Ｗｏｒｋｂｏｏｋｓ（ｍＳａｖｅＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ（２）．Ｄｅｌｅｔｅ
Ｗｏｒｋｂｏｏｋｓ（ｍＳａｖｅＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ（３）．Ｄｅｌｅｔｅ
Ｅｎｄ Ｉｆ
４运行效果
下面以工作中的应用案例阐述使用数据抽取与汇总工具的主要步骤（见图１）和运行效果（见图２）。
（１） 配置数据汇总模板：使用者根据Ｅｘｃｅｌ表单内容，自由配置需要抽取的数据项名称和数据存储位置。其中数据项名称用于显示汇总表的表头信息，数据存储位置为表单中数据项的实际存储位置。
（２） 配置目标文件存储路径：正确填写Ｅｘｃｅｌ目标文件的存储路径。
（３） 读取目标文件列表：该工具根据目标文件存储路径，将该路径下的所有Ｅｘｃｅｌ文件读取出来，并以列表的形式展示在页面上。
（４） 数据抽取与汇总：上述步骤完成后，该工具根据参数将目标文件中的数据抽取出来并保存在结果文件中。
５结束语
在实际应用中，１ １６６个Ｅｘｃｅｌ文件，２ ３３２个表单，１０２ ６０８个数据项，使用该工具在内存为２．０ＧＨｚ，硬盘转速为５ ４００转／秒的计算机上运行３分３０秒左右，结果准确无误。
使用ＶＢＡ开发的Ｅｘｃｅｌ数据抽取与汇总工具不仅具有投入成本低、简单易用、小巧灵活、可移植性好等特点，更重要的是通用性强。通过配置数据汇总模板，可适用于所有格式相同、内容不同的多份Ｅｘｃｅｌ表单的数据抽取与汇总工作，提高了工作效率和准确率。
主要参考文献
［１］ Ｅｘｃｅｌ Ｈｏｍｅ． Ｅｘｃｅｌ应用大全［Ｍ］． 北京：人民邮电出版社，２００８．
［２］ Ｅｘｃｅｌ Ｈｏｍｅ． ＥＸＣＥＬ数据处理与分析实战技巧精粹［Ｍ］． 北京：人民邮电出版社，２００８．
［３］ 黄海． Ｅｘｃｅｌ ＶＢＡ语法与应用词典［Ｍ］． 北京：中国青年出版社，中国青年电子出版社，２００９．
［４］ 姚文涛． Ｅｘｃｅｌ ＶＢＡ应用开发经典案例［Ｍ］． 北京：清华大学出版社， ２００９．
注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文下载本文
