全屏显示专题章节

案例:期末考试结束后，老师选取了其中3个班24名学生的4门课程（数学、英语、计算机和物理）的考试成绩（各门科目的成绩为满分100分），按照班级来对各科成绩进行统计和分析。

要求使用Exce l2007完成所有的统计和分析任务，即计算每个同学的总分并按总分降序排列；设置自动筛选和高级筛选；分类统计出每个班级的总分之和；分类统计出每个班级男生、女生的数学和计算机的平均值；计算成绩等级（平均分大于75为优秀，小于60分为不及格，其余的为良），和优秀率（＝优秀人数/总人数）。

1）创建数据清单

启动Exce l2007，新建一个名为“班级成绩统计表.xlsx”的文件。

（2）建立数据清单并格式化

按照如图4.71所示输入数据，注意:“学号”字段作为文本型数据处理，用前面所讲三种方法之一来输入。首先将C2:K2单元格区域合并及居中，书写标题“班级成绩统计表”，设置成楷体、16号，然后在C3:K27单元格区域中填入各个学生的单科成绩和表格相关信息，并添加边框，效果如图4.71所示。

在白翔的“总分”单元格中输入公式“＝SUM（G4:J4）”，按回车键得出计算结果，然后在K4单元格上利用“填充柄”功能计算其他23名同学的总分，计算结果如图4.72所示。

要求在3个班级中按照总分由高到低的顺序排列。选中需要排序数据列中的任意一个单元格，然后选择“开始”选项卡的“编辑”组中的“排序和筛选”，单击其下拉列表中的“降序”命令即可。完成排序后的结果如图4.73所示。

单击数据清单中任意一个单元格后，选择“数据”选项卡的“排序和筛选”组中的“筛选”命令，在数据清单第一行的各列中将分别显示出一个下拉按钮。单击“计算机”列的下拉按钮，选择“自定义筛选…”命令，弹出“自定义自动筛选方式”对话框，然后输入计算机成绩在80～95分的筛选条件，如图4.74所示，最后单击“确定”按钮即可。

首先在与数据清单至少隔一行的另一区域设置高级筛选条件区域，如图4.75所示。

然后将光标定位于数据清单中的任意一个单元格内，选择“数据”选项卡的“排序和筛选”组中的“高级”命令，弹出“高级筛选”对话框，再在该对话框中选择好数据区域、条件区域和筛选结果的存放位置，最后单击“确定”按钮即可显示出筛选结果。筛选结果如图4.76所示。

老师要分类统计出每个班级的总分之和，因此分类字段是“班级”，在分类汇总之前必须对分类的字段“班级”进行排序，如图4.77所示。

将光标定位在数据清单的任意一个单元格中，选择“数据”选项卡的“分级显示”组中的“分类汇总”命令，弹出“分类汇总”对话框，如图4.78所示。

选择好分类字段、汇总方式、汇总项后，单击“确定”按钮，即得到分类汇总的结果，如图4.79所示。

老师需要分类统计出每个班级男生、女生的数学和计算机的平均值，因此选择数据透视表来进行制作。首先，将光标定位在数据清单的任意一个单元格中，单击“插入”选项卡的“表”组中的“数据透视表”命令的下拉按钮，在其下拉列表中选择“数据透视表”，弹出“创建数据透视表”对话框，然后在对话框中设置“请选择要分析的数据”和“选择放置数据透视表的位置”内容后单击“确定”按钮。此时生成数据透视表，但没有内容，可利用“数据透视表字段列表”对话框来设置透视表数据的布局。再在“选择要添加到报表的字段”中选择“班级”“性别”“计算机”和“数学”，其中行标签为“班级”“计算机”和“数学”，列标签为“性别”，报表筛选没有设置，汇总方式系统默认为“求和”，可以通过单击各个“数值”右边的下拉箭头选择“值字段设置…”，在“值字段设置”对话框中修改汇总方式和汇总项名称后确定即可。上面数据清单中的数据按照要求的布局建立的数据透视表，如图4.80所示。

7）计算成绩等级

成绩等级按照“平均分大于75分为优秀，小于60分为不及格，其余的为良”的条件制作，使用IF函数来完成。

根据成绩等级条件可得出在白翔等级的单元格L4中应输入公式“＝IF（K4＞300，＂优秀＂，IF（K4＜260，＂中＂，＂良＂））”，按回车键得出结果为“良”。在L4单元格上利用填充柄向下填充可计算出余下23名学生的成绩等级，如图4.81所示。

计算方法:优秀率＝优秀人数/总人数。使用COUNTIF函数计算出优秀人数，使用COUNT函数计算出总人数。优秀率计算公式为:“＝COUNTIF（K4:K27，＂＞300＂）/COUNT（K4:K27）”，将结果设置成百分比的形式，并保留两位小数。计算结果如图4.82所示。