VBA辅助筛查公车频繁维修疑点
近日在某局领导干部经济责任审计过程中，笔者审查公务用车购置和运行维护费时发现频繁的车辆维修情况，怀疑可能存在违规报销车辆维修费用的问题。为了确保车辆维修费用合理性和合规性，笔者思考使用VBA（Visual Basic for Applications）辅助审计，筛查公务用车中车辆维修频繁等不合理的疑点，帮助审计组发现其中的违纪违规问题。
一、思路探索
参考摘要信息的相对规范性，考虑利用VBA的数组和正则表达式来实现最终分析结果。实现步骤如下：
1.从摘要信息中提取报销车辆维修的车牌号；
2.将提取的车牌号横式显示转换为竖式显示，并与原凭证号、维修金额一一对应；
3.以年、月统计车辆维修次数、维修金额；指定维修频次筛选出频繁维修的车辆疑点，重点核实，验证疑点。
二、技术简介
VBA是Visual Basic的一种宏语言，是微软公司开发，在其桌面应用程序中执行通用的自动化（OLE）任务的编程语言。主要用于扩展Windows的应用程序功能，特别是Microsoft Office软件。由于微软Office软件的普及，人们常见的办公软件Office软件中的Word、Excel、Access、Powerpoint都可以利用VBA编写模块满足特定的需要。例如：通过一段VBA代码，可以实现画面的切换；运用VBA实现复杂逻辑的统计（从多个表中，自动生成按合同号来跟踪生产量、入库量、销售量、库存量的统计清单）等。
三、实现过程
1.通过AO筛选关键字为“维修；车”获取到该局审计期间业务活动费用的相关维修明细账，剔除机关零星维修、电器类维修、车辆保险年审等数据后，部分数据（以下所有截屏数据均为演示数据）如图1（备注：表格原结构A列是会计日期，B列是凭证号，C列是维修金额，D列是摘要。）所示。
图1
2.在表格中按下Alt＋F11进入设计模式，插入模块1，编写语句后保存。语句如下：
Sub 提取车牌（）
Dim arr
Dim x As Integer， h As Integer
Application．ScreenUpdating ＝ False
h ＝ 2
With Sheets（1）
arr ＝ ．Range（＂D1：r＂ ＆ ．Cells（Rows．Count， 1）．End（xlUp）．Row）
For x ＝ 1 To UBound（arr）
For y ＝ 1 To Len（arr（x， 1））
If Mid（arr（x， 1）， y， 1） ＝ ＂鄂＂ Then
arr（x， h） ＝ Mid（arr（x， 1）， y， 7）
h ＝ h ＋ 1
End If
Next
h ＝ 2
Next
．Range（＂D1＂）．Resize（UBound（arr）， UBound（arr， 2）） ＝ arr
．Range（＂e1：g1＂） ＝ Array（＂提取车牌号1＂， ＂提取车牌号2＂， ＂提取车牌号3＂）
End With
MsgBox ＂从摘要中提取车牌已完成！＂
Application．ScreenUpdating ＝ True
End Sub
3.返回正常模式后添加按钮［提取车牌］，指定宏“提取车牌”，运行结果如图2所示：
图2
4.在模块1中继续编写转置的语句如下：
Sub 转置（）
Dim I1 As New Dictionary
Dim arr， crr（1 To 10， 1 To 2）
Dim x As Integer， k As Integer
Application．ScreenUpdating ＝ False
k ＝ 1
With Sheets（＂明细表＂）
arr ＝ ．Range（＂A2：H＂ ＆ ．Cells（Rows．Count， 2）．End（xlUp）．Row）
．Range（＂H2：H5000＂）．ClearContents
For x ＝ 1 To UBound（arr）
I1（arr（x， 1） ＆ ＂｜＂ ＆ arr（x， 2）） ＝ I1（arr（x， 1） ＆ ＂｜＂ ＆ arr（x， 2）） ＋ 1
For y ＝ 1 To Len（arr（x， 4））
If Mid（arr（x， 4）， y， 1） ＝ ＂鄂＂ Then
crr（k， 1） ＝ Mid（arr（x， 4）， y， 7）
k ＝ k ＋ 1
End If
Next
arr（x， UBound（arr， 2）） ＝ crr（I1（arr（x， 1） ＆ ＂｜＂ ＆ arr（x， 2））， 1）
k ＝ 1
Erase crr
Next
．Range（＂h1＂） ＝ Array（＂提取车牌转置＂）
．Range（＂A2＂）．Resize（UBound（arr）， UBound（arr， 2）） ＝ arr
End With
MsgBox ＂横式车牌已完成转置！＂
Application．ScreenUpdating ＝ True
End Sub
5.返回正常模式后添加按钮［车牌转置］，指定宏“转置”，运行结果如图3所示：
图3
6.至此，车牌提取已完成，并与凭证一一对应，结果如图4所示（为直观显示，此处隐藏了E：G列）：
图4
7.在表格中插入新工作簿，命名为“分析表”。进入设计模式，插入模块2，语句如下：
Sub 统计（）
Dim d1 As New Dictionary
Dim arr， crr（1 To 1000， 1 To 5）
Dim x As Integer， k As Integer
Application．ScreenUpdating ＝ False
k ＝ 1
With Sheets（＂明细表＂）
arr ＝ ．Range（＂a2：h＂ ＆ ．Cells（Rows．Count， 2）．End（xlUp）．Row）
For x ＝ 1 To UBound（arr）
If Not d1．Exists（Year（arr（x， 1）） ＆ ＂｜＂ ＆ Month（arr（x， 1）） ＆ ＂｜＂ ＆ arr（x， 8）） Then
d1．Add Year（arr（x， 1）） ＆ ＂｜＂ ＆ Month（arr（x， 1）） ＆ ＂｜＂ ＆ arr（x， 8）， k
crr（k， 1） ＝ Year（arr（x， 1））： crr（k， 2） ＝ Month（arr（x， 1））： crr（k， 3） ＝ arr（x， 8）： crr（k， 4） ＝ arr（x， 3）： crr（k， 5） ＝ 1
k ＝ k ＋ 1
Else
crr（d1（Year（arr（x， 1）） ＆ ＂｜＂ ＆ Month（arr（x， 1）） ＆ ＂｜＂ ＆ arr（x， 8））， 4） ＝ crr（d1（Year（arr（x， 1）） ＆ ＂｜＂ ＆ Month（arr（x， 1）） ＆ ＂｜＂ ＆ arr（x， 8））， 4） ＋ arr（x， 3）
crr（d1（Year（arr（x， 1）） ＆ ＂｜＂ ＆ Month（arr（x， 1）） ＆ ＂｜＂ ＆ arr（x， 8））， 5） ＝ crr（d1（Year（arr（x， 1）） ＆ ＂｜＂ ＆ Month（arr（x， 1）） ＆ ＂｜＂ ＆ arr（x， 8））， 5） ＋ 1
End If
Next
End With
With Sheets（＂分析表＂）
．UsedRange．ClearContents
．Range（＂a1：e1＂） ＝ Array（＂年份＂， ＂月份＂， ＂车牌号码＂， ＂报销金额＂， ＂报销次数＂）
．Range（＂a2＂）．Resize（UBound（crr）， UBound（crr， 2）） ＝ crr
End With
MsgBox ＂数据统计完毕！＂
Application．ScreenUpdating ＝ True
End Sub
8.返回正常模式，插入按钮［数据统计］，指定宏“统计”，运行结果如图5所示：
图5
9.进入设计模式，在模块2中编写分析语句，如下：
Sub 频次分析（）
Dim pl As Integer
Dim arr， crr（1 To 1000， 1 To 5）
Dim x As Integer， k As Integer
Application．ScreenUpdating ＝ False
k ＝ 1
pl ＝ Application．InputBox（＂请录入频率次数筛选条件！＂）
With Sheets（＂分析表＂）
arr ＝ ．Range（＂a2：e＂ ＆ ．Cells（Rows．Count， 1）．End（xlUp）．Row）
For x ＝ 1 To UBound（arr）
If arr（x， 5） ＞＝ pl Then
For y ＝ 1 To UBound（arr， 2）
crr（k， y） ＝ arr（x， y）
Next
k ＝ k ＋ 1
End If
Next
．Range（＂l1：p1000＂）．ClearContents
．Range（＂l1：p1＂） ＝ Array（＂年份＂， ＂月份＂， ＂车牌号码＂， ＂报销金额＂， ＂报销次数＂）
．Range（＂l2＂）．Resize（UBound（crr）， UBound（crr， 2）） ＝ crr
End With
MsgBox ＂筛选次数大于等于＂ ＆ pl ＆ ＂次的内容已经显示完毕！＂
Application．ScreenUpdating ＝ True
End Sub
10.返回正常模式，插入按钮“频次分析”，指定宏“频次分析”，输入频次条件“2”，运行结果如图6所示：
图6
至此，单月多次报销维修费的疑点结果已成功筛选出来。本次实例利用VBA正则表达式加数组快速从单元格中检索提取所需字符串的功能完美实现，既提高工作效率，同时又保证了工作精度。并且，根据关键内容中所包含规范的部分关键字同样适用于审计现场的其它疑点核查，具备一定的通用性，只需修改VBA代码中的关键字即可。
四、审计成果
通过VBA辅助审查，本次审计发现了2台修理频度偏高的公车，笔者整理了相对应的维修清单，经问询当地汽车维修行业的专业人士，查实了某台公车虚列维修项目骗取财政资金的问题，并移交相关部门。被审单位举一反三开展自查，并积极落实审计整改，建立完善了相应的公车管理制度。利用VBA辅助审计，不仅提升了在“三公”经费庞大数据中快速筛选问题的效率，还能精准发现被审计单位公务用车管理和监督中的漏洞，促进被审计单位积极采取措施，保障公务用车管理规范化、制度化。
注：文章内容属作者个人观点，不代表本网站立场。
