如果你认可一个Client合计为0,可以加一个判断,不加百分数- Sub Macro1()
- Dim arr, brr(), i&, j&, v&, m#, k, t, k2, t2, s$, d As Object, ds As Object
- Set d = CreateObject("scripting.dictionary")
- Set ds = CreateObject("scripting.dictionary")
- arr = Range("A1").CurrentRegion
- For i = 2 To UBound(arr)
- If Not d.Exists(arr(i, 1)) Then Set d(arr(i, 1)) = CreateObject("scripting.dictionary")
- d(arr(i, 1))(arr(i, 2)) = d(arr(i, 1))(arr(i, 2)) + arr(i, 3)
- ds(arr(i, 1)) = ds(arr(i, 1)) + arr(i, 3)
- Next
- k = d.keys
- t = ds.items
- ReDim brr(0 To d.Count - 1, 1 To 2)
- With WorksheetFunction
- For i = 0 To d.Count - 1
- brr(i, 1) = k(i)
- k2 = d(k(i)).keys
- t2 = d(k(i)).items
- m = .Max(t2)
- v = .Match(m, t2, 0) - 1
- s = k2(v)
- brr(i, 2) = s
- If t(i) <> 0 Then brr(i, 2) = brr(i, 2) & Format(m / t(i), "0%") & Chr(10)
- For j = 0 To UBound(t2)
- If j <> v Then brr(i, 2) = brr(i, 2) & "/" & k2(j)
- Next
- Next
- End With
- [k10].Resize(d.Count, 2) = brr
- End Sub
复制代码 |