13.职工工资表的排序与筛选

课题十三：职工工资表的排序与筛选

课时安排：2学时

教学方法与教学手段：采用一体化教学，案例分析法。

教学环境：装有Windows操作系统和广播教学软件的电脑（1台/人）。

教学过程：

【复习旧课】

1、创建图表的步骤？

2、图表格式的设置。（学生到台前演示）

【新课导入】

EXCEL是一个全面的表格处理工具，它为用户提供了强大的数据分析功能。这些功能可以帮助用户完成日常工作中需要进行的各类数据分析。

【新课讲授】

项目：职工工资表的排序与筛选

1、项目提出

通过一个实际的项目，掌握如何对数据表进行排序和数据筛选操作。

2、项目要求与分析

要求：

（1）启动Excel2003软件，打开“素材.xls”文件。

（2）在Sheet9工作表中，用公式或函数计算每个职工的月总收入；用公式或函数计算应扣的所得税，应扣所得税规定如下：月收入在2000元以下为免税部分，超过部分按7.5%缴税；用公式计算出实发数，实发数=总收入—所得税；对数据清单按职称进行降序排序。

（3）在Sheet10工作表中，对数据清单按姓名的笔画进行升序排序。

（4）在Sheet9工作表中，对数据清单利用高级筛选，筛选出奖励补贴大于120的讲师或者奖励补贴大于180的教授的所有记录，条件区域：建在以A24单元格起始的位臵，筛选结果放至以A28单元格为左上角的区域。

（5）在Sheet10工作表中，对数据清单利用高级筛选，筛选出医生且年收入在

50000元以上的所有记录，筛选结果放至以A20单元格为左上角的区域。

（6）按原文件名保存文件。

分析：

该项目涉及到公式与函数sum、if函数，数据排序、高级筛选操作。

3、相关知识点

数据的排序（演示讲解）

数据的排序就是对一个指定区域内的数据清单，根据其一些列（行）中各个单元格取值的大小按递增或递减的规则将所有行（列）重新排列其前后（左右）顺序。

1）．排序方式：

（1）按单个关键字排序。

（2）3个及3个以内关键字（指字段名）的排序。

（3）3个以上关键字的排序：先从第2组开始排序，再排第1组。（难点）

关键字：性别，系别，专业，总成绩

1组 2组

（4）其它排序：除了按指定关键字进行排序外，还可按自定义序列排序、区分大小写排序、按列（行）排序、按字母（笔画）排序。

2）．练习：

打开“素材.xls”文件，完成以下排序练习：

（1）在Sheet1中按性别关键字进行排序，要求女生在前男生在后。

（2）在Sheet2中，先按系别递增排序，系别相同的记录按专业递增排序，专业相同再按总成绩从高分到低分排序。

（3）在Sheet3中，对数据清单先按性别进行递增排序，要求性别相同的情况下按系别递增排序，系别相同再按专业递增排序，若专业相同再按总成绩递减排序。

关键字：性别，系别，专业，总成绩

1组 2组

（4）在Sheet4中，将上例中的数据清单的记录按专业字段值为“应用、软件、微电子、自控”的顺序排列。

数据的筛选（演示讲解）

1）．自动筛选：

数据的筛选是从数据清单中选取出满足条件的记录。筛选的结果是将满足

条件的记录行显示出来，将不满足条件的记录所在的行隐藏起来，同时筛选出

来的行号以蓝色标示。

上机练习：打开“素材.xls”文件，在Sheet5中，完成下列操作后以原文

件名存盘：使用自动筛选选出“平均分”小于80或大于等于85分的微电子专业

的男生的所有记录。

2）．高级筛选：（难点）

高级筛选要建立条件区域，所谓条件区域由至少两行和若干列组成的一块区域。条件区域的建立方法如下：

（1）第一行为条件字段标题，不管有多少个条件，将条件所对应的字段名依次复制到条件区第一行相邻的不同单元格中（字段名也可直接输入）。注意条件字段名必须与数据清单的字段名相同。

（2）紧接的第二行及以下各行为条件，条件放臵的位臵必须和相应的字段名相对应，条件必须和字段名所对应值的数据类型一致。

（3）同一行的条件互为“与”（AND ）的关系，不同行之间为“或”（OR ）的关系。

（4）条件区域不能多选空白行，否则为无限制条件。

（5）条件中可以使用通配符“？”和“*”。

例如：

筛选数据清单中的计算机系总成绩大于等于320分的所有记录，写出该条件区域。

筛选数据清单中的数学成绩大于85分或者是英语成绩大于85分或者是政治成绩大于85分的所有记录，写出

该条件区域。

上机练习：打开“素材.xls ”文件：

1）在Sheet6中，利用高级筛选，筛选出1960年及以前出生的职称为工程师的所有记录，并将筛选结果复制到H1为左上角的区域。

2）在Sheet7中，使用高级筛选，筛选出得分大于80或者得分小于60的公务员的所有记录，筛选结果放至以G5单元格为左上角的区域。

3）对Sheet8中的数据清单使用高级筛选，筛选出英文名称中最后一个字母不为“p ”且第三个字母为“P ”的所有记录，筛选结果在原有数据表区域中显示。

4、项目实现

（1）启动Excel2003应用程序，打开素材文件。

（2）切换到Sheet9工作表：

求总收入：在G3单元格输入公式：=SUM(D4:F4)，并复制到G22。

求所得税：在H3单元格输入公式：=IF(G4<2000,0,(G4-2000)*7.5)，并复制到H22。

求实发工资：在I4单元格输入公式：=G3-H3，并复制到I22

排序：将光标定位在数据区域的任意单元格中执行菜单“数据”→“排序”命令，在“主要关键字”下拉列表框中选择“职称”，排序方式选择“降序”，单击“确定”按钮。

（3）切换到Sheet10工作表：将光标定位在数据区域的任意单元格中执行菜单“数据”→“排序”命令，在“主要关键字”下拉列表框中选择“姓名”，排序方式选择“降序”，单击“选项”按钮，在方法区域选择“笔画排序”，单击“确定”按钮。

（4）切换到Sheet9工作表，在A24单元格开始的地方，创建条件区域：

选择“数据—筛选—高级筛选命令”，设臵列表区域为：$A$2:$I$22，条件区域为：Sheet1!$A$24:$B$26，选择方式：将筛选结果复制到其他位臵，复制到：Sheet1!$A$28，确定。

（5）切换到Sheet10工作表，在I4单元格开始的地方，创建条件区域：

其他操作参照第4步。

（6）按原文件名保存文件。

复习巩固与作业要求

以一个学习小组为单位完成本次课堂上的此项目，该项目将作为作业检查的内容，每一个人都必须完全掌握。

教学小结

使用排序功能，可以对数据按照希望的顺序进行调整。使用筛选功能，可以将重要的数据立即显示出来。所以学生必须熟练的掌握EXCEL中的这两种数据分析功能。