[已解决]用公式计算学生的成绩

武林长风

请教各位老师：用函数计算如M小学一年一班数学的成绩，总分：数学分数由高到低的前31名（这个31可引用计分人数单元格）。平均分是总分/计分人数；及格人数，是数学分数前31个里>=60的个数；优秀人数，是数学分数前31个里>=85的个数。 新建文件夹.rar (17.36 KB, 下载次数: 14)

2014-8-14 13:36 上传

点击文件名下载附件

最佳答案

月排行榜 / 总排行榜

tgydslr

2014-8-14 15:32

按你的人数算吧
全部班级 (, 下载次数: 4)

上传

点击文件名下载附件

跳转到最佳答案楼层

武林长风

1楼的30全改为31.

武林长风

手动算出的结果。

旧书重读

我也想看答案，那个学生成绩统计如果每个班都做成单独一张表，似乎可以用排序的辅助方法来计算。

baksy

D3：G3区域复制以下公式，下拉，
其中除E3单元格以外的公式，都是三键回车(点击公式编辑栏中的任意位置，按住 shift、ctrl 两个键，然后敲enter键 )；
D3=SUM(LARGE(IF(M小学!B$3:B$646=汇总!A3,M小学!E$3:E$646),ROW($1:$31)))
E3=D3/31
F3=COUNT(IF((M小学!B:B=汇总!A3)*(M小学!E$3:E$646>LARGE(IF(M小学!B$3:B$646=汇总!A3,M小学!E$3:E$646),32)),M小学!E:E))
G3=COUNT(IF((M小学!B:B=汇总!A3)*(M小学!E$3:E$646>84),M小学!E:E))

武林长风

baksy 发表于 2014-8-14 14:43
D3：G3区域复制以下公式，下拉，
其中除E3单元格以外的公式，都是三键回车(点击公式编辑栏中的任意位置，按 ...

D3=SUM(LARGE(IF(M小学!B3:B646=汇总!A3,M小学!E3:E646),ROW(1:31)))，ROW(1:31)，不是所有的班都是31.M小学二年二班是前27个。

baksy

各年级通用公式；
D3：G3区域复制以下公式，下拉，
其中除E3单元格以外的公式，都是三键回车(点击公式编辑栏中的任意位置，按住 shift、ctrl 两个键，然后敲enter键 )；
D3=SUM(LARGE(IF(M小学!B$3:B$646=A3,M小学!E$3:E$646),ROW(INDIRECT("$1:$"&INT(0.9*(COUNTIF(M小学!B:B,A3)))+1))))
E3=TEXT(D3/(INT(0.9*(COUNTIF(M小学!B:B,A3)))+1),"0.00")
F3=COUNT(IF((M小学!B:B=汇总!A3)*(M小学!E$3:E$646>LARGE(IF(M小学!B$3:B$646=汇总!A3,M小学!E$3:E$646),INT(0.9*(COUNTIF(M小学!B:B,A3)))+2)),M小学!E:E))
G3=COUNT(IF((M小学!B:B=汇总!A3)*(M小学!E$3:E$646>84),M小学!E:E))

tgydslr

Book1.rar (22.29 KB, 下载次数: 3)

2014-8-14 15:02 上传

点击文件名下载附件

baksy

武林长风 发表于 2014-8-14 14:49
D3=SUM(LARGE(IF(M小学!B3:B646=汇总!A3,M小学!E3:E646),ROW(1:31)))，ROW(1:31)，不是所有的班都是31.M小 ...

公式更新；
D3=SUM(LARGE(IF(M小学!B$3:B$646=A3,M小学!E$3:E$646),ROW(INDIRECT("$1:$"&INT(0.9*(COUNTIF(M小学!B:B,A3)))+1))))
E3=TEXT(D3/(INT(0.9*(COUNTIF(M小学!B:B,A3)))+1),"0.00")
F3=COUNT(IF((M小学!B:B=汇总!A3)*(M小学!E$3:E$646>LARGE(IF(M小学!B$3:B$646=汇总!A3,M小学!E$3:E$646),INT(0.9*(COUNTIF(M小学!B:B,A3)))+2)),M小学!E:E))
G3=COUNT(IF((M小学!B:B=汇总!A3)*(M小学!E$3:E$646>84),M小学!E:E))

武林长风

前面几位的我已看了，下面是手动计算的结果，供核对。