用excel实现自动批卷，并得出不同题号间的正确数
用excel实现自动批卷，并得出不同题号间的正确数（这部分代码是我自己加的）！和大家分享！Sub test()
Dim studentno '学号
Dim rwIndex As Integer'行号
Dim clIndex As Integer'列号
Dim tAnswer As String '标准答案
Dim sAnswer As String '学生答案
Dim trueNumber As Integer'正确数
Dim wrongNumber As Integer '错误数
Dim total1 As Double '客观前10题正确数
Dim total2 As Double '客观前20题正确数
Dim total3 As Double '客观前40题正确数
Dim total4 As Double '客观前70题正确数
rwIndex = 2 '起始行
studentno = Sheet1.Cells(rwIndex, 3)
Do While (studentno <> "")
clIndex = 4 '起始列
trueNumber = 0
wrongNumber = 0
total = 0
Worksheets("Sheet1").Rows(rwIndex + 1).Insert'插入一行
sAnswer = Sheet1.Cells(rwIndex, clIndex)
tAnswer = Sheet2.Cells(2, clIndex)
'判断一个学生的选择题
Do While (sAnswer <> "") '到底怎样控制结束
If Trim(sAnswer) = Trim(tAnswer) Then '比对客观的答案
Sheet1.Cells(rwIndex + 1, clIndex) = "对"
trueNumber = trueNumber + 1 '正确数加一
Else
Sheet1.Cells(rwIndex + 1, clIndex) = "错"
wrongNumber = wrongNumber + 1
End If
If clIndex = 13 Then total1 = trueNumber
If clIndex = 23 Then total2 = trueNumber
If clIndex = 43 Then total3 = trueNumber
If clIndex = 73 Then total4 = trueNumber
clIndex = clIndex + 1
tAnswer = Sheet2.Cells(2, clIndex)
sAnswer = Sheet1.Cells(rwIndex, clIndex)
Loop
Sheet1.Cells(rwIndex + 1, clIndex) = trueNumber
Sheet1.Cells(rwIndex + 1, clIndex + 1) = total1 * 1 + (total2 - total1) * 1 + (total3 - total2) * 2 + (total4 - total3) * 0.5 + (trueNumber - total4) * 0.5
Sheet1.Cells(rwIndex + 1, clIndex + 2) = total1 '1-10题的正确数
Sheet1.Cells(rwIndex + 1, clIndex + 3) = total2 - total1 '10-20题的正确数
Sheet1.Cells(rwIndex + 1, clIndex + 4) = total3 - total2 '20-40题的正确数
Sheet1.Cells(rwIndex + 1, clIndex + 5) = total4 - total3 '40-70题的正确数
Sheet1.Cells(rwIndex + 1, clIndex + 6) = trueNumber - total4 '70-90题的正确数
rwIndex = rwIndex + 2
studentno = Sheet1.Cells(rwIndex, 3)
Loop
Sheet1.Cells(1, clIndex) = "正确数"
Sheet1.Cells(1, clIndex + 1) = "得分"
Sheet1.Cells(1, clIndex + 2) = "1-10“对话听力”正确数"
Sheet1.Cells(1, clIndex + 3) = "10-20“短文听力”正确数"
Sheet1.Cells(1, clIndex + 4) = "20-40“阅读理解”正确数"
Sheet1.Cells(1, clIndex + 5) = "40-70“词汇与结构”正确数"
Sheet1.Cells(1, clIndex + 6) = "70-90“完型填空”正确数"
End Sub
页:
[1]