《【精编】数据透视表应用技巧讲义》由会员分享，可在线阅读，更多相关《【精编】数据透视表应用技巧讲义（47页珍藏版）》请在金锄头文库上搜索。
1、数据透视表应用技巧 IT部内训之四 1 数据透视表简介 3 数据透视表字段布局 6 数据透视表优化 目录 2 创建数据透视表 4 数据透视表组合数据 5 数据汇总方式 为什么使用数据透视表 什么是数据透视表 第一章数据透视表简介 数据透视表的用途 对数据源的要求 以下是否满足创建数据要求 第一节为什么使用数据透视表 第一章 数据透视表简介 第一节为什么使用数据透视表 上表是我公司订单销售情况的表格 其中包括订单月份 客户编号 部门 中文品名 出货数量 外销单价 外销总价 厂商编号 我们希望根据此表快速计算出如下汇总信息 1 每种产品销售金额的总计是多少 2 每个月份的销售金额总计是多少 3 每
2、个部门的销售金额总计是多少 4 每个客户的销售金额总计是多少 5 每个部门中每种产品的销售金额合计是多少 诸多的问题 使用数据透视表可以轻松解决 第一章 数据透视表简介 第二节什么是数据透视表 1 数据透视表是一种可以快速汇总 分析大量数据表格的交互式工具 2 使用数据透视表可以按照数据表格的不同字段从多个角度进行透视 并建立交叉表格 用以查看数据表格不同层面的汇总信息 分析结果以及摘要数据 3 使用数据透视表可以深入分析数值数据 以帮助用户发现关键数据 并做出有关企业中关键数据的决策 第一章 数据透视表简介 第三节数据透视表的用途 1 以友好的方式 查看大量的数据表格 2 对数值数据快速分类
3、汇总 按分类和子分类查看数据信息 3 展开或折叠所关注的数据 快速查看摘要数据的明细信息 4 建立交叉表格 将行移动到列或将列移动到行 以查看源数据的不同汇总 5 快速计算数值数据的汇总信息 差异 个体占总体的百分比信息等 第一章 数据透视表简介 第四节对数据源的要求 若要创建数据透视表 要求数据源必须是比较规则的数据 也只有比较大量的数据才能体现数据透视表的优势1 表格的第一行是字段名称 字段名称不能为空 2 数据记录中最好不要有空白单元格或合并单元格 3 一字段只能包含一个信息 4 每个字段中数据的数据类型必须一致 如 订单日期 字段的值即有日期型数据又有文本型数据 则无法按照 订单日期
4、字段进行组合 5 数据越规则 数据透视表使用起来越方便 第一章 数据透视表简介 第五节以下是否满足创建数据要求 第一章 数据透视表简介 第五节以下是否满足创建数据要求 第一章 数据透视表简介 第五节以下是否满足创建数据要求 第一章 数据透视表简介 新建空的数据透视表 添加字段 第二章创建数据透视表 第一节新建空的数据透视表 尽管数据透视表的功能非常强大 但是创建的过程却是非常简单 1 将光标点在表格数据源中任意有内容的单元格 或者将整个数据区域选中 2 选择 插入 选项卡 单击 数据透视表 命令 第二章 创建数据透视表 第一节新建空的数据透视表 3 在弹出的 创建数据透视表 对话框中 请选择要
5、分析的数据 一项已经自动选中了光标所处位置的整个连续数据区域 也可以在此对话框中重新选择想要分析的数据区域 还可以使用外部数据源 请参阅后面内容 选择放置数据透视表位置 项 可以在新的工作表中创建数据透视表 也可以将数据透视表放置在当前的某个工作表中 第二章 创建数据透视表 第一节新建空的数据透视表 4 单击确定 Excel自动创建了一个空的数据透视表 5 图中左边为数据透视表的报表生成区域 会随着选择的字段不同而自动更新 右侧为数据透视表字段列表 第二章 创建数据透视表 第二节添加字段 创建数据透视表后 可以使用数据透视表字段列表来添加字段 如果要更改数据透视表 可以使用该字段列表来重新排列
6、和删除字段 默认情况下 数据透视表字段列表显示两部分 上方的字段部分用于添加和删除字段 下方的布局部分用于重新排列和重新定位字段 右下方为数据透视表的4个区域 其中 报表筛选 列标签 行标签 区域用于放置分类字段 数值 区域放置数据汇总字段 当将字段拖动到数据透视表区域中时 左侧会自动生成数据透视表报表 第二章 创建数据透视表 报表筛选 列标签 行标签 数值 灵活布局字段 筛选器的使用 第三章数据透视表字段布局 查看数值的详细构成 第一节灵活布局字段 我们来解决前面提到的第一个问题 每种产品销售金额的总计是多少 只需要在数据透视表字段列表中选中 中文品名 字段和 外销总价 字段即可 这时候 中
7、文品名 字段自动出现在 行标签 区域 由于 外销总价 字段是 数字 型数据 自动出现在数据透视表的 数值 区域 第三章 数据透视表字段布局 第一节灵活布局字段 同理 计算每个部门的销售金额总计是多少 只需要在数据透视表字段列表中选中 部门 字段和 外销总价 字段即可 其他依此类推 第三章 数据透视表字段布局 第一节灵活布局字段 在Excel的数据透视表中 如果勾选的字段是文本类型 字段默认自动出现在行标签中 如果勾选的字段是数值类型的 字段默认自动出现在数值区域中 我们也可以将关注的字段直接拖动到相应的区域中 第三章 数据透视表字段布局 第一节灵活布局字段 如果将不同字段分别拖动到行标签区域和
8、列标签区域 就可以很方便的创建交叉表格 第三章 数据透视表字段布局 第二节筛选器的使用 将 部门 字段拖动到 筛选器 区域 将 月份 字段拖动到 列标签 区域 将 中文品名 字段拖动到 行标签 区域 将 外销总价 字段拖动到 数值 区域 则可以按部门查看每种产品在各个月份的金额销售合计情况 第三章 数据透视表字段布局 第二节筛选器的使用 在 筛选器 区域 可以对报表实现筛选 查看所关注的特定地区的详细信息 直接单击 筛选器 区域中 部门 字段右边的下拉键头 即可对数据透视表实现筛选 把 客户编号 字段拖动到 筛选器 区域可以分析客户的销售情况 第三章 数据透视表字段布局 第三节查看数值的详细构
9、成 如果希望查看此汇总数值的详细构成 只需要双击数值所在的数据单元格 Excel会自动在一个新的工作表显示够成此合计信息的每一笔详细记录 第三章 数据透视表字段布局 组合日期数据 组合数值数据 第四章数据透视表组合数据 组合文本字段 第一节组合日期数据 如果我们按照日期做为分类字段来查看汇总信息会是什么样呢 将 部门 字段拖动到 行标签 区域 将 日期 字段拖动到 列标签 区域 所生成数据透视表如下图所示 第四章 数据透视表组合数据 第一节组合日期数据 以上数据透视表显示的是每一天的金额合计 显然不是我们所期望的结果 如果我们希望按照年 季度 月份等来计算金额的汇总信息又该如何实现呢 可以直接
10、在日期字段上单击鼠标右键 选择 创建组 第四章 数据透视表组合数据 第二节组合数值数据 如想得到单次订单金额在0至3000 3000至6000 6000至9000等等的订单的金额合计 占了所有订单金额的百分比 将 外销总价 字段拖动到 行标签 区域 此时 行标签 区域和 数值 区域都是 外销总价 字段 创建如下数据透视表 第四章 数据透视表组合数据 第三节组合文本字段 除了可以对日期字段 数值字段的组合 还可以对文本类字段进行组合分类 如 将中文品名作为行字段 但我们希望将产品进行分类 如插头 插座等属于一类 灯座 节能灯等属于另一类 我们可以使用组合功能将产品进行分类 按住Ctrl键的同时选
11、择插头 插座等 单击鼠标右键选择组合 第四章 数据透视表组合数据 改变数据汇总方式 以百分比方式显示数据 第五章数据汇总方式 显示多个计算字段 重命名字段 不同汇总方式的数据比较 编辑数据透视表公式 第一节改变数据汇总方式 在汇总方式中有一共有11种函数 包括 求和 计数 数值计数 平均值 最大值 最小值 乘积 标准偏差 总体标准偏差 方差 总体方差 第五章 数据汇总方式 第二节以百分比方式显示数据 如图是每种产品在各个季度销售金额合计数 但如果我们希望查看产品分布在不同季度的比例是多少 就可以通过改变值显示方式来实现 第五章 数据汇总方式 第二节以百分比方式显示数据 如果我们希望将地区作为总
12、和 计算每个产品的金额占地区合计的百分比是多少 这时候我们只需将 值显示方式 改为 列汇总的百分比 就可以了 第五章 数据汇总方式 第三节显示多个计算字段 第五章 数据汇总方式 第四节重命名字段 第五章 数据汇总方式 第五节不同汇总方式的数据比较 如果我们希望同时查看各部门在每个月的发生额以及以往月份的累加额合计 该如何得到呢 第五章 数据汇总方式 第六节编辑数据透视表公式 在使用数据透视报表的时候 如果汇总函数和自定义计算 值显示方式 没有提供所需的结果 则可在计算字段或计算项中创建自己的公式 如需在数据透视表显示每个月销售金额的合计信息 但又需要按照销售金额的6 计算税金 可以使用计算字段
13、 向数据透视表添加一个新的字段 税金 方法 1 光标点中数据透视表 在数据透视表的 分析 工具面板 字段 项目和集 组 选择 计算字段 2 在 插入计算字段 对话框 名称 位置输入 税金 在 公式 位置输入公式 外销总价 6 点击添加 即为数据透视表添加了 税金 字段 单击确定后 税金 字段自动添加到数据透视表中 3 若要删除添加的计算字段或计算项 在计算字段或计算字段对话框的名称中 选择要删除的字段 单击对话框右侧的删除按钮即可 第五章 数据汇总方式 第六节编辑数据透视表公式 第五章 数据汇总方式 数据透视表的布局和格式 数据源的控制与刷新 第六章数据透视表优化 删除数据透视表 第一节数据透
14、视表的布局和格式 在我们创建数据透视表之后 可能会出现如下图两种不同的格式 究竟是为什么呢 其实如果是基于以前版本的Excel文件创建的数据透视表 则会显示为如左边的格式 如果是基于Excel2007格式的文件创建的数据透视表 则会显示为如右边的格式 第六章 数据透视表优化 第一节数据透视表的布局和格式 如果希望更改数据透视表的布局 可以选择 分析 工具面板的 选项 组 点击 选项 命令 再点击 显示 栏 勾选 经典数据透视表布局 即可 第六章 数据透视表优化 第一节数据透视表的布局和格式 我们还可以通过选择 设计 工具面板的中的 分类汇总 命令或 总计 命令来改变汇总显示方式 第六章 数据透
15、视表优化 第一节数据透视表的布局和格式 如果对数据透视表默认的格式不喜欢 我们还可以通过选择 设计 工具面板的 数据透视表样式 组中选择数据透视表的格式 从而很容易设计出漂亮的数据透视表 第六章 数据透视表优化 第二节数据源的控制与刷新 当我们使用数据透视表生成报告后 如果数据源变化了 数据透视表报告的信息会不会自动跟着变化呢 当更改数据源后 默认情况下数据透视表并不会自动更新 当数据源的值更改过以后 我们可以通过数据透视表的 分析 工具面板里的 刷新 命令来刷新数据透视表 第六章 数据透视表优化 第二节数据源的控制与刷新 但是 刷新 命令只是刷新数据透视表所引用的数据源的值 当数据源的范围变化了以后 比如数据源增加了新的纪录 则使用刷新命令就不能将数据范围一起更新了 可以通过数据透视表的 分析 工具面板里的 更改数据源 命令来重新选择数据透视表所引用的数据 第六章 数据透视表优化 第三节删除数据透视表 在 分析 选项卡上的 操作 组中 单击 选择 然后单击 整个数据透视表 按Delete 第六章 数据透视表优化
