实例7 期末成绩处理表——数据的排序、筛选与分类汇总
【学习目标】
·掌握Excel 表格中数据的排序
·掌握Excel 表格中数据的筛选
·掌握Excel 表格中数据的分类汇总
【任务描述】
本校高二学生第二学月月考结束,学校需要对每个班的成绩进行分析汇总。 请同学们根据要求对月考成绩进行排序和汇总,以便更清楚地了解学生学习情况。
【操作步骤】
1.启动Excel2010,在本书配套素材中打开模块二\实例7\素材\恒大中学高二年级月考成绩统计表.xlsx 文件。
2.选择sheet1 工作表,用求和函数对所选单元格数据进行求和,只需选中要计算的区域,自动计算结果即能得到,如图7-1 所示。 其余各项合计可以利用自动填充完成。 选中G3 单元格,然后单击“开始”选项卡中“编辑”组中的“自动求和”,选择常用函数sum(),最后选择求和数值范围C3:F3 进行求和。 其余各项合计利用自动填充功能完成,如图7-2所示。
图7-1 选中求和函数
图7-2 利用自动填充求和
3.选择sheet1 工作表,用平均值函数对所选单元格数据进行平均值的计算,只需选中要计算的区域,自动计算平均值即能得到,如图7-3 所示。 其余各项合计可以利用自动填充完成。 选中C15 单元格,然后单击“开始”选项卡中“编辑”组中的“平均值”,选择常用函数AVERAGE(),最后选择求平均值的值范围C3:C14 进行计算。 其余各项合计利用自动填充功能完成,如图7-4所示。
图7-3 选中求平均值函数
图7-4 利用自动填充求平均值
4.选择sheet2 工作表,单击数据中的任意位置,然后单击“开始”选项卡中“编辑”组中的“排序和筛选”,再单击“筛选”,如图7-5 所示。然后单击“语文”科目旁边的小三角
,选择“数字筛选”中的“大于或等于”,设置筛选条件。 其他的科目按照以上相同方式进行操作。如图7-6 和图7-7 所示。
图7-5 选择筛选
图7-6 选择大于或等于
图7-7 自定义筛选方式
5.选择Sheet3 工作表,选中i3 单元格,然后单击“数据”选项卡“数据工具”组中的“合并计算”,如图7-8 所示。 然后在对话框中选择“函数”中的“平均值”,在“引用位置”中单击“
”按钮选择数据,然后在“标签位置”中勾选“最左列”。 如图7-9 所示。
图7-8 选择合并计算
图7-9 设置合并计算参数
6.选择Sheet4 工作表,首先对数据以“班级”降序的方式进行排序。 单击数据中的任意位置,然后单击“数据”选项卡“分级显示”组中的“分类汇总”,如图7-10 所示。 然后在对话框中选择“分类字段”中的“班级”,选择“汇总方式”中的“平均数”为分类字段,在“选定汇总项”中勾选“语文、数学、英语、政治”几个选项,如图7-11 所示。 完成各科成绩平均值的分类汇总。
图7-10 选择分类汇总
图7-11 设置分类汇总参数
【知识链接】
1.数据排序的规则。
EXCEL 允许对字符、数字等数据按大小顺序进行升序或降序排列,要进行排序的数据称之为关键字。 不同类型的关键字的排序规则如下:
·数值:按数值的大小。
·字母:按字母先后顺序。
·日期:按日期的先后。
·汉字:按汉语拼音的顺序或按笔画顺序。
·逻辑值:升序时FALSE 排在TRUE 前面,降序时相反。
·空格:总是排在最后。
2.数据排序的筛选。
数据筛选是数据表格管理的一个常用项目和基本技能,通过数据筛选可以快速定位符合特定条件的数据。
·自动筛选:打开需要筛选的表格EXCEL 文件,使用鼠标点击单元格定位到含有数据的随意一单元格区域,点击菜单栏-数据-筛选-自动筛选。
·筛选方式:点击数字筛选可以为含有大量数据的表格文件进行特定的数据筛选或某个数值区间的数据筛选。
·高级筛选:点击菜单栏-数据-筛选-高级筛选,以此打开高级筛选的菜单。
·列表区域和条件区域:打开高级筛选后可输入单元格范围或使用鼠标勾选需要的区域,分别勾选列表区域和条件区域。
3.数据的分类与汇总。
在对数据进行分析时,常常需要将相同类型的数据统计出来,这就是数据的分类与汇总。 在对数据进行汇总之前,应特别注意的是:首先必须对要汇总的关键字进行排序。
【课后习题】
某图书销售商有三家分店,临近年底,老板需要对三家书店进行盘点,对各个书店销售情况进行分析汇总。 请同学们根据要求对书店的销售数据进行排序和汇总,以便更清楚地了解书店的销售情况。
1.在Sheet1 工作表中以“类别”为主要关键字、“单价”为次要关键字进行降序排序,并对相关数据应用“数据条”中的“绿色数据条”渐变填充的条件格式,实现数据的可视化效果。
2.在Sheet2 工作表筛选“销售数量(本)”大于5 000 或小于4 000 的记录。
3.在Sheet3 工作表中对“文化书店图书销售情况表”“西门书店图书销售情况表”和“中原书店图书销售情况表”的表格进行“求和”的合并计算操作。
4.在Sheet4 工作表以“类别”为分类字段,对“销售数量(本)”进行求“平均值”的分类汇总。