Excel公式技巧43：OFFSET函数应用技巧

OFFSET函数是我们经常会用到的一个函数，能够以某单元格为基点，偏移得到指定位置的单元格或者单元格区域。其语法为：

=OFFSET(reference,rows, cols, [height], [width])

其中：

• 参数reference，指定起始位置（单元格）。
• 参数rows，基于起始位置向下偏移的行数。
• 参数cols，基于起始位置向右偏移的列数。
• 这样，得到了一个新的位置。下面两个参数可选，基于这个新位置获取单元格区域。
• 参数height，返回的单元格区域的行数。
• 参数width，返回的单元格区域的列数。
• 如果忽略这两个参数，则默认为1行1列。

下面以示例来讲解OFFSET函数的应用技巧。如下图1所示的数据工作表。

图1

可以使用OFFSET函数配合SUM函数求出一季度9个区的数量之和：

=SUM(OFFSET(B4,0,0,9,3))

结果如下图2所示。

图2

可以看出，OFFSET函数以单元格B4为起始位置，由于参数rows和cols都为0，因此其没有偏移，新的引用位置仍为单元格B4，以此位置为起始点获取9行3列的单元格区域，即单元格区域B4:D12，将其传递给SUM函数求和。

我们让OFFSET函数与MATCH函数、COUNT函数配合使用，让公式能够动态求和，如下图3所示，在单元格C18中输入公式：

=SUM(OFFSET(B4,0,MATCH(C15,B2:M2,0)-1,COUNT(B:B),COUNTIF(B2:M2,C15)))

结果如下图3所示。

图3

在图3所示的工作表中，单元格C15为要查找的数据，当你修改这个数据时，单元格C18中的值会相应变化，即求不同季度9个区的数量之和。

公式中，OFFSET函数仍然以单元格B4为起始位置，参数rows指定为0，表明新位置与起始位置同一行，参数cols指定为：

MATCH(C15,B2:M2,0)-1

获取单元格C15中的数据在单元格区域B2:M2中的位置，将结果减1，让OFFSET函数偏移到新位置。例如，单元格C15中的数据为“二季度”，那么MATCH函数查找的结果返回4，减去1后得到3，即OFFSET函数偏移到新的位置单元格E4。

参数height指定为：

COUNT(B:B)

统计列B中包含数字的单元格的个数，显然是9。

参数width指定为：

COUNTIF(B2:M2,C15)

统计单元格区域B2:M2中包含单元格C15中的值的单元格的个数，如果单元格C15中的数据为“二季度”，那么返回数值3。

这样，OFFSET函数以新位置E4为起点扩展9行3列，即单元格区域E4:G12。

如果修改单元格C15中的数据，公式将计算出相应的结果，如下图4所示。

图4