VBA技术在数据整治中的使用

摘　要：对于所有的信息系统来说，界面华丽的图表展示，都是由底层的数据来支撑的，如果底层的数据杂乱无章，界面展示的再绚丽，对使用者来说没有任何的价值。所以数据的整治工作在信息系统中变得尤为重要。但数据整治工作工作量都比较大，如何减少数据整治工作的工作量，提升工作效率有多种方法，本文结合实际工作经验，介绍使用VBA技术提升数据整治效率的方法。

关键字：VBA　数据整治

中图分类号：G642

文献标识码：A

文章编号：1672-8882（2012）12-025-02

VBA（Visual　Basic　for　Applications）是Visual　Basic的一种宏语言，主要能用来扩展windows的应用程式功能，特别是Microsoft　Office软件。也就是说是一种应用程式视觉化的Basic　Script。

笔者所在的公司曾为多家公司开展过数据整治工作。数据量从万级到百万级不等。笔者也负责过几次数据整治，深知数据整治工作的重要性。本文将结合笔者的现场工作经验，针对VBA技术在数据整治中的使用进行详细介绍。

数据整治过程中，最常遇到的如数据汇总、数据拆分、数据格式自动检查等，本文就针对这三种情况下VBA技术使用进行阐述。

一、数据汇总

情景：下发数据收集模板进行数据收集，用户提交了很多的数据上来，数据分布在不同的表格内，现需对所有数据进行审核。每个表格检查步骤都一样，如果逐个表格检查，工作量很大，而如果数据汇总起来统一检查，这样会大大减少检查时间，那么下面进行汇总数据。

问题：手工汇总数据就是频繁的打开文件，复制数据，粘贴数据，关闭文件。如果数据有多个表格，多个Sheet页，有些表格提交时忘了去除筛选状态，很容易导致疏漏，或重复汇总问题。

VBA解决办法：

在VBA中使用Dir（pathname&“*.xls”），获取指定目录下所有的EXCEL文件清单，使用Do　while遍历每个Excel文件，遍历文件时使用for循环遍历所有Sheet页。使用AutoFilterMode　=　False，解除表格文件的筛选。可以使用UsedRange来选择数据，也可以一行一行选择数据，本文使用UsedRange为例，代码示例如下：

如果汇总数据可能会超过EXCEL表格最大数据量65536（Excel2003的最大行数）的话，可以在程序中添加代码进行判断，使用Worksheets.Add，新增sheet也，将后面的数据放入新的sheet中。

二、数据拆分

情景：项目组从系统中导出所有的数据，准备下发给用户或者项目组成员整改，数据需要按照一定的逻辑进行拆分开来。那么下面进行数据拆分。

问题：手工拆分数据就是频繁的筛选数据、复制数据，新增表格，粘贴数据。如果需要拆分成很多个表格，很容易导致内容或者标题疏漏。

VBA解决办法：

数据可以拆分至多个excel文件，也可以拆分至多个sheet页，本文以拆分至多个sheet页为例，示例代码如下：

判断sheet页是否存在的getExsit函数代码：

三、数据格式自动检查

情景：收集上来的数据，可能存在很多方面不符合要求，如：文本长度，文本必填，文本唯一性，文本格式，文本有效性等，无法满足系统要求，需要对数据进行检查，那么下面进行数据的检查。

问题：手工检查数据工作量大，需要用大量的公式进行按列检查。公式的操作，可能会大面积变动原有的数据表，容易出错和疏漏。且手工检查只能有少数人进行，检查效率较低。

VBA解决办法：

我们可以使用UsedRange确定数据范围，逐单元格进行检查。定义一些检查的函数，当检查不同列时，调用不同的函数，来判断单元格内的值是否满足要求。部分函数代码示例如下：

检查文本长度：

检查必填：

检查唯一性：

文本格式（以检查数值格式为例）：

文本有效性，可借助VBA的圈释CircleInvalid功能，也可以使用定义好的Range来进行检查，本文中以Range来检查：

当确定单元格文本存在错误后，如何进行标注错误呢？VBA中可以用批注功能AddComment，也可以用单元格底色进行标注，本文使用单元格底色进行标注，这样可以使用多种颜色来标注不同的错误类型，简明直观：

使用VBA检查数据还有一点好处，当模板中嵌入检查代码后，最终收集数据的人员可以自行检查数据问题。这样大大提高汇总前的数据质量，减少最终数据检查的工作量，可以将核心的人员从繁琐的数据检查的工作中释放处理。

当然，数据治理遇到的问题远不止以上列举的三个问题，其他的问题也同样可以考虑使用VBA来解决。微软给我们提供了这么实用的工具，我们要充分利用起来，让枯燥数据治理工作变得越来越轻松。