Excel 2007函数与公式实战技巧精粹: 第24章 数据透视表函数综合应用

数据透视表是Excel中非常出色的功能，它具有操作灵活和数据处理快捷的特点。如果用户既希望能利用透视表出色的数据处理能力，同时又能设计出个性化的表格，使用数据透视表函数是一个很好的选择。

本章将详细介绍数据透视表函数GETPIVOTDATA的使用方法和运用技巧，使用户对数据透视表函数有一个全面的认识，并掌握一定的运用技巧，从而设计出效率更高、更具个性的数据报表。

本章内容主要包括：

（1）初识数据透视表函数。

（2）静态、动态获取数据透视表数据。

（3）获取自定义分类汇总的结果。

（4）特殊的Excel 2000版数据透视表函数。

（5）数据透视表函数与其他函数的联合使用。

（6）数据透视表函数的具体应用。

技巧263 初识数据透视表函数

数据透视表函数是为了获取数据透视表中各种计算数据而设计的，最早出现在Excel 2000中，该函数的语法结构在Excel 2003中得到了进一步改进和完善，Excel 2007仍沿用这些语法结构。

数据透视表函数在自动汇总条件下的基本语法结构如下：

GETPIVOTDATA(data_field,pivot_table,[field1,item1],[field2,item2 ] ,...)

（1）参数data_field表示包含要检索数据表的字段名称，其格式必须是以成对双引号输入的文本字符串或是经转化为文本类型的单元格引用。

当该参数是文本字符串时，必须使用成对双引号引起来；如果是单元格引用，必须使用文本类函数（如T函数），或直接使用文本连接符“＆”连接一个空值符“""”，将该参数转化成文本类型，否则会出现“#REF!”错误。

（2）参数 pivot_table 表示对数据透视表中任何单元格或单元格区域的引用，该信息用于决定哪个数据透视表包含要检索的数据。

（3）参数 field1,item1,field2,item2,…，为一组或多组“字段名称”和“项目名称”，主要用于描述获取数据的条件，该参数可以为单元格引用或常量文本字符串。

（1）如果参数为数据透视表中“不可见”或“不存在”的字段，则GETPIVOTDATA函数将返回“#REF!”错误值。

（2）该语法结构适用于获取数据透视表各种汇总方式下的明细数据，或“自动”分类汇总方式下的分类汇总数据。如果需要获取“自定义”分类汇总方式下的分类汇总数据，则需要使用另外的语法结构。

用户可以在选中数据透视表中的任意单元格后，在【数据透视表工具】→【选项】→【数据透视表】→【选项】下拉列表中，勾选【生成GetPivotData】选项，打开自动生成数据透表函数公式开关，此时，当用户引用数据透视表中“数值”区域中的数据时，Excel就会自动生成数据透视表函数公式。

举例应用如下。

（1）海南分公司2012年3月2日“数量”的值，数据透视表函数的公式如下，如图263-1所示。

=GETPIVOTDATA("求和项:数量",$A$3,"分公司","海南分公司","日期",DATE(2012,3,2))

图263-1 数据透视表函数取值示例一

（2）浙江分公司汇总“金额”的值，数据透视表函数的公式如下，如图263-2所示。

=GETPIVOTDATA("求和项:金额",$A$3,"分公司","浙江分公司")

图263-2 数据透视表函数取值示例二

（3）各分公司“金额”总计的公式如下，如图263-3所示。

=GETPIVOTDATA("求和项:金额",$A$3)

图263-3 数据透视表函数取值示例三

技巧264 自动汇总方法下静态获取数据透视表数值

根据数据透视表函数公式，用户可以方便地获取到数据透视表中的计算数据。在默认情况下，数据透视表会采取“自动汇总”方式进行分类汇总。

图264-1是使用数据透视表汇总的ABC公司各分公司2012年3月份销售表，根据分析要求，现需要从数据透视表中获取有关数据。

图264-1 ABC公司销售汇总透视表

（1）获取销售总金额。

在K17单元格输入如下数据透视表函数公式，计算结果为248122：

=GETPIVOTDATA(" 金额",$A$2)

公式解析如下。

第1个参数表示计算字段名称，本例中为" 金额"，该值是由自动输入工具生成的，也可以手工删除“ 金额”前的空格，改为"金额"。

第2个参数为数据透视表中任意一个单元格，本例中为$A$2。

GETPIVOTDATA函数只有两个参数，没有其他条件时，表示要求获取计算字段的合计数。

（2）获取江苏分公司销售总数量。

在K18单元格输入数据透视表函数公式，计算结果为10500。

=GETPIVOTDATA(" 数量",$A$2,"分公司","江苏分公司")

公式解析如下。

第1个参数，表示需要计算字段名称，本例中为“数量”，也可以删除空格修改为“数量”。

第2个参数，为数据透视表中任意一个单元格，本例中为$A$2。

第3、4个参数，为分类计算条件组，由分类字段“分公司”和分类字段项“江苏分公司”组成。

（3）获取浙江分公司2012年3月2日的销售金额

在K19单元格输入数据透视表函数公式，计算结果为45039。

=GETPIVOTDATA(" 金额",$A$2,"分公司","浙江分公司","日期",DATE(2012,3,2))

公式解析如下。

第1个参数，表示需要计算字段名称，本例中为“金额”，也可以删除空格修改为“金额”。

第2个参数，为数据透视表中任意一个单元格，本例中为$A$2。

第3、4个参数，为分类计算条件组，由分类字段“分公司”和分类字段项“浙江分公司”组成。

第5、6个参数，为分类计算另一条件组，由分类字段“日期”和分类字段项DATE(2012,3,2)组成，这里的日期使用了DATE函数生成，也可以直接写成“2012-3-2”，并用半角双引号引起来。

如果条件值为日期时，日期格式必须与透视表中的格式一致，或用DATE函数生成日期值。

（4）海南分公司2012年3月1日B产品的销售数量。

在K20单元格输入数据透视表函数公式，计算结果为600。

=GETPIVOTDATA(" 数量",$A$2,"品种","B产品","分公司","海南分公司","日期",DATE(2012,3,1))

公式解析如下。

第1个参数，表示需要计算字段名称，本例中为“金额”，也可以删除空格修改为“金额”。

第2个参数，为数据透视表中任意一个单元格，本例中为$A$2。

第3、4个参数，为分类计算条件组，由分类字段“品种”和分类字段项“B产品”组成。

第 5、6 个参数，为分类计算条件组，由分类字段“分公司”和分类字段项“海南分公司”组成。

第7、8个参数，为分类计算另一条件组，由分类字段“日期”和分类字段项DATE(2012,3,1)组成，这里的日期使用了DATE函数生成，也可以直接写成“2012-3-1”，并用半角双引号引起来。

从上述示例可以看出，当数据透视表函数的条件参数越多，获取得到的值越明细，反之得到的将是各级分类汇总的值，计算结果如图264-2所示。

图264-2 透视表函数计算结果

技巧265 自动汇总方法下动态获取数据透视表数值

运用数据透视表函数，用户还可以通过使用混合单元格引用实现动态获取数据透视表数据的目的。

图265-1是使用数据透视表汇总的ABC公司各分公司2012年3月份销售表，根据分析需要，现需要从数据透视表中动态获取有关数据。

图265-1 ABC公司销售汇总透视表

（1）获取销售总金额。

获取销售总金额的数据透视表函数公式如下，计算结果为251443。

=GETPIVOTDATA(T(C5),$A$3)

公式解析如下。

第1个参数为计算字段名称，本例中为C5单元格引用值“金额”，并用T函数将其转换为文本类型，在这里也可以使用C5＆""或其他文本函数将C5单元格引用值转换为文本类型。

第2个参数为数据透视表中任意一个单元格，本例为A3单元格的绝对引用格式。

（2）获取各分公司销售数量合计数。

在C23单元格输入如下公式，并将公式向下拖动填充至C25单元格，计算结果如图265-2所示。

图265-2 获取各分公司销售数量合计

=GETPIVOTDATA(T(D$5),$A$3,"分公司",B23＆"分公司")

公式解析如下。

第1个参数为计算字段名称，本例引用数据透视表中D5单元格值“数量”，并用T函数将其转为文本类型。

第2个参数为数据透视表中任意一个单元格，本例为A3单元格的绝对引用格式。

第3、4个参数为取值条件组，第3个参数“分公司”为分类字段名称，第4个参数为分公司字段相应的数据项的值，本例中的“B23＆"分公司"”。

（3）获取各分公司C产品销售金额合计数。

在C29单元格输入如下公式，并将公式向下拖动填充至C31单元格，计算结果如图265-3所示。

图265-3 获取各分公司C产品销售金额合计数

=GETPIVOTDATA(T($C$28),$A$3,"品种",$A$28,"分公司",B29＆"分公司")

公式解析如下。

第1个参数为计算字段名称，本例引用数据透视表中C28单元格值“金额”，并必须用T函数将其转为文本类型。

第2个参数为数据透视表中任意一个单元格，本例为A3单元格的绝对引用格式。

第3、4个参数为取值条件组，第3个参数“品种”为分类字段名称，第4个参数为$A$28单元格的引用值“C产品”。

第5、6个参数为取值条件组，第5个参数“分公司”为分类字段名称，第6个参数为分公司字段相应的数据项的值，本例中的B29＆"分公司"。

（4）获取各分公司2012年3月2日各产品销售数量。

在 C36 单元格输入如下公式，并将公式向右向下拖动填充至 F38 单元格，计算结果如图 265-4所示。

图265-4 获取各分公司2012年3月2日各产品销售数量

=GETPIVOTDATA(T($B$34),$A$3,"品种",$B36,"分公司",C$35＆"分公司","日期",$A36)

公式解析如下。

第1个参数为计算字段名称，本例引用数据透视表中B34单元格引用值“数量”，并必须用T函数将其转为文本类型。

第2个参数为数据透视表中任意一个单元格，本例为A3单元格的绝对引用格式。

第3、4个参数为第一组取值条件，第3个参数“品种”为分类字段名称，第4个参数为$B36单元格的混合引用，值为具体的产品名称。

第5、6个参数为第二组取值条件，第5个参数“分公司”为分类字段名称，第6个参数为分公司字段相应的数据项的值，本例中的“C$35＆"分公司"”。

第7、8个参数为第三组取值条件，第7个参数为“日期”分类字段名称，第8个参数为$A36的混合引用格式，值为具体的日期值。

当参数引用的单元格是日期型数值时，被引用的日期数值的格式不一定需要与透视表中相应日期数据项格式相一致，但如果该参数值为用双引用号引起的日期形式的文本字符串时，该日期格式必须与透视表中相应的日期数据项格式一致。

在数据透视表函数中，对有关参数使用单元格绝对引用、相对引用和混合引用格式，可以实现数据透视表函数从数据透视表中动态地获取相应计算数值。

技巧266 自定义汇总方法下获取数据透视表数据

当用户需要获取采用“自定义”分类汇总方式生成的数据透视表分类汇总数值时，需要使用GETPIVOTDATA函数的特殊语法，其语法结构如下：

GETPIVOTDATA(pivot_table,"＜GroupName＞[＜GroupItem＞;＜FunctionName＞]data_field ")

（1）参数pivot_table，表示对数据透视表中任何单元格或单元格区域的引用，该信息用于决定哪个数据透视表包含要检索的数据。

（2）第 2个参数，"＜GroupName＞[＜GroupItem＞;＜FunctionName＞]＜data_field＞ "是一个文本字符串，它用引号括起来，描述了要汇总数据取值条件，其中：

＜GroupName＞，表示分组字段名称；

＜GroupItem＞，表示分组字段对应的数据项；

＜FunctionName＞，表示用于分类汇总的方法，包括“求和”、“计数”等；

＜data_field＞,表示取值字段名称，取值字段不只一个时，各字段之间需要用空格隔开。

整个公式可以理解为：

GETPIVOTDATA(透视表内任意单元格, “分类行字段名称[分类条件;分类方式]取值列字段名称组”)

（1）“取值列字段名称组”部分也可以放在“分类字段名称”之间，但需要用空格隔开。

（2）在“自定义”分类汇总方式下，用户使用由 Excel 提供的自动生成数据透视表函数公式工具，获取分类汇总数据时，直接生成的函数公式产生的结果为“#REF!”错误。

生成的错误公式为：

GETPIVOTDATA(pivot_table,"＜GroupName＞[＜GroupItem＞;data,＜FunctionName＞]data_field ")

此时，需要根据正确的函数语法公式，将错误公式中的“data”部分手工删除后才能得到正确数据。

应用举例如下。

图266-1左侧显示的是某单位POS机的刷卡清单，银行每天对该单位发生的所有刷卡金额汇总后，再扣除每笔50元的手续费，将资金汇入该单位企业账户。

图266-1 根据POS机刷卡明细数据创建的数据透视表

企业虽然每天有多笔刷卡交易，但入账金额只有一笔，为了准确快速做好资金核对工作，确保资金安全，用户可以借助数据透视表，并使用数据透视表函数编制如图266-2所示的汇总表，用于与银行对账单进行核对。

图266-2 应用透视表函数编制的“银行POS刷卡入账金额汇总表”

首先对POS机刷卡明细表创建的数据透视表，使用“求和”和“计数”两种自定义分类汇总方式，按天对POS机刷卡金额及笔数进行分类汇总，再应用数据透视表函数进行计算：

（1）计算刷卡金额。

在“银行卡入账金额”工作表C5单元格输入如下公式，并复制填充至C35单元格，计算每天的刷卡总金额：

=IF(COUNTIF(银行卡汇总!$B:$B,$B5)=0,,GETPIVOTDATA(银行卡汇总!$B$3,银行卡汇总!$B$3＆"["＆TEXT ($B5,"yyyy-m-d")＆";求和]"＆银行卡汇总!$D$3))

（2）计算刷卡笔数。

在“银行卡入账金额”工作表D5单元格输入如下公式，并复制填充至D35单元格，计算每天的刷卡总笔数：

=IF(COUNTIF(银行卡汇总!$B:$B,$B5)=0,,GETPIVOTDATA(银行卡汇总!$B$3,银行卡汇总!$B$3＆" ["＆TEXT($B5,"yyyy-m-d")＆";计数]"＆银行卡汇总!$D$3))

有了每天的刷卡汇总金额和刷卡笔数，就可以很容易地计算出每天刷卡手续费用合计及银行最终入账金额：

入账金额=每天刷卡金额合计-每天刷卡笔数×50。

技巧267 特殊的Excel 2000版数据透视表函数

Excel 2000开始新增了数据透视表函数，虽然Excel 2003中该函数的语法得到了修改或完善，并被Excel 2007继续沿用，但出于兼容性的要求，Excel 2007同时也保留了Excel 2000的语法结构，从而形成了另一种特殊语法用法。该函数在Excel 2000中的语法如下：

GETPIVOTDATA(pivot_table, name)

其中 pivot_table表示对数据透视表中任何单元格或单元格区域的引用，该信息用于决定哪个数据透视表包含要检索的数据。

name 参数是一个文本字符串，它用引号括起来，描述要汇总数据取值条件，可以是：＜data_field field1item1field2item2 ……fieldnitemn＞,或

＜data_field field1[item1]field2[item2] ……fieldn[itemn]＞

甚至可以进一步简化为：

＜data_field item1item2 ……itemn＞

整个公式可以理解为：

GETPIVOTDATA(透视表内任意单元格, “取值列字段名称组条件项1 条件项2 ……条件项n”)

该语法的优点在于公式比较简捷，缺点是语法中会出现多个参数条件罗列在一起，不便于使用者阅读和理解。

267.1 使用Excel 2000版数据透视表函数公式静态获取数据

图267-1是ABC公司 2012年3月份销售数据创建的数据透视表，现使用Excel 2000版的数据透视表函数静态地获取数据透视表的相关数据。

图267-1 ABC公司2012年3月份销售汇总透视表

（1）获取销售总金额。

在K17单元格输入Excel 2000版数据透视表函数公式，计算结果为248122。

=GETPIVOTDATA($A$2,"金额")

公式解析如下。

第1个参数，表示数据透视表中任意一个单元格，本例中为$A$2。

第2个参数，为取值条件文本字符串，本例中只有"金额"字段名称一个条件，表示只获取“金额”的合计数。

（2）获取江苏分公司销售总数量。

在K18单元格输入数据透视表函数公式，计算值为10500。

=GETPIVOTDATA($A$2,"数量 江苏分公司")

公式解析如下。

第1个参数，表示数据透视表中任意一个单元格，本例中为$A$2。

第2个参数，为取值条件文本字符串，本例中为“数量 江苏分公司”，其中“数量”为计算字段名称，“江苏分公司”为具体计算条件，该条件表示要求获取江苏分公司数量合计值。

取值条件文本字符串中，各条件值之间需要用空格隔开，各条件值可以相互变换位置。

（3）获取浙江分公司2012年3月2日销售金额。

在K19单元格输入数据透视表函数公式，计算结果为45039。

=GETPIVOTDATA($A$2,"金额 浙江分公司 2012-3-2")

公式解析如下。

第1个参数，表示数据透视表中任意一个单元格，本例中为$A$2。

第2个参数，为取值条件文本字符串，本例中为“金额 浙江分公司 2012-3-2”，其中“金额”为计算字段名称，“浙江分公司”和“2012-3-2”为具体计算条件，该条件表示要求获取浙江分公司2012年3月2日的金额合计值。

取值条件文本字符串中，日期格式必须与透视表中的日期格式一致。

（4）海南分公司2012年3月1日B产品的销售数量。

在K20单元格输入数据透视表函数公式，计算结果为600。

=GETPIVOTDATA($A$2,"数量 B产品 海南分公司 2012-3-1")

公式解析如下。

第1个参数，表示数据透视表中任意一个单元格，本例中为$A$2。

第 2个参数，为取值条件文本字符串，本例中为“数量 B产品 海南分公司 2012-3-1”，其中“数量”为计算字段名称，“B产品”、“海南分公司”、“2012-3-1”为具体计算条件，该表示要求获取海南分公司2012年3月1日B产品的数量值。

使用 Excel 2000版数据透视表函数可以简化函数表达式，但条件参数排列在一起，不便于理解，计算结果如图267-2所示。

图267-2 数据透视表函数计算结果

267.2 使用Excel 2000版数据透视表函数公式动态获取数据

图267-3是ABC公司 2013年3月份销售数据创建的数据透视表，现使用Excel 2000版的数据透视表函数动态地获取数据透视表的相关数据。

图267-3 ABC公司2013年3月份销售汇总透视表

使用Excel 2000版中的数据透视表函数公式同样可以实现动态获取数据透视表数据。

（1）获取销售总金额。

获取销售总金额的Excel 2000的数据透视表函数公式如下，计算结果为251443。

=GETPIVOTDATA($A$3,C5)

公式解析如下。

第1个参数为数据透视表中任意一个单元格，本例为A3单元格的绝对引用格式。

第2个参数为计算字段名称，本例中为C5单元格引用值“金额”。

（2）获取各分公司销售数量合计数。

在C24单元格输入如下公式，并将公式向下拖动填充至C26单元格，计算得到的值如图267-4所示。

=GETPIVOTDATA($A$3,$D$5＆" "＆$B24＆"分公司")

图267-4 获取各分公司销售数量合计数

公式解析如下。

第1个参数为数据透视表中任意一个单元格，本例为A3单元格的绝对引用格式。

第 2 个参数为取值条件字符串，其中$D$5 为计算字段名称，该单元格引用取值为“数量”；“$B24＆"分公司"”为各分公司名称，中间用文本连接符“＆”连接一个空格，形成一个动态取值条件字符串，值为“数量海南分公司”。

（3）获取各分公司C产品销售金额合计数。

在C30单元格输入如下公式，并将公式向下拖动填充至C32单元格，计算得到的值如图267-5所示。

=GETPIVOTDATA($A$3,$C$29＆" "＆$A$29＆" "＆B30＆"分公司")

图267-5 获取各分公司C产品销售金额合计数

公式解析如下。

第1个参数为数据透视表中任意一个单元格，本例为A3单元格的绝对引用格式。

第 2 个参数为取值条件字符串，其中$C$29 为单元格引用，计算值为计算字段名“金额”；“$A$29”为单元格取值，计算值为“C产品”；“$B30＆"分公司"”，为各分公司名称。各条件之间还需要使用文本连接符“＆”连接一个空格，形成一个动态取值条件字符串，值为“金额 C产品海南分公司”。

（4）获取各分公司2013年3月2日各产品销售数量。

在C37单元格输入如下公式，并将公式向右向下拖动填充至F39单元格，计算结果如图267-6所示。

=GETPIVOTDATA($A$3,$B$35＆" "＆$B37＆" "＆C$36＆"分公司"＆" "＆TEXT($A37,"yyyy-m-d"))

图267-6 获取各分公司2013年3月2日各产品的销售数量

公式解析如下。

第1个参数为数据透视表中任意一个单元格，本例为A3单元格的绝对引用格式。

第 2 个参数为取值条件字符串计算字段名称，其中：$B$35 为单元格绝对引用，值为计算字段名称“数量”，$B37 为单元格相对引用，值为各产品名称；C$36＆"分公司"为各分公司名称；TEXT($A37,"yyyy-m-d")，使用TEXT函数将A37单元格引用日期型取值转为与数据透视表中日期格式。各条件之间还需要使用文本连接符“＆”连接一个空格，形成一个动态取值条件字符串，值为“数量 A产品海南分公司 2013-3-2”。

在 Excel 2000版数据透视表函数中，当参数引用的单元格是日期型数值时，该日期格式必须与透视表中相应的日期数据项格式一致。

技巧268 数据透视表函数与其他函数联合使用

提取数据透视表数据，GETPIVOTDATA函数的参数除了使用常量和单元格引用以外，还允许引用其他函数计算的结果。数据透视表函数与其他函数联合使用，可以产生更为神奇的效果。

图268-1是由某公司各个分公司2013年2月份的部分销售数据所创建的数据透视表，如果用户希望了解销售量最大或最小的分公司的情况，而且结果不受数据透视表数据变动的影响，那么就需要运用到GETPIVOTDATA函数的参数支持内存数组的特性。

为了让公式简洁，先定义名称“Corp”，其公式如下：

=IFERROR(GETPIVOTDATA(T(透视表!$E$2),透视表!$A$1,"分公司",透视表!$A$2:$A$99),"")

图268-1 根据销售数据创建的数据透视表

公式中GETPIVOTDATA函数的第4个参数“透视表!$A$2:$A$99”使用了区域引用，这样公式可以生成一个内存数组，再使用IFERROR函数去除错误值后，可以得到内存数组：

{9200;"";"";"";"";"";"";"";"";"";"";"";10500;"";"";"";"";"";"";"";"";"";21500;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}

用户可以使用名称“Corp”进行需要的查询与统计。

（1）计算销售量最大的分公司，计算结果为“浙江分公司”：

=LOOKUP(2,1/(MAX(Corp)=Corp),$A$2:$A$33)

（2）计算销售量最小的分公司，计算结果为“海南分公司：

=LOOKUP(2,1/(MIN(Corp)=Corp),$A$2:$A$33)

（3）计算销售量最大的分公司C产品的销售金额，计算结果为78500：

=GETPIVOTDATA(T(D2),A1,$B$2,"C产品",$A$2,LOOKUP(2,1/(MAX(Corp)=Corp),$A$2:$A$33))

计算结果如图268-2所示。

虽然本技巧能够根据明细数据实时更新而动态变化，但只有在数据透视表的布局结构保持不变时，透视表函数公式才能正确地返回结果，否则将出现错误。

图268-2 与其他函数联合使用的结果

技巧269 同时引用多个字段进行计算

当计算需要涉及数据透视表中的多个字段时，数据透视表函数还可以同时引用多个字段名称进行计算，大大简化了计算公式。

图269是已创建好的数据透视表，要求计算销售量最小的分公司2013年2月1日D产品的销售价格，具体计算公式如下。

图269 引用多个字段数据进行计算

定义名称Corp，其他公式如下：

=IFERROR(GETPIVOTDATA(T(透视表!$E$2),透视表!$A$1,"分公司",透视表!$A$1:$A$100),"")

在H8单元格输入计算公式：

=PRODUCT(GETPIVOTDATA(D2:E2＆"",A1,$B$2,"D 产品",$A$2,LOOKUP(2,1/(MIN(Corp)=Corp), $A$1:$A$33),$C$2,DATE(2013,2,1))^{1,-1})

公式解析如下。

（1）使用 GETPIVOTDATA 函数根据计算条件，同时获取“金额”和“数量”两个字段的值，函数公式如下：

GETPIVOTDATA(D2:E2＆"",A1,$B$2,"D产品",$A$2,LOOKUP(2,1/(MIN(Corp)=Corp), $A$1:$A$33),$C$2,DATE (2013,2,1))

该公式的关键在于GETPIVOTDATA函数的第1个参数“D2:E2＆""”，该参数引用了包含“金额”、“数量”两个计算字段名称所在的单元格区域，在其他计算条件相同的情况下，可以同时获取两个计算字段的值，计算结果为{5976,900}。

当多个计算字段相邻时，可以直接连续引用该字段所在单元格区域；如果计算字段不相邻可以使用 OFFSET 函数、INDIRECT 函数进行间隔引用。

上述公式可以改为：

=GETPIVOTDATA(OFFSET(D2,,,,2)＆"",A1,$B$2,"D 产品",$A$2,LOOKUP(2,1/(MIN(Corp)=Corp),$A$1: $A$33),$C$2,DATE(2013,2,1))

或者

=GETPIVOTDATA(T(INDIRECT("r2c"＆COLUMN(D:E),)),A1,$B$2,"D 产品",$A$2,LOOKUP(2,1/(MIN(Corp)=Corp),$A$1:$A$33), $C$2,DATE(2013,2,1))

（2）使用PRODUCT函数，将GETPIVOTDATA函数计算得到的结果与{1,-1}进行幂计算，形成结构相除算式，最终计算结果为6.64。

技巧270 从多个数据透视表中获取数据

当计算涉及多个数据透视表时，数据透视表函数还可以从多个数据透视表中同时获取数据进行计算。

图270-1列示了某单位2013年1～3月份销售明细表，每个月包含一张销售明细表以及依据各月销售明细表创建的数据透视表。

图270-1 某单位2013年1～3月份分月销售明细表及汇总数据透视表

现要求在“汇总”工作表中动态地反映1～3月份各月每个产品的销售数量、金额的本月数及累计数，编制如图270-2所示的销售汇总统计表。

图270-2 销售汇总统计表

由于汇总数据分别位于“1 月”、“2 月”和“3 月”三个工作表中的不同数据透视表中，计算累计数就要求对多个数据透视表数据进行数据引用并计算汇总，具体公式设置如下。

（1）在B5单元格设置如下公式，并将公式复制填充至B9单元格，对C2单元格进行日期选择，计算出各产品的本月数量：

=SUM(IFERROR(GETPIVOTDATA($B$3＆"",INDIRECT(MONTH($C$2)＆"月!G3"),"品种",$A5),))

（2）在C5单元格设置如下数组公式，并将公式复制填充至C9单元格，用于计算各产品2013 年1～3月份累计数量：

=SUM(IFERROR(GETPIVOTDATA($B$3＆"",INDIRECT(ROW(INDIRECT("1:"＆MONTH($C$2)))＆"月!H5"),"品种",$A5),))

思路分析如下。

（1）使用GETPIVOTDATA函数计算累计数。

GETPIVOTDATA($B$3＆"",INDIRECT(ROW(INDIRECT("1:"＆MONTH($C$2)))＆"月!H5"),"品种",$A5)

该公式关键在于函数第2个参数，这一参数用于指明引用哪个数据透视表，可以是单元格引用，还可以是数组。本例中该参数根据C2单元所选日期，使用了多个函数计算得到一个动态数组，其中：

ROW(INDIRECT("1:"＆MONTH($C$2)))

该公式动态形成一个数组，计算结果为{1;2;3}

ROW(INDIRECT("1:"＆MONTH($C$2)))＆"月!H5"

用于分别引用“1月”、“2月”、“3月”工作表中的3个数据透视表的H5单元格，用以分别指定3个数据透视表，计算结果为{"1月!H5";"2月!H5";"3月!H5"}，最后用INDIRECT函数指定具体的引用值。

GETPIVOTDATA函数计算结果为：{1145.169018;1074.662293;1687.016881}，分别为1月份、2月份和3月份各产品数量的月合计数。

（2）使用IFEEOR函数去除计算过程中的错误值，再用SUM函数求和。

由于每月销售产品品种不同，有的月份会出现无某产品销售情况，这会导致 GETPIVOTDATA函数取值出出错，所以需要使用IFEEOR函数排错，即当出现错误时，取0值。最后用SUM函数求和。

该公式为数组公式，需要同时按下＜Ctrl＞+＜Shift＞+＜Enter＞三键结束公式输入。

（3）“金额”的计算公式与“数量”类似，只需将GETPIVOTDATA函数第1个参数引用的B3单元格值“数量”改为引用D3单元格的值“金额”即可。

D5单元格的公式如下：

=SUM(IFERROR(GETPIVOTDATA($D$3＆"",INDIRECT(MONTH($C$2)＆"月!H5"),"品种",$A5),))

E5单元格的公式如下：

=SUM(IFERROR(GETPIVOTDATA($D$3＆"",INDIRECT(ROW(INDIRECT("1:"＆MONTH($C$2)))＆"月!H5"),"品种",$A5),))

技巧271 应用数据透视表函数为排名评定星级

数据透视表函数与其他函数相结合，可以充分发挥出数据透视表灵活和快速的优势，同时还能 满足各种具体应用的需要。

图271是某企业2013年各月销售人员销售业绩的明细表，根据需要创建数据透视表，按月统计出销售人员的销售金额，并计算出各月销售人月的排名，现要求根据排名情况，为销售人员评定星级。星级评定标准为：月度第1名评为5星、2～4名评为4星、5～7名评为3星、8～10名评为2星、10名以后评为1星。

图271 为名次评定星级

问题分析：

数据透视表已经计算出每月排名情况，因此只要应用 GETPIVOTDATA 函数获取每月销售人员的名次，再利用LOOKUP函数按星级标准返回相应的星级数即可解决问题。

在“星级”工作表中的F2单元格中输入如下公式，将公式复制并填充至F137单元格：

=IFERROR(LOOKUP(GETPIVOTDATA("名次",$A$1,"日期",LOOKUP("々",A$2:A2),"业务员",B2),{1,2,5,8,11},{"★★★★★","★★★★","★★★","★★","★"}),"")

思路解析如下。

（1）用GETPIVOTDATA函数返回销售人员名次值。

GETPIVOTDATA("名次",$A$1,"日期",LOOKUP("々",A$2:A2),"业务员",B2)

该公式中，GETPIVOTDATA函数第4个参数使用了LOOKUP函数，动态填充“星级”工作表A列中“日期”字段中的空值单元格，以确保透视表函数计算正确。

（2）用LOOKUP函数，根据GETPIVOTDATA函数返回的销售人员名次的值，返回相应的星级数。

LOOKUP(星级数,{1,2,5,8,11},{"★★★★★","★★★★","★★★","★★","★"})

（3）用IFERROR函数进行容错处理。

技巧272 应用数据透视表函数根据关键字取值

数据透视表函数不能直接使用关键字作为参数，但运用其参数支持内存数组的特性，可以实现根据关键字检索数据透视表数据的目的。

图272是一份费用凭证清单，清单中的会计科目是由总账科目和明细科目组合而成，根据这份清单创建了一张费用汇总数据透视表，要求使用数据透视表函数直接计算出“营业费用”、“管理费用”和“财务费用”三个总账科目的合计金额。

图272 根据费用凭证记录创建费用汇总数据透视表

问题分析：

透视表中的会计科目是由总账科目和明细科目组合而成，常规的做法是在数据源表中添加辅助列，将总账科目与明细科目分开后，再创建数据透视表，或者是通过手动分组的方法，根据总账科目重新进行分组。

而数据透视表函数与其他函数组合应用，可以在对数据源和数据透视表不进行任何改动的情况下，方便地计算出结果。

在“透视表”工作表中的E4单元格，输入如下数组公式，并同时按下＜Shift+Ctrl+Enter＞组合键，再将公式复制并填充至E6单元格：

{=SUM(IFERROR(GETPIVOTDATA("金额",$A$3,$A$3,IF(FIND($D4,$A$4:$A$40),$A$4:$A$40)),))}

该公式使用了GETPIVOTDATA函数，函数第4个参数，使用了FIND函数在A4:A40单元格区域查找“费用科目”中D4单元格的关键字，再用IF函数将查找结果转为具体会计科目及错误值组成的数组，计算结果如下：

{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VA LUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VAL UE!;#VALUE!;"营业费用/安全评价费";"营业费用/仓储费";"营业费用/港务费";"营业费用/宣传费";"营业费用/运杂费";"营业费用/租赁费";#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

在用FIND函数查找关键字时，所引用的区域的行数应该大于等于透视表的区域的行数，否则将会遗漏数据，造成计算结果不正确。

GETPIVOTDATA函数根据这一参数计算的结果，进一步计算得到各种费用项目的金额，费用项目为错误值时，透视表函数相应返回错误值，计算结果如下：

{#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;# REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;18000;265629.82;8361.5;5757;321873.47;15000;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!}

再用IFERROR函数去除错误值，最后用SUM函数求和计算出合计金额。

技巧273 应用数据透视表函数制作进货单

数据透视表函数还可以根据给定的条件从数据透视表中筛选出特定数据。

图273-1是一份商品进货清单，根据进货清单创建了进货单汇总数据透视表（透视表1），同时创建了进货单号透视表（透视表2）。

图273-1 根据进货记录创建数据透视表

要求：在“进货单”工作表中编制进货单，实现根据进货单号从数据透视表中筛选出相应的进货汇总记录的功能，如图273-2所示。

图273-2 进货单

问题分析如下。

（1）这是一个透视表函数应用于透视表数据筛选的问题。

（2）从透视表中筛选出的记录，需要填制到特定格式的表单中。

具体制作如下。

定义名称S_number，用于在“进货单”工作表的E3单元格中设置不重复单号数据有效性，公式如下：

=OFFSET(透视表!$H$3,1,,COUNTA(透视表!$H:$H)-2)

为了简洁公式，定义名称number，用于填充透视表“进货单号”字段中的空值单元格，形成内容连续的内存数组，公式如下：

=LOOKUP(ROW(透视表!$A$5:$A$20),IF(透视表!$A$5:$A$20＜＞"",ROW(透视表!$A$5:$A$20)),透视表!$A$5:$A$20)

在“进货单”工作表的A5单元格中设置如下公式，并将公式复制填充至A5:E9单元格区域：

=INDEX(透视表!B:B,SMALL(IF(IFERROR(GETPIVOTDATA(T(透视表!$D$4),透视表!$A$3,透视表!$A$4,IF(number=$E$3,number),"名称及规格",透视表!$B$5:$B$11,"单位",透视表!$C$5:$C$20),),ROW(透视表!$A$5:$A$20),100000),ROW(1:1)))

公式解析如下。

（ 1 ）该公式使用了 INDEX(B:B,SMALL(IF(条件,ROW(单元格区域),100000),ROW(1:1))这种常用的筛选公式。

（2）IF函数的判断条件核心是由GETPIVOTDATA函数返回的数组，公式如下：

GETPIVOTDATA(T(透视表!$D$4),透视表!$A$3,透视表!$A$4,IF(number=$E$3,number),"名称及规格",透视表!$B$5:$B$11,"单位",透视表!$C$5:$C$20)

公式第4个参数，使用了IF函数和定义的名称number，返回与E3单元格选定的单号相一致的“进货单号”的数组，具体值为：

{FALSE;FALSE;FALSE;FALSE;"A000004";"A000004";"A000004";FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

透视表函数经IFEEROR进行错误值处理后，返回数组如下：

{0;0;0;0;200;56;20;0;0;0;0;0;0;0;0;0}

该数组作为IF函数的判断条件，当条件值不为0时，返回ROW(透视表!$A$5:$A$20)产生单元格所在行的行数值，当条件值为0时，返回100000这样一个足够大的值，用于INDEX返回得到一个空单元格的值，用于容错处理。

{100000;100000;100000;100000;9;10;11;100000;100000;100000;100000;100000;100000;100000;100000;100000}

（3）SMALL函数将IF函数返回的数组值从小到大排列，并逐一返回满足条件的值所在行号，最用于传递给 INDEX 函数得到最终的查找结果。

最后在A2单元格，使用VLOOKUP函数返回进货单号对应的日期，在B10单元格用SUM函数求得合计金额。

至此，“进货单”中的公式全部设置完毕。当用户在 E3 单元格选定相应的进货单号后，就可以从透视表中筛选出相应的进货汇总记录。

技巧274 计算分类百分比

Excel 2007数据透视表只能实现“占同列数据总和百分比”或“占总和的百分比”，而不能实现“分类百分比”。所谓分类百分比是指每一明细分类项占其上一父级分类汇总项的百分比。而使用Excel 2000版数据透视表函数语法可以轻松实现这一计算功能。

图274-1是根据某企业2013年第三季度销售情况制作的数据透视表，表中反映出第三季度每个月销售金额汇总情况，以及各产品在第三季度销售总金额中所占的比重。

图274-1 根据销售数据创建数据透视表

实际上用户可能同时希望计算出每种产品销售金额占当月销售总额的比重，具体的计算方法如下。

在F4单元格输入如下公式，将公式复制并填充至F16单元格：

=PRODUCT(GETPIVOTDATA($A$3,$C$3＆" "＆LOOKUP("々",A$3:A4)＆" "＆T(OFFSET($B4,,{0,10})))^{1,-1})

思路解析：

要计算每种产品销售金额占当月销售总金额中的比重，实际就是要计算单项占小计的比重。

根据数据透视表布局的特点，“日期”字段中包括空值，各月的汇总项名称与月汇总项对应的“品种”字段值也为“空值”。具体分析如下。

（1）使用Excel 2000版透视表函数公式获取每种产品销售金额及相应各月分类汇总金额。

GETPIVOTDATA($A$3,$C$3＆" "＆LOOKUP("々",A$3:A4)＆" "＆T(OFFSET($B4,,{0,10})))

该公式：

第1个参数为数据透视表中任意单元格引用，本例中值为$A$3。

第2个参数为计算条件字符串，其中：

“$C$3”为计算字段名称，计算结果为“金额”计算字段名称。

LOOKUP("々",A$3:A4)，用于填充“日期”字段中的空值。

T(OFFSET($B3,,{0,10}))，该部分公式通过OFFSET函数的第3个数组参数，可以计算得到一个数组值，用于分别动态引用B列中的具体品种名称和空值，用于分别获取各品种和分类汇总值。

（1）OFFSET($B3,,{0,10})，该函数的第3个参数中的偏移10，是用于取L列的空值，该值可以使用其他空列的对应列数代替。

（2）该公式还需要使用T函数将OFFSET函数计算的数组值进行文本转换。

F4单元格公式计算结果为{27795,61427}，分类汇总行所在F7单元格公式计算结果为{61427,61427}

（2）用PRODUCT函数进行计算得到分类百分比结果。

=PRODUCT(GETPIVOTDATA计算得到的内存数组结果^{1,-1})

使用PRODUCT函数，将GETPIVOTDATA函数计算得到的结果与{1,-1}进行幂计算，形成相除结构算式，从而得到各品种销售金额除以各月销售总金额的结果，即得到分月的分类百分比结果，计算结果如图274-2所示。

图274-2 分类百分比计算结果