前些日子写过一篇文章,名为《利用VBA和INDIRECT 函数实现若干工作表中指定单元格数据的分类汇总》,相关链接是http://www.audit.gov.cn/n6/n41/c86471/content.html,很荣幸被录用。工作闲暇之余,对这项数据归整工作回头看,发现前期把若干电子表格中的指定工作表进行汇总同样可以利用VBA,于是带着这样的思路继续探索实现过程。
数据要求:文件格式为Excel电子表格,其中需要汇总的工作表名为[收支总表],工作表的结构一致。
一、汇总若干电子表格中的指定工作表
1、将所有部门的预算报表都复制到同一个文件夹里面,新建一个空白电子表格,命名为[年度部门预算快速汇总模板],如下图显示
2、打开[年度部门预算快速汇总模板],启动VBA编辑器,新建一个模块命名为【复制汇总】,在代码编辑区输入以下代码:
Option Explicit
Sub 复制汇总()
Dim Sht As Worksheet
Dim Wbk As Workbook
Dim i%
Dim Path_Str As String, Path_Name As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "选择目标文件夹"
If .Show = -1 Then
Path_Str = .SelectedItems(1)
Else
Exit Sub
End If
End With
Path_Name = Dir(Path_Str & "\*.xls*")
Application.ScreenUpdating = False
Do While Path_Name <> ""
If Path_Name <> ThisWorkbook.Name Then
i = i + 1
Set Sht = ThisWorkbook.Worksheets.Add(after:=Sheets(Sheets.Count))
Sht.Name = "收支总表" & i
Set Wbk = Workbooks.Open(Path_Str & "\" & Path_Name)
'ThisWorkbook.Activate
Wbk.Worksheets("收支总表").UsedRange.Copy Sht.Range("A1")
Sht.UsedRange.EntireColumn.AutoFit
Wbk.Close False
Set Sht = Nothing
End If
Path_Name = Dir
Loop
Set Wbk = Nothing
MsgBox "Finish!"
End Sub
该代码的功能是从指定文件夹里读取后缀名为xls或xlsx的文件,并将这些文件中的一张名为[收支总表]的工作表复制到新的汇总表里,命名规则为[收支总表1]、[收支总表2]、[收支总表3]···,效果如下图显示
这样所有单位报送的预算报表里的[收支总表]这张工作表,已经全部复制粘贴到汇总表里了。
二、建立汇总表结构,完成数据引用
1、根据[收支总表]的表结构在[汇总表]里设计新结构样式,并开始第一行数据的引用数据。
利用INDIRECT 函数来完成跨工作表的相对引用,单元格C3的公式修改为INDIRECT("收支总表"&ROW(C1)&"!B6"),表示从[收支总表1]中引用B6单元格的数据。
2、利用拖曳功能完成填充,前提要求是所有[收支总表]的结构完全一致,效果如下图显示
小结:本次案例的实现技术是利用Excel里的VBA加Indirect函数,数据要求是基础表结构要一致,工作表名命名要有规律性,本次设计模板可以在以后的部门预算审计中发挥巨大作用。(刘璐 彭献军)
【关闭】 【打印】 |