|
用字典比较容易
- Public Sub test()
- Dim arr(), d, d1, d2, i, n, k, brr
- arr = Sheet1.Range("a2:c" & Range("c65536").End(3).Rows)
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
-
- For i = 1 To UBound(arr, 1)
- d(arr(i, 2)) = d(arr(i, 2)) + arr(i, 3)
- d1(arr(i, 2)) = d1(arr(i, 2)) + 1
- Next
- brr = d.keys
- For n = 0 To UBound(brr)
- d2(brr(n)) = d(brr(n)) / d1(brr(n))
- Next
- For k = 1 To UBound(arr, 1)
- If arr(k, 3) > (d2(arr(k, 2)) + 1) Then
- Range("c" & k + 1).Font.ColorIndex = 5
- End If
- Next
- Erase arr
- Erase brr
- Set d = Nothing
- Set d1 = Nothing
- Set d2 = Nothing
-
-
-
- End Sub
复制代码 你这个平均值是按出现的次数还是6个月的平均?
我按出现的次数平均算的
|
|