VBA在对内广播效果质量报表中的应用

■文/王 强

VBA在对内广播效果质量报表中的应用

■文/王 强

本文介绍了用VBA实现报表自动化的关键语句和业务逻辑。使用VBA报表的制作速度是人工制作速度的两倍以上，提高了报表制作效率。

VBA使用；报表自动化；数据中心

引言

我所在的部门是数据中心。数据中心是所有数据的汇集地点，也是众多报表的制作地点。使用VBA能大大提高制作报表的效率，将给数据中心带来很大帮助。同时，各直属台也要制作报表。若他们使用VBA，也会给他们带来效率上的提高。

VBA（Visual Basic for Applications）是Visual Basic的一种宏语言，是微软开发出来在其桌面应用程序中执行通用的自动化（OLE）任务的编程语言。主要能用来扩展Windows的应用程序功能，特别是Microsoft Office软件。也可说是一种应用程式视觉化的Basic脚本。

对内效果及质量月报描述了对内效果及质量的情况，是重要的广播报表，包含多个附件。由于该报表要抄送给多个单位，从侧面说明此报表很受重视。在制作过程中，为了便于制作，我又添加了5个中间附件。过去没有加入VBA程序时，要花费3天制作月报。现在，顺利的话，不到1天半的时间可以完成。这样大大提高了制作报表的效率。而且用VBA时，报表不容易出错。

1.报表制作流程

下面介绍比较有特点的VBA使用。复制和刷新透视表是比较多的操作过程。多列去重复是比较特殊的应用，是我用VBA录制宏之后，反向得到的。后几个介绍都是业务流程性的，操作起来比较复杂。目的是为了跟读者分享一下VBA究竟能解决什么复杂问题。

1.1 复制工作薄的使用内容到本工作簿

workbookA.Sheets(x).UsedRange.Copy ThisWorkbook. Sheets(x).Range(“a1”)

把工作簿A中sheet x使用的内容复制到本工作簿sheetx的左上角位置。

1.2 整列复制内容到需要的位置

Sheets(x).Range(“H:H”).Copy Sheets(x1).Range(“A1”)复制sheet x的H列到sheetx1的A1单元格内容。

1.3 刷新透视表

Sheets(x).PivotTables(“ 数 据 透 视 表 x”).PivotCache. Refresh

刷新本工作簿下的透视表x。当报表中存在大量透视表，该语句可以大大提高效率和操作准确度。

1.4 多列去重复

乳酸链球菌素(Nisin)是由乳酸链球菌合成的一种多肽抗菌素，能有效抑制革兰氏阳性菌，如金黄色葡糖球菌、溶血性链球菌、单增李斯特菌、肉毒杆菌，是国际上公认的天然防腐剂。研究表明[11]，在不影响色泽和防腐的情况下，添加Nisin可使亚硝酸盐的含量由原来的150 mg/kg降到40 mg/kg。若添加0.2 g/kg Nisin, 则亚硝酸盐的添加量减少到 0.04 g/kg，香肠中的菌落总数可降低到3200 cfu/g，抑菌效果明显。

Sheets(“x).Range(“$A$1:$C$65535”).RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes

A、B和C三列联合去重复。

Sheets(X).Range(“$A$1:$D$65535”).RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlYes

A、B、C和D四列联合去重复。

去重复也是使用比较多的功能。这个语句我是通过录制宏，得到代码，再调试得到的。这种方法值得学习。

1.5 检查可听率对比表中的数据剧烈变化

程序的一个重要内容是检查可听率对比表中的数据是否存在剧烈变化。所谓剧烈变化检查就是，检查本月的可听率与上月相比是否下降了10%以上（含10%），还有该前端设备本月的可听率是否为0（前端设备未收测）。检查的内容如表1所示。检查的表格还有按地区划分的节目可听率对比表和按地区划分的语言可听率对比表。

表1 按地区分的前端设备可听率对比表

从第一条记录开始检查表1。先判断地区中是否存在前端设备，如果不存在前端设备则跳转到下一个地区。如果存在前端设备，则判断该前端设备的本月可听率是否为0，为0则记录下来“该前端设备本月未收测”，不为0则检查本月的可听率是否下降了10%。若可听率下降了10%，则记录下来“该前端设备的可听率下降达到10%”。检查本表格中是否所有的记录都完成了。没有完成，则检查下一条记录。所有的情况都记录在一个名为《出现的问题及回答》的文件中。记录完成后，还要通过查看直属台月报数据和询问直属台的方法了解可听率出现剧烈变化的原因，并把原因记录在该文件中，以备月报之用。具体流程如图1所示。

图1 可听率剧烈变化的检查方法

在多报表导入前，判断其报表存在性是必要条件。若报表不存在，则程序自动会提出错误信息。但这种错误信息一般不能直接指出出现的问题。所以，需要自己写代码，代码中编写提示信息。这样可以直接指出问题，如图2所示。首先要检查要导入的文件是否存在，若存在，则查找下一个文件。否则，记录不存在的文件名，给出提示信息，跳出程序。待重新准备好相应的文件再次运行程序。该程序是遇到问题直接中断，而没有把所有问题都找出来。这种设计的考虑是一般准备好文件后，顶多有一两个文件漏准备。中断之后，回来快速准备文件，可以快速运行。如果真是多个文件漏准备，熟练的报表制作者运行一次中断后就能知道自己的问题。

图2 多个报表导入前的准备程序

1.7 发射台频率汇总

表2发射台频率汇总表分别记录每个发射台（发射台存在发射台X型和发射台Y型两种类型）每月播出中短波和调频的频率数目。表格要求中央直属发射台记录于上半区，地方发射台记录于下半区。

表2 发射台频率汇总表

从已获得的列表中比对表2的发射台名称，若发射台频率汇总表中存在该发射台名称，则在对应位置填写频率数。若不存在该发射台名称，当获得的发射台属于是发射台X型时，在最上面一行插入相应信息；当获得的是发射台Y型时，在最后一行插入相应信息。

1.8 按地区划分可听率统计表

表3为可听率统计表。程序要按照行政区，把相应的记录放置到对应行政区的工作薄中。具体的做法是，先按照行政区对可听率统计表进行排序。再一次性把记录放置到相应工作簿。

可听率是指可听度大于等于3分的次数与总收听次数之比，用百分数表示。在这里，可听率等于3分以上频次和总收测频次之比。每个频率收测一次，叫做一频次。

表3 可听率统计表

2.结束语

使用VBA制作Excel报表将会给我们带来效率上的大幅提高，而且减少了出错的可能性。希望有更多的报表制作者都尝试使用VBA，体验VBA带来的改变。

[1]李晓玫等.Excel中的VBA程序设计[J].四川师范大学学报，2004，27（4）：423-424.

[2]任小龙等.VBA程序接口在仪表设计中的应用[J].化工自动化及仪表，2016，43（2）：145-146.

（作者单位：国家新闻出版广电总局监测数据处理中心）

TP311.1

A

1671-0134（2017）12-109-03

10.19483/j.cnki.11-4653/n.2017.03.026