8.3　自定义数据透视表的值显示方式

如果【值字段设置】对话框内的汇总方式仍然不能满足需求，Excel还允许选择更多的计算方式。利用此功能，可以显示数据透视表的数据区域中每项占同行或同列数据总和的百分比，或显示每个数值占总和的百分比等。

在Excel 2010数据透视表中，“值显示方式”较之Excel 2007及以前版本增加了更多的计算功能，如“父行汇总的百分比”、“父列汇总的百分比”、“父级汇总的百分比”、“按某一字段汇总的百分比”、“升序排列”和“降序排列”。“值显示方式”功能更易于查找和使用，指定要作为计算依据的字段或项目也更加容易。

8.3.1　数据透视表自定义值显示方式描述

有关数据透视表自定义计算功能的简要说明，如表8-1所示。

表8-1　自定义计算功能描述

8.3.2　“全部汇总百分比”值显示方式

利用“全部汇总百分比”值显示方式，可以得到数据透视表内每一个数据点所占总和比重的报表。

示例8.4　计算各地区、各产品占销售总额百分比

要对如图8-22所示数据透视表进行各地区、各产品销售额占销售总额百分比的分析，请参照以下步骤进行。

图8-22　销售统计表

步　骤1

在数据透视表“求和项：销售金额”字段上单击鼠标右键，在弹出的快捷菜单中选择【值字段设置】命令，在弹出的【值字段设置】对话框中单击【值显示方式】选项卡，如图8-23所示。

图8-23　调出【值、字段设置】对话框

步　骤2

单击【值显示方式】的下拉按钮，在下拉列表中选择“全部汇总百分比”值显示方式，单击【确定】按钮关闭对话框，如图8-24所示。

图8-24　设置数据透视表“全部汇总百分比”计算

步　骤3

完成设置后如图8-25所示。

图8-25　各地区、各产品占销售总额百分比

提示

这样设置的目的就是要将各个“品名”在各个销售地区的销售金额占所有“品名”和“销售地区”销售金额总计的比重显示出来，例如，“按摩椅”在“北京”销售比重（2.05%）=“按摩椅”在“北京”销售金额（139 200）／销售金额总计（6 775 900）。

8.3.3　“列汇总的百分比”值显示方式

利用“列汇总的百分比”值显示方式，可以在每列数据汇总的基础上得到各个数据项所占比重的报表。

示例8.5　计算各地区销售总额百分比

如果希望在如图8-26所示数据透视表的基础上，计算各销售地区的销售构成比率，请参照以下步骤进行。

图8-26　销售统计表

步　骤1

将【数据透视表字段列表】对话框内的“销售金额”字段再次添加进【Σ数值】区域，同时，数据透视表内将会增加一个“求和项：销售金额2”字段，如图8-27所示。

图8-27　向数据透视表内添加字段

步　骤2

在数据透视表“求和项：销售金额2”字段上单击鼠标右键，在弹出的快捷菜单中选择【值字段设置】命令，在弹出的【值字段设置】对话框中单击【值显示方式】选项卡，如图8-28所示。

图8-28　调出【值字段设置】对话框

步　骤3

单击【值显示方式】的下拉按钮，在下拉列表中选择“列汇总的百分比”值显示方式，单击【确定】按钮，关闭对话框，如图8-29所示。

图8-29　设置数据透视表“列汇总的百分比”计算

步　骤4

将“求和项：销售金额2”字段名称更改为“销售构成比率％”，完成设置后如图8-30所示。

图8-30　各地区销售总额百分比

提示

这样设置的目的就是要将各个销售地区的销售金额占所有销售地区的销售金额总计的百分比显示出来，例如，“北京”（39.54%）=2 678 900／6 775 900。

8.3.4　“行汇总的百分比”值显示方式

利用“行汇总的百分比”值显示方式，可以得到组成每一行的各个数据占行总计的比率报表。

示例8.6　同一地区内不同产品的销售构成比率

如果希望在如图8-31所示数据透视表的基础上，计算每个销售地区内不同品名产品的销售构成比率，请参照以下步骤进行。

图8-31　销售统计表

步　骤1

在数据透视表的“求和项：销售金额”字段上单击鼠标右键，在弹出的快捷菜单中选择【值字段设置】命令，在弹出的【值字段设置】对话框中单击【值显示方式】选项卡，如图8-32所示。

图8-32　调出【值字段设置】对话框

步　骤2

单击【值显示方式】的下拉按钮，在下拉列表中选择“行汇总的百分比”值显示方式，单击“确定”按钮关闭对话框，如图8-33所示。

步　骤3

完成设置后，如图8-34所示。

图8-33　设置数据透视表“行汇总的百分比”计算

图8-34　同一地区内不同产品的销售比率统计

提示

这样设置的目的就是要将各个销售地区各个品名的销售金额所占该销售地区总体销售金额的百分比显示出来，例如，北京地区销售“按摩椅”的百分比（5.20%）=北京地区“按摩椅”的销售金额（139 200）／北京地区所有产品的总销售金额（2 678 900）。

8.3.5　“百分比”值显示方式

通过“百分比”值显示方式对某一固定基本字段的基本项的对比，可以得到完成率报表。

示例8.7　利用百分比选项测定员工工时完成率

如果希望在如图8-35所示的数据透视表基础上，将每位员工的“工时数量”与所在小组的“定额工时”对比，进行员工工时完成率的统计，请参照以下步骤。

图8-35　员工工时统计表

步　骤1

在数据透视表的“求和项：工时数量”字段上单击鼠标右键，在弹出的快捷菜单中选择【值字段设置】命令，在弹出的【值字段设置】对话框中单击【值显示方式】选项卡，如图8-36所示。

图8-36　数据透视表的“值显示方式”

步　骤2

单击【值显示方式】的下拉按钮，在下拉列表中选择“百分比”值显示方式，【基本字段】选择“员工姓名”，【基本项】中选择“定额工时”，单击【确定】按钮关闭对话框，如图8-37所示。

图8-37　设置数据透视表“百分比”计算

步　骤3

完成设置后如图8-38所示。

图8-38　测定员工工时完成率的数据透视表

提示

这样设置的目的就是要在字段“员工姓名”的数值区域内显示出每位员工的工时数量与“定额工时”的比率，例如，“安俞帆”（98.58%）=“安俞帆”工时数量（10 844）／第一小组“定额工时”数量（11 000）。

8.3.6　“父行汇总的百分比”数据显示方式

如图8-39所示的数据透视表是在Excel 2007版本中建立的数据透视表，其中有“北京”、“杭州”、“山东”和“上海”4个销售地区，每个销售地区又分别销售“按摩椅”、“微波炉”、显示器”和“液晶电视”4种品名的商品。

如果用户希望得到各种品名的商品占每个销售地区总量的百分比，如“按摩椅”占“北京”地区销售总量的百分比，Excel 2007中的数据透视表没有提供一个直接的解决方案，必须借助在数据源中添加复杂的函数辅助列来实现。

图8-39　Excel 2010以前版本“占同列数据总和的百分比”的数据显示方式

示例8.8　各商品占每个销售地区总量的百分比

在Excel 2010中利用新增的“父行汇总的百分比”数据显示方式可以轻松实现，具体方法请参照以下步骤。

步　骤1

在字段“求和项：销售金额”上单击鼠标右键，在弹出的快捷菜单中选择【值字段设置】命令，弹出【值字段设置】对话框，如图8-40所示。

图8-40　调出【值字段设置】对话框

步　骤2

在【值字段设置】对话框内单击【值显示方式】选项卡，单击【值显示方式】的下拉按钮，在列表框中选择【父行汇总的百分比】显示方式，单击【确定】按钮完成设置，如图8-41所示。

图8-41　“父行汇总的百分比”数据显示方式

如果数据透视表“品名”字段的位置发生改变，如图8-42所示，要达到各商品占每个销售地区总量的百分比的显示效果，则需要运用“父列汇总的百分比”数据显示方式，具体方法请参照以下步骤。

图8-42　“品名”字段位置变化后的数据透视表

在字段“求和项：销售金额”上单击鼠标右键，在弹出的快捷菜单中选择【值显示方式】→【父列汇总的百分比】显示方式，如图8-43所示。

图8-43　“品名”字段位置变化后的数据透视表

最终完成的效果，如图8-44所示。

图8-44　“父列汇总的百分比”数据显示方式

8.3.7　“父级汇总的百分比”数据显示方式

利用“父级汇总的百分比”值显示方式可以通过某一基本字段的基本项和该字段的父级汇总项的对比，得到构成率报表。

图8-45　销售报表

如果希望在如图8-45所示的销售报表基础上，得到每位销售人员在不同地区的销售商品的构成，请参照以下步骤。

示例8.9　销售人员在不同销售地区的业务构成

步　骤1

在数据透视表“数值区域”的任意单元格上（如C5）单击鼠标右键，在弹出的快捷菜单中依次选择【值显示方式】→【父级汇总的百分比】显示方式，弹出【值显示方式】对话框，如图8-46所示。

图8-46　调出【值显示方式】对话框

步　骤2

单击【值显示方式】对话框中【基本字段】的下拉按钮，在弹出的下拉列表中选择“销售地区”字段，最后单击【确定】按钮关闭对话框完成设置，如图8-47所示。

图8-47　“父级汇总的百分比”数据显示方式

8.3.8　“差异”值显示方式

每当一个会计年度结束之后，各个公司都想知道制定的费用预算额与实际发生额的差距到底有多大，以便于来年在费用预算中能够做出相应的调整。利用“差异”显示方式可以在数据透视表中的原数值区域快速显示出费用预算额或实际发生额的超支或者节约水平。

示例8.10　显示费用预算和实际发生额的差异

如果希望对如图8-48所示的数据透视表进行差异计算，请参照以下步骤。

图8-48　预算额与实际发生额汇总表

步　骤1

在数据透视表“求和项：金额”字段上单击鼠标右键，在弹出的快捷菜单中选择【值字段设置】命令，在弹出的【值字段设置】对话框中单击【值显示方式】选项卡，如图8-49所示。

图8-49　数据透视表的“值显示方式”

步　骤2

单击【值显示方式】的下拉按钮，在下拉列表中选择“差异”值显示方式，【基本字段】选择“费用属性”，【基本项】中选择“实际发生额”，单击【确定】按钮关闭对话框，如图8-50所示。

图8-50　设置数据透视表“差异”计算

提示

在【基本项】中选择“实际发生额”，差异计算就会在“预算额”字段数值区域显示“预算额”-“实际发生额”的计算结果，体现预算额编制水平，例如，“07月办公用品”（-2）=“预算额”（1500）-“实际发生额”（1502）。

步　骤3

完成设置后如图8-51所示。

图8-51　体现预算额与实际发生额差异计算的数据透视表

如果步骤2中的【基本项】选择“预算额”，差异计算就会在“实际发生额”字段数值区域显示“实际发生额”-“预算额”的计算结果，体现实际支出水平，如图8-52所示。

图8-52　体现实际发生额与预算额差异计算的数据透视表

8.3.9　“差异百分比”值显示方式

利用“差异百分比”值显示方式，可以求得按照某年度为标准的逐年采购价格的变化趋势，从而得到价格变化信息，及时调整采购策略。

示例8.11　利用差异百分比选项追踪采购价格变化趋势

如果希望对如图8-53所示的数据透视表进行差异百分比计算，请参照以下步骤。

图8-53　历年采购价格统计表

步　骤1

在数据透视表“求和项：单价”字段上单击鼠标右键，在弹出的快捷菜单中选择【值字段设置】命令，在弹出的【值字段设置】对话框中单击【值显示方式】选项卡，如图8-54所示。

图8-54　调出【值字段设置】对话框

步　骤2

单击【值显示方式】的下拉按钮，在下拉列表中选择“差异百分比”值显示方式，【基本字段】选择“采购年份”，【基本项】选择“2009”，单击【确定】按钮关闭对话框，如图8-55所示。

图8-55　设置数据透视表“差异百分比”计算

步　骤3

完成设置后如图8-56所示。

图8-56　历年采购价格的变化趋势

提示

这样设置的目的就是要在数值区域内显示出各个采购年份的采购单价与目标年度“2009”的采购单价之间的增减比率，例如，采购年份“2010”物料品名“储气罐”3.26%=（2010年储气罐的单价“950”-2009年储气罐的单价“920”）/2009年储气罐的单价“920”。

8.3.10　“按某一字段汇总”数据显示方式

利用“按某一字段汇总”的数据显示方式，可以在现金流水账中对余额按照日期字段汇总。

示例8.12　制作现金流水账簿

如果希望对如图8-57所示的数据透视表中的余额按照日期进行累计汇总可以参照如下步骤。

图8-57　现金流水账

步　骤1

在数据透视表“求和项：余额”字段上单击鼠标右键，在弹出的快捷菜单中依次单击【值显示方式】→【按某一字段汇总】，弹出【值显示方式】对话框，如图8-58所示。

图8-58　调出【值显示方式】对话框

步　骤2

【值显示方式】对话框内的【基本字段】保持默认的“日期”字段不变，最后单击【确定】按钮完成设置，如图8-59示。

图8-59　设置数据透视表“按某一字段汇总”计算

如果用户希望对汇总字段以百分比的形式显示，则可以使用“按某一字段汇总的百分比”的数据显示方式。

8.3.11　“升序排列”值显示方式

利用“升序排列”的数据显示方式，可以得到销售人员的业绩排名。

示例8.13　销售人员业绩排名

如果希望对如图8-60所示数据透视表中的销售金额按照销售人员进行排名，可以参照如下步骤。

图8-60　销售人员业绩统计表

步　骤1

在数据透视表标题“求和项：销售金额”上单击鼠标右键，在弹出的快捷菜单中依次单击【值显示方式】→【升序排列】命令，弹出【值显示方式】对话框，如图8-61所示。

图8-61　调出【值显示方式】对话框

步　骤2

【值显示方式】对话框内中的【基本字段】保持默认的“销售人员”字段不变，最后单击【确定】按钮完成设置，如图8-62所示。

步　骤3

单击B4单元格，在【数据透视表工具】的【选项】选项卡中单击【排序】按钮，弹出【按值排序】对话框，如图8-63所示。

图8-62　设置数据透视表“升序排列”计算

图8-63　设置排序

步　骤4

单击【按值排序】对话框中的【确定】按钮，完成后的结果如图8-64所示。

图8-64　设置数据透视表“升序排列”计算

如果用户希望将得到的销售人员业绩排名进行降序排列显示，则可以使用“降序排列”的数据显不方式。

8.3.12　 “指数”值显示方式

利用“指数”值显示方式，可以对数据透视表内某一列数据的相对重要性进行跟踪。

示例8.14　各销售地区的产品短缺影响程度分析

如果希望对如图8-65所示的销售报表进行销售指数分析，确定何种产品在不同的销售地区中最具重要性，请参照以下步骤进行。

图8-65　将要进行销售指数分析的数据透视表

步　骤1

在数据透视表“求和项：销售金额”字段上单击鼠标右键，在弹出的快捷菜单中选择【值字段设置】命令，在弹出的【值字段设置】对话框中单击【值显示方式】选项卡，如图8-66所示。

图8-66　调出【值字段设置】对话框

步　骤2

单击【值显示方式】的下拉按钮，在下拉列表中选择“指数”值显示方式，单击【确定】按钮关闭对话框，如图8-67所示。

图8-67　设置数据透视表“指数”计算

步　骤3

对数据透视表的值字段区域内的数值进行单元格格式设置，完成后如图8-68所示。

图8-68　确定产品在销售地区中的相对重要性

提示

以上示例中“微波炉销售指数”杭州地区1.57为最高，说明微波炉产品的销售在杭州地区的重要性很高，如果该产品在杭州地区发生短缺，将会影响到整个微波炉市场的销售。

杭州地区微波炉指数1.57=（（杭州地区微波炉销售金额68，500)×(总体汇总之和7，201，700））／（（行汇总247，000）×（列汇总1，269，500））

“跑步机销售指数”上海地区2.02为最高，说明跑步机产品的销售在上海地区的重要性很高，如果该产品在上海地区发生短缺，将会影响到整个跑步机市场的销售。上海地区跑步机指数2.02=（（上海地区跑步机销售金额391，600）×（总体汇总之和7，201，700））／（（行汇总1，313，400）×（列汇总1，063，600））

8.3.13　修改和删除自定义数据显示方式

如果用户要修改已经设置好的自定义值显示方式，只需在【值显示方式】的下拉列表中选择其他的值显示方式即可。

如果在【值显示方式】下拉列表中选择了“无计算”值显示方式，将回到数据透视表默认的值显示状态，也就是删除了已经设置的自定义值显示方式。