Offset函数主要应用在单元格区域的定位和统计方面,一般做数据透视表定义名称都需要用到Offset函数。Offset函数属于查找与引用类的函数。
OFFSET(reference,rows,cols,[height],[width])
以指定的引用为参照系,通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。
这个函数有5个参数:
第一个reference参数是基点
第二个rows参数是要偏移几行,正数向下,负数向上。
第三个cols参数是要偏移几列,正数向右,负数向左。
第四个height参数是新引用几行。
第五个width参数是新引用几列。
如果不使用第四个和第五个参数,新引用的区域就是和基点一样的大小。
注意事项
1、如果行数和列数偏移量超出工作表边缘,函数 OFFSET 返回错误值 #REF!。
2、如果省略 height 或 width,则假设其高度或宽度与 reference 相同。
3、函数 OFFSET 实际上并不移动任何单元格或更改选定区域,它只是返回一个引用。
4、函数 OFFSET 可用于任何需要将引用作为参数的函数。
OFFSET 函数应用
第一问:根据上表求最近存款的日期和金额
因考虑到日后还要添加数据,所以这块的函数必须是一个动态引用,让函数总是取最后一天的日期
最近日期 公式 = OFFSET(A1,COUNT(A:A),,)
在这里我以A1做为基准点,以count函数来统计A列中有数字的单元格个数做为往下偏称的行数,因不向右偏移故第三个参数可以省略,但占位的逗后不能省略。
如果A列的数值增加了,COUNT函数的计数结果就增加了,OFFSET函数的行偏移参数也就随之变化。
取出了最近日期,那么找对应的存款金额就很容易了,直接用index和match函数就可以搞定了。
最近存款金额 公式 = INDEX(B:B,MATCH(D2,A:A,0))
第二问:汇总最后三天的存款金额
公式 = SUM(OFFSET(B1,COUNT(B:B),,-3))
解题思路:通过OFFSET函数生成数据区域的动态引用,然后将该动态引用区域用sum函数加总起来。
第四个参数高度为-3,是指从下往上高度为3的一个区域。
联系客服