|
修改了一下
- Option Explicit
- Sub UsingArray()
- On Error Resume Next
- Dim rng As Range, arr(), brr(), i As Integer, j As Integer, k As Integer, n As Integer
- arr = Range([b2], Cells(Rows.Count, 3).End(xlUp))
- n = 1
- For i = 1 To UBound(arr)
- ReDim Preserve brr(1 To 3, 1 To n) '保留已有值
- For j = 1 To UBound(brr, 2)
- If brr(1, j) <> arr(i, 1) Then '判断
- k = k + 1
- Else
- brr(2, j) = brr(2, j) + 1 '夺冠次数
- brr(3, j) = brr(3, j) + arr(i, 2) '总产量累加
- End If
- Next
- If k = n Then '内层循环brr数组2维上标次,说明是新值
- brr(1, n) = arr(i, 1) '初始化
- brr(2, n) = 1
- brr(3, n) = arr(i, 2)
- n = n + 1
- End If
- k = 0 '初始化
- Next
- '--------------------------------------------
- For i = 1 To UBound(brr, 2)
- brr(3, i) = brr(3, i) / brr(2, i) '求平均值
- Next
- '----------------------------------------------
- Range("e2").Resize(UBound(brr, 2), 3) = Application.Transpose(brr)
-
- End Sub
- Sub UsingDictionary()
- Dim rng As Range, arr(), brr(), i As Integer, j As Integer, k As Integer, n As Integer
- Dim d1 As Object, d2 As Object, arr2, arr3
- arr = Range([b2], Cells(Rows.Count, 3).End(xlUp))
- Set d1 = CreateObject("scripting.dictionary") '定义字典
- Set d2 = CreateObject("scripting.dictionary") '定义字典
- '------------------------------------------
- For i = 1 To UBound(arr)
- d1(arr(i, 1)) = d1(arr(i, 1)) + arr(i, 2) '修改键的对应的item值
- d2(arr(i, 1)) = d2(arr(i, 1)) + 1 '记录次数
- Next
- '------------------------------------------
- arr2 = d1.items
- arr3 = d2.items
- For i = 0 To d1.Count - 1 '求平均值
- arr2(i) = arr2(i) / arr3(i) '好像不能直接使用d1.Item(i)
- Next
- '------------------------------------------
- Range("e2").Resize(d1.Count) = Application.Transpose(d1.Keys)
- Range("f2").Resize(d1.Count) = Application.Transpose(d2.items)
- Range("g2").Resize(d1.Count) = Application.Transpose(arr2)
-
- End Sub
复制代码 |
评分
-
查看全部评分
|