Offset函数常用套路集合.docVIP

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轻松学友情推荐