Excel2010中应用数据透视表函数从多个数据透视表中汇总数据

35.1 从多个数据透视表中获取数据

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

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

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

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

图35-2销售汇总统计表

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

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

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

",$A5),))

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

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

思路分析:

◆ 使用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月份各产品数量的月合计数。

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

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

注意：该公式为数组公式，需要同时按下++三键结束公式输入

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),))

本篇文章节选自《Excel 2010数据透视表大全》ISBN：9787115300232 人民邮电出版社