学生考试成绩统计函数

学生成绩统计函数

总分:=SUM(b1:b50)

总分:=SUM()

平均分:=A VERAGE(b1:b45)

及格率:=COUNTIF(b1:b45,">=60")/COUNT(b1:b45)*100

优秀率:=COUNTIF(b1:b45,">=80")/COUNT(b1:b45)*100

及格人数:=COUNTIF(b1:b45,">=60")

优秀人数:=COUNTIF(b1:b45,">=80")

成绩排名:=RANK(D3,$D$3:$D$45,0)。

（C列为学生成绩，D列为学生成绩排名，0为降序排列，若是1则为升序排列）

参考人数=COUNTA(A3:A45):

男生人数: =CONCATENA TE(COUNTIF($B$3:$B$45,"男"),"人")或=COUNTIF(B$3:B$45,"男")

女生人数: =CONCATENA TE(COUNTIF($B$3:$B$45,"女"),"人")或=COUNTIF(B$3:B$45,"女")

自动平均分: =A VERAGEA(C3:C45)

最高分: =MAX(C3:C45)

最低分: =MIN(C3:C45)

100分: =COUNTIF(C3:C45,"=100")

90~100（不含100）: =COUNTIF(C3:C45,">=90")-COUNTIF(C3:C45,">=100")

80~90（不90）: =COUNTIF(C3:C45,">=80")-COUNTIF(C3:C45,">=90")

70~80（不含80）: =COUNTIF(C3:C45,">=70")-COUNTIF(C3:C45,">=80")

60~70（不含70）: =COUNTIF(C3:C45,">=60")-COUNTIF(C3:C45,">=70")

50~60（不含60）: =COUNTIF(C3:C45,">=50")-COUNTIF(C3:C45,">=60")

40~50（不含50）: =COUNTIF(C3:C45,">=40")-COUNTIF(C3:C45,">=50")

40以下（不含40）: =COUNTIF(C3:C45,"<40")

升序：=SMALL(Sheet1!A:A,ROW())

降序：= LARGE (Sheet1!A:A,ROW())