您的位置: 首页 > EXCEL技巧 > ExcelVBA >

VBA对多个工作簿进行合并计算(求和)一例

时间:2013-12-13 整理:docExcel.net

Excel中的合并计算可以对多个工作表的对应项目进行求和、求平均值等计算,但如果需要合并计算的工作表较多,特别是这些工作表位于不同的工作簿内时,逐一选择数据源显得较为繁琐。用VBA中的Range.Consolidate方法可以快速地对多个结构相似的工作表进行合并计算,但如果表格内包含有非数值类型的数据列,合并计算会忽略这些列。例如下图为某个图书销售点1至12月的图书销售记录,销售数量位于D至O列,其中B列和C列为与A列对应的数据,无需参与合并计算,但必须在汇总表中列出。各销售点都有一个类似的销售表格,每个分表列出的图书数量不等,图书名称也不尽相同。现在需要对各销售点的销售表格中D至O列的销售数量按照A列图书名称进行合计,求出总的销售数量。

如果直接使用合并计算,Excel会忽略B列文本,同时对C列(单价)也进行合并计算,显然不符合要求。这时使用VBA中的Dictionary对象,可以解决这一问题,代码如下:

Sub SumWorkbooks()
Dim ThePath As String, TheFile As String
Dim d As Object, Wbk As Workbook
Dim i As Integer, j As Integer, k As Integer
Dim Arr1(11), Arr2(), Arr3(), dk

On Error Resume Next
Application.ScreenUpdating = False
Set d = CreateObject("scripting.dictionary")
ThePath = ThisWorkbook.Path & "\"
TheFile = Dir(ThePath & "*.xls")
 
  Do While TheFile <> ""
   If TheFile <> ThisWorkbook.Name Then
Set Wbk = GetObject(ThePath & TheFile)
With Wbk.Worksheets(1)
  For i = 2 To .Range("A65536").End(xlUp).Row
   '将D至O列数值赋值给Arr1
  For j = 0 To 11
   Arr1(j) = .Cells(i, j + 4).Value
Next j
  If Not d.exists(.Range("A" & i).Value) Then
   'key对应一个数组
   d.Add .Range("A" & i).Value, Arr1
  '将不能求和的数据赋值给Arr2
   ReDim Preserve Arr2(1 To 2, 1 To k + 1)
   For j = 1 To 2
   Arr2(j, k + 1) = .Cells(i, j + 1)
   Next j
   k = k + 1
  Else
   For j = 0 To 11
   '若数据存在则D至O列数值对应合计到Arr1中的每个元素
  Arr1(j) = d(.Range("A" & i).Value)(j) + Arr1(j)
   Next
   d(.Range("A" & i).Value) = Arr1
  End If
  Next
End With
Wbk.Close False
   End If
   TheFile = Dir   '当前文件夹内的下一个工作簿
  Loop

  '输出
  With ThisWorkbook.Worksheets(1)
.Range("A2").Resize(d.Count, 1) = Application.Transpose(d.keys)
dk = d.keys
ReDim Arr3(1 To d.Count, 1 To 12)
For i = 0 To d.Count - 1
  For j = 0 To 11
   Arr3(i + 1, j + 1) = d(dk(i))(j)
  Next j
   Next i
   .Range("D2:O" & d.Count + 1).Value = Arr3
   .Range("B2:C" & d.Count + 1).Value = Application.Transpose(Arr2)
  End With
 
Set d = Nothing
Application.ScreenUpdating = True
  
End Sub

在汇总表中按Alt+F11,打开VBA编辑器,单击菜单“插入→模块”,粘贴上述代码并运行,即可对汇总工作簿所在的文件夹内的其他所有工作簿的第一个工作表进行合并求和,无需打开各个需要汇总的工作簿。汇总后的B、C两列为与A列对应的数据。汇总前须注意以下几点:

1.将汇总工作簿和其他各个工作簿放到同一文件夹内,并保存汇总工作簿。汇总前移走文件夹内所有无关工作簿。

2.各分表应位于各工作簿中的最左侧(第一个)。

3.各分表内的记录数量可以不同,但行标题需相同。

单击 这里 打开示例文件的下载页面。

合并单元格怎么合并数据而不是求和 问题:合并单元格怎么合并数据而不是求和
回答:合并只是格式上的合并,而内容只会保留所选区域的第一个单元格内的内容。你的数据如果是字符串(文本)形式的,建议用CONCATENATE函数来合并
如何在Excel2007中使用合并计算功能 问题:如何在Excel2007中使用合并计算功能
回答:...框中,单击“函数”下面的下拉三角形,选择“求和”函数。 ③ 单击“引用位置”按钮,折叠对话框。 ④ 切换到第一张工作表,选择所需合并内容的整个数据区域之后再次单击折叠按钮。 ⑤ 此时又返...
在excel中合并计算中为什么没有求差 问题:...有求差,如图
回答:可通过将被 减数表的数据乘以负一进行求和,如图:在空白单元格输入-1--》复制该单元格,选中被减数区域--》右击--》”选择性粘贴“--》”乘“,如图:--》”确定“,再合并计算求和,如图:--》”确定“...
多个excell文件对应数据求和 问题:多个excell文件对应数据求和
回答:加就可以了,比如在一个新表中都3个文件A.xls、B.xls和C.xls的M列求和就在M列第一个单元格内输入公式:=[a.xls]sheet1!M1+[b.xls]sheet1!M1+[b.xls]sheet1!M1然后往下拉就可以了。
快速对多个工作表中的同一单元格求和 问题:快速对多个工作表中的同一单元格求和
回答:...表,我们要对其中部分工作表中的同一单元格的数据进行求和,例如要在“总计”工作表的A1单元格中对“Sheet1”、“Sheet3”、“Sheet4”、“Sheet7”四个工作表中的A1单元格进行求和,可以用下面的方法: 1.如果要求和的工作表...
如何在多个表格中相同产量求和 问题:如何在多个表格中相同产量求和
回答:=SUM(Sheet1:Sheet3!A4)这样就能把Sheet1到Sheet3中的各表的A4相加了.注意以下例子:sheet1 A4=1sheet2 A4=2sheet6 A4=4sheet3 A4=3SUM(Sheet1:Sheet3!A4)=10
相关知识:

下面内容对您也许有用

      话题:在excel2007中利用VBA将多个工作簿的第一个工作表合并到一个
      问:应工作需要,我需要将100多个工作薄中的第一个工作表合并到一个工作薄的多个工作
      答:我用07excel试验了,这段代码是没有问题的,建议重新新建一个excel运行上述代码,运行时关闭其他excel工作簿,如果还是不行,换台电脑试试,如果还是不行,你 excel2007中,关于合并相同格式 EXCEL2007 多工作合并
      话题:excel2007中,关于合并相同格式的多个工作簿,行数不一样,
      excel2007中,关于合并相同格式的多个工作簿,行数不一样,怎么合并。可以用VBA1。所有的工作薄是不是在同
      话题:求一个VBA,EXCEL中,如何将多个工作簿合并成一个工作表?
      求一个VBA,EXCEL中,如何将多个工作簿合并成一个工作表?Sub 合并当前目录下所有工作簿的全部工作表()Dim
      话题:如何用EXCEL中的VBA合并同一资料夹中,多个工作簿的同一个工作
      答:说千字,不如一附件 这里有有我以前做的合并文件夹下所有工作簿(包含子目录)到当前工作表的一个程序 楼主可以参考学习下.也可以把问题作为内容(邮件主题一定要 求一个VBA,EXCEL中,如何将多个 Excel工作簿VBA怎么隐藏工作表
      话题:为什么EXCEL中我用VBA合并同一工作簿的工作表,有的工作表合并
      问:我有一个工作簿,里面的30个工作表,我想把所有工作表里的内容复制到一张空的
      答:你再试着这个步骤操作一下看看:1.将需合并的excel文件放在同一个文件中,并这个文件中新建一个excel文件 2.打开新建的excel文件,按alt+f11建,打开宏,新建 在excel中怎样利用vba合并几个有 excel vba 合并各个工作表,到
      话题:Excel 2007 合并计算VBA代码编写
      问:在“发票”工作表选定的单元格中,对当前激活工作簿中的“月份(9)”工作表进行
      答:确定很抽象,不好理解
      话题:为何用VBA合并EXCEL工作簿,出现空表,谢谢大家!知道
      问:上面图片是我用VBA生成的,红色认定表是我要的,SHEET表不是我想要的
      答:应该是VBA语句有问题,你最好检查一下语句 求一个VBA,EXCEL中,如何将多个 在excel中怎样利用vba合并几个有
      话题:同一个文件夹里有N多个工作簿合并汇总问题,求高手解答(需要
      问:同一个文件夹里有N多个工作簿,所有工作簿里的工作表格式都是一致的,我现在想把
      答:一般我都是转成VFP的dbf文件,用程序操作一下,再转出excel的,完全用程序操作也可以的 如何用VBA合并同文件夹中的格式 VBA:一个文件下的多个工作
      话题:EXCEL高手进!如何将多个工作簿变成一个工作簿里的工作表呢?
      问:也就是原来的工作簿1,变成新的工作簿里面的sheet1,有没有宏之类的方法。一个
      答:这里有三种,看下哪种更适合你。有许多实现Excel工作簿合并的方法,在《将多个工作簿中的数据合并到一个工作簿》中介绍过合并工作簿的示例。下面再列举几个示例 excel中如何同时显示多张工作表 EXCEL工作簿多个工作表如何另
      话题:如何用VBA实现多工作簿数据求和及复制
      问:假设一个文件夹中有A,B,C 三个工作薄,每个工作薄中都有一张相同格式的工作表。
      答:求和没问题,汇总也没有问题,不难,有问题而且难的是你没说清楚啊。求和是对哪一列进行求和,汇总是以哪个字段进行汇总。可以Q我细说,40194204 如何复制数据到另一工作表的vba 用excel VBA怎么把多个工作表里
最新评论