3.5 班级考试成绩统计分析表
案例背景
上接3.3节，当某中学初三2班班主任从任课教师回收单科成绩表，汇总形成本班“成绩汇总表”后，需要对本班各科考试成绩进行进一步的统计分析，需要统计应考人数，实考人数，及格人数、不及格人数，平均分，最高分，及格率，良好率（考分不小于80），优秀率（考分不小于90）和标准差。
关键技术点
要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识 数字的“百分比”格式显示
●函数应用 COUNT函数、COUNTIF函数、STDEVP函数
●公式的设计与操作
最总效果展示
3.5.1创建班级考试成绩统计分析表
Step 1创建工作簿、重名工作表
创建工作簿“班级考试成绩统计分析表.xls”，然后将工作表重命名为“考试成绩统计分析表”，并删除多余工作表。
Step 2 输入原始成绩
将3.4节案例中图7复制粘贴到“成绩统计分析表”中，如下图
Step 3输入需统计项目名称
在单元格区域A44：A53分别输入“应考人数”，”应考人数”,” 及格人数”, “不及格人数”,” 平均分”,” 最高分”, “及格率”, “良好率”， “优秀率”和”标准差“。
Step4统计应考人数和实考人数
选中单元格C44，单击常用工具栏“插入函数“按钮，弹出“插入函数“对话框，在”或函数类别“选项框中选择”统计“，在”选择函数“选项框中选择”COUNTA“。
②单击确定按钮弹出“函数参数“对话框，在第一个参数框”Value1“中用光标选中本班学生姓名区域”B3:B43“，然后按键，将参数区域切换成绝对引用形式”$B$3:$B$43“,便于后面操作中向右填充公式。
③然后单击确定按钮，得到本班学生人数，也就是应考人数。
④选中单元格C45，单击常用工具栏“插入函数“按钮，弹出“插入函数“对话框，在”或函数类别“选项框中选择”统计“，在”选择函数“选项框中选择”COUNT “。
⑤单击确定按钮弹出“函数参数“对话框，在第一个参数框”Value1“中函数默认的函数区域为该单元格上面的数据区域”C3:C44“，直接在参数框中将C44改为C43或用光标选中”语文期中“成绩区域”C3:C43“也可。
⑥然后单击确定按钮，得到语文期中实考人数。
⑦选中单元格区域C44：C45，拖曳单元格C45右下角的填充柄至Q列，松开鼠标即可完成所有课程的应考和实考人数的统计。
Step4统计及格人数和不及格人数
①选中单元格C46，单击常用工具栏“插入函数“按钮，弹出“插入函数“，在”或函数类别“选项框中选择”统计“，在”选择函数“选项框中选择”COUNTIF“。
②单击确定按钮弹出“函数参数“对话框，在第一个参数框”Range“中用光标选中”语文期中“成绩区域”C3:C43“，在第二个参数框”“Criteria”中输入条件“>=60”。
③然后单击确定按钮，得到”语文期中“考试成绩的及格人数。
④选中单元格C47，单击常用工具栏“插入函数“按钮，弹出“插入函数“，在”或函数类别“选项框中选择”统计“，在”选择函数“选项框中选择”COUNTIF“。 ⑤单击确定按钮弹出“函数参数“对话框，在第一个参数框”Range“中用光标选中”语文期中“成绩区域”C3:C43“，在第二个参数框”“Criteria”中输入条件“<60”。
⑥然后单击确定按钮，得到”语文期中“考试的不及格人数。
Step5统计平均分和最高分
①选中单元格C48，单击常用工具栏“插入函数“按钮，弹出“插入函数“，在”或函数类别“选项框中选择”统计“，在”选择函数“选项框中选择”AVERAGE“。
②单击确定按钮弹出“函数参数“对话框，在第一个参数框”Number1“中直接将默认的数据区域修改为”C3:C43“。
③然后单击确定按钮，得到”语文期中“考试成绩的平均分。
④选中单元格C49，单击常用工具栏“插入函数“按钮，弹出“插入函数“，在”或函数类别“选项框中选择”统计“，在”选择函数“选项框中选择”MAX“。
⑤单击确定按钮弹出“函数参数“对话框，在第一个参数框”Number1“中直接将默认的数据区域修改为”C3:C43“。
⑥然后单击确定按钮，得到”语文期中“考试成绩的“最高分”。
Step6统计及格率
选中单元格C49，输入公式“=C46/C45”即可计算出本班“语文期中“考试成绩的及格率。 Step7统计良好率和优秀率
①选中单元格C51，单击常用工具栏“插入函数“按钮，弹出“插入函数“，在”或函数类别“选项框中选择”统计“，在”选择函数“选项框中选择”COUNTIF“。 ②单击确定按钮弹出“函数参数“对话框，在第一个参数框”Range“中用光标选中”语文期中“成绩区域”C3:C43“，在第二个参数框”“Criteria”中输入条件“>=80”。
③单击确定按钮，首先得到”语文期中“考试成绩的良好人数。
④然后修改公式，将光标直接单击编辑栏，将光标移动到最后，输入“ /C45“, 按键确认即可计算出”良好率“
⑤按照计算”良好率“的操作过程，只是把第二步的条件改为”>=90“计算出优秀率。
Step8设置“及格率“、”良好率“和”优秀率“为“百分比“形式
①选中单元格区域C50:C52,单击菜单“格式“→”单元格“弹出”单元格格式“对话框，在”分类“选项框中选择”百分比“，在”小数位数“列表框中单击上箭头按钮选择”1“。
②单击确定按钮即可完成数字的百分比形式显示。
Step9统计标准差
①选中单元格C53，单击常用工具栏“插入函数“按钮，弹出“插入函数“，在”或函数类别“选项框中选择”统计“，在”选择函数“选项框中选择”STVAP“。
②单击确定按钮弹出“函数参数“对话框，在第一个参数框”Number1“中直接将默认的数据区域修改为”C3:C43“。
③然后单击确定按钮，得到”语文期中“考试成绩的“标准差”。
Step10复制填充公式
选中其余各区域C46:C53，向右拖曳单元格C53右下角的填充柄至Q列松开鼠标即可完成其余成绩的相应项目的统计。
Step11调整列宽
适当调宽I列、P列和Q列的宽度，使得“英语期中”、“化学期末“和”化学总评“的’及格率”完全显示出来。
至此”班级考试成绩统计分析表”需要统计的内容全部统计完毕，班主任可以根据统计情况对本班学生各门课程的学习情况进行综合对比分析。
关键知识点讲解
1.COUNT函数
函数名称：COUNT
主要功能：返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 可以计算单元格区域或数字数组中数字字段的输入项个数。
使用格式：COUNT(value1,value2,...)
参数说明：Value1, value2, ... 为包含或引用各种类型数据的参数（1 到 30个），但只有数字类型的数据才被计算。
函数说明：
●函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。
●如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。如果要统计逻辑值、文字或错误值，请使用函数 COUNTA。
应用示例：
2.COUNTA函数
函数名称：.COUNTA
主要功能：返回参数列表中非空值的单元格个数。利用函数 COUNTA 可以计算单元格区域或数组中包含数据的单元格个数。
使用格式：COUNTA(value1,value2,...)
参数说明：Value1, value2, ... 为所要计算的值，参数个数为 1 到 30 个。在这种情况下，参数值可以是任何类型，它们可以包括空字符 ("")，但不包括空白单元格。如果参数是数组或单元格引用，则数组或引用中的空白单元格将被忽略。如果不需要统计逻辑值、文字或错误值，请使用函数 COUNT。
3. COUNTIF函数
函数名称：COUNTIF
主要功能：计算区域中满足给定条件的单元格的个数。
使用格式：COUNTIF(range,criteria)
参数说明：
Range 为需要计算其中满足条件的单元格数目的单元格区域。
Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、"32"、">32" 或 "苹果"。
函数说明：
Microsoft Excel 提供其他函数，可用来基于条件分析数据。例如，若要计算基于一个文本字符串或某范围内的一个数值的总和，可使用 SUMIF 工作表函数。若要使公式返回两个基于条件的值之一，例如某指定销售量的销售红利，可使用 IF 工作表函数。
应用示例：
4. STDEVP函数
函数名称：STDEVP
主要功能：返回以参数形式给出的整个样本总体的标准偏差。标准偏差反映相对于平均值 (mean) 的离散程度。
使用格式：STDEVP(number1,number2,...)
参数说明：Number1,number2,... 为对应于样本总体的 1 到 30 个参数。也可以不使用这种用逗号分隔参数的形式，而用单个数组或对数组的引用。
●文本和逻辑值（TRUE 或 FALSE）将被忽略。如果不能忽略逻辑值和文本，则请使用 STDEVPA 工作表函数。
函数说明：
●函数 STDEVP 假设其参数为整个样本总体。如果数据代表样本总体中的样本，应使用函数 STDEV 来计算标准偏差。
●对于大样本容量，函数 STDEV 和 STDEVP 计算结果大致相等。
●此处标准偏差的计算使用“有偏差”和“n”方法。
●函数 STDEVP 的计算公式如下：
其中 x 为样本平均值 AVERAGE(number1,number2,…)，n 为样本大小。
应用示例：
扩展知识点讲解
1.COUNTBLANK函数
函数名称：COUNTBLANK
主要功能：计算指定单元格区域中空白单元格的个数。
使用格式：COUNTBLANK(range)
参数说明：
Range 为需要计算其中空白单元格个数的区域。
函数说明：
即使单元格中含有返回值为空文本 ("")的公式，该单元格也会计算在内，但包含零值的单元格不计算在内。
应用示例：
|
