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