張君伍　石曉倩　王佳美

摘要：基于WPS的Excel和VBA實現(xiàn)安全評價數(shù)據(jù)的匯總，可以在Excel表中自動提取戶內(nèi)安全評價的數(shù)據(jù)，并計算生成匯總信息，自動格式化保存匯總表。以WPS的Excel為平臺建立的這些宏工具，在實際生產(chǎn)中極大提高了工作效率。

關(guān)鍵詞：農(nóng)村;氣代煤;安全檢查;Excel;VBA

中圖分類號：TP311 ? ? 文獻標識碼：A

文章編號：1009-3044（2019）14-0269-02

近幾年北方城市中霧霾污染經(jīng)久不散，霧霾成因中的一個重要的原因是農(nóng)村燃煤的污染。為此，政府大力推動農(nóng)村“氣代煤”工程建設(shè)。農(nóng)村“氣代煤”的大范圍推廣過程中安全使用問題尤其重要。為解決農(nóng)村“氣代煤”使用安全問題，政府推動了“氣代煤”的安全評價工作，聘請第三方安全評價公司進行戶內(nèi)天然氣使用的安全評價工作。評價公司在安全隱患匯總方面常做不到采用數(shù)據(jù)庫管理，而是采用了Excel表形式對用戶使用天然氣的問題建立了電子檔案，但這樣就面臨一個如何匯總安全隱患的問題。針對這種情況開發(fā)了一個WPS的Excel上的VBA宏作為匯總工具以節(jié)省匯總時間。

1 電子檔案和VBA宏功能介紹

農(nóng)村“氣代煤”安全評價采用現(xiàn)場使用檢查表法對戶內(nèi)用氣情況進行評價，現(xiàn)場填寫檢查表和拍攝照片，資料整理須制作電子檔案。電子檔案內(nèi)容包括工程名、檢查編號、戶名、電話、檢查表逐項內(nèi)容及發(fā)現(xiàn)的安全隱患、安全隱患對應(yīng)的現(xiàn)場照片。功能實現(xiàn)采用的WPS的Excel電子表格。Excel VBA宏的提取功能：從所有戶的電子檔案中提取檢查編號、戶名、電話、安全隱患數(shù)量及內(nèi)容。

2 Excel電子檔案設(shè)計

電子檔案設(shè)置有表名、地址（鎮(zhèn)名、村名）、戶名、電話、燃氣表表號、檢查表、用戶簽字、檢查人員、檢查時間、隱患照片等項。檢查表包含四項：序號、檢查內(nèi)容、檢查結(jié)果、隱患內(nèi)容。檢查內(nèi)容設(shè)計成“是否”型問句，沒有安全隱患則統(tǒng)一為“是”，若有安全隱患則為“否”，這樣便于統(tǒng)計安全隱患數(shù)量。

隱患內(nèi)容錄入時采用規(guī)范化的語言，這里采取對單元格設(shè)置“數(shù)據(jù)有效性”規(guī)范錄入的語言。安全隱患計數(shù)采用COUNTIF函數(shù)，例如統(tǒng)計F47單元格到F54單元格中“否”的個數(shù)，選擇單元格輸入“=COUNTIF（F47：F54，"否"）”。

表格下方逐條列出隱患，并附現(xiàn)場照片。根據(jù)實際情況，隱患數(shù)經(jīng)常是0-5個，根據(jù)這個特點設(shè)計自動生成配圖的隱患內(nèi)容文字。在隱患內(nèi)容列右側(cè)單元格如I7、I8、I9等利用if函數(shù)判定有無內(nèi)容，然后向下填充。在隱患內(nèi)容右側(cè)第二列單元格利用IFERR、INDEX、SMALL、ROW函數(shù)將隱患匯總到連續(xù)單元格如J7、J8、J9等，然后向下填充，隱患內(nèi)容匯集到一起。示例中I列、J列數(shù)據(jù)格式均設(shè)置成白色，不顯示。函數(shù)實例如下：

3 Excel VBA宏設(shè)計

首先建立一個“啟用宏的工作薄”即后綴為xlsm格式的文件。在該文件中“ThisWorkbook”對象代碼窗口中錄入代碼。工作中將xlsm文件放到Excel表的電子檔案文件夾中，Excel表電子檔案采用“xlsx”格式。Excel電子檔案中默認錄入的表格對象名稱為“sheet1”，xlsm文件使用宏提取的匯總表在自身文件的表格對象“sheet1”中。

3.1 VBA提取功能

3.1.1 表頭設(shè)計

匯總表表頭設(shè)計見圖1，首先初始化表格，刪除sheet1表中的所有內(nèi)容，然后設(shè)置表頭，合并單元格，輸入表頭內(nèi)容，調(diào)整文字格式，代碼如下：

Application.ScreenUpdating = False ‘關(guān)閉實時刷新

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

Sheets（"sheet1"）.Activate ‘將xlsm格式的文件中“sheet1”表設(shè)置為當前激活狀態(tài)。

Columns（"F：F"）.ColumnWidth = 45 ‘設(shè)置F列列寬

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

BiaoMing = InputBox（"輸入表名"， "表名"， "XX鎮(zhèn)XX村安全隱患匯總表"）

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

Range（"A1：G1"）.Select ‘合并后單元格格式設(shè)置即表名格式設(shè)置

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

Selection.HorizontalAlignment = xlCenter

Selection.VerticalAlignment = xlCenter

Rows（2）.Select ‘為第二行設(shè)置格式

Selection.HorizontalAlignment = xlCenter

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

Range（"A2"） = "序號" ‘第二行表頭錄入

Range（"B2"） = "姓名"： Range（"C2"） = "表號"： Range（"D2"） = "電話"

Range（"E2"） = "隱患條數(shù)"： Range（"F2"） = "隱患內(nèi)容"： Range（"G2"） = "備注"

3.1.2 數(shù)據(jù)提取

提取每戶的電子檔案過程中需要打開每個電子檔案Excel表，此處使用do Loop循環(huán)，逐個打開文件提取數(shù)據(jù)，代碼如下：

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

i = 3 ‘數(shù)據(jù)提取后在xlsm文件“sheet1”表中開始錄入的初始行數(shù)

Do

Workbooks.Open ThisWorkbook.Path & "＼" & Filename ‘打開電子檔案

ThisWorkbook.Sheets（"sheet1"）.Range（"b" & i） = ActiveWorkbook.Sheets（"sheet1"）.Range（"D4"） ‘提取電子檔案中數(shù)據(jù)（編號、戶名、電話等，此處僅舉一例），ActiveWorkbook為打開的電子檔案，ThisWorkbook為宏所在的xlsm文件

…… ‘此處省略與上句相似的提取語句

ActiveWorkbook.Save： ActiveWorkbook.Close ‘保存并關(guān)閉已被打開的電子檔案

Filename = Dir ‘讀取下一個文件名

i = i + 1 ‘匯總數(shù)據(jù)錄入的行數(shù)以加1為步長遞增

Loop Until Filename = "" ‘Filename為空時，讀取完畢，退出Do……Loop循環(huán)

3.1.3 表尾設(shè)計

提取數(shù)據(jù)結(jié)束后，針對數(shù)據(jù)進行簡單匯總，匯總檢查的戶數(shù)，存在問題的百分比，隱患總數(shù)等信息（見圖1）。代碼如下：

k = ThisWorkbook.Sheets（"sheet1"）.UsedRange.Rows.Count ‘獲取匯總表已經(jīng)錄入的行數(shù)

Cells（k + 1， 1） = "總計" ‘本行及以下為表尾數(shù)據(jù)錄入

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

Cells（k + 1， 3） = "檢查戶數(shù)"： Cells（k + 1， 4） = "存在隱患戶數(shù)"

Cells（k + 1， 5） = "隱患條數(shù)"： Cells（k + 1， 6） = "隱患戶數(shù)百分比（%）"

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

表尾的表頭填好后，下面計算匯總明細填入相應(yīng)的表格。沒有安全隱患的戶對應(yīng)的F列安全隱患中是空白，因此存在隱患戶數(shù)計算時采用CountA函數(shù)，統(tǒng)計非空單元格個數(shù)即為存在隱患的戶數(shù)。每戶的隱患條數(shù)在E列，利用Sum函數(shù)計算E列的隱患數(shù)目之和即可。隱患戶數(shù)百分比為存在隱患戶數(shù)除以檢查戶數(shù)所得百分數(shù)。制表時間利用Now函數(shù)獲得，再用Format函數(shù)將日期格式化為“yyyyMMdd”形式。代碼如下：

Cells（k + 2， 3） = k - 2 ‘檢查戶數(shù)

Cells（k + 2， 4） = Application.WorksheetFunction.CountA（Range（"F3：F" & k）） ‘存在隱患戶數(shù)

Cells（k + 2， 5） = Application.WorksheetFunction.Sum（Range（"E3：E" & k）） ‘隱患條數(shù)

Cells（k + 2， 6） = Format（Cells（k + 2， 3） / Cells（k + 2， 2） * 100， "0.0"） ‘隱患戶數(shù)百分比

Cells（k + 2， 7） = Format（Now（）， "yyyy.MM.dd"） ‘將匯總今日的匯總時間填入表中

匯總表制作完成后，為了美觀，調(diào)整一下表格大小和對齊格式。

Columns（"A：E"）.EntireColumn.AutoFit ‘調(diào)整A列至E列的寬度至自動適應(yīng)值

Columns（"A：E"）.HorizontalAlignment = xlCenter ‘調(diào)整A列至E列單元格文字為水平居中

Application.ScreenUpdating = True ‘打開實時刷新

匯總表完成后見圖1。

3.1.4 匯總數(shù)據(jù)保存

為了提高工作效率，采用了VBA自動保存方法，將匯總表保存在當前目錄中，并以鎮(zhèn)村日期等信息命名保存。代碼如下：

Sheets（"Sheet1"）.Select ‘選擇當前xlsm文件中匯總表

Sheets（"Sheet1"）.Copy ‘復(fù)制當前xlsm文件中匯總表

ActiveWorkbook.SaveAs Filename：=ThisWorkbook.Path & "＼" & Format（Date， "yyyymmdd"） & BiaoMing & ".xls"， FileFormat：=xlOpenXMLWorkbook， CreateBackup：=False ‘當前xlsm文件中匯總表另存到當前文件夾下并已“yyyymmdd表名”的文件名保存，文件格式為“xls”，“xls”格式可以和電子檔案的“xlsx”格式區(qū)別開。

ActiveWorkbook.Close ‘關(guān)閉另存為后的匯總表

為了VBA宏的方便使用可以通過“其他命令”將提取數(shù)據(jù)宏添加到窗口頂部的快捷命令中。

4 結(jié)束語

農(nóng)村“氣代煤”安全評價數(shù)據(jù)資料整理中已在運用VBA宏進行匯總整理，安全評價人員相對熟悉Excel工具，應(yīng)用VBA宏操作簡單，學(xué)習(xí)迅速，能夠極大的提高工作效率。Excel的VBA宏工具使用過程中也存在一些不足如當數(shù)據(jù)量過大時耗時過長甚至出錯，這方面問題有待于進一步改進。

參考文獻：

[1] 李政.VBA應(yīng)用基礎(chǔ)與實例教程[M].北京：國防工業(yè)出版社，2009.

[2] 李政，李瑩，張羽.Excel高級應(yīng)用案例教程[M].北京：清華大學(xué)出版社，2010.

【通聯(lián)編輯：梁書】