VBA技術在精準扶貧數據處理中的應用研究

時亞南，郭鳳娟

1.新疆特種設備檢驗研究院，烏魯木齊 830011 2.克拉瑪依市氣象局，新疆克拉瑪依 834000

1 應用背景及意義

新疆南疆四地州是脫貧攻堅工作的主戰場[1]，在國家戰略全局中具有特別重要的意義，和田地區洛浦縣多魯鄉塘瑪合尼村是深度貧困村，根據自治區的安排部署，新疆特種設備檢驗研究院派駐專人參加該村駐村扶貧工作，經過與扶貧專干接觸發現，他們大量的時間都用于各種繁重的扶貧表格填報，僅以貧困戶信息采集表為例，平均每個貧困戶家庭需要采集的數據在100項以上，該村共有43戶貧困戶，僅完成此項工作至少需要兩天時間，任務繁重，占用走訪入戶時間，工作效率低下。

通過與附近兩個扶貧村進行工作交流，新疆農業職業技術學院扶貧專干呂志遠老師介紹了他根據整理的扶貧大數據臺賬表，以及利用Excel第三方插件方方格子從臺賬表抽取數據，自動生成每家每戶的扶貧數據的先進做法，該村共有107戶貧困戶，使用此方法可以將效率提高好幾倍。根據呂老師介紹，使用方方格子綁定數據源，每個數據源僅可以關聯一個數據字段，但是針對每戶的成員信息，比如每戶有4個成員，每個成員有20項信息需要采集，使用該控件只能提取第一位成員的20 項信息，其他成員的信息無法提取，這制約了工作效率的進一步提高，迫切需要解決。鑒于此種現狀，筆者自主編寫一段應用程序，在目前操作人員已經熟悉的方方格子控件的基礎上，增加一段宏程序，實現基礎臺賬數據表和模板表進行自動關聯，并根據已經使用方方格子生成的脫貧臺賬表，從基礎臺賬數據表中抽取方方格子實現不了的每戶信息，使脫貧專干從繁重的填寫表格任務中解放出來，對提高扶貧專干的工作效率具有較大幫助。

2 技術選型

筆者提出了兩種解決方案，第一種方案為利用Python+MySQL 技術編寫程序處理，第二種方案為基于Excel+VBA 技術。考慮到駐村隊員大多都是非計算機專業出身，大都沒有掌握計算機系統數據庫技術，所以第一種方案操作難度較大，難以推廣使用；第二種方案中VBA 是內嵌入Excel 中的宏程序，比較容易實現對Excel 數據的處理，使用簡單方便，可移植性和可維護性方便，容易上手，可推廣性也較強。因此，本人決定放棄第一種解決方案，使用第二種方案解決。

3 程序實現

程序中包含三種表，一種是扶貧大數據臺賬表（即源表），一種是模板表（上級部門提供的需要上報的源表樣式），還有一種是從源表中抽取信息生成的目標表（即需要打印出來存檔或者上報的表）。扶貧大數據臺賬表中每戶的唯一標識為舊門牌號或者新門牌號，包含幾十項信息，信息準確性由扶貧專干進行維護。如果期望生成的每張表都是A3（或A4）打印格式的，只需把模板表格式調整成A3（或A4）格式即可，生成的目標表中的所有格式和模板表一樣。特別格式的，因VBA中的copy函數僅僅復制數據，不做樣式的復制，所以如果對樣式或者排版美觀度有要求的，需要提前將模板表的對應單元格樣式（比如字體，字號，單元格格式等）設置好，生成的目標表樣式和模板表是一樣的。程序實現如下：

（1）首先，分別定義當前sheet和生成的工作簿sheet 的名稱變量 curSheetName 和 genSheetName，設置生成的目標文件中的起始行位置start_pos（如果行數發生改變，修改此值即可），需要獲取的每戶在源表中的起始行號位置begin_pos和end_pos，并通過此計算出每家每戶的人數number。

（2）然后通過Set cursheet = ThisWorkbook.Sheets("sheet1")獲取當前sheet，sheet1為sheet名稱，通過curSheetName = cursheet.Name 獲取取當前sheet的名稱，iRow = cursheet.UsedRange.Rows.Count 獲取當前sheet 的有效行數，iColumn =cursheet.UsedRange.Columns.Count 獲取當前sheet的有效列數，Set genBook = Workbooks.Open("C：UsersAdministratorDesktop貧困戶信息采集表.xls") 根據指定路徑獲取利用方方格子生成的數據地址，因方方格子可以按照指定的模板表按照唯一標識舊門牌號或者新門牌號作為sheet 名生成一個個包含此模板表內容的工作簿，因此需要通過sheetCount = genBook.Sheets.Count 獲取生成的工作簿的數量，通過Set genSheet = genBook.Sheets(sheetCnt) 獲取生成的data 工作簿的當前sheet，genSheetName = genSheet.Name 獲取每個sheet 的值，實際上是獲取新門牌號，大循環中For sheetCnt = 2 To sheetCount 作為第一層for循環，第二層循環中進行如下操作；

通過如下語句從前往后找，找出第一個門牌號出現的位置

For i = 2 To iRow

newDoorNumber = cursheet.Cells(i， 1)

If newDoorNumber = genSheetName Then

begin_pos = i

Exit For

End If

Next

通過如下語句從后往前找，找出最后一個門牌號出現的位置

For i = iRow To 2 Step -1

newDoorNumber = cursheet.Cells(i， 1)

If newDoorNumber = genSheetName Then

end_pos = i

Exit For

End If

Next

然后計算出戶數number = end_pos -begin_pos + 1，要拷貝到的目標地址的開始位置為tmplength = number+ start_pos - 1，然后開始復制數據，如果是未合并單元格復制數據，語句如下：

cursheet.Range("J" & begin_pos & "：J" &end_pos).copy genSheet.Range("B" & start_pos& "：B" & tmppos)，代表從 J 列的 begin_pos 行一直到end_pos 行復制到目標表的Bstart_pos 行到tmppos 行，這個對應關系需要扶貧專干根據表格中的列位置進行修改，如果是單元格數據復制到合并單元格中，使用如下語句：

tmplength = start_pos

tmpbegin_pos = begin_pos

For j = 1 To length

cursheet.Range("J" & tmpbegin_pos).copy genSheet.Range("B" & tmplength & "：D" &tmplength) '復制到 C9、D9 的 A2 性別列，C9、D9 是合并單元格

tmpbegin_pos = tmpbegin_pos + 1

tmplength = tmplength + 1

Next

（3）最后兩層循環都結束后，使用Set genSheet = Nothing，Set genBook = Nothing，Set cursheet = Nothing 將工作表和工作簿對象都置為空，程序運行結束。

上述程序可運行于excel 自帶的宏編輯器中，也可嵌入方方格子控件DIY 工具箱中下的收納箱中，運行于內置的宏編輯器中的好處是如果遇特殊情況，出現問題，便于跟蹤處理，缺點是非專業人士不太熟悉這個內置編輯器，因此，一般推薦將程序按要求修改完，復制粘貼到收納箱中，點執行即可。運行界面大致如下圖：

圖1 利用方方格子控件運行VBA程序界面圖

4 系統應用效果

目前該應用程序已推廣到周邊5 個扶貧村，均取得了較為滿意的效果。另外，類似的數據抽取任務，均可使用該程序處理，只要重新配置源表與模板表中字段的列對應關系即可，對excel 基本操作較為熟悉的工作人員，經過短暫培訓，均能靈活的使用該程序處理日常駐村工作中的表格填寫任務，極大地提高了駐村工作隊和扶貧專干人員的工作效率，以幾天甚至十幾天才能完成的工作量，現在不到10 s就可以輕松完成。