用LEN函數(shù)與雙重“Month”判斷日期所屬季度
?
作者:阿碩來源:部落窩教育發(fā)布時(shí)間:2021-09-29 15:44:59點(diǎn)擊:2253
編按:
Hello小伙伴們,今天是10月中的某一天,屬于一年中的第四個(gè)季度。在日常工作中,根據(jù)日期計(jì)算季度,是很多小伙伴經(jīng)常會(huì)遇到的一個(gè)問題。解決這個(gè)問題的方法也很多,比如使用IF函數(shù)、LOOKUP函數(shù)、VLOOKUP函數(shù)、CHOOSE函數(shù)、INT函數(shù)等等,都可以實(shí)現(xiàn)這種計(jì)算。今天,阿碩老師給大家分享兩個(gè)計(jì)算季度的新方法。這兩種方法,都是利用數(shù)據(jù)分布上的某種神奇的“巧合”來完成季度的計(jì)算,有點(diǎn)“無巧不成書”的意思。下面就讓我們一起來學(xué)習(xí)吧!
方法一:LEN+乘冪法
如下圖所示:A列中為日期,我們打算在B列中計(jì)算A列中日期對(duì)應(yīng)的季度。先給出函數(shù)公式:在B2中輸入“=LEN(2^MONTH(A2))”,然后下拉復(fù)制填充公式至B13,即可得到季度的值。
下面我們分步驟來看一下這個(gè)公式是怎樣完成季度計(jì)算的?
剛才我們所寫的函數(shù)公式,最內(nèi)層的函數(shù)是MONTH函數(shù),它的作用是提取A列中日期對(duì)應(yīng)的月份值。所以,我們首先在C2中輸入“=MONTH(A2)”,然后下拉復(fù)制填充公式至C13,得到的結(jié)果如下圖所示。
接下來,讓我們來寫一個(gè)乘冪運(yùn)算的公式。在D2中輸入“=2^C2”,然后下拉復(fù)制填充公式至D13,得到的結(jié)果如下圖所示。
關(guān)于乘冪運(yùn)算,在這里著重給大家講一下。
(1)乘冪運(yùn)算的符號(hào)是“^”(和數(shù)字6在同一個(gè)鍵盤上面哦~);
(2)乘冪運(yùn)算的兩個(gè)參數(shù)分別是底數(shù)和冪次數(shù),位于“^”前面的是底數(shù),位于“^”后面的是冪次數(shù);
(3)乘冪運(yùn)算的意義,就是計(jì)算底數(shù)的N次方(N即為冪次數(shù)),也就是N個(gè)底數(shù)相乘;
(4)綜上,我們剛才輸入的公式“=2^C2”,它就是計(jì)算以2為底、以月份值為冪次數(shù)的乘冪運(yùn)算,也就是計(jì)算2的N次方。
由上圖可見,C2:C13中的數(shù)據(jù)依次為1、2、3、……、12,所以,D列中的乘冪運(yùn)算就是依次計(jì)算2的1次方、2的2次方、2的3次方……2的12次方,所以,得到的結(jié)果就依次為2、4、8……4096。
這時(shí),請(qǐng)大家注意觀察。有一個(gè)非常神奇的“巧合”出現(xiàn)了,如下圖所示。
可以看到,對(duì)于月份值為1、2、3的數(shù)據(jù),在乘冪運(yùn)算后,得到的結(jié)果分別為2、4、8,這三個(gè)數(shù)都是1位數(shù);對(duì)于月份值為4、5、6的數(shù)據(jù),在乘冪運(yùn)算后,得到的結(jié)果分別為16、32、64,這三個(gè)數(shù)都是2位數(shù);對(duì)于月份值為7、8、9的數(shù)據(jù),在乘冪運(yùn)算后,得到的結(jié)果分別為128、256、512,這三個(gè)數(shù)都是3位數(shù);對(duì)于月份值為10、11、12的數(shù)據(jù),在乘冪運(yùn)算后,得到的結(jié)果分別為1024、2048、4096,這三個(gè)數(shù)都是4位數(shù)。
因此,從季度的角度來觀察,我們可以發(fā)現(xiàn)一個(gè)規(guī)律:1季度、2季度、3季度、4季度對(duì)應(yīng)的乘冪運(yùn)算的結(jié)果,分別為1位數(shù)、2位數(shù)、3位數(shù)、4位數(shù)。
所以,接下來,我們只需計(jì)算一下D列中的數(shù)據(jù)是幾位數(shù),也就是數(shù)據(jù)的長度,就可以用這個(gè)數(shù)據(jù)長度來替代季度值了。計(jì)算長度的話,我們使用LEN函數(shù)。這個(gè)函數(shù)也比較簡單,我們只要在E2中輸入 “=LEN(D2)”,就OK了。輸入LEN函數(shù)公式之后,我們下拉復(fù)制填充公式至E13,得到的結(jié)果如下圖所示。
大家感受一下,這種方法,是不是很巧妙呢?
方法二:雙重MONTH法
小伙們,如果你還沉浸在剛才那個(gè)公式中的話,那么請(qǐng)?zhí)鰟偛诺乃季S,讓我們?cè)賮硌芯恳粋€(gè)新的“巧合”。我們延用上一種方法中的數(shù)據(jù)進(jìn)行操作,A列中依舊是剛才那組日期,我們還是在B列中計(jì)算季度?,F(xiàn)在,我們?cè)?span>B2中輸入“=MONTH(MONTH(A2)*10)”,然后下拉復(fù)制填充公式至B13,即可得到季度的值。
下面我們同樣分步驟來看一下這個(gè)公式是怎么完成季度計(jì)算的。
這個(gè)公式的最內(nèi)層函數(shù)也是MONTH函數(shù),同樣地,我們?cè)?span>C2中輸入“=MONTH(A2)”,然后將公式下拉復(fù)制填充至C13,就可以把月份值提取出來 ,得到的結(jié)果如下圖所示。
接下來,我們將剛才提取出的月份值,乘以10。這個(gè)就比較簡單啦!我們只需要在D2中輸入“=C2*10”,然后下拉復(fù)制填充公式至D13即可,得到的結(jié)果如下圖所示。
可以看到,D2:D13中的數(shù)據(jù)是10、20、30……120這樣一組以10為單位遞增的10的倍數(shù)。接下來,我們對(duì)D2:D13中的數(shù)據(jù)進(jìn)行一下數(shù)據(jù)格式的修改。我們將這些數(shù)據(jù)修改為日期格式,修改后得到的結(jié)果如下圖所示。
此時(shí),原來的10、20、30……120,就變成了1900/1/1/10、1900/1/20、1900/1/30……1900/4/29這樣的日期。這組數(shù)據(jù)由整數(shù)變?yōu)槿掌?,其中的邏輯是什么呢?我們現(xiàn)在也來給小伙伴們講一下。
這是因?yàn)?b>日期在Excel中的本質(zhì)是數(shù)值,在Excel中的最小的日期是1900/1/1,它對(duì)應(yīng)的數(shù)值是1,此后,日期每增加1天,它對(duì)應(yīng)的數(shù)值也增加1。反過來說,數(shù)值1對(duì)應(yīng)的是日期1900/1/1,此后數(shù)值每增加1,日期也增加1天。所以在本例中,D2中的數(shù)值10,變成了1900/1/10,D3中的數(shù)值20,變成了1900/1/20,D4中的數(shù)值30,變成了1900/1/30…… D13中的120,變成了1900/4/29。具體的對(duì)應(yīng)關(guān)系,如下圖所示。
在理解了數(shù)值和日期的對(duì)應(yīng)關(guān)系之后,我們?cè)賮砜匆幌?span>D列中的日期和季度有什么樣的“巧合”存在?細(xì)心的小伙伴們可能已經(jīng)發(fā)現(xiàn)了,月份值為1、2、3的日期,在D列中對(duì)應(yīng)的日期值均出現(xiàn)在1900年1月份,月份值為4、5、6的日期,在D列中對(duì)應(yīng)的日期值均出現(xiàn)在1900年2月份,月份值為7、8、9的日期,在D列中對(duì)應(yīng)的日期值均出現(xiàn)在1900年3月份,月份值為10、11、12的日期,在D列中對(duì)應(yīng)的日期值均出現(xiàn)在1900年4月份。
因此,從季度的角度來觀察,我們可以發(fā)現(xiàn)一個(gè)新的規(guī)律:1季度、2季度、3季度、4季度在D列中對(duì)應(yīng)的月份值,剛好為1、2、3、4。
于是,我們?cè)俅瓮ㄟ^MONTN函數(shù)來提取一下D列中日期的月份值,而這個(gè)月份值,剛好就是我們要計(jì)算的季度值了。好了,話不多說,現(xiàn)在我們?cè)?span>E2中輸入“=MONTH(D2)”,然后下拉復(fù)制填充公式到E13,即可得到季度值,如下圖所示。
好了,各位親愛的小伙伴,今天的學(xué)習(xí)就到這里,你學(xué)會(huì)了嗎?
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
Excel運(yùn)用規(guī)范1:一個(gè)單元格只記錄一條信息
9條最實(shí)用的計(jì)算excel中關(guān)于日期的公式?。ńㄗh收藏)
版權(quán)申明:
本文作者阿碩;同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!