年级成绩统计

ypcf0312

请高手帮忙完善年级成绩统计，万分感谢！说明在附件中

xyxcc177

什么都不说，找义工啊

chxw68

类似的贴子论坛上有很多。

ypcf0312

chxw68 发表于 2018-9-17 18:26
类似的贴子论坛上有很多。

弄了很久，无奈做伸手党，顺便向高手们学习1

excelvlookup

可以啊，连个说明都省了。应考人数在哪里？何谓优秀？何为及格？分数分段是否包含本数？

ypcf0312

朱荣兴 发表于 2018-9-17 19:17
成绩统计分析的问题，前几天刚刚帮人做了一个，废了好大劲。
同时，你得先回答5楼的问题

应考人数为名单的人数，参考人数为各科成绩不为0的人数，〉＝80为优秀，〉＝60为及格，分数分段包含本数

ypcf0312

朱荣兴 发表于 2018-9-17 19:17
成绩统计分析的问题，前几天刚刚帮人做了一个，废了好大劲。
同时，你得先回答5楼的问题

应考人数为名单的人数，参考人数为各科成绩不为0的人数，〉＝80为优秀，〉＝60为及格，分数分段包含本数，请大侠帮帮忙

iwangyi

Sub CC()
Cells.Clear
Set cn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source= " & ThisWorkbook.FullName
Sql = "SELECT DISTINCT 班别 from [成绩$a2:i]"
rs.Open Sql, cn, 1, 3
Do Until rs.EOF
bj = rs.fields("班别")
With Sheets("统计")
n = .[a65536].End(3).Row + 2
.Cells(n, 1) = bj & "班成绩统计"
Sheet3.Rows(3).Copy .Cells(n + 1, 1)
For Each km In Array("语文", "数学", "英语")
Sqll = "select count(姓名),count(" & km & "),count(" & km & ")/count(姓名),sum(" & km & "),avg(" & km & "),sum(iif(" & km & ">=80,1,0)),sum(iif(" & km & ">=80,1,0))/count(姓名),sum(iif(" & km & ">=60,1,0)),sum(iif(" & km & ">=60,1,0))/count(姓名),max(" & km & "),min(" & km & "),sum(iif(" & km & "=100,1,0)),sum(iif(99.5>" & km & " and " & km & " >90,1,0)),sum(iif(89.5>" & km & " and " & km & " >80,1,0)),sum(iif(79.5>" & km & " and " & km & " >70,1,0)),sum(iif(69.5>" & km & " and " & km & " >60,1,0)),sum(iif(59.5>" & km & " and " & km & " >50,1,0)),sum(iif(49.5>" & km & " and " & km & " >40,1,0)),sum(iif(39.5<" & km & " ,1,0)) from [成绩$a2:i] where 班别=" & bj & " "
.Range("b" & n + 2).CopyFromRecordset cn.Execute(Sqll): .Cells(n + 2, 1) = km
.Range(Cells(n + 1, 1), Cells(n + 2, 20)).Borders.ColorIndex = 7
n = .[a65536].End(3).Row - 1
Next
End With
rs.movenext
Loop
End Sub

iwangyi

附件************