|
- Sub tt()
- arr = Sheet1.UsedRange
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- ReDim brr(1 To UBound(arr), 1 To 5)
- For j = 1 To UBound(arr, 2) Step 6
- For i = 2 To UBound(arr)
- xm = arr(i, j) '姓名
- If Len(xm) > 0 Then
- d1(xm) = d1(xm) + 1 '每个名字统计的次数(用于计算平均分)
- If Not d.exists(xm) Then
- n = n + 1
- d(xm) = n
- brr(n, 1) = xm
- brr(n, 2) = arr(i, j + 1)
- brr(n, 3) = arr(i, j + 2)
- brr(n, 4) = arr(i, j + 4)
- Else
- p = d(xm)
- brr(p, 3) = brr(p, 3) & "+" & arr(i, j + 2)
- brr(p, 4) = brr(p, 4) + arr(i, j + 4)
- End If
- End If
- Next
- Next
- For i = 1 To n '计算平均分
- brr(i, 4) = Round(brr(i, 4) / d1(brr(i, 1)), 1)
- Next
- Sheet2.[a8].Resize(n, 4) = brr
- End Sub
复制代码 |
|