Excel技巧：如何在Excel透视表中进行字段计算？（例如完成比计算）

QQ群里小伙伴们依然提问热度不减，我们来看看这位的问题。

这是个什么问题？完全看不懂啊。这位小伙伴又补充了一下。

牛闪闪秒懂了，她想在透视表中对数据进行再加工计算。要计算达成率，却发现用除法的后，计算不正确。用除法是对的，但计算不正确就说明是 *** 作有问题。这类问题在透视表中也经常碰到，牛闪闪在这期给大家总结一下。

打开小伙伴提供的数据表，如下：

然后利用透视表进行透视，统计出相同部门任务和完成量的汇总。具体看动图 *** 作：

接下来就是需要将“完成数量”除以“任务量”，算出达成（完成）比率。小伙伴发现无法直接相除？我们来看看问题出在哪里？

你会发现点击任务的单元格数值时候，他会显示一个叫GETPIVOTDATA的函数公式，这个公式默认是开启的。所以你会发现除完向下引用的时候一直是抓取相同的数值，原因也是这个公式。所以有一个解决的方法就是，关掉这个函数的引用。路径是

数据透视表-分析-选项-生成GETPIVOTDATA 。注意看动图 *** 作：

是不是很简单搞定! 这样做虽然也可以达到目的，但这个方法对后期透视表的变化会有影响，比如透视的结构发生了变化，这个时候手动输入除法的公式就会出问题。例如，牛闪闪将日期拽入了部门的下方。倒是统计结构发生变化，结果呢？手工输入的除法公式是不是报错了？很明显这样的做法是十分山寨的。那在透视表中如何实现真正的这种类似达成率的字段统计呢？

真正的套路如下：

在“字段、项目和集”中插入“计算字段”，然后构造出一个虚拟的字段-达成率。

是不是超级简单，这种做的好处显而易见，当透视表的透视结构发生变化后，达成率的字段统计依然存在。甚至可以单独的存在。

总结：很明显计算字段的 *** 作比手工写除法的方法更专业。而GETPIVOTDATA函数，则是专门为抓取透视表中的数据而产生的函数，其目的就是为了实现当数据透视表发生变化的时候，该抓取的值还是自动的抓取。有兴趣的小伙伴可以去研究以下，也可以到职领办公达人课程网 www.nboffice.cn 搜索透视表教程进行相关学习。目前课程5折优惠中。

本例练习素材文件

提问：欺枫哥哥，请问我在建立好数据透视表之后，该怎样对数据透视表中的数据做进一步计算呢？当我复制公式的时候，我得到的是一模一样的结果！

这种状况在Excel 2002之后就开始发生了。非常的让人头大。

下面我分析一下为什么这种状况会发生吧。在下图的例子中，我们已经利用数据透视表将每天的产量数据按年跟月汇总好了。在平时，我们可以利用公式（2019-2018）/2018算出增长率。但是我们却无法像平常一样正常地使用在数据透视表里面的数据。所以，当我们用鼠标把相应的单元格选出，并且输入相应的数学符号，并且当我们按了回车之后，系统就会自动的得出3.31%这个正确的一月份的增长率。可惜的是，当你把公式向下拖拽，得到的每一个月份的增长率都是一样。

当我们进一步观察原因的时候，会发现，这里的公式是=(GETPIVOTDATA("产量",$F$1,"日期",1,"年",2019)-GETPIVOTDATA("产量",$F$1,"日期",1,"年",2018))/GETPIVOTDATA("产量",$F$1,"日期",1,"年",2018)     但是显然，这个并不是你输入的公式。当时你只是简单的用了鼠标去建立了这个公式而已。

而且同样的情况也会在你用键盘的左右移动来选定需要的单元格时发生。

喜欢思考的朋友应该就会问了：什么是GETPIVOTDATA，为什么这个会跑到我的Excel中呢？我猜，这个可能是微软的人希望你发现GETPIVOTDATA，之后去了解，并且学习怎么使用它。再最后，你就会慢慢的爱上它。可惜这个情况是不会发生的。通常的状况是，我们会觉得它很烦。

当然，如果你希望知道GETPIVOTDATA怎么使用的话，请在下方留言。当人数比较多的时候，我会新开一篇文章来讲解它的用法。

下面是几种比较简单的方式让我们再不深入了解GETPIVOTDATA的情况下达到我们的目的。

方法1. 这是最快也是最方便的一种方法：直接输入你要的公式，但是不要用鼠标或者键盘的移动箭头来选择你要的单元格。而是直接的输入=(H3-G3)/G3  这样我们就建立了一个可以复制的公式。

方法2：一劳永逸的永远关闭这个出现GETPIVOTDATA的选项。 *** 作方式是：选择文件/选项/公式/ 去掉在 使用GetPivotData函数获取数据透视表引用

免费赠送的Excel *** 作小技巧：

有一个很常见的关于数据透视表的问题，就是数据透视表里面的总行数会不停的变动。当我们输入下图的公式得到当月占比的时候。在正常的每个月都有数据时是可以正常显示的。

但是，当某一个月份的数据缺失时，或者新增月份时，这个数据就会出错。

具体原因就是，当数据透视表的总行数的位置从第15行变为第14行时，你的公式依然是除以 I15

解决的方式就是用一个Excel的小技巧去让Excel得到I列的最后一个数字。

公式如下图所示，原理就是利用vlookup的模糊搜索的原理，让其返回一个最大值。并且我们可以根据下图2，设置自定义单元格格式。在第三个区域设为空白，这样我们就可以将所有值为0的数字都隐藏起来了。

---