农村“气代煤”安全评价汇总中Excel及VBA的应用

张君伍　石晓倩　王佳美

摘要：基于WPS的Excel和VBA实现安全评价数据的汇总，可以在Excel表中自动提取户内安全评价的数据，并计算生成汇总信息，自动格式化保存汇总表。以WPS的Excel为平台建立的这些宏工具，在实际生产中极大提高了工作效率。

关键词：农村;气代煤;安全检查;Excel;VBA

中图分类号：TP311     文献标识码：A

文章编号：1009-3044（2019）14-0269-02

近几年北方城市中雾霾污染经久不散，雾霾成因中的一个重要的原因是农村燃煤的污染。为此，政府大力推动农村“气代煤”工程建设。农村“气代煤”的大范围推广过程中安全使用问题尤其重要。为解决农村“气代煤”使用安全问题，政府推动了“气代煤”的安全评价工作，聘请第三方安全评价公司进行户内天然气使用的安全评价工作。评价公司在安全隐患汇总方面常做不到采用数据库管理，而是采用了Excel表形式对用户使用天然气的问题建立了电子档案，但这样就面临一个如何汇总安全隐患的问题。针对这种情况开发了一个WPS的Excel上的VBA宏作为汇总工具以节省汇总时间。

1 电子档案和VBA宏功能介绍

农村“气代煤”安全评价采用现场使用检查表法对户内用气情况进行评价，现场填写检查表和拍摄照片，资料整理须制作电子档案。电子档案内容包括工程名、检查编号、户名、电话、检查表逐项内容及发现的安全隐患、安全隐患对应的现场照片。功能实现采用的WPS的Excel电子表格。Excel VBA宏的提取功能：从所有户的电子档案中提取检查编号、户名、电话、安全隐患数量及内容。

2 Excel电子档案设计

电子档案设置有表名、地址（镇名、村名）、户名、电话、燃气表表号、检查表、用户签字、检查人员、检查时间、隐患照片等项。检查表包含四项：序号、检查内容、检查结果、隐患内容。检查内容设计成“是否”型问句，没有安全隐患则统一为“是”，若有安全隐患则为“否”，这样便于统计安全隐患数量。

隐患内容录入时采用规范化的语言，这里采取对单元格设置“数据有效性”规范录入的语言。安全隐患计数采用COUNTIF函数，例如统计F47单元格到F54单元格中“否”的个数，选择单元格输入“=COUNTIF（F47：F54，"否"）”。

表格下方逐条列出隐患，并附现场照片。根据实际情况，隐患数经常是0-5个，根据这个特点设计自动生成配图的隐患内容文字。在隐患内容列右侧单元格如I7、I8、I9等利用if函数判定有无内容，然后向下填充。在隐患内容右侧第二列单元格利用IFERR、INDEX、SMALL、ROW函数将隐患汇总到连续单元格如J7、J8、J9等，然后向下填充，隐患内容汇集到一起。示例中I列、J列数据格式均设置成白色，不显示。函数实例如下：

3 Excel VBA宏设计

首先建立一个“启用宏的工作薄”即后缀为xlsm格式的文件。在该文件中“ThisWorkbook”对象代码窗口中录入代码。工作中将xlsm文件放到Excel表的电子档案文件夹中，Excel表电子档案采用“xlsx”格式。Excel电子档案中默认录入的表格对象名称为“sheet1”，xlsm文件使用宏提取的匯总表在自身文件的表格对象“sheet1”中。

3.1 VBA提取功能

3.1.1 表头设计

汇总表表头设计见图1，首先初始化表格，删除sheet1表中的所有内容，然后设置表头，合并单元格，输入表头内容，调整文字格式，代码如下：

Application.ScreenUpdating = False ‘关闭实时刷新

Sheets（"sheet1"）.Cells.Select： Selection.Delete Shift：=xlUp'删除所有单元格，初始化表格

Sheets（"sheet1"）.Activate ‘将xlsm格式的文件中“sheet1”表设置为当前激活状态。

Columns（"F：F"）.ColumnWidth = 45 ‘设置F列列宽

Range（"A1：G1"）.Merge ‘合并A1：G1单元格

BiaoMing = InputBox（"输入表名"， "表名"， "XX镇XX村安全隐患汇总表"）

Range（"A1"）.Value = BiaoMing ‘录入表名

Range（"A1：G1"）.Select ‘合并后单元格格式设置即表名格式设置

Selection.Font.Size = 16： Selection.Font.Bold = True

Selection.HorizontalAlignment = xlCenter

Selection.VerticalAlignment = xlCenter

Rows（2）.Select ‘为第二行设置格式

Selection.HorizontalAlignment = xlCenter

Selection.VerticalAlignment = xlCenter： Selection.Font.Bold = True

Range（"A2"） = "序号" ‘第二行表头录入

Range（"B2"） = "姓名"： Range（"C2"） = "表号"： Range（"D2"） = "电话"

Range（"E2"） = "隐患条数"： Range（"F2"） = "隐患内容"： Range（"G2"） = "备注"

3.1.2 数据提取

提取每户的电子档案过程中需要打开每个电子档案Excel表，此处使用do Loop循环，逐个打开文件提取数据，代码如下：

Filename = Dir（ThisWorkbook.Path & "＼" & "*.xlsx"） ‘获取所有电子档案（xlsx格式）文件名

i = 3 ‘数据提取后在xlsm文件“sheet1”表中开始录入的初始行数

Do

Workbooks.Open ThisWorkbook.Path & "＼" & Filename ‘打开电子档案

ThisWorkbook.Sheets（"sheet1"）.Range（"b" & i） = ActiveWorkbook.Sheets（"sheet1"）.Range（"D4"） ‘提取电子档案中数据（编号、户名、电话等，此处仅举一例），ActiveWorkbook为打开的电子档案，ThisWorkbook为宏所在的xlsm文件

…… ‘此处省略与上句相似的提取语句

ActiveWorkbook.Save： ActiveWorkbook.Close ‘保存并关闭已被打开的电子档案

Filename = Dir ‘读取下一个文件名

i = i + 1 ‘汇总数据录入的行数以加1为步长递增

Loop Until Filename = "" ‘Filename为空时，读取完毕，退出Do……Loop循环

3.1.3 表尾设计

提取数据结束后，针对数据进行简单汇总，汇总检查的户数，存在问题的百分比，隐患总数等信息（见图1）。代码如下：

k = ThisWorkbook.Sheets（"sheet1"）.UsedRange.Rows.Count ‘获取汇总表已经录入的行数

Cells（k + 1， 1） = "总计" ‘本行及以下为表尾数据录入

Range（"A" & k + 1 & "：B" & k + 2）.Merge ‘合并单元格

Cells（k + 1， 3） = "检查户数"： Cells（k + 1， 4） = "存在隐患户数"

Cells（k + 1， 5） = "隐患条数"： Cells（k + 1， 6） = "隐患户数百分比（%）"

Cells（k + 1， 7） = "制表日期"

表尾的表頭填好后，下面计算汇总明细填入相应的表格。没有安全隐患的户对应的F列安全隐患中是空白，因此存在隐患户数计算时采用CountA函数，统计非空单元格个数即为存在隐患的户数。每户的隐患条数在E列，利用Sum函数计算E列的隐患数目之和即可。隐患户数百分比为存在隐患户数除以检查户数所得百分数。制表时间利用Now函数获得，再用Format函数将日期格式化为“yyyyMMdd”形式。代码如下：

Cells（k + 2， 3） = k - 2 ‘检查户数

Cells（k + 2， 4） = Application.WorksheetFunction.CountA（Range（"F3：F" & k）） ‘存在隐患户数

Cells（k + 2， 5） = Application.WorksheetFunction.Sum（Range（"E3：E" & k）） ‘隐患条数

Cells（k + 2， 6） = Format（Cells（k + 2， 3） / Cells（k + 2， 2） * 100， "0.0"） ‘隐患户数百分比

Cells（k + 2， 7） = Format（Now（）， "yyyy.MM.dd"） ‘将汇总今日的汇总时间填入表中

汇总表制作完成后，为了美观，调整一下表格大小和对齐格式。

Columns（"A：E"）.EntireColumn.AutoFit ‘调整A列至E列的宽度至自动适应值

Columns（"A：E"）.HorizontalAlignment = xlCenter ‘调整A列至E列单元格文字为水平居中

Application.ScreenUpdating = True ‘打开实时刷新

汇总表完成后见图1。

3.1.4 汇总数据保存

为了提高工作效率，采用了VBA自动保存方法，将汇总表保存在当前目录中，并以镇村日期等信息命名保存。代码如下：

Sheets（"Sheet1"）.Select ‘选择当前xlsm文件中汇总表

Sheets（"Sheet1"）.Copy ‘复制当前xlsm文件中汇总表

ActiveWorkbook.SaveAs Filename：=ThisWorkbook.Path & "＼" & Format（Date， "yyyymmdd"） & BiaoMing & ".xls"， FileFormat：=xlOpenXMLWorkbook， CreateBackup：=False ‘当前xlsm文件中汇总表另存到当前文件夹下并已“yyyymmdd表名”的文件名保存，文件格式为“xls”，“xls”格式可以和电子档案的“xlsx”格式区别开。

ActiveWorkbook.Close ‘关闭另存为后的汇总表

为了VBA宏的方便使用可以通过“其他命令”将提取数据宏添加到窗口顶部的快捷命令中。

4 结束语

农村“气代煤”安全评价数据资料整理中已在运用VBA宏进行汇总整理，安全评价人员相对熟悉Excel工具，应用VBA宏操作简单，学习迅速，能够极大的提高工作效率。Excel的VBA宏工具使用过程中也存在一些不足如当数据量过大时耗时过长甚至出错，这方面问题有待于进一步改进。

参考文献：

[1] 李政.VBA应用基础与实例教程[M].北京：国防工业出版社，2009.

[2] 李政，李莹，张羽.Excel高级应用案例教程[M].北京：清华大学出版社，2010.

【通联编辑：梁书】