excel透视表笔记part1

第 2 章 创建基本数据透视表 1 创建基本的数据透视表 方法 1 1 单击数据源中的任意一个单元格 好处 在默认情况下 这样可以确保数据 透视表能够捕获数据源的范围 2 插入 表格 数据透视表 方法 2 ALT N V T 1 1 添加字段到报表中 数据透视表字段列表中的四个区域分别是 1 报表筛选 添加字段到报表筛选区可以使该字段包含在数据透视表的筛 选区域中 以便对其独特的数据项进行筛选 2 列标签 添加一个字段到列标签区域可以在数据透视表顶部显示来自该 字段的独特的值 3 行标签 添加一个字段到行标签区域可以沿数据透视表左边的整个区域 显示来自该字段的独特的值 4 数值 添加一个字段 数值 区域 可以使该字段包含在数据透视表 的值区域中 并使用该字段中的值进行指定的计算 在开始将各个字段拖放到各个区域中之前 首先询问自己两个问题 对什么进行度 量 如何查看它 第一个问题的答案告诉我们需要对数据源中的哪些字段进行处理 第二个问题的答案告诉我们将这些字段放在什么位置 Excel 如何知道知道字段应该去什么地放 正如我们刚刚经历的 使用新的 数据透视表字段表 界面 简单地选中某一字段 名即可将字段添加到数据透视表中 Excel 自动添加选中的字段到数据透视珍中 问题是 Excel 如何知道将选中的字段放在哪个区域 答案是 Excel 并不是真正地知 道使用哪个区域 它根据数据类型 如果数据类型是数字 Excel 就把该字段放到 数值 区域 否则 Excel 把这个字段放到行标签区域中 这样放置明显可以 强调各字段指定正确的数据类型的重要性 注意字段中的值 如果是在值字段中有一个空单元格 Excel 将把该字段读作文 本字段 1 2 增加数据透视表的层次 1 3 重新排列数据透视表 1 4 创建报表筛选视 通常 可能会要求我们生成某一具体地区 不是为每种可能的分析案分别单独的数 据透视表 而是可以使用筛选字段建立报表筛选哭 1 5 与数据源保持一致 随着时间的推移 数据也可能发生变化 并且增加新的行和列 然而 数据透视表 报表所依赖的数据透视表缓存与数据源没有连接 因而不能反应对数据源所进行的 修改 直到再一次更新数据源 通过再次更新数据源更新透视表缓存的行为称为刷新数据 有两个原因让我们必须 更新数据透视表报表 1 5 1 对现有数据已经进行修改 更新方法 简单地在数据透视表报表中单右击 并选择 刷新 1 5 2 数据源的范围已经随着行或列的增加而扩大 更新方法 在数据透视表内任何地方单击鼠标右键 然后 选择 数据透视表工具 选项卡上的 选项 由此选择 更新数据源 这一选择会引发如下图所示对话框 2 6 共享数据透视表绶存 共享数据透视表的副作用 很多时候 我们可能必须以多种不同的方法分析相同 数据集 这就要求我们使用 相同的数据源创建单独的数据透视表 但是 当我们每次创建数据透视表时 都要 在数据透视表缓存中存储整个数据副本 所创建的每个数据透视表缓存都会增加内 存用量和文件大小 由于文件大小增加了 所以我们应该考虑共享数据透视表缓存 只有这样才能提高内存和文件空间的利用率 通过复制和粘贴的方法很容易解决这些潜在的问题 确实如此 通过简单的复制数 据透视表并粘贴到任何地主 可以创建另一个数据透视表 而不用复制数据透视表 缓存 这样可以链接许多相同数据透视表缓存 而忽略内存和文件大小增加 共享数据透视表的副作用 假设有两个数据透视表使用相同的数据透视表缓存某些操作可能会影响两个数据透 视表包括如下几种 1 刷新数据 不能只刷新一个数据透视表而不刷新另一个 刷新影响两 个数据透视表 2 增加计算字段 如果在一个数据透视表中创建一个计算字段 新创建的 计算字段也会出现在另一个数据透视表 的字段列表中 3 增加计算项 如果三个数据透视表 创建一个计算项 该项也会在另一个 数据透视表中出现 4 组合字段或者取消字段组合 所进行的所有组合或者取消 虽然这些副作用从共享数据透视表缓存的概念上看都不是致命的缺点 但在决定是 否使用数据透视表作为数据源是目前情况的最佳选项时 记住它们很重要 2 7 利用新的数据透视表工具节约时间 2 7 1 推迟布局更新 根据大型数据源建立数据透视表归困难的部分是每次添加字段到数据透视表区域中 Excel 处理数据时 我们都必须一直等待 在 Excel2007 缓解了这样的问题 可以将 布局变化一直推迟到准备好才应用 我们可以激活这个选项 单击 数据透视表 对话框中相对不太惹人注意的 推迟布局更新 复选框 可以激活这一选项 如下 图 请注意 我们在结束数据透视表时 记住要取消选中 推迟布局 复选框 在数据透 视表中一直选中它 可能导致数据透视表一直停留在人工更新状态 阴止数据透视 表的其他特性 即 排序 筛选 分级等 2 7 2 利用利用一次单击从头开始 通常 在处理数据透视表布局时 可能从头开始 那么在不删除数据透视表缓存的 情况下 本质上从头再来 选择 数据透视表工具 选项卡 上的 选项 并选择 清除 下拉列表 2 7 3 重新定位数据视表 把数据透视表移到其他地放的方法 选择 数据透视表工具 选项卡 上的 选项 并选择 移动数据透视表 按钮 这个图标将激活 移动数据透视表 对话框 如 下图 3 1 3 用 0 代替空值 良好的电子表格设计要素之一是在工作表的数值部分永远不要遗留空单元格 对于 数据透视表中的空格一般用 0 来填充 方法 1 在数据透视表内选择一个单元格 2 在 选项 选项卡上 选择 数据透视表选项 组中 选项 图标 显示 数据透视表选项 对话框 3 在 布局和格式 选项卡上 在 格式 部分 在标记为 对于空单元格 显示 字段后输入 0 4 单击 确定 接受所做的修改 3 1 4 修改字段名称 最终的数据秀透视表中的每个字段都有一个名称 列和筛选区域中的字段从源数据 的标题继承其名称 数据部分中的字段会被赋予 求和项 这样的名称 我 们可能会用 总计 来代替默认名称 方法 右击数据透视表的数值区中的某个数 值并选择 值字段设置 在 自定义名称 输入一个新名称 如果输入的是数据 透视表 中已有的名称 那么命名会失败 我们可以在命名字段的结尾处增加一个 空格 Excel 认为没有空格的和有空格的不同 因为这种变化是修饰 所以你的电 子表格的读者不会请注意到该名称后面的空格 3 2 改变布局 Excel2007 提供了 3 种布局样式 新样式 压缩布局 可能是用户的数据透视表的 默认样式 在 设计 选项卡的 布局 组中可以控制布局改变 该组有 4 个菜单 分类汇总 将分类汇总移到组的顶部或底部 或关闭分类汇总 总计 打开或关闭行和列的总计 报表布局 使用压缩 大纲或者表格形式 空行 在每上组后面插入空行 或者删除每一组后的空行 3 2 1 使用新的压缩形式布局 默认情况下 所有新的数据透视表 都使用压缩布局 在这种布局中 行区域中的 多个字段堆叠在列 A 中 压缩形式适合使用 展开 和 折叠 图标 3 2 2 使用大纲形式的布局 以大纲形式显示时 Excel 用最外层的行字段填充列 A 其他的字段占据 B C 等 如果计划将数据透视表的数值复制到新的位置 这种布局比较合适 虽然压缩的形 式的布局能够提供将多个字段挤压到一列中的巧妙方法 但对于以全重新使用数据 并不理想 默认情况下 压缩和大纲布局会将汇总显示在每组的顶部 可以使用 设计 选项卡上的 分类汇总 下拉选项将分类汇总移到每底部 3 2 3 使用传统的表格形式布局 使用 分类汇总从不会出现在组的项部 如果希望在后继分析中使用得到的汇总数 据 表格形式的布局也许是最好的布局 3 3 案例 3 4 利用样式和主题自定义数据透视表的外观 3 4 1 自定义样式 方法 1 在 数据透视表样式 样式库中 找到现有的一种样式 右击选择 复制 Excel 将显示 修改数据透视表快速样式 对话框 2 为该样式选择一个新名称 3 在 元素 列表中 单击 么一行条纹 一个新的 称为 条纹尺寸 的部分 会出现在对话框中 4 重复第二步 完成后 单击确定 3 4 2 修改未来数据透视表的默认样式 我们可以控制计算机未来的数据透视表使用哪种样式作为默认样式 默认的样 式可以是内置的样式之一或是修改的表的自定义样式 在 设计 选项卡的 数据透视表样式 样式库中 右键单击该样式并选择 设为默认样式 3 4 3 用文档主题修改样式 打开 页面布局 选项卡中的 主题 下拉列表 当我们在主题下拉列表上来 回移动鼠标光标时 即时预览 将向我们展示数据表的颜色和字体 要选择某 一主题 单击它 某些主题有临时字体 使用 页面布局 选项卡上 主题 组中的 颜色 下 拉菜单可以对新主题颜色而不改变其字体 3 5 修改汇总计算 3 5 1 了解空单元格会导致计数的原因 如果某列中的所有单元格都含有数值数据 会选择求和 但如果只有 1 个单元 格为空格单元格或者含有文本 Excel 就会选择计数 在 Excel2007 中 在单击 数据透视表字段列表 的 字段 部分数值字段 含 有空格或文本 中时 会出现的情况是 如果 Excel 将数值字段 含有空格或文 本 移动到 行标签 区域 就要知道 Excel 将数值字段 含有空格或文本 看成文本而不是数值 在拖钢产量字段到 数值 区域时要特别警惕 如果某 一计算值看起来地小时 就要检查一直该字段的名称是不是用 计数 代替 求和 解决的方法是 双击 计算 激活 值字段设置 对话框 然后将 值字段汇总方式 由 计数 改为 求和 就 OK 了 3 5 2 使用除计数和求和之外的其他函数 在 Excel2007 中 在单击 数据透视表字段列表 的 汇总方式 部分提供了 9 种函数 1 求和 2 计数 对所有单元格进行计数 包括数值 文本和错误的单元格 等价 于 counta 函数 3 最大值 4 最小值 5 平均值 6 剩积 7 数值计数 只计算数值单元格的个数等价于 count 函数 8 标准偏差和总体标准偏差 计算标准偏差 9 方差和总体标准方差 计算统计方差 10 3 6 添加和删除分类汇总 11 报表汇总是数据透视表的基本特性 有时 我们可能要禁止分类汇总显 示 而有时又需要显示各字段的多种分类汇总结果 12 3 6 1 有许多行字段时 禁止分类汇总 13 方法 为了删除某一字段的分类汇总 可以单击 数据透视表字段列表 的区域节中的那个字段 选择 字段设置 对话框中选择 分类汇总 下面的 无 如下图 14 15 3 6 2 为一个字段添加多种分类汇总 方法 为一个字段添加多种分类汇总 可以单击 数据透视表字段列表 的区 域节中的那个字段 选择 字段设置 对话框中选择 分类汇总 下面的 无 如下图 3 7 使用累积总计选项 如果要创建累积总计或者将一项与另一项对比 就需要使用 值字段设置 对 话框的第 2 个选项卡 方法是 右击数据透视表的数值区中的某个数值并选择 值字段设置 单击 值显示方式 下拉列表时 除 普通 选项卡外 还有 8 个选项可供选 项 占同行数据总和的百分比 将数据透视表一行的总计的百分比显示为 100 占列行数据总和的百分比 将数据透视表一列的总计的百分比显示为 100 点总和的百分比 所有明细数据的总计的百分比显示为 100 差异 显示一项与另一项对比或者前一项对比 二者之间的差异 百分比 将某一项的数值表示为另一项的数值的百分比 差异百分比 表示某一项相对于另一项的变化的百分比 按某一字段汇总 计算连续项的重要性 指数 计算各项的相对重要性 3 7 1 利用差异选项显示年与年之间的不同 各公司总是想知道与上月相比 本月的业绩如何 或者 如果他们的业务是季 节性的 可能想知道本月与去年相同的月份相比业绩如何 为了创建这样的报表 要双击 求和项 并单击 值显示方式 在 值显示 方式 下拉列表中 选择 差异 因为要将一年与另一年的情况进行对比 所以要在 基本字段 中选择 Years 在 基本项 字段中有几个可实施的 选项 如果总是要将当年和上一年的情况进行对比 可以选择 上一个 选项 如下图 如果有若干年的有价值的数据 并且都要和某一基准年 如 2007 年 进行对比 则可以选择 2007 下图同时给出了 值字段设置 对话框的设置和根据该设置所得到的报表 该 报表显示 2008 年 1 月的收入比 2007 年 1 月多 5 万 5 千美元 3 7 2 利用差异百分比选项将当年与上一年进行对比 差异百分比 选项与 差异 选项类似 按 某一字段汇总 跟踪 YTD 总和 如果需要按月比较年初至今总收入 可以使用 按某一字段汇总 选项来完成 3 7 4 确定每一行业务对总计的贡献大小 公司老板一般对公司各部门对公司总收入的贡献的百分比感兴趣 我们可能利 用 占用行数据总和的百分比 3 7 5 创建季节性报表 季节性报表特别适合查看业务的季节性 用 占同列数据的百分比 3 7 8 利用指数选项跟踪相对重要性 最后一个选项 指数 可以进行相当模糊的计算 Microsoft 声称这种计算 可以描述一列某个单元格的相对重要性 3 8 案例 根据业务业务报表的行计算收入 要求人们创建一份报表 能够提供全面查看产品系列的收入 这种分析需要包 括各个市场的产品系列的收入 各产品系列在市场在所占的百分比 以及各个 市场所表示的产品系列占公司总收入的百分比 第四章 查看视透数据的方法 本章主要讨论分级 排序 筛选数据可视化以及数据透视表选项 4 1 1 组合日期字段 方法 将光标定位到数据透视表中任意一个单元格 在 选项 选项卡上 单 击 分级 组中的 将字段分组 字段中含有日期信息 会出现 分组 对话框 目前这种情况下 要选择 月 季度 和 年 如下图 注意 1 季度 和 年 已经添加到数据透视表字段列表中 但我们不要被这 种现象所欺骗 我们的数据源并没有发生变化而包括这些新的字段 这些字段 现在仅仅在内存中数据透视表缓存的一部分 2 默认情况下 年 和 季度 会自动添加到与数据透视表布局中的原日期字段相同的区域 如下图所示 4 1 2 按月组合时包含年 如果我们按月组合字段 总是应该在 分组 对话框中包含 年 如果不包 含 年 就会将原始数据中每年的相同月算在一起 这不是我们要的结果 4 1 3 按周组合日期字段 在 分组 对话框中没有步长 周 如果要按组合 方法是 在数据透视表中任选一个标题 然后从 选项 选项卡选择 将字段分组 在 分组 对和话框中 取消选中所有 步长 选项 而只选选择 日 字段 这样可以使 天数 旋转按钮可用 为了生成周报表 将天数的值由 1 增加到 7 最后必须设置 起始于 日期 通过在开始之前先查看日历知道第 1 组要 从 2007 年 1 月 1 日开始 4 1 3 在一个报表中组合两个日期字段 如果需要生成有两个日期字段的报表 并试图对两个日期字段按月和年分组 Excel 将随机地把第 1 个组合字段命名为 年 第 2 个组合字段命名为 年 2 这样的命名约定不可避免地会带来混淆 在这种情况下重要的是用有意义的名 称重新命名这些字段 4 2 组合值字段 通过值字段的 组合 对话框可以将数据透视表中的项组合成相同的范围 在下图中 行标签 字段含有年龄数字 目前 数据显示的是 20 75 之间的 年龄 在 A 列中选择任一年龄并从 选项 对话框中选项 将字段分组 Excel 将显示 组合 对话框 在 组合 对话框中 选择组合参数 如图 此建议从 20 到 75 按照 10 年的增量进行组合 4 4 查看数据透视字段列表 4 4 1 停靠和浮动数据透视表字段列表 通过拖动的方法可以实现数据透视表字段列表的移动 4 4 2 重新排列数据透视表字段列表 在靠近 数据透视表字段列表 的右上角有一个小的下拉菜单 选择此下拉查 看数据透视表字段列表的 5 种可能的排列方式 4 4 3 使用区域节下拉菜单 4 4 4 使用字段下拉菜单 4 5 数据透视表中的数据进行排序 4 5 1 使用 选项 选项卡上的排序图标排序 在 选项 选项卡的 排序 组合出现 3 个图标 4 5 2 使用字段列表隐藏的下拉菜单排序 4 5 3 使用自定义序列排序 方法 Office 图标菜单 Excel 选项 常用 分类选项卡上 单击 编辑自定义列表 选择数据透视表中任一个要排序字段的单元格之一 选项 选项卡上 单击 排序 在 排序对话框 中的 其他选项 取消选中的 自动排 序 在 其他排序选项 对话框中 打开 主关键字段 然后选择刚刚自定 义的顺序 4 6 对数据透视表中的数据进行筛选 4 6 1 添加字段到报表筛选区域 4 6 2 从报表筛选区中选择一项 4 6 3 从报表筛选区中选择多项 4 6 4 快速选择或清除所有筛选选项 方法 选择下图中的全部 就 OK 了 4 6 5 使用字段列表筛选 这一功能是 Excel 新增的强大功能 4 6 6 使用标签筛选 标签筛选 弹出式菜单中可用的选项包括以下 4 6 7 使用日期筛选 第 5 章 在数据透视表内进行计算 5 1 计算字段与计算项简介 用数据透视表分析数据时 会经常发现需要将分析扩展到要包括基于不在原数 据集内的计算结果的数据 Excel 提供了在数据透视表内通过计算字段和计算 项计算的方法 计算字段是通过对数据透视表内的现有字段进行计算创建的一个字段 可以将 字段看作是添加一个虚拟列到数据集中 该列不占用源数据中的空间 存放的 是用公式定义的数据 并而作为一个字段与数据透视表相互作用 就像 数据透视表中的其他字段一样 计算项是通过对数据字段内现有的数据项进行计算所创建的一个数据项 可以 将计算字项看作是添加一个虚拟行到数据集中 该虚拟行不占用源数据中的空 间 存放的是对同一字段中其他行进行计算的汇总值 计算项可以作为一个数 据项与数据透视表相互作用 就像数据透视表中其他数据项一样 有了计算字段与计算项 就可以向数据透视表中插入公式 创建自己的自定义 字段或者数据项 5 1 1 方法 1 手工添加计算字段到数据源中 可以手工添加计算字段到数据源中 让数据透视表作为普通的数据字段获取该 字段 表面上看 这一选项看起来简单 但这种预计算度量并将其与数据源合 并的方法在若干层次上不切实际 如果计算字段的定义发生变化 就不得不返 回数据源 重新计算每一个度量刷新数据透视表 如果必须增加一种度量 也 得不得不返回没数据源 添加新的计算字段 然后修改数据视表 的范围以捕 捉新字段 5 1 2 在数据透视表外使用公式创建计算字段 还可以通过使用公式中外部单元格内进行计算可以添加计算字段 虽然这种方 法可以给出随数据透视表刷新而更新的计算字段 但是数据透视表的任何结构 变化都可能使该公式无效 5 1 3 直接在数据透视表内插入计算字段 直接在数据透视表内插入计算字段是最佳选项 可以消除要管理公式的需要 提供数据源增加或变化时的可量测性 度量定义变化时的灵活性 这种方法的 另一个优势是可以改变数据透视的结构 甚至依据计算字段度量不同的数据字 段 而不用担心公式出错或者丢失单元格引用 关键问题是集成自定义计算到数据透视表有许多好处 1 可能的公式和单元格引用错误的消除 2 添加或者删除数据透视表中的数据而不影响计算的能力 3 数据透视表改变或者刷新时 自动重新计算的能力 4 度量定义变化时 很容易个性计算的能力 5 有效地管理和维护计算的能力 5 2 创建计算第 1 个计算字段 方法 将光标定位在数据透视表中任一单元格中 单击 选项 工具 公式 计算字段 如下图 选择 计算字段 之后 Excel 会激活插入对话框 如下图 注意此对话框顶部的两个输入框 名称 和 公式 此时的目的是赋予计算 字段一个名称 然后通过选择数据字段和数学运算符的组合建立公式 提供需 要的度量 根据实际定公式 这意味着刚刚在数据源中添加了一列了吗 没有 计算字段类似于数据透视表默认的分类汇总和总计计算 因为它们都是当数据 透视表变化或者更新时可以重新计算的数学函数 计算字段只是模仿数据源中 实际的字段 这样就可以施动它们 修改字段设置 以及将它们和其他的计算 字段一起使用 5 4 创建第 1 个计算项 方法 将光标定位在数据透视表中任一单元格中 单击 选项 工具 公式 计算字项 如下图 选择 计算项 之后 Excel 会激活插入对话框 如下图 5 5 了解数据透视表计算的规则和缺陷 5 5 1 记住运算的优先级次序 Excel 的运算次序如下 求括号内各项的值 求区域 的值 求交叉 空格 的值 求联合 的值 求负 转换百分比 计算乘方 计算乘 除 乘和除的优先级相同 计算加 减 加和减的优先级相同 对文本运算求值 进行比较 注意 优先相同运算将从左向右计算 5 5 2 使用单元格引用和命名区域 在数据透视表内创建计算时同 实际上是在 真空 中工作 惟一可用的数据 是数据透视表缓存中存在的数据 因此 在公式中 不能越过数据透视表缓存 的边界引用单元格或者命名区域 5 5 3 使用工作表函数 可以使用不需要单元格引用或者定义名称作为变量的任何工用表函数 例如 Count Average If not Or 等 5 5 4 使用常量 在数据透视表计算中可以使用任意常量 5 5 5 引用总计 计算公式不能引用数据透视表的分类汇总或者总计 这意味着不能使用分类汇 总或者总计的结果作为计算字段中的变量 5 6 管理和维护数据透视表计算 5 6 1 编辑和删除数据透视表计算 5 6 2 改变计算项的求解次序 如果数据透视表中某个单元格的值取决于两个或者两个计算项的结果 就会改 变求解计算项次序的选项 也就是说 可以指定单个计算的次序 方法 将光标定位在数据透视表中任一单元格中 单击 选项 工具 公式 求解次序 如下图 选择 求解次序 之后 Excel 会激活插入对话框 如下图 5 6 3 提供公式的说明文档 方法 将光标定位在数据透视表中任一单元格中 单击 选项 工具 公式 公式 如下图 选择 公式 之后 Excel 会激活插入对话框 如下图 第 6 章 使用数据透视表视图和其他可视化工具 6 1 什么是真正的数据透视图 和其他人分享自己的分析结果时 可能很快就会发现效果不好的原因是人们需 要图 数据透视表很好 但是它留下了许多需要时间才能消化的并且让人讨厌 的数字 另一方面 利用图可以快速确定自己的数据要揭示什么 图可以直接 让用户满意 让用户可以立即看出其相互关系 指出不同之处并观察趋势 最 关键的是 现在经理想要尽可能快地吸收消化数据 而其他传递信息的手段都 没有图快 这正是数据透视图发挥作用的地方 数据透视表提供分析 而数据 透视图可以提供视觉效果 6 2 创建一个透视图 方法 将光标放在数据透视表内的任何地放 并且应用程序的功能区上单击 插入 选项卡 插入 选项卡上 可以看到 图表 组 它展示了可以创 建的各种不同类型的图表 可能选择自己喜欢会用的数据透视图表类型 正如下图 所看到的 选择图表类型会在电子数据表上生成两个对象 一个图 形和一个 数据透视图筛选窗格 6 3 牢记数据透视规则 6 3 1 修改基础