|
代码长点,但时空还算过得去
Sub test()
t = Timer
Dim ar1(), ar2()
ar1 = Sheet1.[a2:g8061].Value '多取一个空行,便于操作
ar2 = Application.Transpose(Sheet2.[a1:e1]) '结果的表头,转一下是便于用redim
str1 = ar1(1, 2): r% = 1 '初始化一下
For i% = 1 To UBound(ar1)
If str1 = ar1(i, 2) Then
s = s + ar1(i, 1)
n = n% + 1
If ar1(i, 5) = "TOP10%" Then
top10s = top10s + ar1(i, 1) '累加
top10n = top10n% + 1 '计数
End If
If ar1(i, 6) = "TOP30%" Then
top30s = top30s + ar1(i, 1) '累加
top30n = top30n% + 1 '计数
End If
If ar1(i, 7) = "TOP50%" Then
top50s = top50s + ar1(i, 1) '累加
top50n = top50n% + 1 '计数
End If
Else
r = r + 1
If r > UBound(ar2, 2) Then ReDim Preserve ar2(1 To 5, 1 To r + 10)
ar2(1, r) = str1 '写入分类
str1 = ar1(i, 2)
i = i - 1
If n Then '四个if算四类平均值
ar2(5, r) = s / n
s = 0
n = 0
End If
If top10n Then
ar2(2, r) = top10s / top10n
top10s = 0
top10n = 0
End If
If top30n Then
ar2(3, r) = top30s / top30n
top30s = 0
top30n = 0
End If
If top50n Then
ar2(4, r) = top50s / top50n
top50s = 0
top50n = 0
End If
End If
Next
Sheet3.[a1].Resize(r, 5) = Application.Transpose(ar2)
Sheet3.[g2] = Timer - t
End Sub |
评分
-
查看全部评分
|