excel表格制作教程:OFFSET函数应用技巧

2020年8月4日 分类:Excel 作者:企业网盘

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

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

其中:

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

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

图1

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

=SUM(OFFSET($B$4,0,0,9,3))

结果如下图2所示。

图2

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

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

=SUM(OFFSET($B$4,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

阅读已结束,喜欢的话就点个赞吧
注册坚果云网盘
还有其他问题,可以咨询小坚果咨询小坚果
继续阅读