图4-12 排序
4 设置等级
1）在M2单元格输入“等级”，大家会看到这个单元格的格式自动和前面的表头单元格的格式相同，这是因为我们设置了表头的具体格式。
2）设置等级的条件
① 等级的条件如表4-2所示：
表4-2 等级的条件
等级 优秀 良好 一般 合格 不合格 分数段 600<=总分<=700 525<=总分<600 490<=总分<525 420<=总分<490 总分<420 ② 根据表4-2设置等级应该用到IF函数，选择M3单元格，输入“=IF(K3>=600,\优秀\良好\一般\合格\不合格\”，然后单击编辑栏中的“输入”（即“√”）按钮即可。
③ 利用填充柄向下拖动设置每个学生的等级。
5 按照项目二的要求对这些数据重新设置边框，设置效果如图4-13项目三效果图a所示。
图4-13 项目三效果图a
5 筛选
筛选出“计算机基础”大于或等于85分并且“大学语文”大于或等于75分的记录，并以“总分”字段降序排列。如果筛选条件不是按照“并且”（与）而是按照“或者”（或）的关系，在在输入条件时，将“>=85”和“>=75”输如在不同行上。
1）定义筛选条件
随便找一个空白单元格作为筛选条件的定义区域，将光标定位于D33，在这个单元格中输入“计算机基础”，然后在下一行D34中键入相应的条件“>=85”。同样，在E33中输入“大学语文”，在其下面的E34单元格中输入条件“>=75”。
2）筛选
单击数据清单中的任一单元格，选择【数据】→【筛选】→【高级筛选】菜单命令，在显示的“高级筛选”对话框中输入列表区域和条件区域，如图4-14所示，然后按“确定”，即可得到预期结果，如图4-15筛选结果图所示：
图4-14 高级筛选
图4-16 分类汇总
3）取消筛选
要取消自动筛选，则可单击【数据】→【筛选】→【全部显示】命令项。
图4-15 筛选结果图
6 按等级对总分的平均值进行分类汇总
1）先对“等级”列排序，如“等级”相同，则将“总分”以“降序”作为第二关键字，“学号”以“升序”作为第三关键字排序。
2）选择A2：M31，选择【数据】→【分类汇总】菜单命令，弹出“分类汇总”对话框，如图4-16分类汇总所示：
3）在“分类字段”下拉列表框中，选择字段名称“等级”；在“汇总方式”下拉列表框中，选择汇总方式“平均值”；在“选定汇总项”列表中，选择“总分”；选择“汇总结果显示在数据下方”复选框；单击“确定”按钮后即将汇总结果显示在数据下方，如图4-17分类汇总结果所示。
7 保存工作薄
单击【文件】→【另存为】命令，弹出“另存为”对话框，在文件名框中输入“S4_3_1学生相关信息.xls”。
图4-17 分类汇总结果
活动2 分析数据
活动步骤
1 打开已存在工作薄
在对应文件夹中找到“S4_3_1学生相关信息.xls”文件，双击其图标，打开工作薄。 2 单击“单科成绩分析”工作表标签，输入要分析的项目内容，如表4-3所示。
1）在B2：H2单元格分别输入“计算机基础”、“数学”、“英语”、“C语言”、“大学语文”、“体育”和“法律”；将这几个单元格设置为：字体：宋体、字号：10、字形：加粗。
2）在A1单元格输入“单科成绩统计分析”，合并A1：H1单元格，并设置字体：宋体、字号：16、字形：加粗。
3）在A3：A14单元格分别输入“应考人数”、“参考人数”、“缺考人数”、“最高分”、“最低分”、“平均分”、“90分以上人数” 、“80～90分人数” 、“70～80分人数” 、“60～
70分人数” 、“60分以下人数”，并将字号设置为10。
4）由于每门课的应考试人数相同，故合并B3：H3单元格。
表4-3 分析项目内容
1 2 3 4 5 6 7 8 9 10 11 12 13 14
应考人数 参考人数 缺考人数 最高分 最低分 平均分 90分以上人数 80～90分人数 70～80分人数 60～70分人数 60分以下人数
及格率
计算机基础
A
B
C 数学
D 英语
E C语言
F 大学语文
G 体育
H 法律
单科成绩统计分析
3 统计应考人数、参考人数和缺考人数 1）统计应考人数
① 选中B3单元格，选择【插入】→【函数】菜单命令，打开“插入函数”对话框，在“选择类别”中选择“统计”，在“选择函数”选项中选择“COUNTA”，单击“确定”按钮，弹出“函数参数”对话框。
② 在“函数参数”对话框中单击Value1框后面的
按钮，然后单击“学生综合成绩”工作
表标签，在当前的工作表中选择A3：A31，之后再单击按钮回到“函数参数”对话框，单击“确定”按钮，就在B3单元格求出来了应考人数，如图4-19项目三效果图b所示。
2）统计参考人数
① 选中B4单元格，选择【插入】→【函数】菜单命令，屏幕显示“粘贴函数”对话框，在弹出的函数列表中选择=COUNT()函数，单击“确定”按钮，弹出“函数参数”对话框。
② 在“函数参数”对话框中单击Value1框后面的
按钮，然后单击“学生综合成绩”工作
表标签，在当前的工作表中选择D3：D31，之后再单击按钮回到“函数参数”对话框，单击“确定”按钮，就在B4单元格求出来了参加“计算机基础”课程考试的人数。
③ 利用填充柄向右拖动求出每门课的参考人数。 3）统计缺考人数
① 由于每一门考试科目的应考人数都一样，即都在B3单元格，为了利用公式的复制直接求别的科目的缺考人数，故对于B3单元格的引用应该是绝对应用“$B$3”；缺考人数等于应考人数减去参考人数。
② 选中B5单元格，在编辑栏输入“=$B$3-B4”，就在B4单元格求出来了参加“计算机基础”课程的缺考人数。
③ 利用填充柄向右拖动求出每门课的缺考人数。 4 统计最高分、最低分和平均分
1）选中B6单元格，选择“常用”工具栏的“粘贴函数”按钮如图4-11所示，单击“最大值”命令，在编辑框中会出现“= MAX()”函数，在括号中输入参数“学生综合成绩!D3:D31”，然后按下“Enter”键即可求出“计算机基础”科目的最高分。
2）选中B7单元格，选择“常用”工具栏的“粘贴函数”按钮，单击“最小值”命令，在编辑框中会出现“= MIN()”函数，在括号中输入参数“学生综合成绩!D3:D31”，然后按下“Enter”键即可求出“计算机基础”科目的最低分。
3）由于存在缺考学生，所以不能直接利用“AVERAGE()”函数求平均值，应该先求出科目总分，再用总分除以参考人数，求总分可以用“SUM()”函数。
4）选中B8单元格，直接在单元格中输入“=SUM(学生综合成绩!D3:D31)/B4”，然后单击编辑栏中的“输入”（即“√”）按钮即可求出“计算机基础”科目的平均分。
5）利用填充柄分别向右拖动求出每门课的最高分、最低分和平均分。 5 统计各分数段人数
1）选中B9单元格，单击【插入】→【函数】菜单命令，打开“插入函数”对话框，在“选择类别”中选择“统计”，在“选择函数”选项中选择“COUNTIF”，单击“确定”按钮，弹出函数参数对话框。
2）在Range框中输入求不及格人数的范围“学生综合成绩!D3:D31”（可以通过单击Range框后面的单元格选择按钮来选择范围），然后在Criteria条件框中输入条件\（双引号必不可少，且为英文双引号），如图4-18 COUNTIF函数所示，单击“确定”按钮即可以求出“计算机基础”科目中大于90分的人数。
图4-18 COUNTIF函数
3）直接在B10 单元格输入“=COUNTIF(学生综合成绩!D3:D31,\”， 然后单击编辑栏中的“输入”（即“√”）按钮。
4）直接在B11 单元格输入“=COUNTIF(学生综合成绩!D3:D31,\”， 然后单击编辑栏中的“输入”（即“√”）按钮。
5）直接在B12 单元格输入“=COUNTIF(学生综合成绩!D3:D31,\”， 然后单击编辑栏中的“输入”（即“√”）按钮。
6）按照1）---2）的方法设置60分以下的学生人数，只不过将条件改为\。 7）利用填充柄分别向右拖动求出所有科目各分数段人数。 6 统计不及格率 1）将光标定位在B14单元格，输入“=(B4-B13)/B4”，然后单击编辑栏中的“输入”（即“√”）按钮，即可计算出“计算机基础”科目的及格率。
2）利用填充柄向右拖动求出所有科目的及格率。
3）设置B14：H14的单元格格式为小数位数是2位的百分比。 7 保存并关闭退出工作薄
单击【文件】→【另存为】命令，弹出“另存为”对话框，在文件名框中输入“S4_3_2学生