【Office实用平台】 实用办公技巧&在线课程&随身学习锦囊! 在EXCEL引用函数中,OFFSET算是该类型最为典型的函数,也是常用的引用函数之一,各位表亲掌握好该函数的必要性非常大,经常听说什么动态引用、动态图表这类的都少不了该函数的功劳,这一回就来跟表妹聊聊OFFSET的用法。 OFFSET()函数
以指定的引用为参照系,通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。可以指定返回的行数或列数。 图说原理:
为了便于理解,先把OFFSET函数的语法格式摆出来: OFFSET(reference, rows, cols, [height], [width]) 上图为OFFSET单个单元格引用过程,公式为:OFFSET(A1,3,2)=C4,首先指定reference参数,即指定参照基点(可以理解成参照位置)为A1,接着指定行偏移量rows参数为3,即从基点A1向下移动3行产生新的引用位置A4,再指定列偏移量cols参数为2,即从上一偏移结果A4开始向右移动2列产生新的引用位置C4,此处引用的是单个单元格,所以公式到此结束,不需要指定[height],[width]参数,公式最终产生了新的引用C4(显示该单元格数据)。 接下来,再来看单元格区域的引用,只要你充分理解了前面的原理,那区域的引用也就很简单了。
原理大概搞明白了,但问题来了,如果是单元格区域引用的话,OFFSET函数返回的不再是单一的值而是多个单元格的值,即一组数据,该怎么办?怎么办?如果表亲一直都在贱读这大话《公式与函数》的话,那非常感谢您的支持,没忘记的话,那就得恭喜您了,此处的问题就不是问题了,这不就是我们第九回讲的数组公式嘛!不知道的亲,还是温习下“第九回”吧!扯回此处的公式:=OFFSET(G1,3,2,3,3),当你输完公式后得按下ctrl+shift+enter组合键才可得到结果,这就是数组公式特征之一哟!
OFFSET(reference,rows, cols, [height], [width]) 参数说明: reference必需。为函数的参照基点,必须为单元格引用,否则OFFSET返回错误值#VALUE!。 rows必需。需要从基点进行偏移的行数,正数为向下偏移,负数为向上偏移。 cols必需。需要进行偏移的列数,正数为向右偏移,负数为向左偏移。 Height可需。需要返回新引用的行数,必须为正数。 Width可需。需要返回新引用的列数,必须为正数。 大家结合功能原理来解读格式吧,此处就简述了,下面还是来看看案例!
各位表亲应该都知道查找函数VLOOKUP、HLOOKUP等函数只能作正向查找,不能进行反向查找,如以该例的房号为查找字段的话,VLOOKUP函数只能以房号列为标准返回该列右侧列的数据而不能返回左侧列的数据(如认购日期、姓名等)。 该例我们来演示使用OFFSET函数进行反向查找,Let’s GO!
该公式以C1为基点,通过偏移得到房号对应的客户姓名,至于偏移多少?此处是一个变量,输入的房号不同,偏移量就不同,所以用了MATCH函数来定位房号的位置MATCH(G2,D2:D11,0),该部分计算得到房号2-17-3在房号列中位于第8位,此时,公式可简单理解为=OFFSET($C$1,8,,)。即从C1开始向下偏移8行,得到该房号对应的客户姓名C9,由于不需要列偏移,所以此处省略。 说了这么多,大家还是动动手试试吧,才能体会该函数的更多用法! |
|