数据分析神器之透视表.ppt

EXCEL微小课 数据分析神器之透视表 教学步骤 简单介绍 01 创建数据透视表 02 数据的组合与汇总 03 案例练习 04 每种产品销售金额的总计是多少 每个区域的销售金额总计是多少 每个区域中每种产品的销售金额分别是多少 为什么使用数据透视表 左边的 产品销售记录单 记录的是2007年某公司产品销售情况的表格 其中包括订购日期 区域 产品类别等 我们希望根据此表快速计算出如下汇总信息 产品销售记录单 部分 诸多的问题 使用数据透视表可以轻松解决 什么是数据透视表 数据透视表是一种可以快速汇总 分析大量数据表格的交互式工具 按照数据表格的不同字段从多个角度进行透视 并建立交叉表格 用以查看不同层面的汇总信息 分析结果以及摘要数据 并且可以深入分析数值数据 帮助用户发现关键数据 01 用途1 快速查看大量数据的表格2 对数值数据快速分类汇总 按分类和子分类查看数据信息3 建立交叉表格 将行移动到列或将列移动到行 以查看源数据的不同汇总4 快速的计算数值数据的汇总信息 差异 个体占总体的百分比信息等 数据透视表对数据源的要求 3 一字段只能包含一个信息 1 表格的第一行是字段名 字段名称不能为空 2 数据记录中最好不要有空白单元格或各并单元格 4 每个字段中数据的数据类型必须一致 如 订购日期字段的值即有日期型数据又有文本型数据 则无法按照 订购日期 字段进行组合 若要创建数据透视表 要求数据源必须是比较规则的数据 也只有比较大量的数据才能体现数据透视表的优势 数据越规则 数据透视表使用起来越方便 以下数据是否满足创建数据要求 此表属于交叉表 不太适合依据此表创建数据透视表 不是不能使用 只是使用上表创建数据透视表某些功能无法体现 因为其月份被分为12个字段 互相比较混乱 创建数据透视表 02 数据透视表的创建 1 将光标点在表格数据源中任意有内容的单元格 或者将整个数据区域选中 2 选择 插入 选项卡 单击 数据透视表 命令 3 在弹出的 创建数据透视表 对话框中 请选择要分析的数据 一项已经自动选中了整个连续数据区域 选择放置数据透视表位置 项中 可以在新的工作表中创建数据透视表 也可以将数据透视表放置在当前的工作表中 4 单击确定 Excel自动创建了一个空的数据透视表 默认情况下 数据透视表字段列表显示两部分 上方的字段部分用于添加和删除字段 下方的布局部分用于重新排列和重新定位字段 右下方为数据透视表的4个区域 用于放置分类字段 数值 区域放置数据汇总字段 当将字段拖动到数据透视表区域中时 左侧会自动生成数据透视表报表 数据透视表的创建 问题1 每种产品销售额的总计是多少 只需要在数据透视表字段列表中选中 产品类别 字段和 销售额 字段即可 这时候 产品类别 字段自动出现在 行标签 区域 而 销售额 字段是 数字 型数据 一般会自动出现在数据透视表的 数值 区域 没有的话就可以拖到 数值 区域 练习问题 每个区域的销售额总计是多少 在Excel2007的数据透视表中 如果勾选的字段是文本类型 字段默认出现在行标签中 如果勾选的字段是数值类型的 字段默认出现在数值区域中 我们也可以将关注的字段直接拖动到相应的区域中 如 希望创建反映各区域每种产品的销售额总计的数据透视表 就可以将区域和产品类别拖动到行标签区域 将销售额拖动到数值区域 问题 反映各区域每种产品的销售额总计 还可以 将不同字段分别拖动到行标签区域和列标签区域 就可以很方便的创建交叉表格 将 所属区域 字段拖动到 报表筛选 区域 将 产品类别 字段拖动到 行标签 区域 将 销售额 字段拖动到 数值 区域 则可以按地区查看每种产品的销售额合计情况 在 报表筛选 区域 可以对报表实现筛选 查看所关注的特定地区的详细信息 直接单击 报表筛选 区域中 所属区域 字段右边的下拉键头 即可对数据透视表实现筛选 报表筛选字段的使用 数据透视表的创建 使用数据透视表展开或折叠分类数据以及查看摘要数据的明细信息 在上面数据透视表的基础之上 可以显示更详细的信息 比如 要查看每种产品不同数量的销售情况 数据透视表的创建 数据的组合与汇总 03 使用数据透视表组合数据1 组合日期数据前面我们使用地区 产品类别等字段作为分类字段查看数据汇总信息 那么如果我们按照订购日期做为分类字段来查看汇总信息会是什么样呢 将 订购日期 字段拖动到 行标签 区域 所生成数据透视表如下图所示 使用数据透视表组合数据1 组合日期数据以上数据透视表显示的是每一天的金额合计 显然不是我们所期望的结果 如果我们希望按照季度 月份等来计算金额的汇总信息又该如何实现呢 可以直接在日期字段上单击鼠标右键 选择 组合 如果要取消日期的组合 在日期字段上单击鼠标右键 选择 取消组合 即可 使用数据透视表组合数据2 数值数据的分段组合除了日期字段可以组合 那么还有什么字段可以组合呢 如想得到单次订单金额在0至50000 50000至100000 100000至150000等的订单的金额合计 占了所有订单金额的百分比 将 销售额 字段拖动到 行标签 区域 此时 行标签 区域和 数值 区域都是 销售额 字段 创建如下数据透视表 使用数据透视表组合数据3 对文本字段的分类组合除了可以对日期字段 数值字段的组合 还可以对文本类字段进行组合分类 如 将产品类别作为行字段 但我们希望将产品进行分类 如服装 暖靴都属于服装大类 我们可以使用组合功能将产品进行分类 按住Ctrl键的同时选择服装 暖靴等 单击鼠标右键选择组合 会出现 数据组1 对 数据组1 重命名为 服装大类 即可 在汇总方式中有一共有11种函数 包括 求和 计数 数值计数 平均值 最大值 最小值 乘积 标准偏差 总体标准偏差 方差 总体方差 数据透视表的数据汇总方式1 改变数据汇总方式 数据透视表的数据汇总方式2 以百分比方式显示数据之前的图表显示的是每种产品在各个地区销售金额合计数 但如果我们将每种产品的合计作为一个总量 希望查看产品分布在不同地区的比例是多少 就可以通过改变值显示方式来实现 数据透视表的数据汇总方式2 以百分比方式显示数据如果我们突然改变了主意 希望将地区作为总和 计算每个产品的销售额占地区合计的百分比是多少 这时候我们只需将 值显示方式 改为 占同列数据总和的百分比 就可以了 数据透视表的数据汇总方式3 在数据透视表中显示多个计算字段 数据透视表的数据汇总方式4 重命名字段 数据透视表的数据汇总方式5 计算字段在使用数据透视报表的时候 如果汇总函数和自定义计算 值显示方式 没有提供所需的结果 则可在计算字段或计算项中创建自己的公式 如需在数据透视表显示利润 即销售额减去成本的值 可以使用计算字段 向数据透视表添加一个新的字段 利润 方法 1 光标点中数据透视表 在数据透视表的 选项 工具面板 公式 组 选择 计算字段 2 在 插入计算字段 对话框 名称 位置输入 利润 在 公式 位置输入公式 销售额 成本 点击添加 即为数据透视表添加了 利润 字段 单击确定后 利润 字段自动添加到数据透视表中 数据透视表的数据源控制与刷新我们可以使用数据透视表功能 在数据源的基础上生成各种不同的报表 以满足不同层次的需求 但当我们使用数据透视表生成报告后 如果数据源变化了 数据透视表报告的信息会不会自动跟着变化呢 当更改数据源后 默认情况下数据透视表并不会自动更新 当数据源的值更改过以后 我们可以通过数据透视表的 选项 工具面板的 数据 组中的 刷新 命令来刷新数据透视表 或者鼠标右击 刷新 这样数据透视表报告就变成最新的了 数据透视表的数据源控制与刷新但是 刷新 命令只是刷新数据透视表所引用的数据源的值 当数据源的范围变化了以后 比如数据源增加了新的纪录 则使用刷新命令就不能将数据范围一起更新了 可以通过数据透视表的 选项 工具面板里的 数据 组中的 更改数据源 命令来重新选择数据透视表所引用的数据 课堂练习 04 数据透视表综