構(gòu)建計(jì)算有效期天數(shù)的EXCEL公式,只需幾個(gè)步驟!
?
作者:阿碩來(lái)源:部落窩教育發(fā)布時(shí)間:2021-04-12 11:28:28點(diǎn)擊:16822
編按:
日常工作中,我們必然會(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)系部落窩教育。
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- 一個(gè)公式搞定空行的動(dòng)態(tài)插入
- 多功能銷售日歷模板制作
- 用正則函數(shù)快速拆分單元格中的同類數(shù)據(jù)
- 用免費(fèi)AI將圖片批量轉(zhuǎn)表格
- 先入先出出入庫(kù)模板中出庫(kù)批次的自動(dòng)匹配
- 用SUBSTITUTE替換法為產(chǎn)品找到最匹配的類別
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)