|
icenotcool 发表于 2015-12-23 16:00
解释:A1:B22是已经提供的,D2:O22是自动从每个表的R列对应复制过来的,然后C列是根据D2:O22相加得出来的 ...
Sub test()
Dim d, A, k, i, j, m
Set d = CreateObject("scripting.dictionary")
Call test2
'1)统计
For k = 2 To Sheets.Count
With Sheets(k)
m = "|" & .Name & "|"
A = .Range("a1:r" & .Range("a65536").End(3).Row)
End With
For i = 2 To UBound(A)
If Not d.exists(A(i, 1)) Then Set d(A(i, 1)) = CreateObject("scripting.dictionary")
For j = 3 To UBound(A, 2) - 1
d(A(i, 1))(A(i, 1)) = A(i, 2) '姓名
If VBA.IsNumeric(A(i, j)) Then d(A(i, 1))(m) = d(A(i, 1))(m) + A(i, j) '合计
Next j
Next i
Next k
'2)查询
A = Sheets(1).Range("a1").CurrentRegion
For i = 2 To UBound(A)
A(i, 2) = d(A(i, 1))(A(i, 1))
k = 0
For j = 4 To UBound(A, 2)
A(i, j) = d(A(i, 1))(A(1, j))
k = k + A(i, j)
Next j
A(i, 3) = k
Next i
'3)输出
Sheets(1).Range("a1").Resize(i - 1, UBound(A, 2)) = A
End Sub
Sub test2()
Dim n, A, i
n = Sheets.Count
ReDim A(1 To 1, 1 To n - 1)
For i = 2 To n
A(1, i - 1) = "|" & Sheets(i).Name & "|"
Next i
Sheets(1).Range("d1").Resize(1, UBound(A, 2)) = A
End Sub
例子修改2.rar
(116.96 KB, 下载次数: 10)
|
|