農(nóng)村“氣代煤”安全評(píng)價(jià)匯總中Excel及VBA的應(yīng)用

張君伍　石曉倩　王佳美

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

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

中圖分類(lèi)號(hào)：TP311 ? ? 文獻(xiàn)標(biāo)識(shí)碼：A

文章編號(hào)：1009-3044（2019）14-0269-02

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

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

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

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

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

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

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

3 Excel VBA宏設(shè)計(jì)

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

3.1 VBA提取功能

3.1.1 表頭設(shè)計(jì)

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

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

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

Sheets（"sheet1"）.Activate ‘將xlsm格式的文件中“sheet1”表設(shè)置為當(dāng)前激活狀態(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"） = "序號(hào)" ‘第二行表頭錄入

Range（"B2"） = "姓名"： Range（"C2"） = "表號(hào)"： Range（"D2"） = "電話"

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

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

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

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

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

Do

Workbooks.Open ThisWorkbook.Path & "＼" & Filename ‘打開(kāi)電子檔案

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

…… ‘此處省略與上句相似的提取語(yǔ)句

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

Filename = Dir ‘讀取下一個(gè)文件名

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

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

3.1.3 表尾設(shè)計(jì)

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

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

Cells（k + 1， 1） = "總計(jì)" ‘本行及以下為表尾數(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） = "制表日期"

表尾的表頭填好后，下面計(jì)算匯總明細(xì)填入相應(yīng)的表格。沒(méi)有安全隱患的戶對(duì)應(yīng)的F列安全隱患中是空白，因此存在隱患戶數(shù)計(jì)算時(shí)采用CountA函數(shù)，統(tǒng)計(jì)非空單元格個(gè)數(shù)即為存在隱患的戶數(shù)。每戶的隱患條數(shù)在E列，利用Sum函數(shù)計(jì)算E列的隱患數(shù)目之和即可。隱患戶數(shù)百分比為存在隱患戶數(shù)除以檢查戶數(shù)所得百分?jǐn)?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"） ‘將匯總今日的匯總時(shí)間填入表中

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

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

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

Application.ScreenUpdating = True ‘打開(kāi)實(shí)時(shí)刷新

匯總表完成后見(jiàn)圖1。

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

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

Sheets（"Sheet1"）.Select ‘選擇當(dāng)前xlsm文件中匯總表

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

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

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

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

4 結(jié)束語(yǔ)

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

參考文獻(xiàn)：

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

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

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