湖北
点击蓝字【秋叶 Excel】
发送【交流】
立即进【秋叶同学会】交流 Excel！
本文作者：明镜在心
本文编辑：竺兰
Hello，大家好，我是最近在研究数据透视表的小爽~
最近，我收到了一个学员的求助：
简单归纳一下，这个问题就是：
如何能让数据透视表的数据源区域，自动进行扩选？
让数据能够自动扩展，确实非常复杂，要用到函数公式。
比如下面的函数方法~
以销售数据表为例，现在我们需要新增数据，让透视表实时更新。
利用 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 个方法。
第二种方法：这个表格，只要「穿上件衣服」，就能自动扩选区域，变身成为超人！
我们一起来看看吧~
智能表格
我们先来看看它的具体操作~
❶ 插入智能表。
点击【插入】选项卡-【表格】-勾选【表包含标题】-【确定】。
❷ 创建透视表。
点击【插入】选项卡-【数据透视表】。
刚刚我们的智能表的名称为「表 1」，所以创建数据源区域的时候，数据源区域为「表 1」。
❸ 动态更新数据源。
此时添加新数据，数据透视表右键刷新，就能实时更新数据源啦~
这里我们用到了智能表格，肯定会有小伙伴疑惑了：「智能表格究竟是怎么生成的？」
接下来，我们一一剖解它！
智能表格在哪？
普通表格区域，怎么才能变成智能表格？
这里介绍三种方法~
方法一：直接套用表格样式
点击【开始】选项卡-【套用表格样式】。
方法二：直接插入表格
这个也就是我们刚刚操作中所演示的方法~
全选表格，点击【插入】-【表格】。
方法三：快捷键法
转换为智能表格的快捷键有：【Ctrl+T】/【Ctrl+L】。
我们可以这样记忆：
【Ctrl+T】——【control+Table】——控制表格，也就是将普通表格变身为智能表！ 【Ctrl+L】——【control+List】——控制列表，也就是将普通表格变身为智能表！
智能表格还有一个神奇的功能就是扩展表格区域！
这就是表格的自动扩展属性。
数据透视表就是利用这一点，使数据源区域达到动态更新；
我们就是用扩展表格区域，来实时更新数据源的~
更详细的智能表格介绍，请戳
好啦，简单总结一下动态扩选区域的两种方法：
❶ 利用 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.
