excel数据透视表PPT课件

Excel与数据处理 第五章数据管理与数据透视表 本章教学目的与要求 1 掌握工作表数据的排序2 掌握工作表数据的自动筛选3 掌握工作表数据的高级筛选4 掌握工作表数据的分类汇总5 掌握数据透视表的应用6 掌握数据链接的应用7 掌握多表数据的合并计算 本章重点 难点及学时数 重点 1 数据的排序2 数据的筛选3 数据的分类汇总4 数据透视表的应用5 数据链接应用6 数据的合并计算难点 数据的高级筛选学时数 12学时 上机6学时 本章目录 5 1数据排序5 2数据筛选5 3数据的分类与汇总5 4数据透视表5 5数据链接5 6数据合并计算小结思考与练习 5 1数据排序 1 排序的规则升序 数字 字符 逻辑值 错误值 空格降序 错误值 逻辑值 字符 数字 空格数字排序规则 最小负数到最大正数字母排序规则 逐个对应字符相比以不相同字符大小确定字符串大小字符顺序 0 9空格 A Z逻辑值排序规则 FALSE小于TRUE汉字排序规则 汉语拼音排序和笔画排序 5 1数据排序 2 排序的方法 菜单 数据 排序 对话框中选择关键字 选择升降序 确定数值排序例 某班某次期末考试成绩 已经计算出每位同学的平均分 现需得出一个从高分到低分的成绩表 见数据排序 xls 数值排序 日期排序例 某公司的工资表 要求按照日期的升序进行排列 见数据排序 xls 日期排序 5 1数据排序 汉字字符排序例 某公司的工资表 要求按照部门的降序 默认为拼音 和姓名的笔画降序进行排列 见数据排序 xls 汉字字符排序 5 1数据排序 自定义排序 按照人为指定的次序进行排序 例 某大学有7个学院 要求按以下次序排序此表 计算机学院 管理学院 通信学院 财务学院 外语学院 政法学院 中文学院 见数据排序 xls 自定义排序 方法 菜单 工具 选项 对话框中选择自定义序列 按照要求的排序次序设置一个自定义序列 菜单 数据 排序 对话框中选择选项按钮 选择排序序列 确定 5 1数据排序 多重排序 对数据表中的数据按照两个或以上的关键字进行排序 在第一关键字相同的情况下按照第二关键字排序 在第二关键字相同的情况下按照第三关键字排序 例 有2004年某杂货店各雇员的销售数据表 要求以第一季度为第一关键字 第二季度为第二关键字 第三季度为第三关键字进行排序 见数据排序 xls 多重排序 方法 菜单 数据 排序 对话框中选择主 次 第三关键字 选择升降序 确定 目录 5 2数据筛选 概述数据筛选就是将数据表中所有不满足条件的记录行暂时隐藏起来 只显示那些满足条件的数据行 筛选的方式自动筛选自动筛选提供了快速查找工作表数据的功能 只需通过简单的操作 就能够筛选出需要的数据 高级筛选高级筛选能够完成自动筛选所不能实现的多条件复杂筛选 筛选的方法选择菜单 数据 筛选 自动筛选 或 高级筛选 命令 5 2数据筛选 1 自动筛选选择菜单 数据 筛选 自动筛选 命令 Excel会显示出自动筛选标志 即下拉列表标志 筛选的下拉列表中各项列表的意义 前10个 只显示数据表中满足指定条件的前若干个数据行 可用于选择最大或最小的若干个数据行 自定义 表示用户自己定义筛选条件 并在 自定义自动筛选方式 对话框中键入条件 全部 将工作表恢复到筛选前的状态 显示所有数据行 注 自定义自动筛选方式 对话框中运算符的解释 始于 表示从左边截取若干字符如 学历始于大 即首字符为大 大学 大专等止于 表示截取最后若干字符如 学历止于士 即末字符为士 硕士 博士等包含 其中含有给定的字符串 5 2数据筛选 例 某公司的人员档案是按招聘报到的先后次序建立的 见数据筛选 xls 自动筛选 公司的管理人员可能需要这样的信息 1 已婚人员的名单 2 各部门的人员档案信息 3 工资高于1200的人员信息 4 奖金高于1000的人员信息 5 各种学历的人员分类档案 如此等等 5 2数据筛选 2 高级筛选条件区域的类型 或 条件 不同行上具有多个条件 与 条件 同行上具有多个条件条件区域的构建条件区域首行必须包含与数据清单中完全相同的列标 首行之下为筛选条件 见数据筛选 xls 条件例 5 2数据筛选 高级筛选操作为高级筛选建立条件区域 单击数据清单中任一非空单元格 选择 数据 菜单中 筛选 子菜单中的 高级筛选 命令 在 高级筛选对话框 中对 数据区域 中的单元格区域进行检验 然后在 条件区域 输入框中输入筛选条件所在的单元格区域 上述操作后可在数据清单中显示筛选结果 若要在其他区域显示筛选结果 只要在 高级筛选对话框 中 方式 中作选择 且给出结果区域 5 2数据筛选 例 查看 运维部 和 信息资源部 工资高于1000且奖金高于500元的人员 见数据筛选 xls 高级筛选 5 2数据筛选 使用计算条件的高级筛选计算条件 是包括任何测试而不是一个简单的列值同常量相比较的结果如 条件例中找出比平均销售量高的销售员 计算条件的构造方法计算条件中的标题不能与数据清单中的任一列标相同 可以是任何文本或都是空白 必须以绝对引用的方式引用数据清单或数据库中之外的单元格 必须以相对引用的方式引用数据清单或数据库中之内的单元格 5 2数据筛选 例 某商场有2月1号到8号的销售记录如图A2 F10单元格区域所示 现在要找出销售额高于中间值的销售记录 见数据筛选 xls 计算条件筛选 5 2数据筛选 MEDIAN函数功能 返回给定数值集合的中位数 中位数是在一组数据中居于中间的数 即在这组数据中有一半的数据比它大 有一半的数据比它小 格式 MEDIAN number1 number2 Number1 number2 是需要找出中位数的1到30个数字参数 说明 参数必须是数字 名称 数组或包含数字的引用 如果数组或引用参数中包含有文字 逻辑值或空白单元格 则忽略这些值 但是其值为零的单元格会计算在内 例 MEDIAN 1 2 3 4 5 等于3MEDIAN 1 2 3 4 5 6 等于3 5 即3与4的平均值 目录 5 3数据的分类与汇总 1 概述分类汇总是对工作表中指定的行或列中的数据进行汇总统计 通过折叠或展开原工作表中的行 列数据及汇总结果 从汇总和明细两种角度显示数据 可快捷地创建各种汇总报告 分类汇总的数据折叠层次可达8层 2 功能在数据表中显示一组或多组数据的分类汇总数据 在分组数据上完成不同的计算 如求和 统计个数 求平均数 求最大 最小值 等 5 3数据的分类与汇总 3 建立分类汇总准备工作 分类汇总表的每个数据列都有列标题 对要进行分类的列排序 说明 排序的列标题称为分类汇总关键字 在进行分类汇总时 只能指定排序后的列标题为汇总关键字 操作方法 选择 数据 分类汇总 菜单 打开分类汇总对话框 在对话框中完成操作 5 3数据的分类与汇总 5 3数据的分类与汇总 汇总结果保存替换当前分类汇总 最后一次的汇总会取代以前的分类汇总 每组数据分页 各种不同的分类数据将被分页保存 汇总结果显示在数据下方 原数据的下方会显示汇总计算的结果 说明 以上3种方式可同时选中 Excel的默认选择是第1和第3项 5 3数据的分类与汇总 分类汇总的删除对刚建立的分类汇总 若要恢复为原始数据表 则选择 编辑 中的 撤消 菜单项取消分类汇总 或单击撤消按钮 若建立分类汇总之后又进行了其他操作 可按如下方法删除分类汇总 1 单击分类汇总表中的任一单元格 2 选择 数据 分类汇总 菜单项 3 单击 分类汇总 对话框中的 全部删除 按钮 5 3数据的分类与汇总 例 在职工工资表中完成以下工作 见分类汇总 xls 一级分类汇总1 2 3 1 各部门有多少人 2 分发给各部门的奖金总金额 3 各部门的平均工资 通过函数能够完成这些事件 但分类汇总简单多了 5 3数据的分类与汇总 多次分类汇总Excel可以对同一分类进行多次汇总 若要在同一汇总表中显示两个以上的汇总数据 只需对同一数据清单进行两次不同的汇总运算 第二次汇总在第一次的汇总结果上进行 例 在汇总各部门奖金总金额的基础上 统计每部门的人数 见分类汇总 xls 二级分类汇总 多重分类汇总对多个字段进行多重排序之后分别汇总 目录 5 4数据透视表 1 数据透视表的作用能够改变数据表的行 列布局 能够快速汇总大量数据 能够基于原数据表创建数据分组 并对分组进行汇总统计 5 4数据透视表 2 数据透视表的建立选择 数据 数据透视表和数据透视图 按向导提示做完1 2步 在第3步中选择 版式 按钮 出现如图所示的对话框 5 4数据透视表 解释 行 拖放到行中的数据字段中的每个数据项将占据透视表的一行 列 拖放到列中的数据字段中的每个数据项将占据透视表的一列 行和列确定一个二维表格 页 拖放在页中的字段 Excel将按该字段的数据项对透视表进行分页 数据 进行计数或汇总的字段名称 5 4数据透视表 例 假设某电视销售商场有6位职工销售几种品牌电视产品 每天销售数据都记录在Excel的一个工作表中 数据透视表 Xls 欲完成以下统计分析工作 统计每位销售员的总销售量 例1 统计各销售员销售各类电视的数量及销售额 例2 统计销售员陈诺销售TCL品牌的明细表 例3 可从例2得到 统计每天各销售员对各种电视的销售数及销售额 例4 试采用页行列共用方式 统计各销售员对各种电视按不同运输方式的销售数 例5 试采用多重字段方式 5 4数据透视表 例1中统计每位销售员的总销售量 透视表布局如下图所示 5 4数据透视表 3 修改数据透视表显示或隐藏行 列中的数据项在行或列字段的下拉列表条中选择或取消复选标志 重新组织数据透视表行 列置换 鼠标移至行字段上按住拖动到列字段上 列方法类似 例2 在字段内移动个别项 鼠标按在要移动字段边框线上拖动到指定位置放开 在原有行列字段上增加或减少字段增加 单击数据透视表 在数据透视表工具栏上将所需字段拖入行或列的字段处 减少 选择数据透视表行或列的字段名 拖到表外例 在例2中分别增加和减去行字段 运输方式 5 4数据透视表 修改数据透视表的概要函数概要函数 即数据透视表中使用的透视函数 如 求和 计数 平均 百分比 最大值 标准偏差等 操作方法 选择数据透视表中数据字段 在对象菜单或数据透视表工具栏中选择 字段设置 在数据透视表对话框的汇总方式列表中选择所需函数 5 4数据透视表 修改透视表数据的显示方式 见数据透视表 修改数据透视表的显示方式 操作方法 选择数据透视表中数据字段 在对象菜单或数据透视表工具栏中选择 字段设置 在数据透视表对话框中单击 选项 按钮 在 数据显示方式 列表中选择所需的显示方式 以百分比显示数据 5 4数据透视表 4 数据透视图表概述 数据透视图表是根据数据透视表制作的图表 与数据透视表相关联 它们具有彼此对应的字段 若数据透视表中内容更改 数据透视图表中的内容也随之更改 建立数据透视图表的方法单击数据透视表中的非空单元格 选择 插入 菜单的图表命令 或单击常用工具栏中的图表工具 Excel会自动在当前工作簿中插入一个新的数据透视图 目录 5 5数据链接 1 概述链接就是指两个不同工作表或不同文档 可以是不同应用程序建立的文档 如WORD和EXCEL之间 存在的数据连接 链接让一个工作簿可以共享另一个工作薄中的数据 可以链接单元格 单元格区域 命名公式 常量或工作表 链接具有以下优点 在不同的工作簿和工作表之间 可以进行数据共享 小工作簿比大工作簿的运行效率更高 分布在不同地域的数据管理可以在不同工作簿中完成 通过超级链接可以远程数据采集 更新和汇总 可以在不同的工作薄中修改 更新数据 大家可以同时工作 5 5数据链接 2 工作薄间的链接方法用复制 粘贴建立链接方法 见数据链接嵌入 xls N季度 在源工作簿中选择作为链接的单元格区域 选择复制命令 激活目标 需要数据的 工作簿 选择存放数据的第一单元格 从 编辑 菜单中选择 选择性粘贴 对话框中单击 粘贴链接 按钮用输入公式建立方法 见数据链接嵌入 xls 全年汇总 单击目标工作簿中的工作表 选择存放数据的第一单元格 输入公式 指定数据来源的工作簿工作表名称 向下复制 5 5数据链接 通过鼠标点取建立链接方法 见数据链接嵌入 xls 21寸汇总 单击目标工作簿中的工作表 选择存放数据的第一单元格 按下 单击源工作簿工作表的要复制公式的单元格 在目标单元格中将复制的公式中的绝对引用改为相对引用即可例 某茶厂生产的茶叶销售于西南地区 每个季度的销售统计分别在不同的工作簿中 将这两不同工作薄中的工作表链接到一个工作簿中 见数据链接嵌入 xls 碧云山茶叶销售一 二 季度 5 5数据链接 3 将Excel工作表或图表链接到其他应用程序方法 打开数据源工作簿 选择要链接的数据区域 图表 并复制 打开要链接的word文档 其它应用程序 在word中单击 编辑 选择性粘贴 对话框中选择 粘贴链接 列表中选择 EXCEL工作表对象 例 某茶叶厂第一季度的销售数据表及销售对比柱形图如图所示 现要将此图表作为在Word工作报表中的一部份 以使报告更有说服力 见碧云山茶厂年度工作总结 doc 目录 5 6合并计算 1 概述合并计算 是对来源于同一工作簿的不同工作表 或不同工作簿中的工作表进行合并计算 Excel支持不超过255个工作表中的信息收集到一个主工作表中 5 6合并计算 2 同一工作表中数据的合并计算把本表中相同项目中的数据通过使用某种函数进行合并运算到一个新的区域中例 在合并计算 xls中按部门对实发工资进行合并计算 结果存放在本表的J1 K7位置 见合并计算 xls 本表合并计算 操作步骤 首先将部门与实发工资形成一连续区域 选择结果区域第一单元格 单击 数据 合并计算 对话框中确定引用位置 可在数据表中用鼠标选择 后按 添加 选择 首行 与 最左列 复选框 按确定 5 6合并计算 3 不同工作表中的数据合并计算把不同工作表中相同项目中的数据通过使用某种函数进行合并运算到一个新工作表中例 某电视机厂生产几种规格的电视产品 该厂每个季度进行一次销售统计 统计分析保存在一个独立的工作簿表中 见合并计算 xls 不同表合并计算 5 6合并计算 操作步骤 打开数据源表和合并计算工作表 激活存放合并数据的单元格区域 选择 数据 菜单中的 合并计算 命令 在对话框下拉列表中选择函数 在合并计算的引用位置编辑框中输入第一个工作簿工作表的数据区域 按 添加 重复添加第二第三 工作簿的数据区域 按确定注意 若要显示数据源表的明细数据 可在合并计算对话框中选中创建连至源数据的链接 目录 综合实例1 单关键字段排序 实例 员工信息表的单关键字段排序 实例内容 对员工信息表要求按姓名进行升序排列 综合实例1 单关键字段排序 实例操作步骤 步骤1 单击数据表中的任一非空单元格 步骤2 选择 数据 菜单中的 排序 命令 弹出 排序 对话框 在对话框中的设置如左下图所示 排序对话框 单关键字段排序结果 综合实例2 多关键字段排序 实例 员工信息表的多关键字段排序 实例内容 在员工信息表上进行操作 要求先按部门进行笔划排序 部门相同时按性别进行排序 实例操作步骤 步骤1 单击数据表中的任一非空单元格 步骤2 选择 数据 菜单中的 排序 命令 弹出 排序 对话框 在 主要关键字 中输入 部门 在 次要关键字 中输入性别 如右图所示 多关键字段排序 综合实例2 多关键字段排序 步骤3 单击 选项 按钮 弹出 排序选项 对话框 在 方法 区域中选中 笔划排序 单选按钮 如左下图所示 笔画排序方法设置 多关键字段排序结果 综合实例3 自定义排序 实例3 员工信息表的自定义排序 实例内容 对于员工信息表 应能按照超市领导的要求 使表中的部门能按照下面的顺序进行排列 家电部 化妆部 健身器材部 蔬菜水果部 办公室 财务部 人力资源部 信息部 技术部 综合实例3 自定义排序 实例操作步骤 步骤1 选择 工具 菜单中的 选项 命令 步骤2 在弹出 选项 对话框量选择 自定义序列 选项卡 在 输入序列 文本框中输入自定义序列 每输入一个部门后按回车键 如图4 8所示 步骤3 自定义序列输入完成后 按 添加 按钮实现自定义序列的建立 自定义序列的建立 综合实例3 自定义排序 步骤4 单击数据表中的任一非空单元格 选择 数据 菜单中的 排序 命令 弹出 选项 对话框 在 主要关键字 下拉列表中选择 部门 如左图所示 步骤5 单击 选项 按钮 弹出 排序选项 对话框 在 自定义排序次序 的下拉列表框中选择前面所建的自定义序列 单击 确定 按钮完成 排序 对话框设置 自定义排序设置 排序结果 综合实例4 自动筛选 实例4员工工资表的自动筛选 实例内容 利用自动筛选查看员工工资表中 家电部 员工工资情况 员工工资表 综合实例4 自动筛选 实例操作步骤 步骤1 单击员工工资表中的任一非空单元格 步骤2 选择 数据 菜单中的 筛选 命令 在数据表中的每个列标题右边添加下拉列表标志 步骤3 单击 部门 旁的下拉列表箭头 显示出可用的筛选条件 从中选择 家电部 如下图所示 自动筛选结果见下一张幻灯片 自动筛选的 部门 下拉列表内容 综合实例4 自动筛选 自动筛选结果 综合实例5 高级筛选 实例5员工工资表的高级筛选 实例内容 在员工工资表中查找 人力资源部 或加班补贴高于700元的人员 实例操作步骤 步骤1 复制数据表的字段标题 并粘贴在数据表下方 其起始位置为A48 步骤2 在C49单元格中输入 人力资源部 在E50单元格中输入 700 条件区域的建立 A48 J50 综合实例5 高级筛选 高级筛选 对话框 高级筛选结果 综合实例5 高级筛选 现若需要在员工工资表中利用高级筛选来查找 家电部 基本工资高于2000元的员工档案 其涉及的两个条件是 与 的关系 就应放在同一行 建立的条件区域及筛选结果如下图所示 与 关系的筛选条件及筛选结果 综合实例5 高级筛选 现如果要求在员工工资表中再次利用高级筛选以查找 办公室 和 技术部 基本工资高于2500元的人员信息 分析发现 本次的高级筛选的多个条件既有 与 的关系 又有 或 的关系 用条件表达式表示为 部门 办公室 AND 基本工资 2500 OR 部门 技术部 AND 基本工资 2500 与 和 或 条件组合的高级筛选 综合实例6 根据计算条件进行的高级筛选 实例6员工工资表中根据计算条件进行的高级筛选 实例内容 在员工工资表中根据计算条件进行高级筛选得到实发工资大于本超市平均工资的员工信息 实例操作步骤 步骤1 在J75单元格中输入 平均实发工资 在单元格J76中计算出本超市的员工平均工资 公式为 AVERAGE J3 J49 步骤2 计算条件的条件区域建立 在单元格G75中输入计算条件标题 该标题要求不能与数据表中的各列标题相同 本例的标题为 实发工资高于平均值的员工 在单元格G76中输入计算条件 为 J3 J 76 综合实例6 根据计算条件进行的高级筛选 步骤3 单击数据表中的任一非空单元格 选择 数据 菜单下的 筛选 子菜单中的 高级筛选 命令 弹出 高级筛选 对话框 步骤4 在 高级筛选 对话框的 方式 中选择 将筛选结果复制到其他位置 单选框 并在 复制到 右边栏中指定筛选结果存放的起始位置A77 步骤5 在对话框中的 条件区域 输入筛选条件所在的单元格区域G75 G76 点击 确定 按钮完成 综合实例6 根据计算条件进行的高级筛选 根据计算条件进行的高级筛选 综合实例7 数据分类与汇总 实例7数据分类与汇总的建立与删除 实例内容 在商品销售表中利用数据分类与汇总得到超市2007年各部门的销售总额 实例操作步骤 步骤1 对商品销售表按部门进行排序 步骤2 单击数据表中的任一非空单元 步骤3 选择 数据 菜单中的 分类汇总 命令 弹出的 分类汇总 对话框 步骤4 从 分类字段 的下拉列表中选择 部门 汇总方式 中默认的值为 求和 因为是计算销售总额 故不做修改 选定汇总项 列出了数据表中的所有列标题 本次汇总选择 销售额 分类汇总设置如图4 27所示 综合实例7 数据分类与汇总 分类汇总对话框 分类汇总统计结果 综合实例8 多次分类汇总 实例8商品销售表的多次分类汇总 实例内容 要求在商品销售表上进行数据分类汇总得到超市2007年各部门的平均销售额和各部门的销售总额 实例操作步骤 步骤1 单击数据表中的任一非空单元 步骤2 选择 数据 菜单中的 分类汇总 命令 弹出的 分类汇总 对话框 步骤3 从 分类字段 的下拉列表中选择 部门 汇总方式 中选择 平均值 因为本次分类汇总是计算平均销售额 选定汇总项 中选择 销售额 取消选中 替换当前分类汇总 复选框 综合实例8 多次分类汇总 第二次分类汇总对话框设置 多次分类汇总结果 综合实例9 多重分类汇总 实例9商品销售表的多重分类汇总 实例内容 在商品销售表上通过分类汇总得到超市2007年各部门所有商品的全年销售总额和各部门内每种商品的全年销售总额 实例操作步骤 步骤1 单击数据表中的任一非空单元 选择 数据 菜单中的 分类汇总 命令 在弹出的 分类汇总 对话框中单击 全部删除 按钮完成对原有分类汇总结果的删除 步骤2 选择 数据 菜单中的 排序 命令 弹出 排序 对话框 指定 主要关键字 为部门 次要关键字 为 商品名 步骤3 选择 数据 菜单中的 分类汇总 命令 弹出的 分类汇总 对话框 综合实例9 多重分类汇总 步骤4 在对话框中的 分类字段 下拉列表中选择 部门 汇总方式 中默认的值为 求和 选定汇总项 中选择 销售额 如下图所示 第一重分类汇总的对话框设置 综合实例9 多重分类汇总 步骤5 再次进行分类汇总操作 在 分类汇总 对话框进行设置 分类字段 下拉列表中选择 商品名 汇总方式 中选择 平均值 选定汇总项 中选择 销售额 取消选择 替换当前分类汇总 复选框 如下图所示 第二重分类汇总的对话框设置 综合实例9 多重分类汇总 多重分类汇总的结果 综合实例10 数据透视表的创建 实例10商品销售表的数据透视分析 实例内容 现要求利用数据透视表实现对该数据表进行数据分析统计 超市各个季度每个部门的销售总额 实例操作步骤 步骤1 单击数据表中的任一非空单元格 步骤2 选择 数据 菜单中的 数据透视表和数据透视图 命令 在弹出的对话框中指定待分析数据的数据源类型为EXCEL数据列表 指定所需创建的报表类型为 数据透视表 综合实例10 数据透视表的创建 步骤3 单击 下一步 按钮 在弹出的对话框中有 选定区域 一栏 系统已经自动识别并填入数据透视表所基于的数据区域A2 I58 确认数据区域无误 如下图所示 数据透视表向导 步骤之2 综合实例10 数据透视表的创建 步骤4 单击 下一步 按钮 在弹出的对话框中选择数据透视表的显示位