【原】我面試了幾十個(gè)大學(xué)生都不達(dá)標(biāo)，發(fā)現(xiàn)他們竟連這個(gè)日期公式都不會(huì)！

Excel教程平臺(tái) 2021-04-12

展開全文

每天一點(diǎn)小技能

職場(chǎng)打怪不得慫

編按：日常工作中，我們必然會(huì)遇到各種日期問題，需要對(duì)它進(jìn)行查找、計(jì)算……，如果連這些都不掌握，可能面試都過不了！今天，小E給大家介紹的就是計(jì)算有效期天數(shù)的方法！從簡(jiǎn)單到難的三個(gè)不同問題，全面為大家解構(gòu)公式的組成和應(yīng)用！

在EXCEL中，日期的本質(zhì)是數(shù)值，所以一般情況下，在計(jì)算有效期的時(shí)候，只要適當(dāng)?shù)剡\(yùn)用日期函數(shù)，基本上都能夠得出預(yù)期的結(jié)果?？墒?，當(dāng)遇到某些奇葩的有效期計(jì)算要求的時(shí)候，就要多殺死一些腦細(xì)胞了。

小張就職于一家寵物食品公司，對(duì)寵物食品的保質(zhì)期有嚴(yán)格的要求。小張的日常工作之一就是對(duì)保質(zhì)期進(jìn)行計(jì)算和管理。

最簡(jiǎn)單的日期計(jì)算

原本呢，公司生產(chǎn)的寵物食品的保質(zhì)期，規(guī)定為70天。所以，要計(jì)算70天有效期的話，只需將生產(chǎn)日期加上70就可以了，這對(duì)于小張來說，是個(gè)小case。

操作：在B2中輸入“=A2+10”，用鼠標(biāo)向下復(fù)制填充即可。

不難的日期計(jì)算

沒過多久，公司出了新的規(guī)定，規(guī)定保質(zhì)期為2個(gè)月零10天。這種情況下的有效期計(jì)算也在小張的射程范圍之內(nèi)——先用EDATE函數(shù)求得兩個(gè)月之后的日期，然后再加10，就行了。

操作：在B2中輸入“=EDATE(A2,2)+10”，用鼠標(biāo)向下復(fù)制填充即可。

難度升級(jí)的日期計(jì)算

近日，公司又出了更奇葩的規(guī)定——將保質(zhì)期定為2個(gè)月零10天的同時(shí)，要求有效期的最后一位數(shù)字與生產(chǎn)日期最后一位數(shù)字保持一致！這下，情況就變得復(fù)雜了。

問題分析：

這里面會(huì)涉及到每月天數(shù)、閏年和平年2月份天數(shù)不同的問題，甚至涉及到平年跨到平年、平年跨到閏年等多種狀況。以2019年12月20日為例，向后推移2個(gè)月，應(yīng)為2020年2月20日，2020年2月20日之后的第一個(gè)尾數(shù)為0的日期是2020年3月10日。

在公司的這種規(guī)定下，天數(shù)差就不再是一個(gè)有明確規(guī)律的數(shù)字了，最低天數(shù)差值為69天，而最高的天數(shù)差值則高達(dá)81天：

公眾號(hào)回復(fù)：入群，群里下載練習(xí)課件

小張一籌莫展，這真的是難死“寶寶”了。

思路：

通過上面對(duì)于天數(shù)差值的分析，大家可以看出，有效期與生產(chǎn)日期之間的天數(shù)差值范圍，在69天至81天之間。先將生產(chǎn)日期往后推69天至81天的天數(shù)構(gòu)成一個(gè)數(shù)組，然后再在這個(gè)數(shù)組中，把第一個(gè)與生產(chǎn)日期天數(shù)的最后一位數(shù)字一致的天數(shù)匹配出來。用數(shù)組來解決這個(gè)問題。

操作：

在B2中的輸入：=INDEX(A2+ROW($69:$82),MATCH(0,MOD(DAY(A2+ROW($69:$81))-DAY(A2),10),0))，按Ctrl+Shift+回車鍵后，用鼠標(biāo)將公式向下復(fù)制填充。

這樣，就完美的計(jì)算出了對(duì)應(yīng)的有效期！

函數(shù)分析：

INDEX+MATCH函數(shù)是Excel中的經(jīng)典組合，先用MATCH函數(shù)匹配到符合條件的數(shù)據(jù)的位置，再通過INDEX函數(shù)返回?cái)?shù)組（或區(qū)域）中這個(gè)位置對(duì)應(yīng)的值。

公式看起來很長(zhǎng)，其實(shí)理解起來很簡(jiǎn)單！下面，筆者就為大家一一剖析構(gòu)成這個(gè)公式的步驟：

第1步：構(gòu)造日期數(shù)組

在B2中輸入公式如下：=A2+ROW($69:$81) ，按Ctrl+Shift+回車鍵后，用鼠標(biāo)將公式向下復(fù)制填充。

通過F9鍵，可以查看B2中的計(jì)算結(jié)果，如下：

={44286;44287;44288;44289;44290;44291;44292;44293;44294;44295;44296;44297;44298}

其中顯示的是日期對(duì)應(yīng)的數(shù)值，不太容易看清楚效果。

補(bǔ)充檢測(cè)小技巧：

大家可以增加一列輔助列，利用TEXT函數(shù)將數(shù)值改成日期顯示。

在C2中輸入公式：=TEXT(A2+ROW($69:$81),"yyyy-m-d")，按Ctrl+Shift+回車鍵后，用鼠標(biāo)將公式向下復(fù)制填充。

通過F9鍵，可以查看C2中的計(jì)算結(jié)果，如下：

={"2021-3-31";"2021-4-1";"2021-4-2";"2021-4-3";"2021-4-4";"2021-4-5";"2021-4-6";"2021-4-7";"2021-4-8";"2021-4-9";"2021-4-10";"2021-4-11";"2021-4-12"}

【注：TEXT函數(shù)在后續(xù)的計(jì)算中，用不到它。】

可以看到，在構(gòu)造出的數(shù)組中，天數(shù)尾數(shù)為1的有3個(gè)，分別為2021年3月31日、2021年4月1日、2021年4月11日，這些天數(shù)的尾數(shù)都與2021年1月21日的天數(shù)尾數(shù)一致。

第2步：構(gòu)建天數(shù)數(shù)組

通過DAY函數(shù)將構(gòu)造出的日期數(shù)組中的每一個(gè)日期的天數(shù)提取出來，構(gòu)成一個(gè)新的數(shù)組。

B2中的公式擴(kuò)展為：=DAY(A2+ROW($69:$81))，按Ctrl+Shift+回車鍵后，用鼠標(biāo)將公式向下復(fù)制填充。

通過F9鍵，可以查看B2中的計(jì)算結(jié)果，如下：

={31;1;2;3;4;5;6;7;8;9;10;11;12}

第3步：構(gòu)建天數(shù)差值數(shù)組

將天數(shù)數(shù)組中的天數(shù)與生產(chǎn)日期中的天數(shù)相減，以求得天數(shù)差值。

B2中的函數(shù)第二次擴(kuò)展為：=DAY(A2+ROW($69:$81))-DAY(A2)，按Ctrl+Shift+回車鍵后，用鼠標(biāo)將公式向下復(fù)制填充。

通過F9鍵，可以查看B2中的計(jì)算結(jié)果，如下：

={10;-20;-19;-18;-17;-16;-15;-14;-13;-12;-11;-10;-9}

肉眼觀察即知，與2021年1月21日的日期尾數(shù)相同的天數(shù)與2021年1月21日的天數(shù)差為10（2021年3月31日），-20（2021年4月1日），-10（2021年4月11日），在天數(shù)差值數(shù)組中，第一個(gè)天數(shù)差值為10的整數(shù)倍的數(shù)據(jù)，就對(duì)應(yīng)著日期數(shù)組中的第一個(gè)滿足條件的日期。