編按：

大家好呀，這里是小E！今天小E要跟大家吹爆這個看起來復(fù)雜實際上好用到飛起的WEEKNUM函數(shù)。WEEKNUM函數(shù)屬于日期函數(shù)中較為復(fù)雜的函數(shù)，在計算某個日期是第幾周時尤其好用，計算成百上千行數(shù)據(jù)的日期，別人要用半天甚至一天，而你用這個函數(shù)只需要幾分鐘就搞定了！接下來我們就去學(xué)習(xí)一下這個函數(shù)的用法吧，還有老師為你層層拆解函數(shù)邏輯哦~

小馬是某連鎖企業(yè)的活動策劃專員，7月份針對公司六個片區(qū)的近300+店面設(shè)置了活動計劃?；顒佑媱澅砣鐖D所示，有活動起止日期、區(qū)域以及門店代碼。其他信息與我們今天要討論的問題基本無關(guān)，就不列出來了。

活動時間從7月2日到8月1日，每期活動基本在一周之內(nèi)，具體的活動周期如圖所示。

當(dāng)小馬把完成的活動計劃表發(fā)給經(jīng)理以后，經(jīng)理提出兩個優(yōu)化建議。

第一，增加周次和周內(nèi)排期兩列，效果如下圖所示。

第二，篩選一個區(qū)域的最終結(jié)果，效果如下圖所示。

小張聽明白經(jīng)理的要求以后，馬上開始行動，但是才弄了幾十行就頭大了。弄完將近兩千行數(shù)據(jù)，這得一上午吧……

實際上解決這個問題有兩條路，公式法和操作法。

從使用角度來說，操作法效率其實更高，但是也不能浪費這樣一個學(xué)習(xí)函數(shù)公式的好機會，所以咱們先來聊聊公式法。

計算周的公式：

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

周內(nèi)排期的公式：

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

公式看起來有點長，但是邏輯不復(fù)雜，先來看第一個公式。

第一個需要解決的問題是：根據(jù)活動時間段標出對應(yīng)的周次。

一、提取結(jié)束日期

說起周次，熟悉日期函數(shù)的同學(xué)應(yīng)該會想到WEEKNUM這個函數(shù)，這個函數(shù)的功能是計算某個日期是在一年中的第幾周。

但本例中計算的周次并不是一個具體日期，而是一個日期區(qū)間7.2-7.4。

第一步：要從這個日期區(qū)間先把日期提取出來，咱們就取結(jié)束日期；

從數(shù)據(jù)中可以發(fā)現(xiàn)結(jié)束日期都是“-”這個符號之后的內(nèi)容。因此就能用MID和FIND這個組合套路，關(guān)于這個組合的用法，可以參考往期教程：https://mp.weixin.qq.com/s/PuoU4ZNlBJUAuIrme4bWOw

提取結(jié)束日期的公式為：=MID(D2,FIND("-",D2)+1,9)

第二步：上圖日期的寫法是用小數(shù)點分隔月和日。在Excel中，只有用“-”和“/”分隔的才是標準日期，。因此還需要對提取出來的日期做個處理，把小數(shù)點換成“-”，這就得用SUBSTITUTE函數(shù)實現(xiàn)。

關(guān)于這個教程的用法參考往期教程https://mp.weixin.qq.com/s/Ka2CT_nSGPvEUyMBErAuyg

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

第三步：現(xiàn)在看上去像是日期的標準寫法了，但實際還是文本格式，需要繼續(xù)轉(zhuǎn)化。高手常用的方法是在公式最前面加兩個減號，也就是=--SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-")

這樣G2單元格里的數(shù)據(jù)就變成一個數(shù)字了，我們只需要把單元格格式改成日期格式就OK。至此就完成了結(jié)束日期的提取。順帶一提，其實公式是不受格式影響的，在公式中不管是按數(shù)字顯示還是按日期顯示，最終的計算結(jié)果都一樣的。

二、計算周次

接下來解釋周次的問題，周次是這項工作中最讓人頭大的一個問題。

第一步：為了便于大家理解，需要把整個活動中涉及到的時間區(qū)間先列舉出來，再結(jié)合一個7月份的日歷，來發(fā)現(xiàn)其中的規(guī)律。

可以看出，共有7個活動時間段，分別分布在五周。從我們前面提取出的每期活動的結(jié)束日期來看，有周一、周四、周日結(jié)束三種情況。每期活動基本都是3-4天，要將同一檔活動歸結(jié)在某一周的話，這里的周就不能是周一到周日這種習(xí)慣上的劃分標準了。比較符合要求的是周二開始到周一結(jié)束這樣的劃分方式。

第二步：

恰好在WEEKNUM函數(shù)中，第二參數(shù)就可以靈活設(shè)置每周的起始點。如果要用周二作為一周的起點，第二參數(shù)使用12即可。WEEKNUM函數(shù)的規(guī)則為：WEEKNUM(日期,返回類型)，其中返回類型就是選擇周幾作為一周的開始，共有以下選項：

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

第三步：上面的步驟得到的是當(dāng)年的第幾周，而不是活動中的第幾周，第一周顯示的是27，需要在結(jié)果后面-26作為調(diào)整。

所以最終計算周次的公式為：=WEEKNUM(--SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-"),12)-26

重要說明：在計算周次的問題中，往往需要根據(jù)具體情況和實際需求去調(diào)整參數(shù)值，并且需要做加減量使得結(jié)果與實際相符，本例中的“12”和“-26”就是基于實際需要才得以確定的。

三、調(diào)整顯示格式

折騰了半天只是得到一個數(shù)字，而且還不是第幾周的這種格式，需要調(diào)整。這就是TEXT函數(shù)的拿手菜了。

在單元格中輸入函數(shù)：=TEXT(H2,"第[DBNum1]0周")就可以實現(xiàn)格式轉(zhuǎn)化。

需要重點說明的是第二參數(shù)格式代碼的含義。

在"第[DBNum1]0周"中，0是數(shù)字占位符，必須要的。[DBNum1]是指定數(shù)字的大寫格式，可以試試將里面的1改成2、3、4能得到什么結(jié)果。“第”和“周”是數(shù)字前后的固定文字，沒有特殊含義。最終就是第幾周這樣的格式了。

再來回顧一下這個公式，其實就三步：提取結(jié)束日期、計算周次、調(diào)整顯示格式。

但是其中所包含的信息量卻是非常大的，值得每一位想學(xué)函數(shù)的同學(xué)去仔細研究。

關(guān)于第二個公式的講解和針對本問題的操作法，咱們下次接著聊。

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

７個Excel小技巧，提高表格查看效率

Excel運用規(guī)范1：一個單元格只記錄一條信息

快速整理不規(guī)范的Excel表格的7個公式

9條最實用的計算excel中關(guān)于日期的公式?。ńㄗh收藏）

版權(quán)申明：

本文作者郅龍；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。