理解数据透视表的GetPivotData函数

走过路过不要错过

当我们在单元格中输入“=”，然后单击数据透视表中的某个数据时，可能会看到Excel自动帮我们生成一个很长的公式。例如下图：

这里出现的getpivotdata函数，是单击数据透视表中的数据时，在公式中自动生成的。

在引用数据透视表中的数据时，如果不想看到这一长串公式，可以关闭getpivotdata。与直接的单元格引用相比，getpivotdata函数比较长，看上去比较复杂，但当数据透视表布局变化时，getpivotdat函数仍然能按照指定条件正确取值。

1、getpivotdata函数使用示例

getpivotdata函数的功能是，从数据透视表中按条件取值。

（1）在B11单元格输入“=”后，单击B7单元格，自动生成公式：

=GETPIVOTDATA("金额",$A$1,"月份","5月","商品","打印机")

从数据透视表取值，必须要告诉excel从哪个数据透视表取值，以及取哪个字段的值。本例中数据透视表的数据分布在A1:E9区域，在getpivotdata函数中，“$A$1”足以指明数据透视表的位置。该公式的含义是，在A1单元格的数据透视表中，提取“月份”为“5月”，“商品”为“打印机”的“金额”字段数值。

（2）在B11单元格输入“=”后，单击E7单元格，自动生成公式：

=GETPIVOTDATA("金额",$A$1,"月份","5月")

E7单元格的数据是5月份各商品的销售总额。getpivotdata函数提取值的条件只有一个，即“月份”为“5月”。

（3）在B11单元格输入“=”后，单击B9单元格，自动生成公式：

=GETPIVOTDATA("金额",$A$1,"商品","打印机")

B9单元格的数据是“打印机”的销售总额。getpivotdata函数提取值的条件只有一个，即“商品”为“打印机”。

（4）在B11单元格输入“=”后，单击E9单元格，自动生成公式：

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

getpivotdata函数提取的是所有商品在所有月份的销售总额，没有条件限制。

（5）从上面的（1）~（4）示例中可以看到，自动生成的getpivotdata公式中的条件不是单元格引用，而是双引号括起来的文本。这意味如果向下或向右复制公式时，getpivotdata返回的值是固定的。例如下图中，返回的始终是“1月”“手机”的销售额。

因此，在自动生成的getpivotdata公式中需要做些修改。如下图所示，将公式中的条件1、条件2修改为单元格引用$B12、C$11。

2、关闭getpivotdata函数

单击数据透视表中的数据时，在公式中自动生成的getpivotdata函数。如果不想看到这一长串公式，可以关闭getpivotdata。

选中数据透视表中的任意一个单元格，会出现【数据透视表分析】选项卡。

如果【数据透视表分析】-【选项】下拉列表中的“生成GetPivotData”被勾选，公式中就会自动出现getpivotdata函数。

单击“生成GetPivotData”，取消勾选，再单击数据透视表中的数据时，就是普通的单元格引用形式。

勾选该选项前后，公式的变化：

3、与直接单元格引用比较

如下图所示，要求从数据透视表获取“5月”销售的“打印机”的销售额。

C11单元格使用getpivotdata函数，C12单元格使用简单的公式“=B7”。

C11、C12单元格的值都能够获取5月打印机销售额“960”。但C11单元格使用getpivotdata函数，公式比较长。这样看来似乎没有必要使用getpivotdata函数，使用普通的单元格引用就可以。

如果我们把数据透视表按“总计”升序排序，就会发现C11单元格的getpivotdata函数仍然返回5月打印机销售额“960”，而C12单元格返回值变成1月打印机销售额“0”。

这是getpivotdata函数与直接单元格引用想比的优势。当数据透视表的布局变化时，getpivotdata函数仍然能依照指定条件正确取值。

点个在看你最好看