|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Sub kfx()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Call pub
- ls = 26
- With Worksheets("录入成绩")
- r = .Cells(.Rows.Count, 4).End(xlUp).Row
- arr = .Range("a2:q" & r)
- End With
- For j = 5 To UBound(arr, 2) - 1
- If Application.Count(Application.Index(arr, 0, j)) > 0 Then
- Set d(arr(1, j)) = CreateObject("scripting.dictionary")
- jq30 = Application.Large(Application.Index(arr, 0, j), 30)
- jq60 = Application.Large(Application.Index(arr, 0, j), 60)
- jh50 = Application.Small(Application.Index(arr, 0, j), 50)
- For i = 2 To UBound(arr)
- If Not d(arr(1, j)).exists(arr(i, 2)) Then
- ReDim brr(1 To ls)
- brr(1) = arr(i, 2)
- brr(2) = arr(1, j)
- Else
- brr = d(arr(1, j))(arr(i, 2))
- End If
- brr(3) = brr(3) + 1
- If Len(arr(i, j)) <> 0 Then
- brr(4) = brr(4) + 1
- brr(5) = brr(5) + arr(i, j)
- Select Case arr(i, j)
- Case Is >= d_cs(arr(1, j))("A")
- brr(8) = brr(8) + 1
- Case Is >= d_cs(arr(1, j))("B")
- brr(9) = brr(9) + 1
- Case Is >= d_cs(arr(1, j))("C")
- brr(10) = brr(10) + 1
- Case Is >= d_cs(arr(1, j))("D")
- brr(11) = brr(11) + 1
- Case Else
- brr(12) = brr(12) + 1
- End Select
- If arr(i, j) >= d_cs(arr(1, j))("C") Then
- brr(13) = brr(13) + 1
- End If
- If arr(i, j) >= d_cs(arr(1, j))("B") Then
- brr(16) = brr(16) + 1
- End If
- If IsEmpty(brr(21)) Then
- brr(21) = arr(i, j)
- Else
- If brr(21) < arr(i, j) Then
- brr(21) = arr(i, j)
- End If
- End If
- If IsEmpty(brr(22)) Then
- brr(22) = arr(i, j)
- Else
- If brr(22) > arr(i, j) Then
- brr(22) = arr(i, j)
- End If
- End If
- If arr(i, j) >= jq30 Then
- brr(24) = brr(24) + 1
- End If
- If arr(i, j) >= jq60 Then
- brr(25) = brr(25) + 1
- End If
- If arr(i, j) <= jh50 Then
- brr(26) = brr(26) + 1
- End If
-
- End If
- d(arr(1, j))(arr(i, 2)) = brr
- Next
- End If
- Next
- With Worksheets("科分析")
- .Cells.Clear
- x = 1
- For Each aa In d.keys
- ReDim crr(1 To d(aa).Count, 1 To UBound(brr))
- ReDim drr(1 To UBound(crr, 2))
- drr(1) = "合计"
- drr(2) = aa
- m = 0
- For Each bb In d(aa).keys
- m = m + 1
- brr = d(aa)(bb)
- For j = 1 To UBound(brr)
- crr(m, j) = brr(j)
- Next
- For Each y In Array(3, 4, 5, 8, 9, 10, 11, 12, 13, 16, 19, 24, 25, 26)
- drr(y) = drr(y) + brr(y)
- Next
- Next
- For i = 1 To UBound(crr)
- If Len(crr(i, 4)) <> 0 And crr(i, 4) <> 0 Then
- crr(i, 5) = Round(crr(i, 5) / crr(i, 4), 2)
- crr(i, 14) = Round(crr(i, 13) / crr(i, 4), 4)
- crr(i, 17) = Round(crr(i, 16) / crr(i, 4), 4)
- crr(i, 19) = Round(crr(i, 8) / crr(i, 4), 4)
- crr(i, 23) = Round(crr(i, 12) / crr(i, 4), 4)
- End If
- Next
- If Len(drr(4)) <> 0 And drr(4) <> 0 Then
- drr(5) = Round(drr(5) / drr(4), 2)
- drr(14) = Round(drr(13) / drr(4), 4)
- drr(17) = Round(drr(16) / drr(4), 4)
- drr(19) = Round(drr(8) / drr(4), 4)
- drr(23) = Round(drr(12) / drr(4), 4)
- drr(21) = Application.Max(Application.Index(crr, 0, 21))
- drr(22) = Application.Min(Application.Index(crr, 0, 22))
-
- End If
-
- For i = 1 To UBound(crr)
- crr(i, 7) = crr(i, 5) - drr(5)
- Next
- For Each y In Array(5, 14, 17, 19)
- d1.RemoveAll
- For i = 1 To UBound(crr)
- If Len(crr(i, y)) <> 0 And crr(i, y) <> 0 Then
- d1(crr(i, y)) = d1(crr(i, y)) + 1
- End If
- Next
- nn = 1
- kk = d1.keys
- For k = 0 To UBound(kk)
- mm = Application.Large(kk, k + 1)
- ss = d1(mm)
- d1(mm) = nn
- nn = nn + ss
- Next
- For i = 1 To UBound(crr)
- If Len(crr(i, y)) <> 0 And crr(i, y) <> 0 Then
- crr(i, y + 1) = d1(crr(i, y))
- End If
- Next
- Next
- With .Cells(x, 1)
- .Value = jbcs(1, 2) & jbcs(2, 2) & jbcs(3, 2) & jbcs(4, 2) & aa & "科质量分析"
- .Resize(1, ls).Merge
- With .Font
- .Size = 18
- .Bold = True
- End With
- End With
- .Cells(x + 1, 1).Resize(1, ls) = Array("班级", "科目", "应考" & vbLf & "人数", "实考" & vbLf & "人数", "平均" & vbLf & "分", "名" & vbLf & "次", "平均" & vbLf & "相对" & vbLf & "分", "A" & vbLf & "(人)", "B" & vbLf & "(人)", "C" & vbLf & "(人)", "D" & vbLf & "(人)", "E" & vbLf & "(人)", "及格人数" & vbLf & "(C以上)", "及格" & vbLf & "率", "名次", "良好人数" & vbLf & "(A/B)", "良好" & vbLf & "率", "名次", "A率", "名次", "最高" & vbLf & "分", "最低" & vbLf & "分", "低分率" & vbLf & "(E)", "级前" & vbLf & "30名" & vbLf & "(人)", "级前" & vbLf & "60名" & vbLf & "(人)", "级后" & vbLf & "50名" & vbLf & "(人)", "A" & vbLf & "(人)", "B" & vbLf & "(人)", "C" & vbLf & "(人)", "D" & vbLf & "(人)")
- .Cells(x + 2, 1).Resize(1, UBound(drr)) = drr
- .Cells(x + 3, 1).Resize(UBound(crr), UBound(crr, 2)) = crr
- With .Cells(x + 1, 1).Resize(UBound(crr) + 2, ls)
- .Borders.LineStyle = xlContinuous
- With .Font
- .Size = 10
- End With
- End With
- .Rows(x).RowHeight = 22.5
- .Rows(x + 1).RowHeight = 36
- .Rows(x + 2).Resize(UBound(brr) + 2).RowHeight = 15
- x = x + UBound(crr) + 4
- Next
- .Range("n:n,q:q,s:s,w:w").NumberFormatLocal = "0.00%"
- .Columns("a:z").AutoFit
- With .UsedRange
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- End With
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|