合并计算多个工作表的数据（Consolidate）

liulang0808 · 发表于 2016-1-2 10:17

声明：本帖内容来源于EH论坛编著的《EXCEL 2007 VBA实战技巧精粹》技巧94

分享原因：
1、本人不会这个方法，之前基本上用字典处理
2、论坛内对此类的汇总需求不少

Option Explicit

SubConsolidateSheets()

Dim Arr(1 To 3) As String, i AsInteger

Dim rng As Range

For i = 1 To 3

Arr(i) = Sheets(i).Name & "!"& _

Sheets(i).Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)

With rng

.Consolidate Sources:=Arr, Function:=xlSum, _

TopRow:=True,LeftColumn:=True

.Value = "业务员"’ Consolidate同时基于行列汇总，将忽略左上角的内容

End With

Sheets(1).Range("A1").Copy

With rng

.Resize(1, .CurrentRegion.Columns.Count) _

.PasteSpecial xlPasteFormats

End With

Sheets(1).Range("A2").Copy

With rng.CurrentRegion

.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _

.PasteSpecial xlPasteFormats

End With

Application.CutCopyMode = False

rng.Select

Set rng = Nothing

EndSub

以下是本人增加的解释说明：

Range.Consolidate(Sources, Function,TopRow, LeftColumn, CreateLinks)

名称	必选/可选	数据类型	描述
Sources	可选	Variant	以文本引用字符串数组的形式给出合并计算的源，该数组采用 R1C1-样式表示法。这些引用必须包含将要合并计算的工作表的完整路径。
Function	可选	Variant	XlConsolidationFunction 常量之一，用于指定合并计算的类型。
TopRow	可选	Variant	如果为 True，则基于合并计算区域中首行内的列标题对数据进行合并。如果为 False，则按位置进行合并计算。默认值为 False。
LeftColumn	可选	Variant	如果为 True 则基于合并计算区域中左列内的行标题对数据进行合并计算。如果为 False，则按位置进行合并计算。默认值为 False。
CreateLinks	可选	Variant	如果为 True，则让合并计算使用工作表链接。如果为 False，则让合并计算复制数据。默认值为 False。

XlConsolidationFunction 参数说明

名称	值	描述
xlAverage	-4106	平均。
xlCount	-4112	计数。
xlCountNums	-4113	只计数数值。
xlMax	-4136	最大值。
xlMin	-4139	最小值。
xlProduct	-4149	乘。
xlStDev	-4155	基于样本的标准偏差。
xlStDevP	-4156	基于全体数据的标准偏差。
xlSum	-4157	总计。
xlUnknown	1000	未指定任何分类汇总函数。
xlVar	-4164	基于样本的方差。
xlVarP	-4165	基于全体数据的方差。

Range.Address(RowAbsolute, ColumnAbsolute,ReferenceStyle, External, RelativeTo)

名称	必选/可选	数据类型	描述
RowAbsolute	可选	Variant	如果为 True，则以绝对引用返回引用的行部分。默认值为 True。
ColumnAbsolute	可选	Variant	如果为 True，则以绝对引用返回引用的列部分。默认值为 True。
ReferenceStyle	可选	XlReferenceStyle	引用样式。默认值为 xlA1。
External	可选	Variant	如果为 True，则返回外部引用。如果为 False，则返回本地引用。默认值为 False。
RelativeTo	可选	Variant	如果 RowAbsolute 和 ColumnAbsolute 为 False，并且 ReferenceStyle 为 xlR1C1，则必须包括相对引用的起始点。此参数是定义起始点的 Range 对象。

XlReferenceStyle 参数说明

名称	值	描述
xlA1	1	默认值。使用 xlA1 返回 A1 样式的引用。
xlR1C1	-4150	使用 xlR1C1 返回 R1C1 样式的引用。

amzxfgh9632 · 发表于 2016-1-2 10:51

谢谢分享，收藏了，不错的帖子

wangil · 发表于 2016-1-2 10:55

谢谢分享

chengshanming · 发表于 2016-1-3 00:06

用函数做的分享，第一列姓名排列顺序要一致，表格可以无限制添加，但位置稍注意下。

pigyoung · 发表于 2016-5-31 16:01

楼主你好，我看的是VBA实战技巧2010那本书，里边的技巧90讲的consolidate函数。
里边的第三行在定义需要进行合并的数据区域arr的时候是将所有的sheet的名字都列出来了，这样使用很不方便，想请教下您这个有办法解决么，不然我每次更新数据源都可能需要更新代码中的这个范围。
盼复，谢谢！
附：代码如下：
Sub consolidatedemo()
Dim arr As Variant
arr = Array("Report!R1C1:R40C90", "Report (2)!R1C1:R40C90", "Report (3)!R1C1:R40C90")
With Worksheets("Sheet1").Range("A1")
.Consolidate sources:=arr, Function:=xlSum, toprow:=True, leftcolumn:=True
.Value = "行别"
End With
End Sub