|
- Sub 按条件汇总()
- Dim arr, i&, j&, x, d, brr, n&, p&, Tj, crr, d1, a, drr
- Tj = Sheets(1).[b1].Value & Sheets(1).[e1].Value '指定条件
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- arr = Sheets(2).[a1].CurrentRegion
- ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
- For i = 2 To UBound(arr)
- x = arr(i, 1) & arr(i, 2)
- If x = Tj Then
- n = n + 1
- For j = 1 To UBound(arr, 2)
- brr(n, j) = arr(i, j)
- Next
- End If
- Next
- n = 0
- ReDim crr(1 To UBound(arr), 1 To 5)
- For i = 1 To UBound(brr)
- x = brr(i, 3) & brr(i, 4) & brr(i, 5)
- If Len(x) Then
- If Not d.exists(x) Then
- n = n + 1
- d(x) = n
- For j = 1 To 3
- crr(n, j) = brr(i, j + 2)
- Next
- crr(n, 4) = brr(i, 6): crr(n, 5) = brr(i, 7)
- Else
- p = d(x)
- crr(p, 4) = crr(p, 4) + brr(i, 6): crr(p, 5) = crr(p, 5) + brr(i, 7)
- End If
- End If
- Next
- For i = 1 To UBound(crr)
- x = crr(i, 1) & crr(i, 2)
- d1(x) = d1(x) + 1
- Next
- n = 0
- ReDim drr(1 To UBound(crr), 1 To 5)
- For Each a In d1.keys
- For i = 1 To UBound(crr)
- x = crr(i, 1) & crr(i, 2)
- If d1(a) > 1 And x = a Then
- n = n + 1
- For j = 1 To 5
- drr(n, j) = crr(i, j)
- Next
- End If
- Next
- Next
- With Sheets(1)
- .[a4:e100].Clear
- On Error Resume Next
- .[a4].Resize(n, 5) = drr
- .Range("a3").CurrentRegion.Borders.LineStyle = 1
- End With
- End Sub
复制代码 |
|