项目十六: 管理班级成员的基本信息 ——Excel基本数据处理

一、项目情景 五月份快到了，按照惯例班委会要为五月份过生日的同学准备生日礼物了，班委会需要统计班上哪些同学五月份过生日，按照年龄由小到大的顺序对同学进行排列，统计过生日的同学都来自哪些省份，根据不同地方的习俗准备相应数量的生日礼物。 为了统计这些信息，班委会找出已建立的班级成员基本信息表，该如何对信息表进行操作以获得这些信息呢？

二、项目分析 有更好更快的方法吗？ 班级成员的信息较多且各有不同，若一个个判断统计，工作量大、容易出错且效率不高。 方法： 可利用Excel数据管理功能实现其操作。 有更好更快的方法吗？

二、项目分析 要统计出班级成员的信息，只需要完成以下几项任务： 1、统计五月份过生日的同学 对“出生年月”字段进行自动筛选，找出五月份过生日的同学。 2、按照年龄由小到大的顺序对同学进行排序 要对过生日的同学按年龄由小到大进行排列，需要按“出生年月”字段的降序进行排序。 3、依据籍贯统计要准备多少份不同的生日礼物 先按“籍贯”字段排序（升序或降序），再分类汇总，统计出各省份的人数，从而根据不同地方的习俗准备相应数量的生日礼物。

三、项目实施 （一）统计五月份过生日的同学 要统计五月份过生日的同学，就是在“班级成员基本信息表”中，只显示五月份出生的同学信息，将其他同学信息隐藏起来，这就要用到Excel的“筛选”功能。 数据筛选是使数据清单中只显示满足指定条件的数据记录，而将不满足条件的数据记录在视图中隐藏起来，取消筛选之后，所有数据都会重新出现。 Excel同时提供了“自动筛选”和“高级筛选”两种方法来筛选数据，前者适合简单条件，后者适合复杂条件。

三、项目实施 （一）统计五月份过生日的同学 步骤： ①在“班级成员基本信息表”工作表中,单击某一数据单元格，单击“数据”→“筛选”按钮，建立自动筛选器。 ②打开“出生日期”列筛选器。单击“日期筛选”→“期间所有日期” →“五月”命令，即可。

三、项目实施 ③筛选后的信息表如下图所示，五月份过生日的同学被筛选出来。

三、项目实施 （二）按照年龄由小到大的顺序对同学进行排序 从“班级成员基本信息表”中筛选出五月份过生日的同学之后，要按照年龄由小到大的顺序对同学进行排序，可以通过Excel的排序功能来实现。 排序分为简单排序和多条件排序。 简单排序：指按一个关键字排序，排序时单击该列任一单元格，在“数据”选项卡中单击升序或 降序按钮。 多条件排序：指按多个关键字排序，排序时单击数据清单中任一单元格，单击“数据”→“排序”按钮，在弹出的对话框中设置主要关键字、次要关键字。

三、项目实施 按照年龄由小到大的顺序对同学进行排序的步骤如下： 方法一： 在筛选后的信息表中，单击“出生年月”列任一数据单元格，单击“数据”→“降序”按钮，即可按出生年月的降序排序。

三、项目实施 方法二： ①在筛选后的信息表中，单击任一数据单元格，单击“数据”→“排序”按钮，弹出排序对话框。 ②在对话框中，设置主要关键字为“出生年月”，次序为“降序 ，确定即可。

三、项目实施 ③排序后的信息表如下图所示。

三、项目实施 （三）依据籍贯统计要准备多少份不同的生日礼物 本班同学来自全国各地，班委会想根据不同地方的习俗来准备生日礼物，五月份过生日的同学都来自哪些省份，要准备多少份不同的生日礼物呢？这就需要使用Excel的分类汇总功能来实现。 分类汇总的实质是先根据分类字段，对工作表中的记录进行排序，然后根据分类字段和汇总项计算每个分类的汇总值，并分级显示列表，以便为每个分类汇总显示和隐藏明细数据行。

三、项目实施 步骤： ①在“班级成员基本信息表”中，复制数据区域A1:I51，在“班级成员基本信息表” 后创建一个新表，表名为“各省份人数及总人数”，将数据粘贴到A1:I11区域。 ②在“各省份人数及总人数” 表中，按籍贯进行“升序”（或降 序）排列，以升序为例。 ③再单击“数据”→分类汇总 按钮，在弹出的对话框中，设置分 类字段为“籍贯”，汇总方式为“ 计数”，汇总项为“籍贯”，确定 即可。

三、项目实施 ④分类汇总后，记录行和汇总结果混在一起，为了更直观地查看汇总结果，单击工作表左上方的标记“ 2”后，只显示各省份的人数及总人数，其它记录行被隐藏。

四、项目拓展 通过对班级成员信息表进行管理，我们统计出了五月份过生日的同学信息。Excel的数据管理功能非常强大，除了刚才学习的这些内容，还有很多，如：条件格式、高级筛选等。下面，我们一起来学习这些内容。

四、项目拓展 （一）将总分在300分以上的分数设置格式 在“期末考试成绩表”工作表中，将总分在300分以上的分数设置为红色、加粗，需要使用Excel2010的条件格式功能。 步骤： ①在“期末考试成绩表”中，选中单元格区域“H3:H26”，单击“开始”→“条件格式”→突出显示单元格规则→其他规则…，弹出“新建格式规则”对话框。

四、项目拓展 ②在“新建格式规 则”对话框中设置规 则为：单元格值，大 于或等于，300。 ③再单击“格式…” 按钮，弹出“设置单 元格格式”对话框， 设置字体颜色为“红 色”，字形“加粗”。

四、项目拓展 ④设置条件格式后，300分以上的分数突出显示。

四、项目拓展 （二）统计成绩表中单科不及格学生 要求：从“期末考试成绩表”工作表中，筛选出单科 成绩不及格的学生。 要从“期末考试成绩表”工作表中，筛选出单科成绩 不及格的学生。使用Excel2010的“自动筛选”功能无法 一次完成该任务，因为自动筛选无法实现不同字段之间的 “或”运算，只能运用Excel2010的“高级筛选”功能来 解决。

四、项目拓展 步骤： （1）建立条件区域 将成绩表的列标题复制 到第28行，在对应字段下面 输入筛选条件，如右图所示。 （2）执行“高级筛选”功能 ①单击成绩表中任一数据单元格，再单击“数据”→“高级”按钮，弹出“高级筛选”对话框 。

四、项目拓展 ②在对话框中设置条件区域为“$A$28:$H$32”，其他为默认设置，确定即可筛选出符合条件的记录 。

五、项目小结 通过实施“管理班级成员的基本信息”项目，学 习了对班级成员信息表进行自动筛选，从而找出五月 份过生日的同学，通过排序对过生日的同学按年龄由 小到大排列，通过分类汇总来统计过生日的同学来自 哪些省份，各省份的人数。并通过项目拓展实现了设 置条件格式、数据的高级筛选等更复杂的数据管理功 能。 Excel2010可以对所有的Excel数据表进行管理， 实现对数据的有效利用。

六、创新作业 利用Excel数据管理功能，打开“素材光盘:\第16单元\作业素材\工资表（素材）.xlsx”工作簿，对“工资表”进行管理操作。具体要求如下： （1）按实发工资进行降序排列（2）将实发工资高于平均值的数字，设置格式为：绿填充色深绿色文本。 效果如下图所示，供参考。 排序并设置条件格式后的工资表

六、创新作业 （3）显示人事处基本工资大于或等于1000的工资信息。 效果如下图所示，供参考。 筛选后的工资表

六、创新作业 （4）求单位5月份应发工资的和，同时求出各科室应发工资的和。效果如下图所示，供参考。 分类汇总后的工资表