大家好，我是在研究 Excel 各种功能，方便日常排坑的小爽~下面是一张以前的员工基础工资表，为了方便统计，小张做了一个数据透视表进行汇总统计。临近年底，老板跟她说：刚好那天是三八妇女节，给女同胞多发 100 元的福利，男同事增加 10 块钱工资。会点 Excel 的小张，她点击「数据透视表」任意单元格-选择【数据透视表分析】选项卡-【字段、项目和集】-【计算字段】。
公式为：
点击【确定】按钮，搞定！
咦，什么回事？结果怎么不对？？怎么不管是男是女都是 10 块钱，而且总计的金额也是 10 块钱？？计算字段出 Bug 了？这到底怎么回事呢？这个问题还没来得及解决，老板又说，还需要求销售表的总额平均值。小张又做了一个透视表，继续用计算字段，同样的先插入计算字段，名称为总额，公式为：
不可思议的事情发生了！平均值项的总额居然跟求和项总额一毛一样？小张百度了很久，但还是没有明白该怎么解决这两个问题，于是她找到了我。
小张之所以出现上述问题，其实是由于对计算字段的运算原理不熟悉。计算字段是指：通过对「数据透视表」中现有的字段执行计算后的得到的新字段。
它可以对「数据透视表」中的现有数据（包括其他的计算字段和计算项生成的数据）进行运算，但无法引用数据透视表之外的工作表数据。
❶ 计算字段可以使用运算符，表达式，可以使用数据源中已有存在的数据，但是不能使用需要将单元格引用或定义的名称作为参数的工作表函数。
❷ 数据透视表的计算字段是依据字段之和来计算的。数据透视表使用各个值字段分类求和的结果来应用计算字段。即使数值字段的汇总方式别设置为平均值，计算字段也会将其看作求和。
首先，我们以小张的第一个数据透视表为例子进行解释。公式为：
❶ 透视表筛选：老板
双击老板增加的工资的单元格，我们可以得到相关的数据源子集。❸ 套入计算字段的公式：
由于 0 不等于女，所以结果为 10。
同理其他单元格的值都为 10，总计行中计算字段的计算结果也是一样的道理。名称为总额，公式为：
运算逻辑：
❶ 透视表筛选：老板
双击老板增总额的单元格，我们可以得到相关的数据源子集。
❹ 值汇总为平均值，由于总额就只有 1118 一个数据，所以结果依旧是 1118。
到这里，大家应该都知道小张计算字段结果出错的原因了吧。
前面我们知道了计算字段出错的原因，那我们就来说说具体的解决方法。既然使用计算字段出错误，那我们可以直接做一个辅助列进行操作，在数据源中添加辅助列，再插入数据透视表，搞定！公式为：
点击属性后-出现连接属性对话框-选择【定义】-在下面进行编写 SQL 语句-选择【确定】。输入 SQL 语句：
select *,iif(性别="女",100,10) as 增加的工资 from [数据$]SQL 语句中 iif 函数相当于工作表中的 if 函数。*星号代表所有字段。上一步操作后，会返回如下界面-选择【确定】-即可创建数据透视表。从透视表中，我们可以看到出现了增加的工资的字段，拖拽做成数据透视表。Excel2013 以上版本，Powerpivot 已经作为加载项内嵌在 Excel 中，我们只需要在加载项中调用出来。② 在「管理」框中，单击「COM 加载项」>「转到」。
③ 选中「Microsoft Office Power Pivot」框，然后单击「确定」。④ 现在，功能区出现一个「Power Pivot 选项卡」。我们以小张第二个例子为例，说明一下 powerpivot 的做法。选择【power pivot】选项卡-选择【度量值】-【新建度量值】。Dax 公式为：
=AVERAGEX('区域 1','区域 1'[单价]*'区域 1'[数量])
▲ 左右滑动查看
下一步，检查一下数据是否正确，双击总额平均值老板对应的单元格，通过验算得知结果为 307，与透视表结果一致。
总结一下
❶ 由于数据透视表的计算是在透视表的缓存中进行的，所以计算字段可以使用已有的数据，不能使用单元格引用和名称引用的数据。
比如只能使用简单的函数运算（如：Sum，If，Text， Aad， Not，Or，Count， Average）；不能使用需要单元格引用或定义名称的参数的函数（如 Match 等）。❷ 数据透视表是使用各个值字段分类求和的结果来应用计算字段的。
在计算时是先对字段中相关行的值求和，然后按照公式得到乘积，而不是先把两个字段相关行的值相乘后再对积求和。即使数值字段的汇总方式别设置为平均值，计算字段也会将其看作求和。
❶ 添加辅助列（推荐）；
❷ 利用 SQL 语句进行添加需要的字段；
❸ 利用 Power pivot 新建度量值。
关于数据透视表，或者其他有关 Excel 的功能，大家出现过哪些困扰许久的问题？欢迎在后台留言，或许你的问题就是我们后面的选题哦~
↓↓↓