365betvip

审计之窗

您当前所在位置:首页 > 审计之窗 > 审计论坛

浅析VBA在多表格汇总中的运用

发布时间:2019-04-02来源:365betvip_365bet澳洲账号_365bet体育在线官网阅读量:


项目审计过程中,大数据审计的运用越来越广泛。如何快速对多张表格汇总形成有效的审计结果,笔者结合实践,就大量的表格与条目利用VBA进行批量汇总做些交流,供同仁参考。

一、分析表的结构,确定思路

审计过程中,需统计多张疑点表的疑点个数和疑点金额占比。明细表中包含以下字段:疑点表名称、疑点表条数、疑点金额、核“是”疑点条数、核“是”疑点金额。打开疑点表分析字段位置、字段数据类型、字段间的结构,发现疑点表名称可以通过VBA功能,利用DIR读取文件名;疑点条数利用rows.count-1或者function.count皆可;疑点金额利用function.sum实现;核“是”条数用function.countif实现;核“是”金额可用function.sumif或者function.sum实现。

 

1  待统计的疑点表

二、清理疑点表格式,保证表结构相同

为了降低VBA代码的复杂,减少判定语句的使用,要求各审计小组核实完毕的疑点表必须与下发疑点表相同的表结构,主要是保证无额外添加的列与行。统计明细前也要对所有表格进行检查

一是检查疑点表的个数是否为1,可以利用worksheets.count对每个工作簿中sheet个数进行计数;二是检查疑点表中列数,由于所有疑点表的字段数一致,可以利用columns.count查看所有表的字段数是否一致;三是核对下发疑点表中行数与核查后疑点表中行数是否一致,由于上级审计机关下发疑点表时会附带一张所有疑点表的统计情况表,利用rows.count对下发疑点表的行进行计数并与疑点表的统计情况表进行行数比对。

三、编写VBA命令,实现审计构想

打开Excel 2016进入宏开发界面,编写代码如下:

Sub 核实疑点汇总表()

Application.ScreenUpdating = False

Dim i

filename = Dir(ThisWorkbook.Path & "\*.xls")‘将核实疑点汇总表放在与需要进行汇总疑点表相同的目录中,如不放在一起则要将ThisWorkbook.Path 替换为相应目录

i = 1

Do While filename <> ""  ‘利用循环对疑点表进行操作

     i = i + 1

     Range("A" & i) = filename

     Workbooks.Open (ThisWorkbook.Path & "\" & filename)

     Range("B"&i)= ActiveWorkbook.Worksheets(1).Range("a1").CurrentRegion.Rows.Count - 1   ‘统计每张表的疑点数

     Range("c"&i)= Application.WorksheetFunction.Sum(ActiveWorkbook.Worksheets(1).[E2:E740])‘统计疑点金额

     Range("d"&i)= Application.WorksheetFunction.CountIf(ActiveWorkbook.Worksheets(1).[B2:B740], "是")‘统计核是疑点数

     Range("e"&i)= Application.WorksheetFunction.Sum(ActiveWorkbook.Worksheets(1).[F2:F740])‘统计核是疑点金额

     ActiveWorkbook.Close True

     filename = Dir

Loop

MsgBox ("完成统计")

End Sub

运行结果如下:

 

图 2 核实疑点汇总表

为降低代码复杂度,笔者在14、17、20行中利用表格行数据,直接限定了统计区域。单独运行以下代码:

Sub 疑点数()

Dim i

filename = Dir(ThisWorkbook.Path & "\*.xls")

i = 1

Do While filename <> ""

     i = i + 1

     Range("A" & i) = filename

     Workbooks.Open (ThisWorkbook.Path & "\" & filename)

     Range("B"&i)= ActiveWorkbook.Worksheets(1).Range("a1").CurrentRegion.Rows.Count - 1

Loop

End Sub

可以得到所有疑点表的行数,取最大值739行,则在求和计数中直接对所有疑点表中B2:B740区域进行求和或计数。运行结果为图2中A、B列所示。

四、疑点表逻辑性验证

一是验证表名,利用vlookup函数将核实疑点表汇总表中“表名”列与下发疑点汇总表中“表名”列进行比对,结果为N/A的即为表名不一致,应进行核对更改;二是利用vlookup函数将核实疑点表汇总表中“行数”列与下发疑点汇总表中“行数”列进行比对,结果为N/A的即为行数不一致,应进行核对更改;三是核“是”条数应小于疑点条数,利用公式if(核是条数<疑点条数,””,有问题),运行公式后出现“有问题”的行即为问题行;四是核“是”条数为0的行对应的核“是”金额应当也为0,利用筛选功能筛选出核“是”条数为0的行,再筛选出核“是”金额不为0的行即可。五是核“是”金额应小于疑点金额,利用公式if(核是金额<疑点金额,””,有问题),运行公式后出现“有问题”的行即为问题行。(刘余键)


 
友情链接:审计署|湖北省审计厅|湖北省政府网|荆州市政府网
版权所有:365betvip_365bet澳洲账号_365bet体育在线官网
地址:荆州市沙市区太岳路 邮编:434000 电话:0716-8525809
鄂ICP备05001388号 鄂公网安备42100202000010号 网站标识号 4210000031 网站地图