基于Excel宏语言的数据自动汇总研究.docxVIP

基于Excel宏语言的数据自动汇总研究摘要：利用Excel进行数据抽取汇总时，常需要把位于不同文件夹里大量的具有相同表结构的Excel文件中汇总数据，合并到新的文件中，以便于对数据进行进一步的分析。通过Excel VBA编程，可以自动将指定的数据汇总合并到新的Excel文件中，提高工作效率。关键词：Excel VBA；相同表结构；多工作表；数据汇总正文：背景介绍在院校的教育教学管理工作中，经常遇到数据汇总的工作，将分散数据合并到新的文件中，以便进行数据分析工作。如果采集表数量较少、结构简单，一般是通过手工打开数据表，采用复制、粘贴的方式将数据汇总到一起。这样的简单而繁复的操作，不仅工作效率低，而且容易出错还难以查觉，导致数据汇总的准确率低。因此，准确的从大量相同结构的的复杂工作表中汇总数据是数据处理工作的一个关键环节。Microsoft Excel是微软公司的办公软件Microsoft office的组件之一，它可以进行各种数据的处理、统计分析，广泛地应用于众多领域。VBA（Visual Basic for Applications）是Visual Basic的一种宏语言，主要能用来扩展Windows的应用程序功能，特别是Microsoft Office软件。Excel VBA能够用Excel的宏语言来使Excel自动化，可以规范使用者的操作，控制操作行为，可以制作人性化的操作界面，可以将多个步骤的繁复手工操作通过执行VBA代码迅速、准确的实现。本文将介绍利用Excel VBA开发数据汇总程序，实现从位于不同文件夹内的Excel文件中抽取分散的数据，存放于新生成的Excel文件中。案例应用条件分析本案例中，需将位于不同文件夹内的多份格式相同、内容不同的Excel工作表的分散的数据值自动汇总到新的Excel文件中，供数据分析使用，面临的主要问题有3 个：（1）大量相同格式的Excel文件是存放在三个文件夹中，\北京\2013\数据\、\河北\上报\2013\、\天津\2013\，文件夹名称和目录深度各不相同。（2）每个Excel文件中有2个工作表做为源数据，工作表中的数据格式不统一，数据表格式如图1、2所示。 图1 源数据工作表1的数据格式图2 源数据工作表2的数据格式（3）汇总后的数据需要按照图3所示的模板格式，存到新生成的文件中。图3 汇总工作表格式因此程序主要解决的问题是：如何处理复杂目录？如何设置循环才能遍历复杂文件夹内的Excel文件，并读取指定的数据值？。程序设计利用Excel VBA开发数据抽取汇总程序时，灵活运用VBA内置对象进行数据的访问，按不同文件夹生成不同的文件，将复杂问题简单化，既提高运行效率，又具有一定的适应性。程序分为三个部分，具体设计如下：初始应用程序运行环境，利用变量存储路径，可以方便将每个目录下所抽取的数据汇总到单个Excel文件中：大量的数据存储在不同的文件夹中，这些文件所处的文件夹的名称和路径深度是无规则的，这里以相对路径的方式将不同文件夹的路径赋值给变量，便于程序循环调用。定义目录变量，存放无规律的目录结构Dim m1 As String Dim m2 As StringDim m3 As String初始应用程序环境，提高运行效率Application.DisplayAlerts = False Application.ScreenUpdating = FalseApplication.DisplayAlerts = FalseRowStart = 4 设置在当前文件写入数据的起始行号给目录变量赋值，需根据实际目录结构填写m1 = \北京\2013\数据\ m2 = \河北\上报\2013\m3 = \天津\2013\利用双层的DO…Loop循环遍历所有的目录和目录中的文件：外层循环是一次访问一个目录，访问的次数为目录变量的数量。内层循环是访问目录路径中的Excel文件，从这些文件中读取数据，并读取数据到模板文件中。i1 = 1Do 从各个目录读取数据，开始外层循环，并遍历文件夹内的所有文件UserForm1.Show 显示窗体i1 = UserForm1.TextBox2.ValueIf i1 = 12345 Then GoTo Line2ElseIf i1 3 Then GoTo Line1End If确定新产生的文件名Select Case i1 Case 1 FileName = Path2 Mid(m1, 1, 3) (2012).xlsx DirName = m1 Case 2 FileName = Path2 Mid(m2, 1, 3) (2012).xlsxDirName = m2 Case 3 FileName = Path2 Mid(m3, 1, 3)