|
Sub 汇总2()
Dim arr, brr, crr
Set d = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
Set d3 = CreateObject("scripting.dictionary")
arr = Sheets("数据源").Range("a1").CurrentRegion
ReDim drr(1 To UBound(arr), 1 To 8)
Rows("2:1000").Clear
For i = 2 To UBound(arr)
zf1 = arr(i, 1)
zf2 = arr(i, 1) & "," & arr(i, 5)
zf3 = arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 3) & "," & arr(i, 5) & "," & arr(i, 6) & "," & arr(i, 7)
d(zf1) = d(zf1) + 1
d2(zf2) = d2(zf2) + arr(i, 4)
d3(zf3) = d3(zf3) + arr(i, 4)
Next i
brr = d3.keys
crr = d2.keys
For Each k In d.keys
For i = 0 To UBound(brr)
If Split(brr(i), ",")(0) = k Then
n = n + 1
drr(n, 1) = Split(brr(i), ",")(0)
drr(n, 2) = Split(brr(i), ",")(1)
drr(n, 3) = Split(brr(i), ",")(2)
drr(n, 4) = d3(brr(i))
drr(n, 5) = Split(brr(i), ",")(3)
drr(n, 6) = Split(brr(i), ",")(4)
drr(n, 7) = Split(brr(i), ",")(5)
End If
Next i
For i = 0 To UBound(crr)
If Split(crr(i), ",")(0) = k Then
dw = Split(crr(i), ",")(1)
s = s & d2(crr(i)) & dw & "+"
End If
Next i
r = Sheets("目标结果").Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("目标结果").Cells(r, 1).Resize(n, 8) = drr
Cells(r, 8) = Mid(s, 1, Len(s) - 1)
Cells(r, 8).Resize(n).Merge
s = ""
n = 0
Next k
End Sub
|
|