您当前的位置:首页 > 电脑百科 > 软件技术 > office

Excel中,OFFSET函数的使用方法

时间:2022-04-15 11:59:54  来源:  作者:是一颗小白菜呀

OFFSET是Excel中的函数,在Excel中,OFFSET函数的功能为以指定的引用为参照系,通过给定偏移量得到新的引用。

它的语法结构为:

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

Excel中,OFFSET函数的使用方法

 

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

 

返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用;否则,函数 OFFSET 返回错误值#VALUE!。

 

函数说明

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

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

函数 OFFSET 实际上并不移动任何单元格或更改选定区域,它只是返回一个引用。函数 OFFSET可用于任何需要将引用作为参数的函数。

例如,公式 SUM(OFFSET(C2,1,2,3,1)) 将计算比单元格C2 靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。

 

函数示例

将示例复制到空白工作表中,你可能会更易于理解该示例。

 

A

B

1

公式

说明(结果)

2

=OFFSET(C3,2,3,1,1)

显示单元格 F5 中的值 (0)

3

=SUM(OFFSET(C3:E5,-1,0,3,3))

对数据区域 C2:E4 求和 (0)

4

=OFFSET(C3:E5,0,-3,3,3)

返回错误值 #REF!,因为引用区域不在工作表中

具体应用:

OFFSET函数经过偏移后返回的是一个区域,所以我们可以对这个区域求和、平均值、计数、最大最小值等。比如对下面的返回的区域求和,那么在offset函数前加上sum函数,然后就可以计算出结果是36。

Excel中,OFFSET函数的使用方法

 

OFFSET与一个match函数结合求和。

在下图中,要计算1月到某月的销量和,如果切换月份时,累计销量也会变化。该如何操作呢?

首先,在F2单元格添加数据验证。选择【数据】——【数据验证】——允许选择【序列】——来源选择A2到A13单元格的数据——【确定】。这时我们就在F2单元格建立好月份的下拉菜单选项。

然后在G2单元格输入函数=SUM(OFFSET(B2,0,0,MATCH(F2,A2:A13,0))),此时在F2单元格选择相应的月份,G2单元格的累计销量就会随着选择的月变化而变化。

这个函数有三个公式,最里面的MATCH(F2,A2:A13,0),表示查找F2位于A2到A13单元格的第几行,比如F2单元格是十月,十月在A2到A13单元格的第10行,所以match函数返回的结果是10。OFFSET(B2,0,0,10)中省略了第五个参数,返回的结果是B2到B11单元格,最后利用sum函数对B2到B11单元格进行求和。

Excel中,OFFSET函数的使用方法

 

OFFSET函数与多个match函数进行求和。

仍然是上面一组数据,那么可不可以求任意两个月份之间的累计销量呢?我们考虑到既然match函数返回的值是所选单元格在区域中的位置,那么就可以利用match函数嵌套来编制公式。

我们在D2和F2单元格分别设置月份的下拉菜单选项,然后在G2单元格中输入函数=SUM(OFFSET(B2,MATCH(D2,A2:A13,0)-1,0,MATCH(F2,A2:A13,0)-MATCH(D2,A2:A13,0)+1),0)。此时我们在D2和F2中选择相应的月份,就可以求出两个月份之间的累计销量了。

这个函数看上去比较长,实际在编写函数的时候比较容易想到,也容易理解,因为match函数可以返回行数,所以在以B2单元格为起始单元格的前提下,向下偏移的行数要根据D2单元格的变化而变化,第二个参数用了MATCH(D2,A2:A13,0)-1,表示如果D2单元格选择九月,那么这个match函数返回的是8,即9月对应的销量在B2开始数的第9-1=8行数据。第4个参数用到了两个match函数相减,因为F2单元格所在的行数减D2单元格所在的行数,需要对计算结果加1进行调整。最后利用sum函数求和即可。

但是这种情况如果选择时D2单元格大于F2单元格时,计算的就不是正确结果怎么办?此时只要把offset函数第四个参数嵌套一个if函数,即如果F2的月份大于D2,那么match函数相减后加1,如果F2的月份小于D2,那么等于match函数相减后减1。这样设置后无论如何选择月份都会计算出正确结果。

Excel中,OFFSET函数的使用方法

 

OFFSET与count函数组合求最近几个累计数

如下图所示,我想要计算最近3个月的累计销量,当增加月份时,销量也会变动,函数如何写呢?

此时输入的函数是=SUM(OFFSET(B1,COUNT(B:B),0,-3))。在下面继续添加月份和销售时,累计销量始终是最近3个月的销售累计。

这个函数offset函数第二个参数COUNT(B:B)表示对B列数据进行计算,因为count函数对文本、空白单元格都不会进行计数,所以B列有多少有数据的单元格,count函数就返回多少。而第四个参数-3,表示从B1单元格偏移到最后一个单元格后,往回折了3个单元格。所以可以表示计算最近三笔销量之和。

Excel中,OFFSET函数的使用方法

 

综合运用:OFFSET与match、countif、vlookup函数,定义名称结合制作二级下拉菜单并动态查找数据

下图左侧是我国34个省级行政区,300多个市级行政区及对应销量,我们根据右侧的下拉箭头选择省级行政区后,就可以在后面的下拉箭头选择当前省级行政区下的市及对应销量。因为步骤比较多,此处不再对具体操作进行演示,简单说一下操作步骤。以后在介绍动态图表制作的时候会进行详细介绍。

首先把A列的数据复制到E列中(此处为了能看清楚动图,E列已隐藏)。然后选择【数据】选项卡——【删除重复值】,把E列中的每个省份名称只留下一个值。

然后打开【公式】选项卡——【定义名称】,输入函数=5'!$E$2:$E$35,前面这个5'!是引用的工作表名称。名称输入“省”。继续定义名称,输入函数=OFFSET('5'!$B$1,MATCH('5'!$G$2,'5'!$A$2:$A$342,0),0,COUNTIF('5'!$A$2:$A$342,'5'!$G$2),1),名称输入“市”。

接着在G2单元格中,添加【数据验证】——【序列】——【来源】=省。在H2单元格中,【数据验证】——【序列】——【来源】=市

最后在I2单元格中输入函数=VLOOKUP(H2,$B:$C,2,0),就可以实现动态查找了。

Excel中,OFFSET函数的使用方法

 

这就是本文介绍的offset函数的应用,试着操作一下吧。



Tags:Excel   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
Excel进行数据处理的时候,相信大家对数据求和肯定不陌生。在进行数据求和汇总的时候,相信绝大多数同学首先就会想到一个Excel函数,那就是sum函数。sum函数是Excel中最简单的求...【详细内容】
2022-04-18  Tags: Excel  点击:(62)  评论:(0)  加入收藏
OFFSET是Excel中的函数,在Excel中,OFFSET函数的功能为以指定的引用为参照系,通过给定偏移量得到新的引用。它的语法结构为:=OFFSET(基准位置,向下或上偏移几行,向右或左偏移几列,引...【详细内容】
2022-04-15  Tags: Excel  点击:(28)  评论:(0)  加入收藏
我们企业现在只有30人左右的规模,但有着上千种的产品SKU,同时我们的经营模式是自研自产自销,所以研发、生产、推广和销售都是由这30个人去完成,这其中面临着很大的挑战。苏州柔...【详细内容】
2022-02-14  Tags: Excel  点击:(42)  评论:(0)  加入收藏
快过年了,又到了公司年底评级的时候了。今年的评级和往常一下,每个人都要填写公司的民主评议表,给各个同事进行评价打分,然后部门收集起来根据收集上来的评价表进行汇总统计。想...【详细内容】
2022-01-21  Tags: Excel  点击:(85)  评论:(0)  加入收藏
想学习掌握Excel,或者想进一步地提升工作效率?那么,这篇Excel快捷键大全或许能够为你带来不少的帮助,本篇文章包含了100多个Excel快捷键,基本上适用于现有的各个Excel版本,在工作...【详细内容】
2021-12-30  Tags: Excel  点击:(65)  评论:(0)  加入收藏
如下所示,有一份模拟的txt数据,想放到excel表格里面去分析 如果我们直接CTRL+A全选数据,CTRL+C复制,然后在Excel里面CTRL+V粘贴,数据就会变成如下所示情形一: 整行数据会放在挤在...【详细内容】
2021-12-30  Tags: Excel  点击:(69)  评论:(0)  加入收藏
今天我们来唠唠Excel中复制粘贴功能,可能很多人都会说复制粘贴有什么好讲的,不就是Ctrl+C,Ctrl+V吗?那你就太小看它了。今天我们要讲的是一种更加强大的复制粘贴功能,他就是【选...【详细内容】
2021-12-30  Tags: Excel  点击:(87)  评论:(0)  加入收藏
办公软件究竟是选Ms Office还是WPS Office很多人都为此争吵不休,我的观点就是重度办公首选Ms Offic,轻办公首选WPS Office,跟大家分享的也都是关于Excel的教程,今天跟大家分享几...【详细内容】
2021-12-30  Tags: Excel  点击:(121)  评论:(0)  加入收藏
大家好, 前面我们讲过利用常规的方法,制作二级下拉列表辅助表。 今天我们就来介绍一下,Dax 度量值(Power Pivot)和 Power Query 的 M 函数做法,主要带大家拓展思路。 如下图,我们现...【详细内容】
2021-12-28  Tags: Excel  点击:(82)  评论:(0)  加入收藏
Excel常用电子表格公式大全   一、Excel基本公式   1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。   2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"200...【详细内容】
2021-12-21  Tags: Excel  点击:(89)  评论:(0)  加入收藏
▌简易百科推荐
Excel进行数据处理的时候,相信大家对数据求和肯定不陌生。在进行数据求和汇总的时候,相信绝大多数同学首先就会想到一个Excel函数,那就是sum函数。sum函数是Excel中最简单的求...【详细内容】
2022-04-18  Excel函数与VBA实例    Tags:Excel   点击:(62)  评论:(0)  加入收藏
统计分析,是Excel的拿手好戏,但对于大多数的亲来说,是道难题,Why?究其原因就是多条件函数公式不掌握,不熟练,不会应用……所以,小编今天分享的办公必备的多条件统计函...【详细内容】
2022-04-18  Excel函数公式    Tags:多条件统计函数   点击:(12)  评论:(0)  加入收藏
OFFSET是Excel中的函数,在Excel中,OFFSET函数的功能为以指定的引用为参照系,通过给定偏移量得到新的引用。它的语法结构为:=OFFSET(基准位置,向下或上偏移几行,向右或左偏移几列,引...【详细内容】
2022-04-15  是一颗小白菜呀    Tags:Excel   点击:(28)  评论:(0)  加入收藏
在激活 Office前,我们首先要确定电脑有没有预装正版Office,可以通过查看电脑外包装标签或者商详有没有预装Office的标签图,或联系售后客服来确认。(对正版Office有需求的用户,可...【详细内容】
2022-04-13  华小硕情报    Tags:激活 Office   点击:(21)  评论:(0)  加入收藏
在我们使用Microsoft Office进行日常办公时,一般来说一个窗口只能打开一个文档,其余的文档都是在任务栏上堆叠起来的,来回切换文档要么用“Ctrl+tab”键,要么鼠标切换文档所在窗...【详细内容】
2022-02-25  阿龙带你玩转电脑    Tags:office插件   点击:(51)  评论:(0)  加入收藏
当我们在word中编辑时,会遇到多行姓名的情况,有的小伙伴就会手动+空格去修改,费时费力。今天就为大家分享两个快速对齐姓名的操作方法,简单方便,提升工作效率没烦恼。一起来看看...【详细内容】
2022-02-22  小侯电脑帮    Tags:Word   点击:(100)  评论:(0)  加入收藏
今天给大家分享6个值得收藏的word技巧,简单且实用,让你办公更加高效率哟! 1、快速排版当你在排版时遇到长短不一的文字,你还在一个一个地敲空格对齐吗?这样效率也太慢了,想要快速...【详细内容】
2022-02-16  职场科技范    Tags:word技巧   点击:(54)  评论:(0)  加入收藏
Office 2021发布于2021年10月,Office 2019发布于2019年1月;功能上21版本更为先进,增加了许多功能点;对计算机系统配置的要求,两者近乎相同;价格接近,以下是两者典型特征对比。 可用...【详细内容】
2022-01-18  海蓝office    Tags:Office   点击:(280)  评论:(0)  加入收藏
想学习掌握Excel,或者想进一步地提升工作效率?那么,这篇Excel快捷键大全或许能够为你带来不少的帮助,本篇文章包含了100多个Excel快捷键,基本上适用于现有的各个Excel版本,在工作...【详细内容】
2021-12-30  Excel函数公式    Tags:Excel快捷键   点击:(65)  评论:(0)  加入收藏
如下所示,有一份模拟的txt数据,想放到excel表格里面去分析 如果我们直接CTRL+A全选数据,CTRL+C复制,然后在Excel里面CTRL+V粘贴,数据就会变成如下所示情形一: 整行数据会放在挤在...【详细内容】
2021-12-30  Excel自学成才    Tags:Excel表格   点击:(69)  评论:(0)  加入收藏
站内最新
站内热门
站内头条