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技巧精选,作者:技巧妹 |
|