|
- Sub go()
- brr = [b2:g10]
- Set d = CreateObject("scripting.dictionary")
- For i = 2 To UBound(brr) '每个运动员在结果数组中的位置
- d(brr(i, 1)) = i
- Next
- arr = Range("j1:L" & [j65536].End(3).Row)
- For i = 2 To UBound(arr)
- x = arr(i, 1): cj = arr(i, 3)
- If d(x) > 0 Then
- p = d(x) '找到运动员在结果数组中的位置
- If cj = "缺席" Then brr(p, 3) = "Y"
- If Val(cj) > 0 Then '如果有成绩
- brr(p, 2) = brr(p, 2) + 1 '场数+1
- brr(p, 6) = brr(p, 6) + cj '总成绩
- If Len(brr(p, 4)) = 0 Then '最高分
- brr(p, 4) = cj
- Else
- If brr(p, 4) < cj Then brr(p, 4) = cj
- End If
- If Len(brr(p, 5)) = 0 Then '最低分
- brr(p, 5) = cj
- Else
- If brr(p, 5) > cj Then brr(p, 5) = cj
- End If
- End If
- End If
- Next
-
- For i = 2 To UBound(brr)
- If brr(i, 2) > 0 Then
- If brr(i, 3) = "" Then brr(i, 3) = "N" '无缺席者
- If brr(i, 2) > 2 Then '参加场数大于2,减最高分最低分求平均
- brr(i, 6) = (brr(i, 6) - brr(i, 4) - brr(i, 5)) / (brr(i, 2) - 2)
- Else '参加场数<=2,直接求平均
- brr(i, 6) = brr(i, 6) / brr(i, 2)
- End If
- End If
- Next
- [b2:g10] = brr
-
- End Sub
复制代码 |
|