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就可以轻松完成。