哈喽,大家好,今天咱们来学习OFFSET函数。

根据过往的经验来看,这是咱们学习Excel函数过程中一个不大不小的坎,OFFSET算是比较高级的函数,参数比较多,变化也比较多;不过它在数据结构转换、高级图表制作等方方面用到的也比较多,所以还是要学一下。

别紧张,下面咱们玩个小游戏。谁正式学习之前不先打半个小时王者荣耀不是?

咱们这个游戏的名字叫:刘皇叔的野望。刘备要从老家出发,跑到成都去,打下一块地盘,准备一统江山。

offset函数所有用法(OFFSET函数其实是个游戏机)(1)

现在,假如你是刘备,你在B1单元格,你要走到成都(D5单元格)。

怎么走?

正常来说,有两条路。

一条是先向下走4个单元格,再向右走2个单元格。

一条是先向右走2个单元格,再向下走4个单元格。

好了,分析完了,你走吧。

如何使用函数来实现咱们的移动过程和目标呢?OFFSET函数最是恰当不过了。▎=OFFSET(B1,4,2)OFFSET函数的第1参数是基点,也就是咱们出发点(老家),本例为B1;第2参数为纵向移动几行,本例为向下移动4行;第3参数为横向移动几列,本例为向右移动2列。

offset函数所有用法(OFFSET函数其实是个游戏机)(2)

运行公式,返回结果成都。

叮咚~恭喜你,成功到达成都城,闯过游戏第一关简易级副本。请问你是否选择勇闯第一关普通级副本?叮咚~你选择了我没选我没选我说你选了你就选了第一关普通级副本,2秒后副本开启。

副本的任务看起来确实很简单嘛,请使用OFFSET函数将A列的数据转置为一行。

offset函数所有用法(OFFSET函数其实是个游戏机)(3)

来,找诸葛亮星光兄弟一起分析下现状。咱们的基点,也就是老家,在A1单元格,公式写在C1单元格,然后向右复制填充,公式每向右复制一个单元格,基点就要向下移动一行。因此公式如下:=OFFSET($A$1,COLUMN(A1)-1,0)COLUMN(A1)返回A1单元格的列号,也就是1。随公式横向复制填充后,依次返回B2/C1/D1……的列号,也就是2~3~4……,减去1之后,作为OFFSET的第2参数。OFFSET第3参数为0,表示不需要横向移动。于是以A1为基点,每次向下递增偏移1行。C1单元格公式为:=OFFSET($A$1,0,0),返回A1单元格D1单元格公式为:=OFFSET($A$1,1,0),返回A2单元格E1单元格公式为:=OFFSET($A$1,2,0),返回A3单元格…………以此类推,即为结果。

offset函数所有用法(OFFSET函数其实是个游戏机)(4)

那么反过来,又如何将C1:K1单元格区域的数据转换为A1:A9呢?——别看我,侬自己想想。叮咚~恭喜你,轻松闯过第一关普通级副本,获得评分SSS。请问你是否选择勇闯第一关困难级副本?叮咚~你选择了我没选我没选我说你选了你就选了第一关困难级副本,副本马上开启。请使用OFFSET函数将A列的数据转换为三行三列,如C1:E3区域所示。然后……请再使用OFFSET函数将C1:E3区域三行三列的数据,转换为A列单列数据……

offset函数所有用法(OFFSET函数其实是个游戏机)(5)

叮咚,很不幸,闯关失败呐你,请下次再来吧。

offset函数所有用法(OFFSET函数其实是个游戏机)(6)

谁玩游戏还没遇到过bug不是?不要灰心,解锁答案可以参考文末示例文件。咱们的目标是经略西南~一统江山,下面还请继续勇闯第二关。…………话说刘备占领成都城后,地位渐稳,脾气渐长,野心骨质增生。8842年的冬天,成都下了第一场雪,刘备趁老婆不在家,在赵云的陪同下吃了一顿丰富的老坛酸菜牛肉方便面,一时间辣气干云,遂决定出师向南,占领成都临近的3座城市:江州、建宁和武陵。

offset函数所有用法(OFFSET函数其实是个游戏机)(7)

——别发呆啊,该你上场了,请使用OFFSET函数完成从D5单元格成都城出发,占领临近3座城池的目标。

是否查看游戏攻略

OFFSET函数基本语法如下:

=OFFSET(基点,移动的行数,移动的列数,[新引用的行数],[新引用的列数])

在第一关时,你已经懂得了第1、2、3参数的意义。它们可以将指定单元格或区域,按指定行/列数,移动到另一个单元格或区域。这里需要补充说明的是,移动的行列数可以是正数,也可以是负数。第2参数使用正数时,表示从基地向下偏移,使用负数时,表示向上偏移。第3参数使用正数时,表示向右偏移,使用负数时,表示向左偏移——这段话和你闯第2关没啥关系。第4和第5参数是可选的,如果省略这两个参数,新引用的区域就是和基点一样的大小。如果没省略……就代表以基点位置开始,向四周扩张地盘。其中第4参数代表地盘扩张后的行数,如为正数,则向下扩张,如为负数,则向上扩张;第5参数代表地盘扩张后的列数,如为正数,则向右扩张,如为负数,则向左扩张。第2、3参数指定了基点移动的行/列数,是不包含基点自身的。而第4、5参数指定了地盘大小的行列数,是包含基点自身的。因此第4和5参数不能为零,为零就说明你连老家都丢了,GAME OVER翘辫子了不是?

攻略结束

——看罢攻略,相比你对闯关已经胸有成竹了。

答案如下:

=OFFSET(D5,0,0,2,2)

OFFSET函数以D5单元格成都城为基点,第2、3参数为0,表示基点原地不动,然后向下扩张2行,向右扩充2列,也就是D5:E6区域。

公式运行后会返回错误值#VALUE!,这是多维引用结果落地的问题,咱们先不管它,以后有缘再聊。这里可以先选中公式,按<F9>查看公式返回的结果是否正确。

offset函数所有用法(OFFSET函数其实是个游戏机)(8)

OFFSET函数直接生成一个区域的用法,常用于动态图表的数据源;而在函数处理和数据分析中,常作为其它聚合统计或查找匹配类函数的参数。比如统计D5:E6区域数据的个数:

=COUNTA(OFFSET(D5,0,0,2,2))

更多技巧请继续往下阅览。

……

叮咚~恭喜你,成功闯过第二关简易级副本,获得评分SSS。请问你是否选择勇闯第二关普通级副本?叮咚~你选择了我没选我没选我说你选了你就选了第二关普通级副本,副本马上开启。

请使用OFFSET函数,直接从老家(B1单元格)出发,占领成都、江都、建宁和武陵(D5:E6)区域。

=OFFSET(B1,4,2,2,2)

OFFSET函数以B1单元格为基点,向下移动4行,然后向右移动2列,将基点移动到成都城,然后向下扩张2行,向右扩充2列,即为D5:E6区域。

叮咚~恭喜你,成功闯过第二关普通级副本,获得评分SS。请问你是否继续选择勇闯第二关普通级副本?叮咚~你再次选择了我没选我没选我说你选了你就选了第二关普通级副本,副本马上开启。

offset函数所有用法(OFFSET函数其实是个游戏机)(9)

请使用OFFSET函数,动态计算B列销售额最近8条记录的平均销售值。动态是个什么意思呢?就是当B列有新加的销量时,公式必须自动显示最新结果。

比如目前平均值区域是B7:B14;但当我在B15填入一个新的销量,平均值计算区域就应该自动更新为B8:B15……

公式如下:=AVERAGE(OFFSET(B1,COUNTA(B:B)-1,0,-8))COUNTA函数计算出B列非空单元格的个数,然后减去1,扣掉标题行。OFFSET函数以B1单元格为基点,以COUNTA函数的计算结果作为向下移动的行数,也就是B列有多少个销售记录,就向下移动多少行。此时基点移动到了B列最后的数值所在单元格,例如B14。第4参数指定了纵向扩张的行数:-8,意思是将基点向上扩充8行,于是得到单元格区域B7:B14。如果B列的数值增加了,COUNT函数的计数结果就增加了,OFFSET函数的结果也就会自动更新。最后使用AVERAGE函数计算出这个引用区域中的平均值。…………

今天和大家分享的内容就到这里,后会有期。

原载公众号:知识星球

图文作者:星光

,