透视表函数-GETPIVOTDATA

1、数据透视表函数的语法一 GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,field14,item14)Data_field：为包含要检索的数据的数据字段的名称，用引号引起。也可以用T函数进行转换，转换之后则是一个加有引号的文本。Pivot_table：在数据透视表中对任何单元格、单元格区域或定义的单元格区域的引用。该信息用于决定哪 个数据透视表包含要检索的数据。Field1,Item1,Field2,Item2：为1到14对用于描述检索数据的字段名和项名称，可以任何次序排列。字段名和项名称（而不是日期和数字）

2、用引号引起来。此语法适用于默认汇总方式，应用方法见实例2、实例3、实例4。 数据透视表函数的语法二 GETPIVOTDATA(pivot_table,data_field&" "&item1&" "&item2,&" "&item14)data_field&" "&item1&" "&item2,&" "&item14是中间用空格连接成的完整的文本。此语法的da

3、ta_field可以引用多个单元格，可不用OFFSET进行引用，从而达到精简函数目的。此语法适用于默认汇总方式，应用方法见实例5。 数据透视表函数的语法三 GETPIVOTDATA(pivot_table,"field1item1;汇总方式data_field")此语法适用于自定义汇总方式，应用方法见实例6。 快速获取getpivotdata函数的方法：在目标单元格输入等号，然后单击透视表中的任意单元格，如果没有自动生成getpivotdata函数，说明该函数功能没有打开，需要在透视表工具单击向下三角删除和添加按钮数据透视表勾选“生成getpi

4、votdata”。这样在工具栏添加了一个新的按钮，这个按钮相当于一个开关键，将新加的按钮按下去即可自动生成getpivotdata函数。如果想取消，再点击一次按钮即可。  一、认识数据透视表函数 实例1:1、销售总量：=GETPIVOTDATA("求和项:数量",$A$1)2、海口分公司销售金额：=GETPIVOTDATA("求和项:金额",$A$1,"分公司","海口分公司")3、南京分公司2008年10月2日C产品的销售量：=GETPIVOTDATA("求和项:数量&qu

5、ot;,$A$1,"品种","C产品","分公司","南京分公司","日期",DATE(2008,10,2)注意：在使用日期字段里的字段项的时候最好用DATE函数返回日期的结果，以避免日期格式的变化出现查找出错的情况。4、A产品销售总金额：=GETPIVOTDATA("求和项:金额",$A$1,"品种","A产品")注意：数据透视表函数只能查找数据透视表里面已经反应出来的数据，没有的数据将会出现错误值。如上例，因为在数据透视表中没有A产

6、品销售金额汇总的值。所以出现错误的值。另处，除了利用快速引用的方法，还可以修改函数公式的参数。因为在公式里的参数都是具体的文本值，可以通过引用单元格的数据使公式更加灵活。 二、动态获取数据透视表的数据实例2：（数据透视表函数语法1应用） 原公式：E3=GETPIVOTDATA("求和项:金额",$A$1,"品种","A产品","分公司","海口分公司")/GETPIVOTDATA("求和项:数量",$A$1,"品种","A产品

7、","分公司","海口分公司")修改的公式：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$

8、3:A3)即是在$A$3:A3这个动态的区域里面查找""（这个字符在字码表里大于所有的汉字，输入方法：Alt+41385），因为当LOOKUP查找的值不存在时，会返回一个小于查找值的最大值，即用A3的数据对A4：A6进行填充。在进行引用时要注意，引用的第一个字段要用T函数进行处理，还要注意单元格的引用方式，行字段要用绝对引用，如果行字段下的数据项是连续的数据就可采用相对引用，如果数据项有空值就要用LOOKUP函数来进行填充。三、数据透视表函数与更多的函数的联合应用实例3、（数据透视表函数语法1应用） 定义名称：g_name=SUBSTITUTE(LOOKUP(&q

9、uot;",透视表!$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应用） 定义名称：

10、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

11、(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产品的销售价格：=P

12、RODUCT(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(O

13、FFSET(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日")是设置查找日期的格式与数据透视表日期的

14、格式相同。此语法在使用时会更加灵活，它的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应用

15、）  根据数据透视表生成汇总表：B4=GETPIVOTDATA(透视表!$A$1,透视表!$A$2&""&$A4&"计数"&B$3)公式向下和向右拖动即可生成汇总表。 六、量身定做自己的数据表实例7、（数据透视表函数综合应用）可以利用透视表函数计算速度快的特点创建自定义的数据报表，对数据透视表数据比较多，数据量大的数据透视表进行摘录时，就可用数据透视表函数来完成。 本节课主要内容小结：一、数据透视表函数三种语法结构1、默认汇总方式下的二种。2、自定义汇总方法下的一种。二、数据透视表函数各参数可以是：常量、单元格引用（单一或多个单