Excel-07.ppt

第7章数据管理与数据透视表 Excel与数据处理第3版 本章学习目标 1 掌握数字与汉字排序的区别2 掌握EXCEL排序的方法3 理解条件区域4 掌握数据筛选的方法5 掌握工作表数据的分类汇总方法6 掌握数据透视表的功能及用法7 应用数据链接管理多表数据 7 1数据排序 1 排序规则数字排序规则字母排序规则逻辑值排序规则汉字排序规则汉语拼音序按笔画排序2 排序方式升序降序 7 1 2数值排序 3 排序案例 例7 1 某班某次期末考试成绩如图 a 所示 已经计算出了每位同学的平均分 现需得出一个名次表 即从高分到低分的成绩表 以便为前3名同学发奖学金 7 1 2数值排序 按日期排序 7 1 3汉字与字符排序 按汉字笔画排序 1 选中要排序的数据区域后 单击 排序和筛选 按钮 2 单击 自定义排序 按钮 弹出 排序 对话框 3 单击 选项 按钮 弹出 选项 对话框 3 指定 笔画序 7 1 4自定义排序次序 自定义排序有时需要按人为指定的次序对某此资料排序 例7 3 某大学有7个学院 每个学院的基本情况如图所示 要求按以下次序排序此表 计算机学院 管理学院 通信学院 财务学院 外语学院 政法学院 中文学院 7 1 4自定义排序次序 方法单击Office按钮 Excel选项 常用 使用Excel时采用的首选项 编辑自定义列表 Excel会弹出 选项 对话框按前面的方式显示出 排序 对话框中 通过 序序 下面的 自定义序列 指定建立的序列为排序依据 在此输入自定义的排序序列 7 1 5账号 零件号等排序 账号 零件号排序的困难由于账号 零件号常采用不同的区段进行编码 为排序带来了困难 账号 零件号排序的解决方法一种方法是确保同类编号中的任何部分都有相同的长度 例如在上述例子中 将 ZK 999 10 输入为 ZK 0999 10 这样在比较排序时就不会出错了 另一种方法是把编号的不同部分输入在不同列的单元格中 如上面两个编号用图7 6所示的方式输入 在排序时 对编号所涉及到的三列同时进行排序 排序结果就不会出错 最后再用 运算符把排序后的各部分组合起来 7 1 6多关键字排序 多关键字排序就是对数据表中的数据按两个或两个以上的关键字进行排序 多关键字排序可使数据在第一关键字相同的情况下 按第二关键字排序 在第一 第二关键字都相同的情况下 数据按第三关键字有序 其余的以此类推 在Excel2007中 最多允许有64个排序关键字 但不管有多少关键字 排序之后的数据总是按第一关键字有序的 7 1 6多关键字排序 例7 4 2004年某杂货店各雇员的销售数据如图7 7 a 的A1 F7所示 以第一季度为第一关键字 第二季度为第二关键字 第三季度为第三关键字的排序结果如图7 7 a 的A9 F15区域所示 排序的方式是递增 1 源数据表 2 单击 开始 排序和筛选 自定义排序 命令 弹出 排序 对话框 3 依次指定排序关键字 3 排序结果 7 2数据筛选 1 概念数据筛选就是将数据表中所有不满足条件的记录行暂时隐藏起来 只显示那些满足条件的数据行 2 Excel的数据筛选方式1 自动筛选自动筛选提供了快速查找工作表数据的功能 只需通过简单的操作 就能够筛选出需要的数据 2 高级筛选高级筛选能够完成自动筛选所不能实现的复杂筛选 7 2 1数据筛选 3 自动筛选案例 例7 5 某公司的人员档案是按招聘报到的先后次序建立的 如图7 8 a 所示 可以看出 图7 8的档案比较混乱 公司的管理人员可能需要这样的信息 已婚人员的名单 各部门的人员档案信息 工资高于1200的人员有哪些 奖金高于1000的人员有哪些 各种学历的人员档案 1 已婚人员的名单 2 各部门的人员档案信息 3 工资高于1200的人员有哪些 4 奖金高于1000的人员有哪些 5 各种学历的人员档案 如此等等 7 2 1数据筛选 原数据表 具有自动筛标记的数据表 单击 数据 筛选 Excel会在表格的标题边显示出自动筛选标志 可借此完成各种自动筛选 7 2 1数据筛选 自动筛选的结果示例 筛选出运维部的工作人员 7 2 1数据筛选 如果筛选关键字对应的列是数值型数据 可通过 数字筛选 命令指定自定义筛选条件 实现灵活的数据查询 例7 6 在图7 8 a 所示的职工档案表中 筛选年龄最大的3名职工 1 单击 数据 筛选 为数据表显示出自动筛选标志 2 单击 年龄 右边的自动筛选下拉箭头 3 单击 10个最大的值 弹出对话框 4 将 10 改为3 即可筛选出年龄最大的3位员工 例7 7 查看图7 10中职工档案表中工资高于2800元的人员 1 指定工作表的 筛选 标志 2 单击 工资筛选 中的 大于 命令 3 在弹出的对话框中输入工资数据 5 2数据筛选 4 高级筛选高级筛选的条件区域的类型 1 或 条件 2 与 条件 3 多列的 或 条件 4 用 或 连接的 与 条件 7 2 2条件区域 条件区域的构建方法 7 2 3高级筛选 1 条件 与筛选 例7 8 查看例7 5 图7 8 中 运维部 工资和奖金都高于500元的人员 1 单击 高级 按钮 弹出高级筛选设置对话框 2 指定筛选结果的保存方式 2 指定筛选条件和数据区域 7 2 3高级筛选 2 条件 或 的高级筛选 例7 9 对于图7 8 现要找出其中奖金小于500或已经结婚的工作人员 7 2 3高级筛选 3 与条件同或条件的组合应用 例7 10 对于前面讨论的职工档案数据表 现要查找运维部和信息资源部中工资高于1000且奖金高于500的人员档案 7 2 3高级筛选 4 使用计算条件的高级筛选什么是计算条件计算条件的构造方法计算条件中的标题可以是任何文本或都是空白 但不能与数据清单中的任一列标相同 这一点正好与前面讨论的条件区域相反 必须以绝对引用的方式引用数据清单或数据库中之外的单元格 必须以相对引用的方式引用数据清单或数据库中之内的单元格 7 2 3高级筛选 基于计算条件的高级筛选案例 例7 11 某商场2003年2月1号到8号的销售记录如图7 18的区域A2 F10所示 找出其中销售额高于平均值的销售记录 7 3数据的分类与汇总 1 概述分类汇总就是对工作表中指定的行或列中的数据进行汇总统计 它通过折叠或展开原工作表中的行 列数据及汇总结果 从汇总和明细两种角度显示数据 可以快捷地创建各种汇总报告 分类汇总的数据折叠层次可达8层 2 分类汇总能够完成以下事性在数据表中显示一组或多组数据的分类汇总及总和 在分组数据上完成不同的计算 如求和 统计个数 求平均数 求最大 最小值 等 7 3数据的分类与汇总 案例右图是某公司的职工工资表 完成以下事情 每部门有多少人 每部门平均工资 哪个部门的总评奖金最高 应分发给各部门的总金额各是多少 通过函数能够完成这些事件 但分类汇总简单多了 7 3数据的分类与汇总 3 分类汇总的准备工作 分类汇总表的每个数据列都有列标题 必须对要进行分类汇总的列排序 排序的列标题称为分类汇总关键字 在进行分类汇总时 只能指定排序后的列标题为汇总关键字 按部门排序后的工作表 7 3数据的分类与汇总 4 建立分类汇总单击 数据 选项卡中的 分类汇总 命令 显示 分类汇总 对话框 1 指定分类字段 2 指定汇总方式 3 指定汇总字段 4 指定汇总结果的保存方式 7 3数据的分类与汇总 5 汇总结果的保存方式替换当前分类汇总 选择这种方式时 最后一次的汇总会取代以前的分类汇总 每组数据分页 选择这种方式时 各种不同的分类数据将被分页保存 汇总结果显示在数据下方 选择这种方式时 原数据的下方会显示汇总计算的结果 上述3种方式可同时选中 Excel的默认选择是第1和第3项 7 3数据的分类与汇总 6 分类汇总结果示例 7 3 3高级分类与汇总 7 多次分类汇总Excel可以对同一分类进行多重汇总 若要在同一汇总表中显示两个以上的汇总数据 只需对同一数据清单进行两次不同的汇总运算 第二次分类汇总在第一次的汇总结果上进行 在前面的汇总结果基础上 统计每部门的人数 7 3 4嵌套分类汇总 1 嵌套分类汇总在一个已经建立了分类汇总的汇总表中再进行另一种分类汇总 两次的分类汇总关键字不同 这种分类汇总方式称为嵌套分类汇总 2 嵌套分类汇总的排序方式建立嵌套分类汇总的前提仍然是要对每个分类汇总关键字排序 第一级汇总关键字应该是排序的第一关键字 第二级汇总关键字应该是第二排序关键字 其余以此类推 有几层嵌套汇总就需要进行几次分类汇总操作 第二次汇总在第一次的结果集上操作 第三次在第二次的结果是操作 其余以此类推 在一个已经建立了分类汇总的汇总表中再进行另一种分类汇总 两次的分类汇总关键字不同 这种分类汇总方式称为嵌套分类汇总 建立嵌套分类汇总的前提仍然是要对每个分类汇总关键字排序 第一级汇总关键字应该是排序的第一关键字 第二级汇总关键字应该是第二排序关键字 其余以此类推 7 3 4嵌套分类汇总 以部门为第一汇总关键字 婚否为第二汇总关键字进行的嵌套分类汇总 7 3 5删除分类汇总 8 删除分类汇总对于刚建立的分类汇总 如果要将其恢复为原始数据表 可以选择 编辑 中的 撤消 菜单项取消分类汇总 或单击撤消按钮 但若在建立分类汇总之后 又进行了其他操作 上述方法就无效了 可按如下方法删除分类汇总 1 单击分类汇总表中的任一单元格 2 选择 数据 选项卡中的 分类汇总 命令 3 单击 分类汇总 对话框中的 全部删除 按钮 7 4数据透视表 数据透视表的功能 对数值数据进行分类汇总和聚合 展开或折叠要关注结果的数据级别 查看感兴趣区域摘要数据的明细 将行移动到列或将列移动到行 或 透视 以查看源数据的不同汇总 对最有用和最关注的数据子集进行筛选 排序 分组和有条件地设置格式 突出显示重要信息 提供简明 带有批注的联机报表或打印报表 7 4数据透视表 2 源数据表与数据透视表的对应关系 7 4 2建立数据透视表 3 案例 注 数据透视表的全部案例都在Ch7 15 Xlsx工作薄的各个工作表中 例7 15 假设某电视销售商场有6位职工 分别是劳得诺 令狐冲 陆大安 任我行 韦小宝 向问天 销售的电视产品有TCL 长虹 康佳 创维 熊猫几种品牌 每天的销售数据都记录在Excel的一个工作表中 如图所示 该数据表有300多行数据 7 4 2数据透视表 完成以下统计分析工作统计各种品牌的电视的总销售额 统计每位职工的总销售额 统计每位职工销售的各种类型的电视机的销售额 统计各种运输方式的总数 数据透视表能够很方便地完成这些工作 7 4 2数据透视表 4 建立数据透视表的过程 1 建立源数据表后 单击 插入 选项卡中的 数据透视表 弹出 创建数据透视表 对话框 1 创建数据透视表 对话框 1 指定数据透视表的数据区域后 单击 确定 按钮 将弹出数据透视表的布局设置对话框 2 根据第1步设置创建的数据透视表 1 用鼠标将数据字段拖放到行 列标签和数值汇总对应的文本框中 7 4 2数据透视表 其中数据透视表布局中的内容行标签拖放到行中的数据字段 该字段中的第一个数据项将占据透视表的一行 列标签与行相对应 放置在列中的字段 该字段中的每个项将占一列 报表筛选行和列相当于X轴和Y轴 由它们确定一个二维表格 页则相当于Z轴 拖放在页中的字段 Excel将按该字段的数据项对透视表进行分页 数值进行计数或汇总的字段名称 7 4 2数据透视表 统计各销售员销售数据的透视表布局 7 4 3查看透视表中数据的具体来源 统计各销售员销售数据的透视表 双击任一单元格 会得到这些数据的来源明细数据 7 4 3查看透视表中数据的具体来源 双击上表E5单元格得到的明细数据表 7 4 4利用报表筛选创建分页显示 利用报表筛选 即页字段 创建分页显示 页字段 用鼠标单击它 从下拉列表中选择某个日期 可以查看此日期的销售数据 5 4数据透视表 5修改数据透视表增加或减少行 列中的数据项重新组织数据透视表行 列置换在字段内移动个别项 7 4 5建立多字段的数据透视表 6 使用多重数据字段建立透视表 例7 16 在前面所讨论的电视销售数据表中 要查看每位职工销售了多少种产品 每种产品的数量各是多少 而且还要查看其销售的产品中 每种运输方式各运输了多少数量 在行标签中放置了 销售员姓名 和 运输方式 两字段 7 4 6修改数据透视表的汇总函数 7 修改数据透视表的汇总函数数据透视表中使用的透视函数称为汇总函数 在默认情况下 数据透视表采用SUM函数对其中的数值项进行汇总 用COUNT函数对表中的文本类型字段项进行计数 Excel提供了许多汇总函数 如下图所示 单击 求和项 的下拉箭头 从弹出的菜单中选择 值字段设置 弹出值字段设置对话框 在此选择需要的 汇总函数 7 4 7修改透视表数据的显示方式 8 修改透视表数据的显示方式 以百分比显示数据 单击 求和项 的下拉箭头 从弹出的菜单中选择 值字段设置 弹出值字段设置对话框 单击 此 下拉箭头 从弹出的菜单中选择 占总和的百分比 7 4 8显示数据项的明细数据 例7 19 查看图7 32 a 中创为电视各种运输方式明细数据的百分比 如图7 33 a 所示 用鼠标右击某一产品名称 从弹出的菜单中选择 展开 折叠 显示出 显示明细数据 对话框 从中选择要显示明细数据的数据项名称 7 4 9对日期进行分组透视 在日常工作中 常常需要制作月报表 季度报表或年度报表之类与日期密切相关的报表 这类报表的制作往往需要对源数据表中的日期和时间进行分组计算 才能制作出来 例如 对于前面介绍的电视销售记录 要制作出每种产品在每个月的员工销报表 其制作方法是把源数据中的产品名称字段放在数据透视表的 报表筛选 中 把销售员姓名字段放在 行标签 中 把日期字段放在 列标签 中 得到的数据透视表如图7 34 a 所示 很显然 图7 34 a 不是我们需要的数据透视表 需要的是图7 34 b 所示的透视报表 1 以 销售员姓名 为行标签 日期 为列标签制作数据透视表 2 单击列标签下面的任何一个日期 如 1997年2月27日 然后单击 数据透视表工具 选项 将所选内容分组 Excel会弹出 分组 设置对话框 3 选择一种分组的时间方式 如月 对日期进行分组透视的过程 7 4 10制作数据透视图表 9 制作数据透视图表 7 5数据链接 嵌入 1 链接概述链接就是指两个不同工作表或不同文档 可以是不同应用程序建立的文档 如WORD和EXCEL之间 存在的数据连接 链接让一个工作簿可以共享另一个工作薄中的数据 可以链接单元格 单元格区域 命名公式 常量或工作表 总的说来 链接具有以下优点 在不同的工作簿和工作表之间 可以进行数据共享 小工作簿比大工作簿的运行效率更高 分布在不同地域中的数据管理可以在不同的工作簿中完成 通过超级链接可以进行远程数据采集 更新和汇总 可以在不同的工作薄中修改 更新数据 大家可以同时工作 如果所有的数据都存于一个工作表中 就增加了多人合作办公的难度 7 5 1建立链接 2 建立EXCEL工作薄之间的链接 例7 20 某茶厂生产的茶叶销售于西南地区 每个季度的销售统计分别在不同的工作簿中 下图就是第一 二季度的茶叶销售统计工作薄示例 7 5 1建立链接 建立一新工作薄 将这前面两不同工作薄中的工作表连接在该工作薄 本工作薄中的两个工作表是从其它工作薄链接过来的 修改原工作表中的数据时 本工作薄中的数