大话《Excel公式与函数》第二十五回 ——OFFSET函数

四叶草的期望 2016-05-27

展开全文

【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(G1,3,2,3,3)=$I$4:$K$6，前3个参数reference, rows, cols同前所述不再解释，通过偏移得到了新的引用单元格I4，由于引用的最终结果是一个单元格区域，所以还得指定新的单元格区域的大小（行、列数），height参数表示高度即行的数目为3，width参数表示宽度即列的数目为3，最终产生的新引用即以I4单元格为起点的3行3列单元格区域I4:K6。

原理大概搞明白了，但问题来了，如果是单元格区域引用的话，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!

如图所示，蓝色表格为明细数据，在绿色表格G2单元格中输入房号，根据此查找出该房号对应的业主姓名，并显示在H2单元格中。在H2单元格中输入公式：=OFFSET($C$1,MATCH(G2,D2:D11,0),,)。

该公式以C1为基点，通过偏移得到房号对应的客户姓名，至于偏移多少？此处是一个变量，输入的房号不同，偏移量就不同，所以用了MATCH函数来定位房号的位置MATCH(G2,D2:D11,0)，该部分计算得到房号2-17-3在房号列中位于第8位，此时，公式可简单理解为=OFFSET($C$1,8,,)。即从C1开始向下偏移8行，得到该房号对应的客户姓名C9，由于不需要列偏移，所以此处省略。