如何在数据透视表中创建计算字段

走过路过不要错过

在数据透视表中创建的计算字段，可以在数据透视表中添加新的数据列，而这个新的数据列是源数据中不存在的数据列。

本文通过两个示例来讲解如何在数据透视表中使用计算字段。

1

计算每种产品的平均单价

本例中介绍如何在数据透视表中插入计算字段，计算每种产品的平均单价。

下图中的A1:D7是产品销售数据，要求据此计算每种产品的平均单价。

使用数据透视表的计算字段计算平均单价的步骤如下：

（1）选中A1:D7，插入数据透视表，先将“产品”字段拖动到行区域。

（2）单击【数据透视表分析】-【字段、项目和集】-【计算字段】

在打开的【插入计算字段】对话框中，【名称】框中输入“平均单价”。

（3）在【公式】框中，删除默认的“0”，选中下方【字段】列表框中的“销售额”，单击【插入字段】，就可以在【公式】框中插入“销售额”字段。输入除号“/”，最后插入“销量”字段。单击确定。

得到的结果如下图所示：

（4）选中数据透视表中“平均单价”列任意单元格，右键单击，选择【数字格式】命令，打开【设置单元格格式】对话框，设置保留两位小数。

单击确定，得到的结果如下图所示：

如何理解计算字段？

（1）计算字段的默认汇总方式是求和。在【插入计算字段】对话框中，设置“平均单价”的公式为“=销售额/销量”，其实就是每种产品的销售额之和除以该产品的销量之和。

如下图所示，将“销售额”和“销量”字段拖动到数据透视表的值区域。品A的销售额为80，销量为7，80/7=11.43，与插入的计算字段“平均单价”的数值相等。

（2）计算字段的默认汇总方式是求和，且这种汇总方式是不能改变的。在计算字段的公式中，使用的其他字段是基于求和函数得到的结果，要注意这种计算方式，以免得到错误的结果。

如下图所示，A1:D7是产品的销量和单价表。要求在数据透视表中统计每种产品的总销售额。

如果插入如下图所示的计算字段，公式=销量*单价，计算的结果是错误的。

使用计算字段计算的销售额结果是错误的，如下图所示：

计算字段“销售额”得到的结果是每种产品的销量之和与单价之和的乘积。例如品A，销量之和为2+5=7，单价之和为10+12=22，那么计算字段计算的销售额就是7*22=154。这个结果显然是错误的，正确的销售额为2*10+5*12=80。

在这个例子中，计算每种产品销售总额的正确做法应该是，先在源数据中添加1列计算销售额，然后在数据透视表中统计每种产品销售总额。

（3）虽然为计算字段定义名称为“平均单价”，但数据透视表会自动添加“求和项:”，在数据透视表中手动修改字段名称即可。

修改前：

修改后：

注意修改后的字段名称“平均单价”后输入空格，如果直接输入“平均单价”，Excel会提示“已有相同数据透视表字段名存在”。

2

计算销售提成

本例中介绍如何在计算字段公式中使用函数计算销售提成。

如下图所示，在数据透视表中计算每个员工的销售提成。当销售额超过400时，提成比例为2%，低于400，提成比例为1%。

在打开的【插入计算字段】对话框中，输入名称“销售提成”，【公式】框中输入公式：=if( 销售额>=400, 销售额*2%, 销售额*1%)

单击确定，得到的结果如下图所示：

在使用IF函数计算销售提成时需注意：

（1）插入计算字段的公式中不能使用单元格引用或定义的名称。如果将公式中的提成比例2%、1%替换为单元格引用J1、J2，Execl就会弹出如下提示。

由于定义的计算字段公式中不能使用单元格引用，因此可以使用的函数也是有限的。

（2）各员工的销售提成之和与总计的销售提成不相等。皮卡球的销售提成为3.2，朱猪侠的销售提成为9.2，两者之和为12.4，而数据透视表的销售提成总计为15.6。这是因为定义的计算字段计算公式也同样应用于总计行，总计销售额为780，超过400，提成比例为2%，总计的销售提成为780*2%=15.6。

这个问题没有直接的解决方法，为了避免误解，可以设置数据透视表不显示总计。如下图所示：

点个在看你最好看