大厂HR:有这种Excel思维的实习生,我真的想高薪留下来
★
编按
★
各位小伙伴们好~在上一期教程的开头我们提出了两个函数公式,一个是解决周次计算问题,另一个是解决周内排期问题。由于篇幅原因我们仅详细讲解了计算周次问题的函数与其内在逻辑。
今天我们接着讲解计算周内排期的函数和逻辑。值得一提的是,相比函数教学,老师更想通过这篇文章向大家传达Excel的学习和运用思维。话不多说,跟小E一起来学习吧!
上次我们提出了一个活动计划中增加周次和周内排期的问题,效果如图所示。
活动属于第几周的函数公式已经介绍完了,我们今天继续探讨周内排期的公式。
周内排期公式:
=TEXT(--SUBSTITUTE(LEFT(D2,FIND("-",D2)-1),".","-"),"周aaa")&"--"&TEXT(--SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-"),"周aaa")
这个公式看起来很长,其实原理并不复杂。就是从活动时间(D列)中把开始日期和结束日期分别提取出来后,再用TEXT函数将其显示为“周几”这样的格式。
可能有些同学会想到用WEEKDAY,这个函数当然可以的。但是请注意,WEEKDAY函数得到的结果是一个数字,要符合结果还需要转为中文数字并在前面加“周”字,公式会比现在这个更长。
扫码入群,下载Excel练习文件
1.提取开始日期和结束日期
好了,回到我们的问题。首先要解决的是:如何把开始日期和结束日期提取出来。
在周次的问题中,已经介绍了提取结束日期的公式,也就是MID(D2,FIND("-",D2)+1,9)这部分。提取开始日期可以用LEFT和FIND函数组合:=LEFT(D2,FIND("-",D2)-1) 结果如图所示。
2.替换分隔符转为标准日期
开始日期和结束日期的提取如果没问题的话,还是需要把日期中的小数点换成日期分隔符,这个在上次的教程也说的很详细了,套到今天的问题,就是:SUBSTITUTE(LEFT(D2,FIND("-",D2)-1),".","-")和SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-")。
在计算周次的时候,需要用WEEKNUM函数,计算星期时要用WEEKDAY函数,公式是这样的。
用开始日期为例:=WEEKDAY(J2,2)
3.用函数输出需要的格式
计算结果是一个数字,如果要显示为“周五”这样的格式,还需要用到NUMBERSTRING函数。这个函数可能很多同学都是第一次见到,而且这个函数在Excel中无法找到,是一个隐藏函数。
NUMBERSTRING函数的用法很简单:格式NUMBERSTRING (数值,类型)。
其中类型有三种:
1)为汉字小写;
2)为汉字大写;
3)为汉字读数。
本例中公式就是="周"&NUMBERSTRING(WEEKDAY(J2,2),1),前面用&连接一个周字。
同样的结果,用TEXT函数就会显得更简短。直接用=TEXT(J2,"周aaa")就可以咯。
再来回顾一下这个问题的解决思路:首先提取开始日期和结束日期;然后替换分隔符转为标准日期;最后用TEXT函数输出需要的格式。完成以后用&将开始日期和结束日期加分隔符连接起来。
还是那句话,这两个公式学习的价值胜于实用价值,如果真要解决问题的话,下面分享一个更高效的做法:把操作的思路和公式结合起来。
因为在这个活动列表中,看似行数很多,但真的按活动时间段筛选的话,实际上只有7种情况:
可以将这7个日期区间提取出来单独做个对照表,就是这样的:
红框内的两列哪怕就是手动填一遍,也用不了几分钟。但是这个列表做出来以后,只要你会VLOOKUP,就算有成千上万行数据,也就是双击一下的事情了。关于最后这个思路,就留给感兴趣的同学们自己实践一下。
想给各位同学一句忠告:遇到问题一定要看场合,如果比较急,而你一下子又不能马上研究出来公式的话,一个一个手动搞肯定不合适,死磕公式也不合适,不妨转换思路,换个角度可能会有更高效的方法呢。但是在问题处理完之后,很有必要返回来继续研究实用性并不大的那些公式,因为这是你不断提高和进步最好的试炼场。
如果有什么疑问或者想学的教程,欢迎大家在评论区留言哦。
今日互动话题
在评论区留下你的足迹叭~
你会用VLOOKUP解决这个问题吗?
在线咨询Excel课程
阅读推荐
关注我们,发现更多Excel优质教程
90后小姐姐面试想拿7K,老板说:连错位求和都不会,只值3K!
课程推荐
所谓自由,不是随心所欲,而是自我主宰
想要全面系统学习Excel,不妨关注部落窝教育《一周Excel直通车》视频课或者《Excel极速贯通班》。
最实用接地气的Excel视频课《一周Excel直通车》,风趣易懂,快速高效,带您7天学会Excel38 节视频大课(已更新完毕,可永久学习),理论+实操一应俱全。
主讲老师:滴答
Excel技术大神,资深培训师;课程粉丝100万+;
开发有《Excel小白脱白系列课》《Excel极速贯通班》。