透视表函数GETPIVOTDATA.docx

透视表函数GETPIVOTDATA.docx

《透视表函数GETPIVOTDATA.docx》由会员分享，可在线阅读，更多相关《透视表函数GETPIVOTDATA.docx（12页珍藏版）》请在冰点文库上搜索。

透视表函数GETPIVOTDATA

数据透视表函数的语法一

GETPIVOTDATA（data_field,pivot_table,field1,item1,field2,item2,…field14,item14）

Data_field：

为包含要检索的数据的数据字段的名称，用引号引起。

也可以用T函数进行转换，转换之后则是一个加有引号的文本。

Pivot_table：

在数据透视表中对任何单元格、单元格区域或定义的单元格区域的引用。

该信息用于决定哪个数据透视表包含要检索的数据。

Field1,Item1,Field2,Item2：

为1到14对用于描述检索数据的字段名和项名称，可以任何次序排列。

字段名和项名称（而不是日期和数字）用引号引起来。

此语法适用于默认汇总方式，应用方法见实例2、实例3、实例4。

数据透视表函数的语法二

GETPIVOTDATA（pivot_table,data_field&""&item1&""&item2,…&""&item14）

data_field&""&item1&""&item2,…&""&item14是中间用空格连接成的完整的文本。

此语法的data_field可以引用多个单元格，可不用OFFSET进行引用，从而达到精简函数目的。

此语法适用于默认汇总方式，应用方法见实例5。

数据透视表函数的语法三

GETPIVOTDATA（pivot_table,"field1[item1;汇总方式]data_field"）

此语法适用于自定义汇总方式，应用方法见实例6。

快速获取getpivotdata函数的方法：

在目标单元格输入等号，然后单击透视表中的任意单元格，如果没有自动生成getpivotdata函数，说明该函数功能没有打开，需要在透视表工具单击向下三角》删除和添加按钮》数据透视表》勾选“生成getpivotdata”。

这样在工具栏添加了一个新的按钮，这个按钮相当于一个开关键，将新加的按钮按下去即可自动生成getpivotdata函数。

如果想取消，再点击一次按钮即可。

一、认识数据透视表函数

实例1:

1、销售总量：

=GETPIVOTDATA（"求和项:

数量",$A$1）

2、海口分公司销售金额：

=GETPIVOTDATA（"求和项:

金额",$A$1,"分公司","海口分公司"）

3、南京分公司2008年10月2日C产品的销售量：

=GETPIVOTDATA（"求和项:

数量",$A$1,"品种","C产品","分公司","南京分公司","日期",DATE（2008,10,2））

注意：

在使用日期字段里的字段项的时候最好用DATE函数返回日期的结果，以避免日期格式的变化出现查找出错的情况。

4、A产品销售总金额：

=GETPIVOTDATA（"求和项:

金额",$A$1,"品种","A产品"）

注意：

数据透视表函数只能查找数据透视表里面已经反应出来的数据，没有的数据将会出现错误值。

如上例，因为在数据透视表中没有A产品销售金额汇总的值。

所以出现错误的值。

另处，除了利用快速引用的方法，还可以修改函数公式的参数。

因为在公式里的参数都是具体的文本值，可以通过引用单元格的数据使公式更加灵活。

二、动态获取数据透视表的数据

实例2：

（数据透视表函数语法1应用）

原公式：

E3=GETPIVOTDATA（"求和项:

金额",$A$1,"品种","A产品","分公司","海口分公司"）/GETPIVOTDATA（"求和项:

数量",$A$1,"品种","A产品","分公司","海口分公司"）

修改的公式：

E3=GETPIVOTDATA（T（$D$2）,$A$1,$B$2,B3,$A$2,LOOKUP（"々",$A$3:

A3））/GETPIVOTDATA（T

（$C$2）,$A$1,$B$2,B3,$A$2,LOOKUP（"々",$A$3:

A3））

这样公式即可向下拖动，由于公式向下拖动时，行字段只有第一个数据项有值。

比如海口分公司只有A3有数据，A4：

A6为空值，所以当公式向下拖动时会出现错误。

这里就用LOOKUP对其进行处理，LOOKUP（"々",$A$3:

A3）即是在$A$3:

A3这个动态的区域里面查找"々"（这个字符在字码表里大于所有的汉字，输入方法：

Alt+41385），因为当LOOKUP查找的值不存在时，会返回一个小于查找值的最大值，即用A3的数据对A4：

A6进行填充。

在进行引用时要注意，引用的第一个字段要用T函数进行处理，还要注意单元格的引用方式，行字段要用绝对引用，如果行字段下的数据项是连续的数据就可采用相对引用，如果数据项有空值就要用LOOKUP函数来进行填充。

三、数据透视表函数与更多的函数的联合应用

实例3、（数据透视表函数语法1应用）

定义名称：

g_name=SUBSTITUTE（LOOKUP（"々",透视表!

$A$3:

$A1）," 汇总",""）

应用公式：

E3=GETPIVOTDATA（T（$C$2）,$A$1,$A$2,g_name,IF（$B3=0,$A$2,$B$2）,IF（$B3=0,g_name,$B3）

）/GETPIVOTDATA（T（$C$2）,$A$1,$A$2,g_name）

定义名称的用途是填充行字段“分公司”下为空值的项。

另外，因为此透视表有汇总行，所以要用SUBSTITUTE将“汇总”替换为空文本。

四、在透视表函数中运用内存数组

实例4、（数据透视表函数语法1应用）

定义名称：

aa=IF（ISNUMBER（GETPIVOTDATA（T（$D$2）,透视表!

$A$1,$A$2,透视表!

$A$3:

$A$34））,

GETPIVOTDATA（T（$D$2）,透视表!

$A$1,$A$2,透视表!

$A$3:

$A$34））

1、销售量最大的分公司：

=LOOKUP（2,1/（MAX（aa）=aa）,$A$3:

$A$34）

2、销售量最小的分公司：

=LOOKUP（2,1/（MIN（aa）=aa）,$A$3:

$A$34）

3、销售量最大的分公司C产品的销售金额：

=GETPIVOTDATA（T（$E$2）,$A$1,$B$2,"C产品",$A$2,LOOKUP（2,1/（MAX（aa）=aa）,$A$3:

$A$34））

4、销售量最大的分公司2008年10月1日A产品的销售价格：

=GETPIVOTDATA（T（$E$2）,$A$1,$B$2,"A产

品",$A$2,LOOKUP（2,1/（MAX（aa）=aa）,$A$3:

$A$34）,$C$2,DATE（2008,10,1））/GETPIVOTDATA（T（$D$2）,

$A$1,$B$2,"A产品",$A$2,LOOKUP（2,1/（MAX（aa）=aa）,$A$3:

$A$34）,$C$2,DATE（2008,10,1））

5、销售量最小的分公司2008年10月1日B产品的销售价格：

=PRODUCT（GETPIVOTDATA（T（OFFSET（D2,,

{0,1}））,$A$1,$B$2,"B产品",$A$2,LOOKUP（2,1/（MIN（aa）=aa）,$A$3:

$A$34）,$C$2,DATE（2008,10,1））^{

-1,1}）

注意：

此语法不能同时引用数量和金额（E2和D2）两个单元格，所以用OFFSET（D2,,{0,1}）来进行处理，然后再将两个数相除。

6、所有分公司A产品的销量：

=SUM（GETPIVOTDATA（" 数量",$A$1,"品种","A产品","分公司",T（OFFSET（A2,{1,13,23},））））

公式中OFFSET（A2,{1,13,23},）即是引用三个分类汇总的行。

实例5、（数据透视表函数语法2应用）

查寻结果：

南京分公司B产品2008年10月2日数量：

=GETPIVOTDATA（$A$1,$H$18&""&$H$19&""&$H$20&""&TEXT（$H$21,"YYYY年mm月d日"））

TEXT（$H$21,"YYYY年mm月d日"）是设置查找日期的格式与数据透视表日期的格式相同。

此语法在使用时会更加灵活，它的data_field参数可以引用两个或多个单元格。

比如：

销售量最大的分公司2008年10月1日A产品的销售价格：

{=PRODUCT（GETPIVOTDATA（$A$1,D2:

E2&""&LOOKUP（2,1/（MAX（aa）=aa）,$A$3:

$A$34）&""&"A产品"&""&TEXT（C11,"yyyy年mm月d日"））^{-1,1}）}

五、获取自定义分类汇总结果

实例6、（数据透视表函数语法3应用）

根据数据透视表生成汇总表：

B4=GETPIVOTDATA（透视表!

$A$1,透视表!

$A$2&"["&$A4&";计数]"&B$3）

公式向下和向右拖动即可生成汇总表。

六、量身定做自己的数据表

实例7、（数据透视表函数综合应用）

可以利用透视表函数计算速度快的特点创建自定义的数据报表，对数据透视表数据比较多，数据量大的数据透视表进行摘录时，就可用数据透视表函数来完成。

本节课主要内容小结：

一、数据透视表函数三种语法结构

1、默认汇总方式下的二种。

2、自定义汇总方法下的一种。

二、数据透视表函数各参数可以是：

常量、单元格引用（单一或多个单元格）、公式、数组（常量、公式结果）等。

三、数据透视表函数应用范围

1、创建自定义数据表。

2、对数据透视表数据进行摘录。

很快紧张的一个月的学习时间过去了，回想一下，学到的东西真还不少，但是我感觉还有些问题自已掌握的还不是很牢固，还需要在课后认真的复习和应用，才能够真正的学为已用，学有所用。

其实做好笔记也是为了以后复习方便。

另外，这已经是最后一课的小节了，在这里我要向所有的同学说声再见，希望大家在今后的学习中取得更优异的成绩，希望大家的Excel水平更上一层台阶。

（注：

专业文档是经验性极强的领域，无法思考和涵盖全面，素材和资料部分来自网络，供参考。

可复制、编制，期待你的好评与关注）