​​Excel技巧，教你如何玩转OFFSET函数！

 

OFFSET是Excel中非常重要的引用函数之一，很多高手喜欢利用这个函数进行数据引用，以此来解决一些棘手的问题。今天小琥就带大家来详细认识一下这个函数。

 

说起offset函数，表示引用某一个单元格或者区域。从复杂的数据汇总、到数据透视表再到高级动态图表，都离不开offset函数的默默付出。很多新手对怎么使用offset函数，不是很了解，下面就来告诉大家offset函数的使用方法?

offset函数是什么?

在Excel中，offset函数的功能为以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。

如果行数和列数偏移量超出工作表边缘，函数offset返回错误值#REF!

如果省略height或width，则假设其高度或宽度与reference相同。

函数offset实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。函数offset可用于任何需要将引用作为参数的函数。例如，公式 SUM(offset(C2,1,2,3,1)) 将计算比单元格C2靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。

 

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),))

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函数进行数据引用。

 

 

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需99元，即可下载本站文章涉及的文件和软件。