1、项目五项目五 ExcelExcel的数据处理功能的数据处理功能知识讲解 1.单元格地址、名称和引用1）单元格地址工作簿中的基本元素是单元格，单元格中包含文字、数字或公式。单元格在工作簿中的位置用地址标识，由列号和行号组成。例如，A3表示A列第三行。一个完整的单元格地址，除了列号和行号以外，还要指定工作簿名和工作表名。其中，工作簿名用方括号“”括起来，工作表名与列号、行号之间用叹号“！”隔开。例如，“员工工资.xlsxsheet1!A1”表示员工工资工作簿中的sheet1工作表的A1单元格。项目五项目五 ExcelExcel的数据处理功能的数据处理功能2）单元格名称在Excel数据处理过程中，经
2、常要对多个单元格做相同或类似的操作，此时可以利用单元格区域或单元格名称来简化操作。当一个单元格或单元格区域被命名后，该名称会出现在“名称框”的下拉列表中，如果选中所需的名称，则与该名称相关联的单元格或单元格区域就会被选中。例如，在该任务的“工资表”工作表中为员工姓名所在单元格区域命名，操作方法如下。项目五项目五 ExcelExcel的数据处理功能的数据处理功能方法一：选中所有员工“姓名”单元格区域（B4：B14），在“编辑栏”左侧的“名称框”中输入名称“姓名”，按Enter键完成命名。方法二：在“公式”选项卡的“定义的名称”组中单击“定义名称”下拉按钮，在其下拉列表中选择“定义名称”命令，打开
3、“新建名称”对话框，如图5-1所示，在“引用位置”文本框中对要命名的单元格区域进行正确引用，在“名称”文本框中输入命名的名称，单击“确定”按钮即完成命名。项目五项目五 ExcelExcel的数据处理功能的数据处理功能图5-1 “新建名称”对话框 项目五项目五 ExcelExcel的数据处理功能的数据处理功能要删除已定义的单元格名称，在“公式”选项卡的“定义的名称”组中单击“名称管理器”按钮，打开“名称管理器”对话框，如图5-2所示，选中名称“姓名”，单击“删除”按钮即可删除。图5-2 “名称管理器”对话框项目五项目五 ExcelExcel的数据处理功能的数据处理功能利用“名称管理器”对话框还可
4、以完成新建名称、编辑名称操作。提 示项目五项目五 ExcelExcel的数据处理功能的数据处理功能3）单元格引用单元格引用的作用是标识工作表中的一个单元格或一组单元格，以便说明要使用哪些单元格中的数据。Excel 2007中提供了3种单元格引用。（1）相对引用。（2）绝对引用。（3）混合引用。项目五项目五 ExcelExcel的数据处理功能的数据处理功能 2.公式公式是对工作表中的数值执行计算的等式，公式以等号（=）开头。公式一般包括函数、引用、运算符和常量。1）运算符及优先级运算符有4种类型。（1）算术运算符：加（+）、减（-）、乘（*）、除（/）、乘幂（）、百分比（%）、括号（）等，算术运
5、算的结果为数值型。（2）比较运算符：等于（=）、大于（）、小于（=）、小于等于（=），不等于（），比较运算的结果为逻辑值True或False。（3）文本连接运算符：&，连接一个或多个文本。例如，“辽宁”“沈阳”的结果为“辽宁沈阳”。项目五项目五 ExcelExcel的数据处理功能的数据处理功能（4）引用运算符：冒号（：）、逗号（，）、空格（）。其中，冒号用于表示一个连续的单元格区域，如A1：C3；逗号用于将多个单元格区域合并成一个引用，如AVERAGE（A1:A3，C1）表示计算单元格区域A1:A3和单元格C1中包含的所有单元格（A1、A2、A3、C1）的平均值；括号用于处理区域中互相重叠的部
6、分，如AVERAGE（A1:B3 B1:C3）表示计算单元格区域A1:B3和单元格区域B1:C3相交部分单元格（B1、B2、B3）的平均值。项目五项目五 ExcelExcel的数据处理功能的数据处理功能项目五项目五 ExcelExcel的数据处理功能的数据处理功能运算符的优先级如表5-1所示。项目五项目五 ExcelExcel的数据处理功能的数据处理功能2）输入公式Excel公式是由数字、运算符、单元格引用、名称和内置函数构成的。具体操作方法为：选中要输入公式的单元格，在“编辑栏”中输入“=”后，再输入具体的公式，单击“编辑栏”左侧的输入按钮或按Enter键，完成公式输入。项目五项目五 Exc
7、elExcel的数据处理功能的数据处理功能 3）复制公式方法一：选中包含公式的单元格，利用“复制”和“粘贴”命令完成公式复制。方法二：选中包含公式的单元格，拖动填充柄选中所有需要运用此公式的单元格，释放鼠标后，公式即被复制。项目五项目五 ExcelExcel的数据处理功能的数据处理功能 3.函数函数是预先定义好的内置公式，是一种特殊的公式，可以完成复杂的计算。1）函数的格式函数名（参数1，参数2，）其中，参数可以是数字、文本、逻辑型数据、单元格引用或表达式等。还可以是常量、公式或其他函数。所有在函数中使用的标点符号，若不是作为文本输入的，都必须是英文符号。项目五项目五 ExcelExcel的数
8、据处理功能的数据处理功能（1）选择要输入函数的单元格，在“编辑栏 ”中输入“=”，再输入具体的函数，按Enter键完成函数的输入。（2）选择要输入函数的单元格，单击“编辑栏”左侧的“插入函数”按钮或在“公式”选项卡的“函数库”组中单击“插入函数”按钮，打开“插入函数”对话框，选择需要的函数，单击“确定”按钮，打开“函数参数”对话框，设置需要的函数参数，单击“确定”按钮即可完成函数的输入。2）函数的输入项目五项目五 ExcelExcel的数据处理功能的数据处理功能3）函数的嵌套在某些情况下，可能需要将某函数作为另一函数的参数，这就是函数的嵌套。Excel 2007中函数的嵌套可达64层。项目五项
9、目五 ExcelExcel的数据处理功能的数据处理功能 4.数据排序Excel 2007可以对一列或多列中的数据按文本（升序或降序）、数字（升序或降序）以及日期和时间（升序或降序）进行排序。还可以按自定义序列或格式（包括单元格颜色、字体颜色和图标集）进行排序。大多数排序操作都是针对列进行的。数据排序一般分简单排序、复杂排序和自定义排序。项目五项目五 ExcelExcel的数据处理功能的数据处理功能1）简单排序简单排序是指设置一个排序条件进行数据的升序或降序排序。方法是：单击条件列字段中的任意单元格，在“数据”选项卡的“排序和筛选”组中单击“升序”或“降序”按钮即可。项目五项目五 ExcelEx
10、cel的数据处理功能的数据处理功能 2）复杂排序复杂排序是指按多个字段进行数据排序的方式。方法是：在“数据”选项卡的“排序和筛选”组中单击“排序”按钮，打开“排序”对话框，在该对话框中可以设置一个主要关键字、多个次要关键字，每个关键字均可按“升序”或“降序”进行排列。项目五项目五 ExcelExcel的数据处理功能的数据处理功能可以使用自定义序列按用户定义的顺序进行排序。方法是：在“排序”对话框中，选择要进行自定义排序的关键字，在其对应的“次序”下拉列表框中选择“自定义序列”选项，打开“自定义序列”对话框，选择或建立需要的排序序列即可。3）自定义排序项目五项目五 ExcelExcel的数据处理
11、功能的数据处理功能 5.数据筛选筛选是指找出符合条件的数据记录，即显示符合条件的记录，隐藏不符合条件的记录。1）自动筛选自动筛选是指工作表中只显示满足给定条件的数据。进行自动筛选的方法是：选中任意单元格，在“数据”选项卡的“排序和筛选”组中单击“筛选”按钮，各标题名右侧出现下拉按钮，说明对单元格数据启用了“筛选”功能，单击下拉按钮，可以显示列筛选器，在此可以进行筛选条件设置，完成后在工作表中将显示筛选结果。项目五项目五 ExcelExcel的数据处理功能的数据处理功能 2）自定义筛选当需要对某字段数据设置多个复杂筛选条件时，可以通过自定义自动筛选的方式进行设置。在该字段的列筛选器中选择“数字筛
12、选”命令的下一级菜单中的“自定义筛选”命令，打开“自定义自动筛选方式”对话框，对该字段进行筛选条件设置，完成后工作表中将显示筛选结果。项目五项目五 ExcelExcel的数据处理功能的数据处理功能3）高级筛选一般来说，自动筛选和自定义筛选都不是太复杂的筛选，如果要设置复杂的筛选条件，可以使用高级筛选。高级筛选结果可以显示在源数据表格中，不符合条件的记录则被隐藏起来，也可以在新的位置显示筛选结果而源数据表不变。项目五项目五 ExcelExcel的数据处理功能的数据处理功能4）清除筛选如果需要清除工作表中的自动筛选和自定义筛选，可以在“数据”选项卡的“排序和筛选”组中单击“清除”按钮，清除数据的筛
13、选状态。如果再单击“筛选”按钮，则取消了启用筛选功能，即删除列标题右侧的下拉按钮，使工作表恢复到初始状态。项目五项目五 ExcelExcel的数据处理功能的数据处理功能筛选条件中可以使用通配符“?”和“*”，其中“?”代表一个字符，“*”代表多个字符。例如，筛选条件是“王*”，表示要筛选出所有姓“王”的人的记录。提 示项目五项目五 ExcelExcel的数据处理功能的数据处理功能 6.分类汇总分类汇总是指对某个字段的数据进行分类，并对各类数据进行快速的汇总统计。汇总的类型有求和、计数、平均值、最大值、最小值等，默认的汇总方式是求和。创建分类汇总时，首先要对分类的字段进行排序。创建数据分类汇总后
14、，Excel会自动按汇总时的分类对数据清单进行分级显示，并自动生成数字分级显示按钮，用于查看各级别的分级数据。项目五项目五 ExcelExcel的数据处理功能的数据处理功能 7.合并计算利用Excel 2007的合并计算功能，可以将多个工作表中的数据进行计算汇总，在合并计算过程中，存放计算结果的区域称为目标区域，提供合并数据的区域称为源数据区域，目标区域可与源数据区域在同一个工作表上，也可以在不同的工作表或工作簿内。数据源可以来自单个工作表、多个工作表或多个工作簿中。合并计算有两种形式：一种是按分类进行合并计算，另一种是按位置进行合并计算。项目五项目五 ExcelExcel的数据处理功能的数据
15、处理功能1）按分类进行合并计算通过分类来合并计算数据是指当多个数据源区域包含相似的数据却以不同分类标记排列时进行的数据合并计算方式。例如，某公司有两个分公司，分别销售不同的产品，总公司要获得完整的销售报表，就必须使用“分类”的方式来合并计算数据。如果数据源区域顶行包含分类标记，则在“合并计算”对话框中选中“首行”复选框；如果数据源区域左列有分类标记，则选中“最左列”复选框。在一次合并计算中，可以同时选中这两个复选框。项目五项目五 ExcelExcel的数据处理功能的数据处理功能 2）按位置进行合并计算通过位置来合并计算数据是指在所有源区域中的数据被相同地排列，即每一个源区域中要合并计算的数据必
16、须在被选定源区域的相同的相对位置上。这种方式非常适用于处理相同表格的合并工作。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表任务描述任务描述公司财务处的小张每月负责审查各部门考勤表及考勤卡，根据公司制度审查员工的加班工时和出差费用，计算、编制员工工资表，并对工资表做相应的数据统计工作。对于公司2011年1月份的工资管理报表，具体编制要求如下。（1）2011年1月工作日总计25天，满勤的员工才有全勤奖。（2）奖金级别：经理：200元/天。副经理：150元/天。职员：100元/天。（3）应发工资=基本工资+奖金/天出勤天数+全勤奖+差旅补助。任务一任务一 制作公司员工工资管理报表制
17、作公司员工工资管理报表任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表（4）个人所得税起征点为3 500元，应发工资扣去起征点部分为需缴纳部分。本例中最高工资为8 700元，即需缴纳部分为5 200元，涉及的规则如下。需缴纳部分不超过500元的：缴纳部分5%。需缴纳部分超过500元至2 000元的：缴纳部分10%-25。需缴纳部分超过2 000元至5 000元的：缴纳部分15%-125。需缴纳部分超过5 000元至20 000元的：缴纳部分20%-375。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表（5）实发工资=应发工资-个人所得税。（6）统计工资排序情况、超
18、出平均工资的人数、最高工资和最低工资。原始的员工工资管理报表如图5-3所示，小张最终完成的员工工资管理报表如图5-4所示。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表 图5-3 宏发公司员工工资管理报表（原始数据）任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表图5-4 宏发公司员工工资管理报表（结果样文）任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表任务分析任务分析在Excel 2007中计算、编制员工工资报表的根本方法是正确、合理地使用公式和函数。因此，完成本工作任务需要进行如下工作。（1）根据员工职务级别确定“奖金/天”。（2）计算员工的“
19、应发工资”。（3）按规定计算员工“个人所得税”。（4）计算员工的“实发工资”，并对“实发工资”进行排位。（5）进行工资数据统计：“超过平均工资的人数”、“最高工资”和“最低工资”。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表任务实现任务实现打开“宏发公司员工工资管理报表.xlsx”工作簿文件，选 择“工资表”工作表。IF函数的语法如下：IF(logical_test,value_if_true,value_if_false)其中，logical_test是任何可能被计算为True或False的值或表达式（条件式）；value_if_true表示logical_test为Tru
20、e时的返回值；value_if_false表示logical_test为False时的返回值。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表步骤步骤01选中F4单元格，单击“编辑栏”的“插入函数”按钮 ，或在“公式”选项卡的“函数库”组中单击“插入函数”按钮，打开“插入函数”对话框，如图5-5所示。图5-5 “插入函数”对话框任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表步骤步骤02在“选择类别”下拉列表框中选择“常用函数”选项，在“选择函数”列表框中选择“IF”函数，单击“确定”按钮，打开“函数参数”对话框，如图5-6所示。图5-6 IF函数参数对话框任务一任
21、务一 制作公司员工工资管理报表制作公司员工工资管理报表步骤步骤03将光标定位于“Logical_test”文本框，单击右侧的 按钮，显示压缩了的“函数参数”对话框，如图5-7所示。图5-7 压缩了的“函数参数”对话框任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表步骤步骤04此时在工作表中选中C4单元格，单击 按钮，重新扩展“函数参数”对话框 。在“Logical_test”文本框中将条件式“C4=经理”填写完整，在“Value_if_true”文本框中输入200，表示当条件成立时（当前员工的职务是经理时），函数返回值为200，如图5-8所示。任务一任务一 制作公司员工工资管理报
22、表制作公司员工工资管理报表 图5-8 填写了条件式“C4=经理”的IF函数参数对话框 任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表步骤步骤05因为需要继续判断当前员工的职务，所以“Value_if_false”中要再嵌套IF函数进行职务判断。将光标定位“Value_if_false”文本框中，然后在工作表的“编辑栏”最左侧的函数下拉列表中选择“IF”函数，如图5-9所示，再次打开“函数参数”对话框。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表 图5-9 函数下拉列表 任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表步骤步骤06 此时将光标定位于
23、“Logical_test”文本框，并将条件式“C4=副经理”填写完整，在“Value_if_true”文本框中输入150，在“Value_if_false”文本框中输入100，表示当条件成立时（当前员工的职务是副经理时），函数返回150，否则函数返回100，如图5-10所示。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表 图5-10 填写了条件式“C4=副经理”的IF函数参数对话框 任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表步骤步骤07单击“确定”按钮，返回工作表，此时F4单元格中的公式是“=IF(C4=经理,200,IF(C4=副经理,150,100)”
24、，其返回值是100。步骤步骤08其他员工的“奖金/天”数据列的值可以通过复制函数的方式来填充。选中F4单元格，并将指针移至该单元格的右下角，当指针变成十字形状时按住鼠标左键进行拖动，拖至目标位置F14单元格时释放鼠标即可。此时可以看到，IF函数被复制到其他单元格。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表步骤步骤09选中G4单元格，在“编辑栏”内直接输入公式“=IF(E4=25,200,0)”，单击“编辑栏”左侧的“输入”按钮 或按Enter键，即可得到该员工的“全勤奖”数值，其他员工的“全勤奖”可通过复制函数的方式获得。任务一任务一 制作公司员工工资管理报表制作公司员工工
25、资管理报表应清楚应发工资的计算方法：应发工资=基本工资+奖金/天出勤天数+全勤奖+差旅补助。提 示任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表步骤步骤10“应发工资”列数据的填充可以通过在单元格中输入加法公式实现。选中I4单元格，在“编辑栏”内输入公式“=D4+E4*F4+G4+H4”，按Enter键，即可计算出第一个员工的“应发工资”，其他员工的“应发工资”可以通过复制公式的方式来填充，即用鼠标拖动I4单元格右下角的填充柄，至目标位置I14单元格时释放鼠标。此时完成所有员工的“应发工资”列数据的填充。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表“应发工资”
26、列数据的填充也可以通过求和函数SUM实现。SUM函数的功能是返回某一单元格区域中所有数字之和。提 示任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表步骤步骤11选中I4单元格，在“编辑栏”内输入公式“=SUM(D4，E4*F4，G4，H4)”，按Enter键，计算出第一位员工的“应发工资”，其他员工的“应发工资”可以通过复制函数的方式填充。步骤步骤12可以通过IF函数计算每名员工的个人所得税。选中J4单元格后，按Enter键，即可得到第一位员工的个人所得税。其他员工的个人所得税可以通过复制函数的方式来填充。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表要清楚实发工
27、资的计算方法：实发工资=应发工资个人所得税。提 示任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表步骤步骤13 选中单元格K4，在“编辑栏”内输入公式“=I4-J4”，按Enter键，可计算出第一位员工的应发工资。其他员工的应发工资同样可以通过复制公式的方式来填充。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表利用Excel 2007 中的RANK函数可以实现对“实发工资”的排序。RANK函数的功能是返回一个数字在数字列表中的排位。提 示任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表步骤步骤14选中L4单元格，在“编辑栏”内输入公式“=RANK(K
28、4,K4:K14)”，按Enter键，计算出第一位员工的工资排名，其他员工的工资排名可以通过复制函数的方式填充，也可以通过下面的方法实现：选中L4单元格，打开“插入函数”对话框，在“选择类别”下拉列表框中选择“统计”选项，在“选择函数”列表框中选择“RANK”函数，单击“确定”按钮，将打开“函数参数”对话框，如图5-11所示。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表 图5-11 RANK函数参数对话框 任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表步骤步骤15将光标定位于“Number”文本框，单击右侧的 按钮，选择要排位的单元格K4；再将光标定位于“Re
29、f”文本框，单击右侧的 按钮，在工作表中选中K4：K14单元格区域（要排位的数字列表），并且进行绝对引用（选中“Ref”文本框中的K4：K14，按F4键）；在“Order”文本框中输入数字0，表示按降序排位。单击“确定”按钮，函数返回值为11，说明第一位员工的“工资排名”是11。其他员工的“按工资排序”数据列的值可以通过复制函数的方式来填充。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表计算超过平均工资的人数需要使用平均值函数AVERAGE和COUNTIF函数。AVERAGE函数的功能是返回参数的平均值（算术平均值），COUNTIF函数的功能是计算单元格区域中满足给定条件的单元
30、格的个数。提 示任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表步骤步骤16选中要存放结果的单元格D16，在“编辑栏”中输入公式“=COUNTIF(K4:K14,=&AVERAGE(K4:K14)”，计算出超过平均工资的人数。其中，“K4:K14”表示要统计的单元格区域，“=&AVERAGE(K4:K14)”表示大于或等于平均实发工资，是统计的条件。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表统计最高工资和最低工资需要使用最大值函数MAX和最小值函数MIN。MAX函数的功能是返回一组值中的最大值，MIN函数的功能是返回一组值中的最小值。提 示任务一任务一 制作公
31、司员工工资管理报表制作公司员工工资管理报表步骤步骤17 选中D17单元格，在“编辑栏”中输入公式“=MAX(K4:K14)”，按Enter键，计算出最高工资。选中D18单元格，在“编辑栏”中输入公式“=MIN(K4:K14)”，按Enter键，计算出最低工资。至此，宏发公司员工工资管理报表编制完成。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表能力评价能力评价根据个人任务完成的情况实行个人自评、小组成员之间互评和教师评价，评价分优、良、中、差4个等级，请教师给出提升建议和综合评价，见表5-1。任务一任务一 制作公司员工工资管理报表制作公司员工工资管理报表任务二任务二 商品销售统
32、计表的数据处理商品销售统计表的数据处理任务描述任务描述宏发公司总部每个季度都要对各个销售处的商品销售数据进行汇总、计算、排序等工作。目前，准备对所属的第一销售处、第二销售处、第三销售处在13月的商品销售情况进行汇总，具体工作包括以下几项。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理（1）按月对商品的销售额进行降序排列，对每个经销处按商品销售额进行降序排列。（2）对指定月份、指定经销处、指定销售数量的商品销售情况进行列表显示。（3）统计各经销处13月的平均销售额，同时汇总各经销处的月销售额。（4）对第一经销处、第二经销处、第三经销处的商品销售数量和销售金额进行合并计算。任务二任
33、务二 商品销售统计表的数据处理商品销售统计表的数据处理任务分析任务分析必须使用Excel 2007中提供的数据排序功能、数据筛选功能、数据的分类汇总功能和合并计算功能，才能实现任务要求的各项数据分析和统计要求。利用排序功能实现方法如下。（1）利用“排序”对话框实现按月份对商品的销售额进行降序排列。（2）通过在“排序”对话框中自定义排序序列，实现对每个经销处按商品销售额进行降序排列。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理利用筛选功能实现方法如下。（1）使用自动筛选功能可以完成对1月份的商品销售情况进行列表显示，其余数据被隐藏。（2）通过自定义自动筛选方式可以完成对1月份销
34、售数量在5085台的商品销售情况进行列表显示。（3）通过高级筛选功能可以将第一经销处1月份销售数量超过70台的销售数据及“索尼EA35”在3月份的销售情况进行列表显示（设置筛选条件区域）。1）按月对商品的销售额进行降序排列在素材中打开“宏发公司商品销售.xlsx”文件，选择“宏发公司商品销售表”工作表，并建立其副本，将副本更名为“排序”，将“排序”工作表设置为当前工作表。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理任务实现任务实现 1.数据排序任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理步骤步骤01选中工作表中的任意单元格，在“数据”选项卡的“排序和筛选”组
35、中单击“排序”按钮 ，打开“排序”对话框。步骤步骤02在“主要关键字”下拉列表框中选择“月份”，在“次序”下拉列表框中选择“升序”，表示首先按月份升序排列。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理步骤步骤03在“排序”对话框中单击“添加条件”按钮 ，添加次要关键字。步骤步骤04与设置主要关键字的方式一样，在“次要关键字”下拉列表框中选择“销售金额（元）”，在“次序”下拉列表框中选择“降序”，表示在“月份”相同的情况下按“销售金额”降序排列，如图5-12所示。排序后结果如图5-13所示。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理 图5-12 “排序”对话
36、框 图5-13 排序结果示意图 任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理2）对每个经销处按商品销售额进行降序排列打开“宏发公司商品销售.xlsx”文件，选择“宏发公司商品销售表”工作表，并建立其副本，将副本更名为“自定义排序”，将“自定义排序”工作表设置为当前工作表。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理 在对“销售部门”字段进行排序时，系统默认的汉字排序方式是以汉字拼音的字母顺序排列的，所以依次出现的“销售部门”是“第二经销处”、“第三经销处”、“第一经销处”，不符合要求，这里要采用自定义排序方式定义“销售部门”字段的正常排列顺序，按“第一经销处
37、”、“第二经销处”、“第三经销处”的顺序统计各销售处商品销售金额由高到低的顺序。注 意任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理步骤步骤01选择工作表中的任意数据单元格，在“数据”选项卡的“排序和筛选”组中单击“排序”按钮，打开“排序”对话框。步骤步骤02将主要关键字设置为“销售部门”，在“次序”下拉列表框中选择“自定义序列”选项，打开“自定义序列”对话框，在“输入序列”列表框中依次输入“第一经销处”、“第二经销处”、“第三经销处”，如图5-14所示，单击“添加”按钮，再单击“确定”按钮返回“排序”对话框，则“次序”下拉列表框中已设置为定义好的序列。任务二任务二 商品销售统
38、计表的数据处理商品销售统计表的数据处理 图5-14 自定义序列任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理步骤步骤03单击“添加条件”按钮，将次要关键字设置为“销售金额（元）”，并设置次序为“降序”，单击“确定”按钮则完成了对每个经销处按商品销售额进行降序排列。最终效果如图5-15所示。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理 图5-15 以“自定义序列”排序的效果图 任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理 2.数据筛选1）对1月份的商品销售情况进行列表显示打开“宏发公司商品销售.xlsx”文件，选择“宏发公司商品销售表”工作表，
39、并建立其副本，将副本更名为“筛选”，将“筛选”工作表设置为当前工作表。打开列筛选器，取消对“2月份”、“3月份”复选框的选择，如图5-16所示，单击“确定”按钮。此时工作表中将只显示“1月份”的相关数据条目，如图5-17所示。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理图5-16 选择“1月份”的数据 图5-17 “1月份”的商品销售情况任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理2）对1月份销售数量在5085台的商品销售情况进行列表显示为完成此项操作，除了“1月份”这个筛选条件以外，还需要补加“销售数量=50”的条件对商品的销售数量进行筛选。任务二任务二
40、商品销售统计表的数据处理商品销售统计表的数据处理步骤步骤01对“1月份”的商品销售情况进行筛选。步骤步骤02单击“销售数量”右侧的下拉按钮，在列筛选器中选择“数字筛选”命令，弹出子菜单，选择“自定义筛选”命令，如图5-18所示，打开“自定义自动筛选方式”对话框，如图5-19所示。在其中设置“销售数量”大于或等于50台并且（“与”）“销售数量”小于或等于85台，单击“确定”按钮即可。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理图5-18 选择“自定义筛选”命令 图5-19 “自定义自动筛选方式”对话框 任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理此时，就可以对
41、1月份销售数量在5085台的商品销售情况进行列表显示，如图5-20所示。图5-20 “自定义筛选”结果示意图 任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理3）将第一经销处1月份销售数量超过70台的销售数据及“索尼EA35”在3月份的销售情况进行列表显示打开“宏发公司商品销售.xlsx”文件，选择“宏发公司商品销售表”工作表，并建立其副本，将副本更名为“高级筛选”，并将“高级筛选”工作表设置为当前工作表。要完成此操作，需要设置两个复杂条件。条件1：销售部门=“第一经销处”与 月份=“1月份”与 销售数量70条件2：商品名称=“索尼EA35”与 月份=“3月份”条件1和条件2之间
42、是“或”关系。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理步骤步骤01设置条件区域并输入筛选条件。在数据区域的下方设置条件区域，其中条件区域必须具有列标签，同时确保在条件区域与数据区域之间至少留一个空白行，如图5-21所示。图5-21 设置条件区域并输入高级筛选条件任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理 图5-22 “高级筛选”对话框 步骤步骤02选择数据列表区域、条件区域和目标区域。选中数据区域中任意单元格，在“数据”选项卡的“排序和筛选”组中单击“高级”按钮，打开“高级筛选”对话框，如图5-22所示，在列表区域已默认显示了数据源区域。任务二任务二
43、商品销售统计表的数据处理商品销售统计表的数据处理步骤步骤03单击“条件区域”文本框右侧的“选择单元格”按钮 ，在工作表中选择已设置的条件区域，在“方式”选项组中选中“将筛选结果复制到其他位置”单选按钮，再单击“复制到”文本框右侧的“选择单元格”按钮，选择显示筛选结果的目标位置，单击“确定”按钮即可将所需的商品销售情况进行列表显示，如图5-23所示。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理 图5-23 高级筛选结果效果图任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理 3.统计各经销处13月的平均销售额，同时汇总各经销处的月销售额打开“宏发公司商品销售.xls
44、x”文件，选择“宏发公司商品销售表”工作表，并建立其副本，将副本更名为“分类汇总”，并将“分类汇总”工作表设置为当前工作表。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理步骤步骤01将“销售部门”作为主关键字、“月份”作为次关键字进行排序，其中“销售部门”通过自定义序列“第一经销处、第二经销处、第三经销处”进行排序。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理 图5-24 “分类汇总”对话框 步骤步骤02选择数据区域中的任意单元格，在“数据”选项卡的“分级显示”组中单击“分类汇总”按钮，打开“分类汇总”对话框，如图5-24所示。任务二任务二 商品销售统计表的数
45、据处理商品销售统计表的数据处理步骤步骤03设置“分类字段”为“销售部门”，“汇总方式”为“平均值”，“选定汇总项”为“销售金额（元）”，同时选中“替换当前分类汇总”和“汇总结果显示在数据下方”复选框，单击“确定”按钮。则按经销处对数据进行一级分类汇总，效果如图5-25所示。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理步骤步骤04 在步骤2的基础上，再次执行分类汇总。在“分类汇总”对话框中，设置“分类字段”为“月份”，“汇总方式”为“求和”，“选定汇总项”为“销售金额（元）”，同时取消选中“替换当前分类汇总”复选框，单击“确定”按钮则实现二级分类汇总。此二级分类汇总首先实现了对
46、各经销处13月的销售额平均值的计算，然后对每个经销处进行按月的销售额统计。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理 图5-25 一级“分类汇总”结果示意图 任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理在“分类汇总”对话框中，如果单击“全部删除”按钮，可将工作表恢复到初始状态。提 示任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理两次分类汇总的结果如图5-26所示。图5-26 二级“分类汇总”结果示意图 任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理 4.对第一经销处、第二经销处、第三经销处的商品销售数量和销售金额进行合并计算
47、在“宏发公司商品销售.xlsx”工作簿文件中新建工作表并命名为“合并计算”，用于存放合并数据。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理图5-27 “合并计算”对话框步骤步骤01 选中“合并计算”工作表中的A2单元格，在“数据”选项卡的“数据工具”组中单击“合并计算”按钮，打开“合并计算”对话框，如图5-27所示。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理步骤步骤02在“函数”下拉列表框中选择“求和”运算。步骤步骤03在“引用位置”文本框中单击右侧的“选择单元格”按钮，选择工作表“第一销售处统计表”中的A2:D8单元格区域作为第一个要合并的源数据区域，单
48、击“添加”按钮，将该引用位置添加进“所有引用位置”列表框中。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理步骤步骤04按步骤3中的操作方法依次添加“第二经销处统计表”中的A2:D7单元格区域和“第三经销处统计表”中的A2:D7单元格区域到“所有引用位置”列表框中。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理图5-28 数据合并结果步骤步骤05在“标签位置”选项组中选中“首行”、“最左列”复选框。单击“确定”按钮即可完成对3个数据表的数据合并功能，结果如图5-28所示。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理步骤步骤06在“合并计算”工作表
49、中将显示如图5-28所示的合并计算结果，由于对文本数据无法实现合并计算，所以“月份”字段值为空。可以删除“月份”数据列，在A2单元格补写“商品名称”，并适当美化“合并计算”工作表，操作结果如图5-29所示。图5-29 “合并计算”结果示意图任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理能力评价能力评价根据个人任务完成的情况实行个人自评、小组成员之间互评和教师评价，评价分优、良、中、差4个等级，请教师给出提升建议和综合评价，见表5-2。任务二任务二 商品销售统计表的数据处理商品销售统计表的数据处理实训一实训一 编制石化班期末考试成绩单编制石化班期末考试成绩单实训目的实训目的（1）
50、掌握公式、常用函数的用法。（2）了解运算符的优先级并能正确输入公式。实训一实训一 编制石化班期末考试成绩单编制石化班期末考试成绩单实训步骤实训步骤按要求完成“石化班期末考试成绩单”的编制，效果如图5-30所示。图5-30 石化班期末考试成绩单实训一实训一 编制石化班期末考试成绩单编制石化班期末考试成绩单 具体编制要求如下。（1）计算每位学生的总分、平均分、名次和总评等级。（2）统计各门课程、总分列、平均分列的最高分、最低分、优秀率（90分以上人数的百分比）。（3）总评等级是根据每位学生平均分来划分的，具体规定如下：优秀：平均分90。良好：80平均分90。中等：70平均分80。及格：60平均分7
