時亞南，郭鳳娟

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

1 應(yīng)用背景及意義

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

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

2 技術(shù)選型

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

3 程序?qū)崿F(xiàn)

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

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

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

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

For i = 2 To iRow

newDoorNumber = cursheet.Cells(i， 1)

If newDoorNumber = genSheetName Then

begin_pos = i

Exit For

End If

Next

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

For i = iRow To 2 Step -1

newDoorNumber = cursheet.Cells(i， 1)

If newDoorNumber = genSheetName Then

end_pos = i

Exit For

End If

Next

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

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

tmplength = start_pos

tmpbegin_pos = begin_pos

For j = 1 To length

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

tmpbegin_pos = tmpbegin_pos + 1

tmplength = tmplength + 1

Next

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

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

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

4 系統(tǒng)應(yīng)用效果

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