资源描述：
《Offset函数常用套路集合.doc》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。
1、Offset函数常用套路集合 Offset函数在实际工作中有很多应用，我们经常用其他函数公式给Offset的参数赋值，生成动态引用，从而实现丰富多彩的功能。 今天就来总结一下Offset都有哪些用法。 1、分组求和 如下图所示，在A1:L6的区域内有每个月的销售数据，我们需要将每行数据按照季度汇总，也就是每三个单元格作为一组进行求和。 我们在M2单元格中输入以下公式： =SUM(OFFSET($A2,,(COLUMN()-COLUMN($M$1))*3,,3)) 然后向右、向下拖拉复制公式填充，这样就完成了所有的求和了。 2、间隔取值 还是上面的例子，假如
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单元格的值
3、在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列有多少个数字，再减掉需要显示的天数
4、+1。 可以这么理解，如果参数只是COUNT(B:B)，则到达了最后一个单元格B9，也就是显示了最近1天的数据，如果要显示最近两天的数据，就需要在COUNT(B:B)的基础上减1，也就等于-D2+1。 第二个公式中的行高使用了负值，这个实际上是可以的，也是常见的用法之一。 5、创建二级下拉菜单 以下是每个部门及对应的二级部门，二级部门个数还不一样。 我们需要设定选择不同的一级部门后，生成对应的二级部门的列表。 我们需要创建两个名称： 一级菜单 =OFFSET(二级下拉菜单!$A$1,1,,COUNTA(二级下拉菜单!$A:$A)-1) 二级菜单 =O
5、FFSET(二级下拉菜单!$B$1,MATCH(二级下拉菜单!G2,一级菜单,0),,,COUNTA(OFFSET(二级下拉菜单!$B$1:$E$1,MATCH(二级下拉菜单!G2,一级菜单,0),))) 在【公式】的【名称管理器】中点【新建】按钮可以插入名称。 一级菜单可以动态地确定A列的区域范围。 二级菜单会根据选择的一级菜单，动态的返回对应的二级菜单区域。 在G2单元格设置数据有效性，设置“序列”， 来源=一级菜单。 同样的方法，在H2设置数据有效性，来源=二级菜单。 6、创建动态数据透视表 定义名称： 动态数据透视表区域 =OFFSET(动态
6、的数据透视表!$A$1,,,COUNTA(动态的数据透视表!$A:$A),COUNTA(动态的数据透视表!$1:$1)) 这个是非常常见的用法，从A1单元格区域开始扩展，最终行高就是A列有多少个数据，列宽就是第一行有多少个数据，这里要注意A列和第一行都不能有空值，否则确认的区域就不正确。当我们在数据区域中增加行后，只需要刷新数据透视表就可以将增加的数据包含进去。 我们在插入数据透视表时输入定义好的名称“动态数据透视表区域”就可以了，在定义名称时可以自己命名一个简单点的名字。 7、创建动态图表 用Offset既然可以生成动态区域，那么我们可以通过指定不同的参数，从而生成不
7、同的区域引用，在图表的数据区域中加以引用，就可以生成动态图表了。 本文由公众号Excel轻松学友情推荐
