信息公开目录
局长信箱
咨询投诉
<站内留言
首页 >> 工作动态 >> 信息化建设  
两步实现若干工作表中指定单元格数据的分类汇总
来源:
字体:[  
时间: 2016-09-01

  前些日子写过一篇文章,名为《利用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函数,数据要求是基础表结构要一致,工作表名命名要有规律性,本次设计模板可以在以后的部门预算审计中发挥巨大作用。(刘璐 彭献军(湖北省襄阳市谷城县审计局)

  

相关附件:
  关闭窗口
新余市审计局版权所有 通讯地址:新余市长青北路416号 赣ICP备07500015号
联系电话:0790-6441164 邮箱:xinyusjj@163.com 邮编:338000
建议:电脑显示的分辨率1024*768;IE浏览器6.0以上
Copyright2006-2009 WWW.XYSJJ.GOV.CN All Rights Reserved.
360网站安全检测平台