|
亲爱的各位爱知趣的小伙伴们好！！ 今天在爱知趣3群接到一位群员的求助，他有一份排班表，想根据录入的时间来查询出对应哪个班组！ 看到这问题，那就是一个查找嘛，查找函数很多，方法也有很多，今天要用什么来解呢？还是先来看数据图吧！图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写好以后，双击填充就好啦！！！ 有同学会说，不是减嘛？怎么用的是加呢？ 其实吧，加负数和减正数结果是一样的，高兴怎么玩就怎么玩，反正结果对了就行！ 很多同学认为视频教程看了就能做了，所以根本不会抽时间来学习和练习！ 其实吧，除非这教程是用你的问题来做的，否则你看再多，自己不多练习是无法把它吸收掉的！ 现在没时间练习不怕，可以加我们的群，我们会不定时选一些案例出来做讲解，说不定下一次的案例就是你的问题了！！ 你学会了吗？ |
|
|
