私信回复关键词【福利】，获取丰富办公资源，助你高效办公早下班！

Hello，大家好，我是最近在研究数据透视表的小爽~

最近，我收到了一个学员的求助：

简单归纳一下，这个问题就是：

如何能让数据透视表的数据源区域，自动进行扩选？

让数据能够自动扩展，确实非常复杂，要用到函数公式。

比如下面的函数方法~

以销售数据表为例，现在我们需要新增数据，让透视表实时更新。

01

利用 Offset 和 Counta 函数

我们先看看具体做法：

❶编写公式。

=OFFSET($A$1,,,COUNTA($A:$A),8)

▲左右滑动查看

❷定义名称管理器。

点击【公式】-【名称管理器】-「引用位置」。

粘贴上面的公式-名称为「数据源」。

=OFFSET(销售数据表!$A$1,,,COUNTA(销售数据表!$A:$A),8)

▲左右滑动查看

❸创建数据透视表。

插入数据透视表，表区域为上面的定义的名称——「数据源」。

❹实时更新。

这里我们用到两个函数——Offset 函数和 Counta 函数。

Counta 函数，能够计算非空单元格的个数；

COUNTA($A:$A)也就是计算 A 列中非空单元格的个数。

当我们在 A 列中往后添加数据，Counta 函数就会实时更新非空单元格的个数。

Offset 函数，能够偏移单元格。

基本语法如下：

=OFFSET(起始单元格，下移 N 行，右移 N 列，[扩展 N 行]，[扩展 N 列])

就好比我去找小 E 玩，我从起点出发。

向下走 2 行，再向右走 2 列，就到了小 E 家，小 E 家在 3 行 4 列的位置上。

公式：

=OFFSET($A$1,,,COUNTA($A:$A),8)

▲左右滑动查看

案例中该公式的含义就是：

COUNTA 函数去获取 A 列非空单元格有 N 个。

OFFSET 函数收到 COUNTA 的数字后，

在 $A$1 单元格中直接扩选 N 行 8 列，8 是整个表的字段数。

到这里，大家应该知道这个公式的由来了吧~

不过我猜！

也可能有小伙伴会这样想：

这个函数公式好复杂啊，Offset 函数又是啥？我还是个连 Vlookup 函数都不会用的小！盆！友！哭料！

别担心，接下来我们就来见证「一个神奇的做法」~

如果你觉得你现在的能力，还不能拿下第 1 种方法，那请跳过直接看第 2 个方法。

第二种方法：这个表格，只要「穿上件衣服」，就能自动扩选区域，变身成为超人！

我们一起来看看吧~

02

智能表格

我们先来看看它的具体操作~

❶插入智能表。

点击【插入】选项卡-【表格】-勾选【表包含标题】-【确定】。

❷创建透视表。

点击【插入】选项卡-【数据透视表】。

刚刚我们的智能表的名称为「表 1」，所以创建数据源区域的时候，数据源区域为「表 1」。

❸动态更新数据源。

此时添加新数据，数据透视表右键刷新，就能实时更新数据源啦~

这里我们用到了智能表格，肯定会有小伙伴疑惑了：「智能表格究竟是怎么生成的？」

接下来，我们一一剖解它！

智能表格在哪？

普通表格区域，怎么才能变成智能表格？

这里介绍三种方法~

◆ 方法一：直接套用表格样式 ◆

点击【开始】选项卡-【套用表格样式】。

◆ 方法二：直接插入表格 ◆

这个也就是我们刚刚操作中所演示的方法~

全选表格，点击【插入】-【表格】。

◆ 方法三：快捷键法 ◆

转换为智能表格的快捷键有：【Ctrl+T】/【Ctrl+L】。

我们可以这样记忆：

【Ctrl+T】——【control+Table】——控制表格，也就是将普通表格变身为智能表！

【Ctrl+L】——【control+List】——控制列表，也就是将普通表格变身为智能表！

普通表格变身智能表格的做法，你 Get 到了吗~

智能表格还有一个神奇的功能就是扩展表格区域！

在智能表格下方一行，直接输入数据，表格区域就会自动扩展至新的一行。

这就是表格的自动扩展属性。

数据透视表就是利用这一点，使数据源区域达到动态更新；

我们就是用扩展表格区域，来实时更新数据源的~

好啦，简单总结一下动态扩选区域的两种方法：

❶ 利用 Offset 和 Counta 函数——常用的动态扩选区域的公式套路；

❷ 智能表格——套上外衣，变身超人，可以自动扩选区域。

还有任何疑问的小伙伴，欢迎留言互动！

看完文章，觉得有帮助，可以转评赞三连~笔芯~

私信回复关键词【福利】，获取丰富办公资源，助你高效办公早下班！