排序分类汇总筛选与数据透视表和图表.ppt

第3章管理工作表中的数据 除了利用公式和函数对数据进行计算和分析外 Excel还提供了强大的数据排序 筛选 分类汇总及假设分析等功能 使用这些功能 用户可方便地管理 分析工作表中的数据 本章内容提要 数据排序数据筛选分类汇总合并计算假设分析 3 1数据排序 排序是对工作表中的数据进行重新组织安排的一种方式 Excel可以对整个工作表或选定的某个单元格区域进行排序 在Excel中 可以对一列或多列中的数据按文本 数字以及日期和时间进行排序 还可以按自定义序列 如大 中和小 或格式 包括单元格颜色 字体颜色或图标集 进行排序 大多数排序操作都是针对列进行的 但是 也可以针对行进行 3 1 1简单排序 简单排序是指对数据表中的单列数据 按照Excel默认的升序或降序的方式排列 升序排序数字 按从最小的负数到最大的正数进行排序 日期 按从最早的日期到最晚的日期进行排序 文本 按照特殊字符 数字 0 9 小写英文字母 a z 大写英文字母 A Z 汉字 以拼音排序 排序 逻辑值 FALSE排在TRUE之前 错误值 所有错误值 如 NUM 和 REF 的优先级相同 空白单元格 总是放在最后 降序排序与升序排序的顺序相反 要进行简单排序 可利用排序按钮进行 首先打开要排序的工作表 然后单击要进行排序的列或确保活动单元格在该列中 再单击 数据 选项卡上 排序和筛选 组中 升序 按钮或 降序 按钮 所选列即按所设方式进行排序 按 公司名称 进行升序排 3 1 2多个关键字排序 多关键字排序 也就是对工作表中的数据按两个或两个以上的关键字进行排序 在此排序方式下 为了获得最佳结果 要排序的单元格区域应包含列标题 对多个关键字进行排序时 在主要关键字完全相同的情况下 会根据指定的次要关键字进行排序 在次要关键字完全相同的情况下 会根据指定的下一个次要关键字进行排序 依次类推 不管有多少排序关键字 排序之后的数据总是按主要关键字排序的 对 公司名称 进行升序排序 对 公司地址 进行降序排序 3 1 3自定义排序 在某些情况下 如果已有的排序规则不能满足用户的要求 此时可以用自定义排序规则来解决 我们除了可以使用Excel内置的自定义序列进行排序外 还可根据需要创建自定义序列 并按创建的自定义序列进行排序 查看Excel内置的自定义序列 创建自定义序列 用英文逗号隔开 按自定义排序对工作表排序 3 2数据筛选 在对工作表数据进行处理时 有时需要从工作表中找出满足一定条件的数据 这时可以用Excel的数据筛选功能显示符合条件的数据 而将不符合条件的数据隐藏起来 且隐藏的数据不会被打印出来 要进行筛选操作 数据表中必须有列标签 Excel提供了自动筛选 自定义筛选和高级筛选三种方式 自动筛选可以快速地显示出工作表中满足条件的记录行 自定义筛选可以按照用户的要求设置筛选条件 高级筛选则能完成比较复杂的多条件查询 3 2 1自动筛选 自动筛选一般用于简单的条件筛选 使用自动筛选可以创建3种筛选类型 按列表值 按格式或按条件 不过 这三种筛选类型是互斥的 用户只能选择其中的一种 筛选出 不签约 的客户 按条件进行筛选 按填充颜色进行筛选 3 2 2自定义筛选 在 筛选 箭头下拉列表选择 文本筛选 或 数字筛选 或 日期筛选 项 然后在其子选项中选择 自定义筛选 项 可以自定义筛选条件进行筛选操作 将工作表中 所在地区 带 华 字的记录筛选出来 3 2 3高级筛选 高级筛选用于条件较复杂的筛选操作 其筛选结果可显示在原数据表格中 不符合条件的记录被隐藏起来 也可以在新的位置显示筛选结果 不符合条件的记录同时保留在数据表中 从而便于进行数据的对比 在高级筛选中 筛选条件又可分为多条件筛选和多选一条件筛选两种 1 多条件筛选 多条件筛选 即利用高级筛选功能查找出同时满足多个条件的记录 查找出客户资料管理且中联系人中含 先生 文本 联系方式末尾数字为 0 的记录 2 多选一条件筛选 多选一条件筛选 也就是在查找时只要满足几个条件当中的一个 记录就会显示出来 筛选出 所在地区 含 华 字 或 联系人 含 小姐 文本的记录 3 2 4取消筛选 若要取消在数据表中对某一列进行的筛选 可以单击该列列标签单元格右侧的筛选按钮 在展开的列表中选择 全选 复选框 然后单击 确定 按钮 此时筛选按钮上的筛选标记消失 该列所有数据显示出来 若要取消在工作表中对所有列进行的筛选 可单击 数据 选项卡上 排序和筛选 组中的 清除 按钮 此时筛选标记消失 所有列数据显示出来 若要删除工作表中的三角筛选箭头 可单击 数据 选项卡上 排序和筛选 组中的 筛选 按钮 3 3分类汇总 分类汇总是把数据表中的数据分门别类地统计处理 无需建立公式 Excel将会自动对各类别的数据进行求和 求平均值 统计个数 求最大值 最小值 和总体方差等多种计算 并且分级显示汇总的结果 从而增加了工作表的可读性 使用户能更快捷地获得需要的数据并做出判断 要进行分类汇总的数据表的第一行必须有列标签 而且在分类汇总之前必须先对数据进行排序 以使得数据中拥有同一类关键字的记录集中在一起 然后再对记录进行分类汇总操作 分类汇总分为简单分类汇总 多重分类汇总和嵌套分类汇总三种 3 3 1简单分类汇总 简单分类汇总指对数据表中的某一列以一种汇总方式进行分类汇总 对 销售业绩统计表 中的数据按区域进行分类汇总 3 3 2多重分类汇总 对工作表中的某列数据选择两种或两种以上的分类汇总方式或汇总项进行汇总 就叫多重分类汇总 也就是说 多重分类汇总每次用的 分类字段 总是相同的 而汇总方式或汇总项不同 3 3 3嵌套分类汇总 嵌套分类汇总是指在一个已经建立了分类汇总的工作表中再进行另外一种分类汇总 两次分类汇总的字段总是不相同的 在建立嵌套分类汇总前也要首先对工作表中需要进行分类汇总的字段进行排序 排序的主要关键字应该是第1级汇总关键字 排序的次要关键字应该是第2级汇总关键字 其他的依次类推 有几套分类汇总就需要进行几次分类汇总操作 第2次汇总是在第1次汇总的结果上进行操作的 第3次汇总操作是在第2次汇总的结果上进行的 依次类推 第一次分类汇总结果 嵌套分类汇总结果 3 3 4分级显示或隐藏汇总结果 1 分级显示明细数据 在如分级显示符号中 单击所需级别的数字 较低级别的明细数据会隐藏起来 要显示所有明细数据 可单击分级显示符号的最低级别 即最大数字 要隐藏所有明细数据 可单击 2 隐藏与显示明细数据 单击工作表左侧的折叠按钮 可以根据需要隐藏原始数据 此时该按钮变为 单击该按钮将显示组中的原始数据 3 清除分级显示 不需要分级显示时 可以根据需要将其部分或全部的分级删除 选择要取消分级显示的行 然后单击 数据 选项卡上 分级显示 组中的 取消组合 清除分级显示 项 可取消部分分级显示 要取消全部分级显示 可单击分类汇总工作表中的任意单元格 然后单击 数据 选项卡上 分级显示 组中的 取消组合 清除分级显示 项即可 3 3 5取消分类汇总 删除分类汇总时 Excel将删除与分类汇总一起插入到列表中的分级显示符号和任何分页符 3 4合并计算 所谓合并计算 是指用来汇总一个或多个源区域中数据的方法 合并计算不仅可以进行求和汇总 还可以进行求平均值 计数统计和求标准差等运算 利用它可以将各单独工作表中的数据合并计算到一个主工作表中 单独工作表可以与主工作表在同一个工作簿中 也可位于其他工作簿中 Excel提供了两种合并计算数据的方法 一是按位置合并计算 二是按分类合并计算 3 4 1建立合并计算 要想合并计算数据 首先必须为合并数据定义一个目标区 用来显示合并后的信息 此目标区域可位于与源数据相同的工作表中 也可在另一个工作表中 其次 需要选择要合并计算的数据源 此数据源可以来自单个工作表 多个工作表或多个工作簿 1 按位置合并计算 按位置合并计算要求源区域中的数据使用相同的行标签和列标签 并按相同的顺序排列在工作表中 且没有空行或空列 要进行合并计算的工作表 添加第一个数据源区域 将各书店一月和二月的各类图书销量 位于不同的工作表中合并计算到一个主工作表中 2 按分类合并计算 当源区域中的数据没有相同的组织结构 但有相同的行标签或列标签时 可采用按分类合并计算方式进行汇总 要进行合并计算的工作表 将各书店二月和三月的各类图书销量合并计算到一个主工作表中 3 4 2更改合并计算 1 增加源区域 新的数据源必须在相同位置中有数据 如果此前是按位置进行合并计算 或者有与合并计算中其他区域内的那些标签匹配的标签 如果此前是按分类进行合并计算 添加数据源区域后的合并计算结果 2 从合并计算中删除部分源区域 删除部分源数据也就是改变源区域的引用 重新调整合并计算结果的单元格区域 删除部分引用及合并计算结果 3 5假设分析 Excel2007提供了与假设前提相关的三种简单的数据分析方法 方案管理器 单变量求解和模拟数据表 它们的共同特点是问题的求解都由两个部分组成 一个待求解目标模型 一组与模型相关的变量 通过假设求解过程可以获得两种类型的结果 一是在指定假设目标结果的制约下与目标相关某因素的可行解 二是能够在指定变量值域范围的前提下观察各变量对于模型所产生的影响 3 5 1方案管理器 方案是Excel保存在工作表中并可进行自动替换的一组值 用户可以使用方案管理器来预测工作表模型的输出结果 同时还可以在工作表中创建并保存不同的数值组 然后切换到任意新方案以查看不同的结果 假设有如下3种贷款类型供用户选择 下面根据这些贷款类型定义方案管理器 以便选择一种合适的贷款方案 创建方案运算区域 计算出本金为4000000 年利率为5 68 的月还款额 创建 贷款A 方案 用同样的方法创建 贷款B 和 贷款C 方案 汇总各种方案并查看各种方案的详细信息 从中选择一种合适的贷款方案 3 5 2单变量求解 单变量求解是解决假定一个公式要取得某一结果值 其中变量的引用单元格应取值为多少的问题 Excel根据所提供的目标值 将引用单元格的值不断调整 直至达到所需要求的公式的目标值时 变量的值才得以确定 例如 假设一个销售员的年终奖是全年销售额的0 35 前三个季度的销售额已经知道 该销售员想知道第四季度的销售额为多少时 才能保证年终奖金为3500元 此时可用单变量求解来计算 选定包含想产生特定数值的公式的目标单元格 3 5 3数据表 数据表是一个单元格区域 用于显示在一个或多个公式中替换不同值所得到的结果 Excel提供了两种类型的数据表 单变量数据表 公式中只有一个变量 和双变量数据表 公式中有两个变量 数据表可以将变量取不同值时对应的结果一次计算出来 并按规律将结果显示在工作表中 从而便于查看 比较和作出决策 假设一个项目初始投资300 000元 经过分析论证 估计项目的建设可以获得年收益5 5 左右 预计投资时间为12年 如果年收益百分比发生了变化 如分别为3 4 5 和6 时 如何了解年收益总额的不同情况呢 假设 年收益总额 初始投资总额 1 年收益百分比 投资年 此时可利用单变量数据表进行计算 建立数据表模型 计算年收益率为5 5 时的年收益总额 计算总收益和总收益率 计算不同收益率时的总收益和总收益率 包含公式和替换值的单元格区域 假设年收益百分比和投资时间都发生变化 如 年收益率分别为3 4 5 和6 投资时间分别为7 11年 如何查看到期总额呢 此时可利用双变量数据表进行计算 投资时间为12年 年收益率为5 5 时的年收益总额 创建数据表模型 选中要进行计算的数据表区域 在 输入引用行的单元格 编辑框中单击 然后在工作表中单击C3单元格 在 输入引用列的单元格 编辑框中单击 然后在工作表中单击C4单元格 计算出不同年收益百分比和投资时间所对应的到期总额 综合实例 对家电销售表数据进行管理 具体操作步骤见视频6 1 首先将工作表以 产品名称 为第一关键字 产品型号 为第二关键字进行升序排序 接着筛选出 成交价格 高于2200 销售数量 高于200的数据 重新显示全部数据后查找出 产品型号 含 HV 文本且 销售部门 为 A部 的数据 最后对家电销售表进行分类汇总 统计出每种产品的平均销售数量 本章小结 学完本章内容 用户应重点掌握以下知识 掌握对工作表中的数据进行简单排序 多关键字排序和自定义排序的方法 掌握对工作表中的数据进行自动筛选 自定义筛选和高级筛选的方法 掌握对工作表中的数据进行简单分类汇总 多重分类汇总和嵌套分类汇总的方法 了解分级显示或隐藏汇总结果及取消分类汇总的方法 掌握使用合并计算功能将多个工作表中的数据合并到一个工作表中的方法 掌握利用Excel的假设分析工具对工作表数据进行方案管理 单变量求解和运用数据表进行分析的方法 第4章使用数据透视表和图表 数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格 它能帮助用户分析和组织数据 而利用Excel图表可以直观地反映工作表中的数据 方便用户进行数据的比较和预测 本章主要介绍在Excel中创建和编辑数据透视表和图表的方法 本章内容提要 数据透视表和透视图图表应用 4 1数据透视表和透视图 数据透视表能够将筛选 排序和分类汇总等操作依次完成 不需要使用公式和函数 并生成汇总表格 是Excel强大数据处理能力的具体体现 数据透视图是数据透视表更深一层次的应用 它可将数据以图形形式表示出来 4 1 1创建数据透视表 要创建数据透视表 首先要在工作表中创建数据源 这种数据源可以是现有的工作表数据或外部数据 然后在工作簿中指定要创建的位置 最后设置字段布局 为确保数据可用于数据透视表 在创建数据源时需要做到如下几个方面 删除所有空行或空列 删除所有自动小计 确保第一行包含列标签 确保各列只包含一种类型的数据 而不能是文本与数字的混合 下面我们以创建按职称查看的数据透视表为例介绍数据透视表的创建方法 将 姓名 字段拖到 列标签 区域 职称 字段拖到 行标签 区域 工资 字段拖到 数值 区域 然后在数据透视表外单击 即可创建好数据透视表 4 1 2编辑数据透视表 1 交换数据透视表行 列字段 在 数据透视表字段列表 窗格互换行 列标签字段 2 向数据透视表添加或删除字段 向数据透视表中添加 岗位 和 性别 字段后效果 分别将 岗位 和 性别 字段拖到 列标签 区域 要删除字段 可在 数据透视表字段列表 窗格的 选择要添加到报表的字段 列表中取消某些复选框的选中 或在 数据透视表字段列表 窗格下方的布局部分单击并按住字段名 然后将它拖到数据透视表字段列表窗格之外 3 移动数据透视表 移动数据透视表是将数据透视表从一个工作簿移动到另一个工作簿中 或从一个工作表移动到另一个工作表中 按 Ctrl X 组合键 然后切换到其他工作簿或工作表中 单击要放置数据透视表的位置 再按 Ctrl V 组合键即可 4 1 3设置数据透视表样式 4 1 4更改数据透视表的数据源 数据透视表建好后 还可以更改数据源数值的大小 但该操作不能直接在数据透视表中进行 必须回到数据源工作表中 修改数据后切换到要更新的数据透视表 然后单击 数据透视表工具选项 选项卡上 数据 组中的 刷新 按钮 此时当前数据透视表会闪动一下 表示数据透视表中的数据已自动更新 4 1 5创建数据透视图 数据透视图是以图形形式表示数据透视表中的数据 它通常有一个使用相同布局的相关联的数据透视表 两个报表中的字段相互对应 如果更改了某一报表的某个字段位置 则另一报表中的相应字段位置也会改变 创建数据透视图并设置样式 综合实例 对销售报表中的数据进行透视分析 具体操作步骤见视频7 1 先创建按分店名称查看T恤销售数量的数据透视图 然后利用 数据透视图工具 选项卡中的各选项对其进行编辑美化操作 如调整布局样式 设置图表样式 设置文本填充颜色 筛选出折扣大于85 的记录 再设置文本的字号和形状的效果 最后为数据透视图添加图表标题和坐标轴标题 4 2图表应用 图表以图形化方式表示工作表中的内容 由于图表具有较好的视觉效果 所以可使用户更加方便地查看数据的差异和预测趋势 使用图表还可以让工作表中抽象的数据直观化 让平面的数据立体化 使乏味的数据变得生动起来 4 2 1图表类型与组成元素 Excel2007支持各种类型的图表 可帮助我们以多种方式表示工作表中的数据 单击 插入 选项卡上 图表 组中的各种按钮 然后可在展开的列表中选择需要的图表类型 图表类型列表 柱形图 用于显示一段时间内的数据变化或显示各项之间的比较情况 在柱形图中 通常沿水平轴组织类别 而沿垂直轴组织数值 折线图 可以显示随时间而变化的连续数据 非常适用于显示在相等时间间隔下数据的趋势 在折线图中 类别数据沿水平轴均匀分布 所有值数据沿垂直轴均匀分布 饼图 显示一个数据系列中各项的大小与各项总和的比例 饼图中的数据点显示为整个饼图的百分比 条形图 显示各个项目之间的比较情况 面积图 强调数量随时间而变化的程度 也可用于引起人们对总值趋势的注意 散点图 显示若干数据系列中各数值之间的关系 或者将两组数绘制为xy坐标的一个系列 对于大多数图表 如柱形图和条形图 可以将工作表的行或列中排列的数据绘制在图表中 而有些图形类型 如饼图和气泡图 则需要特定的数据排列方式 股价图 经常用来显示股价的波动 曲面图 显示两组数据之间的最佳组合 圆环图 像饼图一样 圆环图显示各个部分与整体之间的关系 但是它可以包含多个数据系列 气泡图 排列在工作表列中的数据可以绘制在气泡图中 雷达图 比较若干数据系列的聚合值 2 图表组成 4 2 2创建图表 要创建图表 首先要在工作表中输入用于创建图表的数据 然后选择该数据并选择一种图表类型即可 1 嵌入式图表 嵌入图表是指与源数据位于同一个工作表中的图表 当要在一个工作表中查看或打印图表或数据透视图及其源数据或其他信息时 嵌入图表非常有用 插入的嵌入式图表 2 独立图表 如果要创建独立的图表 可先创建嵌入式图表 然后选中该图表 单击 图表工具设计 选项卡上 位置 组中的 移动图表 按钮 打开 移动图表 对话框 选中 新工作表 单选钮 然后单击 确定 按钮 即可在原工作表的前面插入一 Chart 数字 工作表以放置创建的图表 4 2 3编辑图表 1 更改图表类型 对于大多数二维图表 可以更改整个图表的图表类型以赋予其完全不同的外观 也可以为任何单个数据系列选择另一种图表类型 使图表转换为组合图表 对于气泡图和大多数三维图表 只能更改整个图表的图表类型 单击图表区或绘图区 2 调整图表大小 要调整图表大小 可以利用鼠标拖动法 也可以在 图表工具格式 选项卡的 大小 组中的编辑框中直接输入数值进行精确调整 拖动方向 3 移动图表 在工作表内移动 在工作表内移动图表 此图表必须是嵌入式图表 的操作非常简单 将鼠标指针指向图表区的空白处