Excel教程

Excel函数
Excel技巧

【EXCEL教程】OFFSET函数,你能玩转它吗?

当前位置:网站首页 > Excel教程

【EXCEL教程】OFFSET函数,你能玩转它吗?

* 来源: 【office教程学习】 * 作者: 【EXCEL教程】 * 发表时间: 2020-07-16 12:13:38 * 浏览: 2


OFFSET函数是一个非常好玩的函数,无论是数据汇总,还是数透中获取动态数据区域,还是动态图表的制作等等,都能有它的身影,默默付出!


函数定义:


以指定的引用为参照系,通过给定偏移量返回新的引用。


语法格式:


OFFSET(reference, rows, cols, [height], [width])



备注:


如果rows和cols的偏移使引用超出了工作表边缘,则OFFSET返回错误值#REF!。如果省略height或width,则假设其高度或宽度与reference相同。


OFFSET实际上并不移动任何单元格或更改选定区域,它只是返回一个引用;返回的引用可以是单个单元格或单元格区域,也可以指定要返回的行数和列数。


OFFSET可以与任何期待引用参数的函数一起使用。


1、返回单个单元格的引用


比如下图中厉致诚是怎么到达林浅的位置的



输入公式:=OFFSET(A2,2,2)


以A2单元格为参照


向下偏移2行到达A4单元格


向右偏移2列到达C4单元格


返回C4单元格内容“林浅”


2、返回单元格区域的引用



选中区域F2:G3单元格


输入公式:=OFFSET(A2,2,2,2,2)


三键结束


以A2单元格为参照


向下偏移2行到达A4单元格


向右偏移2列到达C4单元格


返回以C4单元格为起点2行2列的C4:D5单元格区域的引用。


3、求销售三部1-6月的总业绩



输入公式:

=SUM(OFFSET(B1:G1,MATCH(A8,A2:A5,0),))


MATCH(A8,A2:A5,0)部分找到“销售三部”在区域A2:A5中的位置3


那么公式OFFSET部分就是OFFSET(B1:G1,3,),


注意此处省略了OFFSET函数的第三参数(用逗号占位)


OFFSET部分是以单元格区域B1:G1为参照,向下偏移3行,偏移0列,返回新区域B4:G4单元格的引用


最后用SUM函数对B4:G4这个区域求和。


4、求所有部门1-6月的总业绩



输入公式:

=SUM(OFFSET(B1:G1,1,,4,6))


OFFSET(B1:G1,1,,4,6)部分是以单元格区域B1:G1为参照


向下偏移1行,到达区域B2:G2


偏移0列(省略了第三参数,用逗号占位)


返回行高为4,列宽为6的新区域B2:G5单元格的引用


最后用SUM函数对B2:G5这个区域求和。


5、多行多列转一列



输入公式:

=OFFSET($A$3,INT(ROW(A2)/2)-1,MOD(ROW(A2),2))&""


INT(ROW(A2)/2)-1下拉生成0,0,1,1,2,2…序列


MOD(ROW(A2),2) 下拉生成0,1,0,1,0,1…序列


D1公式:=OFFSET($A$3,0,0)&""


以A3单元格为参照,偏移0行0列,返回A3单元格内容“Excel”


公式下拉后依次为:


D2公式:=OFFSET($A$3,0,1)&""


以A3单元格为参照,偏移0行1列,返回B3单元格内容“教”


D3公式:=OFFSET($A$3,1,0)&""


以A3单元格为参照,偏移1行0列,返回A4单元格内容“程”


……以此类推


6、单条件查找



输入公式:

=OFFSET(B1,MATCH(D2,A2:A5,0),)


MATCH(D2,A2:A5,0)部分找到“销售二部”在区域A2:A5中的位置2


那么公式就是OFFSET(B1,2,)


注意此处省略了OFFSET函数的第三参数(用逗号占位)


以B1单元格为参照,偏移2行0列,返回B3单元格内容“10888”


7、多条件查找



输入公式:

=OFFSET(A1,MATCH(A9,A2:A5,0),MATCH(B9,B1:G1,0))


MATCH(A9,A2:A5,0)部分找到“销售三部”在区域A2:A5中的位置3


MATCH(B9,B1:G1,0) 部分找到“4月”在区域B1:G1中的位置4


那么公式就是OFFSET(A1,3,4)


以A1单元格为参照


向下偏移3行,到达A4单元格


向右偏移4列,到达E4单元格


返回E4单元格内容“4640”


8、生成工资条



输入公式:=CHOOSE(MOD(ROW(),3)+1,A$1,OFFSET(A$1,ROW(3:3)/3,),)&""


向右向下填充公式



9、获取动态区域


插入数据透视表时,不想手动获取数据区域,该怎么办呢?


设置动态获取数据区域,


操作步骤:


【公式】→【定义名称】(或按Ctrl+F3键)


引用位置:

=OFFSET(总表!$A$1,,,COUNTA(总表!$A:$A),5)



COUNTA(总表!$A:$A)部分算出A列中非空单元格的个数


以A1单元格为基点,


向下偏移0行,向右偏移0列,


返回一个行高为COUNTA(总表!$A:$A),列宽为5的新区域的引用。


设置完成后,当你在总表中添加数据或删除数据时能自动获取数据单元格区域。


10、动态图表



输入公式:

=OFFSET($A$1,MATCH($A$9,$A$2:$A$5,0),COLUMN(A1))



今天的分享就到这,如果教程对大家有用,希望大家多多分享点赞支持小编哦!你的每一次点赞和转发都是支持小篇坚持原创的动力。