透视表函数–GETPIVOTDATA.docxVIP

数据透视表函数的语法一  ? 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