編按：

日常工作中，我們必然會(huì)遇到各種日期問(wèn)題，需要對(duì)它進(jìn)行查找、計(jì)算……今天，小E給大家介紹的就是計(jì)算有效期天數(shù)的方法！從簡(jiǎn)單到較難的三個(gè)不同問(wèn)題，全面為大家解構(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ì)于小張來(lái)說(shuō)，是個(gè)小case。

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

不難的日期計(jì)算

沒(méi)過(guò)多久，公司出了新的規(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ù)雜了。

問(wèn)題分析：

這里面會(huì)涉及到每月天數(shù)、閏年和平年2月份天數(shù)不同的問(wèn)題，甚至涉及到平年跨到平年、平年跨到閏年等多種狀況。以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天：

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

思路：

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

操作：

在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ù)的位置，再通過(guò)INDEX函數(shù)返回?cái)?shù)組（或區(qū)域）中這個(gè)位置對(duì)應(yīng)的值。

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

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

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

通過(guò)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ù)制填充。

通過(guò)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ì)算中，用不到它?！?/span>

可以看到，在構(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ù)組

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

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

通過(guò)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ù)制填充。

通過(guò)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è)滿足條件的日期。

第4步：構(gòu)建余數(shù)數(shù)組

思路：

由于滿足條件的天數(shù)差值為10，-20，20，它們的一致規(guī)律就是除以10后求余數(shù)，均為0。從而，我們可以得出這樣結(jié)論：第一個(gè)為余數(shù)為0的數(shù)據(jù)，也對(duì)應(yīng)著日期數(shù)組中的第一個(gè)滿足條件的日期。

B2中的函數(shù)修改為：=MOD(DAY(A2+ROW($69:$81))-DAY(A2),10)，按Ctrl+Shift+回車鍵后，用鼠標(biāo)將公式向下復(fù)制填充。

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

={0;0;1;2;3;4;5;6;7;8;9;0;1}

第5步：通過(guò)MATCH函數(shù)，進(jìn)行匹配

通過(guò)MATCH函數(shù)，在余數(shù)數(shù)組中，匹配第一次出現(xiàn)0的位置。

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

通過(guò)F9鍵，可以查看B2中的計(jì)算結(jié)果，為1。（注：B2單元格中顯示為1900/1/1，在函數(shù)欄中通過(guò)F9鍵查看，顯示結(jié)果為1。）

至此，在構(gòu)建的日期數(shù)組中，第一個(gè)滿足條件的日期出現(xiàn)在第1個(gè)位置，大家再用INDEX函數(shù)去日期值數(shù)組中，返回第1個(gè)日期值，就OK了。

第6步：通過(guò)INDEX+MATCH函數(shù)組合，完成日期匹配

B2中的公式得到最終的完善，得到最開(kāi)始的公式：=INDEX(A2+ROW($69:$82),MATCH(0,MOD(DAY(A2+ROW($69:$81))-DAY(A2),10),0))。最后，與上述步驟一致，按Ctrl+Shift+回車鍵后，用鼠標(biāo)將公式向下復(fù)制填充。

這樣，令小張頭疼已久的有效期問(wèn)題就解決了。

親愛(ài)的小伙伴，本文中問(wèn)題的涉及到數(shù)組、MOD函數(shù)、MATCH函數(shù)、INDEX函數(shù)等綜合應(yīng)用，請(qǐng)務(wù)必多次練習(xí)，從而明白其中的奧義哦！

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

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

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

相關(guān)推薦：

到期日計(jì)算，EDATE甩DATE函數(shù)兩條街！

自動(dòng)提醒產(chǎn)品還有多少天過(guò)期的5種Excel方法

玩轉(zhuǎn)excel日期函數(shù)，一文看懂90%的日期運(yùn)算

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

版權(quán)申明：

本文作者阿碩；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。