Offset函数常用套路集合

Offset函数在实际工作中有很多应用，我们经常用其他函数公式给Offset的参数赋值，生成动态引用，从而实现丰富多彩的功能。

今天就来总结一下Offset都有哪些用法。

1、分组求和

如下图所示，在A1:L6的区域内有每个月的销售数据，我们需要将每行数据按照季度汇总，也就是每三个单元格作为一组进行求和。

我们在M2单元格中输入以下公式：

=SUM(OFFSET($A2,,(COLUMN()-COLUMN($M$1))*3,,3))

然后向右、向下拖拉复制公式填充，这样就完成了所有的求和了。

2、间隔取值

还是上面的例子，假如我们想要取一、四、七、十这几个月的值该怎么做呢？

如果是一次取一个单元格的值的话，只需要OFFSET就可以了，不需要SUM了。

在M1单元格输入以下公式：

=OFFSET($A2,,(COLUMN()-COLUMN($M$1))*3,,1)

3、动态确定求和的数据区域

以下是数据区域，需要在其他区域按照产品获取某些月份的总和。

我们在P2单元格输入以下公式：

=SUMIF($A:$A,$O2,OFFSET($A:$A,,MATCH(P$1,$B$1:$M$1,0)))

这个公式中的Offset的参数是由Match函数来确定的，以A列为基点，确定P1单元格的值在B1:M1中的第几个位置，就是要移动几列。

比如Feb在第2个位置，就从A列移动2列到了C列。这样再用sumif求和就可以了。

当我们需要获取不连续区域的内容时，就可以用类似的公式，通过match确定是从那一列取值。

4、动态显示最近几天的总数量

在以下数据区域中显示了一段日期的销量，我们需要动态地显示最近3天的销量。

在E2单元格中输入以下公式：

=SUM(OFFSET(B1,COUNT(B:B)-D2+1,,D2))

或者

=SUM(OFFSET(B1,COUNT(B:B),,-D2))

第一个公式的Offset参数是，确定B列有多少个数字，再减掉需要显示的天数+1。

可以这么理解，如果参数只是COUNT(B:B)，则到达了最后一个单元格B9，也就是显示了最近1天的数据，如果要显示最近两天的数据，就需要在COUNT(B:B)的基础上减1，也就等于-D2+1。

第二个公式中的行高使用了负值，这个实际上是可以的，也是常见的用法之一。

5、创建二级下拉菜单

以下是每个部门及对应的二级部门，二级部门个数还不一样。

我们需要设定选择不同的一级部门后，生成对应的二级部门的列表。

我们需要创建两个名称：

一级菜单

=OFFSET(二级下拉菜单!$A$1,1,,COUNTA(二级下拉菜单!$A:$A)-1)

二级菜单

=OFFSET(二级下拉菜单!$B$1,MATCH(二级下拉菜单!G2,一级菜单,0),,,COUNTA(OFFSET(二级下拉菜单!$B$1:$E$1,MATCH(二级下拉菜单!G2,一级菜单,0),)))

在【公式】的【名称管理器】中点【新建】按钮可以插入名称。

一级菜单可以动态地确定A列的区域范围。

二级菜单会根据选择的一级菜单，动态的返回对应的二级菜单区域。

在G2单元格设置数据有效性，设置“序列”，

来源=一级菜单。

同样的方法，在H2设置数据有效性，来源=二级菜单。

6、创建动态数据透视表

定义名称 ：

动态数据透视表区域

=OFFSET(动态的数据透视表!$A$1,,,COUNTA(动态的数据透视表!$A:$A),COUNTA(动态的数据透视表!$1:$1))

这个是非常常见的用法，从A1单元格区域开始扩展，最终行高就是A列有多少个数据，列宽就是第一行有多少个数据，这里要注意A列和第一行都不能有空值，否则确认的区域就不正确。当我们在数据区域中增加行后，只需要刷新数据透视表就可以将增加的数据包含进去。

我们在插入数据透视表时输入定义好的名称“动态数据透视表区域”就可以了，在定义名称时可以自己命名一个简单点的名字。

7、创建动态图表

用Offset既然可以生成动态区域，那么我们可以通过指定不同的参数，从而生成不同的区域引用，在图表的数据区域中加以引用，就可以生成动态图表了。

本文由公众号Excel轻松学友情推荐