首页 数据透视表正文

太难了,但真的好用。透视表函数GETPIVOTDATA实用技巧,如何用公式引用透视表中的数据。

Excel2016数据透视表入门教程
第-8.1-节透视表函数GETPIVOTDATA
想要对数据透视表的报表结果循环利用,结果发现引用结果总是数据错误,这些都是GETPIVOTDATA函数没用好的表现,今天小编excel小课堂(ID:excel-xiaoketang 长按复制)就带大家学习下这个神奇的函数该如何使用,实现数据透视表的深度挖掘。

01透视表函数GETPIVOTDATA
GETPIVOTDATA函数是在数据透视表基础上,使用的一个函数,用来解决数据透视表的灵活性问题,特别是3维及以上数据透视表的报表杂乱问题。不仅可以在数据透视表的基础上,按照自己需要的格式获取数据,甚至可以支持最多14对的filed,item对筛选条件,使结果一目了然,不用人费眼的去寻找。
为什么要用GETPIVOTDATA?GETPIVOTDATA 将从数据透视表中检索数据。该函数的最大好处是,当透视表布局更改时它能继续检索正确的数据。如果在布局更改时某块数据的单元格位置也更改了,函数仍将找到并返回正确的值,不管它在哪个单元格。但是当字段更名或者移出数据透视表时,它就会显示错误值。
技巧:使用透视表过程中,在外部键入等号然后选择内部的单个单元格时,会自动输入 GETPIVOTDATA 函数。


02GETPIVOTDATA函数语法参数
说实在的,这个函数语法用起来有点复杂,不过小编相信,各位都能搞得定。
函数语法:
GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)
参数说明:
Data_field    必需。包含要检索的数据的数据字段的名称,用引号引起来。Pivot_table    必需。数据透视表中的任何单元格、单元格区域或命名区域的引用。此信息用于确定包含要检索的数据的数据透视表。
Field1、Item1、Field2、Item2    可选。描述要检索的数据的 1 到 126 个字段名称对和项目名称对。这些对可按任何顺序排列。
特别说明:
1、在函数 GETPIVOTDATA 的计算中可以包含计算字段、计算项及自定义计算方法。
2、如果 pivot_table 为包含两个或更多个数据透视表的区域,则将从区域中最新创建的报表中检索数据。
3、如果字段和项的参数描述的是单个单元格,则返回此单元格的数值,无论是文本串、数字、错误值或其他的值。
4、如果项目包含日期,则此值必须以序列号表示或使用 DATE 函数进行填充,以便在其他位置打开此工作表时将保留此值。例如,引用日期 1999 年 3 月 5 日的项目可按 36224 或 DATE(1999,3,5) 的形式输入。时间可按小数值的形式输入或使用 TIME 函数输入。

03常见排错指南
列举常见的错误原因与解决方法。
1、pivot_table参数不正确。如果 pivot_table 并不代表找到了数据透视表的区域,则函数 GETPIVOTDATA 将返回错误值 #REF!。

2、数据字段名或项错误。如果fieldi,itemi未描述可见字段,或者参数包含未显示的页字段(即在建数据透视表表格的时候,未显示在数据透视表中的字段),则GETPIVOTDATA函数将返回#REF。

3、第一参数单元格格式错误。尝试在GETPIVOTDATA函数中使用了单元格引用,结果却报错了。C3单元格的公式为“=GETPIVOTDATA(C2,$A$2,"订单日期",1)”,左看右看,都没发现公式有啥错误,原因在于data_field参数要求数据类型必须是文本字符串,可以使用T函数或者&""将内容转换成文本字符串,所以正确的公式应为GETPIVOTDATA(T(C2),$A$2,"订单日期",1)或GETPIVOTDATA(C2&“”,$A$2,"订单日期",1).

如果不想看到出现#REF!错误,而且要当GETPIVOTDATA 函数的值是#REF!时让他显示0,可以使用IFERROR函数优化显示。IFERROR函数使用教程


04取消自动生成GETPIVOTDATA公式
如果只想引用单元格的值,可以关闭自动生成GETPIVOTDATA公式。

方法1:选中数据透视表任意单元格——数据透视表工具——选项功能区——选项(下拉小箭头)——把“生成GETPIVOTDATA”的钩勾选或者去掉控制打开还是关闭;

方法2 :打开“选项”进行设置,取消勾选。

采用上述任一方法完成设置后,在C3单元格输入“=“号,再单击数据透视表的B7单元格,则引用单元格地址,如下图所示,而不会自动生成数据透视表函数。


 点击下方“阅读原文”,更多数据透视表教程等你来    
关注excel小课堂(ID:excel-xiaoketang 长按复制)微信号,学好excel,提高职业素养,提升职场竞争力,升职加薪不再是梦!

版权声明

本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。

评论