|
zjdh大大範本
Dim arr, dic, i%
Set dic = CreateObject("scripting.dictionary")
Set dic1 = CreateObject("scripting.dictionary")
s = 0
p = 0
With ActiveSheet
arr = Sheets("表").Range("P2:X" & Sheets("表").Range("X65535").End(xlUp).Row)
For i = 1 To UBound(arr)
If dic.exists(arr(i, 9)) Then
dic(arr(i, 9)) = dic(arr(i, 9)) + 1
'dic1(arr(i, 1)) = dic1(arr(i, 9)) + arr(i, 1)
dic1(arr(i, 9)) = dic1(arr(i, 9)) + arr(i, 1)
Else
dic(arr(i, 9)) = 1
dic1(arr(i, 9)) = arr(i, 1)
End If
s = s + arr(i, 1)
p = p + 1
Next
Sheets("匯總表").Range("C2:E5000").ClearContents
Sheets("匯總表").[C2].Resize(dic.Count, 1) = Application.Transpose(dic.Keys)
Sheets("匯總表").[D2].Resize(dic.Count, 1) = Application.Transpose(dic.Items)
Sheets("匯總表").[E2].Resize(dic.Count, 1) = Application.Transpose(dic1.Items)
Sheets("匯總表").Range("d" & dic.Count + 2) = p
Sheets("匯總表").Range("e" & dic.Count + 2) = s
End With |
评分
-
查看全部评分
|