项目四 任务四 统计并分析建筑材料销售表（课件）《计算机应用基础项目教程》（航空工业出版社）同步教学

计算机应用基础项目教程 模板来自于 http://docer.wps.cn 1 Excel是Office办公套装软件中的另一个重要成员，它是一款优秀的电子表格制作软件，具有强大的数据组织和处理功能。利用它可以快速制作出各种美观、实用的电子表格，以及对表格中的数据进行计算、统计、分析和预测等，并可按需要将表格打印出来。本项目主要学习Excel的使用方法。 项目四 使用Excel设计与制作电子表格 Transition Page 过渡页 学习 目标 01 掌握在工作表中输入、编辑数据并设置表格格式的方法。 02 掌握使用公式和函数对工作表数据进行计算的方法。 03 掌握使用排序、筛选、分类汇总和条件格式对工作表数据进行管理的方法。 04 掌握利用图表对工作表数据进行统计分析的方法。 05 掌握利用数据透视表和数据透视图对工作表数据进行统计分析的方法。 Transition Page 过渡页 过渡页 任务四 统计并分析建筑材料销售表 ——数据筛选与分类汇总 Transition Page 任务背景 某建筑材料销售公司为了统计和分析销售成果，需要设计一份销售报表。这份报表要能够准确统计各商品的销售情况，反映经营利润，并且可以实现按条件对数据进行筛选、汇总等统计分析。 分析任务 本任务主要完成的工作是制作建筑材料销售表，通过该任务让读者了解Excel的数据筛选、分类汇总等知识。任务完成效果如图4-93所示。 任务四 统计并分析建筑材料销售表 任务四 统计并分析建筑材料销售表 图4-93 建筑材料销售表效果 任务四 统计并分析建筑材料销售表 任务目标 能对工作表数据按要求进行自动筛选和高级筛选。 能对工作表数据按要求进行分类汇总。 任务实施 一、制作建筑材料销售表 任务四 统计并分析建筑材料销售表 步骤1 ☞ 新建“建筑材料销售表”工作簿，将“Sheet1”工作表重命名为“源数据”，然后在其中输入各列标题：产品名称、商标、规格型号、计量单位、数量、单价（元）、金额（元）。 步骤2 ☞ 在A2:F20单元格区域根据实际情况输入销售数据，再对工作表进行简单的格式设置，如设置表格的框线和对齐方式，效果如图4-94所示。 图4-94 输入数据 步骤3 ☞ 在G2单元格中输入公式“=E2*F2”，然后向下拖动G2单元格右下角的填充柄至G20单元格后释放鼠标，计算出所有产品的销售金额。 任务四 统计并分析建筑材料销售表 二、数据筛选 通过数据筛选可以使数据表仅显示满足条件的行，不符合条件的行将被隐藏。在Excel中，可以使用两种方式筛选数据，即自动筛选和高级筛选。 1 自动筛选 这种筛选方法可以快速显示出工作表中满足条件的数据行，它适用于简单条件的筛选。自动筛选有3种筛选类型：按列表值、按格式或按条件。这3种筛选类型是互斥的，用户只能选择其中的一种进行数据筛选。 任务四 统计并分析建筑材料销售表 步骤1 ☞ 将计算金额后的“源数据”工作表复制一份，将其重命名为“自动筛选”。 步骤2 ☞ 在工作表的任意数据区域单击，然后单击“数据”选项卡“排序和筛选”组中的“筛选”按钮 ，如图4-95所示。此时在各列标题的右侧会出现筛选按钮。 图4-95 单击“筛选”按钮 任务四 统计并分析建筑材料销售表 步骤3 ☞ 单击“金额”列右侧的筛选按钮，在展开的列表中选择“数字筛选”>“大小”选项，如图4-96所示。 图4-96 选择“大于”选项 任务四 统计并分析建筑材料销售表 步骤4 ☞ 打开“自定义自动筛选方式”对话框，设置筛选条件为大于1000000，如图4-97所示。 图4-98 筛选结果 步骤5 ☞ 单击“确定”按钮，即可看到筛选结果，不符合条件的记录自动隐藏起来，如图4-98所示。 图4-97 设置筛选条件 任务四 统计并分析建筑材料销售表 2 高级筛选 这种筛选方法适用于复杂的条件筛选，至少2种或2种以上的条件。使用时，首先在选定工作表中的指定区域创建筛选条件，然后选择参与筛选的数据区域和筛选条件来进行筛选。在高级筛选中，筛选条件又可分为多条件筛选和多选一条件筛选。 1）多条件筛选 步骤1 ☞ 将计算金额后的“源数据”工作表复制一份，将其重命名为“高级筛选”。 任务四 统计并分析建筑材料销售表 步骤2 ☞ 将“数量”和“金额（元）”列标签复制到I1:J1单元格区域，然后在其下方的单元格中输入筛选条件数量大于2000并且金额要大于1000000，如图4-99所示。 步骤3 ☞ 在工作表的数据区域单击，然后单击“数据”选项卡“排序和筛选”组中的“高级”按钮 ，如图4-99所示。 图4-99 输入筛选条件 任务四 统计并分析建筑材料销售表 步骤4 ☞ 打开“高级筛选”对话框，可看到“列表区域”显示了要进行筛选操作的数据区域，若该区域的单元格引用地址无误，可保持默认。这里重新在工作表中选择数据区域A1:G20，如图4-100所示。 步骤5 ☞ 图4-100 重新选择要进行筛选操作的数据区域 单击“条件区域”右侧的 按钮，打开“高级筛选-条件区域”对话框，然后在工作表中拖动鼠标选择步骤2设置的条件区域I1:J2，再单击对话框中的 按钮，如图4-101所示，返回“高级筛选”对话框。 图4-101 选择条件区域 任务四 统计并分析建筑材料销售表 步骤6 ☞ 设置筛选结果的显示方式，如选择“将筛选结果复制到其他位置”单选钮，然后在“复制到”编辑框中单击，再到工作表中单击A22，将其设置为筛选结果放置区左上角的单元格，如图4-102所示。 图4-102 选择筛选结果的放置位置 任务四 统计并分析建筑材料销售表 步骤7 ☞ 单击“确定”按钮，Excel程序将根据指定的条件对工作表进行筛选，并将筛选结果放置到指定区域，如图4-103所示。 图4-103 高级筛选结果 任务四 统计并分析建筑材料销售表 这种筛选只要满足几个条件中的一个，记录就会显示出来。例如筛选数量大于1000或单价小于10的数据。在输入筛选条件的时候要注意，列标题可以在同一行，但是“条件”不能在同一行。也就是“>1000”和“<10”要换行显示，只有这样才能将满足一个条件的行筛选出来。 2）多选一条件筛选 任务四 统计并分析建筑材料销售表 三、数据分类汇总 分类汇总是把工作表中的数据按指定的关键字（字段）进行各种汇总。它不需要建立公式，Excel会自动对各类别的数据进行求和、求平均值等计算，并且分级显示汇总的结果，从而增加了工作表的可读性，使用户能更快捷地获得需要的数据并做出判断。 对工作表的数据进行分类汇总时，应注意如下事项。 （1）因为分类汇总是按字段名进行的，所以要进行分类汇总的数据表的第一行必须有列标签，并且数据区域中没有空行或空列。 （2）在分类汇总之前必须先对数据进行排序，将要进行分类汇总的数据信息组合在一起，然后再进行分类汇总操作。 （3）进行分类汇总前，应选择数据区域中的任意一个单元格。 （4）进行分类汇总的字段必须是已经排序的字段，如果选择其他字段，则分类汇总结果不正确。 任务四 统计并分析建筑材料销售表 提示 如果只对工作表中的某项数据进行汇总，可以使用公式或函数对其求和而不需要使用分类汇总。 步骤1 ☞ 排序工作表。复制“源数据”工作表副本，将其重命名为“分类汇总”。单击工作表的“产品名称”列，再单击“数据”选项卡“排序和筛选”组中的“升序”按钮，将工作表数据按“产品名称”字段进行升序排序，效果如图4-104所示。 图4-104 排序结果 下面对计算后的建筑材料销售表进行分类汇总操作，如按“产品名称”字段对“数量”和“金额”进行求和分类汇总。 任务四 统计并分析建筑材料销售表 步骤2 ☞ 对工作表进行分类汇总。单击“数据”选项卡“分级显示”组中的“分类汇总”按钮 ，打开“分类汇总”对话框，在“分类字段”下拉列表中选择要分类的字段“产品名称”，在“汇总方式”下拉列表中选择汇总方式“求和”，在“选定汇总项”列表中选择要汇总的项目“数量”和“金额”选项，如图4-105所示。 图4-105 设置分类汇总选项 任务四 统计并分析建筑材料销售表 提示 在“分类汇总”对话框中做相关设置时，注意在“选定汇总项”列表中选择的汇总项，要与“汇总方式”下拉列表中选择的汇总方式相符合。例如，文本是不能进行平均值计算的。 步骤3 ☞ 在“分类汇总”对话框下方选择汇总数据的保存方式，此处保持默认。单击“确定”按钮，Excel为每一个产品名称增加一行，以存放分类汇总的结果，并注明分类汇总的名称。此外，还在工作表左侧插入分级显示符号，如图4-106所示。 图4-106 分类汇总结果 任务四 统计并分析建筑材料销售表 选中“替换当前分类汇总”复选框：表示最后一次的分类汇总会取代以前的分类汇总。 选中“汇总结果显示在数据下方”复选框：表示在原工作表数据的下方会显示出汇总计算的结果。 选中“每组数据分页”复选框：表示将分页保存各种不同的分类数据，这样更有利于保存和查阅，还可以将其分别打印出来。 任务四 统计并分析建筑材料销售表 提示 用户可以对数据清单中的数据进行多次分类汇总，从而选择不同的分类字段或汇总方式进行汇总。进行多次分类汇总时要注意，除第1次分类汇总外，其他分类汇总需要在“分类汇总”对话框取消“替换当前分类汇总”复选框的选中状态。此外，若使用多个分类字段进行汇总，需先对这些分类汇总字段进行相应的排序。 图4-107 多重分类汇总结果 例如，要将建筑材料销售表按“产品名称”对“金额”求和，对“数量”求平均值，分类汇总参数设置和结果如图4-107所示。 任务四 统计并分析建筑材料销售表 四、查看并删除分类汇总 对工作表中的数据进行分类汇总后，在工作表行号的左侧会显示一些分级显示符号，如 和 ，利用这些符号可按照所需的明细程度分级显示汇总结果。 在 分级显示符号中，单击所需级别的数字，较低级别的明细数据会隐藏起来。其中数值越大的显示符号表示显示的汇总结果就越详细，单击符号 可隐藏分类汇总结果的所有明细数据，如图4-108所示。 图4-108 分级显示数据 任务四 统计并分析建筑材料销售表 要隐藏工作表中的某组明细数据行，可单击该组行号左侧的 按钮，此时 按钮变为 按钮，如图4-109所示，单击该按钮可重新显示本组中的明细数据。 图4-109 隐藏明细数据 要取消分类汇总，可打开“分类汇总”对话框，单击“全部删除”按钮。删除分类汇总的同时，Excel会删除与分类汇总一起插入到列表中的分级显示。 任务四 统计并分析建筑材料销售表 高手秘籍 1 使用【Ctrl+Shift+方向键】选取数据 如果选中的单元格和相邻单元格内有数据，则按下【Ctrl+Shift+方向键】组合键后可以将当前选中的区域扩展到同一行或同一列中不间断的最后一个非空单元格。 图4-110所示为单击B5单元格后按住【Ctrl+Shift】组合键，再按一次向下和向右方向键的选择效果。 图4-110 使用【Ctrl+Shift+方向键】选取数据 如果选中的单元格邻侧是空单元格，则按下【Ctrl+Shift+方向键】后会将选区扩展到同一行或同一列的第一个非空单元格。 如果整行或整列都是空单元格，会把选区扩展到这行或这列的首或尾（取决于方向键的方向）。该功能常被用于大面积选取空白区域。 任务四 统计并分析建筑材料销售表 2 筛选不同颜色的数据 如果工作表中用不同的颜色对单元格进行填充过，那么在进行筛选操作时，单击列标签右侧的筛选按钮后，在展开的列表中可看到“按颜色筛选”选项变为可用，并且在其子选项中会显示该列中所有单元格的填充颜色，如图4-111所示。从中选择一种颜色，即可筛选出工作表中填充该颜色的数据。 图4-111 按颜色筛选 任务四 统计并分析建筑材料销售表 拓展训练 统计和分析家电销售表 1 训练目的 强化练习对工作表数据进行自动筛选、高级筛选和分类汇总的操作。 2 训练内容 （1）打开任务一制作完成的家电销售表，将其另存为“家电销售表（统计与分析）”。 （2）计算出销售额、折扣最大值和销售额最小值。 （3）筛选出A部的销售记录。 （4）按产品名称汇总产品的销售数量和销售金额。 任务四 统计并分析建筑材料销售表 $$