EXCEL高阶知识offset函数用法详解 excel offset函数 _生活经验

前文提到，会offset函数是面试要求“EXCEL操作能力较强”这类岗位的加分项。文章发出后已经有人询问这个函数的用法，本来学习EXCEL函数是要循序渐进的，不应该开始就讲，但是为了真的能为大家的面试加分，今天就来说说offset函数的用法。
首先看offset函数语法：
offset(reference, rows, cols, [height], [width])
再看EXCEL中给出的该函数解释：
以指定的引用为参考系，通过给定偏移量返回新的引用。
这个解释太过简单，估计很多人还没有理解，那我再把EXCEL中关于该函数的帮助文件（学习EXCEL很好的一个途径，就是查阅帮助文件！）放出来，见下图：

文章插图
看完了帮助文件，应该都有了大致的了解，下面咱们开始实操！offset函数和其他函数一样，都有语法和解释，但是offset和其他函数不一样的地方在于：不通过实操即使你把语法和解释都背会了都没用，因为只有通过实操你才能发现offset的应用场景，而善于发现函数的应用场景（解决问题的思路）才是最重要的！见下图：

文章插图
上图的练习算是热身，帮助大家理解offset函数语法含义的，接下来才进入到应用场景。
1、对前**名的数据名单进行公示
相信大家都会网络购物，应该碰到过商家类似的宣传：活动开始后前**名支付的订单额外赠送**
说点题外话：在2016年之前，这类的活动还基本都是真的（活动结束后，商家会公布名单），你去抢的话，如果抢到了，商家会兑现承诺的；2016年后，这类的活动则基本都是假的了，因为你无法自己证明你是多少名，名单掌握在商家手中，是否公布？如何公布？都是商家说的算了，所以这类的活动就不要参加了，那种前**分钟有优惠的才是可以参加的，因为你有支付成交时间，商家抵赖不了。
好了，回到正题，见下图：

文章插图
上图的左侧是系统中导出的含有用户ID和支付时间的数据源，并对数据源进行了初步加工（隐藏用户ID中间的字符，加上支付时间），最终要实现的效果是上图的右侧部分，即这个问题的理论表述是：如何把单列数据转成多列？
【EXCEL高阶知识offset函数用法详解 excel offset函数】大家注意观察，上图的右侧部分不仅仅是把单列数据转成了多列，还把原来竖向的排序方式变成了横向的排序，这样做的目的当然是照顾用户的阅读习惯（以用户思维设计数据分析产品，是一个数据分析从业者的职业素养）。
如何把单列数据转成多列？能难倒一大批人，当然你非要一个个复制过来另当别论，但是学会了offset函数，这个问题可以说在分分钟内就可以解决！
我们看最终写好的offset函数（上图中G2单元格位置）：
=OFFSET($D$1,COLUMN(A1) 6*(ROW(A1)-1),0,1,1)
这里额外用到了两个函数：column（主要作用是返回当前单元格在第几列，比如COLUMN(A1)返回的数字就是1）、row(主要作用是返回当前单元格在第几行，比如ROW(A1)返回的数字就是1) 。此外还用到了单元格绝对引用知识点，即$D$1，在列或者行前面加上$符号，表示把列或者行固定住了，当拖动公式时相应的地方会保持不动，这里把D1的列和行都加上了$，是因为我们的函数写好后既要向右拖、又要向下拖，而我们的目是在不管怎么拖，都要保证offset函数的参考系不动。
对函数中的COLUMN(A1) 6*(ROW(A1)-1)需要解释下，他的功能就是当向右拖动函数时，把原来的竖向排列变成横向的，当函数向下拖动时，就每隔6行（因为之前的单列数据现在被我们变成了6列数据）开始取数据。COLUMN(A1) 6*(ROW(A1)-1)其实是用了等差数列知识点。
有没有人对“不用函数就解决单列数据转多列”感兴趣？不管有没有，”数据分析小哥哥”额外赠送一段，请看下图：

文章插图
具体步骤：
（1）在G2单元格输入D2，然后向右拖动鼠标到L2，你会发现后面依次变成了D3、D4、…D7；
（2）回到G2，向下拖动鼠标到G3，你会发现G3位置上是D3，这时手动给它改成D8，然后继续向右拖动鼠标到L3，后面会依次变成D9、D10、…D13；
（3）同时选中G2到L3，然后向下拖动鼠标，做出上图的右侧部分效果来；
（4）见证奇迹的时候到了：你按下CTRL H，调出查找替换对话框，在“查找内容”那里输入D，在“替换为”那里输入=D，然后点“全部替换”，发生什么了？我的天呐！这么神奇吗？！！！（此处应有掌声，哦，不对，是转发和点赞）
2、快速查询**业务员**月的销售业绩
问题1只是offset函数的最基本用法，虽然也能惊艳一部分人，但是offset的真正神奇之处还没有体现出来，请看下图：

文章插图
现实工作中，领导总喜欢问这问那的，而我们不可能记得每一个数据，那么我们该如何快速的回答领导的问题呢？很显然，建立一套“数据查询模板”能很好的解决这个问题。
上图中：左边是基础的数据源，右边是查询框，其中“业务员姓名”和“销售月份”那里都设置了下拉菜单（对这个知识点感兴趣的可留言），可以灵活的选择业务员和月份，“业绩查询”那里用offset函数预先设置好了，于是：不管领导问哪个业务员、哪个月的销售业绩，都能迅速的查询出来。这里用到的函数是：
=OFFSET(B2,MATCH(Q3,B3:B10,0),MATCH(R3,C2:O2,0),1,1)
这里面嵌套了match函数，它的功能是查找特定的项（Q3）在某个区域（B3:B10）中的位置，参数“0”表示精确查找。于是上述这个函数的含义是：以B2单元格为参考系，向下的偏移量是“某业务员在业务员区域内的位置”，向右的偏移量是“某月份在月份区域内的位置”，返回的数据区域高度和宽度都是1 。
这个问题的解决，可能有人会认为offset函数也不过如此，这么想你就错了，因为我为了方便解释，举的例子比较简单，如果我为上述问题多加几个条件呢？比如加入部门、加入某几个月累计、加入同比、环比对比…你还会认为简单吗？这些条件如果都加入，那么这个查询框将彻底变成一个查询系统！整个报表的设计应该是这样的：
一个工作表里专门存储数据源（最好直接来自ERP）、一个工作表里专门存储按不同的业务逻辑进行初步运算的结果数据、一个工作表里专门设计一个复杂的查询系统，然后再美化下，加入说明、LOGO、各种可视化图表，一套《**公司**数据查询系统》就彻底上线了！这套系统将是模型化的、可视化的、动态化的，每天仅仅需要到ERP里把前一天的数据导出来放到“存储数据源”的那个工作表里即可，而这些功能实现的前提就是必须有offset函数和其他函数的各种复杂嵌套！
3、设计动态图表
请大家体谅”数据分析小哥哥”码字的辛苦，就不换其他数据了，还用问题2的数据：当我选不同的业务员时，实现下图的效果：

文章插图
上图中，选不同的业务员，下方的折线图会随着变化，变成“对应业务员的年度销售曲线图” 。
具体步骤：
（1）在EXCEL中依次点击：公式-名称管理器-新建，跳出如下对话框：

文章插图
在“名称”那里随便取个名字，我这里取的是“趋势线”，在引用位置那里输入如下函数：=OFFSET(业绩查询!$B$2,MATCH(业绩查询!$Q$3,业绩查询!$B$3:$B$9,0),1,1,12)
注意：“业绩查询”是我这里的工作表名称，大家练习时可以更换。另：关于名称管理器，有想深入学习的可以留言，我根据留言人数再决定是否专门写文章讲这个知识点。
（2）选择B2到N3数据，插入曲线图
（3）选中曲线-鼠标右键-选择数据，跳出如下对话框：

文章插图
（4）点上图红色方框的编辑，跳出如下对话框：

文章插图
把上图中“系列名称”那里的位置换成业务员姓名下拉框那里的位置，我这里是Q3位置；把“系列值”那里换成刚才定义的名称，我这里是“趋势线”，然后点击确定即可。
（5）这时返回工作表，你再在业务员姓名下拉框那里选择看看，是不是曲线图跟着变化了？神奇吧！
offset函数在动态图表中的应用远不止此，举例只是小试牛刀。
今天关于offset函数的应用其实还没讲完，只能说是才讲了常见的应用，算是告一段落，看完了没学会的麻烦多看几遍，还学不会的…呃…那就跟着”数据分析小哥哥”继续前行吧，今后可能会继续碰到这个函数的，总会学会的~~
“数据分析小哥哥”头条号，以后将不定时更新我在数据分析领域的见解，可能会有数据思维训练、数据分析过程解读、数据报告撰写、分析工具使用等方面的文章不断出现，如果你想学点数据分析方面的知识，想提升自己的数据分析能力，那么请跟着”数据分析小哥哥”一起前行吧，喜欢”数据分析小哥哥”的话，别忘了分享给你的同事、同学和朋友哦~
,