|
楼主 |
发表于 2010-7-4 23:16
|
显示全部楼层
找不到?那我直接传代码
Sub 算成绩()
Dim xx(1 To 9) As String
Dim xxhz(1 To 9) As String
xx(1) = "南": xx(2) = "兴": xx(3) = "东": xx(4) = "西": xx(5) = "峪": xx(6) = "普": xx(7) = "尧": xx(8) = "苏": xx(9) = "后"
xxhz(1) = "南村": xxhz(2) = "兴中": xxhz(3) = "东风": xxhz(4) = "西沟": xxhz(5) = "峪头"
xxhz(6) = "普乐塬": xxhz(7) = "尧场": xxhz(8) = "苏家坡": xxhz(9) = "后沟"
m = InputBox("请输入年级如1、2、3") '记录年级
If m < 5 Then
xxs = 9
Else
xxs = 7
End If
For q = 1 To xxs
Sheets(xx(q)).Activate
n = Application.WorksheetFunction.CountA(Range("A1: A50")) - 12 '记录考试人数
'计算成绩
l = Application.WorksheetFunction.Match("语文", Range("a2:e2"), 0) '记录"语文"所在列数
ywzf = Application.WorksheetFunction.Sum(Range(Cells(3, l), Cells(n + 2, l))) '语文总分
sxzf = Application.WorksheetFunction.Sum(Range(Cells(3, l + 1), Cells(n + 2, l + 1))) '数学总分
ywjg = Application.WorksheetFunction.CountIf(Range(Cells(3, l), Cells(n + 2, l)), ">59") '语文及格人数
sxjg = Application.WorksheetFunction.CountIf(Range(Cells(3, l + 1), Cells(n + 2, l + 1)), ">59") '数学及格人数
ywyx = Application.WorksheetFunction.CountIf(Range(Cells(3, l), Cells(n + 2, l)), ">79") '语文优秀人数
sxyx = Application.WorksheetFunction.CountIf(Range(Cells(3, l + 1), Cells(n + 2, l + 1)), ">79") '数学优秀人数
If m > 2 Then
yyzf = Application.WorksheetFunction.Sum(Range(Cells(3, l + 2), Cells(n + 2, l + 2))) '英语总分
yyjg = Application.WorksheetFunction.CountIf(Range(Cells(3, l + 2), Cells(n + 2, l + 2)), ">59") '英语及格人数
yyyx = Application.WorksheetFunction.CountIf(Range(Cells(3, l + 2), Cells(n + 2, l + 2)), ">79") '英语优秀人数
End If
'输出成绩
Cells(n + 3, l) = ywzf: Cells(n + 3, l + 1) = sxzf '语文、数学总分
Cells(n + 4, l) = Round(ywzf / n, 2) '语文、数学均分
Cells(n + 4, l + 1) = Round(sxzf / n, 2)
Cells(n + 5, l) = Round(Cells(n + 4, l) * 0.6, 2) '语文、数学均分折分
Cells(n + 5, l + 1) = Round(Cells(n + 4, l + 1) * 0.6, 2)
Cells(n + 6, l) = ywjg: Cells(n + 6, l + 1) = sxjg '语文、数学及格人数
Cells(n + 7, l) = Round(ywjg / n * 100, 2) '语文、数学及格率
Cells(n + 7, l + 1) = Round(sxjg / n * 100, 2)
Cells(n + 8, l) = Round(Cells(n + 7, l) * 0.25, 2) '语文、数学及格率折分
Cells(n + 8, l + 1) = Round(Cells(n + 7, l + 1) * 0.25, 2)
Cells(n + 9, l) = ywyx: Cells(n + 9, l + 1) = sxyx '语文、数学优秀人数
Cells(n + 10, l) = Round(ywyx / n * 100, 2) '语文、数学优秀率
Cells(n + 10, l + 1) = Round(sxyx / n * 100, 2)
Cells(n + 11, l) = Round(Cells(n + 10, l) * 0.15, 2) '语文、数学优秀率折分
Cells(n + 11, l + 1) = Round(Cells(n + 10, l + 1) * 0.15, 2)
Cells(n + 12, l) = Cells(n + 5, l) + Cells(n + 8, l) + Cells(n + 11, l) '语文、数学积分
Cells(n + 12, l + 1) = Cells(n + 5, l + 1) + Cells(n + 8, l + 1) + Cells(n + 11, l + 1)
If m > 2 Then '英语科
Cells(n + 3, l + 2) = yyzf
Cells(n + 4, l + 2) = Round(yyzf / n, 2)
Cells(n + 5, l + 2) = Round(Cells(n + 4, l + 2) * 0.6, 2)
Cells(n + 6, l + 2) = yyjg
Cells(n + 7, l + 2) = Round(yyjg / n * 100, 2)
Cells(n + 8, l + 2) = Round(Cells(n + 7, l + 2) * 0.25, 2)
Cells(n + 9, l + 2) = yyyx
Cells(n + 10, l + 2) = Round(yyyx / n * 100, 2)
Cells(n + 11, l + 2) = Round(Cells(n + 10, l + 2) * 0.15, 2)
Cells(n + 12, l + 2) = Cells(n + 5, l + 2) + Cells(n + 8, l + 2) + Cells(n + 11, l + 2)
End If
'计算个人总分,统计合格人数
hg = 0
For i = 3 To n + 2
If m < 3 Then
Cells(i, l + 2) = Round(Cells(i, l) + Cells(i, l + 1), 0)
If Cells(i, l) > 59 And Cells(i, l + 1) > 59 Then hg = hg + 1
Else
Cells(i, l + 3) = Round(Cells(i, l) + Cells(i, l + 1) + Cells(i, l + 2), 0)
If Cells(i, l) > 59 And Cells(i, l + 1) > 59 And Cells(i, l + 2) > 59 Then hg = hg + 1
End If
Next i
If m < 3 Then
Cells(n + 6, l + 2) = hg
Else
Cells(n + 6, l + 3) = hg
End If
Next q
End Sub |
|