chapter-5数据管理与数据透视表.ppt

第5章数据管理与数据透视表 Excel数据处理与统计初步第4版 本章学习目标 1 掌握数字与汉字排序的区别2 掌握EXCEL排序的方法3 理解条件区域4 掌握数据筛选的方法5 掌握工作表数据的分类汇总方法6 掌握数据透视表的功能及用法7 应用数据链接管理多表数据 5 1数据排序 1 排序规则数字排序规则字母排序规则逻辑值排序规则汉字排序规则汉语拼音序按笔画排序2 排序方式升序降序 5 1数据排序 3 排序案例 例5 1 某班某次期末考试成绩如图 a 所示 已经计算出了每位同学的平均分 现需得出一个名次表 即从高分到低分的成绩表 以便为前3名同学发奖学金 5 1数据排序 按日期排序 5 1数据排序 按汉字笔画排序 5 1数据排序 自定义排序有时需要按人为指定的次序对某此资料排序 例 某大学有7个学院 每个学院的基本情况如图所示 要求按以下次序排序此表 计算机学院 管理学院 通信学院 财务学院 外语学院 政法学院 中文学院 5 1数据排序 方法建立自定义序列在排序对话框中 通过 选项 指定建立的序列为排序依据 5 2数据筛选 1 概念数据筛选就是将数据表中所有不满足条件的记录行暂时隐藏起来 只显示那些满足条件的数据行 2 Excel的数据筛选方式1 自动筛选自动筛选提供了快速查找工作表数据的功能 只需通过简单的操作 就能够筛选出需要的数据 2 高级筛选高级筛选能够完成自动筛选所不能实现的复杂筛选 5 2数据筛选 3 自动筛选案例某公司的人员档案是按招聘报到的先后次序建立的 到目前为止 该公司的人员档案如下图9所示 公司的管理人员可能需要这样的信息 1 已婚人员的名单 2 各部门的人员档案信息 3 工资高于1200的人员有哪些 4 奖金高于1000的人员有哪些 5 各种学历的人员档案 如此等等 5 2数据筛选 选择 数据 筛选 自动筛选 Excel会显示出自动筛选标志 可借此完成各种自动筛选 原数据表 具有自动筛标记的数据表 5 2数据筛选 自动筛选的结果示例 筛选出运维部的工作人员 5 2数据筛选 4 高级筛选高级筛选的条件区域的类型 1 或 条件 2 与 条件 3 多列的 或 条件 4 用 或 连接的 与 条件 5 2数据筛选 条件区域的构建方法 5 2数据筛选 高级筛选案例查看下表中 运维部 和 信息资源部 人员中 工资高于1000且奖金高于500元的有哪些 5 2数据筛选 使用计算条件的高级筛选什么是计算条件计算条件的构造方法计算条件中的标题可以是任何文本或都是空白 但不能与数据清单中的任一列标相同 这一点正好与前面讨论的条件区域相反 必须以绝对引用的方式引用数据清单或数据库中之外的单元格 必须以相对引用的方式引用数据清单或数据库中之内的单元格 5 2数据筛选 基于计算条件的高级筛选案例某商场有2月1号到8号的销售记录如图的A2 F10单元格区域所示 现在要找出销售额高于平均值的销售记录 5 3数据的分类与汇总 1 概述分类汇总就是对工作表中指定的行或列中的数据进行汇总统计 它通过折叠或展开原工作表中的行 列数据及汇总结果 从汇总和明细两种角度显示数据 可以快捷地创建各种汇总报告 分类汇总的数据折叠层次可达8层 2 分类汇总能够完成以下事性在数据表中显示一组或多组数据的分类汇总及总和 在分组数据上完成不同的计算 如求和 统计个数 求平均数 求最大 最小值 等 5 3数据的分类与汇总 案例右图是某公司的职工工资表 完成以下事情 每部门有多少人 每部门平均工资 哪个部门的总评奖金最高 应分发给各部门的总金额各是多少 通过函数能够完成这些事件 但分类汇总简单多了 5 3数据的分类与汇总 3 分类汇总的准备工作 分类汇总表的每个数据列都有列标题 必须对要进行分类汇总的列排序 排序的列标题称为分类汇总关键字 在进行分类汇总时 只能指定排序后的列标题为汇总关键字 按部门排序后的工作表 5 3数据的分类与汇总 4 建立分类汇总选择 数据 分类汇总 菜单 1 指定分类字段 2 指定汇总方式 3 指定汇总字段 4 指定汇总结果的保存方式 5 3数据的分类与汇总 5 汇总结果的保存方式替换当前分类汇总 选择这种方式时 最后一次的汇总会取代以前的分类汇总 每组数据分页 选择这种方式时 各种不同的分类数据将被分页保存 汇总结果显示在数据下方 选择这种方式时 原数据的下方会显示汇总计算的结果 上述3种方式可同时选中 Excel的默认选择是第1和第3项 5 3数据的分类与汇总 6 分类汇总结果示例 5 3数据的分类与汇总 7 多次分类汇总Excel可以对同一分类进行多重汇总 若要在同一汇总表中显示两个以上的汇总数据 只需对同一数据清单进行两次不同的汇总运算 第二次分类汇总在第一次的汇总结果上进行 在前面的汇总结果基础上 统计每部门的人数 5 3数据的分类与汇总 8 删除分类汇总对于刚建立的分类汇总 如果要将其恢复为原始数据表 可以选择 编辑 中的 撤消 菜单项取消分类汇总 或单击撤消按钮 但若在建立分类汇总之后 又进行了其他操作 上述方法就无效了 可按如下方法删除分类汇总 1 单击分类汇总表中的任一单元格 2 选择 数据 分类汇总 菜单项 3 单击 分类汇总 对话框中的 全部删除 按钮 5 4数据透视表 数据透视表的功能数据透视表不仅能够改变数据表的行 列布局 而且能够快速汇总大量数据 也能够基于原数据表创建数据分组 对建立的分组进行汇总统计 5 4数据透视表 2 源数据表与数据透视表的对应关系 5 4数据透视表 3 案例假设某电视销售商场有6位职工 分别是劳得诺 令狐冲 陆大安 任我行 韦小宝 向问天 销售的电视产品有TCL 长虹 康佳 创维 熊猫几种品牌 每天的销售数据都记录在Excel的一个工作表中 如图所示 该数据表有300多行数据 5 4数据透视表 完成以下统计分析工作统计各种品牌的电视的总销售额 统计每位职工的总销售额 统计每位职工销售的各种类型的电视机的销售额 统计各种运输方式的总数 数据透视表能够很方便地完成这些工作 5 4数据透视表 4 建立数据透视表的过程选择 数据 数据透视表和数据透视图 按向导提示做完第一 二步 在最后的第三步向导对话框中 选择 布局 按钮 会见到如图所示的对话框 5 4数据透视表 其中行拖放到行中的数据字段 该字段中的第一个数据项将占据透视表的一行 列与行相对应 放置在列中的字段 该字段中的每个项将占一列 页行和列相当于X轴和Y轴 由它们确定一个二维表格 页则相当于Z轴 拖放在页中的字段 Excel将按该字段的数据项对透视表进行分页 数据进行计数或汇总的字段名称 5 4数据透视表 统计各销售员销售数据的透视表布局 5 4数据透视表 统计各销售员销售数据的透视表 双击任一单元格 会得到这些数据的来源明细数据 5 4数据透视表 双击上表E5单元格得到的明细数据表 5 4数据透视表 利用页字段创建分页显示 页字段 5 4数据透视表 5修改数据透视表增加或减少行 列中的数据项重新组织数据透视表行 列置换在字段内移动个别项 5 4数据透视表 6 使用多重数据字段建立透视表 5 4数据透视表 7 修改数据透视表的概要函数数据透视表中使用的透视函数称为概要函数 在默认情况下 数据透视表采用SUM函数对其中的数值项进行汇总 用COUNT函数对表中的文本类型字段项进行计数 Excel提供了许多概要函数 如下图所示 5 4数据透视表 8 修改透视表数据的显示方式 以百分比显示数据 5 4数据透视表 9 制作数据透视图表 5 5数据链接 嵌入 1 概述链接就是指两个不同工作表或不同文档 可以是不同应用程序建立的文档 如WORD和EXCEL之间 存在的数据连接 链接让一个工作簿可以共享另一个工作薄中的数据 可以链接单元格 单元格区域 命名公式 常量或工作表 总的说来 链接具有以下优点 在不同的工作簿和工作表之间 可以进行数据共享 小工作簿比大工作簿的运行效率更高 分布在不同地域中的数据管理可以在不同的工作簿中完成 通过超级链接可以进行远程数据采集 更新和汇总 可以在不同的工作薄中修改 更新数据 大家可以同时工作 如果所有的数据都存于一个工作表中 就增加了多人合作办公的难度 5 5数据链接 嵌入 2 建立EXCEL工作薄之间的链接 例5 某茶厂生产的茶叶销售于西南地区 每个季度的销售统计分别在不同的工作簿中 下图就是第一 二季度的茶叶销售统计工作薄示例 5 5数据链接 嵌入 建立一新工作薄 将这前面两不同工作薄中的工作表连接在该工作薄 本工作薄中的两个工作表是从其它工作薄链接过来的 修改原工作表中的数据时 本工作薄中的数据会立即更新 5 5数据链接 嵌入 3 将Excel的工作表或图表链接到其他应用程序案例某茶叶厂第一季度的销售数据表及销售对比柱形图如图所示 现要将此图表作为在Word工作报表中的一部份 以使报告更有说服力 5 5数据链接 嵌入 链接到Word报告中的Excel工作表 5 6合并计算 1 概念所谓合并计算 就是对来源于同一工作簿的不同工作表 或不同工作簿中的数据表进行合并计算 Excel支持将不超过255个工作表中的信息收集到一个主工作表中 支持