🎁小E还为同学们准备了Excel插件安装包，

👉获取直接在公种号【秋叶Excel】回复【B站】即可~

大家好，我是在研究 Excel 各种功能，方便日常排坑的小E~

我们今天来看看同事小张的问题！

下面是一张以前的员工基础工资表，为了方便统计，小张做了一个数据透视表进行汇总统计。

临近年底，老板跟她说：刚好那天是三八妇女节，给女同胞多发 100 元的福利，男同事增加 10 块钱工资。

01发现问题

会点 Excel 的小张，她点击「数据透视表」任意单元格-选择【数据透视表分析】选项卡-【字段、项目和集】-【计算字段】。

名称为：增加的工资

公式为：

=if(性别="女"，100,10)

点击【确定】按钮，搞定！

咦，什么回事？结果怎么不对？？怎么不管是男是女都是 10 块钱，而且总计的金额也是 10 块钱？？计算字段出 Bug 了？这到底怎么回事呢？

这个问题还没来得及解决，老板又说，还需要求销售表的总额平均值。

小张又做了一个透视表，继续用计算字段，同样的先插入计算字段，名称为总额，公式为：

=单价*数量

拖入到透视表中，值汇总依据为平均值。

不可思议的事情发生了！平均值项的总额居然跟求和项总额一毛一样？

小张百度了很久，但还是没有明白该怎么解决这两个问题，于是她找到了我

02分析问题

小张之所以出现上述问题，其实是由于对计算字段的运算原理不熟悉。

我们先来看看什么是计算字段？

计算字段是指：通过对「数据透视表」中现有的字段执行计算后的得到的新字段。

它可以对「数据透视表」中的现有数据（包括其他的计算字段和计算项生成的数据）进行运算，但无法引用数据透视表之外的工作表数据。

这里需要注意的是：

❶ 计算字段可以使用运算符，表达式，可以使用数据源中已有存在的数据，但是不能使用需要将单元格引用或定义的名称作为参数的工作表函数。

❷ 数据透视表的计算字段是依据字段之和来计算的。数据透视表使用各个值字段分类求和的结果来应用计算字段。即使数值字段的汇总方式别设置为平均值，计算字段也会将其看作求和。

文字理解起来有点复杂，我们用实际例子来说明：

首先，我们以小张的第一个数据透视表为例子进行解释。

名称为：增加的工资

公式为：

=if(性别="女"，100,10)

👉运算逻辑：

❶ 透视表筛选：老板

双击老板增加的工资的单元格，我们可以得到相关的数据源子集。

❷ 对老板的姓名字段进行求和：Sum(姓名)=0

❸ 套入计算字段的公式：

=if(0="女",100,10)

由于 0 不等于女，所以结果为 10。

❹ 值汇总为求和，结果也为 10。

同理其他单元格的值都为 10，总计行中计算字段的计算结果也是一样的道理。

我们来看看小张的第二个数据透视表的计算字段：

名称为总额，公式为：

=单价*数量

👉运算逻辑：

❶ 透视表筛选：老板

双击老板增总额的单元格，我们可以得到相关的数据源子集。

❷ 先对单价和数量字段进行求和：

Sum（单价）=26 

Sum（数量）=43

❸ 套入计算字段的公式：

总额=单价*数量=26*43=1118

❹ 值汇总为平均值，由于总额就只有 1118 一个数据，所以结果依旧是 1118。

同理，其他的单元格的结果也和求和项一样。

到这里，大家应该都知道小张计算字段结果出错的原因了吧。

前面我们知道了计算字段出错的原因，那我们就来说说具体的解决方法。

下面介绍常见的三种方法：

❶ 创建辅助列；

❷ 利用 SQL 添加字段；

❸ 利用 Power Pivot 添加度量值。

▋利用辅助列

既然使用计算字段出错误，那我们可以直接做一个辅助列进行操作，在数据源中添加辅助列，再插入数据透视表，搞定！

公式为：

=IF(B2="女",100,10)

不过如果老板要求不能使用辅助列，这该怎么办？

下面介绍两种做法：

▋利用 SQL 进行添加数据

我们以小张的第一个例子来讲述 SQL 的做法。

❶ 选择数据源。

选择【数据】选项卡-单击【现有连接】。

选择【浏览更多】-选择工作簿的路径。

选择数据源中该工作簿的路径-选择【打开】。

选择【数据$】-点击【确定】。

导入数据-选择【数据透视表】-选择【属性】。

❷ 编写 SQL 语句。

点击属性后-出现连接属性对话框-选择【定义】-在下面进行编写 SQL 语句-选择【确定】。

输入 SQL 语句：

select *,iif(性别="女",100,10) as 增加的工资 from [数据$]

SQL 语句中 iif 函数相当于工作表中的 if 函数。*星号代表所有字段。

❸ 创建数据透视表。

上一步操作后，会返回如下界面-选择【确定】-即可创建数据透视表。

从透视表中，我们可以看到出现了增加的工资的字段，拖拽做成数据透视表。

▋利用 PowerPivot 添加度量值

❶ 加载 PowerPivot 选项卡。Excel2013 以上版本，Powerpivot 已经作为加载项内嵌在 Excel 中，我们只需要在加载项中调用出来。① 转到「文件」>「选项」>「加载项」。

② 在「管理」框中，单击「COM 加载项」>「转到」。

③ 选中「Microsoft Office Power Pivot」框，然后单击「确定」。

④ 现在，功能区出现一个「Power Pivot 选项卡」。

❷ 勾选数据模型。

插入数据透视表，勾选数据模型-【确定】。

❸ 添加度量值。

我们以小张第二个例子为例，说明一下 powerpivot 的做法。

选择【power pivot】选项卡-选择【度量值】-【新建度量值】。

度量值名称为：总额平均值。

Dax 公式为：

=AVERAGEX('区域 1','区域 1'[单价]*'区域 1'[数量])

选择【确定】之后，拖动数据透视表字段，搞定！

下一步，检查一下数据是否正确，双击总额平均值老板对应的单元格，通过验算得知结果为 307，与透视表结果一致。

好啦~本文就到这里结束啦~（才怪）

04总结一下

▋计算字段需要注意

❶ 由于数据透视表的计算是在透视表的缓存中进行的，所以计算字段可以使用已有的数据，不能使用单元格引用和名称引用的数据。

比如只能使用简单的函数运算（如：Sum，If，Text， Aad， Not，Or，Count， Average）；

不能使用需要单元格引用或定义名称的参数的函数（如 Match 等）。

❷ 数据透视表是使用各个值字段分类求和的结果来应用计算字段的。

在计算时是先对字段中相关行的值求和，然后按照公式得到乘积，而不是先把两个字段相关行的值相乘后再对积求和。

即使数值字段的汇总方式别设置为平均值，计算字段也会将其看作求和。

▋解决计算字段出现问题的方法

❶ 添加辅助列（推荐）；

❷ 利用 SQL 语句进行添加需要的字段；

❸ 利用 Power pivot 新建度量值。

关于数据透视表，或者其他有关 Excel 的功能，大家出现过哪些困扰许久的问题？欢迎在文下留言~

🎁小E还为同学们准备了Excel插件安装包，

👉获取直接在公种号【秋叶Excel】回复【B站】即可~