基于VBA的廣東省水利工程維修養護定額標準經費測算程序開發與應用

袁文娜，付 山

(1.廣東省水利電力勘測設計研究院有限公司，廣州 510635；2.南方電網調峰調頻發電有限公司，廣州 510630)

1 背景

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

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

VBA的全稱為Visual Vasic for Applications[7]，它能讓office 等應用程序執行通用的自動化任務。VBA編程是基于Microsoft office的一種內置式編程語言，通過編寫程序控制Excel，可實現個性化、自動化、批量化的操作，從而大大提高工作效率；同時，VBA還可以進行復雜的數據處理和分析，并創建個性化窗體界面對Excel進行二次開發[8-12]。VBA不但繼承了VB的開發機制，具有和VB相似的語言結構，而且幾乎繼承了VB的所有功能，易于理解和編譯[13]。

2 數據來源

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

3 技術路線

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

圖1 維修養護經費測算的技術路線示意

4 結果與分析

本文以水庫工程為例，演示本程序的設計和應用過程。在實際測算中，針對水閘工程和堤防工程，準備好這兩類工程的調研數據匯總表和經費測算標準表，稍微調整程序部分代碼，亦可快速完成經費測算工作。

4.1 制作經費測算標準表格

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

表1 水庫工程基本維修養護項目經費計算(節選)

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

4.2 主程序界面設計

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

圖2 維修養護經費測算主程序界面示意

模塊之間有先后順序，點擊先后順序依次是“維修養護經費測算”、“制作經費測算匯總表”、“創建數據透視圖表”和“輸出經費測算匯總表”。

4.3 維修養護經費批量測算

根據“調研數據匯總表”中“水庫名稱”列數據，批量生成以水庫名稱命名的各水庫工程測算空表，使用循環語句將上一步完成的經費測算標準表格粘貼到每一張已命名的水庫工程經費測算空表中，刷新后即批量水庫工程的維修養護經費完成測算。為提高代碼執行速度，在循環開始前關閉屏幕刷新，等循環結束再打開屏幕刷新。實現這一過程的主要代碼如下：

Sub維修養護經費測算()

……

On ErrorGoTo exitsub

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

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

Rngstr = Application.InputBox("請輸入批量創建工作表中模板區域的存放單元格地址，如A1或b3", "批量創建工作表模板", 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

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

圖3 工作表名稱區域的輸入框示意

圖4 工作表內容模板區域的輸入框示意

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

圖6 經費測算完成后的提示框示意

4.4 實現制作經費測算匯總表

針對已完成經費測算水庫，通過循環語句獲取已完成測算的各水庫編號、工程名稱、工程規模、維修養護類別、經費測算等數據，匯總后形成經費測算匯總表。實現這一過程的關鍵代碼如下：

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

繼續點擊“維修養護經費測算”按鈕，程序將自動匯總已完成測算的各水庫編號、工程名稱、工程規模、經費測算等數據，生成的經費測算匯總表文件如圖7所示。匯總表中最后一列將超鏈接至各水庫測算表中，方便查閱測算文件。

圖7 經費測算匯總表文件示意

4.5 創建數據透視表和數據透視圖

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

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

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

With PvtTbl

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

.Orientation = xlPageField

.Position = 1

End With

With .PivotFields("地區類別")

.Orientation = xlRowField

.Position = 1

End With

With .PivotFields("工程規模")

.Orientation = xlRowField

.Position = 2

End With

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

End With

點擊“創建數據透視圖表”按鈕，生成的數據透視表和數據透視圖如圖8、圖9所示。

圖8 維修養護經費數據透視示意

圖9 維修養護經費數據透視示意

4.6 打印經費測算匯總表

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

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

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 結語

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