课题十二：公司商品出入库管理
【课时安排】2学时
【教学方法与教学手段】采用一体化教学，案例分析法
【教学环境】装有office2003办公软件和广播教学软件的电脑（1台/人）【教学重点】
1、掌握数据清单中数据的排序方法
2、掌握数据清单中数据的自动筛选和高级筛选方法
3、掌握数据清单中数据的分类汇总及复杂分类汇总的方法
【教学难点】
1、数据的排序方法
2、数据的分类汇总方法
【教学过程】
一、复习旧课
1、单元格格式设置中自定义数据格式的方法？
2、函数的嵌套使用方法？（并举例说明）
二、新课导入
在实际生活工作中，常常通过对excel表格中的数据进行排序和筛选，得到一些需要的数据。
下面通过某公司出入库存表的管理来学习。
三、新课讲授
项目名称：公司商品出入库管理
1、项目提出（老师完成）
本项目以某公司商品入库管理为例介绍数据清单中数据的排序方法、数据的自动筛选和高级筛选方法、数据清单中数据的分类汇总及复杂分类汇总的方法。
2、相关知识点简介（老师完成）
（一）数据的排序（教师演示讲解）
数据的排序就是对一个指定区域内的数据清单，根据其一些列（行）中各个单元格取值的大小按递增或递减的规则将所有行（列）重新排列其前后（左右）顺序。
1．排序方式：
（1）按单个关键字排序。
（2）3个及3个以内关键字（指字段名）的排序。
（3）3个以上关键字的排序：先从第2组开始排序，再排第1组。
关键字：性别，系别，专业，总成绩
1组 2组
其它排序：除了按指定关键字进行排序外，还可按自定义序列排序、区分大小写排序、按列（行）排序、按字母（笔画）排序。
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分的所有记录，写出该条件区域。
示例：（教师提问学生回答或演示）
只写出条件区域：
1）在Sheet6中，利用高级筛选，筛选出1960年及以前出生的职称为工程师的所有记录，并将筛选结果复制到H1为左上角的区域。
2）在Sheet7中，使用高级筛选，筛选出得分大于80或者得分小于60的公务员的所有记录，筛选结果放至以G5单元格为左上角的区域。
3）对Sheet8中的数据清单使用高级筛选，筛选出英文名称中最后一个字母不为“p”且第三个字母为“P”的所有记录，筛选结果在原有数据表区域中显示。
（三）数据清单中数据的分类汇总及复杂分类汇总的方法。
（四）数据透视表的操作方法与运用。
3、项目要求（学生完成）
要求：
打开“素材.xls”文件，在Sheet9工作表中完成（1）-（7）题：
（1）运用公式计算出每样商品的购进总价和每样商品剩余数量。
（2）运用sumif函数分别求出三种不同手机的平均进货价格。
（3）以“购进数量”字段为“主要关键字”，按降序对记录进行排序。
（4）以“购进单价”字段为“主要关键字”、“购进数量”字段为“次要关键字”，按升序对记录进行排序。
（5）创建自定义序列“手机，笔记本，投影机，多媒体电脑”。
（6）以上一步中所创建好的自定义序列“手机，笔记本，投影机，多媒体电脑”为要求顺序对记录进行排序。
（7）运用自动筛选方法，筛选出购进时间为“2006-3-4”的所有记录。
打开“素材.xls ”文件，在Sheet10工作表中完成（8）-（11）题：
（8）运用高级筛选方法，筛选出产地为广州和东莞的记录。
条件区域：放在A21单元格起始的位臵，筛选结果放在以A27单元格为左上角的区域。
（9）利用数据透视表求出手机产品不同型号的平均购进价格和产地。
布局和效果如下图所示：数据透视表显示的位臵在新建工作表中。
效果
布局
（10）分类汇总操作，“商品名称“为分类字段、汇总方式为“求和”、汇总项为“购进数量”、“卖出数量”、“剩余商品数量”。
（11）在上一步分类汇总的基础上，进行第二次的分类汇总。
以“商品名称”为分类字段，对“购进单价”进行求最大值汇总。
4、项目分析与实现（学生团队和老师完成）
该项目涉及到数据排序（四种排序情况）、数据自动/高级筛选、分类汇总、等操作。
难点是自定义序列的数据排序和高级筛选，要重点讲解。
（一）项目分析与实现
打开“素材.xls”文件，单击Sheet9工作表中：
（1）购进总价＝购进单价*购进数量，单击G3单元格，输入公式：=E3*F3；剩余商品数量＝购进数量-卖出数量，单击L3单元格，输入公式： =F3-I3。
（2）不同手机的平均进货价格＝不同手机的平均进货价格总和/不同手机进货数量总和。
单击D22单元格，输入公式：并复制公式求出其他型号的手机平均进货价格。
=SUMIF($C$3:$C$18,C22,$G$3:$G$18)/SUMIF($C$3:$C$18,C22,$F$3:$F$18) （3）单多键字排序：单击数据区任一单元格，选择菜单栏“数据”－“排序”，如以下对话框设臵：
（4）多关键字排序：在单关键字排序的基础上选择，添加多个关键字，操作如下：
单击数据区任一单元格，选择菜单栏“数据”－“排序”，如以下对话框设臵：
再单击按钮，添加第二个条件：如下图所示：
（5）要先打开“自定义序列”对话框，可以使用以下两种方法：
1)文件－选项－高级－常规－编辑自定义列表
2)开始－排序和筛选－自定义排序，打开“次序”下拉框，自定义序列
在打开的“自定义序列”对话框中输入题目所要求排序的内容，并“添加”：
（6）单击数据区任一单元格，选择菜单栏“数据”－“排序”，主关键字选择“商品名称”，次序选择“自定义序列”，选择（5）题所创建的自定义序列内容来排序，如下图所示设臵：
（7）自动筛选：选择“数据”－“排序和筛选”—“筛选”，在数据表中右边的下拉按钮中选择如下所示：筛选出购进时间为“2006-3-4”
单击Sheet10工作表，
（8）在H21：H23单元格中，创建条件：
高级筛选：选择“数据”－“排序和筛选”—“高级”，按如下图所示设臵：
（9）数据透视表：选择“插入”—“表格”—“数据透视表”，如下图所示
确定后，如下图所示设臵：分别拖动相应的字段至相应的区域：
（10）—（11）分类汇总：注意要先排序，按“商品名称”排序，再完成分类汇总。
多次分类汇总，如果汇总方式不相同，要记得取消替换当前汇总，以保留多次分类汇总后的结果。
操作如下：
1）要按“商品名称”排序，
2）进行第（10）题的分类汇总：将光标定位在数据区域中任一单元格，再选择“数据”—“分级显示”—“分类汇总”；如下第一张图所示设臵：3）进行第（11）题的分类汇总：再选择“分类汇总”，如下第二张图所示
（二）指导答疑
集中解决学生在各个子项目以及总体汇合设计调试中存在的问题，指导完成项目实训，突破该项目的各个难点。
5、项目验收及评价（老师完成）
（1）结合学生完成的情况进行点评，在下次课上通过抽查知识点给出考核成绩，（2）展示学生优秀项目完成效果，激发学生学习热情。
【作业要求】
以七人学习小组为单位完成本次课堂上的三个项目，三个项目都作为作业检查的内容，每一个人都必须完全掌握。
【教学小结】
本次主要讲解了数据排序（四种排序情况）、数据自动/高级筛选、分类汇总、等操作。
难点是自定义序列的数据排序和高级筛选，要重点讲解。
学生掌握得较好。