【原】我面試了幾十個大學生都不達標，發(fā)現(xiàn)他們竟連這個日期公式都不會！

Excel教程平臺 2021-04-12

展開全文

每天一點小技能

職場打怪不得慫

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

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

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

最簡單的日期計算

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

操作：在B2中輸入“=A2+10”，用鼠標向下復制填充即可。

不難的日期計算

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

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

難度升級的日期計算

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

問題分析：

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

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

公眾號回復：入群，群里下載練習課件

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

思路：

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

操作：

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

這樣，就完美的計算出了對應的有效期！

函數(shù)分析：

INDEX+MATCH函數(shù)是Excel中的經典組合，先用MATCH函數(shù)匹配到符合條件的數(shù)據的位置，再通過INDEX函數(shù)返回數(shù)組（或區(qū)域）中這個位置對應的值。

公式看起來很長，其實理解起來很簡單！下面，筆者就為大家一一剖析構成這個公式的步驟：

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

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

通過F9鍵，可以查看B2中的計算結果，如下：

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

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

補充檢測小技巧：

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

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

通過F9鍵，可以查看C2中的計算結果，如下：

={"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ù)的計算中，用不到它?！?/span>

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

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

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

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

通過F9鍵，可以查看B2中的計算結果，如下：

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

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

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

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

通過F9鍵，可以查看B2中的計算結果，如下：

={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ù)組中，第一個天數(shù)差值為10的整數(shù)倍的數(shù)據，就對應著日期數(shù)組中的第一個滿足條件的日期。