|
- Sub tt()
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- arr = [a1].CurrentRegion
- For i = 3 To UBound(arr)
- x = arr(i, 3) & "," & arr(i, 4) '组别+班级为key
- If arr(i, 5) > 0 Then
- d(x) = d(x) + 1
- d1(x) = d1(x) + arr(i, 5)
- End If
- Next
- dk = d.keys
- ReDim brr(1 To UBound(dk), 1 To 15)
- Dim r(1 To 3)
- For i = 0 To UBound(dk)
- x = dk(i)
- xrr = Split(x, ",")
- zb = xrr(0)
- k = IIf(zb = "1组", 1, IIf(zb = "2组", 2, 3))
- c = IIf(zb = "1组", 1, IIf(zb = "2组", 6, 11))
- r(k) = r(k) + 1
- brr(r(k), c) = zb
- brr(r(k), c + 1) = xrr(1)
- If d(x) > 0 Then brr(r(k), c + 2) = d1(x) / d(x)
- Next
- [g4].Resize(UBound(brr), UBound(brr, 2)) = brr
- [j4].Resize(r(1)).Formula = "=rank(rc[-1],r4c9:r" & 3 + r(1) & "c9)"
- [o4].Resize(r(2)).Formula = "=rank(rc[-1],r4c14:r" & 3 + r(2) & "c14)"
- [t4].Resize(r(3)).Formula = "=rank(rc[-1],r4c19:r" & 3 + r(3) & "c19)"
- End Sub
复制代码 |
|