|
考试已经结束了,我们都来把毕业题的代码来贴一贴,大家可以互相互相观摩学习一下,如果能加上必要的注释就更好了,同时也欢迎船长和两位学委也来贴一贴,有时间就指点一下。
我就先来个抛砖引玉:- Sub TONGJI()
- Dim d1 As New Dictionary, d2 As New Dictionary, Arr1, Arr2, ARR3, i&, j&, X&, t As Double
- 'T = Timer
- With ThisWorkbook
- X = .Worksheets("统考成绩").Range("A65536").End(3).Row '最后一行的行号
- If X < 3 Then MsgBox "统考成绩表中没有数据,请复制成绩到此表后再运行": Exit Sub
- Arr1 = .Worksheets("统考成绩").Range("B3:E" & .Worksheets("统考成绩").Range("A65536").End(3).Row) '如果行号小于3就判断没有数据退出
-
- For i = 1 To UBound(Arr1)
- If Not d1.Exists(Arr1(i, 1)) And Len(Arr1(i, 1)) > 0 Then '如果字典中不存在就添加到D1和D2中
- d1(Arr1(i, 1)) = d1.Count + 1
- d2.Add (Arr1(i, 1)), New Dictionary '创建嵌套字典
- End If
- d2(Arr1(i, 1))(Arr1(i, 2)) = "" '把学生姓名加入到嵌套字典中
- Next i
-
- ReDim Arr2(1 To d1.Count, 1 To 9) '重新定义数组使其行数与D1相同
- For i = 1 To UBound(Arr1)
- Select Case Arr1(i, 3)
- Case Is = "语文"
- Arr2(d1(Arr1(i, 1)), 2) = Arr2(d1(Arr1(i, 1)), 2) + Arr1(i, 4) '把语文成绩按学校班级累计
- If Arr1(i, 4) >= 60 Then '如果成绩及格,累计到及格人数中,并在嵌套字典的项值中连接字符"1"
- Arr2(d1(Arr1(i, 1)), 3) = Arr2(d1(Arr1(i, 1)), 3) + 1
- d2(Arr1(i, 1))(Arr1(i, 2)) = d2(Arr1(i, 1))(Arr1(i, 2)) & 1
- End If
- Case Is = "数学"
- Arr2(d1(Arr1(i, 1)), 4) = Arr2(d1(Arr1(i, 1)), 4) + Arr1(i, 4)
- If Arr1(i, 4) >= 60 Then
- Arr2(d1(Arr1(i, 1)), 5) = Arr2(d1(Arr1(i, 1)), 5) + 1
- d2(Arr1(i, 1))(Arr1(i, 2)) = d2(Arr1(i, 1))(Arr1(i, 2)) & 2
- End If
- Case Is = "英语"
- Arr2(d1(Arr1(i, 1)), 7) = Arr2(d1(Arr1(i, 1)), 7) + Arr1(i, 4)
- If Arr1(i, 4) >= 60 Then
- Arr2(d1(Arr1(i, 1)), 8) = Arr2(d1(Arr1(i, 1)), 8) + 1
- d2(Arr1(i, 1))(Arr1(i, 2)) = d2(Arr1(i, 1))(Arr1(i, 2)) & 3
- End If
- End Select
- Next i
-
- For i = 1 To UBound(Arr2)
- Arr2(i, 1) = d2.Items(i - 1).Count '统计学校班级人数
- Arr2(i, 2) = WorksheetFunction.Round(Arr2(i, 2) / Arr2(i, 1), 1) '求平均分
- Arr2(i, 4) = WorksheetFunction.Round(Arr2(i, 4) / Arr2(i, 1), 1)
- Arr2(i, 6) = WorksheetFunction.Round(Arr2(i, 7) / Arr2(i, 1), 1)
- ARR3 = d2.Items(i - 1).Items '把子字典的项值赋值给数组
- For j = 0 To UBound(ARR3)
- If InStr(ARR3(j), "1") > 0 And InStr(ARR3(j), "2") > 0 And InStr(ARR3(j), "3") > 0 Then '如果包含字符123,说明此人全部及格
- Arr2(i, 9) = Arr2(i, 9) + 1 '累计全部合格人数
- End If
- Next j
- Set ARR3 = Nothing
- Next i
- With .Worksheets("统计结果")
- .Range("A3:J65536").Clear
- .Range("A3").Resize(d1.Count, 1) = Application.WorksheetFunction.Transpose(d1.Keys) '将字典的关键字赋值给单元格
- .Range("B3").Resize(UBound(Arr2), UBound(Arr2, 2)) = Arr2 '将数组赋值给单元格
- X = .Range("A" & Rows.Count).End(3).Row
- .Range("C3:C" & X & ",E3:E" & X & ",G3:H" & X).NumberFormatLocal = "#0.0_ "
- With .Range("A3:J" & X).Borders
- .LineStyle = xlContinuous
- .Weight = xlThin
- End With
- End With
- End With
- 'T = Timer - T
- 'MsgBox "运行时间" & T & "秒"
- End Sub
复制代码 |
|