excel中实现数据自动汇总到汇总表的常用办法是vba汇总，高效快速。

操作方法如下：

打开excel，按住alt+F11打开VBA编辑器：

新建一个模块，贴入以下代码：

Sub 汇总()

Dim d As Object

Dim Arr As Variant

Arr =Worksheets("数据源")Range("A2:B" & Worksheets("数据源")Range("B"& RowsCount)End(xlUp)Row)

Dim i As Long

Set d =CreateObject("Scriptingdictionary")

For i = 1 ToUBound(Arr)

d(Arr(i, 1)) =d(Arr(i, 1)) + Arr(i, 2)

Next i

Range("A1:B1") = Array("产品编码", "数量")

Range("A2")Resize(dCount) =ApplicationWorksheetFunctionTranspose(dkeys)

Range("B2")Resize(dCount) =ApplicationWorksheetFunctionTranspose(ditems)

With Range("a1")CurrentRegion

BordersLineStyle = xlContinuous

HorizontalAlignment = xlCenter

VerticalAlignment = xlCenter

End With

End Sub

3指定执行的宏：

复制粘贴可累死人，如果人数较多，需要使用VBA来汇总，人数少可以同时打开所有文件，使用公式汇总，但是都有一个要求，所有表格必须格式严格一致，每个栏目的单元格位置必须固定。

可以百度云上分享文件，帮你做好。

Sub GetData()

Dim arr, brr(), buf(), rlt()

Dim i As Long, j As Long, k As Long, m As Long, n As Long, x As Long, y As Long

arr = Sheets("Sheet2")Range("a2:e13")

ReDim brr(LBound(arr, 2) To UBound(arr, 2), LBound(arr) To UBound(arr))

For i = LBound(arr) To UBound(arr)

If arr(i, 2) = Sheets("Sheet1")Range("b2") Then

For j = LBound(arr, 2) To UBound(arr, 2)

brr(j, LBound(arr) + k) = arr(i, j)

Next j

k = k + 1

End If

Next i

ReDim buf(LBound(brr, 2) To UBound(brr, 2), LBound(brr) To UBound(brr))

For i = LBound(brr) To UBound(brr)

For j = LBound(brr, 2) To UBound(brr, 2)

buf(j, i) = brr(i, j)

Next j

Next i

m = LBound(buf)

n = LBound(buf) + 6 - 1 '最前6行

x = LBound(buf, 2) + 3 - 1

y = UBound(buf, 2)

ReDim rlt(m To n, x To y)

For i = m To n

For j = x To y

rlt(i, j) = buf(i, j)

Next

Next

Sheets("Sheet1")Range("b5:d10")ClearContents

Sheets("Sheet1")Range("b5:d10") = rlt

End Sub

运行实测图：