|
Offset函数在实际工作中有很多应用，我们经常用其他函数公式给Offset的参数赋值，生成动态引用，从而实现丰富多彩的功能。 比如前几天给大家分享了Offset函数用于分组求和、间隔取值。 今天就来总结一下Offset都有哪些用法。 开始之前我们还是来回顾一下Offset的语法及使用说明。 语法：OFFSET(reference, rows, cols, [height], [width]) 说明：返回对单元格或单元格区域中指定行数和列数的区域的引用。 返回的引用可以是单个单元格或单元格区域。 可以指定要返回的行数和列数。 参数rows和cols分别代表从基准单元格开始偏移的行数和列数。rows，正数表示向下，负数表示向上；cols正数表示向右，负数表示向左。 参数 height和width，代表从基点单元格偏移完之后返回的区域的行高和列宽。 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既然可以生成动态区域，那么我们可以通过指定不同的参数，从而生成不同的区域引用，在图表的数据区域中加以引用，就可以生成动态图表了。 关于动态图表，以后会开专题，这里就不过多地深入讲解了。感兴趣的可以自己试试看。 |
|
|
