Offset函数常用套路集合.doc

文档介绍：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,一级菜单,