- Sub Macro1()
- Dim arr, brr, d, i&, s%, n%
- Set d = CreateObject("scripting.dictionary")
- arr = Sheet3.[f2:g84]
- ReDim brr(1 To 2000, 1 To 10)
- For i = 1 To UBound(arr)
- s2 = s2 + arr(i, 2)
- If Not d.exists(arr(i, 1)) Then
- s = s + 1
- d(arr(i, 1)) = s
- brr(s, 1) = s
- brr(s, 2) = arr(i, 1)
- brr(s, 8) = 1
- brr(s, 9) = arr(i, 2)
- Else
- n = d(arr(i, 1))
- brr(n, 8) = brr(n, 8) + 1
- brr(n, 9) = brr(n, 9) + arr(i, 2)
- End If
- Next
- pjf = Application.Round(s2 / UBound(arr), 2)
- For i = 1 To s
- brr(i, 3) = Application.Round(brr(i, 9) / brr(i, 8), 2)
- brr(i, 4) = pjf
- brr(i, 5) = Application.Round(brr(i, 3) - brr(i, 4), 2)
- Next
- Sheet2.Activate
- [a4].Resize(s, 7) = brr
- [f4] = "=RANK(C4,C:C)"
- [f4].AutoFill [f4].Resize(s)
- End Sub
复制代码 |