Excel OFFSET函数实在太好用了!

来自:Excel技巧精选(微信号:ExcelSkill),作者:技巧妹 

OFFSET是一个很有趣的函数,在数据的动态引用方面起到非常奇妙的作用。今天技巧妹和大家分享一下有关这个函数的一些应用。


首先来看下OFFSET函数的语法结构:


=OFFSET(基准位置,向下偏移行数,向右偏移列数,引用区域的高度,引用区域的宽度)


公式里面的第1个参数可以是单个单元格,也可以是单元格区域;第2和第3个参数为正数,代表向下和向右偏移,如果是负数,则是向上和向左偏移;第4和第5个参数如果省略不写,默认和第1个参数大小一致。


接下来我们用4个具体例子来讲解一下OFFSET函数的典型应用:


1、动态提取最新销量数据


在销售工作中,经常会流水记录产品的销量数据,如何提取最新的销量数据呢?

在D2单元格中输入公式:=OFFSET(B1,COUNT(B:B),)



解析:先用COUNT函数计算出B列数字个数,然后用OFFSET函数公式以B1为基准位置,向下偏移COUNT函数公式返回的行数,向右偏移0列,公式里面可以省略0不写,只保留逗号。


2、统计指定产品的总销量


比如我们要统计产品A的总销量,在H2单元格输入公式:

=SUM(OFFSET(A2:A11,,MATCH(G2,A1:E1,0)-1))



解析:先用MATCH函数定位G2单元格里面的内容在A1:E1区域中的位置,减去1作为OFFSET函数公式中的第3个参数,即以A2:A11为基准位置,向下偏移0行,向右偏移MATCH函数公式返回结果减去1后的列数,最后用SUM函数对引用的区域数据进行求和即可。


3、计算所有产品最近3个月的总销量


在G2单元格中输入公式:=SUM(OFFSET(B1:E1,COUNTA(A1:A11)-3,,3,))



解析:先用COUNTA函数计算出A列中非空单元格的个数,减去3后作为OFFSET函数公式的第2个参数,即以B1:E1为基准位置,向下偏移对应的行数,向右偏移0列,引用3行的单元格区域。


4、查找指定产品指定月份的销量


OFFSET函数也可以用来进行多条件查找,比如这里我们要查找产品B的5月份销量,

在I2单元格中输入公式:=OFFSET(A1,MATCH(H2,A2:A11,0),MATCH(G2,B1:E1,))



解析:先用MATCH函数分别定位出指定月份和产品在A2:A11和B1:E1区域中的位置,作为OFFSET函数的第2和第3个参数,然后以A1为基准位置偏移对应的行数和列数即可。

推荐↓↓↓
Excel技巧精选