Excel計算周內排期,這可能是最高級的方法
?
作者:郅龍來源:部落窩教育發(fā)布時間:2021-08-04 11:59:11點擊:3183
編者按:
各位小伙伴們好~在上一期教程的開頭我們提出了兩個函數公式,一個是解決周次計算問題,另一個是解決周內排期問題。由于篇幅原因我們僅詳細講解了計算周次問題的函數與其內在邏輯,今天我們接著講解計算周內排期的函數和邏輯。值得一提的是,相比函數教學,老師更想通過這篇文章向大家傳達Excel的學習和運用思維。話不多說,跟小E一起來學習吧!
上次我們提出了一個活動計劃中增加周次和周內排期的問題,效果如圖所示。
活動屬于第幾周的函數公式已經介紹完了,我們今天繼續(xù)探討周內排期的公式。
周內排期公式:
=TEXT(--SUBSTITUTE(LEFT(D2,FIND("-",D2)-1),".","-"),"周aaa")&"--"&TEXT(--SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-"),"周aaa")
這個公式看起來很長,其實原理并不復雜。就是從活動時間(D列)中把開始日期和結束日期分別提取出來后,再用TEXT函數將其顯示為“周幾”這樣的格式。
可能有些同學會想到用WEEKDAY,這個函數當然可以的。但是請注意,WEEKDAY函數得到的結果是一個數字,要符合結果還需要轉為中文數字并在前面加“周”字,公式會比現在這個更長。
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個日期區(qū)間提取出來單獨做個對照表,就是這樣的:
紅框內的兩列哪怕就是手動填一遍,也用不了幾分鐘。但是這個列表做出來以后,只要你會VLOOKUP,就算有成千上萬行數據,也就是雙擊一下的事情了。關于最后這個思路,就留給感興趣的同學們自己實踐一下。
想給各位同學一句忠告:遇到問題一定要看場合,如果比較急,而你一下子又不能馬上研究出來公式的話,一個一個手動搞肯定不合適,死磕公式也不合適,不妨轉換思路,換個角度可能會有更高效的方法呢。但是在問題處理完之后,很有必要返回來繼續(xù)研究實用性并不大的那些公式,因為這是你不斷提高和進步最好的試煉場。
本文配套的練習課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關注公眾號,可隨時隨地學習Excel:
相關推薦:
將excel中的數據按照指定數量進行重復,非得用VBA才能實現嗎?
Power Query的數據替換技巧比Excel函數更萬能!
版權申明:
本文作者郅龍;同時部落窩教育享有專有使用權。若需轉載請聯(lián)系部落窩教育。