Excel中一个非常特殊的函数-GETPIVOTDATA
今天介绍Excel中的一个非常特殊的函数，GETPIVOTDATA。
一般在基础性的培训课上，我都不会讲这个函数，即使有人问起，我也会说不用关心它，对于普通人来说，用处不大，完全可以不学它。
不过，在有些特殊场合，这个公式可以起到很大的作用，比如在DASHBOARD中，或者数据报告里。
看完本文的介绍，相信你会发现这个函数的用处的😉
本文内容：
认识GETPIVOTDATA
使用GETPIVOTDATA的8个例子和注意事项
为什么要使用GETPIVOTDATA函数
GETPIVOTDATA的问题
关闭GETPIVOTDATA的自动生成
01
大部分使用Excel的朋友对这个函数都相当陌生。但是，我相信很多人都见过这个函数，大多数人第一次见到这个函数是如下的场景：
假设我们有这个一个透视表：
我们希望引用一月份的总销售额然后求产品的平均值，由于1月份的销售额合计在F5单元格，因此，我们希望的公式是：=F5/5（5个产品）。于是，你像输入其他公式一样，在G5单元格中输入一个：=，然后鼠标点击F5单元格，出乎你的意料，你没有得到：=F5，却得到了一长串内容：
很多人可能会把这一长串内容“吓着”，赶紧就按Esc键，取消输入，甚至都没有来得及看看这一长串内容写的是什么😉。
这一长串内容就是在公式引用透视表的单元格时自动生成的GETPIVOTDATA函数。
这个函数其实很简单的。
先来看名字：
从名字上看，这个公式的作用就是从透视表中获得数据。
它的使用也比较简单。我们看下面的例子：
这里这个公式=GETPIVOTDATA("数量",$A$3,"区域","西区","月",1)使用了6个参数，很简单就可以看出是什么意思：
这个公式的意思就是：
取得——A3单元格所在的透视表中，列字段“区域”中项目为“西区”，行字段“月份”中项目为“1” 的“数量”
实际上，就是通过这些参数唯一限定了透视表区域的一个单元格。
尽管简单，这个公式在使用中还是有一些需要注意的地方，下面我们结合例子来详细介绍一下。
02
例1 最简单的GETPIVOTDATA公式
这个公式中不是除了前面两个参数外，其余的参数都不是必须的。因此，我们可以写这个一个公式：
=GETPIVOTDATA("数量",$A$3)
这个公式的意思是获得A3单元格所在的透视表中的数量。对照这个透视表，你能够指出到底是哪一个值：
这里没有通过行字段和列字段指定范围，那么在这个透视表上用“数量”唯一能够确定的就是右下角那个单元格F8，即1082279。
例2 只指定列字段
理解了上一个例子的，下面这个公式就很容易理解了：
=GETPIVOTDATA("数量",$A$3,"区域","西区")
这个值只要取得透视表中区域为西区的数量值，由于没有行字段的限定，因此，只能是西区的合计值，就E8单元格的值。
同样，可以只指定行字段，而不要列字段。
例3 多个行标签（或列标签的情况）
如果透视表上行标签不止一个，那么要唯一确定透视表的单元格就必须用多个行标签来限定，参数就会更多。
例如这个公式：
=GETPIVOTDATA("数量",$A$3,"日期",2,"区域","东区","产品","芬达橙味200","月",1)
其中，取得值字段是“数量”，列标签是“区域”，项目是“东区”，但是行标签是通过月份，日期，产品3个标签来限定的。
从这个例子也可以看出，除了前两个参数外，其他的参数顺序无所谓，只要能唯一限定一个单元格就好了。
例4 这个公式本质上是个查找
如果你换一个角度看这个公式，实际上这个公式就是个多条件3D的查找公式。
3D指三个维度：
值字段——第一个参数
行字段——行字段名称和行字段项目
列字段-列字段名称和列字段项目
这个公式根据这个3个维度唯一确定一个值。
还是上面的公式，如果我们把透视表变成表格格式，就看得更加清楚了：
实际上，这个公式是在这个表格中查找满足行列条件的那个单元格的值。
例5 透视表布局会影响结果。
由于上面这个原因，不同的透视表布局会影响同一个公式的结果。例如：
现在这个公式的值是1082279。如果我们将筛选改成1月：
公式没变，结果却变了。这个例子再次说明这个公式的机制就是在透视表表格中查找，它并不负责根据源数据计算相应的结果。
例6 如果公式中指定的标签在透视表上不存在怎么办？
假设，原来的公式是：
=GETPIVOTDATA("数量",$A$3,"日期",2,"区域","东区","产品","芬达橙味200","月",1)
现在，我们将透视表中产品从行标签中去掉：
可以看到，现在公式返回了一个错误值，表示引用的区域不存在了！
例8 公式中可以引用单元格
这个很容易理解。例如，公式：
=GETPIVOTDATA("数量",$A$3,"日期",2,"区域","东区","产品","芬达橙味200","月",1)
可以写成：
=GETPIVOTDATA("数量",$A$3,"日期",2,"区域","东区","产品","芬达橙味200","月",G1)
在G1中输入不同月份，就可以取出对应月份的数量。
这样我们就可以很灵活的控制我们需要的内容了。
03
大部分在了解了这个函数后，下一个必然的问题就是：既然都使用了透视表了，为什么还需要使用这个函数？
根据我的经验，有2个原因：
报告布局的要求
在我们做报告或者Dashboard时，对版式布局的要求比较严格。而透视表很多时候不方便进行布局，又可能多出了很多我们不需要展示的数据。但是我们又需要通过透视表来分析汇总数据。这时，我们就可以通过这个函数来取出我们想要的数据按照我们的布局要求放在最终的结果表上。这篇文章介绍了一个这么使用的例子：【Excel模板】年底了，赠送九宫人才盘点模板及模板制作方法。速度的要求
这些值通过函数可以根据源数据算出来，但是会造成计算速度过慢等问题，此时，就可以使用透视表把数据计算出来，然后通过GETPIVOTDATA获得想要的数据，从而提高计算速度。
04
看到这里，相信有很多朋友会发现这个函数有一个问题：
想使用GETPIVOTDATA取得相应的数值，必须保证有一个透视表存在，并且该透视表的布局必须保证要取得的单元格是存在的。
如果你有多个需求，很可能这些需求不能在一个透视表布局上得到满足，就需要做多个透视表。这会给后续的维护带来相当大的复杂性。
这是GETPIVOTDATA这个函数的机制造成的，没有办法避免。如果想规避这种情况，又想利用GETPIVVOTDATA这种方法，可以使用CUBE函数，我会在以后的文章中为大家详细介绍。
05
很多人不知道在透视表中这个函数可以关闭“自动生成”这个函数的功能。选中透视表任意单元格，在“分析”选项卡中，点击最左边的选项，然后去掉“生成GetPivotData”的勾选：
这样，你再采取本文一开始的方法，在公式中点击透视表的数值单元格时，就不会生成GETPIVOTDATA函数，而是直接引用单元格了。
需要说明的是，这个操作并没有“关闭”这个公式的使用，你仍然可以在单元格中直接输入：=GETPIVOTDATA来使用这个函数。