用微信扫码二维码

分享至好友和朋友圈

点击蓝字【秋叶 Excel】

发送【交流】

立即进【秋叶同学会】交流 Excel！

本文作者：明镜在心

本文编辑：竺兰

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

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

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

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

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

比如下面的函数方法~

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

利用 Offset 和 Counta 函数

我们先看看具体做法：

❶ 编写公式。

❷ 定义名称管理器。

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

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

❸ 创建数据透视表。

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

❹ 实时更新。

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

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

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

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

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

基本语法如下：

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

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

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

公式：

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

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

不过我猜！

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

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

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

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

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

我们一起来看看吧~

智能表格

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

❶ 插入智能表。

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

❷ 创建透视表。

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

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

❸ 动态更新数据源。

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

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

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

智能表格在哪？

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

这里介绍三种方法~

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

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

方法二：直接插入表格

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

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

方法三：快捷键法

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

我们可以这样记忆：

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

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

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

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

更详细的智能表格介绍，请戳

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

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

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

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

为了帮助职场的 Excel 困难户提升工作效率，用数据分析技能、图表思维提升竞争力，咱们秋叶团队推出了《3 天 Excel 集训营》课程！

大神带你学习表格飞速排版、数据高效整理、图表美化设计……！！和志同道合的小伙伴一起交流进步~

秋叶《3 天 Excel 集训营》

但只要你是秋叶 Excel 的读者

就能限时1 元秒杀！！

仅需 3 天

你就可能成为 Excel 高手！

赶紧扫码抢课吧！！

优惠名额有限，先到先得！

现在扫码报名

还能免费领《35 个函数使用手册》！

最后，感谢你阅读今天这篇文章，下面是 1 个抽奖链接，7 月 4 日中午 11 点 40 分自动开奖，奖品是包邮赠送的一本《秒懂 Excel》图书。

中奖的小伙伴请在5 天内根据小程序给出的二维码添加我的微信，发送收货地址及信息，我们会安排寄送。

遇到有价值的文章

点点在看支持一下 !

动动小手

分享给朋友~

特别声明：以上内容(如有图片或视频亦包括在内)为自媒体平台“网易号”用户上传并发布，本平台仅提供信息存储服务。

Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services.