Excel教程

Excel函数
Excel技巧

【EXCEL教程】根据录入时间进行排班表查询

当前位置:网站首页 > Excel教程

【EXCEL教程】根据录入时间进行排班表查询

* 来源: 【office教程学习】 * 作者: 【EXCEL教程】 * 发表时间: 2020-05-16 21:20:11 * 浏览: 21


亲爱的各位小伙伴们好!!


今天接到一位群员的求助,他有一份排班表,想根据录入的时间来查询出对应哪个班组!


看到这问题,那就是一个查找嘛,查找函数很多,方法也有很多,今天要用什么来解呢?还是先来看数据图吧!图001



数据图中很容易看出很有规律,

A列是日期,从1开始到31日结束(不要纠结2月31号)

第一行是月,每月占三列,分别是 三个班次,第二行就是班次了!

白班(8点-16点)

中班(16点-24点)

夜班(0点-8班)


数据源看到了,规则也弄清楚了,那就来看要求了!图002

其实要求很简单,B列给定一个日期和时间,C列求出对应班组!


从这要求来看,我们可以查找类函数,vlookup,index,lookup,当然还可以使用offset


为什么不用vlookup?因为它的出场费太高,咱请不起,所以找出场费相对较低的offset函数降低点成本吧!(其实是因为列数太多,全装进vlookup,会影响效率!)


先来认识一个OFFSET函数吧

怎么样,这函数的提示看起来好不好理解?

其实它就是一个偏移函数,给它一个基点,再告诉他偏移多少行,偏移多少列

然后它就能给你返回另一个单元格引用了(注意描述文字中的顺序哟!)


在这里它的用法如下:

Offset(基点,偏移行数,偏移列数)


PS:这里只描述了3个参数,如果要返回一个区域,可以用上第4和第5参数哟!


好啦,函数的功能也知道了,既然说到了是偏移,它会有基点(起点),那就把这基点设置为排班表中A2单元格了(你可能会问为什么是A2,我会告诉你为了偷懒)


第一个参数设置好了,那第二个参数偏移行数又怎么设置呢?

从数据源里看出来了,A列就是日期中的天(,数字1 到31,正好能用上!

所以我们可以使用 day(b3) 来返回日期中的天(日)用offset函数的参数2

(如果参数1不设置为a2,那这里还需要做一个加减法运算,不然位置就跑偏了)


第二个参数设置好了,那第三个参数呢?偏移列,在第一行能看到,每3列为一个月

既然有day可以取出天(日),那就有month可以取出月


英语好一些的同学猜的没错,这函数名和英文就是这么的巧,谁说英语不好学函数没影响的,英语好的看到开头,他都能猜出结尾来!


这里光取出来还没用,还得做矩阵运算,这样才能保证每月能移动3列

所以 参数三得是 month(b3)*3


到这里还没完,这一步只是实现了每月跳3列!还没实现取出对应班次数据呢!


在这里知道了它会偏移三列到D列,那我们接着看


白班在D列的左边第二列,所以在减2列才对

班在D列的左边第列,所以在减1列才

夜班的就不用减了,可为了统一运算那就减0吧


为了方便了解清楚结构,咱还是建个数据关系区吧

注:此表G列按时间必须按升序排序,否则结果出错!


关系表建好以后,就可以请出大明星vlookup了,还得墨迹一下,时间中按时来区分班别的,那这个时也正好有个英语单词叫Hour(这世上巧合的事情不多,遇到了就一定要盯紧),所以用它来出小时,再用vlookup去查找,再返回要减去的数字就OK了!


公式如下:

VLOOKUP(HOUR(B3),$G$2:$H$4,2)


关于此公式用法,已经有很多教程了,这里就不做详细解说了!


一步一步的写到这里,公式就写完了,接下来该爆全家富了!

=OFFSET(排班表!$A$2,DAY(B3),MONTH(B3)*3+VLOOKUP(HOUR(B3),$G$2:$H$4,2))


C3写好以后,双击填充就好啦!!!


有同学会说,不是减嘛?怎么用的是加呢?


其实吧,加负数和减正数结果是一样的,高兴怎么玩就怎么玩,反正结果对了就行!


很多同学认为视频教程看了就能做了,所以根本不会抽时间来学习和练习!


其实吧,除非这教程是用你的问题来做的,否则你看再多,自己不多练习是无法把它吸收掉的!


现在没时间练习不怕,可以加我们的群,我们会不定时选一些案例出来做讲解,说不定下一次的案例就是你的问题了!!


你学会了吗?

今天的分享就到这,如果教程对大家有用,希望大家多多分享点赞支持小编哦!你的每一次点赞和转发都是支持小篇坚持原创的动力。