|
- Sub Macro1()
- Dim arr, brr, crr, d, d2, i&, s&, j&
- Set d = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- [h:z].ClearContents
- If Application.CountA([b:b]) = 1 Then Exit Sub
- Range("a1").CurrentRegion.Sort [b2], Order1:=xlDescending, Header:=xlGuess
- arr = Range("a1").CurrentRegion
- ReDim crr(1 To UBound(arr) - 1, 1 To 1)
- For i = 2 To UBound(arr)
- d2(arr(i, 3)) = d2(arr(i, 3)) + 1
- If Not d.exists(arr(i, 2)) Then
- s = s + 1
- d(arr(i, 2)) = s
- crr(i - 1, 1) = s
- Else
- crr(i - 1, 1) = d(arr(i, 2))
- End If
- Next
- ReDim brr(1 To 6, 1 To d2.Count)
- a = d.keys: b = d2.keys: c = d2.items
- For j = 0 To d2.Count - 1
- brr(1, j + 1) = b(j)
- For i = 2 To UBound(arr)
- If arr(i, 3) = b(j) Then brr(6, j + 1) = brr(6, j + 1) + arr(i, 2)
- If arr(i, 2) >= a(4) And arr(i, 3) = b(j) Then brr(2, j + 1) = brr(2, j + 1) + 1
- If arr(i, 2) >= a(9) And arr(i, 3) = b(j) Then brr(3, j + 1) = brr(3, j + 1) + 1
- If arr(i, 2) <= a(d.Count - 5) And arr(i, 3) = b(j) Then brr(4, j + 1) = brr(4, j + 1) + 1
- If arr(i, 2) <= a(d.Count - 10) And arr(i, 3) = b(j) Then brr(5, j + 1) = brr(5, j + 1) + 1
- Next
- brr(6, j + 1) = Round(brr(6, j + 1) / c(j), 2)
- Next
- Range("d2").Resize(UBound(crr)) = crr
- Range("h1").Resize(UBound(brr), UBound(brr, 2)) = brr
- End Sub
复制代码 |
|