9.4　自动汇总方法下动态获取数据透视表数据

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

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

img204b

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

9.4.1　使用基本函数公式动态获取数据

1．获取销售总金额

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

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

公式解析：

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

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

2．获取各分公司销售数量合计数

在C23单元格中输入如下公式，并将公式向下拖动填充柄至C25单元格，计算得到的值如图9-10所示。

img204c

图9-10　获取各分公司销售数量合计

=GETPIVOTDATA(T(D$5),$A$3,"分公司”，B23&”分公司”）

公式解析：

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

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

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

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

在C29单元格中输入如下公式，并将公式向下拖动填充柄至C31单元格，计算得到的值如图9-11所示。

=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单元格，计算得到的值如图9-12所示。

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

img205a

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

img205b

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

公式解析：

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

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

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

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

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

注意img01

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

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

9.4.2　使用Excel 2000版函数公式动态获取数据

使用Excel 2000版中的数据透视表函数公式同样可以实现动态获取数据透视表数据，仍以图9-9所示数据透视表数据为例。

1．获取销售总金额

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

=GETPIVOTDATA($A$3,C5)

公式解析：

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

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

2．获取各分公司销售数量合计数

在C47单元格中输入如下公式，并将公式向下拖动填充柄至C49单元格，计算得到的值如图9-13所示。

=GETPIVOTDATA（$A$3，$D$5&" "&$B4 7&"分公司"）

公式解析：

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

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

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

在C53单元格中输入如下公式，并将公式向下拖动填充柄至C55单元格，计算得到的值如图9-14所示。

=GETPIVOTDATA($A$3,$C$52&" "&$A$52&" "&B53&"分公司"）

img206a

图9-13　获取各分公司销售数量合计数

img206b

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

公式解析：

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

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

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

在C60单元格中输入如下公式，并将公式向各向下拖动填充柄至F62单元格，计算得到的值如图9-15所示。

=GETPIVOTDATA($A$3,$B$58&" "&$B60&" "&C$59&"分公司"&""&TEXT($A60,"yyyy-m-d"))

img207

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

公式解析：

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

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

注意img01

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