pivot函数的作用（privot函数）



刚发现，这里也能发文章。正好，最近事情有点多，微信公众号那边来不及更新新文章，就发了一篇老文章，顺便发在这里吧。这篇文章详细介绍了Excel中的GETPIVOTDATA函数，正好跟接下来一段时间计划发的一系列文章有关，发在这里算是一个“温习”和“预热”吧。😊

认识GETPIVOTDATA

大部分使用Excel的朋友对这个函数都相当陌生。但是，我相信很多人都见过这个函数，大多数人第一次见到这个函数是如下的场景：

假设我们有这个一个透视表：

我们希望引用一月份的总销售额然后求产品的平均值，由于1月份的销售额合计在F5单元格，因此，我们希望的公式是：=F5/5（5个产品）。于是，你像输入其他公式一样，在G5单元格中输入一个：=，然后鼠标点击F5单元格，出乎你的意料，你没有得到：=F5，却得到了一长串内容：

很多人可能会把这一长串内容“吓着”，赶紧就按Esc键，取消输入，甚至都没有来得及看看这一长串内容写的是什么😉。

这一长串内容就是在公式引用透视表的单元格时自动生成的GETPIVOTDATA函数。

这个函数其实很简单的。

先来看名字：

从名字上看，这个公式的作用就是从透视表中获得数据。

它的使用也比较简单。我们看下面的例子：

这里这个公式=GETPIVOTDATA("数量",$A$3,"区域","西区","月",1)使用了6个参数，很简单就可以看出是什么意思：

这个公式的意思就是：

取得——A3单元格所在的透视表中，列字段“区域”中项目为“西区”，行字段“月份”中项目为“1” 的“数量”

实际上，就是通过这些参数唯一限定了透视表区域的一个单元格。

尽管简单，这个公式在使用中还是有一些需要注意的地方，下面我们结合例子来详细介绍一下。

GETPIVOTDATA例子和注意事项

例1 最简单的GETPIVOTDATA公式

这个公式中不是除了前面两个参数外，其余的参数都不是必须的。因此，我们可以写这个一个公式：

=GETPIVOTDATA("数量",$A$3)

这个公式的意思是获得A3单元格所在的透视表中的数量。对照这个透视表，你能够指出到底是哪一个值：

这里没有通过行字段和列字段指定范围，那么在这个透视表上用“数量”唯一能够确定的就是右下角那个单元格F8，即。

例2 只指定列字段

理解了上一个例子的，下面这个公式就很容易理解了：

=GETPIVOTDATA("数量",$A$3,"区域","西区")

这个值只要取得透视表中区域为西区的数量值，由于没有行字段的限定，因此，只能是西区的合计值，就E8单元格的值。

同样，可以只指定行字段，而不要列字段。

例3 多个行标签（或列标签的情况）

如果透视表上行标签不止一个，那么要唯一确定透视表的单元格就必须用多个行标签来限定，参数就会更多。

例如这个公式：

=GETPIVOTDATA("数量",$A$3,"日期",2,"区域","东区","产品","芬达橙味200","月",1)

其中，取得值字段是“数量”，列标签是“区域”，项目是“东区”，但是行标签是通过月份，日期，产品3个标签来限定的。

从这个例子也可以看出，除了前两个参数外，其他的参数顺序无所谓，只要能唯一限定一个单元格就好了。

例4 这个公式本质上是个查找

如果你换一个角度看这个公式，实际上这个公式就是个多条件3D的查找公式。

3D指三个维度：

这个公式根据这个3个维度唯一确定一个值。

还是上面的公式，如果我们把透视表变成表格格式，就看得更加清楚了：

实际上，这个公式是在这个表格中查找满足行列条件的那个单元格的值。

例5 透视表布局会影响结果。

由于上面这个原因，不同的透视表布局会影响同一个公式的结果。例如：

现在这个公式的值是。如果我们将筛选改成1月：

公式没变，结果却变了。这个例子再次说明这个公式的机制就是在透视表表格中查找，它并不负责根据源数据计算相应的结果。

例6 如果公式中指定的标签在透视表上不存在怎么办？

假设，原来的公式是：

=GETPIVOTDATA("数量",$A$3,"日期",2,"区域","东区","产品","芬达橙味200","月",1)

现在，我们将透视表中产品从行标签中去掉：

可以看到，现在公式返回了一个错误值，表示引用的区域不存在了！

例8 公式中可以引用单元格

这个很容易理解。例如，公式：

=GETPIVOTDATA("数量",$A$3,"日期",2,"区域","东区","产品","芬达橙味200","月",1)

可以写成：

=GETPIVOTDATA("数量",$A$3,"日期",2,"区域","东区","产品","芬达橙味200","月",G1)

在G1中输入不同月份，就可以取出对应月份的数量。

这样我们就可以很灵活的控制我们需要的内容了。

为什么要使用GETPIVOTDATA函数

大部分在了解了这个函数后，下一个必然的问题就是：既然都使用了透视表了，为什么还需要使用这个函数？

根据我的经验，有2个原因：

GETPIVOTDATA的问题

看到这里，相信有很多朋友会发现这个函数有一个问题：

想使用GETPIVOTDATA取得相应的数值，必须保证有一个透视表存在，并且该透视表的布局必须保证要取得的单元格是存在的。

如果你有多个需求，很可能这些需求不能在一个透视表布局上得到满足，就需要做多个透视表。这会给后续的维护带来相当大的复杂性。

这是GETPIVOTDATA这个函数的机制造成的，没有办法避免。如果想规避这种情况，又想利用GETPIVVOTDATA这种方法，可以使用CUBE函数。接下来我会安排一系列视频介绍这个函数结合数据模型的应用。

关闭GETPIVOTDATA函数

很多人不知道在透视表中这个函数可以关闭“自动生成”这个函数的功能。选中透视表任意单元格，在“分析”选项卡中，点击最左边的选项，然后去掉“生成GetPivotData”的勾选：

这样，你再采取本文一开始的方法，在公式中点击透视表的数值单元格时，就不会生成GETPIVOTDATA函数，而是直接引用单元格了。

需要说明的是，这个操作并没有“关闭”这个公式的使用，你仍然可以在单元格中直接输入：=GETPIVOTDATA来使用这个函数。

版权声明：

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若内容造成侵权、违法违规、事实不符，请将相关资料发送至xkadmin@xkablog.com进行投诉反馈，一经查实，立即处理！

转载请注明出处，原文链接：https://www.xkablog.com/haskellbc/41352.html