|
- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- Set dcs = CreateObject("scripting.dictionary")
- Set djs = CreateObject("scripting.dictionary")
- With Worksheets("参数设置")
- arr = .Range("a4:j9")
- For j = 2 To UBound(arr, 2)
- Set dcs(arr(1, j)) = CreateObject("scripting.dictionary")
- For i = 2 To UBound(arr)
- dcs(arr(1, j))(arr(i, 1)) = arr(i, j)
- Next
- Next
- r = .Range("a12").End(xlDown).Row
- arr = .Range("a12:k" & r)
- For i = 2 To UBound(arr)
- Set djs(arr(i, 1)) = CreateObject("scripting.dictionary")
- For j = 2 To UBound(arr, 2)
- djs(arr(i, 1))(arr(1, j)) = arr(i, j)
- Next
- Next
- End With
- With Worksheets("成绩")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:n" & r)
- For j = 4 To 12
- If Not d.exists(arr(1, j)) Then
- Set d(arr(1, j)) = CreateObject("scripting.dictionary")
- End If
- For i = 2 To UBound(arr)
- If Len(arr(i, j)) <> 0 Then
- If Not d(arr(1, j)).exists(arr(i, 1)) Then
- ReDim brr(1 To 16)
- brr(1) = arr(i, 1)
- brr(14) = arr(i, j)
- brr(15) = arr(i, j)
- If djs.exists(arr(i, 1)) Then
- If djs(arr(i, 1)).exists(arr(1, j)) Then
- brr(16) = djs(arr(i, 1))(arr(1, j))
- End If
- End If
- Else
- brr = d(arr(1, j))(arr(i, 1))
- End If
- brr(2) = brr(2) + 1
- brr(3) = brr(3) + arr(i, j)
- If dcs.exists(arr(1, j)) Then
- If arr(i, j) <= dcs(arr(1, j))("过差") Then
- brr(4) = brr(4) + 1
- End If
- If arr(i, j) >= dcs(arr(1, j))("及格") Then
- brr(5) = brr(5) + 1
- End If
- If arr(i, j) >= dcs(arr(1, j))("优秀") Then
- brr(6) = brr(6) + 1
- End If
- If arr(i, j) >= dcs(arr(1, j))("特优") Then
- brr(7) = brr(7) + 1
- End If
- If arr(i, j) < brr(14) Then
- brr(14) = arr(i, j)
- End If
- If arr(i, j) > brr(15) Then
- brr(15) = arr(i, j)
- End If
- End If
- d(arr(1, j))(arr(i, 1)) = brr
- End If
- Next
- Next
- End With
- With Worksheets("分析2")
- .UsedRange.offset(1, 0).Clear
- .Columns("i:l").NumberFormatLocal = "0.00%"
- For Each aa In d.keys
- brr = Application.Transpose(Application.Transpose(d(aa).items))
- For i = 1 To UBound(brr)
- If Len(brr(i, 2)) <> 0 Then
- brr(i, 8) = Round(brr(i, 3) / brr(i, 2), 2)
- For j = 4 To 7
- brr(i, j + 5) = Round(brr(i, j) / brr(i, 2), 4)
- Next
- End If
- brr(i, 13) = Round(brr(i, 11) * 0.5 + brr(i, 10) * 0.2 + brr(i, 8) * 0.3, 2)
- Next
- r = .Cells(.Rows.Count, 1).End(xlUp).Row + 2
- .Cells(r, 1) = "科目"
- With .Cells(r, 2)
- .Value = aa
- .Resize(1, 15).Merge
- End With
- .Cells(r + 1, 1).Resize(1, 16) = Array("班级", "参考人数", "总分", "过差人数", "及格人数", "优秀人数", "特优人数", "平均分", "过差率", "及格率", "优秀率", "特优率", "综合指数", "最低分", "最高分", "任课教师")
- .Cells(r + 2, 1).Resize(UBound(brr), UBound(brr, 2)) = brr
- r1 = .Cells(.Rows.Count, 1).End(xlUp).Row
- .Range(.Cells(r, 1), .Cells(r1, 16)).Borders.LineStyle = xlContinuous
- Next
- With .UsedRange
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- End With
-
- End Sub
复制代码 |
|