Excel数据透视表某项、总计、父行、父列和父级汇总、列行汇总、差异和字段的百分比等值显示方式的计算

在 Excel 数据透视表中，值显示方式主要用于汇总百分比，主要包括某项的和总计的百分比，行列汇总的百分比，父行、父列和父级汇总的百分比，差异和差异汇总的百分比，按某一字段汇总及其百分比，其中比较抽象的是父行、父列和父级汇总的百分比。

父行汇总的百分比常用于汇总产品不同地区销量占所有地区销量的百分比；父列汇总的百分比常用于汇总每个月的销量占总销量的百分比，它需要先把每个月的数据用多重合并计算汇总到数据透视表中；父级汇总的百分比比较灵活，它可以指定某一字段为父字段，然后计算其子字段及子字段下字段的百分比。

一、总计的百分比与某项的百分比（Excel数据透视表值显示方式）

（一）总计的百分比

1、假如要计算“应发工资”总计的百分比。把鼠标移到“应发工资”字段上，按住左键，把它拖到“∑ 值”列表框中，双击 C3 单元格，打开“值字段设置”窗口，把名称改为“总计的百分比”，单击“确定”；右键 C4，在弹出的菜单中依次选择“值显示方式”→ 总计的百分比，则统计出每个员工和部门的工资分别占总工资的百分比；操作过程步骤，如图1所示：

图1

2、计算方法为：总计的百分比 = 某项的值 / 总计，以计算“财务部”工资总计的百分比为例，它等于 20063.3/44307.3。

（二）某项的百分比

1、假如要计算其它部门的工资占财务部工资的百分比。再把一个“应发工资”字段拖到“∑ 值”列表框中，双击 D4，打开“值字段设置”窗口，把名称改为“某项的百分比”；右键 D4，在弹出的菜单中依次选择“值显示方式”→ 百分比，在打开的窗口中，“基本字段”选择“部门”，“基本项”选择“财务部”，单击“确定”，则计算出其它部门（行政部和技术部）工资占财务部工资的百分比；操作过程步骤，如图2所示：

图2

2、计算方法为：某项的百分比 = 某项的值 / 基础项的值。例如财务部的工资都是百分之百，因为它是基础项，是自己与自己比，行政部为的 82.67%，它是用行政部的总工资比财务部的总工资（即 16586/20063.3）计算而来，技术部的 38.17% 宜是这样计算而来。

总计的百分比与某项的百分比的区别：前者用某一项与总计相比，例如财务部的总工资（或某个员工的工资）与总工资相比；后者用某一项与基本项（即指定项）相比，例如演示中的行政部的总工资与指定项财务部的总工资相比。

二、列汇总与行汇总的百分比（Excel数据透视表值显示方式）

（一）列汇总的百分比

1、假如要求各类与各件服装1月销量占所有服装1月总销量的百分比。像上面演示一样，再把一个“1月销量”字段拖到“∑ 值”的列表框中，单击 C3，在打开的窗口中把名称改为“列汇总的百分比”，单击“确定”；右键 C3，在弹出的菜单中选择“值显示方式”→ 列汇总的百分比，则统计出每类与每件服装1月销量占所有服装1月总销量的百分比；操作过程步骤，如图3所示：

图3

2、计算方法为：列汇总的百分比 = 某项的值 / 列总计的值。以计算“T恤”列汇总的百分比为例，它等于 435/6198。

（二）行汇总的百分比

行汇总的百分比仅在一列中的一行计算，即用一个单元格的值与自身相比，结果全为 100%，它不能把一行中的每列进行汇总后再计算，因此求得的结果不是一行各列汇总的百分比；如果求一行各列汇总后的百分比，需要用多重合并计算生成数据透视表，具体请参考“父列汇总的百分比”。

三、父行、父列和父级汇总的百分比（Excel数据透视表值显示方式）

（一）父行汇总的百分比

1、假如要汇总服装地区销量的父行汇总的百分比。把鼠标移到字段“销量”上，按住左键并拖到“∑ 值”的列表框中，双击“求和项:销量（件）”所在的单元格 C3，在打开的窗口中，把名称改为“父行汇总百分比”，单击“确定”；右键 C3，在弹出的菜单中依次选择“值显示方式”→ 父行汇总的百分比，则汇总出每件衣服和每个城市的父行汇总的百分比；操作过程步骤，如图4所示：

图4

2、计算方法：

A、父行汇总的百分比 = 某项的值 / 行上父项的值。“行上父项的值”对于不同级别的行有不同的含意，就拿演示中的行来说，它分为四类，一类为每件服装所在的行，第二类为服装销售城市所在的行，第三类为“地区”所在的行，第四类为“总计”所在的行，第二类是第一类的父行（如“广州”是“白衬衫”的父行），第三类是第二类的父行（如“华南”是“广州”的父行），第四类是第三类的父行（如“总计”是“华南”的父行）。

B、具体计算实例。“白衬衫”的父行汇总的百分比 = B6（652）/ B5（2717），“广州”的父行汇总的百分比 = B5（2717）/ B4（3703）,“华南”的父行汇总的百分比 = B4（3703）/ B18（5752）。提示：计算式中的 B 其实是 C，为便于查看具体数值，因此用 B 代替，以下同。

（二）父列汇总的百分比

1、有一个 4 到 6 月的服装销量数据透视表，要求计算父列汇总的百分比。右键数据区的任意一个单元格（如 C5），在弹出的菜单中依次选择“值显示方式””→ 父列汇总的百分比，则计算出每件衣服在 4 到 6 月的销量百分比；操作过程步骤，如图5所示：

图5

提示：演示中的“4 到 6 月的服装销量数据透视表”是由4月、5月和6月三个服装销量表用多重合并计算生成，每个表格结构相同，都由字段“产品名称和销量”组成，具体怎么合并请看《Excel多重合并计算数据区域用于合并多个表格到数据透视表，含把地区和城市的销量与营业额用多页字段合并》一文。数据透视表中的列（4月、5月和6月）是原表格对应月份的销量。

2、计算方法：

A、父列汇总的百分比 = 某项的值 / 列上父项的值。例如“白衬衫”4月销量 422（即 C5 的值）为某项的值，“白衬衫”的“总计”1588（即 F5 的值）为“列上父项的值”。

2、计算实例。“白衬衫”4月销量的父列汇总的百分比 = C5/F5，即 422/1588 = 26.57%；“白衬衫”5月销量的父列汇总的百分比 =D5/F5，即 485/1588 = 30.54%。

（三）父级汇总的百分比

1、同样以汇总服装地区销量的父级汇总百分比为例。上面已经把两个“销量”字段拖到“∑ 值”列表框中，一个用于查看数值，另一个用于汇总；右键“父级汇总百分比”所在的单元格 C3，在弹出的菜单中依次选择“值显示方式”→ 父级汇总的百分比，打开“值显示方式（父级汇总百分比）”窗口，“基本字段”选择“地区”，单击“确定”或按回车，则汇总出每件衣服和每个城市的父级汇总的百分比；操作过程步骤，如图6所示：

图6

2、计算方法：

A、父级汇总的百分比 = 某项的值 / 指定的“基本字段”的值。“某项的值”为子级，指定的“基本字段”的值为父级，例如演示中的每件衣服（如“白衬衫”）和城市（如“广州”）为子级，地区（如“华南”）为父级。

B、具体计算实例。“白衬衫”的父级汇总的百分比 =B6（652）/ B4（3703），“广州”的父级汇总的百分比 =B5（2717）/ B4（3703）。

四、差异与差异百分比（Excel数据透视表值显示方式）

（一）差异

1、假如以计算服装地区与城市的销量为例。上面的实例已把三个“销量”字段拖到“∑ 值”列表框中，一个用于查看数值，第二个用于计算地区差异，第三个用于计算城市差异；右键“地区差异”所在的单元格 C3，在弹出的菜单中依次选择“值显示方式”→ 差异，打开“值显示方式（地区差异）”窗口，“基本字段”选择“地区”，“基本项”选择“华南”，单击“确定”，则计算出服装在“华中”的销量与在“华南”的销量的差异 -1654；重复上面操作，“基本字段”选择“城市”，“基本项”选择“广州”，单击“确定”，则只计算出服装在“华南”地区的“深圳”的销量与“广州”的销量的差异；操作过程步骤，如图7所示：

图7

2、计算方法：

A、差异 = 某项的值 - 指定“基本字段”的值。例如演示中计算地区的差异，以“华南”为基本字段，其它地区（如“华中”）为某项。

B、计算实例。服装在华中与华南的销量差异 = B13（2049）- B4（3703）= -1654；服装在深圳与广州的销量差异 = B10（986）- B5（2717）= -1731。

（二）差异的百分比

1、以把上面的差异改为差异的百分比为例。右键“地区差异百分比”所在的单元格 C3，在弹出的菜单中，依次选择“值显示方式”→ 差异的百分比，打开“值显示方式（地区差异百分比）”窗口，“基本字段”选择“地区”，“基本项”选择“华南”，单击“确定”，则计算出服装在“华中”的销量与在“华南”的销量的差异的百分比 -44.67%；重复上面操作计算出服装在“华南”地区的“深圳”的销量与“广州”的销量的差异百分比；操作过程步骤，如图8所示：

图8

2、计算方法：

差异的百分比 = （某项的值 - 指定“基本字段”的值）/ 指定“基本字段”的值。例如计算服装在华中与华南的销量差异的百分比，计算式为： (B13（2049）- B4（3703）)/ B4（3703）= -1654/3703 = -44.67%；服装在深圳与广州的销量差异的百分比 = （B10（986）- B5（2717））/ B5（2717）= -63.71%。

五、按某一字段汇总和按某一字段汇总的百分比（Excel数据透视表值显示方式）

（一）按某一字段汇总

1、假如以按服装在地区和城市销量汇总为例。按上面的操作方法把两个“销量”字段拖到“∑ 值”列表框中，并把它们分别改名为“销量按地区汇总”和“销量按城市汇总”，右键“销量按地区汇总”，在弹出的菜单中依次选择“值显示方式”→ 按某一字段汇总，在打开的窗口中，“基本字段”保持默认的“地区”，单击“确定”，则返回以“地区”为基本字段的汇总；重复上面步骤，“基本字段”选择“城市”，则返回以“城市”为基本字段的汇总；操作过程步骤，如图9所示：

图9

2、从演示中可知，当以“地区”为基本字段，则汇总“地区”及其下的字段，例如汇总了华南、华中及它们下面的城市销量；但不会汇总“地区”以上的字段，例如没有汇总“总计”。当以“城市”为基本字段，只汇总到城市，它上面的“地区”没有汇总，并且城市汇总是累加的汇总，例如“深圳”的汇总结果为 3703，它是在广州的销量 + 在深圳的销量计算而来。

（二）按某一字段汇总的百分比

1、以把上面的“按某一字段汇总”改为“按某一字段汇总的百分比”为例。右键“销量按地区汇总百分比”，在弹出的菜单中，依次选择“值显示方式”→ 按某一字段汇总的百分比，在打开的窗口中，“基本字段”保持默认的“地区”，单击“确定”，则计算出以“地区”为基本字段的汇总百分比；重复以上操作，“基本字段”选择“城市”，则计算出以“城市”为基本字段的汇总百分比；操作过程步骤，如图10所示：

图10

2、从演示中可知，当以“地区”为基本字段，华南地区汇总的百分比计算式为=B4/(B4+B13)，即 3703/(3703 + 2049) = 64.38%，华中地区汇总的百分比计算式为 =(B4+B13)/(B4+B13)，即 (3703 + 2049)/(3703 + 2049) = 100%；以“城市”为基本字段也是一样的计算方法。