使用VBA实现Excel表单数据的自动抽取与汇总.docVIP

使用VBA实现Excel表单数据的自动抽取与汇总 2011年7月 第14卷第l4期 中国管理信息化 ChinaManagementInformationization Ju1..201l V0I.14.No.14 使用VBA实现Excel表单数据的自动抽取与汇总 刘悦 (中国疾病预防控制中心环境与健康相关产品安全所,北京100021) [摘要]本文介绍如何使用VBA开发通用型Excel数据抽取和汇总工具.从格式相同内容不同的多份Excel表单中抽取 部分或者全部数据并自动汇总到Excel文件中,以方便后续统计分析工作. [关键词]Excel;VBA;表单;数据抽取;汇总 doi:10.3969/j.issn.1673—0194.2011.14.046 [中图分类号]TP317.1[文献标识码]A[文章编号]1673—0194(2011)14-0078—03 1背景介绍 在日常工作中.经常会遇到从多份格式相同但内容不同的 Excel表单中抽取部分或者全部数据到一个Excel文件中.以便 进行统计分析工作.如调查表汇总统计,实验报告汇总统计和监 测数据汇总统计等.如果表单份数和内容较少,格式简单,时间 富裕,准确率要求不高.可以采用人工抽取数据并汇总的方式完 成工作.但如果表单份数和内容非常多,格式复杂,时间紧迫,准 确率要求高.采用人工抽取数据并汇总的方式将无法在短时间 内准确完成工作.因此.如何准确,快速地从Excel表单中抽取数 据并汇总是数据处理工作中的一个难题 VBA(VisualBasicForApplication)是Office内嵌的应用开发 工具.基于VBA开发的应用程序可以实现一些有规律的,重复性 的工作,以替代手工工作方式,提高工作效率和准确率.本文将 介绍如何使用VBA开发数据抽取与汇总工具.实现从格式相同, 内容不同的多份Excel表单中自动抽取部分或全部数据并汇总 到一个Excel文件中 2设计思想 使用VBA开发的Excel数据抽取与汇总工具的目标是将多 份格式相同,内容不同的Excel表单中的部分或者全部数据自动 抽取并汇总到一个Excel文件中.以方便后续的统计分析工作. 该工具具有通用性和灵活性两个主要特点通用性:该工具适用 于所有格式相同内容不同的Excel表单.使用者通过前端展示页 面配置数据汇总模板中的参数.使该工具适应新的数据抽取和 汇总要求,而不需要改动程序.灵活性:该工具可以让使用者根 据工作需要自由抽取表单中的数据项.可以部分抽取也可以全 部抽取.灵活自由.为此.程序需要解决以下3方面问题:①判断 需要抽取数据的Excel目标文件.②判断从每个Excel文件的 [收稿日期]2011-03—23 Sheet表单中需要抽取哪些数据.③设置怎样的循环才能将目标 文件所有Sheet表单中的数据抽取并汇总到一个Excel文件中 为了解决上述问题.该工具使用Excel作为前端展示贞面, 通过可配置的程序参数,实现操作简便,自由灵活,可重复使用的 数据抽取与汇总功能.其中.程序参数包括: (1)目标文件的存储路径:程序根据该参数查找目标文件. 并将目标文件列表保存在前端展示页面的指定位置 (2)待抽取数据的存储位置:程序根据该参数在目标文件中 定位.并从中抽取数据 (3)结果文件的保存路径和名称:程序根据该参数将结果文 件存储在指定路径下.并以约定的方式为其命名. 3功能实现 基于VBA的Excel数据抽取与汇总工具主要由3个功能模 块组成,即目标文件列表读取功能模块,数据抽取与汇总功能模 块和生成结果文件功能模块 3.1目标文件列表读取功能模块 目标文件列表读取功能是根据设定的路径将所有Excel目 标文件读取出来.并将文件名显示在前端展示页面的指定位置. 形成目标文件列表.以方便使用者检查核对该功能模块的关键 是设置正确的循环关系.以便将路径参数下所有目录中的Excel 目标文件读取出来.该功能的核心代码如下: PrivateSubReadFileListClick() DimmFilePathAsString mFilePath=Range(“gl于存储路径位置n).Value ChDir(mFilePath) ChDriveLefl(mFilePath,1) IfRight(mFilePath,1)lt;gt;”\1.Then mFilePath=mFilePathamp;”\丰.xls” W.),(,),…,(Tt,tlJ)}(1’tlt;m).利用软聚类算法对文档集 D进行话题识别,形成层次话题集TP={tp,,:,…,帆}.在聚类 过程中,动态形成话题重心点向量集C={c,,C,…,C}.根据话 题tp中的所有文档向量集tpl:{dl,,…,},对XML文档集 中话题节点的标注信息