EXCEL高阶知识offset函数用法详解 excel offset函数

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

EXCEL高阶知识offset函数用法详解 excel offset函数

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

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

文章插图
上图的左侧是系统中导出的含有用户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)其实是用了等差数列知识点 。
有没有人对“不用函数就解决单列数据转多列”感兴趣?不管有没有,”数据分析小哥哥”额外赠送一段,请看下图:
EXCEL高阶知识offset函数用法详解 excel offset函数

文章插图
具体步骤:
(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的真正神奇之处还没有体现出来,请看下图:
EXCEL高阶知识offset函数用法详解 excel 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的数据:当我选不同的业务员时,实现下图的效果:
EXCEL高阶知识offset函数用法详解 excel offset函数

文章插图
上图中,选不同的业务员,下方的折线图会随着变化,变成“对应业务员的年度销售曲线图” 。
具体步骤:
(1) 在EXCEL中依次点击:公式-名称管理器-新建,跳出如下对话框:
EXCEL高阶知识offset函数用法详解 excel offset函数

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

文章插图
(4) 点上图红色方框的编辑,跳出如下对话框:
EXCEL高阶知识offset函数用法详解 excel offset函数

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