客户服务

您在浏览我们网站时,如有什么问题,可以直接拨打我们下方的电话或在意见箱留言!

当前位置: 首页 - 审计园地 - 参考资料

参考资料

VBA在计划台账工作中的运用

发布时间:2019/09/20 14:09信息来源:武汉市审计局官网

江汉区审计局 史沄涛

将审计计划管理系统里填报的各项目的批量指标进行汇总,是一项日常而琐碎的事情。常规办法是依次打开各项目的批量指标文件,然后分别复制,再手工粘贴到一个新的Excel工作簿,以此来进行汇总。对于个别项目进行上述这样的操作还算方便,但如果面对几十个乃至上百个项目,虽然通过上述方法也可以实现,但耗时较长,还难免会出现错粘、漏粘,可能导致最终的数据提供不准确。

面对这种情况,借助ExcelVBA的强大功能,可以帮助人员高效、准确地完成汇总工作,方便随时向各部门提供所需数据。

一、所需数据

审计计划管理系统导出的若干项目批量指标表
二、操作思路流程图


 

三、具体操作步骤

(一)按规范名称新建表文件

(1)在“批量指标标准表”中,输入项目名称及类型,并将文件名规范为“批量指标-序号.xls”形式,如下图;

 

注:“规范命名”下列单元格使用公式自动填冲,如:E2=B2&"-"&A2&".xls"

(1)创建“宏”,编写VBA语句,如下:

Sub 批量指标标准表()

Dim s As String, i As Integer, file As String, rownum As Integer

If Workbooks.Count > 1 Then

    MsgBox "请关掉除宏工作簿之外的其他所有excel文件"

Else

Application.DisplayAlerts = False

For i = 2 To Workbooks(1).Sheets(1).Range("e1").CurrentRegion.Rows.Count

        s = Workbooks(1).Sheets(1).Range("e" & i).Value 

        file = Workbooks(1).path & "\" & s

        If Dir(file) = "" Then

            Workbooks.Add

            ActiveWorkbook.SaveAs file

        End If

    Next

End If

Application.DisplayAlerts = True '打开系统提示

End Sub

(2)点击执行按钮,实现按规范名称自动创建表文件。

(二)从计划台账系统导出对应批量指标数据

每月填报台账后,可将项目的批量指标及时导出,为后续汇总作准备。导出批量指标时,即可另存到上述规范命名的标准表文件中,如下图:

 

(三)对各个台账表格进行初加工

打开标准命名的各项批量指标文件,在首列插入一列,标题命名为“项目”,并将下方单元格填冲为对应的项目名称,方便后续汇总时明确各项数据的来源,如图:

 

(四)一键生成汇总计划台账

(1)创建一个“合并.xlsm”的文件。

(2)新建宏,用于汇总各项批量指标,并输入以下指令,如下:

Sub 合并()

Dim i As Integer, n As Integer, s As String, rownum As Integer, sum As Integer

n = 4

sum = 2

For i = 1 To n

    s = "\批量指标-" & i & ".xls"

    Workbooks.Open ThisWorkbook.Path & s

    rownum = Workbooks(2).Sheets(1).Range("a1").CurrentRegion.Rows.Count 

    If i = 1 Then Workbooks(2).Sheets(1).Range("a1").Resize(1, 18).Copy Workbooks(1).Sheets(1).Range("a1").Resize(1, 18)

       Workbooks(2).Sheets(1).Range("a2").Resize(rownum - 2, 18).Copy Workbooks(1).Sheets(1).Range("a" & sum).Resize(rownum - 2, 18)  

    sum = sum + rownum - 1

    Workbooks(2).Close

Next

Application.DisplayAlerts = False

Application.DisplayAlerts = True

End Sub

注:指令中的“n”,为需要汇总的项目批量指标文件个数,需根据具体情况进行修改。

3)点击执行按钮,实现各批量指标自动汇总,如下图:

 

(五)对汇总台账进行格式调整


分享到: