offset函数的高级用法_【Excel函数教程】史上最弱的一篇offset函数教程，让你见识什么叫凌波微步！...

OFFSET你去哪了？千呼万唤始出来

小菜鸟：OFFSET，在不，在不，你在不在吖……

OFFSET：来了，来了，别喊了，魂都被你喊出来了。

小菜鸟：老菜鸟答应让你来陪我们玩的，你咋这么久不出现？

OFFSET：因为我找到一本秘籍《凌波微步》，太好玩了，一下子就忘了时间，让大家久等了，罪过罪过！

小菜鸟：很多人都觉得你好神秘的，还难以捉摸，我也看不懂你，今天你可得好好给我们说道说道。

OFFSET：咦，我挺简单的啊，为什么会让大家觉得神秘呢，还不可捉摸，这有点夸张啊。

小菜鸟：真的是有点难以捉摸啊，你看，VLOOKUP我都能学会，但是只要有你出现的公式，基本上都是蒙圈的状态，完全看不懂

OFFSET：你要说大V的话，那我就知道了，我和他压根不是一个玩法，你按照和他的玩法来找我玩，那不蒙圈才怪呢。

小菜鸟：就是不懂么，那到底该怎么和你玩呢？

OFFSET：老菜鸟没教过你么，要和函数玩，你得先懂参数，不懂参数就没得玩。这样吧，我先告诉你我的五个参数都是干啥的吧，其实我真的很简单……

OFFSET的参数五个参数里四个都是数字，还有两个可以省略……

OFFSET：首先你要搞明白，我和VL完全不是一个套路，VL是内容至上，按照指定的内容去返回你需要的另一个内容。而我是位置至上，因此我的这些参数，都是为了确定一个位置而存在的。

小菜鸟：一个是内容，一个是位置，还是似懂非懂，能不能举例说明啊。

OFFSET：可以啊，先来一个简单的例子，只用三个参数。

=OFFSET(A1,3,2)的意思就是A1单元格向下3行向右2列的单元格C4中的值。这个能不能理解？

小菜鸟：只看这个例子好像挺简单，也能看明白。

OFFSET：重点是我返回的是C4这个位置，而不是52这个结果，如果这个不能理解的话，也不勉强你，以后再说吧。

我问下你，比如要找销售6组，3月份的数据，公式该怎么改？

小菜鸟：额，这个我得想想，A1向下6行，向右3列，对不对？

OFFSET：我问的是公式咋改，不是你咋想，想出来了自己改，改好了自己试一下结果对不对，别啥都问，这么点自信都没有，没函数愿意和你玩的。

小菜鸟：好吧，我来改下，=OFFSET(A1,6,3)，公式改成这样。我再来验证一下……

噢耶，正确！

OFFSET：我是不是没那么神秘了？
通过这个例子，你对前三个参数应该有点明白了吧，再来给你解释一下。
第一参数是基点（坐标原点），可以是一个单元格，也可以是一个单元格区域，不可省略；
第二参数是行偏移数，正数向下，负数向上，默认为0，不可省略；
第三参数是列偏移数，正数向右，负数向左，默认为0，不可省略。

小菜鸟：这三个参数我大概理解了，还有两个呢？
还有就是明明2、3这两个参数也是可以省略的，我明明见过大佬这样用过，为啥你说不可省略？
还有就是既然你这么简单，为啥感觉就是不会用呢？
还有就是……

OFFSET：打住打住，你是十万个为什么吗？
饭要一口一口吃，问题要一个一个说。
还记得我一开始说的吗，我是位置至上！
如果需要的只是一个单元格那么大的位置，后面两个参数就别写了，如果需要的是好几个单元格那么大的位置，就要说清楚高度和宽度。
什么是高度和宽度你知道吗？

小菜鸟：你以为我真是菜鸟吗，高度就是一个区域的行数，宽度就是一个区域的列数，一个单元格就相当于高度和宽度都是1的区域，这个我知道呢！

OFFSET：所以最后两个参数的意思就是：
第四参数是返回引用的高度，正数表示向下多少行，负数表示向上多少行，默认为1，可以省略，不能为0；
第五参数是返回引用的宽度，正数表示向右多少列，负数表示向左多少列，默认为1，可以省略，不能为0。
具体什么时候该用，那就看你遇到的问题是什么了，但是两个参数的效果不像前面两个那么直观，可以借助SUM函数来看看。

OFFSET(A1,1,1,3,1)可以得到三个单元格组成的区域，求和结果证明了这一点。

这个公式看懂了吗？

小菜鸟：等下我瞅瞅……A1,1,1这三个参数确定了一个单元格，3,1这两个确定了区域的大小，所以就最终确定了一个区域，理解的对吗？

OFFSET：用你自己能明白的话描述，按照自己的想法结合测试去验证就行，对于同样的一个概念，不同的人可能会有不同的叙述方式和理解方式，这也是我被很多人误解的一个原因，其实我真的很简单。

小菜鸟：好吧，这五个参数我会了，那另外的几个问题呢？

参数的特殊用法尤其要搞清楚默认和省略的区别

OFFSET：先说一个刚才你没问的问题，其实第一参数也可以使用区域的，例如公式OFFSET(A1:A3,1,1)和OFFSET(A1,1,1,3,1)效果其实一样的。

小菜鸟：额，还没见过这种。
也就是说当OFFSET第一参数使用单元格区域，并且省略第四、五参数时，得到的新区域大小形状与第一参数一致……

OFFSET：bingo，你终于学会思考了！

小菜鸟：别吵，我要去验证一下如果第一参数用了区域，后面有指定不同的高度和宽度，会有什么情况发生，似乎挺有趣欸

OFFSET：等下再去验证吧，我把省略和默认的事情先给你说明白。

小菜鸟：好的吧，那你继续……

OFFSET：首先你要了解很重要的一个点，就是公式里的逗号。

=OFFSET(A1,3,2)和=OFFSET(A1,3,2,,)

这两个公式别看只是差了两个逗号，意义却是不一样的。

第一个公式中，有两个逗号，说明函数里只有三个参数，第二个公式中，有四个逗号，说明函数里有五个参数，只不过有两个没写具体的参数值，所以就用了默认值。

小菜鸟：等下等下，说慢点，听得有点晕呢……

OFFSET：这两个对比说明最后两个参数是可以省略也有默认值。
=OFFSET(A1,,)这个公式也不算错，省略了两个参数，默认了两个参数。
但是=OFFSET(A1)就不行，如果你真的这么写了，Excel会告诉你“你为此函数输入的参数太少”。

这是因为第二、三两个参数是不能省略的！

小菜鸟：我一直没清楚这个状况，原来根源在这呢！

OFFSET：对啊，就拿你已经学会的VLOOKUP来说，最后一个参数既可以省略，也可以默认，通常默认也被说成忽略，但是忽略和省略却是完全不一样的，省略表示模糊匹配，忽略则是精确匹配，区别其实就那一个逗号的事情。

小菜鸟：我还以为自己会了VLOOKUP呢，搞半天还是不会呀。

OFFSET：函数就是这样的，你以为会的时候，其实和他并不熟悉，只是打过交道照了个面而已，真要了解函数就得和他多玩。

别哭啦，一会我带你玩凌波微步，太好玩了。

小菜鸟：好呀好呀，凌波微步在哪，怎么玩的？

OFFSET的成名绝技凌波微步&心法口诀

OFFSET：你先看这个，他们是一个方阵，我可以把他们搞成一字长蛇阵。

小菜鸟：额，这是啥玩意，和凌波微步有关系吗？

OFFSET：必须有呀，你看看这个公式你看得懂不？=OFFSET($A$1,MOD(ROW(A5),5)+1,INT(ROW(A5)/5)-1)

小菜鸟：对对对，就是这种的，一看见这种的就晕，本来觉得你挺难的，后来和你聊了这么久，觉得你挺简单的，现在一看这个，觉得你又难了。

OFFSET：哈哈哈，那你一定是看到我耍凌波微步，然后有点发晕了，我给你看看步法口诀你可以就明白了。

其实玩凌波微步，只要学会步法口诀，然后2、3两个参数用上口诀就好了。

小菜鸟：…………………………

OFFSET：我还能把一字长蛇阵再给他变回方阵呢，你看这个。

小菜鸟：………………

OFFSET：还有超好玩的隔空取数，跨行跨列合并数据……跑快点，前面好玩的可多呢。

小菜鸟：你倒是等等我啊，我还在一字长蛇阵里出不来呢

OFFSET：对了，聊了好久，你到底是妹妹还是弟弟啊，妹妹的话就等你，弟弟的话你自己找老菜鸟学凌波微步去，不然真的就一辈子待在一字长蛇阵算了，哈哈哈。
我去也……

本文素材取自《菜鸟的Excel函数修炼手册》第15课：这个函数能玩瞬移——OFFSET函数值得拥有

最弱的函数教程目前已有两篇，除了本篇还有【Excel函数教程】史上最弱的一篇vlookup教程，再学不会你就可以放弃了！

还想看什么函数请留言。

对了，和offset聊天的这个小菜鸟到底是妹妹还是弟弟呢，哪位朋友能告诉我一下……

推一下我新出的书，也就是这本，反响非常好，出版社的主编说“第一次看到这本书的时候感觉非常好，这么多年能把函数用这种风格的语言讲出来的，几乎没有”。因此推荐过需要从头系统学习函数的小伙伴，不夸张地说，看了肯定能受益，毕竟书里的内容，都是我自己踩过的坑。而且有小伙伴说他解决工作中的问题基本上都是拿书里的示例直接套用，点击图片下面的链接可以免费阅读，觉得不错再购买。

《菜鸟的Excel函数修炼手册》(郅龙)【简介_书评_在线阅读】 - 当当图书