如何成为Excel大神Offset函数
大家好,我是永不止步的老牛
今天我们来了解下Excel的Offset函数的实用用法
Offset函数的语法是:
Offset(reference, rows, cols, [height], [width]),其中
Reference:表示要引用的 原点 ,
是一个单元格,也可以是一个单元格区域
Rows:表示原点(Reference)的 行偏移数量 ,
正数向下,负数向上,默认为0
Cols:表示原点(Reference)的列偏移数量,
正数向右,负数向左,默认为0
Height:表示返回 引用的高度 ,
正数表示向下多少行,负数表示向上多少行,
默认为1,可以省略,不能为0
Width:表示返回 引用的宽度 ,
正数表示向右多少列,负数表示向左多少列,
默认为1,可以省略,不能为0
什么意思呢? 就是说你可以通过这个函数在指定一个原点(Reference),然后往下偏移Rows行,再往右偏移Cols列,以这开始,返回Width列Height行的引用区域
我们用这张图来理解一下
=OFFSET(B4,4,5,1,1)
这个公式表示从原点B4,往下偏移4行,往右偏移5列,然后返回1行1列
这里有几点要特别说明一下
如果 行数和列数偏移量超出工作表边缘,函数 OFFSET 返回错误值 #REF!
假如把公式改成=OFFSET(B4,5000000,5,1 ,1),就会返回错误
Rows、Cols、Height和Width都有默认值
Rows和Cols的默认值是0,就是说不写具体数值的话,就表示从原点没有偏移
选中G15:H17,输入公式OFFSET(B4, , ,3,2),然后同时按Ctrl+Shift+Enter结束
Height和Width的默认值是1 ,就是说不写具体数值的话,返回行列偏移后的那个单元格
选中G15,输入公式OFFSET(B4,4 ,5 ,,),返回的就是G8
Height和Width可以省略,则假设其高度或宽度与 reference 相同
省略和默认大家要分清楚,
默认是数字可以不写,逗号要有,
省略是数字和逗号都不写
Height和Width可以省略时,
其数值就和reference的一样,
就是说如果reference是个单元格,那么Height和Width就是1,
如果reference是B1:C6,
那么Height就是6,Width就是2,
因为reference的高度是6,宽度是2
Height和Width可以是负数
这里说一下,网上很多文章说这两个参数不能是负数,是可以为负数的,负数代表方向相反,往下的变成往上,往右变成往左
下来我们用实例展示一下Offset函数的用法
1、 根据选择的考试场次I14,计算该场次近3次考试外语平均成绩,结果放在I15中
选中I14,输入公式:
=AVERAGE(OFFSET(B4,3,MATCH(I14,C4:N4,0)-2,1,3))
表示以B4为原点,往下偏移3,往右偏移MATCH(I14,C4:N4,0)-2,返回1行3列
MATCH(I14,C4:N4,0)表示返回I14在C4:N4中的位置,近3次,就给它减2
我们前面说过,
Height和Width可以是负数
负数代表方向相反,往下的变成往上,往右变成往左
那么我们可以在用MATCH(I14,C4:N4,0)找到场次的位置后,不减2,
而是用负号,告诉Offset往左边数,公式变成:
=AVERAGE(OFFSET(B4,3,MATCH(I14,C4:N4,0),1,-3))
结果是一样的
2、 根据选择的科目B13,获取十二次模考成绩,结果放在C13:N13中
选中C13,输入公式:
=OFFSET(B4,MATCH($B$13,$B$5:$B$12,0),1,1,1)
MATCH($B$13,$B$5:$B$12,0)表示返回B13在B5:B12中的位置,
就是找所选科目在第几行,
以B4为原点,往下偏移MATCH返回的位置,往右偏移1,返回1行1列
我们在用得到的数据生成个折线图
Offset和Match经常配合使用
3、 多行多列转换成一列,
我们需要将C3:H6的人员转换成一列,结果从K3开始放
以C3为原点,我们按每组4人算,
就是需要行偏移按0,1,2,3,循环,
列偏移是0,0,0,0,1,1,1,1,2,2,2,2…...
我们先写行偏移公式,
以C3计算,先得出行号,再给减去3,然后除以4取余,
就得到我们想要的结果
公式是=MOD((ROW(C3)-3),4)
下来写列偏移公式,
以C3计算,先得出行号,再给减去3,然后除以4取整,
就得到我们想要的结果
公式是=INT((ROW(C3)-3)/4)
然后带入到Offset函数里,最后的公式是
=OFFSET(C$3,MOD((ROW(C3)-3),4),INT((ROW(C3)-3)/4))
4、 生成成绩条
我们需要根据学生成绩表生成成绩条,
要求每名学生的成绩条都有表头,
并用空行隔开,方便裁剪
因为要有空行分割,
每名学生的成绩条就需要3行,
为了计算方便,我们从A9输入公式
CHOOSE(MOD(ROW(),3)+1,A$1,OFFSET(A$1,ROW(3:3)/3,),)
Offset负责取每条学生的数据
Choose负责按表头、学生成绩、空行循环显示
Ok,掌握了Offset函数的用法,你离大神又进了一步。
湖南2023年高考学生身份证加急办理可走绿色通道华声在线11月1日讯(全媒体记者杨斯涵黄京)高考生注意了!今日,2023年普通高等学校招生考试报名开始。为便利全省高考报名学生办理居民身份证,湖南省公安厅人口与出入境管理局推出20
美国或迎来最贵感恩节面粉涨价超20禽流感抬升火鸡价格美国民众在一家超市购买打折商品。(资料图)海外网11月1日电据美国新闻周刊10月30日报道,受美国国内通货膨胀供应链问题和禽流感暴发等因素影响,今年或将成为美国史上最贵的感恩节。美
特朗普将重返推特?马斯克要打造美国版微信或解雇超5成推特员工图片来源新浪微博ElonMusk我还买了一辆特斯拉,现在没了工作都不知道怎么才能付得起账单。推特总部门口,两个抱纸箱子的男子悲伤地向记者们透露整个数据程序员团队被开除的消息。多家主
提供免费住房?美国无家可归者超过58万人2022年9月8日,在美国加利福尼亚州洛杉矶市区,街头可见多个无家可归者的帐篷。(新华社曾慧图)六年前的深冬,弗兰克瑞恩死在美国加利福尼亚州的一个垃圾箱前那里臭气熏天,堆满了垃圾和
盛夏光年人生所有的收获都来自于你积攒的努力跟善良积攒努力努力的意义对每个人来说都不尽相同,但可以确定的是,无论是谁,只有努力多一点,人生的遗憾才会少一点。生活中,我们努力让自己变得更强大,是为了当暴风雨突然来临时,拥有可以抵御风
小时候以为离别只是离别,长大才明白离别是真的离别1。其实离别哪有不伤感,只是人长大了,习惯掩饰伤感。2。离别的意思是生离,死别。3。爱从不知晓自己的深刻,直到离别时分。4。以前不曾理解死亡是什么,直到再也没有了爷爷。5人在乎的没
李桂芹聚会随笔聚会随笔文李桂芹农历十月十日称双十节,又称丰收节等,是十全十美的吉日。辛丑双十节这天,李国兴先生不顾年高体弱,特邀贤官初中学第三届同学,与会共十五人聚会于星领汇大酒店。皓首相聚,童
不愧中超诺伊尔王大雷昨晚再现50米长传球反击太精准本赛季中超联赛进入到了关键阶段,在昨晚第20轮的一场补赛中,山东泰山就与上海海港迎来了一场天王山之战,双方的强强对决也被称为本赛季的国家德比,门将位置上同样是王大雷和颜骏凌的国门之
上野动物园举办大熊猫来日本50周年纪念日活动1972年10月28日,中国赠送日本的两只大熊猫康康和兰兰首次来到日本并入住上野动物园。从那时起,10月28日就被定为熊猫日。2022年10月28日(星期五)是大熊猫来日50周年纪
每个人都想静静,时间对谁都很公平!讨论一下志向,谁都有曾几何时,年少时,几乎所有小孩子都会面临一个灵魂拷问,你长大了要做什么?我们当时怎么回答的,你还记得吗?意气风发,少年不知愁滋味的你,现在离你当年的志向越来越近?越来越远?一个最关
不读书没有焦虑?相信经常刷短视频的人,总会看见各种学习视频,这里指的是各种思维的学习。这些视频都是让你读书,研究新思维,站在富人的角度或是造物主的角度看世界,这样你就可以变得与众不同,凌驾于平凡之