深度解析！透视表专有函数：GETPIVOTDATA
作者：壹仟伍佰万来源：部落窝教育发布时间：2019-12-31 19:34:55点击：14578
编按：
哈喽，大家好！今天是部落窝函数课堂的第8课，我们将一起来认识GETPIVOTDATA函数！不知道小伙伴们还记不记得这个函数。没错！它就是我们前段时间发布的《受够加班煎熬，我整理出10条职场人士最常用的透视表技巧！（下篇）》教程中，所提到的透视表的专有函数。
GETPIVOTDATA函数的主要功能是返回透视表中的可见数据。需要在“数据透视表工具”栏下的“分析”选项卡下，点击“选项”，勾选“生成GetPivotData”才能使用GETPIVOTDATA函数。
先来看一下函数的结构：
GETPIVOTDATA(data_field,pivot_table,[field1,item1,field2,item2], ...)
data_field：必须是透视表中的值字段名称。格式必须是以成对的英文双引号输入的文本字符串或是经转化为文本类型的单元格引用。
pivot_table：对数据透视表中任何单元格或单元格区域的引用，该参数主要用于确认要检索数据的数据透视表。
[field1,item1,field2,item2,...]：一组或多组的“行/列字段名称”和项目名称。主要用于描述获取数据的条件，该参数可以是单元格引用或者常量文本字符串。最多可以有126组。
为了便于大家理解，我们可以根据上述信息，将函数结构翻译成大白话：
=GETPIVOTDATA("透视表的值字段名称",数据透视表中任意单元格,"透视表的字段名称1",条件1,"透视表的字段名称2",条件2)
（注意：除日期、数字和引用单元格外，参数都必须加上英文双引号）
看到公式这么长，估计很多小伙伴都晕了，其实只要在单元格中输入“=”（等号），然后在数据透视表中单击包含要返回数据的单元格，即可快速输入公式。
怎么样？是不是感觉整个人都轻松了不少，看起来很容易嘛~
说了这么多，我们还是举个例子实际操作一下：
统计下图中的销售额。
在G2单元格中输入公式：
=GETPIVOTDATA("销售额",$A$1,"销售地区","北京","商品","吹风机")
当然也可以直接在G2单元格输入“=”，再点击C2单元格的值，按回车键，同样可以得到结果。
接着我们将公式下拉到G4单元格，发现结果出错了。
这是怎么回事呢？
观察上图可以发现，下拉公式后，数据并没有随之变动。这就不得不提到GETPIVOTDATA函数的另一个特性：内容引用。大家都知道引用数据一般分为地址引用和内容引用。区别在于：地址引用时，如果引用的单元格位置发生变动，那么该值也会随之变动。
而内容引用时，如果引用的单元格位置发生变动，该值不会发生变化。
虽然内容引用可以在一定程度上保证引用数据的正确性，但以目前来看，我们需要将公式调整为地址引用，才能进行后续的计算。
在G2单元格中输入公式：
=GETPIVOTDATA("销售额",$A$1,$E$1,E2,$F$1,F2)
我们将原本公式中以文本形式输入的透视表字段名称和条件，以单元格引用的形式输入，成功得到了结果。
看到这里，有的小伙伴要提问了：既然上面的参数可以用单元格引用的形式输入，那GetPivotData函数第一参数可不可以呢？
我们来测试一下，在G2单元格中输入公式：
=GETPIVOTDATA(G1,$A$1,$E$1,E2,$F$1,F2)
结果很明显，公式报错了。其实我们在前面解释参数的时候，也提到过，GetPivotData函数的第一参数格式必须是以成对的英文双引号输入的文本字符串或是经转化为文本类型的单元格引用。
也就是说，如果第一参数要引用单元格，那么需要将其转换为文本类型的单元格引用。比如将第一参数G1变为""&G1 或 G1&"" ,抑或是trim(G1)。
因为我们需要下拉公式，所以需使用“$”锁定单元格。
好了，说了这么多，相信大家对GetPivotData函数已经有了一个大致的了解，接下来上“正菜”！
合并单元格绝杀函数！秒杀VLOOKUP、LOOKUP函数！
统计下图中的销售额。
又是令人头大的合并单元格的问题，先来看看大佬级函数VLOOKUP和LOOKUP是怎么解决问题的！
=VLOOKUP(F2,OFFSET($B$1,MATCH(E2,$A$2:$A$17,0),0,COUNTA($B$2:$B$17)-MATCH(E2,$A$2:$A$17,0)+1,2),2,0)
=LOOKUP(F2,INDIRECT("B"&MATCH(E2,A:A,0)&":C17"))
上面两个公式，相信绝大部分同学都看不懂，由于并不是今天的重点，我们就不过多解释了。
还有一种稍微简单一点的方法就是先取消合并单元格，并将空白部分填充为对应的部门，再使用公式：
=VLOOKUP(E2&F2,IF({1,0},$A$2:$A$17&$B$2:$B$17,$C$2:$C$17),2,0)
=LOOKUP(1,0/(($E2=$A$2:$A$17)*($F2=$B$2:$B$17)),$C$2:$C$17)
虽然经过填充空白单元格的操作后，这个问题被当成多条件查询来处理了，似乎看着比前面直接使用公式的方法更简单，但是对于才掌握VLOOKUP和LOOKUP函数基础用法的同学，估计也不太好懂。下面我们就给大家介绍一种更为简单的方法，相信在座的你，一定能学会！
还是需要先取消合并单元格，并将空白部分填充为对应的部门。然后选中数据区域，插入数据透视表。
将“销售地区”、“商品”拖放在“行”字段下，“销售额”拖放到“值”字段下。接着将建立好的数据透视表变成我们常规的表格样式。并在“数据透视表工具”栏下的“分析”选项卡下，点击“选项”，勾选“生成GetPivotData”，启用GETPIVOTDATA函数。
不熟悉步骤的小伙伴可以查看往期教程《受够加班煎熬，我整理出10条职场人士最常用的透视表技巧！（上篇）》
做到这一步，小伙伴们有没有觉得很熟悉呢？没错，这就是我们上面用GETPIVOTDATA函数举的例子，后面的公式，相信大家也都知道啦~就不再重复了。
我们最后总结一下，在使用GETPIVOTDATA函数时，需要注意的问题。
1. GETPIVOTDATA函数第一参数的格式必须是以成对的英文双引号输入的文本字符串或是经转化为文本类型的单元格引用。
2. GETPIVOTDATA函数属于内容引用，下拉填充公式时，需要将其中的参数修改为地址引用。
3. GETPIVOTDATA函数是透视表专有函数，仅能在透视表中使用。
另外，在使用GETPIVOTDATA函数查找数据时，查找的数据必须在数据透视表中可见。如果数据被折叠，那函数将会返回#REF!错误。
如果要关闭GETPIVOTDATA函数，可以在“数据透视表工具”栏下的“分析”选项卡下，点击“选项”，取消选中“生成GetPivotData”选项即可。
是不是比VLOOKUP、LOOKUP好理解多了，赶紧动手试一试吧！
本文配套的练习课件请加入QQ群：747953401下载。
做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
扫下方二维码关注公众号，可随时随地学习Excel：
相关推荐：