数据透视表

566 第 28章 使用数据透视 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 分析数据 本章将向读者介绍如何创建数据透视表、设置数据透视表的格式、数据透视表的排序及组合、 数据透视表内的复杂计算、创建动态数据源的数据透视表与复合范围的数据透视表，以及数据透 视表的进阶使用技巧。通过学习可以让读者掌握创建数据透视表的基本方法和运用技巧。 本章学习要点： 要点 1： 创建数据透视表。 要点 2： 数据透视表的项目组合。 要点 3： 在数据透视表中插入计算字段及计算项。 要点 4： 创建复合范围的数据透视表。 28.1　关于数据透视表 数据透视表是用来从 Excel 数据列表、关系数据库文件或 OLAP 多维数据集中的特殊字段中总 结信息的分析工具。它是一种交互式报表，可以快速分类汇总和比较大量的数据，并可以随时选 择其中页、行和列中的不同元素，以快速查看源数据的不同统计结果，同时还可以随意显示和打 印出你所感兴趣区域的明细数据。 数据透视表有机地综合了数据排序、筛选、分类汇总等数据分析的优点，可方便地调整分类 汇总的方式，灵活地以多种不同方式展示数据的特征。一张“数据透视表”仅靠鼠标移动字段位 置，即可变换出各种类型的报表。同时，数据透视表也是解决函数公式速度瓶颈的手段之一。因此， 该工具是最常用、功能最全的 Excel 数据分析工具之一。 28.1.1　数据透视表的用途 数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式动态表格，能帮助用户分析、 组织数据。例如，计算平均数、标准差，建立列联表、计算百分比、建立新的数据子集等。建好 数据透视表后，可以对数据透视表重新安排，以便从不同的角度查看数据。数据透视表的名字来 源于它具有“透视”表格的能力，从大量看似无关的数据中寻找背后的联系，从而将纷繁的数据 转化为有价值的信息，以供研究和决策所用。 总之，合理运用数据透视表进行计算与分析，能使许多复杂的问题简单化并极大地提高工作 效率。 28.1.2　一个简单的例子 图 28-1 所示的数据列表显示了一家贸易公司的销售数据清单。清单中包括年份、季度、销 售地区、品名、数量、单价、金额；时间跨度为 8 个季度（2005～2006 年）。只需几步简单操作， 就可以将这张“平庸”的数据列表变成能够提供有用信息的数据透视表，如图 28-2 所示。 567 28.1　关于数据透视表 E x c e l 第 章 28图 28-1　数据列表，用来创建一个数据透视表 图 28-2　根据数据列表创建的数据透视表 此数据透视表显示了不同销售人员在不同年份所销售的各种产品的销售额汇总，数据透视表 最后一行是所有销售人员所销售的各种产品的销售额总计。 从图 28-2 中可以很容易找出原始数据清单所记录的大多数信息，没有显示的数据信息仅为 销售数量和销售地区。只要再将数据透视表做进一步调整，就可以将这些信息也显示出来。将销 售年份、销售季度、销售地区移到数据区以外，使销售数量与销售金额并排显示，只需简单地从 销售年份、季度、地区字段标题左侧的下拉列表框中选择相应的年份、季度、销售地区即可查看 不同时期和不同销售地区的数据。如图 28-3 所示。 图 28-3　从数据源中提炼出符合特定视角的数据 568 第 28 章 使用数据透视表分析数据 E x c e l 第 章 28 28.1.3　数据透视表的数据组织 用户可以从 4种类型的数据源中来创建数据透视表。 （1） Excel 数据列表 （2）外部数据源 （3）进行多重合并计算的独立的 Excel 数据列表 （4）其他的数据透视表 如果以Excel数据列表作为数据源，则标题行不能有空白单元格或合并的单元格， 否则不能生成数据透视表，会出现错误提示，如图28-4所示。 图 28-4　错误提示 注意 数据透视表中的术语： 数据源 从中创建数据透视表的数据列表或多维数据集。 轴 数据透视表中的一维，如行、列、页。 列字段 信息的种类，等价于数据列表中列。 行字段 在数据透视表中具有行方向的字段。 分页符 数据透视表中进行分页的字段。 字段标题 描述字段内容的标志。可以通过拖动字段标题对数据透视表进行透视。 项目 组成字段的成员。如图 28-2 中，2005 和 2006 就是组成年份字段的项。 组 一组项目的集合，可以自动或手动地为项目组合。 透视 通过改变一个或多个字段的位置来重新安排数据透视表。 汇总函数 Excel 计算表格中数据的值的函数。文本和数值的默认汇总函数为计数和求和。 分类汇总 数据透视表中对一行或一列单元格的分类汇总。 刷新 重新计算数据透视表，反映目前数据源的状态。 28.1.4　数据透视表的工具栏 数据透视表的所有功能和特性都可以通过数据透视表工具栏来实现，如图 28-5 所示。 图 28-5　数据透视表工具栏 显示数据透视表的工具栏有 2种方法。 569 28.2　创建数据透视表 E x c e l 第 章 28 （1）选择“视图”→“工具栏”→选择“数据透视表”菜单项。 （2）鼠标右击数据透视表→在弹出的菜单中选择“显示数据透视表工具栏”。 有关数据透视表工具栏中按钮的用途，请参阅表 28-1。 表 28-1 数据透视表工具栏按钮功能 按　　钮 按 钮 名 称 按 钮 功 能 设置报告格式 弹出“自动套用格式”对话框，应用 Excel 设定好的表格样式 图表向导 创建数据透视图 隐藏明细数据 隐藏内部行或列字段 显示明细数据 显示内部行或列字段 刷新数据 刷新数据透视表 在汇总中包含隐藏项 只用于 OLAP 数据源的，在汇总中包含隐藏项 始终显示项目 始终显示项目 字段设置 弹出“字段设置”对话框 字段列表 “数据透视表字段列表”的显示或隐藏开关 28.2　创建数据透视表 使用数据透视表和数据透视图向导可以创建数据透视表，启用此向导的方法是单击 Excel 菜 单栏中的“数据”→“数据透视表和数据透视图”。在该向导的指导下，用户只要按部就班地一 步一步进行操作，就可以轻松地完成数据透视表的创建。 它的操作步骤共分为 3步。 步 骤1 选择数据源类型。 步 骤2 选择数据源区域。 步 骤3 指定数据透视表位置。 28.2.1　步骤 1　指定数据源的类型 选定图 28-1 所示的销售数据清单中任意一个数据单元格，单击菜单“数据”→“数据透视 表和数据透视图”，出现“数据透视表和数据透视图向导——3 步骤之 1”对话框，如图 28-6 所示。 图 28-6　建立数据透视表步骤 1 570 第 28 章 使用数据透视表分析数据 E x c e l 第 章 28 该步骤帮助用户确定数据源类型和报表类型。单击不同选项的选项按钮，对话框左侧的图像 将会产生相应变化。此处保留对默认选项的选择，即数据源类型为 Excel 数据列表，报表类型为 数据透视表。 28.2.2　步骤 2　指定数据源的位置 指定了数据源类型后，单击“下一步”按钮，向导将显示第 2 个对话框，“数据透视表和数 据透视图向导——3 步骤之 2”，要求指定数据源的位置，如图 28-7 所示。 图 28-7　建立数据透视表步骤 2 该步骤用于选定数据源区域。由于数据列表都是位于某个连续的单元格区域，所以，一般情 况下 Excel 会自动识别数据源所在的单元格区域，并填入到“选定区域”框。 如果 Excel 识别的数据源区域不正确，则需要用户重新选定区域，单击“选定区域”的折叠 按钮选定整个数据源，如图 28-8 所示。 图 28-8　重新选定数据源区域 如果数据源是当前未打开的数据列表，可以单击“浏览”按钮打开另一个工作表，并选择 范围。 28.2.3　步骤 3　指定数据透视表的显示位置 如图 28-9 所示，在向导的最后一个对话框中，需要指定数据透视表的显示位置。 图 28-9　建立数据透视表步骤 3 571 28.2　创建数据透视表 E x c e l 第 章 28 如果要将数据透视表显示到新的工作表上，可以选择“新建工作表”选项按钮，Excel 将为数 据透视表插入一个新的工作表。否则，可以选择“现有工作表”选项按钮，并且在文本框中指定 开始单元格位置。 单击“完成”按钮之前，可以单击“选项”按钮对数据透视表格式和数据提前进行设置。但 是建议用户在完成数据透视表的创建以后再使用“数据透视表选项”对话框设置这些选项，后者 更加灵活方便。 有两种方法可以设置数据透视表的布局，这也是创建数据透视表过程中最关键的一步。 方法 1：在“数据透视表和数据透视图向导——3 步骤之 3”对话框中单击“布局”按钮，在 “布局”对话框中设置数据透视表的结构。 方法 2：单击“完成”按钮，创建一个空的数据透视表，然后使用“数据透视表字段列表” 工具栏来布局数据透视表。 1．使用对话框布局数据透视表 当用户在“数据透视表和数据透视图向导——3 步骤之 3”对话框内单击“布局”按钮， 会出现“数据透视表和数据透视图向导——布局”对话框，如图 28-10 所示。 图 28-10　调整数据透视表的布局 销售数据清单中的各列标题作为按钮出现在对话框的右半部分。用鼠标拖动这些按钮，将其 按自己的设计要求放置在左边图中相应的位置就可以构造出数据透视表。 从结构上看，数据透视表分为 4个部分。 （1）页：此标志区域中按钮将作为数据透视表的分页符。 （2）行：此标志区域中按钮将作为数据透视表的行字段。 （3）列：此标志区域中按钮将作为数据透视表的列字段。 （4）数据：此标志区域中按钮将作为数据透视表的显示汇总的数据。 将“销售人员”、“销售年份”、“销售季度”字段按钮拖动到“行”区域；将“品名”字段按 钮拖动到“列”区域；将“销售金额”字段按钮拖动到“数据”区域，如图 28-11 所示。 单击“确定”按钮关闭“布局”对话框，最后单击“数据透视表和数据透视图向导——3 步 骤之 3”对话框的“完成”按钮，即可创建出如图 28-2 所示的数据透视表。 572 第 28 章 使用数据透视表分析数据 E x c e l 第 章 28 图 28-11　调整数据透视表的布局 2．使用“数据透视表字段列表”工具栏来布局数据透视表 用户可以使用“数据透视表字段列表”工具栏在工作表中直接来布局数据透视表。 如果用户在“数据透视表和数据透视图向导——3 步骤之 3”对话框中没有利用“布局”命 令来做相关设置，而是直接单击“完成”按钮，则 Excel 将显示一张空的数据透视表。单击空表 区域内的任意位置，将会出现放置不同字段类型的提示，并且显示“数据透视表字段列表”，如 图 28-12 所示。 图 28-12　创建空的数据透视表 在“数据透视表字段列表”内将“销售人员”，“销售年份”，“销售季度”依次拖入“将行字 段拖至此处”的区域，如图 28-13 所示。 图 28-13　矩形框和箭头说明要把字段拖至何处 573 28.2　创建数据透视表 E x c e l 第 章 28 如果字段列表对话框没有出现，可以先选中数据透视表，然后再单击数据透视表工具栏“显 示字段列表”按钮 ；也可以在数据透视表内单击鼠标右键，选择显示字段列表，激活字段列 表对话框。 在“数据透视表字段列表”内将“品名”拖入“将列字段拖至此处”的区域；将“销售金额￥” 拖入“请将数据项拖至此处”的区域，如图 28-14 所示。 图 28-14　矩形框和箭头说明要把字段拖至何处 有关数据透视表拖动字段显示的图标的意义，请参阅表 28-2。 表 28-2 数据透视表拖动字段显示的图标 图　　标 图标的意义 将字段拖至行区域 将字段拖至列区域 将字段拖至数据区域 将字段拖至页面区域 将字段移除出报表区域 此外，还可以使用“数据透视表字段列表”工具栏的“添加到”按钮把字段添加进报表。单 击“数据透视表字段列表”中的“数量”，在下拉框中将字段位置从“行区域”改为“数据区域”， 如图 28-15 所示，然后单击“添加到”按钮。 图 28-15　用“数据透视表字段列表”对话框把字段添加进数据透视表 574 第 28 章 使用数据透视表分析数据 E x c e l 第 章 28 28.2.4　创建动态的数据透视表 用户在完成数据透视表后，如果数据源增加了新的行或列，即使刷新数据透视表，新增的数 据仍无法出现在数据透视表中。为了避免这种情况的发生，可以为数据源定义名称或使用数据列 表功能来获得动态的数据源，从而来创建动态的数据透视表。 1．定义名称法创建动态的数据透视表 使用定义名称的方法来创建动态的数据透视表，首先要使用一个动态的公式定义数据透视表 的数据源。当一个新的记录添加到表格中时，数据源会自动扩展。然后将定义的名称范围用于数 据透视表，从而创建动态的数据透视表。 示例 28.1 定义名称创建动态数据透视表 在 图 28-1 所 示 的 销 售 明 细 表 中 定 义 名 称 data=OFFSET( 销 售 明 细 表 !$A$1，0，0， COUNTA( 销售明细表 !$A:$A)，COUNTA( 销售明细表 !$1:$1))，如图 28-16 所示。 图 28-16　定义动态数据源 有关定义名称的更多内容可以参阅本书第 11 章。 将定义的名称范围用于数据透视表。 步 骤1 单击销售明细表中的任意一个有效数据单元格，单击菜单“数据”→“数据透视 表和数据透视图”。 步 骤2 在“数据透视表和数据透视图向导——3步骤之1”对话框中选择“Microsoft office Excel数据列表或数据库” 并单击“下一步”。 步 骤3 在“数据透视表和数据透视图向导——3步骤之2”对话框的选定区域框内，输入 范围名称data，单击“完成”按钮。 575 28.2　创建数据透视表 E x c e l 第 章 28 图 28-17　将定义的名称用于数据透视表 步 骤4 在工作表中，将“数据透视表字段列表”对话框内的字段按钮拖至数据透视表中行、 列和数据区域。 如此即完成了动态数据透视表的创建。用户可以向作为数据源的销售明细表中添加一些新记 录，如新增一条记录，“销售地区”为“深圳”、“销售人员”为“张林波”。在数据透视表中单击 鼠标右键，在弹出的快捷菜单中单击“刷新数据”命令，即可见到新增的数据，如图 28-18 所示。 图 28-18　动态数据透视表自动增添新数据 由于在数据源“销售明细表”中添加的新记录只有销售地区和销售人员的数据，而 没有相应的增加销售年份、销售季度、品名以及数量、金额数据，因此数据透视表 中销售年份等字段会显示为“（空白）”。注意 576 第 28 章 使用数据透视表分析数据 E x c e l 第 章 28 2．使用数据列表功能创建动态的数据透视表 列表功能是 Excel 2003 中新增的功能，利用列表对数据源的自动扩展可以创建动态的数据透 视表。 示例 28.2 使用数据列表功能创建动态数据透视表 在图 28-1 所示的销售明细表中操作如下。 步 骤1 在“销售明细表”中单击任意一个有效数据单元格，单击菜单“数据”→“列表” →鼠标指向“创建列表”并单击它，如图28-19所示。 图 28-19　创建列表 步 骤2 单击“确定”按钮即可创建一张列表，如图28-20所示。 图 28-20　列表 577 28.3　改变数据透视表的布局 E x c e l 第 章 28 步 骤3 在创建的列表内单击任意一个有效数据单元格 ，在菜单栏上依次单击“数据”→“数 据透视表和数据透视图”，在“数据透视表和数据透视图向导——3步骤之1”对 话框中选择“Microsoft Office Excel数据列表或数据库”，单击“完成”。 这样，利用列表创建的动态数据透视表完成，用户可以向销售明细表中添加一些新记录，如： 新增记录中的“销售地区”为“深圳”、“销售人员”为“张林波”。在数据透视表中单击右键， 在弹出的快捷菜单中单击“刷新数据”命令，即可见到新增的数据，如图 28-17 所示。 有关列表功能的详细信息请参阅 26.7 节。 28.3　改变数据透视表的布局 对于已经完成的数据透视表，用户在任何时候都只需拖动字段按钮就可以重新安排透视布局， 满足新的要求。例如，用户想生成按销售年份统计销售金额的报表，以图 28-2 所示的数据透视 表为例，将“销售季度”字段按钮拖至“销售人员”的左侧，“销售年份”字段按钮拖至“销售季度” 的左侧，结果如图 28-21 所示。 图 28-21　 改变数据透视表布局 现在，“销售人员”出现在“销售年份”及“销售季度”的组合中，并且按照“销售年份”、“销 售季度”进行分类汇总。 28.3.1　数据透视表页面区域的使用 当字段显示在列区域或行区域上时，滚动数据透视表就可以看到字段中的所有项。然而，当 字段位于页面区域中时，则一次只能显示一项。例如在图 28-3 中，只能看到“销售地区”字段 的“北京”项。要查看字段的其他项，可以在该字段的下拉列表框中依次进行选择。 1．显示页面区域中字段的汇总 数据透视表中每个字段的下拉列表框中，首选项都是“全部”。选择该选项可以显示属于该 字段所有项目的信息。例如，将图 28-3 所示数据透视表页面区域中的“销售地区”、“品名”、“销 售年份”字段都选择“全部”，则可以得到每个销售人员在所有年份和销售地区销售所有品名的 578 第 28 章 使用数据透视表分析数据 E x c e l 第 章 28 E x c e l 第 章 28 汇总数据，结果如图 28-22 所示。 图 28-22　页面区域中所有项的汇总 2．数据透视表的分页显示功能 虽然数据透视表可以包含多个页面区域，但通常情况下只显示其中的一个页面数据。利用数 据透视表的分页显示功能，用户就可以创建一系列链接在一起的数据透视表，每张表显示页字段 的一项。 示例 28.3　分页显示数据透视表 要在图 28-22 所示的数据透视表中创建分页显示，可以参照以下步骤。 步 骤1 单击数据透视表工具栏中的“数据透视表”。 步 骤2 在弹出的快捷菜单中单击“分页显示”命令。 步 骤3 在“分页显示”对话框中单击“销售地区”。 步 骤4 最后单击“确定”按钮，可将“销售地区”字段中的每个销售地区分页显示在不 同的工作表中，并且按照“销售地区”字段的各项对工作表命名，如图28-23所示。 图 28-23　数据透视表的分页显示 579 28.3　改变数据透视表的布局 E x c e l 第 章 28 28.3.2　整理数据透视表字段 数据透视表完成后，用户可以通过对数据透视表字段的整理来满足自己对数据透视表格式上 的需求。 1．整理复合字段 示例 28.4 设置数据透视表的数据字段并排显示 如果数据透视表的数据区域中垂直显示“销售金额￥”、“数量”两个字段，如图 28-24 所示， 为了便于读取和比较数据，用户可以重新安排数据透视表的字段。 单击“数据”按钮，按着鼠标左键并拖拉“数据”按钮到有“汇总”一词的单元格，松开鼠 标左键，如图 28-25 所示。 图 28-24　数据区域垂直放置两个字段 图 28-25　拖动“数据”按钮到汇总 此时两个数据字段变成水平位置排列，如图 28-26 所示。 图 28-26　两个数据字段水平排列 2．重命名字段 当用户向数据区域添加字段后，它们都将被重命名，例如 “销售金额￥” 变成了 “求和项 : 销售金额￥”或“计数项 : 销售金额￥”，这样就会加大字段所在列的列宽，影响表格的美观。 580 第 28 章 使用数据透视表分析数据 E x c e l 第 章 28 E x c e l 第 章 28 下面介绍两种对字段重命名的方法： （1）单击数据透视表中的标题单元格“求和项 : 销售金额￥”，输入新标题“金额合计”，按 下 键，这种方法是最简便易行的。 （2）如果用户要保持原有字段的名称不变，可以采用替换的方法。选中数据透视表的标题单 元格，在菜单栏上依次单击“编辑”→“替换”，在弹出的“查找和替换”对话框中的“查找内容” 文本框中输入“求和项:”，在“替换为”文本框中输入一个空格，单击“全部替换”，完成标题重命名。 数据透视表中每个字段的名称必须唯一，Excel不接受任意两个字段具有相同的 名称。注意 28.3.3　整理数据透视表数据 1．显示所有数据项 在数据透视表中，每个字段右侧都有一个小箭头，单击它可打开一个数据项下拉列表，选择 “（全部显示）”复选框将显示全部数据项。 2．隐藏数据项 如果用户不需要显示所有数据项的数据，可以将不需要的数据项隐藏。单击数据项下拉列表 中“（全部显示）”复选框，至少选中其中一个数据项的复选框，并单击“确定”按钮。 3．隐藏页字段数据 页字段下拉列表中不是复选框，用户不能同时选中多个数据项，只能选择“全部”项或可视数 据项中的一项。下面介绍两种方法可以隐藏或显示多个数据项，以图28-26所示的数据透视表为例。 示例 28.5 隐藏数据透视表的页字段数据 方法 1　利用“数据透视表字段”对话框中的“隐藏数据项”。 步 骤1 双击页字段按钮“品名”调出“数据透视表字段”对话框。 步 骤2 在“隐藏数据项”列表中单击你想要隐藏的数据项，如“按摩椅”和“微波炉”， 如图28-27所示。 图 28-27　隐藏页字段 步 骤3 单击“确定”按钮完成。 581 28.3　改变数据透视表的布局 E x c e l 第 章 28 方法 2　利用页字段的下拉列表框。 步 骤1 将页字段按钮“品名”拖到行区域。 步 骤2 单击“品名”字段的下拉按钮，在出现的下拉列表框中取消勾选“全部显示”，再 分别取消“按摩椅”和“微波炉”的勾选，如图28-28所示。 图 28-28　隐藏页字段 步 骤3 单击“确定”按钮，将页字段按钮“品名”拖回原处，页字段“品名”的选项变为“多 项”，如图28-29所示。 图 28-29　隐藏页字段的数据透视表 4．清除数据透视表原有数据项 数据透视表的数据源改变后，会导致字段下拉列表中有些无用的数据项存在。例如有些销售 人员已经离开公司，但他们的名字仍然会在数据透视表的数据项中存在。即使用户对数据透视表 进行刷新，这些名字仍然会与新名字同时显示。 582 第 28 章 使用数据透视表分析数据 E x c e l 第 章 28 示例 28.6 彻底清除数据透视表中不应存在的数据项 在下面的数据项下拉列表中，“李兵”已经被“刘坤”代替，但他的名字仍然存在，如图28-30所示。 图 28-30　无用的数据项 清除原有的数据项的操作方法如下： 步 骤1 将数据透视字段“销售人员”字段拖到数据透视表以外的区域。 步 骤2 在数据透视表内单击右键，刷新数据。 步 骤3 最后将“销售人员”字段拖回到原来的行区域位置。 28.4　数据透视表的刷新 源数据发生变化后，数据透视表本身并不会自动刷新。要对数据透视表进行刷新，只需在数 据透视表内单击右键，单击“刷新数据”即可。 28.4.1　在打开文件时刷新 用户如果希望Excel在每次打开数据透视表所在的工作表时都进行数据刷新，则可以这样设置： 步 骤1 右键单击数据透视表，在弹出的快捷菜单中单击“表格选项”。 步 骤2 在“数据透视表选项”对话框中勾选“数据选项”的“打开时刷新”复选框，如 图28-31所示。 图 28-31　打开时刷新 步 骤3 单击“确定”按钮。 583 28.4　数据透视表的刷新 E x c e l 第 章 28 28.4.2　刷新链接在一起的数据透视表 当数据透视表用作其他数据透视表的数据源时，对其中任何一张数据透视表进行刷新，都会 引起所有链接在一起的数据透视表进行刷新。 28.4.3　刷新引用外部数据的数据透视表 如果创建的数据透视表是基于对外部数据的查询，Excel 可以在用户工作时在后台中执行数 据查询。 步 骤1 右键单击数据透视表，在弹出的快捷菜单中单击“表格选项”。 步 骤2 在“数据透视表选项”对话框中勾选“外部数据选项”中的“后台查询”复选框， 如图28-32所示。 图 28-32　基于外部数据的后台查询 步 骤3 单击“确定”按钮。 “外部数据选项”只对由外部数据创建的数据透视表可用，否则“外部数据选项” 下的复选框均为灰色。注意 28.4.4　定时刷新 如果要让数据透视表自动地定时刷新，可以这样设置： 步 骤1 右键单击数据透视表，在弹出的快捷菜单中单击“表格选项”。 步 骤2 在“数据透视表选项”对话框中勾选“数据源选项”中的“刷新频率”复选框， 并选择以分钟为单位指定刷新的时间间隔。 步 骤3 单击“确定”按钮。 这一选项也只对由外部数据创建的数据透视表适用。 584 第 28 章 使用数据透视表分析数据 E x c e l 第 章 28 28.5　设置数据透视表的格式 用户可用设置单元格格式的方法来修改数据透视表中单元格的外观。例如，改变字体、字号、 设置数字格式、填充颜色等。如果要避免刷新数据后发生格式丢失，可按如下步骤加以解决。 步 骤1 右键单击数据透视表。 步 骤2 在弹出的快捷菜单中单击“表格选项”。 步 骤3 在“数据透视表选项”对话框的“格式选项”中勾选“保留格式”复选框。 步 骤4 最后单击“确定”按钮。 28.5.1　数据透视表自动套用格式 Excel 为数据透视表提供了超过 20 种的自动套用格式选项。要应用这些格式，可在数据透 视表中选择任意单元格，然后单击数据透视表工具栏上的“设置报告格式”按钮，在“自动套 用格式”对话框中选择一种报表格式。此外，也可以单击 Excel 菜单“格式”→“自动套用格式” 来设置。 要取消现有数据透视表中的自动套用格式，右键单击数据透视表，在弹出的快捷菜单中单击 “表格选项”，在“数据透视表选项”对话框中取消勾选“自动套用格式”复选框，单击“确定”按钮。 有关设置工作表格式的详细信息，请参阅第 6 章。 28.5.2　改变数据透视表中所有单元格的数字格式 如果用户要改变数据透视表中所有单元格的数字格式，只需选中这些单元格再设置单元格格 式，可以参见以下步骤。 步 骤1 鼠标单击数据透视表。 步 骤2 按下组合键，选中整个数据透视表，单击鼠标右键。 步 骤3 在弹出的快捷菜单中单击“设置单元格格式”。 步 骤4 在弹出“单元格格式”对话框中选择“数字”选项卡进行相关的数字格式设置。 有关数字格式设置的详细信息，请参阅第 5 章。 28.6　在数据透视表中排序 28.6.1　使用手动排序 用户可以使用 Excel 中的“排序”功能对数据透视表的字段项进行排序，该字段项所有的实 例都将会进行排序，并在改变数据透视表布局时保持既定的排列顺序。 585 28.6　在数据透视表中排序 E x c e l 第 章 28 示例 28.7 在数据透视表中进行手动排序 图 28-33　数据透视表的垂直居中 要对图 28-33 所示的数据透视表中按“销售季度”项进行降序排序，可以通过以下步骤来 实现。 步 骤1 单击单元格B6（销售季度）。 步 骤2 在菜单栏上依次单击“数据”→“排序”。 步 骤3 在“排序”对话框中选择“降序”。 步 骤4 单击“确定”按钮结束。 Excel 会重新排列“销售季度”的显示顺序，在数据透视表中任何出现“销售季度”的地方均 按“4”、“3”、“2”、“1”的顺序排列，如图 28-34 所示。 图 28-34　按“销售季度”降序排序 586 第 28 章 使用数据透视表分析数据 E x c e l 第 章 28 28.6.2　使用自动排序 用户还可以对希望排序的字段使用自动排序。自动排序特性与标准的排序功能一样，都会对 该字段项所有的实例进行排序，只是增加了附加选项，可以按照数据的值对字段项进项排序。如 图 28-35 所示。 图 28-35　数据透视表自动排序的附加选项 示例 28.8 在数据透视表中进行自动排序 要对图 28-33 所示的数据透视表中按“销售人员”字段进行降序的自动排序，可按以下步骤 进行。 步 骤1 双击“销售人员”字段按钮，出现“数据透视表字段”对话框。 步 骤2 单击“高级”按钮，出现“数据透视表字段高级选项”对话框，如图28-36所示。 图 28-36　“数据透视表字段高级选项”对话框 步 骤3 自动排序选项选择“降序”单选按钮，“使用字段”下拉列表框中选择“销售人员”， 单击“确定”按钮。 28.6.3　使用非标准顺序排序 用户如果既不希望按降序又不希望按升序排序，可以使用自定义的排序方法进行排序。 在第 27 章中介绍过的有关自定义排序的方法，同样适用于数据透视表。 587 28.7　数据透视表的项目组合 E x c e l 第 章 28 28.7　数据透视表的项目组合 数据透视表能够自动地在外部字段的标题下对内部字段的项进行组合，并可按照用户的要求 来创建各个内部字段的分类汇总。在如图 28-22 所示的数据透视表中，“销售人员”属于外部字段， 而“销售季度”则属于内部字段。 项目组合是数据透视表的一个非常有用的特性，Excel 提供了以下几种对项进行组合的选择。 28.7.1　组合数据透视表的指定项 图 28-37　组合前的数据透视表 图 28-37 所示的示例文件“28.7 指定项组合 .xls”可从 Excel Home 网站下载。 如果用户在图 28-37 所示的数据透视表中，希望将“国内市场”、“送货上门”、“网络销售”、 “邮购业务”的销售数据组合在一起，合称为“国内业务”，可参考如下步骤创建此项组合。 步 骤1 在数据透视表中，鼠标单击“国内市场”，按住键盘上的键，用鼠标依次 单击“送货上门”、“网络销售”、“邮购业务”标题（在图28-37中就是A6、A7、 A8、A9单元格）。 步 骤2 单击鼠标右键，在弹出的快捷菜单中单击“组及显示明细数据”，鼠标指向“组合” 并单击它；Excel创建了新的字段标题，并自动命名为“销售途径2”，并且将选中 的项组合到新的“数据组1”的项中。 步 骤3 选中标为“数据组1”的单元格，输入新的名称“国内业务”，如图28-38所示。 图 28-38　创建指定项的组合 28.7.2　数字项组合 要将字段中的数字项组合，可以在分组对话框中进行设置。例如，将图 28-2 所示的“销 售季度”字段组合，在数据透视表中单击该字段的任意项，单击鼠标右键，在弹出的快捷菜单 588 第 28 章 使用数据透视表分析数据 E x c e l 第 章 28 中单击“组及显示明细数据”，鼠标指向“组合”并单击它，出现“组合”对话框，如图 28-39 所示。 图 28-39　“组合”对话框 如果用户希望将每 2 个季度创建为一组，可以在“起始于”文本框中输入“1”，在“终止 于”文本框中输入“4”，在“步长”文本框中输入“2”，单击“确定”按钮，完成后的效果如 图 28-40 所示。 图 28-40　数字项组合 28.7.3　按日期或时间项组合 图 28-41　日期按原始项目排列的数据透视表 如图28-41所示的数据透视表显示了按订单日期统计的销售人员的订单金额。在实际工作中， 因为日期仍然按照原始项目按天排列，难以体现出分段时间的统计信息。在此种情况下，用户可 以对日期项进行分组使表格变得更有意义，具体步骤如下。 589 28.7　数据透视表的项目组合 E x c e l 第 章 28 步 骤1 在数据透视表“订单日期”字段内单击鼠标右键，在弹出的快捷菜单中单击“组 及显示明细数据”，鼠标指向“组合”并单击它，如图28-42所示。 图 28-42　按日期项组合 步 骤2 在出现的“分组”对话框中，保持起始和终止日期的默认设置，再单击“步长” 列表框中的“年”，按键同时再选中列表框中的“月”，如图28-43所示。 图 28-43　组合日期字段 步 骤3 单击“分组”对话框中的“确定”按钮，完成的效果如图28-44所示。 图 28-44　按日期项组合后的数据透视表 590 第 28 章 使用数据透视表分析数据 E x c e l 第 章 28 28.7.4　取消项目组合 如果不需要某个组合，可以选中这个组合单击鼠标右键，在弹出的快捷菜单中单击“组及显 示明细数据”，鼠标指向“取消组合”并单击它，即可删除组合，将字段恢复到组合前的状态。 28.7.5　组合数据时遇到的问题 当用户试图对一个日期或字段进行分组时，可能会得到一个错误信息警告，内容为“选定区 域不能分组”，如图 28-45 所示。 进行分组字段的数据源中如果包含有空白的单元格或文本 型的日期、数字，在组合时会提示用户选定的区域不能分组。 解决这个问题可以参见以下步骤。 步 骤1 在空白单元格填充日期或数字 (如有必要填充一个假设的日期或数字)。 步 骤2 如果在日期或数字字段存在文本，移除它。 步 骤3 如果数字被Excel视为了文本，将它改变为常规类型。 28.8　在数据透视表中执行计算 在默认设置下，Excel 对数据透视表数据区域的数字字段应用求和函数，对非数字字段应用计 数函数。 Excel 提供了多种汇总方式供用户选择，包括“求和”、“计数”、“平均值”、“最大值”、“最小值”、 “乘积”等。要选择汇总方式，可在数据透视表的数据区域中单击右键，在弹出的快捷菜单中单 击“字段设置”。Excel 会显示出如图 28-27 所示的“数据透视表字段”对话框，选择要采用的汇 总方式，最后单击“确定”按钮。 28.8.1　对同一字段应用多种汇总方式 用户可以对一个数据字段同时应用多种汇总方式。要为已经位于数据透视表数据区域的字段 添加第二种或更多的汇总方式，只需在“数据透视表列表”内将该字段再一次拖进数据透视表的 数据区中，利用“字段设置”对话框选择想用的汇总方式即可，如图 28-46 所示。 图 28-46　同一字段应用多种汇总方式 图 28-45　选定区域不能分组 591 28.8　在数据透视表中执行计算 E x c e l 第 章 28 28.8.2　自定义计算 如果“数据透视表字段”对话框内的汇总方式仍然不能满足需求，Excel 还允许用户自定义计 算。利用此功能，可以计算数据透视表数据区域中每项占同行或同列数据总和的百分比，或显示 每个数值占总和的百分比。 应用自定义计算的步骤为： 步 骤1 在数据透视表的数据区域中单击鼠标右键，在弹出的快捷菜单中单击“字段设置”， 在“数据透视表字段”对话框内单击“选项”按钮，出现扩展对话框“数据显示方式”， 如图28-47所示。 图 28-47　通过“数据显示方式”应用自定义计算 步 骤2 在“数据显示方式”下拉列表中选择想要执行的计算，再从“基本字段”和“基 本项”列表中选择适当的选项。例如，在如图28-43所示的数据透视表中要显 示每名销售人员与销售人员“毕春艳”的“订单金额”的差异，在“数据显示方 式”中选择“差异”；“基本字段”选择“销售人员”；“基本项”中选择“毕春艳”， 如图28-48所示。 图 28-48　使用“差异”自定义计算 最后单击“确定”按钮完成。 592 第 28 章 使用数据透视表分析数据 E x c e l 第 章 28 有关数据透视表自定义计算功能描述，请参阅表 28-3。 表 28-3 自定义计算功能描述 选　　项 功 能 描 述 普通 数据区域字段按普通方式而不是指定的函数汇总，此为默认的计算 差异 数据区域字段与指定的基本字段和基本项的差值 百分比 数据区域显示为基本字段和基本项的百分比 差异百分比 数据区域字段显示为与基本字段项的差异百分比 按某一字段汇总 数据区域字段显示为基本字段项的汇总 占同行数据总和的百分比 数据区域字段显示为每个数据项占该行所有项总和的百分比 占同列数据总和的百分比 数据区域字段显示为每个数据项占该列所有项总和的百分比 占总和的百分比 数据区域字段显示为每个数据项占该字段所有项总和的百分比 指数 使用公式：(( 单元格的值 )×( 总体汇总之和 ))/(( 行汇总 )×( 列汇总 )) 28.8.3　在数据透视表中使用计算字段和计算项 除了以上介绍的多种汇总方式以外，Excel 还允许用户向数据透视表中添加计算字段和计算 项。计算字段是通过对表中现有的字段执行计算后得到的新字段；计算项则是在已有的字段中 插入新的项，是通过对该字段现有的其他项执行计算后得到的。一旦创建了自定义的字段或项， Excel 就允许在表格中使用它们，它们就像是在数据源中真实存在的一样。 1．创建计算字段 图 28-49 中展示了一张已经创建成功的数据透视表，下面通过添加计算字段，来完成销售人 员提成的计算。 图 28-49　需要创建计算字段的数据透视表 步 骤1 在数据透视表内单击任意单元格，单击数据透视表工具栏的“数据透视表”，在弹 出的快捷菜单中单击“公式”，鼠标指向“计算字段”并单击它，激活“插入计算 字段”对话框，如图28-50所示。 步 骤2 在“插入计算字段”对话框的“名称”框内输入“销售人员提成”，将光标定位到“公式” 框中，清除原有的数据“＝0”，双击“字段”中的“订单金额”，然后在“公式”框的 “=订单金额”后输入“*0.015”（销售人员的提成按1.5％计算），如图28-51所示。 593 28.8　在数据透视表中执行计算 E x c e l 第 章 28 图 28-50　插入计算字段 图 28-51　将现有的字段乘上参数得到新字段 步 骤3 单击“添加”按钮，最后单击“确定”按钮关闭对话框。此时，数据透视表的数 据区域中已经创建了一个新的字段“销售人员提成”，如图28-52所示。 图 28-52　添加“销售人员提成”计算字段后的数据透视表 2．添加计算项 如果要对如图28-53所示的数据透视表创建“国际业务-国内市场”的计算项，操作步骤如下。 图 28-53　准备创建计算项的数据透视表 步 骤1 单击数据透视表中“销售途径”字段标题或分别单击“销售途径”字段标题下的“国 际业务”和“国内市场”，单击数据透视表工具栏的“数据透视表”，在弹出的快 捷菜单中单击“公式”，鼠标指向“计算项”并单击它，激活“在销售途径中插入 计算字段”对话框。 594 第 28 章 使用数据透视表分析数据 E x c e l 第 章 28 步 骤2 在“在‘销售途径’中插入计算字段”对话框的“名称”框内输入“国际业务- 国内市场”，将光标定位到“公式”框中，清除原有的数据“＝0”，双击“项”中 的“国际业务”，在“公式”框的“=国际业务”后输入“-”，再双击“项”中的“国 内市场”，此时对话框如图28-54所示。 图 28-54　添加“国际业务—国内市场”计算项 步 骤3 单击“添加”按钮，最后单击“确定”按钮关闭对话框。此时，数据透视表行字 段区域中已经插入了一个新的项目“国际业务-国内市场”，如图28-55所示。 图 28-55　添加“国际业务—国内市场”计算项后的数据透视表 28.9　获取数据透视表数据 数据透视表创建完成后，用户要想获取数据透视表中数据，可以使用 GETPIVOTDATA 函数。 只需在数据透视表之外的单元格中输入一个等号并单击数据透视表中的一个单元格就可以看到一 个替代单元格引用产生的透视数据公式，如图 28-56 所示。 图 28-56　引用数据透视表数据 595 28.10　创建复合范围的数据透视表 E x c e l 第 章 28 GETPIVOTDATA 函数有 2个参数。 第 1 个参数是对数据透视表本身的引用，它可以是数据透视表中的任意单元格、分配给数据 透视表的数据区域名或存储在表格左上角内的单元格中的标志。 第 2 个参数则告知 Excel 所需数据的类型，它包含在双引号中，指定各字段名，这些字段名 在表中的交叉点就是要获取的数据。第 2个参数中的各字段名之间要用空格键隔开。 GETPIVOTDATA函数获取的数据在数据透视表中必须是可见的，否则将返回错误 值“#REF!”。注意 例如，对于图 28-2 所示的数据透视表，要获取销售人员“毕春艳”在 2006 年第三季度的销 售数据，可以输入： =GETPIVOTDATA(数据透视表 !A1,"毕春艳 2006 3") 要获取销售人员“高伟”液晶电视的销售数据，可以输入： =GETPIVOTDATA(数据透视表 !A1,"高伟 液晶电视 ") 要获取销售人员“何庆”在 2006 年第二季度跑步机的销售数据，可以输入： =GETPIVOTDATA(数据透视表 !A1,"何庆 2006 2 跑步机 ") 28.10　创建复合范围的数据透视表 用户可以使用来自同一工作簿的不同工作表或不同工 作簿中的数据，来创建数据透视表，前提是它们的结构完 全相同。在创建好的数据透视表中，每个源数据区域均显 示为页轴的一项。通过页轴上的下拉列表，用户可以一览 无余地查看各个源数据区域以及对各数据区域合并计算后 的汇总表格。例如，图 28-57 按月份显示了某公司各部门 的费用明细，数据存储在 3 个独立的工作表“1 月份”、“2 月份”和“3 月份”中。 28.10.1　创建单页字段 要从工作表生成多重合并计算数据区域的数据透视表，可按照如下步骤进行： 步 骤1 单击“数据”菜单，选择“数据透视表和数据透视图”命令。 步 骤2 “数据透视表和数据透视图向导——3 步骤之 1”对话框显示出来后，选择“多重 合并计算数据区域”选项，接着单击“下一步”按钮。 步 骤3 “数据透视表和数据透视图向导——3 步骤之 2a”对话框显示出来后，接受“创 建单页字段”选项（此项为默认选项）。 单击“下一步”按钮，激活“数据透视表和数据透视图向导——3 步骤之 2b”对话框， 如图28-58所示。 图 28-57　可以合并计算工作簿 中的 3 个工作表 596 第 28 章 使用数据透视表分析数据 E x c e l 第 章 28 图 28-58　在此对话框中指定合并计算的所有数据区域 步 骤4 选择参加数据透视表合并计算的第一个数据区域，接着单击“添加”按钮。本例中， 第一个参加合并计算的数据区域为'1月份'!$A$1:$E$26。其他区域分别是'2月份 '!$A$1:$E$17、'3月份'!$A$1:$E$23。 在指定数据区域进行合并计算时，要包括行和列的标题，但是不要包括汇总的行 和列，数据透视表创建后会自动计算汇总的行和列。注意 步 骤5 对每个希望合并计算的区域重复步骤4。当指定了所有的数据区域后，单击“下一 步”按钮，选择Excel显示数据透视表的位置，再单击“完成”按钮。 步 骤6 在默认的情况下，Excel对数据源区域中的数值进行求和汇总。如果希望使用不同 的汇总方式，可双击“求和项:值”标题，选择要用的汇总方式，最后单击“确定” 按钮完成。 图 28-59　完成后的多重合并计算数据区域的数据透视表 图 28-59 显示了完成后的数据透视表。现在的页轴项为“全部”，这一页显示了所有月份费 用明细的汇总。选择页轴上的其他项，可单独显示各个月份的数据。 28.10.2　创建自定义页字段 在上例步骤 3 中，“数据透视表和数据透视图向导——3 步骤之 2a”对话框显示出来后，接 受的是默认选项“创建单页字段”，完成后的数据透视表页轴会出现 Excel 默认的“全部”、“项 1”、 “项 2”、“项 3”，如果用户需要将页轴中的各项改变为与待合并的各个工作表同名，则可以选择“自 597 28.11　创建数据透视图 E x c e l 第 章 28 定义页字段”。添加各月份的数据区域后，在“字段 1”的文本框中分别输入“1 月份”、“2 月份”、 “3 月份”即可，如图 28-60 所示。 图 28-60　使用自定义页字段 28.10.3　创建复合范围的数据透视表的限制 在创建多重合并计算数据区域的数据透视表时，Excel会以各个待合并的子表的第一列作为合并的 基准，如果子表的非数据列有多个，Excel也只会选择第一列。这一点与Excel的合并计算功能比较类似。 28.11　创建数据透视图 用户可以通过“数据透视表和数据透视图向导”（如图 28-5 所示），在所需创建的报表类型 中选择“数据透视图（及数据透视表）”来创建数据透视图，Excel 将会在创建一张新的数据透视 表的同时，创建一幅数据透视图。此外，也可以先创建数据透视表，接着在数据透视表中选择任 意单元格，然后单击“数据透视表”工具栏中的“图表向导”按钮，Excel 将在当前数据透视表的 基础上创建一幅数据透视图，如图 28-61 所示。 图 28-61　数据透视图 不论采用哪种方法，数据透视图和数据透视表都链接在一起，其中之一的改变就会立即影响 另一个。 图 28-61 所示的“28.11 数据透视图 .xls”数据图示例文件可从 Excel Home 网站下载。