《办公软件应用（Office 2016）》课件 项目7-任务2 制作销售情况分析表

项目七：Excel2016表格数据的排版、筛选与汇总

任务二：制作销售情况分析表作者：xxx任务二：制作销售情况分析表在日常办公应用中，常需要对大量的数据按不同的类别进行汇总计算。例如，在销售情况汇总过程中，需要对不同时间的销售进行汇总、对不同产品的销售情况进行汇总。本例将应用Excel中的合并计算机和分类汇总功能对销售情况分析表中的数据进行分析。任务二：制作销售情况分析表教学目标掌握邮件合并功能的使用、中文简繁转换的方法重点和难点邮件合并功能的使用

资源同步文件素材文件：资源/素材文件/第七章/制作销售情况分析表.xlsx结果文件：资源/成品文件/第七章/制作销售情况分析表.xlsx任务二：制作销售情况分析表教学任务：一、销售金额的汇总二、应用分类汇总功能汇总数据三、筛选数据任务二：制作销售情况分析表有时候我们需要按某一个分类将数据结果进行汇总计算，可以应用Excel中的合并计算出每月总的销售金额，并将该结果列举到新工作表中，具体操作方法如下：任务二：制作销售情况分析表（1）各月销售金额汇总①制作汇总表并进行计算第一步：新建工作表并设置工作表名称为“各月销售总额”；第二步：在A1单元格区域中输入“日期”，B1单元格输入“销售金额”；第三步：选择A2单元格；第四步：选择“数据”选项卡“数据工具”组中的“合并计算”按钮，如下图所示。任务二：制作销售情况分析表任务二：制作销售情况分析表②设置合并计算参数第一步：经上部操作弹出“合并计算”对话框，在“函数”下拉列表框中选择“求和”选项；第二步：在“引用设置”参数框中引用“原始数据表”工作表中“日期”和“销售金额”列中的数据，即D2:E20单元格区域；第三步：在“标签位置”栏中选中“最左列”复选框；第四步：单击“添加”按钮，如下图所示。任务二：制作销售情况分析表③合并计算并查看合并结果单击“合并计算”对话框中的“确定”按钮，即可在“各月销售总额”工作表中计算出各月的销售总额，如下图所示。任务二：制作销售情况分析表（2）按产品汇总销售金额在素材文件“销售情况分析表”中列举了各产品各月的销售金额，现需要计算出各产品总的销售金额，并将该结果列举到新工作表中，为了与“原始数据表”工作表中的数据同步，即若“原始数据表”工作表中的数据发生变化，该汇总结果也会随之变化，此时可在进行合并计算时选中“创建指向源数据的链接”复选框，具体操作方法如下。任务二：制作销售情况分析表①制作汇总表并进行合并计算第一步：新建工作表并设置工作表名称为“各产品销售金额汇总”；第二步：选择A1单元格；第三步：单击“数据”选项卡“数据工具”组中的“合并计算”按钮一个图标，打开“合并计算”对话框，如下图所示。任务二：制作销售情况分析表任务二：制作销售情况分析表②设置合并计算参数第一步：在“函数”下拉列表框中选择“求和”选项；第二步：在“引用位置”参数框中引用“原始数据表”工作表中“产品”、“日期”和“销售金额”列中的数据，即C2:E20单元格区域；第三步：在“标签位置”栏中选中“最左列”和“创建指向源数据的链接”复选框；第四步：单击“添加”按钮，如下图所示。任务二：制作销售情况分析表任务二：制作销售情况分析表③合并计算并查看合并结果第一步：单击“合并计算”对话框中的“确定”按钮，第二步：在“各产品销售总额”工作表中计算出产品的销售总额；第三步：删除结果中不需要的空列，并在顶部插入一行，添加列标题行，最终效果如下图所示。任务二：制作销售情况分析表任务二：制作销售情况分析表（3）关于合并计算的应用应用合并计算可以快速将同类别的数据进行合并，进行合并的数据可以在不同的工作表中，只要数据具有相同标签即可进行合并。在进行合并运算时，表明数据类别的数据称为“标签”，它可以存在于进行合并的数据的最左列，亦可存在于数据的第一行，例如本例中就是将“产品称”作为合并的标签，对所有相同标签的数据进行了求和的计算。在对数据进行合并计算时，除使用“求和”方式外，在“合并计算”对话框中的“函数”下拉列表框中还可以选择其他计算方式合并数据，如“平均值”、“最大值”和“乘积”等。任务二：制作销售情况分析表二、应用分类汇总功能汇总数据应用合并计算可以快速对某一类数据进行汇总计算，合并之后重在体现其计算结果，无法清晰地显示出明细数据。为对不同类别的数据进行汇总，同时能更清晰地查看汇总后的明细数据，可以使用分类汇总功能。在使用分类汇总前，需要将要进行分类汇总的数据进行排序，使类别相同的数据位置排列在一起，从而实现分类的功能，然后再使用分类汇总命令按相应的分类进行数据汇总。任务二：制作销售情况分析表（1）按部门分类汇总销售金额在本例中，为清晰地查看到各部门的销售情况，可按“部门”字段进行分类，并汇总销售金额，具体操作方法如下。任务二：制作销售情况分析表①制作汇总表并按部门排列数据第一步：复制“原始数据表”工作表并将其重命名为“各部门销售表”;第二步：选择“部门”列中的任意单元格；第三步：单击“数据”选项卡“排序和筛选”组中的“升序”按钮，按“部门”列中数据的升序进行排序，如下图所示。任务二：制作销售情况分析表任务二：制作销售情况分析表②执行“分类汇总”命令单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，打开“分类汇总”对话框，如下图所示任务二：制作销售情况分析表③设置分类汇总参数第一步：在“分类字段”下拉列表框中选择“部门”选项；第二步：在“汇总方式”下拉列表框中选择“求和”选项；第三步：在“选定汇总项”列表框中选中“销售金额”复选框；第四步：单击“确定”按钮，如下图所示。任务二：制作销售情况分析表任务二：制作销售情况分析表④查看汇总结果完成分类汇总后在工作表中的数据将分级显示，单击左侧的“一”按钮可隐藏明细数据，单击“+”按钮可显示出该分类中的明细数据，如下图所示。任务二：制作销售情况分析表（2）按姓名分类汇总平均销售金额在本例中，为清晰地查看各员工的平均销售金额，可按“姓名”字段进行分类，并以平均值方式汇总销售金额，具体操作方法如下。任务二：制作销售情况分析表①制作汇总表并按姓名排列数据第一步：复制“原始数据表”工作表并将其重命名为“按姓名分类”；第二步：选择“姓名”列中的任意单元格；第三步：单击“数据”选项卡“排序和筛选”组中的“升序”按钮，按“姓名”列中数据的升序进行排序，如下图所示。任务二：制作销售情况分析表任务二：制作销售情况分析表②执行“分类汇总”命令单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，打开“分类总汇”对话框，如下图所示。任务二：制作销售情况分析表③设置分类汇总参数第一步：在“分类字段”下拉列表框中选择“姓名”选项；第二步：在“汇总方式”下拉列表框中选择“平均值”选项；第三步：在“选定汇总表”列表框中选中“销售金额”复选框；第四步：单击“确定”按钮，如下图所示。任务二：制作销售情况分析表任务二：制作销售情况分析表④查看汇总结果完成分类总汇后如下图所示。任务二：制作销售情况分析表（3）按商品和日期分类汇总销售金额在本例中，为清晰地查看各种产品各月的销售情况，可利用分类汇总功能按产品进行分类汇总，并在该分类汇总的基础上再根据日期分类汇总，即在同一表格区域中使用两次分类汇总。为使用两次分类汇总，在对表格中的数据进行排序时，应以第一次分类的字段为排序的主要关键字，以第二次分类的字段为排序的次要关键字，在排序之后进行两次分类汇总即可，具体操作方法如下。任务二：制作销售情况分析表①制作汇总表并列数据第一步：复制“原始数据表”工作表并将其重命名为“按产品和日期分类”；第二步：选择表格中的任意单元格；第三步：单击“数据”选项卡“排序口筛选”组中的“排序”按钮，如下图所示。任务二：制作销售情况分析表任务二：制作销售情况分析表②设置排序关键字及选项第一步：打开“排序”对话框，单击“添加条件”按钮；第二步：设置“主要关键字”为“产品”，次要关键字为“日期”；第三步：单击“选项”按钮，如下图所示。任务二：制作销售情况分析表任务二：制作销售情况分析表③执行“分类汇总”命令第一步：打开“排序选项”对话框,选中“笔划排序"单选按钮；第二步：单击“确定”按钮，如下图所示。任务二：制作销售情况分析表④执行“分类汇总”命令单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，如下图所示。任务二：制作销售情况分析表⑤设置分类汇总参数第一步：打开“分类汇总”对话框，在“分类字段”下拉列表框中选择“产品”选项；第二步：在“汇总方式”下拉列表框中选择“求和”选项;第三步：在“选定汇总项”列表框中选中“销售金额”复选框；第四步：单击“确定”按钮，如下图所示。任务二：制作销售情况分析表任务二：制作销售情况分析表⑥再次执行“分类汇总”命令再次单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，如下图所示。任务二：制作销售情况分析表⑦设置分类汇总参数第一步：打开“分类汇总”对话框，在“分类字段”下拉列表框中选择“日期”选项；第二步：在“汇总方式”下拉列表框中选择“求和”选项；第三步：在“选定汇总项”列表框中选中“销售金额”复选框;第四步：取消选中“替换当前分类汇总”复选框;第五步：单击“确定”按钮，如下图所示。任务二：制作销售情况分析表任务二：制作销售情况分析表⑧完成并查看分类汇总结果经过上步操作，即可查看到同时对两个字段进行分类汇总的结果，如下图所示。任务二：制作销售情况分析表三、筛选数据要从拥有大量数据的表格中快速找出需要的数据，可以使用筛选功能，本例将应用筛选功能对销售情况分析表中的数据进行筛选。任务二：制作销售情况分析表（1）筛选销售金额最高的5条数据在对数据进行筛选时，有时需要筛选出数据中某项数据值最大或最小的多条数据。例如，本例中需要筛选出销售金额最高的5条数据，此时可应用“自动筛选”功能中的数字筛选功能，具体操作方法如下。任务二：制作销售情况分析表①开启自动筛选功能第一步：选择“原始数据表”工作表中的数据区域或选择数据区域中的任意单元格；第二步：单击“数据”选项卡“排序和筛选”组中的“筛选”按钮，开启自动筛选功能，如下图所示。任务二：制作销售情况分析表②设置排序关键字及选项第一步：打开“排序”对话框，单击“添加条件”按钮；第二步：设置“主要关键字”为“产品”，次要关键字为“日期”；第三步：单击“选项”按钮，如下图所示。任务二：制作销售情况分析表任务二：制作销售情况分析表②进行数字筛选第一