|
本帖最后由 白云无尽9987 于 2016-1-4 20:29 编辑
- Sub 班级成绩统计() '27行
- Sheets("kk").Range("g7:i21").ClearContents
- With ActiveSheet
- arr = .[a1].CurrentRegion
- rs = 7 '第7行
- mf = Array(100, 100, 100) '各科满分值
- For j = 7 To 9 '第7----9列
- Set Rng = .Cells(46, j).Resize(UBound(arr) - 1)
- .Cells(rs + 0, j) = Application.WorksheetFunction.CountA(Sheets("KK").Range("E46:E115")) '应到人数
- .Cells(rs + 1, j) = Application.WorksheetFunction.Count(Rng) '各科目参考人数
- .Cells(rs + 2, j) = .Cells(rs + 0, j) - .Cells(rs + 1, j) '各科目缺考人数
- .Cells(rs + 3, j) = Application.WorksheetFunction.Sum(Rng) '各科目总分
-
-
- .Cells(rs + 4, j) = Round(Application.WorksheetFunction.Average(Rng), 2) '各科目均分_______________________________________________________________________________________________________________________________________________________?
- ' yx = mf(j - 4) * 0.8: jg = mf(j - 4) * 0.6
- YX = 80: JG = 60: DF = 30
- .Cells(rs + 5, j) = Application.WorksheetFunction.CountIf(Rng, ">=" & YX) '各科目优秀人数
- .Cells(rs + 6, j) = Round(.Cells(rs + 5, j) / .Cells(rs + 1, j), 2) '各科目优秀率
- .Cells(rs + 11, j) = Application.WorksheetFunction.CountIf(Rng, "<" & DF) '各科目低分人数
- .Cells(rs + 12, j) = Round(.Cells(rs + 11, j) / .Cells(rs + 1, j), 2) '各科目低分率
- .Cells(rs + 13, j) = Application.WorksheetFunction.CountIf(Rng, ">=" & JG) '各科目及格人数
- .Cells(rs + 14, j) = Round(.Cells(rs + 13, j) / .Cells(rs + 1, j), 2) '各科目及格率
-
- .Cells(rs + 7, j) = .Cells(rs + 13, j) - .Cells(rs + 5, j) '各科目良好人数_____________及格-优秀OK
- .Cells(rs + 8, j) = Round(.Cells(rs + 7, j) / .Cells(rs + 1, j), 2) '各科目良好率
- .Cells(rs + 9, j) = .Cells(rs + 1, j) - .Cells(rs + 13, j) - .Cells(rs + 11, j) '各科目中等人数_____________参考-及格-低分OK
- .Cells(rs + 10, j) = Round(.Cells(rs + 7, j) / .Cells(rs + 1, j), 2) '各科目中等率
- Next
- End With
- End Sub
复制代码
9999.rar
(157.54 KB, 下载次数: 9)
|
|