9.9　数据透视表函数综合应用示例

9.9.1　应用数据透视表函数为名次评定星级

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

示例9.6　应用数据透视表函数为排名评定星级

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

img213a

图9-22　为名次评定星级

问题分析：

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

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

img213b

思路解析：

（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函数进行容错处理。

9.9.2　应用数据透视表函数根据关键字取值

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

示例9.7　应用数据透视表函数根据关键字取值

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

img214a

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

问题分析：

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

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

在“透视表”工作表的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函数将查找结果转为具体会计科目及错误值组成的数组，计算结果如下：

img214b

注意img01

在用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函数求和计算出合计金额，计算结果如图9-24所示。

img215a

图9-24　最后计算得到的各项费用总账科目的结果

9.9.3　应用数据透视表函数制作进货单

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

示例9.8　应用数据透视表函数制作进货单

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

img215b

图9-25　根据进货记录创建数据透视表

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

问题分析：

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

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

img216

图9-26　进货单

具体制作如下。

步　骤1img01

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

=OFFSET（透视表！$H$3,1,,COUNTA（透视表！$H:$H）-2）

步　骤2img01

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

=LOOKUP（ROW（透视表!$A$5:$A$20）,IF（透视表!$A$5:$A$20<>"",ROW（透视表！$A$5:$A$20））,透视表！$A$5:$A$20）

步　骤3img01

在“进货单”工作表的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时，返回100 000这样一个足够大的值，用于INDEX返回得到一个空单元格的值，用于容错处理。

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

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

步　骤4img01

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

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

9.9.4　计算分类百分比

Excel 2010新增了计算分类百分比的功能，所谓百分比是指每一明细分类项占其上一父级分类汇总项的百分比。而使用Excel 2000版数据透视表函数语法也可以轻松实现这一计算功能。

示例9.9　使用数据透视表函数计算分类汇总百分比

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

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

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

img217

图9-27　根据销售数据创建数据透视表

=PRODUCT(GETPIVOTDATA($A$2,$C$2&""&LOOKUP("々",A$3:A3)&""&T(OFFSET($B3,,{0,4})))^{1,-1})

思路解析：

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

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

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

GETPIVOTDATA($A$2,$C$2&""&LOOKUP("々",A$3:A3)&""&T(OFFSET($B3,,{0,4})))

公式解析：

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

第2个参数为计算条件字符串，其中，“$C$2”为计算字段名称，计算结果为“金额”计算字段名称。LOOKUP("々",A$3:A3)用于填充“日期”字段中的空值。

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

注意img01

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

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

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

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

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

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

img218

图9-28　分类百分比计算结果

提示img02

Excel 2010可以在【数据透视表工具栏】的【设计】选项卡中单击【报表布局】→【重复所有项目标签】命令，用以填充日期字段的空值，这样计算分类百分比的公式可以进一步简化为：

=PRODUCT(GETPIVOTDATA($A$2,$C$2&" "&A3&”"&T(OFFSET($B3,,{0,4})))^{1,-1})