​Excel中的“OFFSET函数”你会用吗？8点总结一次攻略，太神了

Excel中的“OFFSET函数”你会用吗？8点总结一次攻略，太神了

OFFSET是Excel中非常重要的引用函数之一，这也是很多高手爱用的函数之一，大家会用这个函数吗？

OFFSET函数主要用来返回对单元格或单元格区域中指定行数和列数的区域的引用。返回的结果可以是单个单元格，也可以是单元格区域。它的语法结构为：

=OFFSET(基点,偏移的行数,偏移的列数,引用的行数,引用的列数)

注意：第2个参数为正数表示向下偏移，负数则向上偏移，第3个参数为正数表示向右偏移，负数则向左偏移；第4和第5个参数需为正数，如果省略的话，则表示引用的区域大小和基点一致。

1、单个单元格引用

如下图表格，输入公式：=OFFSET(A1,2,1)，返回结果是沈阳。

公式说明：以A1为基点，向下偏移2行，向右偏移1列，省略第4和第5个参数，表示引用区域大小和基点一致，即单个单元格，结果是沈阳。

2、单元格区域引用

选中表格外任意4个空白单元格区域，输入公式：=OFFSET(A1,3,1,2,2)，按Ctrl+Shift+Enter三键输入公式，返回杭州、成都、福州和长春这四个城市。

公式说明：以A1为基点，向下偏移3行，向左偏移1列，引用2行和2列的数据区域。

公式也可以写成：=OFFSET(A1:B2,3,1,)，也就是以单元格区域为基点进行移动，第4和第5个参数省略，引用区域大小和基点一致。

3、单条件查找

利用OFFSET函数对数据的引用原理，我们可以根据指定条件进行数据查找，如下图表格，要查找刘丽丽的业绩，输入公式：=OFFSET(B1,MATCH(D2,A2:A12,0),)

公式说明：先利用MATCH函数找出刘丽丽具体位置，再用OFFSET函数进行引用，OFFSET函数公式中的第3个参数也省略了，表示在当列上下移动,但要保留逗号进行占位。

4、多条件查找

也可以利用OFFSET函数实现多条件查找，如下图表格，要查找刘丽丽2月份的业绩，输入公式：=OFFSET(A1,MATCH(F2,A2:A12,0),MATCH(G2,B1:D1,0))

5、指定条件求和

如果要计算刘丽丽3个月的总业绩，输入公式：

=SUM(OFFSET(A1,MATCH(F7,A2:A12,0),1,1,3))

或=SUM(OFFSET(B1:D1,MATCH(F2,A2:A12,0),))

牛账网，为您提供会计实操干货与考证经验分享、2000+们免费教学视频及题库等，让您所学及所用。

公众号：牛账会计

6、指定条件计算平均值：

也可以计算指定月份的平均业绩，比如这里需要计算出2月份所有人员的平均业绩，输入公式：=AVERAGE(OFFSET(A1,1,MATCH(F3,B1:D1,0),11,1))

或=AVERAGE(OFFSET(A2:A12,,MATCH(F2,B1:D1,0)))

7、多行多列转换为一列

利用OFFSET函数还可以把多行多列数据转换为一列，如下图表格，输入公式：=OFFSET($A$1,INT((ROW(A1)-1)/3),MOD((ROW(A1)-1),3))&""

公式说明：INT((ROW(A1)-1)/3)向下填充时，生成000111222333444序列号；MOD((ROW(A1)-1),3)向下填充时，生成012012012012……序列号；最后用连字符加上""，使得单元格数据引用完毕后显示为空。

8、动态获取最后数据记录

如下图表格，记录每天的销量数据，如何动态获取最后的数据记录？

获取最后日期：=OFFSET(A$1,COUNTA(A$1:A15)-1,0)

获取最后销量：=OFFSET(B$1,COUNTA(B$1:B15)-1,0)

公式说明：先用COUNTA函数计算非空单元格数量，再用OFFSET函数进行数据引用。

End.

编辑：南北（牛账网小编）

牛账网，为您提供会计实操干货与考证经验分享、2000+们免费教学视频及题库等，让您所学及所用。

公众号：牛账会计

免责声明：本文内容仅供阅读者参考，具体以相应法规及当地行政机关判定结果为准。引用或转载，请注明以上信息。

点击下方“了解更多”领取免费实操课程/0基础入门课程！