今天介绍Excel中的一个非常特殊的函数,GETPIVOTDATA。 一般在基础性的培训课上,我都不会讲这个函数,即使有人问起,我也会说不用关心它,对于普通人来说,用处不大,完全可以不学它。 不过,在有些特殊场合,这个公式可以起到很大的作用,比如在DASHBOARD中,或者数据报告里。 看完本文的介绍,相信你会发现这个函数的用处的😉 本文内容:
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个原因:
04 看到这里,相信有很多朋友会发现这个函数有一个问题: 想使用GETPIVOTDATA取得相应的数值,必须保证有一个透视表存在,并且该透视表的布局必须保证要取得的单元格是存在的。 如果你有多个需求,很可能这些需求不能在一个透视表布局上得到满足,就需要做多个透视表。这会给后续的维护带来相当大的复杂性。 这是GETPIVOTDATA这个函数的机制造成的,没有办法避免。如果想规避这种情况,又想利用GETPIVVOTDATA这种方法,可以使用CUBE函数,我会在以后的文章中为大家详细介绍。 05 很多人不知道在透视表中这个函数可以关闭“自动生成”这个函数的功能。选中透视表任意单元格,在“分析”选项卡中,点击最左边的选项,然后去掉“生成GetPivotData”的勾选: 这样,你再采取本文一开始的方法,在公式中点击透视表的数值单元格时,就不会生成GETPIVOTDATA函数,而是直接引用单元格了。 需要说明的是,这个操作并没有“关闭”这个公式的使用,你仍然可以在单元格中直接输入:=GETPIVOTDATA来使用这个函数。 |
|
来自: hercules028 > 《excel》