二维码 购物车
部落窝在线教育欢迎您!

常用日期函数太简单?那是你不知道WEEKNUM函数

 

作者:郅龙来源:部落窝教育发布时间:2021-07-27 17:30:40点击:248

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。


编按:

大家好呀,这里是小E!今天小E要跟大家吹爆这个看起来复杂实际上好用到飞起的WEEKNUM函数。WEEKNUM函数属于日期函数中较为复杂的函数,在计算某个日期是第几周时尤其好用,计算成百上千行数据的日期,别人要用半天甚至一天,而你用这个函数只需要几分钟就搞定了!接下来我们就去学习一下这个函数的用法吧,还有老师为你层层拆解函数逻辑哦~

 

小马是某连锁企业的活动策划专员,7月份针对公司六个片区的近300+店面设置了活动计划。活动计划表如图所示,有活动起止日期、区域以及门店代码。其他信息与我们今天要讨论的问题基本无关,就不列出来了。

 

 

活动时间从72日到81日,每期活动基本在一周之内,具体的活动周期如图所示。

 

 

当小马把完成的活动计划表发给经理以后,经理提出两个优化建议。

第一,增加周次和周内排期两列,效果如下图所示。

 

 

第二,筛选一个区域的最终结果,效果如下图所示。

 

 

小张听明白经理的要求以后,马上开始行动,但是才弄了几十行就头大了。弄完将近两千行数据,这得一上午吧……

 

实际上解决这个问题有两条路,公式法和操作法

 

从使用角度来说,操作法效率其实更高,但是也不能浪费这样一个学习函数公式的好机会,所以咱们先来聊聊公式法。

 

计算周的公式:

=TEXT(WEEKNUM(--SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-"),12)-26,"[DBNum1]0")

 

 

周内排期的公式:

=TEXT(--SUBSTITUTE(LEFT(D2,FIND("-",D2)-1),".","-"),"aaa")&"--"&TEXT(--SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-"),"aaa")

 

 

公式看起来有点长,但是逻辑不复杂,先来看第一个公式。

 

第一个需要解决的问题是:根据活动时间段标出对应的周次。

 

一、提取结束日期

 

说起周次,熟悉日期函数的同学应该会想到WEEKNUM这个函数,这个函数的功能是计算某个日期是在一年中的第几周。

但本例中计算的周次并不是一个具体日期,而是一个日期区间7.2-7.4

 

第一步:要从这个日期区间先把日期提取出来,咱们就取结束日期;

从数据中可以发现结束日期都是“-”这个符号之后的内容。因此就能用MIDFIND这个组合套路,关于这个组合的用法,可以参考往期教程:https://mp.weixin.qq.com/s/PuoU4ZNlBJUAuIrme4bWOw

 

提取结束日期的公式为:=MID(D2,FIND("-",D2)+1,9)

 

 

第二步:上图日期的写法是用小数点分隔月和日。在Excel中,只有用“-”和“/”分隔的才是标准日期,。因此还需要对提取出来的日期做个处理,把小数点换成“-”,这就得用SUBSTITUTE函数实现。

 

关于这个教程的用法参考往期教程https://mp.weixin.qq.com/s/Ka2CT_nSGPvEUyMBErAuyg

 

这部分的公式是:=SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-")

 

 

 

第三步:现在看上去像是日期的标准写法了,但实际还是文本格式,需要继续转化。高手常用的方法是在公式最前面加两个减号,也就是=--SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-")

 

 

这样G2单元格里的数据就变成一个数字了,我们只需要把单元格格式改成日期格式就OK。至此就完成了结束日期的提取。顺带一提,其实公式是不受格式影响的,在公式中不管是按数字显示还是按日期显示,最终的计算结果都一样的。

 

二、计算周次

 

接下来解释周次的问题,周次是这项工作中最让人头大的一个问题。

第一步:为了便于大家理解,需要把整个活动中涉及到的时间区间先列举出来,再结合一个7月份的日历,来发现其中的规律。

 

 

可以看出,共有7个活动时间段,分别分布在五周。从我们前面提取出的每期活动的结束日期来看,有周一、周四、周日结束三种情况。每期活动基本都是3-4天,要将同一档活动归结在某一周的话,这里的周就不能是周一到周日这种习惯上的划分标准了。比较符合要求的是周二开始到周一结束这样的划分方式。

 

第二步:

恰好在WEEKNUM函数中,第二参数就可以灵活设置每周的起始点。如果要用周二作为一周的起点,第二参数使用12即可。WEEKNUM函数的规则为:WEEKNUM(日期,返回类型),其中返回类型就是选择周几作为一周的开始,共有以下选项:

 

 

公式为=WEEKNUM(--SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-"),12)

 

 

第三步:上面的步骤得到的是当年的第几周,而不是活动中的第几周,第一周显示的是27,需要在结果后面-26作为调整。

所以最终计算周次的公式为:=WEEKNUM(--SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-"),12)-26

 

 

重要说明:在计算周次的问题中,往往需要根据具体情况和实际需求去调整参数值,并且需要做加减量使得结果与实际相符,本例中的“12”和“-26”就是基于实际需要才得以确定的。

 

三、调整显示格式

 

折腾了半天只是得到一个数字,而且还不是第几周的这种格式,需要调整。这就是TEXT函数的拿手菜了。

在单元格中输入函数:=TEXT(H2,"[DBNum1]0")就可以实现格式转化。

 

 

需要重点说明的是第二参数格式代码的含义。

"[DBNum1]0"中,0是数字占位符,必须要的。[DBNum1]是指定数字的大写格式,可以试试将里面的1改成234能得到什么结果。“第”和“周”是数字前后的固定文字,没有特殊含义。最终就是第几周这样的格式了。

 

再来回顾一下这个公式,其实就三步:提取结束日期、计算周次、调整显示格式

但是其中所包含的信息量却是非常大的,值得每一位想学函数的同学去仔细研究。

关于第二个公式的讲解和针对本问题的操作法,咱们下次接着聊。

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

说明: IMG_256

相关推荐:

7个Excel小技巧,提高表格查看效率

Excel运用规范1:一个单元格只记录一条信息

快速整理不规范的Excel表格的7个公式

9条最实用的计算excel中关于日期的公式!(建议收藏)

版权申明:

本文作者郅龙;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。