第1章 快速掌握数据透视表技术.pdf

1、人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 第1章 快速掌握数据透视表技术 可能经常需要处理数据量非常庞大的表格，面对各种计算、分类统计、筛选数据等操作，是不是总感觉非常复杂，而且进行着重复而乏味的劳动？如果希望让工作变得更加轻松，那么请使用数据透视表。数据透视表可以通过几下单击，完成众多通过复杂操作才能完成的任务。本章将详细介绍有关数据透视表的各方面技术。 1.1 了解与创建数据透视表数据透视表在 Excel 中是一个非常出色的工具，它具有极强的交互性，可以把用户手中的数据魔术般地转换成各种有使用价值的报表（所谓报表，即指有意义的数据统计表或数

2、据清单） 。只要为普通数据创建了数据透视表，就可以根据用户的需要随意安排数据透视表的布局结构，不同的搭配方式可以获得不同的统计结果，这正是数据透视表的灵活所在。也正因为数据透视表具有这种独一无二的特性，才使得人们在处理大型数据时更加得心应手、化繁为简。 1.1.1 哪些数据适合创建数据透视表 一般情况下，在创建数据透视表时所使用的数据源都存储在 Excel 工作簿中。也有些时候需要从外部数据库中导入已经整理好的大量数据，如 Access 数据库、SQL Server 数据库等。而在 Excel 工作簿中的数据源又分为两种：普通数据和表数据。这些类型的数据都可作为创建数据透视表的数据来源。 1

3、Excel 工作簿内的普通数据 存储在Excel工作簿中的普通数据，可作为创建数据透视表的数据源。但是在创建数据透视表时，对这些数据的格式是有一定要求的，具体内容请参考1.1.3节。如图 1-1所示就是可用于创建数据透视表的普通数据。 1人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 图1-1 Excel工作簿内的普通数据 2 Excel 中以表形式存在的数据 在创建数据透视表之前， 可能有些用户根据需要已经使用了Excel 2013提供的表功能，对数据进行了某些分析工作，例如排序与筛选。如果已经将工作表中的数据转换为 Excel特有的表形式（如图

4、1-2所示） ，那么可以将表直接转换为数据透视表。将普通数据转换为表的方法如下：单击数据区域中任意一个单元格，然后单击功能区中的【插入】 【表格】【表格】按钮，在打开的【创建表】对话框中单击【确定】按钮。 图1-2 将普通数据转换为表 3 外部来源的数据 除了使用 Excel 工作簿内的不同数据形式来创建数据透视表外，还可以使用位于 Excel外部的多种类型的数据来作为数据透视表的数据源。在 Excel 中可以导入以下几种外部来源的数据： Access 数据库中的数据： 由 Access 应用程序创建的数据库文件。 在 Excel 中可以导入该类型的数据，并选择其中的表数据来创建数据透视表。

5、SQL Server 数据库中的数据： 如果在计算机中安装了 SQL Server 应用程序， 并在其中创建了数据库，那么可以通过 Excel 来导入 SQL Server 数据库中的数据，直接将其创建为数据透视表。 OLAP 多 维数据集： 使用 Excel 2013 中提供的 Analysis Services 连接， 可 以连接到联机分析处理（OLAP ，On Line Analytical Processing ）数据库，然后将其中的多维数据集导入到 Excel 工作表中，并选择创建为数据透视表或数据透视图。 其他数据类型：其他数据类型包括文本文件和网站中的数据等。 1.1.2 了解数

6、据透视表的结构 在学习后续大量关于数据透视表的操作和技巧之前，有必要先来了解一下数据透视表的结构，希望您能和我达成共识，这样便于交流，也便于理解后面内容中的一些术语。对于任何一个数据透视表来说，可以将其整体结构划分为 4大区域。 2人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 1 报表筛选区域（页字段与页字段项） 报表筛选区域是数据透视表顶端的一个或多个下拉列表， 通过选择下拉列表中的选项，可以一次性对整个数据透视表进行筛选。在如图 1-3所示的数据透视表中， 【产地】字段位于报表筛选区域，并且选择了该字段中的【北京】字段项，得到如图 1-3 所示

7、的筛选结果，该结果显示了【产地】为【北京】的所有产品的销售情况。 报表筛选区域 图1-3 深灰色区域为数据透视表的报表筛选区域 在报表筛选区域中主要放置一些要重点统计的内容类型。例如，希望对整个数据表按不同的日期进行统计，或者按商品的不同产地来观察整个产品的销售情况。那么就需要将“日期”或“产地”放置到报表筛选区域中。 提示： Excel 2013 对默认创建的数据透视表使用了压缩类型的布局方式， 而上图使用的是表格形式的布局方式。本章后面内容中的数据透视表主要以表格形式的布局呈现，目的是兼顾使用数据透视表的老用户。有关如何改变报表布局的内容请参考1.3.1 节。 2 行区域（行字段与行字段项

8、） 行区域位于数据透视表的左侧，其中包括具有行方向的字段。每个字段又包括很多字段项，每项占一行，通过单击行字段右侧的下拉按钮，可以在弹出列表中选择这些项。例如，在如图 1-4所示的数据透视表中， 【类别】和【商品】就是行字段，而【类别】字段又包括【电器】 、 【化工】 、 【家具】等字段项。 有些数据透视表包括不止一个行字段（如图 1-4所示就是这样的例子） 。对于这样的情况来说，将靠近数据透视表左边界的行字段称为“外部行字段” ，而将远离数据透视表左边界的行字段称为“内部行字段” ，内部行字段的显示附属于外部行字段。 举例来说，在图 1-4 中，首先按【类别】行字段中的字段项（电器、化工）显

9、示数据，然后根据【类别】字段中字段项的不同，显示这些项下更详细的分类数据（彩电、微波炉） ，这就说明在数据透视表中可以让数据以层级方式显示， 只要排列好行字段的先后顺序即可。 3人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 行区域 图1-4 深灰色区域为数据透视表的行区域 在行区域中主要放置一 如，商品、产地、城市等。 组成，其中包括具有列方向的字段。每个字段又些可用于进行分组或分类的内容，例3 列区域（列字段与列字段项） 列区域由位于数据透视表各列顶端的标题包括很多字段项，每项占一列，通过单击列字段右侧的下拉按钮，可以在弹出列表中选择这些项。例如

10、，在如图 1-5所示的数据透视表中， 【季度】就是列字段，它包括【第 1季度】 、 【第 2 季度】 、 【第 3 季度】和【第 4 季度】等字段项。 图1-5 深灰色区域为数据透视表的列区域 列区域 4人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 在列区域中主要放置一 、月份等。对于像商品、产地除去前面介绍的 3 大区域外的其他部分，即为数值区域，如图 1-6所示。在数值区域的最右侧 ，可以根据实际需要决定是1.1.3 整理用于创建数据透视表的数据源 也许有很多人在平时的工作中要用到数据透视表，但是在创建数据透视表之前是否考虑过，应该确保每列数据

11、包含列标题。这样在创建数据透视些可以随时间变化的内容，例如，年份、城市等内容也可以放到列区域中。 4 数值区域 在数据透视表中，数值区域中的数据是对数据透视表信息进行统计的主要来源，在这个区域中的数据应该是可以参与计算的。默认情况下，Excel 对数值区域中的数据进行求和计算。 图1-6 深灰色区域为数据透视表的数值区域 数值区域和最下方，默认会显示对行列数据的总计否显示这些内容。在数值区域中，Excel还会对行字段中的数据进行分类汇总，但是用户可以根据实际需要改变分类汇总的类型，或者决定Excel是否显示这些分类汇总，具体方法请参考1.3.7节。 要先对数据源进行检查，看看其格式是否可以满足

12、创建数据透视表的要求。其实，在创建数据透视表之前，应该对数据源的布局排列方式加以重视，只有这样才不至于在创建的过程中出现错误，或导致在创建后的数据透视表中存在某些问题。 数据中的每列都要包含标题 在用于创建数据透视表的数据源中表后，才能正确显示出分类字段，然后将各分类字段放置到数据透视表中，便于正常使用。如图 1-7所示为每列拥有标题的数据源及其创建数据透视表后的效果。在右侧的【数据透视表字段】窗格中可以看到，由于数据源的每列中都包含列标题，所以在创建的数据 5人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 透视表中的字段名称就是数据源中的标题名称，

13、每类数据的含义非常清晰。 图1-7 拥有列标题的数据源与创建后的数据透视表 如果在数据源中缺 图 1-8所示。在右侧的【数少列标题，那么创建出的数据透视表将类似如据透视表字段】窗格中可以看到，由于数据源的每列中缺少标题，所以在创建的数据透视表中的字段名称只能以数据源中每列的第 1 个数据内容命名。这样就会使每列的数据含糊不清，为以后分析数据带来很多问题。 图1-8 没有列标题的数据源与创建后的数据透视表 6人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 每列数据具有惟一性 “每列数据具有惟一性” ，这句话从字面上来看似乎不是很好理解，这里还是来看一个

14、实际的例子。如图 1-9所示的表格是一个很普通的表格，但是如果将这种结构的数据创建为数据透视表，可能就不是很适合。因为 B、C、D列的标题既可以作为标题，又可以作为具体的数据。 图1-9 同类型的字段在表格中多列分布 在理想的数据源格式中，这种情况的列标题最好不要出现，可以通过整理并重新安排表格结构来解决这个问题。这里要重新创建两列数据，一列用于存放月份，而另一列则存放与月份对应的销量数据。因此，经过整理后得到如图 1-10所示的表格，这种结构对于创建数据透视表是比较理想的。 图1-10 整理后的表格 通过上面的例子，说明了在要创建数据透视表的数据源中，每列数据尽量为不同类型，不要出现同类型的

15、数据。这里说的数据类型不是指数值格式、日期格式等类型，而是指可以互相包容或可以归纳到一起的数据内容。像上面例子中的每个具体的“月” （1 月、2 月等）可以归纳到“月份”中。再比如，工厂中每个具体的“车间” （第 1 车间、第 2 车间等）可以归纳到“车间”中。 数据中不能有空行、空列和空单元格 在用于创建数据透视表的数据源中，一定要保证数据源的有效区域中不能存在有空白行。因为如果数据源中的某行没有任何数据，当使用 Excel 自动获取数据区域的方法来选择数据源时，只会将用于创建数据透视表的有效区域选择到空行处为止。也就是说由于空行的存在，将整个区域隔开了，导致 Excel 无法正确判断整个数

16、据源的范围。如图 1-11所示为数据源中存在空行后打开的【创建数据透视表】对话框，在【表/区域】文本框中自动填入 Excel 获取的数据源区域。 7人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 图1-11 存在空行将自动获取不完整的数据源区域 数据源中存在空白列的问题与空白行是类似的，都会影响数据区域的正确获取问题，但是空白列的问题会更加严重。因为将存在空白列的数据源创建数据透视表时， Excel 只能获取到存在空白列左侧的数据源区域，而空白列右侧的所有数据区域将无法被获取，这样在创建后就会丢失很多字段。因此，在创建数据透视表前，应该对数据源中的空

17、白行和空白列问题多加注意。 除了空行和空列问题外，还应该注意数据源中是否存在空白单元格。这里需要说明的是，数据源中存在空白单元格也是可以创建数据透视表的，而且并不影响数据源区域的完整性。但是由于其中存在空白数据，势必会影响对数据的正确分析结果。对于数据源存在空白单元格的情况，应该尽可能地以同类型的代表缺少意义的值来填充空单元格。例如，可以使用 0来填充这些空白单元格。 技巧：要想快速填充具有同类型数据的空白单元格，可以通过 Excel 提供的“定位条件” 功能快速完成。 选择整个数据源区域， 然后单击功能区中的 【开始】 【编辑】 【查找和选择】按钮并选择【定位条件】命令，打开【定位条件】对话

18、框，选中【空值】单选按钮。 单击 【 确定】 按钮后将自动选中数据源中的所有空白单元格， 输入 0 后按 【Ctrl+Enter 】组合键即可。 1.1.4 创建数据透视表 在了解了创建数据透视表的数据来源和数据透视表的整体结构后，下面我们就可以开始着手创建符合自己需要的数据透视表了。 在 Excel 2003 中要创建数据透视表首先会打开一个对话框，经过几个步骤的设置才能创建出基本的数据透视表。在 Excel 2013 中，为了简化创建过程的复杂性，虽然最开始也需要先打开一个对话框，但只需在这个对话框稍加设置或根本不用设置，即可快速创建数 8人民邮电出版社Excel公式与函数大辞典配套光盘附

19、赠电子书Excel 2013数据透视表篇 据透视表。打开【创建数据透视表】对话框可以使用以下两种方法： 单击数据源区域中的任意一个单元格，然后单击功能区中的【插入】【表格】【数据透视表】按钮。 先将数据区域转换为表 （请参考1.1.1 节） ， 然后单击功能区中的 【设计】 【工具】【通过数据表汇总】按钮。 打开如图 1-12所示的【创建数据透视表】对话框。根据操作的不同，可以将该对话框分为上下两部分。上半部分主要是让用户选择用于创建数据透视表的数据源的来源和具体范围；下半部分是让用户选择数据透视表创建后所要放置的位置。这里先介绍上半部分的操作，分为以下两种情况： 图1-12 【创建数据透视表

20、】对话框 如果在打开【创建数据透视表】对话框之前，已经单击了数据源区域内的任意一个单元格或紧邻数据源区域的单元格，那么在默认打开的【创建数据透视表】对话框中， 将自动选中 【选择一个表或区域】 单选按钮， 并在 【表/ 区域】 文本框中自动填入与光标所在单元格连续的整个数据区域， Excel 可以很智能地检测出连续的数据范围。 如果在打开【创建数据透视表】对话框之前，并没有单击数据源区域内或与数据源区域紧邻的单元格，那么在打开【创建数据透视表】对话框后，在【表/ 区域】文本框中将不会自动填入任何内容。可以直接返回工作表中选择数据源区域，选择后将自动返回 【创建数据透视表】 对话框 。 也可以单

21、击 【表/ 区域】 文本框右侧的折叠按钮 ，返回工作表选择需要的数据源区域，如图 1-13所示。选择好后单击展开按钮 返回【创建数据透视表】对话框。 9人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 图1-13 手工选择数据源区域 提示：如果需要导入位于 Excel 外部的数据源，那么需要选中【使用外部数据源】单选按钮，然后单击【选择连接】按钮完成后续的导入操作。 在选择好用于创建数据透视表的数据源后，接下来就可以选择将数据透视表创建到什么位置上。这里也分为两种情况： 如果在上一步选择数据源区域是由 Excel 自动获取的，那么默认创建数据透视表的位

22、置则为新的工作表中， 也就是在 【创建数据透视表】 对话框中将自动选中 【新工作表】单选按钮。 如果在默认情况下 Excel 没有自动获取数据源，而是由用户手工选择，那么将自动选中【现有工作表】单选按钮，也就是将数据透视表创建在与数据源所在的同一个工作表中。可以单击【位置】文本框右侧的折叠按钮 重新选择要放置数据透视表的工作表。 提示：在创建好数据透视表后，可以随时移动数据透视表的位置。只要单击数据透视表中的任意一个单元格，然后单击功能区中的【分析】 【操作】 【移动数据透视表】按钮，打开【移动数据透视表】对话框（如图 1-14 所示） ，选择数据透视表的新位置即可 。 图1-14 【移动数据

23、透视表】对话框 数据源和放置数据透视表的位置都选择好之后，单击【创建数据透视表】对话框中的【确定】按钮，即可在指定位置创建一个空白的数据透视表，并在右侧自动打开【数据透视表字段】窗格，如图 1-15所示。左侧的报表用于显示经过字段排列布置后而得到的汇总结果，右侧的【数据透视表字段】窗格则是用来布置各字段在数据透视表中的位置，或是 10人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 对字段进行操作的区域。 图1-15 创建空白的数据透视表 1.2 构建数据透视表的布局创建的空白数据透视表毫无意义，只有经过各种设置，才能使其反应出有价值的汇总信息。本部分

24、将介绍如何构建出有意义的数据透视表。 1.2.1 了解【数据透视表字段】窗格 如何能够让数据透视表根据用户的需求而重组数据以便显示多种汇总信息呢？这就要用到【数据透视表字段】窗格，而该窗格也是创建与布置数据透视表的基地。在数据源区域中，顶端第一行排列着各列的标题，当以数据源创建数据透视表后，数据源中各列的标题将以复选框的形式出现在【数据透视表字段】窗格中，如图 1-16所示。 11人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 图1-16 数据源中各列的标题与【数据透视表字段】窗格中字段的对应关系 可以将【数据透视表字段】窗格分为上下两部分：上面的区

25、域显示了数据透视表中可用的字段，选中字段前的复选框即可将其按Excel默认的设置添加到下方的 4 个列表框中。而下面的区域包括了 4 个列表框，它们分别对应于1.1.2节介绍的数据透视表结构的 4 个部分。 当第一次使用【数据透视表字段】窗格时，其外观如图 1-25 所示。但是如果不喜欢这种布局方式，那么可以单击【数据透视表字段】窗格中的 按钮，在弹出菜单中选择【数据透视表字段】窗格的其他布局方式。如图 1-17所示为选择【字段节和区域节并排】布局后的【数据透视表字段】窗格。 图1-17 选择【字段节和区域节并排】布局 12人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel

26、2013数据透视表篇 提示：默认情况下，如果单击数据透视表之外的单元格，那么将自动隐藏【数据透视表字段】窗格。只要再次单击数据透视表区域内的单元格，即可重新显示【数据透视表字段】窗格。如果单击数据透视表区域内部后未显示该窗格，那么可以单击功能区中的【分析】 【显示】 【字段列表】 命令。 或右键单击数据透视表内部， 在弹出菜单中选择 【显示字段列表】命令。 1.2.2 布置数据透视表布局 在 Excel 2013 中，默认创建的数据透视表中是不显示任何数据的，可以将【数据透视表字段】窗格中的字段添加到数据透视表中。例如，在如图 1-18所示的数据透视表中，选中【数据透视表字段】窗格中的【季度】

27、 、 【类别】 、 【产地】 、 【品名】和【金额】复选框，将得到默认布局结构的数据透视表。 图1-18 让 Excel自动为数据透视表布局 提示：Excel 2013 对于在数据透视表中添加字段具有比较高的智能化操作。字段中的数据如果表示文本类型，则在选中该字段后会将其自动添加到行区域。而对于字段中包含数值类型的数据，在选中后自动将其添加到数值区域。 虽然 Excel 可以根据字段的数据类型自动对数据透视表进行布局，但是多数情况下这 13人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 种自动化操作得到的汇总报表并不能让用户满意。因此，需要重新排列字

28、段位置，或者从一开始就手动放置字段位置。 1.2.3 重新构建数据透视表布局 如果不满意 Excel 自动对字段进行布局的结果，那么可以手动进行调整，也可以在一个空白数据透视表中直接将所需字段放置到合适的区域中。只要在【数据透视表字段】窗格中将所需字段拖动到下方的任意一个区域即可。也可以在【数据透视表字段】窗格的【选择要添加到报表的字段】列表框中右键单击字段名称，在弹出菜单中选择相应的命令来移动字段位置，如图 1-19所示。 图1-19 通过右键菜单移动字段位置 对于已经移动到【数据透视表字段】窗格下方 4 个区域中的字段，也可以通过将它们拖动到其他区域来移动它们。还可以通过拖动来调整一个区域

29、内各字段的排列顺序，这将影响左侧报表中数据的汇总结果。对于【数据透视表字段】窗格下方 4 个区域内的字段，可以单击字段右侧的下拉按钮，在弹出菜单中选择所需命令对字段进行移动和排序操作。 如果将【数据透视表字段】窗格下方 4 个区域中的字段拖出【数据透视表字段】窗格，则可将其从当前区域中删除，以后还可以重新添加。 提示：也许可能已经注意到，如果将【数据透视表字段】窗格下方 4 个区域中的字段拖出该窗格时，在【数据透视表字段】窗格上方的【选择要添加到报表的字段】列表框中将清除相应字段的选中状态。换句话说，如果不想让某个字段所代表的数据出现在左侧报表中，那么可以直接在【数据透视表字段】窗格的【选择要

30、添加到报表的字段】列表框中去除该字段的选中状态。 除此之外，如果您在构建数据透视表字段布局时，向数据透视表中添加了很多字段，而后又对布局结果很不满意。如果一个一个地将它们删除然后再重新添加，势必会浪费很 14人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 多时间，这时可以单击功能区中的【分析】 【操作】 【清除】按钮并选择【全部清除】命令，快速将数据透视表恢复为初始状态一个空白的数据透视表，之后重新添加字段。 1.2.4 使用以前的方法对数据透视表布局 在Excel 2003中可以直接将字段拖动到数据透视表的适当位置来构建数据透视表布局结构，在 Ex

31、cel 2013 中保留了这个功能。要使用此功能，需要单击功能区中的【分析】 【数据透视表】 【选项】按钮，打开【数据透视表选项】对话框，如图 1-20所示。在【显示】选项卡中选中【经典数据透视表布局（启用网格中的字段拖放） 】复选框即可。 图1-20 启用早期 Excel版本的拖放字段进行布局的方法 1.3 设置数据透视表的格式为了获得最佳的数据透视表显示效果，在创建数据透视表后，一般可能都需要对数据透视表的格式进行一些自定义设置，使数据显示更加人性化。 1.3.1 设置数据透视表的基本外观 当创建数据透视表后，一切都使用 Excel 默认的格式，包括：以压缩的方式显示数据透视表、对行字段进

32、行分类汇总、对行和列进行总计等。对于这些内容的显示，可能有些人并不需要，与其让它白白占用数据透视表空间，不如将它们隐藏起来。在如图 1-21所示 15人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 的数据透视表中，隐藏了分类汇总和总计，将数据透视表显示为表格形式，而且在每个行字段项之间加入了一个空行。 图1-21 自定义数据透视表的外观 要得到上图中的数据透视表布局外观，需要进行以下操作（顺序不分先后） ： 单击功能区中的【设计】 【布局】 【分类汇总】按钮并选择【不显示分类汇总】命令。 单击功能区中的【设计】 【布局】 【总计】按钮并选择【对行和列

33、禁用】命令。 单击功能区中的【设计】 【布局】 【报表布局】按钮并选择【以表格形式显示】命令。 单击功能区中的【设计】 【布局】 【空行】按钮并选择【在每个项目后插入空行】命令。 1.3.2 修改字段名称 当创建数据透视表后，对于数值区域的字段，其名称默认为“求和项：XX” （其中 XX表示数值字段的原始名称） 。如果对于这种命名方式感到很不习惯，那么可以修改字段名称，具体操作如下： （1）右键单击要修改的值字段名称所在列的任意一个单元格（比如要修改“求和项：金额”字段） ，在弹出菜单中选择【值字段设置】命令，打开【值字段设置】对话框，在【自定义名称】文本框中输入新的名称，如图 1-22所示。

34、 16人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 图1-22 修改值字段名称 （2）单击【确定】按钮，数据透视表中所有该值字段将使用新的名称（ “金额总计”字段） ，如图 1-23所示。 图1-23 修改值字段的名称 提示：还可以单击要修改名称的字段所在的单元格，然后输入新的名称并按【Enter 】键，或在公式栏中输入新名称并按【Enter 】键。 可以使用类似方法修改行区域和列区域中的字段名称。与修改值字段名称不同的是，在右键菜单和打开的对话框中名称显示为【字段设置】而非【值字段设置】 。当对行列字段和数值字段进行修改后，观察【数据透视表字段】

35、窗格，可以发现值字段的名称并未改变，而行列字段的名称已改变。在如图 1-24所示中，在数据透视表中重命名的值字段【金额总计】在【数据透视表字段】窗格中并未改变，而重命名后的行字段【产品类别】则进行了同步更改。 17人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 图1-24 修改字段名称在数据透视表和【数据透视表字段】窗格中的变化 尽管在数据透视表中修改了字段的名称，但是在数据源中对应位置上的字段名称是不会改变的。 注意：在修改值字段名称后，如果将其从数据透视表中删除，当以后再次将其添加到数据透视表中时，名称将恢复为默认形式。如果要使用新名称，就只能再

36、次重新命名了。 1.3.3 设置数据的数字格式 为了使数据透视表中的数据更具专业化，可以根据需要为数值设置数字格式。例如，对于表示金额类的数值，可以将其设置为货币数字格式。在如图 1-25所示的数据透视表中，要将【金额】字段中的数据显示为货币格式，具体操作如下： （1）右键单击数据透视表中【金额】字段列中的任意一个单元格，在弹出菜单中选择【值字段设置】命令。 18人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 图1-25 右键单击金额列中的任意单元格并选择【值字段设置】命令 （2）打开【值字段设置】对话框，单击【数字格式】按钮，打开【设置单元格格式】

37、对话框，如图 1-26所示。与普通的【设置单元格格式】对话框类似，只不过此处的【设置单元格格式】对话框仅有一个【数字】选项卡。 图1-26 数据透视表中的【设置单元格格式】对话框 （3）选择好数字格式，然后单击两次【确定】按钮，即可将所有【金额】字段列中的数值设置为货币格式，如图 1-27所示。 19人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 图1-27 将【金额】字段列中的数据设置为货币格式 提示：如果只想设置数据透视表中某一列数据的数字格式，那么需要先选择该列（单击相应的列字母） ， 然后单击功能区中的 【开始】 【单元格】 【格式】 按钮并

38、选择 【设置单元格格式】命令进行设置。也就是说，对于选中的这一列，按设置普通数据格式的方法进行设置。 如果希望选择同一字段的多个列，那么可以将光标移动到该字段上方的位置，当出现向下的黑色箭头 时单击即可（请注意 D列【数量】字段上方的箭头） ，如图 1-28所示。 图1-28 快速选择数据透视表中的多个列 1.3.4 设置数据透视表的样式 为了让数据透视表更加精益求精，可以考虑对已经制作好的报表进行一些外观上的美化处理。单击数据透视表区域内的任意一个单元格，然后可以在【设计】 【数据透视表样式】库的下拉列表中选择一种预置样式来美化数据透视表，如图 1-29所示。 20人民邮电出版社Excel公

39、式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 图1-29 通过样式美化数据透视表 另外，还可以在【设计】 【数据透视表样式选项】组中通过组合选择其中的选项来改变数据透视表的外观。也可以使用用于设置单元格或区域的单元格样式来设置数据透视表，该功能位于【开始】【样式】【单元格样式】库的下拉列表中。 1.3.5 更新数据透视表中的数据 如果对数据透视表数据源区域中的内容进行了修改，默认情况下 Excel 是不会将修改后的内容自动反应到数据透视表中的。为了使数据透视表真实反应数据源的内容，就必须要手动更新数据透视表中的数据。这一操作很简单，右键单击数据透视表中的任意一个单元格，在弹

40、出菜单中选择【刷新】命令即可。也可以单击功能区中的【分析】 【数据】【刷新】按钮完成该操作。 1.3.6 更改数据透视表的数据源 当数据透视表的数据源区域扩大或缩小后，无法使用1.3.6节介绍的刷新操作使数据透视表自动获取最新的数据源区域的范围。这时只有重新选择数据源区域，并使其添加到当前数据透视表中。具体操作如下： （1）单击数据透视表区域中任一单元格，然后单击功能区中的【分析】 【数据】 【更改数据源】按钮。 （2）打开【更改数据透视表数据源】对话框，而且将自动切换到数据源所在的工作表中，选择新的数据源区域并单击【确定】按钮即可，如图 1-30所示。 21人民邮电出版社Excel公式与函数

41、大辞典配套光盘附赠电子书Excel 2013数据透视表篇 图1-30 更改数据透视表的数据源 1.3.7 改变数据的汇总方式 默认情况下，数据透视表中数据的汇总方式为求和。如果希望查看不同种类统计方式下的数据分析报表，那么可以随时改变汇总方式。方法很简单，只要右键单击要改变汇总方式的字段列中的任意一个单元格，在弹出菜单中选择【值汇总依据】命令，然后在子菜单中选择所需的汇总方式即可。如果有多个字段项，那么可以将每个字段项以不同的汇总方式显示。如图 1-31所示为将【金额】以平均值、 【数量】以最大值进行汇总后产生的报表。 图1-31 改变多个字段数据的汇总方式 提示：在汇总数据时，数值字段名称用

42、默认设置较好，因为这样可以清楚了解当前字段中的数据采用的是哪种汇总方式。 如果在【数据汇总依据】子菜单中选择【其他选项】命令，将打开【值字段设置】对话框，在【值汇总方式】选项卡的列表框中可以选择更多的汇总方式。 22人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 技巧： 可以为同一个字段添加多种分类汇总。 右键单击要生成多种汇总方式的行字段，在弹出菜单中选择【字段设置】命令，打开【字段设置】对话框，如图 1-32所示。 在【分类汇总和筛选】 选项卡中选中 【自定义】 单选按钮， 然后按住 【Ctrl 】 键的同时选择多个汇总方式，单击【确定】按钮即可

43、。 图1-32 选择多种汇总方式 1.3.8 改变字段的数据显示方式 默认情况下，在数据透视表中的数据是普通显示方式，也就是说，数据源中的数据是什么样的，在创建的数据透视表中也是按原样显示。但是在需要进行某些数据分析时，这种显示方式无法提供更多的有用信息。因此，Excel 提供了更有分析价值的数据显示方式。除了普通显示方式外，还包括以下 9 种： 差异：以某列数据为基准，将其他列数据与该列数据进行比较。 百分比：将某一项数据表示为另一项数据的百分比。 差异百分比：将某一项数据表示为相对于另一项变化的百分比。 按某一字段汇总：计算连续项的汇总。 占同行数据总和的百分比： 将数据透视表一行数据的总

44、和显示为 100% ， 同行内的每个数据显示为占总和的百分比。 占同列数据总和的百分比： 将数据透视表一列数据的总和显示为 100% ， 同列内的每个数据显示为占总和的百分比。 占总和的百分比： 将数据透视表中所有数据的总和显示为 100% ， 数据透视表中每个数据显示为占总和的百分比。 指数：计算各项的相对重要性。 23人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 例如，希望以第 1 季度销售额为基准，查看其他 3 个季度销售额与第 1 季度销售额的比较，以便于了解后 3 个季度的销售情况是否有所好转。要进行这样的统计，具体操作如下： （1）右键

45、单击数值区域中【第 1 季度】列的任意一个单元格，在弹出菜单中选择【值字段设置】命令，打开【值字段设置】对话框。 （2）选择【值显示方式】选项卡，在【值显示方式】下拉列表中选择【差异】选项。然后在【基本字段】列表框中选择【季度】 ，在【基本项】列表框中选择【第 1 季度】 ，如图 1-33所示。 图1-33 设置【差异】显示方式 （3）单击【确定】按钮，将以第 1季度销售额为基准，显示其他 3 个季度与第 1季度销售额之差，如图 1-34所示。其中正数表示比第 1 季度增长的数值，负数表示比第 1 季度减少的数值。 图1-34 显示差异数据 对于已经设置了数据显示方式的数据透视表， 要恢复为数

46、据的正常显示 （即普通方式） ， 24人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 只要在【值字段设置】对话框的【值显示方式】下拉列表中选择【无计算】选项即可。 1.4 对字段进行分组有时在数据透视表中按照详细的日期显示销售数据，但是可能需要对一个日期段进行统计，比如按照月份或年份来查看阶段日期的数据分析报告。对于类似这种要求，Excel提供了很好的解决措施组合。利用组合，可以实现任意数据的分段统计。组合除了可用于日期字段外，还可用于非日期字段。本部分将介绍对数据透视表数据组合的有关内容。 1.4.1 根据日期自动分组 对于日期型字段数据，要进行组

47、合相当简单。例如，在如图 1-35所示的数据透视表中，显示了 20052007 年的销售数据的一部分。现在想要按年度来查看销售情况，那么就需要对这些日期按年进行分组，具体操作如下： （1）右键单击数据透视表中日期字段所在列的任意一个单元格，然后选择【创建组】命令（也可单击功能区中的【分析】 【分组】 【组选择】或【组字段】按钮） ，打开【组合】对话框。 （2）默认选中【月】 ，如果只想选择【年】 ，那么需要先单击【月】以便取消其选中状态，然后再单击【年】 ，如图 1-36所示。 图1-35 按日期统计的销售数据 图1-36 选择分组依据 （3）单击【确定】按钮，将得到以年为日期单位的数据透视表

48、，如图 1-37所示。 提示：也许您希望对数据透视表中的日期同时按年和月进行分组，那么在【组合】对话框中同时选择【年】和【月】即可。这样将得到类似如图 1-38所示的 数据透视表。 25人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 图1-37 对日期按【年】分组 图1-38 以年和月对日期进行分组 要取消已经完成的分组，右键单击数据透视表中分组字段所在列的任意一个单元格，在弹出菜单中选择【取消组合】命令即可。 1.4.2 根据价格自动分组 可能希望根据商品价格的范围来确定某一价格范围内所包含的具体商品。例如，在如图 1-39所示的数据透视表中，想要

49、统计金额为 04999 和 500010000 之间都有哪些商品，也就是说，希望按价格范围统计商品明细。要实现这个要求，具体操作如下： （1） 右键单击数据透视表 【金额】 字段项中的任意一个单元格， 在弹出菜单中选择 【创建组】命令，打开【组合】对话框。 （2）在【起始于】文本框中输入【0】 ，在【终止于】文本框中输入【10000】 ，在【步长】文本框中输入【5000】 ，如图 1-40所示。 图1-39 通过价格查看商品的数据透视表 图1-40 设置分组条件 26人民邮电出版社Excel公式与函数大辞典配套光盘附赠电子书Excel 2013数据透视表篇 （3）单击【确定】按钮，将按指定的价

50、格范围进行分组，如图 1-41所示。这样便于查看某一价格范围内包含哪些商品。 但是从图 1-41 中可以看出，在 04999 和 500010000 这两个价格范围内，有很多重复的商品，这种结果会扰乱我们提取出正确的信息。因此，我们可以在报表筛选区域添加一些分类字段，以缩小汇总数据的范围，获得更加精确的结果。 如图 1-42所示为在报表筛选区域添加了【产地】和【季度】两个字段，并且经过对这两个字段的筛选，得到一个在第 1季度中产地为北京的商品列表。 图1-41 按价格范围进行分组 图1-42 使用更多报表筛选字段获得精确数据 1.4.3 手动任意分组 对于非日期型数据，将无法让 Excel 自