第5章
定制数据透视表的外观

通常情况下，默认创建的数据透视表并不一定满足我们的需要。此时我们可以使用Excel提供的丰富功能来自由定制数据透视表的外观。可以对数据透视表进行定制的选项包括很多，例如，可以改变数据透视表的整体布局，也可以对数据透视表的某个部分进行调整；可以灵活设置数据透视表内元素的显示状态，也可以控制是否显示数据透视表中的总计和汇总；还可以设置数据透视表中数据的格式，使其符合实际所需的格式。本章将全面介绍定制数据透视表外观的方法。

本章要点

❖改变数据透视表的整体布局

❖显示/隐藏数据透视表中的元素

❖设置数据透视表的样式

❖设置总计的显示方式

❖设置汇总的显示方式

❖设置值区域中数据的格式

5.1 改变数据透视表的整体布局

从Excel 2007开始，数据透视表可以使用3种布局形式，而默认创建的数据透视表将使用压缩形式布局，但用户可以在创建数据透视表后随时改变布局形式。Excel 2010延续了Excel 2007的布局类型，仍然提供了3种布局，可以通过单击功能区中的【设计】⇨【布局】⇨【报表布局】按钮，然后在弹出菜单中切换数据透视表的布局形式，如图5-1所示。

5.1.1 使用压缩布局形式

默认情况下，Excel 2010将对创建的数据透视表使用压缩布局形式，这种布局将所有的行字段堆积在一列中。如果在数据透视表中放置了多个行字段，那么采用压缩布局会将字段进行分层处理，显示出类似于资源浏览器中的树状文件结构。通常使用压缩布局可以节省横向空间，而将多个行字段压缩在一列中。

单击功能区中的【设计】⇨【布局】⇨【报表布局】按钮，在弹出菜单中选择【以压缩形式显示】命令，将切换到如图5-2所示的压缩布局形式。这里的行区域包含了“部门”和“职位”两个字段，这两个字段中包含的项依层次展开，每个具体的部门下方包含了不同的职位，这种结构与 Windows 资源浏览器中的树状结构是极其相似的。正是由于这种结构，因此可以展开或折叠某个字段中的项，从而显示或隐藏字段中包含的项。另外，在压缩布局形式中，Excel 会将父字段项的汇总值显示在与父字段同行的右侧，即每组顶部。

提示：有关查看数据透视表明细数据的方法请参考本书第6章。

为了便于描述，我们可以将具有高层次的字段项称为父字段项，而其下属的字段项则称为子字段项，这种称呼的方法是相对而言的。例如，在上图中“财务部”是其下属的“总经理”字段项的父字段项，而“总经理”字段项则是“财务部”字段项的子字段项。

5.1.2 使用大纲布局形式

单击功能区中的【设计】⇨【布局】⇨【报表布局】按钮，在弹出菜单中选择【以大纲形式显示】命令，将切换到如图5-3所示的大纲布局形式，该布局根据数据透视表中放置的行字段数量和位置，将行字段由左到右依次展开排列，最左侧的行字段对应于【数据透视表字段列表】窗格【行标签】列表框中位于顶部的字段，其他字段以此类推。

如果需要将数据透视表中的数据复制到新的位置以便进行其他再利用或处理，那么使用大纲布局将比压缩布局要合适。但是与压缩布局相同，大纲布局也将每个父字段项的汇总值显示在与父字段项同行的右侧，即每组顶部。

5.1.3 使用表格布局形式

单击功能区中的【设计】⇨【布局】⇨【报表布局】按钮，在弹出菜单中选择【以表格形式显示】命令，将切换到如图5-4所示的表格布局形式，该布局与大纲布局类似，也是根据数据透视表中放置的行字段数量和位置，将行字段由左到右依次展开排列。但是表格布局将每个父字段项的汇总值显示在每组的底部。在大多数情况下，使用表格布局可能使数据看上去更直观、更清晰。

提示：可以人为控制数据透视表中汇总出现的位置，具体方法请参考本章5.5.2节。

5.2 显示/隐藏数据透视表中的元素

在默认创建的数据透视表中，只要鼠标位于数据透视表内部，就会自动显示【数据透视表字段列表】窗格、行字段和列字段的标题，以及用于显示或隐藏明细数据的展开和折叠按钮。用户可以自由指定这几类与数据透视表紧密相关的元素的显示状态。

5.2.1 设置【数据透视表字段列表】窗格的显示状态

默认情况下，创建数据透视表时将自动在Excel窗口右侧显示【数据透视表字段列表】窗格。如果对字段完成布局后，只需要查看数据透视表报表结果而不需要修改数据透视表的布局，那么可以将【数据透视表字段列表】窗格隐藏起来，这样可以扩大报表的可视区域。要隐藏【数据透视表字段列表】窗格，只需单击数据透视表内的任意一个单元格，然后单击功能区中的【选项】⇨【显示】⇨【字段列表】按钮，如图5-5所示。

5.2.2 设置行、列字段标题的显示状态

默认情况下，Excel 会在创建的数据透视表中显示行字段和列字段的标题。如果希望隐藏行字段和列字段标题，需要单击数据透视表内的任意一个单元格，然后单击功能区中的【选项】⇨【显示】⇨【字段标题】按钮。如图 5-6 所示为隐藏行、列字段后的数据透视表，采用的是表格布局形式。

5.2.3 设置展开和折叠按钮的显示状态

默认情况下，如果在【数据透视表字段列表】窗格的【行标签】或【列标签】列表框中包含了两个或两个以上的字段，那么在数据透视表报表中就会对不同区域中的多个字段按层次进行排列。

例如，在如图5-7所示的数据透视表中，在【行区域】中包含【部门】和【职位】两个字段，其中【部门】字段是【职位】字段的父字段，也就是说，每个部门都会包含不同的职位，并且在【部门】字段每一项标题的左侧都会包含一个折叠按钮，单击该按钮可以折叠部门中包含的职位。

如果不想显示字段的展开 或折叠 按钮，那么可以将它们隐藏起来。单击数据透视表内的任意一个单元格，然后单击功能区中的【选项】⇨【显示】⇨【+/-按钮】按钮。如图5-8所示为隐藏展开/折叠按钮后的数据透视表。

注意：虽然隐藏了字段的展开/折叠按钮，但是仍可以通过双击字段来显示或隐藏叠字段中包含的子数据项。

5.2.4 自动填充字段中的项

默认情况下，当使用大纲布局或表格布局时，外层字段项只会显示一次。例如，在如图5-9所示的数据透视表中使用了表格布局形式，单元格A6中显示了财务部，其下属的职位包含4个标题，但是“财务部”只在顶部显示一次，而不是在每个职位的左侧都显示。

Excel 2010提供了可以重复显示某一个字段项的功能。例如，如果希望每一个职位的左侧都显示其所属的部门名称，那么无须手工输入部门名称，此时可以单击功能区中的【设计】⇨【布局】⇨【报表布局】按钮，在弹出菜单中选择【重复所有项目标签】命令，如图5-10所示。

如果想恢复原来的显示方式，那么可以单击数据透视表内的任意一个单元格，然后单击功能区中的【设计】⇨【布局】⇨【报表布局】按钮，在弹出菜单中选择【不重复项目标签】命令。

5.3 设置数据透视表的样式

如果希望改变数据透视表的整体外观，那么可以从数据透视表的样式入手。通过样式可以从全局改变数据透视表的外观，主要是对数据透视表中不同区域的填充色和边框线的调整。

5.3.1 使用内置的数据透视表样式

Excel内置了85种数据透视表样式供用户使用，只要单击数据透视表内的任意一个单元格，然后在功能区中的【设计】⇨【数据透视表样式】组中可以打开如图5-11所示的数据透视表样式列表，其中的样式被分为3组：浅色、中等深浅、深色，越靠列表底部的样式越复杂。

直接单击列表中的任何一种样式，即可改变当前数据透视表的外观。如图5-12所示为选择【浅色】组中名为【数据透视表样式浅色8】的样式后的数据透视表。

提示：可以通过功能区中的【设计】⇨【数据透视表样式选项】组中的选项改变数据透视表中的行、列边框与填充效果。例如，如果选中【镶边行】和【镶边列】复选框，那么图5-12中的数据透视表将变为如图5-13所示，Excel将自动加深行和列之间的边框线。

5.3.2 为数据透视表自定义样式

如果觉得内置的数据透视表样式都不太合适，那么可以创建自定义样式。打开数据透视表样式列表，选择底部的【新建数据透视表样式】命令，打开如图5-14所示的【新建数据透视表快速样式】对话框。

在该对话框中，可以由用户任意指定数据透视表的外观。首先在【名称】文本框中输入自定义样式的名称，然后在【表元素】列表框中选择要设置的内容，接着单击【格式】按钮，在打开的【设置单元格格式】对话框中对所选内容进行格式设置，主要包括边框和底纹的设置。

重复上面的步骤，直到设置好【表元素】列表框中所需的所有数据透视表元素。在设置的过程中，【新建数据透视表快速样式】对话框右侧的预览会反映出样式的外观变化，如图5-15所示。如果对某个表元素进行了修改，那么Excel将以黑色字体显示。

单击【确定】按钮，创建自定义样式。当再次打开数据透视表样式列表时，可以在顶部看到新建的自定义样式，并自动将其划分到【自定义】组中，将鼠标指向该样式可以看到数据透视表外观的变化，如图5-16所示。

技巧：如果创建的样式与内置的样式类似，那么可以先复制已有样式，然后对复制的样式进行修改即可。要复制样式，只需右击数据透视表样式列表中的某个样式，在弹出菜单中选择【复制】命令，即可复制该样式。

5.3.3 为数据透视表设置默认的样式

每次创建数据透视表时，都会自动为数据透视表设置默认的样式，这样最初创建的数据透视表就已经具有一定的外观。但如果不喜欢这种默认的样式，那么可以任意指定一种内置或自定义的样式为创建数据透视表时默认的外观，方法很简单，右击数据透视表列表中的一种喜欢的样式，在弹出菜单中选择【设为默认值】命令，如图5-17所示。

如果数据透视表样式正位于【新建数据透视表快速样式】对话框中，那么可以通过选中对话框底部的【设为此文档的默认数据透视表快速样式】复选框，将自定义样式指定为创建数据透视表时使用的默认样式。

5.3.4 通过主题改变数据透视表的外观

主题是指将一组设置好的字体、颜色、外观效果组合到一起，形成多种不同的界面设计方案。可以在多个不同的主题之间切换，从而灵活地改变演示文稿的整体外观。Word 2010、Excel 2010和PowerPoint 2010共享相同的主题。可以使用Excel预置的主题，也可以创建自定义主题。

默认情况下，Excel工作簿会自动使用名为【Office】的主题。要使用其他主题，可以单击功能区中的【页面布局】⇨【主题】⇨【主题】按钮，打开如图5-18所示的主题列表，从中选择任意一种主题。主题的更改会影响工作簿中所有工作表中的形状、图表、数据透视表等内容的外观。

只要对当前主题进行了任何一点改动，保存后即可成为自定义主题，可将自定义主题应用到其他工作簿中。创建自定义主题的具体操作如下。

1 对当前主题进行所需的任何修改，使用功能区【页面布局】⇨【主题】组中的【颜色】、【字体】和【效果】按钮修改主题颜色、主题字体和主题效果。

2 单击功能区中的【页面布局】⇨【主题】⇨【主题】按钮，打开主题列表，选择【保存当前主题】命令。

3 打开如图5-19所示的【保存当前主题】对话框，此时自动定位到【Document Themes】文件夹中，该文件夹就是存放Office主题的位置。

4 在【文件名】文本框中输入自定义主题的名称，然后单击【保存】按钮，即可创建自定义主题。当再次打开主题列表时，就可以在列表顶部看到新建的自定义主题。

5.3.5 使用空白行间隔每组数据

如果感觉数据透视表中的数据过于拥挤，那么可以在每组数据之间插入空白行，以便将各组数据划分得整齐有序。具体操作如下所示。

1 单击数据透视表内的任意一个单元格，激活功能区中的【设计】选项卡。

2 单击功能区中的【设计】⇨【布局】⇨【空行】按钮，在弹出菜单中选择【在每个项目后插入空行】命令，即可在数据透视表中的每组数据之间自动添加一个空行，如图5-20所示。

如果将数据透视表转换为表格布局形式，插入空行后的效果可能会更好，如图5-21所示。

如果需要删除每组之间的空行，只需单击数据透视表内的任意一个单元格，然后单击功能区中的【设计】⇨【布局】⇨【空行】按钮并选择【删除每个项目后的空行】命令。

5.3.6 自动合并外部字段项

对于表格布局形式的数据透视表来说，默认情况下外部行字段项在每组中只会显示一次，可以使用5.2.4节中的方法让Excel自动填充字段项。但有时并不希望重复显示同样的标题，而是希望将标题显示在每个组左侧的中间位置，这就需要通过合并单元格来完成。具体操作如下所示。

1 右击数据透视表内的任意一个单元格，在弹出菜单中选择【数据透视表选项】命令。

2 打开【数据透视表选项】对话框，在【布局和格式】选项卡中选中【合并且居中排列带标签的单元格】复选框，如图5-22所示。

3 单击【确定】按钮，将对数据透视表中的外部行字段和列字段进行单元格的合并处理，如图5-23所示。

注意：无法通过手工合并的方法来合并数据透视表中的行字段，即使用功能区中的【开始】⇨【对齐方式】⇨【合并后居中】按钮无效。

5.4 设置总计的显示方式

默认情况下，创建的数据透视表中包含行和列的总计（如图5-24所示），这样可以直观地反映出数据的总和情况。用户也可以根据需要开启或关闭总计功能。

5.4.1 不在行和列中显示总计

如果不希望显示行数据和列数据的总计，那么可以单击数据透视表内的任意一个单元格，然后单击功能区中的【设计】⇨【布局】⇨【总计】按钮，在弹出菜单中选择【对行和列禁用】命令，如图5-25所示。

禁用行和列总计后的数据透视表如图5-26所示，此时将隐藏总计行和总计列。

5.4.2 在行和列中同时显示总计

默认创建的数据透视表是显示总计行和总计列的，但是如果已经隐藏了总计行和总计列，那么可以通过单击数据透视表内的任意一个单元格，然后单击功能区中的【设计】⇨【布局】⇨【总计】按钮，在弹出菜单中选择【对行和列启用】命令来重新显示总计行和总计列。

5.4.3 只显示总计行

如果只想显示总计行，而不想显示总计列，那么可以单击数据透视表内的任意一个单元格，然后单击功能区中的【设计】⇨【布局】⇨【总计】按钮，在弹出菜单中选择【仅对行启用】命令。此时将只显示总计行（数据透视表的底部），而不显示总计列，如图5-27所示。

5.4.4 只显示总计列

如果只想显示总计列，而不想显示总计行，那么可以单击数据透视表内的任意一个单元格，然后单击功能区中的【设计】⇨【布局】⇨【总计】按钮，在弹出菜单中选择【仅对列启用】命令。此时将只显示总计列（数据透视表的最右侧），而不显示总计行，如图5-28所示。

5.5 设置汇总的显示方式

与总计类似，在创建数据透视表时会自动显示每组数据的汇总值。当然，用户也可以根据实际情况决定是否显示汇总数据。尤其在数据透视表中包含多个行字段时，为了避免多个分类汇总带来的混乱，因此通常会关闭分类汇总的显示。

5.5.1 数据透视表中可用的汇总函数

默认情况下，在创建数据透视表后，Excel会自动对值区域中的数据按求和或计数来进行汇总。如果数据源中的数据是数字，那么将自动进行求和；如果数据源中的数据是文本，那么将自动进行计数。然而，非常值得注意的是，如果数据源中只要有一个单元格是文本或为空，在创建数据透视表后，Excel也会对这些数据进行计数而不是求和运算，这样将导致严重的错误。

然而，除了常用的求和与计数这两种汇总方式外，还可以使用其他汇总方式来得到不同的汇总统计结果，包括平均值、最大值、最小值、乘积、标准偏差等。右击数据透视表中值区域中的任意一个单元格，在弹出菜单中选择【值汇总依据】命令，在弹出的子菜单中可以选择不同的汇总方式，如图5-29所示。

提示：如果希望使用更多的汇总方式，那么可以在图5-29中所示的菜单中选择【其他选项】命令，打开如图5-30所示的【值字段设置】对话框，然后在【选择用于汇总所选字段数据的计算类型】列表框中选择其他汇总函数。

如图5-31所示为统计每个部门的工资最大值的汇总结果。

5.5.2 改变汇总结果的显示位置

在默认创建的数据透视表中自动使用压缩布局形式，而且每个组中的汇总结果自动显示在与外部行字段项同行的右侧。如果需要，可以将汇总结果放置到每组的底部。只需单击数据透视表内的任意一个单元格，然后单击功能区中的【设计】⇨【布局】⇨【分类汇总】按钮，在弹出菜单中选择【在组的底部显示所有分类汇总】命令。这样分类汇总置于每个组的底部，如图5-32所示。

5.5.3 为一个字段设置多种汇总方式

可以为行字段或列字段设置多种分类汇总。默认情况下，Excel总是对行字段或列字段进行求和或计数。可以为一个字段设置多种汇总方式。例如，假设希望查看每个部门工资的最大值、最小值、平均值以及总和，那么可以对部门同时设置4个分类汇总，具体操作如下所示。

1 右击数据透视表中部门字段所在列的任意一个单元格（单元格A15），在弹出菜单中选择【字段设置】命令，如图5-33所示。

2 打开如图5-34所示的【字段设置】对话框，默认情况下，在【分类汇总和筛选】选项卡中自动选中【自动】单选框。

3 选中【自定义】单选框，然后在下方的列表框中通过单击选择一个或多个汇总函数，如图5-35所示。

4 单击【确定】按钮，可以看到在数据透视表中已经对各部门进行了4种汇总分析，分别统计出了每个部门的工资总和、平均工资、最多工资以及最少工资，如图5-36所示。

5.5.4 删除汇总数据

如果不希望显示分类汇总，那么可以单击数据透视表内的任意一个单元格，然后单击功能区中的【设计】⇨【布局】⇨【分类汇总】按钮，在弹出菜单中选择【不显示分类汇总】命令，隐藏所有字段的分类汇总，如图5-37所示。

5.6 设置值区域中数据的格式

使用数据透视表对数据的分析主要在于观察值区域中的数据。为了使数据透视表中的数据能更准确地反映不同类型的数据，可以对数据的格式进行设置，也可以利用条件格式突出显示某些符合条件的特定数据。另外，还可以利用数据透视表本身的功能控制数据中空值和错误值的显示方式。

5.6.1 设置数字格式

与设置普通数据区域的数字格式类似，也可以为数据透视表值区域中的数据设置数字格式。例如，可以将值区域中工资字段的所有数据设置为货币格式，这样可以很明显地了解到哪些数据表示的是货币。

例如，在如图5-38所示的数据透视表中，统计了各部门的工资总和及平均工资，其中C列为工资总和，D列为平均工资。可以发现，C列和D列中的数据只是普通格式，而不是货币格式。另外，D列中某些单元格中的数据包含了很多小数位，这样是不符合要求的。因为对于货币来说，通常只需保留两位小数即可。

因此，需要对C列和D列数据的数字格式进行设置，具体操作如下所示。

1 右击数据透视表中【求和项：工资】字段所在列中的任意一个单元格，在弹出菜单中选择【数字格式】命令。

2 打开如图5-39所示的【设置单元格格式】对话框，在【分类】列表框中选择【货币】选项，然后在右侧自动选中了列表框中的第4项，并且小数位数自动设置为2位。

3 单击【确定】按钮，将看到如图 5-40 所示的效果，【求和项：工资】字段中的数据都显示为货币格式。

4 使用相同的方法设置【平均值项：工资】字段中数据格式，结果如图 5-41所示。

提示：除了使用Excel内置的数字格式来设置值区域中数据的格式外，还可以使用自定义格式。只要在【设置单元格格式】对话框中的左侧列表框中选择【自定义】类型，然后在右侧输入自定义格式代码即可。

5.6.2 设置条件格式

就像对普通数据区域设置条件格式一样，也可以非常方便地在数据透视表中对数据使用条件格式，从而突出显示符合指定条件的数据，这样将更加便于快速找出某些关键数据。例如，对于如图5-42所示的数据透视表，希望快速找出工资总和排名前3位的部门。

要完成上述要求，具体操作如下所示。

1 单击【求和项：工资】字段所在列的任意一个单元格。

2 单击功能区中的【开始】⇨【样式】⇨【条件格式】按钮，在弹出菜单中选择【项目选取规则】⇨【其他规则】命令，如图5-43所示。

3 打开如图5-44所示的【新建格式规则】对话框，选中【所有显示“求和项：工资”值的单元格】单选框，然后将对话框下方【前】的右侧的数字修改为 3，表示前3名。

4 单击【格式】按钮，打开【设置单元格格式】对话框，切换到【填充】选项卡，然后在颜色列表中选择一种颜色，如图5-45所示。

5 单击【确定】按钮，返回【新建格式规则】对话框，可以在对话框底部的【预览】中看到设置的格式效果。单击【确定】按钮，将自动为工资额前3位的工资数值设置灰色底纹，如图5-46所示。

5.6.3 设置空值的显示方式

前面曾经多次讨论过数据源中存在空单元格或文本时，在创建数据透视表后，Excel会对值区域中的数据采用计数而非求和的方式进行汇总。因此，在创建数据透视表前，应该使用0值来填充所有空单元格；或者在创建数据透视表后，手动将错误的计数改为求和。

然而，有时在创建数据透视表后，会发现在数据透视表内包含一些空白单元格。如果不想显示空白单元格，那么可以任何内容来填充数据透视表中的这些空白单元格。具体操作如下所示。

1 右击数据透视表内的任意一个单元格，在弹出菜单中选择【数据透视表选项】命令。

2 打开【数据透视表选项】对话框，切换到【布局和格式】选项卡，选中【对于空单元格，显示】复选框，并在右侧的文本框中输入要代替空单元格而显示的内容，如图5-47所示。此处输入0，表示用0填充数据透视表中的空白单元格。

3 单击【确定】按钮，可以发现Excel将自动使用0填充数据透视表中的空白单元格，如图5-48所示。

5.6.4 设置错误值的显示方式

有时会发现在数据透视表中出现了错误值，这种情况多见于添加了自定义的计算字段后，由于数据透视表中原先包含空单元格，而计算字段中的公式包含了对这些空单元格进行的除法运算，从而导致除数为0而显示错误值，如图5-49所示。

如果不希望这些错误值出现在数据透视表中，那么可以采用与上一节介绍的对于空单元格的相同处理方式，使用一种指定的内容来代替错误值。具体操作如下所示。

1 右击数据透视表内的任意一个单元格，在弹出菜单中选择【数据透视表选项】命令。

2 打开【数据透视表选项】对话框，切换到【布局和格式】选项卡，选中【对于错误值，显示】复选框，并在右侧的文本框中输入要代替错误值而显示的内容，如图5-50所示。此处输入星号“*”，表示用星号“*”代替数据透视表中的错误值。

3 单击【确定】按钮，可以发现Excel将自动使用星号“*”代替数据透视表中的错误值，如图5-51所示。