数据透视表学习-08:如何制作数据透视表动态数据源？

2020-04-18 本文已影响0人会挽雕弓射天狼

什么是动态数据源，所谓动态，是指透视表引用的数据区域会随着其范围的扩大或缩小自动变更。

常用的方法有：功能表法、函数法、PowerQuery、SQL和VBA。

1 功能表法

先来说下功能表法，这个方法最简单。

首先选中数据源任意单元格，按下快捷键<Ctrl+L>或<Ctrl+T>弹出【创建表】对话框，勾选【表包含标题】，单击【确定】按钮，即可将当前的数据列表转换为【功能表】

然后单击【功能表】任意单元格创建透视表即可。

当数据源的记录有增加时，刷新透视表即可获取最新数据。

这种方法也有不完美的地方：通过复制粘贴的方式新增的数据，并不会被自动纳入功能表的范围，也就不能被自动添加到透视表数据源中。

假设数据源所在的工作表表名为“销售表”。模样如下图所示。

使用定义名称+函数的方法。

先看动画演示操作过程。

文字描述操作步骤及相应公式如下：

在【公式】选项卡下单击【名称管理器】按钮，打开【名称管理器】对话框。单击【新建】按钮，弹出【新建名称】对话框，在名称管理框中输入“data”，在【引用位置】文本框中输入如下公式。

=OFFSET(销售表! $A$ 1,0,0,COUNTA(销售表! $A:$ A),COUNTA(销售表! $1:$ 1))

单击【确定】按钮关闭【新建名称】对话框，单击【关闭】按钮关闭【名称管理器】对话框。

在【插入】选项卡下单击【数据透视表】图标，打开【创建数据透视表】对话框，在【表/区域】编辑框中输入公式：=data

data是我们刚刚在名称管理器创建的名称，你也可以换做其他名称，只需要和名称框名称保持一致即可。

然后解释一下OFFSET函数。

OFFSET就像个小游戏，意思是从一个据点(第1个参数)，通过行列移动(2、3参数)奔袭到另外一个据点，然后自由决定是否需要扩张据点的行列范围(4、5参数可选)

在本例中，OFFSET的第2、3参数均为0，意思是据点就留在A1单元格别动，第4和第5参数分别使用COUNTA计算A列和第一行非空单元格的数量，然后以此计算结果将据点分别向下和向右扩张，也就是获取当前数据表的数据区域。