excelperfect OFFSET函数是我们经常会用到的一个函数,能够以某单元格为基点,偏移得到指定位置的单元格或者单元格区域。其语法为: =OFFSET(reference,rows, cols, [height], [width]) 其中:
下面以示例来讲解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 相关文章: 欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。 欢迎到知识星球:完美Excel社群,进行技术交流和提问,获取更多电子资料。 |
|
来自: hercules028 > 《excel》