Excel教程:WEEKNUM函数计算活动周期

来源:互联网 作者:

 

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


  

活动时间从7月2日到8月1日,每期活动基本在一周之内,具体的活动周期如图所示。


  


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

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


  


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


  


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


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


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


计算周的公式:

=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")


  


公式看起来有点长,但是逻辑不复杂。先来看第一个公式,第一个需要解决的问题是:根据活动时间段标出对应的周次



1

提取结束日期

说起周次,熟悉日期函数的同学应该会想到WEEKNUM函数,这个函数的功能是:计算某个日期是在一年中的第几周。但本例中计算的周次并不是一个具体日期,而是一个日期区间7.2-7.4。


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


从数据中可以发现结束日期都是“-”这个符号之后的内容。因此就能用MID和FIND这个组合套路,关于这个组合的用法,可以参考往期教程:

Excel教程:FIND+MID函数,乱序产品规格中提取型号


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


  


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


关于这个教程的用法参考往期教程:

Excel教程:SUBSTITUTE函数解决文本单元格的判断和计算,你会吗?


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


  


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


  


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


2

计算周次

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


【第一步】为了便于大家理解,需要把整个活动中涉及到的时间区间先列举出来,再结合一个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”就是基于实际需要才得以确定的。


3

调整显示格式

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


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


  


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

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


再来回顾一下这个公式,其实就三步:提取结束日期、计算周次、调整显示格式。但是其中所包含的信息量却是非常大的,值得每一位想学函数的同学去仔细研究。


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


资讯来源说明:本文章来自网络收集,如侵犯了你的权益,请联系:puerppt#163.com进行删除。

PPT模板

  • 现代古风通用PPT
  • 故宫国风演示文档PPT
  • 古风水墨简约模板PPT
  • 古风中国风环保主题PPT
  • 古风工作总结汇报模板PPT
  • 古风古典雅致通用模板PPT
  • 扁平插画手绘中小学生安全教育日PPT
  • 小学交通安全教育课件PPT
  • 小学交通安全教育课件PPT
  • 可爱卡通幼儿教学课件模板PPT

Excel模板

  • 单据粘贴单11
  • 出差申报单11
  • 出差申报单(2)1
  • 付款申请单11
  • 付款申请单(3)1
  • 入库单1
  • 领料单
  • 销售明细单1
  • 销售明细单(3)
  • 付款申请单(2)1

Word模板

  • 面试招聘
  • 社会招聘面试审批流程
  • 电话营销销售人员的招聘面试流程
  • 求职招聘登记表及面试记录表
  • 校园招聘面试结果评价表
  • 校园招聘求职者面试报告示范模板
  • 招聘面试评分记录表
  • 招聘面试评估表
  • 招聘面试评价表
  • 招聘面试记录表
优页文档

优页文档(www.youyedoc.com)是一家专注于分享高质量的PPT模板、Excel表格、Word模板的下载网站,1000+各行业优质设计师每日更新200+优质办公文档模板,满足各行业办公需求。海量office文档制作教程,致力于打造国内最大最权威的办公文档下载一站式服务平台

Copyright © 2021-2021 www.youyedoc.com. All Rights Reserved.   粤ICP备2021116258号

本站所有文档资源来源于互联网或作者上传,仅供学习研究使用,版权归作者所有,请勿用于商业用途,如果用于商业用途请联系作者,如果因为您将本站资源用于其他用途而引起的纠纷,本站不负任何责任。

如果本站内容无意中侵犯了您的版权,请联系youyedoc,我们会及时处理。