袁文娜，付 山

(1.廣東省水利電力勘測設(shè)計研究院有限公司，廣州 510635；2.南方電網(wǎng)調(diào)峰調(diào)頻發(fā)電有限公司，廣州 510630)

1 背景

維修養(yǎng)護定額標(biāo)準(zhǔn)的編制是水利工程管理體制改革的基礎(chǔ)工作[1-3]，為適應(yīng)廣東省水利工程管理體制改革、為水利工程維修養(yǎng)護提供經(jīng)費編制和核定依據(jù)，保證水利工程維修養(yǎng)護資金足額到位，廣東省水利廳正在組織相關(guān)單位編制廣東省水利工程維修養(yǎng)護定額標(biāo)準(zhǔn)(以下簡稱“廣東省定額標(biāo)準(zhǔn)”)。定額標(biāo)準(zhǔn)編制完成后，需要對定額標(biāo)準(zhǔn)水平測算，其作為整個定額編制工作中的重要環(huán)節(jié)，對做好后續(xù)修改完善及評審意義重大[4]。定額標(biāo)準(zhǔn)水平的測算可檢測其項目的設(shè)置是否合理，項目的運用是否方便，是否有遺漏或重復(fù)的工程內(nèi)容[5]，其標(biāo)準(zhǔn)水平是否達到平衡與協(xié)調(diào)。由于“廣東省定額標(biāo)準(zhǔn)”為新編定額，無法和原定額進行比較，因此在省水利廳的要求下，編制組擬通過測算全省各類水利工程總宗數(shù)5%～10%的工程維修養(yǎng)護經(jīng)費，分析經(jīng)費分布規(guī)律，并與實際維修養(yǎng)護經(jīng)費對比，以期達到測算定額標(biāo)準(zhǔn)水平的目的。以水庫工程為例，廣東全省現(xiàn)有7 922座[6]，滿足總宗數(shù)8%的要求需測算634座水庫。另外，本次測算除水庫工程外，“廣東省定額標(biāo)準(zhǔn)”中還包括水閘工程和堤防工程兩大類，因此，經(jīng)費測算工作量特別大。

面對定額標(biāo)準(zhǔn)編制時間短、傳統(tǒng)人工逐宗測算效率低下、耗費大量人力的現(xiàn)狀，筆者作為編制組成員，以Excel VBA進行程序開發(fā)，快速實現(xiàn)工程維修養(yǎng)護經(jīng)費測算、經(jīng)費測算匯總、數(shù)據(jù)透視圖表輸出等。該程序操作簡單、易于普及，能大大提高定額標(biāo)準(zhǔn)編制組工作效率，減少人為誤差，在維修養(yǎng)護費用標(biāo)準(zhǔn)調(diào)整后重新測算方便。

VBA的全稱為Visual Vasic for Applications[7]，它能讓office 等應(yīng)用程序執(zhí)行通用的自動化任務(wù)。VBA編程是基于Microsoft office的一種內(nèi)置式編程語言，通過編寫程序控制Excel，可實現(xiàn)個性化、自動化、批量化的操作，從而大大提高工作效率；同時，VBA還可以進行復(fù)雜的數(shù)據(jù)處理和分析，并創(chuàng)建個性化窗體界面對Excel進行二次開發(fā)[8-12]。VBA不但繼承了VB的開發(fā)機制，具有和VB相似的語言結(jié)構(gòu)，而且?guī)缀趵^承了VB的所有功能，易于理解和編譯[13]。

2 數(shù)據(jù)來源

廣東省水利工程維修養(yǎng)護經(jīng)費測算需要的各類水利工程的基本信息主要是以通過省水利廳發(fā)函下發(fā)調(diào)研數(shù)據(jù)統(tǒng)計表，各市區(qū)配合填寫后發(fā)回給編制組，編制組通過對調(diào)研數(shù)據(jù)的檢查、篩除、與之前廣東省第一次全國水利普查數(shù)據(jù)的對比和匯總，得到調(diào)研基礎(chǔ)數(shù)據(jù)匯總表。為保證后續(xù)測算工作的順利進行，此步驟為人工完成，主要包括合并各市縣提交的各類水利工程基礎(chǔ)信息數(shù)據(jù)，檢查數(shù)據(jù)有效性，篩除數(shù)據(jù)不完整的工程，將日期型數(shù)據(jù)轉(zhuǎn)化成數(shù)值型數(shù)據(jù)等。

3 技術(shù)路線

各類水利工程的維修養(yǎng)護經(jīng)費測算是在“廣東省定額標(biāo)準(zhǔn)”初步編制完成后，維修養(yǎng)護經(jīng)費測算的過程主要包括以下幾個方面(如圖1所示)：① 整理、匯總調(diào)研基礎(chǔ)數(shù)據(jù)，形成各類水利工程基礎(chǔ)數(shù)據(jù)庫；② 制作各類水利工程的經(jīng)費測算標(biāo)準(zhǔn)表格，自動完成維修養(yǎng)護類別判定；③ 快速實現(xiàn)批量工程維修養(yǎng)護經(jīng)費的測算；④ 實現(xiàn)制作經(jīng)費測算匯總表，為與實際維修養(yǎng)護經(jīng)費對比提供數(shù)據(jù)支撐；⑤ 實現(xiàn)創(chuàng)建數(shù)據(jù)透視表和數(shù)據(jù)透視圖，為分析經(jīng)費分布規(guī)律提供直觀顯示；⑥ 打印經(jīng)費測算匯總表。

圖1 維修養(yǎng)護經(jīng)費測算的技術(shù)路線示意

4 結(jié)果與分析

本文以水庫工程為例，演示本程序的設(shè)計和應(yīng)用過程。在實際測算中，針對水閘工程和堤防工程，準(zhǔn)備好這兩類工程的調(diào)研數(shù)據(jù)匯總表和經(jīng)費測算標(biāo)準(zhǔn)表，稍微調(diào)整程序部分代碼，亦可快速完成經(jīng)費測算工作。

4.1 制作經(jīng)費測算標(biāo)準(zhǔn)表格

制作經(jīng)費測算標(biāo)準(zhǔn)表格是整個經(jīng)費測算過程中最關(guān)鍵的工作之一，主要依靠Excel自帶函數(shù)實現(xiàn)。制作標(biāo)準(zhǔn)表格需要從人工處理的工作場景入手(見表1)，水庫工程基本情況中除“水庫名稱”和“維修養(yǎng)護類別”外，各項對應(yīng)的值可使用Vlookup函數(shù)從“調(diào)研數(shù)據(jù)匯總表”中按列查找獲取所需要的值?！熬S修養(yǎng)護類別”可根據(jù)水庫庫容和壩高，使用If函數(shù)嵌套自動判定工程維修養(yǎng)護類別。

表1 水庫工程基本維修養(yǎng)護項目經(jīng)費計算(節(jié)選)

考慮到下一步各水庫工程經(jīng)費測算表格批量生成時，須自動命名各工作表(Sheet)的名稱為水庫名稱，為保持各測算表中水庫名稱和工作表名稱(即水庫名稱)一致，因此，使用Rignt、Cell、Len、Find函數(shù)組合后實現(xiàn)“水庫名稱”的對應(yīng)值為工作表名稱。根據(jù)已判定的維修養(yǎng)護類別，使用If函數(shù)嵌套自動獲取對應(yīng)維修養(yǎng)護類別下的費用標(biāo)準(zhǔn)填入“費用標(biāo)準(zhǔn)”列。根據(jù)水庫工程費用標(biāo)準(zhǔn)調(diào)整系數(shù)表編寫計算公式自動計算各影響因素的調(diào)整系數(shù)及綜合調(diào)整系數(shù)，使用Sum函數(shù)合計后，再考慮地區(qū)調(diào)整系數(shù)即為該工程的維修養(yǎng)護經(jīng)費。

4.2 主程序界面設(shè)計

為了測算過程中每次使用方便，在編程過程中采用模塊化處理，主程序界面如圖2所示。

圖2 維修養(yǎng)護經(jīng)費測算主程序界面示意

模塊之間有先后順序，點擊先后順序依次是“維修養(yǎng)護經(jīng)費測算”、“制作經(jīng)費測算匯總表”、“創(chuàng)建數(shù)據(jù)透視圖表”和“輸出經(jīng)費測算匯總表”。

4.3 維修養(yǎng)護經(jīng)費批量測算

根據(jù)“調(diào)研數(shù)據(jù)匯總表”中“水庫名稱”列數(shù)據(jù)，批量生成以水庫名稱命名的各水庫工程測算空表，使用循環(huán)語句將上一步完成的經(jīng)費測算標(biāo)準(zhǔn)表格粘貼到每一張已命名的水庫工程經(jīng)費測算空表中，刷新后即批量水庫工程的維修養(yǎng)護經(jīng)費完成測算。為提高代碼執(zhí)行速度，在循環(huán)開始前關(guān)閉屏幕刷新，等循環(huán)結(jié)束再打開屏幕刷新。實現(xiàn)這一過程的主要代碼如下：

Sub維修養(yǎng)護經(jīng)費測算()

……

On ErrorGoTo exitsub

Set Rng1 =Application.InputBox("請輸入需要批量生成工作表名稱的區(qū)域", "批量創(chuàng)建測算表", Type:=8)

Set Rng2 =Application.InputBox("請輸入各工作表內(nèi)容模板的區(qū)域", "批量創(chuàng)建測算表", Type:=8)

Rngstr = Application.InputBox("請輸入批量創(chuàng)建工作表中模板區(qū)域的存放單元格地址，如A1或b3", "批量創(chuàng)建工作表模板", Type:=2)

Application.ScreenUpdating = False

For Eachrng In Rng1

Sheets.Add Before:=Sheet1

Rng2.Copy

ActiveSheet.Range(Rngstr).PasteSpecial Paste:=xlPasteAll

ActiveSheet.Range(Rngstr).PasteSpecial Paste:=xlPasteColumnWidths

ActiveSheet.Name = rng.Value

i = i + 1

Nextrng

Application.CutCopyMode = False

Application.ScreenUpdating = True

Sheet1.Select

exitsub:

End Sub

運行主程序，點擊“維修養(yǎng)護經(jīng)費測算”按鈕，根據(jù)輸入框提示，先后輸入工作表名稱區(qū)域、工作表內(nèi)容模板區(qū)域和工作表模板存放地址3個區(qū)域(如圖3～圖5所示)，也可用鼠標(biāo)先后框選這3個區(qū)域，程序經(jīng)過數(shù)據(jù)提取、處理等一系列操作完成測算后，將彈出“恭喜！已完成812座水庫的測算表！”的提示框(見圖6)。

圖3 工作表名稱區(qū)域的輸入框示意

圖4 工作表內(nèi)容模板區(qū)域的輸入框示意

圖5 工作表模板存放地址的輸入框示意

圖6 經(jīng)費測算完成后的提示框示意

4.4 實現(xiàn)制作經(jīng)費測算匯總表

針對已完成經(jīng)費測算水庫，通過循環(huán)語句獲取已完成測算的各水庫編號、工程名稱、工程規(guī)模、維修養(yǎng)護類別、經(jīng)費測算等數(shù)據(jù)，匯總后形成經(jīng)費測算匯總表。實現(xiàn)這一過程的關(guān)鍵代碼如下：

For n = 2 To Sheets.Count - 5

Sheet6.Range("A" & n) = n - 1

Sheet6.Range("B" & n) = Sheets(n - 1).Range("C1")

……

Sheet6.Range("F" & n) = Sheets(n - 1).Range("G84") / 10000

……

Next

For x = 2To Sheets.Count - 5

Sheet6.Hyperlinks.Add Anchor:=Cells(x, 14), Address:=ActiveWorkbook.Name, SubAddress:=Sheets(x - 1).Name & "!A1", TextToDisplay:=Sheets(x - 1).Name

Next

繼續(xù)點擊“維修養(yǎng)護經(jīng)費測算”按鈕，程序?qū)⒆詣訁R總已完成測算的各水庫編號、工程名稱、工程規(guī)模、經(jīng)費測算等數(shù)據(jù)，生成的經(jīng)費測算匯總表文件如圖7所示。匯總表中最后一列將超鏈接至各水庫測算表中，方便查閱測算文件。

圖7 經(jīng)費測算匯總表文件示意

4.5 創(chuàng)建數(shù)據(jù)透視表和數(shù)據(jù)透視圖

Excel在創(chuàng)建數(shù)據(jù)透視表時，會先為數(shù)據(jù)源創(chuàng)建一個緩存副本，再基于緩存創(chuàng)建數(shù)據(jù)透視表[9]。因此，本程序用VBA創(chuàng)建數(shù)據(jù)透視表時先創(chuàng)建數(shù)據(jù)源的緩存，相應(yīng)的對象為PivotCache，再基于緩存創(chuàng)建數(shù)據(jù)透視表。創(chuàng)建數(shù)據(jù)透視表之后，可基于數(shù)據(jù)透視表再創(chuàng)建數(shù)據(jù)透視圖，以便更加直觀地觀察和分析數(shù)據(jù)。實現(xiàn)這一過程的關(guān)鍵代碼如下：

Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= sht1.Range("A1:F813"))

Set PvtTbl = PvtCache.CreatePivotTable(TableDestination:=sht2.Range("A1"), TableName:="經(jīng)費測算平均值")

With PvtTbl

With .PivotFields("經(jīng)費測算(萬元)")

.Orientation = xlPageField

.Position = 1

End With

With .PivotFields("地區(qū)類別")

.Orientation = xlRowField

.Position = 1

End With

With .PivotFields("工程規(guī)模")

.Orientation = xlRowField

.Position = 2

End With

.AddDataField .PivotFields("經(jīng)費測算(萬元)"), "平均值", xlAverage

End With

點擊“創(chuàng)建數(shù)據(jù)透視圖表”按鈕，生成的數(shù)據(jù)透視表和數(shù)據(jù)透視圖如圖8、圖9所示。

圖8 維修養(yǎng)護經(jīng)費數(shù)據(jù)透視示意

圖9 維修養(yǎng)護經(jīng)費數(shù)據(jù)透視示意

4.6 打印經(jīng)費測算匯總表

在Excel VBA中，可以使用PrintOut方法打印工作表，使用PageSetup對象設(shè)置頁邊距，使用PageSetup對象的PrintArea屬性自定義打印范圍，使用PageSetup對象的Zoom屬性設(shè)置打印頁面的縮放比例等。實現(xiàn)這一過程的關(guān)鍵代碼如下：

Setmysht = Worksheets("經(jīng)費測算匯總表")

m =Application.CentimetersToPoints(2)

Withmysht.PageSetup

.PrintTitleRows = mysht.Rows(1).Address

.PrintArea = mysht.UsedRange.Address

.TopMargin = m

……

.Orientation =xlLandscape

.PaperSize = xlPaperA4

.Zoom = 65

End With

mysht.PrintOut ActivePrinter:="Microsoft Print to PDF"

5 結(jié)語

通過在廣東省水利工程維修養(yǎng)護定額標(biāo)準(zhǔn)經(jīng)費測算過程中用Excel內(nèi)置的VBA平臺進行程序開發(fā)，能夠快速實現(xiàn)維修養(yǎng)護經(jīng)費的批量測算處理，快速完成測算后工程規(guī)模、維修養(yǎng)護類別及維修養(yǎng)護經(jīng)費等指標(biāo)的統(tǒng)計、數(shù)據(jù)透視圖表的顯示、經(jīng)費測算匯總文件的輸出打印等。據(jù)測算，在準(zhǔn)備好調(diào)研數(shù)據(jù)匯總表基礎(chǔ)上，傳統(tǒng)人工逐宗測算634座水庫維修養(yǎng)護經(jīng)費大致需花費105.67工時，應(yīng)用本程序測算只需花費0.42工時，且該程序操作簡單，維修養(yǎng)護費用標(biāo)準(zhǔn)調(diào)整后重新測算方便，實現(xiàn)了數(shù)據(jù)處理自動化，成功避免人工測算繁瑣、易出錯的問題，使定額編制組成員從重復(fù)、枯燥的勞動中解脫出來，在提高了維修養(yǎng)護經(jīng)費測算的工作效率同時，使定額編制人員有更多精力專注于定額標(biāo)準(zhǔn)水平專業(yè)分析，有效提升水利工程維修養(yǎng)護定額標(biāo)準(zhǔn)水平測算的工作質(zhì)量。本文對定額標(biāo)準(zhǔn)經(jīng)費測算程序關(guān)鍵步驟、關(guān)鍵代碼均進行了詳細(xì)描述，本程序的開發(fā)思路在同類型的數(shù)據(jù)處理上具有廣泛的應(yīng)用參考價值。