|
- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- mcd = [{0.15,0.35,0.5,0.8,1;10,8,5,3,1}]
- With Worksheets("学生成绩统计册")
- .AutoFilterMode = False
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- c = .Cells(3, .Columns.Count).End(xlToLeft).Column
- arr = .Range("a2").Resize(r - 1, c)
- End With
- s = 0
- For j = 5 To UBound(arr, 2) Step 2
- Set d(arr(1, j)) = CreateObject("scripting.dictionary")
- ReDim fsd(1 To UBound(mcd, 2))
- For i = 1 To UBound(mcd, 2)
- fsd(i) = Application.Large(Application.Index(arr, 0, j), (UBound(arr) - 2) * mcd(1, i))
- Next
- For i = 3 To UBound(arr)
- If Not d(arr(1, j)).exists(arr(i, 1)) Then
- ReDim brr(1 To 12)
- brr(1) = arr(1, j)
- brr(2) = arr(i, 1)
- s = s + 1
- Else
- brr = d(arr(1, j))(arr(i, 1))
- End If
- brr(4) = brr(4) + 1
- n = Application.Match(arr(i, j), fsd, -1)
- If Not IsError(n) Then
- brr(n + 4) = brr(n + 4) + 1
- End If
- d(arr(1, j))(arr(i, 1)) = brr
- Next
- Next
- ReDim crr(1 To s, 1 To 12)
- m = 0
- For Each aa In d.keys
- For Each bb In d(aa).keys
- brr = d(aa)(bb)
- For j = 1 To UBound(mcd, 2)
- If Len(brr(j + 4)) <> 0 Then
- brr(j + 4) = brr(j + 4) * mcd(2, j)
- brr(10) = brr(10) + brr(j + 4)
- End If
- Next
- If Len(brr(4)) <> 0 And brr(4) <> 0 Then
- brr(11) = Round(brr(10) / brr(4), 2)
- End If
- m = m + 1
- For j = 1 To UBound(brr)
- crr(m, j) = brr(j)
- Next
- Next
- Next
- With Worksheets("各段点一览表")
- .UsedRange.Offset(4, 0).Clear
- With .Range("a5").Resize(UBound(crr), UBound(crr, 2))
- .Value = crr
- .Borders.LineStyle = xlContinuous
- End With
- With .UsedRange
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- End With
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|