EXCEL统计函数中的数据分析

来源：百度知道 编辑：UC知道 时间：2024/05/22 18:19:15
原题如下：
学生成绩表
学号 姓名 语文 数学 英语 总分 名次
13 张13 93 96 92 281
4 张4 91 86 94 271
3 张3 92 86 85 263
2 张2 86 94 83 263
10 张10 76 88 95 259
1 张1 92 85 79 256
15 张15 79 82 94 255
6 张6 87 90 78 255
17 张17 94 84 76 254
11 张11 95 77 81 253
8 张8 91 75 85 251
5 张5 83 79 84 246
14 张14 76 88 81 245
7 张7 75 87 83 245
18 张18 77 84 82 243
9 张9 75 82 84 241
19 张19 94 70 69 233
16 张16 76 81 76 233
20 张20 75 70 69 214
12 张12 50 20 91 161
班平均分 82.85 80.2 83.05

问题：编写数据分析表格公式。试问 下表该如何填写 要具体方法

数据分析

分析条件 语文 数学 英语
90分以上
80--90分之间
70--80分之间
60--70分之间
60分以下

用countif函数统计各分数段的人数。
=COUNTIF(C$2:C$21,">=90") =COUNTIF(D$2:D$21,">=90") =COUNTIF(E$2:E$21,">=90")
=COUNTIF(C$2:C$21,">=80")-COUNTIF(C$2:C$21,">=90") =COUNTIF(D$2:D$21,">=80")-COUNTIF(D$2:D$21,">=90") =COUNTIF(E$2:E$21,">=80")-COUNTIF(E$2:E$21,">=90")
=COUNTIF(C$2:C$21,">=70")-COUNTIF(C$2:C$21,">=80") =COUNTIF(D$2:D$21,">=70")-COUNTIF(D$2:D$21,">=80") =COUNTIF(E$2:E$21,">=70")-COUNTIF(E$2:E$21,">=80")
=COUNTIF(C$2:C$21,">=60")-COUNTIF(C$2:C$21,">=70") =COUNTIF(D$2:D$21,">=60")-COUNTIF(D$2:D$21,">=70") =COUNTIF(E$2:E$21,">=60")-COUNTIF(E$2:E$21,">=70")
=COUNTIF(C$2:C$21,"<60") =COUNTIF(D$2:D$21,"<60") =COUNTIF(E$2:E$21,"<60")

用countif函数统计各分数段的