第9章 Excel综合应用 9.1 排序和筛选 9.2 分类汇总的使用 9.3 数据透视表的使用 2 2 第9章 Excel综合应用 重点难点 学习目标 掌握Excel的排序、筛选、分类汇总和数据透视表等数据 处理工具的使用方法和技巧。 重点：排序、筛选、分类汇总和数据透视表的概念、操作。 难点：分类汇总、数据透视表 3 3 第9章 Excel综合应用 9.1 排序和筛选 9.2 分类汇总的使用 9.3 数据透视表的使用 对Excel工作表中的数据进行查询、分析，可以帮助用 户准确判断、正确决策。Excel提供的排序、筛选、分类汇 总和透视表等数据处理工具，可方便用户快速、有效地进行 数据处理。本章通过实例讲述这些工具的使用方法和技巧。 4 4 9.1 排序和筛选 一、任务分析和实现途径 二、基本知识和基本概念 三、任务的实现 5 5 9.2 分类汇总的使用 一、任务分析和实现途径 二、基本知识和基本概念 三、任务的实现 6 6 9.3 数据透视表的使用 一、任务分析和实现途径 二、基本知识和基本概念 三、任务的实现 7 7 9.1 排序和筛选 1. 任务分析 书店需要一个使用简便、快速有效的收款程序。包括两个工作表： （1） 图书信息表 一、任务分析和实现途径 8 8 9.1 排序和筛选 （2） 图书销售表 其中通过使用“查询和引用”函数VLOOKUP建立它与“图书信息表” 表单元格之间的关联。这样既能简化销售人员的工作量，又能减少输 入的错误。 2. 实现途径 需要掌握Excel提供的“排序”和“筛选”功能，以便快捷地从大量数 据中查找到特定记录。 能在工作表指定区域的首列中沿垂直方向 搜索满足条件的单元格，从而确定检索到 的单元格在搜索区域中的行号，然后返回 该行指定单元格中的内容。 9 9 9.1 排序和筛选 1. 排序 按照一定的规则对数据列表中的数据进行整理排列的过程。 Excel 2010提供了多种数据排序的方法，用户可以按照默认规 则对数据进行升序或降序排列，也可以自定义排序规则。 在“数据”选项卡的“排序和筛选”组中设有三个排序按钮：“升序 ”、“降序”和“排序”按钮。 二、基本知识和基本概念 1010 9.1 排序和筛选 2. 数据筛选 数据筛选是指从工作表中查询符合条件的行或列数据。数据筛选是将 所有不满足指定条件的数据暂时隐藏，只显示满足条件的记录。 Excel的数据筛选功能有两种方式： （1）自动筛选; （2）高级筛选 3. 自动筛选 自动筛选就是按照设定的条件对工作表中的数据进行筛选，用于筛选 简单的数据。能够帮助用户在具有大量数据记录的数据列表中快速查找满 足条件的记录。 4. 高级筛选 高级筛选功能较强，通过“高级筛选”对话框进行多条件的复杂查询。 它可以自由地选择工作表中的筛选区域，允许设置多种筛选条件。筛选的 结果可以在原有数据区域显示，也可将结果复制到指定的其他位置。 9.1 排序和筛选 例：请从 “图书销售表”中查看所有数 据结构与算法分析-C+描述一书的销 售情况。 请从 “图书销售表”中查看最高销售额记 录。 请查看“计算机类”图书销售额在1000元 以上的记录。 1111 9.1 排序和筛选 例：请从 “图书销售表”中查找2011年2 月1日-10日的图书销售情况。 请从 “图书销售表”中查找“清华大学出 版社”出版的“计算机类”的图书销售情 况。 1212 9.1 排序和筛选 例：请从 “图书销售表”中查找“清华大 学出版社”出版的或者是“计算机类”的 图书销售情况。 1313 1414 9.1 排序和筛选 三、任务的实现 ch09素材高职基础第九章用表.xlsx 1排序 请从 “图书销售表”中查找“算法概论”一书。 演示！ （1）打开“数据”选项卡，在“排序和筛选”组中单击“排序”按钮 。 （2）在“排序”对话框的“主要关键字” 中选择“类别”，在“排序依据” 中选择默认的“数值”，在“次序”中选择 “升序”。 （3）单击 “添加条件”按钮，给主要关键字增加次要关键字，并在“次 要关键字” 中选择“书名” 。 （4）单击“确定”按钮，完成排序，从中可以找到“算法概论”的记录。 步骤： 1515 9.1 排序和筛选 （1）在“图书销售表”中选择区域A1:K75。 （2）打开“数据”选项卡，在“排序与筛选”组中单击“筛选”按钮 。 （3）单击第一行“类别”右侧的下拉按钮，在弹出的下拉列表中，勾 选“理工”复选框。 （4）在“书名”和“日期” 的下拉列表中选择“现代光学基础”和“2011-2 -6” 。 （5）要取消某一字段的筛选，可单击该字段下拉列表中的“从XX中 清除筛选” 。 （6）若要清除全部筛选，可单击“筛选”按钮。 2. 筛选 演示！ 请从 “图书销售表”中查找2011年2月6日销售的“现代光学基础”一书 的记录。 步骤： 1616 9.1 排序和筛选 3. 高级筛选 使用“高级筛选”实现前面“自动筛选”提出的任务 。 （1）打开“数据”选项卡，在“排序和筛选”中单击“高级”按钮 。 （2）选择列表区域。 全部选中。 （3）设置条件区域。 在A78和B78两个单元格中分别填入“日期”和“书名”。 在A79和B79中输入筛选对象的值“=2011-2-6”和“=现代光学基础” 。 （4）确定条件区域。 在“条件区域”选择“Sheet2!$A$78:$B$79”。 （5）选择显示筛选结果的位置。 （6）显示筛选结果。 演示！ 步骤： 1717 9.2 分类汇总的使用 1. 任务分析 书店要求按销售图书的名称、类别或时间进行汇总统计，分析获得 的统计数据，进而帮助他确定下一步的营销策略。 一、任务分析和实现途径 2. 实现途径 使用Excel的数据处理工具分类汇总。 分类汇总是一个功能强大、使用方便的数据处理工具，它能按照用 户指定的分类规则对指定的字段进行汇总计算并在工作表中显示出来。 1818 9.2 分类汇总的使用 单击“数据”选项卡，在“分级显示”选 项组中单击“分类汇总”按钮，弹出“分类 汇总”对话框，如右图所示。 （1）分类字段 用来设置分类的类别 。 （2）汇总方式 确定分类后的汇总计算方式。 （3）选定汇总项 选择哪些字段被汇总计算并显示。 （4）其它3个选项（复选框） 。 （5）“全部删除”按钮，可以删除当前 的分类汇总。 二、基本知识和基本概念 1919 9.2 分类汇总的使用 三、任务的实现 1按照图书“类别”对图书销量和合计进行求和汇总 演示！ （1）在“图书销售表”中选择元月份的图书销售记录。一定要将第一行 的字段名包括在所选的区域之内。 （2）对所选区域按“类别”升序排序。 （3）打开“数据”选项卡，在“分级显示”组中单击“分类汇总”按钮。 （4） 在“分类汇总”对话框的“分类字段”中选择“类别”；在“汇总方式” 中选择“求和”；在“选定汇总项”中选择“数量”和“合计”。 （5）单击“确定”按钮，即显示分类求和汇总的结果。 步骤： 2020 9.2 分类汇总的使用 2统计元月份每天图书交易的笔数 （1）按“日期”对图书销售表进行排序。 （2）在“分类汇总”对话框中，“分类字段”选择“日期”，“汇总方 式”选择“计数”，“选定汇总项”选择“书名”，并勾选“每组数据分页” 复选框。 （3）单击“确定”按钮，并隐藏明细数据，则显示按天统计的交易 笔数 。 步骤： 2121 9.3 数据透视表的使用 1. 任务分析 “分类汇总”工具的分析能力有较大的局限性，每次只能进行一种类 型的统计，获得的信息太少。希望提供更有效、更灵活的数据分析工具 ，以便得到不同时期每类或每种图书的销量、销售额以及销售趋势等信 息。 一、任务分析和实现途径 2. 实现途径 使用Excel 中的“数据透视表和数据透视图”工具。 数据透视表是一种交互式工作表，可对Excel工作表中的数据进行 组织、汇总和分析，揭示数据遵循的规律和关系，得到变化趋势，将枯 燥的数据转化成有意义的信息。 数据透视图则以图表的方式直观地反映数据分析的结果。 2222 9.3 数据透视表的使用 二、基本知识和基本概念 1数据透视表 Excel的数据透视表集成电子表格的筛选、排序和分类汇总等功能于 一体，最终生成包含多种汇总统计信息的汇总表格，是用户进行数据分析 的有力工具。 2透视表的数据源 透视表的数据源是为数据透视表提供数据的工作表或数据库中的记录 ，它可以是一个或多个Excel工作表、其他的数据透视表，也可以是外部 数据库，例如Access或SQL Server等数据库。 2323 9.3 数据透视表的使用 3数据透视表对数据源的要求 数据透视表在选择数据源时有如下要求。 （1）数据源的第一行必须是每列的字段标题，数据透视表向导会把它们 作为每列的字段名使用。 （2）数据源中不应含有空行或空列。如果有，应把它们删除。 （3）作为数据源的Excel工作表中不能包含使用“分类汇总”产生的分类 汇总和总计的数据。如果工作表中存在这些数据，应使用“分类汇总”对话 框中的“全部删除”将其删除。 4数据透视表的结构 数据透视表主要由五个部分组成，每个区域各被一个蓝色框线包围。 （1）页字段区。可以按照拖放的项目进行页筛选。 （2）列字段区。拖放到此区域的字段按列显示，数据各占一列沿垂直 方向显示。 2424 9.3 数据透视表的使用 （3）行字段区。拖放到此区域字段按行显示。每个字段数据各占一行 ，数据沿水平方向显示。 （4）数据字段区。汇总的字段拖放区域， 数据源中的字段可以拖放在行字段区，也可以拖放在列字段区。 2525 9.3 数据透视表的使用 三、任务的实现 1创建一个能够分析元月份每天、每类图书销售情况的 数据透视表。 演示！ （1）选择数据源。在“图书销售表”中选择元月份的销售记录，即选择 区域A1:K19 。 （2）打开“插入”选项卡，单击“表格”组中的“数据透视表”按钮，在 下拉列表中选择“数据透视表”选项。 （3）在“表/区域”选择数据源区域，由于数据源已确定，所指定的区 域Sheet2!$A$1:$K$19已显示在文本框中。 （4）将“数据透视表字段列表”中的“日期”字段拖放到数据透视表的 行字段区。 步骤： 2626 9.3 数据透视表的使用 （5）将“类别”字段拖放到页字段区。 （6）将“合计”字段拖放到数据字段区。 演示！ 2727 9.3 数据透视表的使用 2. 了解某几天的销售记录，例如1月3日、10日和15日的销 售情况 。 演示！ 步骤： （1） 单击“日期”右端的下拉按钮，选择要 查看的日期； （2）单击“确定”按钮。 2828 9.3 数据透视表的使用 3. 了解计算机类中各个出版社的销售情况 。 演示！ （1）将字段“出版社”拖放到数据透视 表的行字段区域中，如右图所示 ； （2）将“出版社”字段拖入列字段区， 或将位于行字段区域的“出版社”字段直接 拖入列字段区，数据透视表会以下图形式 显示统计结果。 步骤： 2929 9.3 数据透视表的使用 4. 删除或修改页字段区、列字段区和行字段区的字段。 演示！ 如果拖放中出现错误或删除已有的字段，可以将该字