OFFSET函数（offset函数用来干嘛）

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

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

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

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

现在，假如你是刘备，你在B1单元格，你要走到成都（D5单元格）。

怎么走？

正常来说，有两条路。

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

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

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

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

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

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

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

来，找诸葛亮星光兄弟一起分析下现状。

咱们的基点，也就是老家，在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单元格
……
……
以此类推，即为结果。

那么反过来，又如何将C1:K1单元格区域的数据转换为A1:A9呢？
——别看我，侬自己想想。
叮咚~恭喜你，轻松闯过第一关普通级副本，获得评分SSS。
请问你是否选择勇闯第一关困难级副本？

叮咚~你选择了我没选我没选我说你选了你就选了第一关困难级副本，副本马上开启。
请使用OFFSET函数将A列的数据转换为三行三列，如C1:E3区域所示。
然后……请再使用OFFSET函数将C1:E3区域三行三列的数据，转换为A列单列数据……

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

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

——别发呆啊，该你上场了，请使用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函数直接生成一个区域的用法，常用于动态图表的数据源；而在函数处理和数据分析中，常作为其它聚合统计或查找匹配类函数的参数。比如统计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函数，动态计算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函数计算出这个引用区域中的平均值。
……
……

适当游戏益脑，沉迷游戏伤身，今天和大家分享的内容就到这里。身体要紧啊少年，一桶江山什么的也别要了。

原载：知识星球

图文作者：看见星光