揭开Excel中神奇的动态引用函数Offset的庐山真面目

【txb】发表时间:2021-11-25 23:19:49 | 栏目:技术

 

前段时间给一家税务机关培训Excel的时候,学员小A课后向我咨询了一个工作中遇到的问题。小A有一个每月税收预测的数据表格,他需要在每个月月初的时候填上对应的数据之后上报给领导,这个表格的标题“1-?月预测数据”,现在是他每个月手工填上对应的月份数字。

图表的制作也需要手工选择数据源的范围,比如3月份制作的时候,他只能选择A2:B5的范围来制作图表,到4月份再制作图表的时候,他就需要重新修改一下数据源。

小A的问题是有没有方法让这个表格的数据源是动态的,当他输入了4月的预测数据之后,表格标题会变成1-4月,图表的数据源也会变成1-4月的数据。

要实现小A的需求,就需要引入一个动态数据源的概念,接下来我们通过2个函数来解决小A的问题。

1、统计非空单元格个数:COUNTA函数

首先,我们来解决标题“1-?月”的动态变化需求。我们只需要检测一下在B3:B14中有多少个非空单元格,就可以知道最终的月份了。

这时我们就可以利用COUNTA这个统计非空单元格个数的函数了,我们先来了解一下COUNTA函数的语法:

统计非空单元格个数:COUNTA(区域)

公式如下:

="1-"&COUNTA(B3:B14)&"月预测数据"

当我们输入4月份的数据的时候,标题会自动变更为“1-4月预测数据”。

2、动态引用:OFFSET函数

解决完动态标题的问题之后,我们再来解决图表的动态数据源。要实现图表的动态数据源,我们需要利用Excel中神奇的动态引用函数OFFSET来实现这个需求。

我们先来了解OFFSET函数的语法:

动态引用:OFFSET(基点,偏移行[不含基点],偏移列[不含基点],引用的高度[含基点],引用的宽度[含基点])

以指定的基点作为参照系,通过给定偏移量得到新的引用。返回的引用可以是一个单元格或单元格区域,并可以指定返回的行数或列数。

我们以这个公式为例解释一下

=offset(a7,2,2,5,3)

第一个参数是a7,也就是起点(基点)定在了a7单元格,就好比你站立的位置在a7。

第二个参数是2,相对于a7向下移动了2行,那就到了a9。

第三个参数是2,相对于a9向右移动了2列,那就到了c9。

第四个参数是5,从偏移后的起点(基点)c9开始,总共包含5行。

第五个参数是3,从偏移后的起点(基点)c9开始,总共包含3列。

最后得到数据源就是c9:e13这个区域。

在图表的案例中,公式就是:

标签:=OFFSET(税收预测!$A$3,0,0,COUNTA(税收预测!$B$3:$B$14),1)

数据:=OFFSET(税收预测!$B$3,0,0,COUNTA(税收预测!$B$3:$B$14),1)

标签起点是A3,按F4键把A3地址锁定变成$A$3,向下移动0行,向右移动0列,那起点就在A3,总行数用COUNTA统计$B$3:$B$14中包含多少个非空单元格,总共1列。

数据的起点是B3,其他参数都相同。

有了动态数据源之后,要怎样将其设置为图表的数据源呢?

首先我们需要将这两个函数进行命名,之后再设置为图表数据源。点击“公式-定义名称”

在弹出“新建名称”的对话中输入名称,将公式粘贴到引用位置。

再点击鼠标右键打开图表的“选择数据”对话框,点击“水平(分类)轴标签”中的“编辑”功能。

在弹出的对话框中将!后面的地址修改为“标签”。

按照同样的方法修改数值轴,选择“图表项(序列)”中的“编辑”,将序列值!后面的地址修改为“数据”。

以后再更新数据,图表就可以自动更新了。

 

 

 

 阅读全文

逆风飞翔

txb

生命中我们要学会坚强

tags