![]()
Excel在数据透视表中,当我们引用的时候,其实使用的就是GETPIVOTDATA函数但是你可能没有特别明白这个函数的意义所在如何你第一次尝试单独使用GETPIVOTDATA可能结果并不满意这是可以理解的，因为它的默认形式是相当不灵活的。
然而，与常规的单元格引用相比，使用GETPIVOTDATA的好处是巨大的，它可以减少很多工作量.
为什么?数据透视表报告通常会随着你向数据源添加新数据和改变筛选等而更新或改变字段选择。如果您链接到数据透视表中具有常规单元格引用特性，然后由于刷新、应用筛选器或切片器而更改数据的位置，那么公式会突然返回错误的信息。
GETPIVOTDATA不是这样。如果你的数据的位置移动了，那么GETPIVOTDATA仍然会返回原本的数据，前提是它仍然在你的数据透视表的某个地方可见。
因此，本教程的目标是将您从一个懵懂于GETPIVOTDATA的人转变为一个喜欢GETPIVOTDATA的人!
利用GETPIVOTDATA功能的诀窍是用嵌套公式替换硬性参数，从而使GETPIVOTDATA公式变得动态。听起来很复杂，但事实并非如此。看看下面就知道了
例如，下面用红色标出的是“硬性”参数:
=GETPIVOTDATA("销售额",$A$2,"订购日期",1,"年",2002)
开始GETPIVOTDATA
首先，为了让Excel生成GETPIVOTDATA公式，必须打开首选项。如果你不小心关闭了它，你可以在Ribbon的数据透视表选项卡中重新打开它:
![]()
Excel GETPIVOTDATA 函数例子
让我们看一个简单的场景。你可以在下面看到我的数据透视表。当我在单元格E5中输入一个等号，然后点击数据透视表中的单元格D5, Excel会自动输入这个公式:
=GETPIVOTDATA("销售额",$A$2,"订购日期",1,"年",2002)GETPIVOTDATA公式读取，返回2002年1月的销售总额。
![]()
对于通常=D5来说，这似乎是一个很大的公式，但是请记住，使用GETPIVOTDATA有很大的优势。注意:上面GETPIVOTDATA公式中对单元格A2的引用只是数据透视表的位置，它告诉Excel要从哪个数据透视表返回值。理论上，这可以是数据透视表中的任何单元格，但最安全的做法是选择一个始终存在的单元格，而不考虑数据透视表大小的任何变化。
Excel GETPIVOTDATA 用法
GETPIVOTDATA的烦人之处在于，当您复制并粘贴公式时，例如向下的列E，引用不是相对的。也就是说，它们不像普通的单元格引用那样更新以获取范围内的下一个单元格。所以你得到了一列相同的值就像E列中的这些:
![]()
使得 GETPIVOTDATA 函数自己动起来
让我们从更新公式开始，这样当复制到列中时，它就会每个月更新一次。这里的技巧是知道数据透视表如何表示日期;对于Excel GETPIVOTDATA函数来说，这些月份的名称似乎是1月、2月、3月…，但实际上它们是数字1、2、3…到12。因此，我们需要将GETPIVOTDATA公式中的月份参数替换为当我们将公式复制到列中时自动计数的参数。对于这个我们可以使用行函数。
ROW函数
ROW函数只返回引用的行号。因此，公式ROW(A1)将返回1，ROW(A2)将返回2，以此类推。我们可以把ROW放入E4单元格的公式中，像这样:
=GETPIVOTDATA("销售额",$A$2,"订购日期",ROW(A1),"年",2002)计算结果是:
=GETPIVOTDATA("销售额",$A$2,"订购日期",1,"年",2002)
现在，当你将单元格E5公式复制到列下时，行函数中对单元格A1的相对引用会动态地增加1。我已经写出了E在F中的公式，如下图所示:
![]()
这个方法我们只是是这个函数稍微的变成了一个动态,但是这只是第一步,后面会告诉你们怎么使用这样的小型动态来做一些其他的事情.
小贴士:
通过这种办法也可以是年变成动态的显示这样的办法还可以使用在不仅仅是ROW()函数还可以使用在COLUMN()函数上,道理是一样的.需要强调的这个ROW函数和COLUMN函数也使用在其他地方不仅仅是数据透视表,和其他的公式搭配使用,也可以达到动态的效果,最简单的就是自动变化的序号.
Excel GETPIVOTDATA 函数配合数据验证
如果我们使用GETPIVOTDATA搭配数据验证序列会怎么样?一步一步来
![]()
我们的湖南总销售额的公式是这样的:=GETPIVOTDATA("销售额",$A$2,"收货省份","湖南")
我们可以将”湖南”改成E2单元格的内容,E2单元格,咱们做成两个省份的序列数据验证
=GETPIVOTDATA("销售额",$A$2,"收货省份",E2)
现在，当您从数据验证列表中选择一个不同的省份时，GETPIVOTDATA公式将动态更新。
这个很简单,但是你要掌握这些小技巧,小巧多了,你才能做一个属于你自己的大型管理表格.
Excel GETPIVOTDATA 函数总结
一般使用这个公式,首先我会在一个需要返回或者引用的值后直接打一个等于号,然后引用这个数据,需要改的话,直接在公式里进行修改.从头开始单独使用这个公式会让你记不起很多数据透视表中定义过的表头名称
你可以是所有的参数都变成动态的,然后搭配名称管理器来使用,这也是很多人做DASHboard的惯用手法.
你可以使用GETPIVOTDATA这个函数来引用值,但是不能查询数据源本身.
你可以用它来返回计算值.
就像其他的函数一样,函数都是可以进行计算的,如果你的数据需要进行引用之后的计算,也是可以的,比如: =GETPIVOTDATA("销售额",$A$2,"收货省份",E2)*2.3 意思就是湖南或者新疆地区的总销售额乘以2.3 当然这个比喻并不恰当,但是在其他的逻辑表里可以用这样的方法去算.
如果参数描述的值不在数据透视表中，它将返回#REF!错误。
希望你能学会这个函数
课件地址:https://share.weiyun.com/53v8SpE
|
|
转播
