Excel教程:如何通過起始時間,周期,自動計算結(jié)束日期和還有多少天結(jié)束?
?
作者:E圖表述來源:部落窩教育發(fā)布時間:2022-05-23 15:29:54點擊:13498
編按:
在EXCEL日常工作中,如果需求中帶有“自動”字樣,往往都會用到VBA,今天就來給大家講一講,如果不會VBA那該如何處理呢?比如下面這個通過起始時間、周期,來計算結(jié)束日期,以及還有多少天結(jié)束的情況!
最近看到一份有意思的需求,拿出來給大家分享一下。
需求如下:
1、在A列、B列填入內(nèi)容后,C列自動計算,但是“日”要是A列“日”的前一天。例如:A2=2021-08-06,B2=10,那么C2=2022-06-05;
2、根據(jù)C列內(nèi)容,自動計算從今天算起還有幾天到結(jié)束日期,今天也算1天。例如:C2=2022-06-05,那么D2=52;
3、D列字體需要自動有“變色功能”,大于30天為【黑色】,小于等于30天為【紅色】;
4、按照D列的天數(shù),把A、B、C三列自動填充背景色,7天以內(nèi)(含7天)為【紅色】,8至15天(含15天)為【黃色】,16至30天(含30天)為【藍色】,31至60天(含60天)為【綠色】,60天以上不標記顏色;
5、自動按照D列的天數(shù)升序排列。
初看需求以為是個“青銅”,細一看才發(fā)現(xiàn)原來是個“王者”。
基本每一個需求都有【自動】兩個字,那我們先給大家展示一下,如何在不使用VBA的情況下實現(xiàn)“自動”?
C列“預(yù)埋”函數(shù),因為不知道要輸入多少行內(nèi)容,10行?100行?….10000行?好吧,預(yù)埋到20000行也就是了。
首先,在名稱框中輸入單元格區(qū)域C2:C20000,然后按回車鍵;
此時就選中了C2:C20000單元格區(qū)域,然后輸入函數(shù)
=IF(AND(A2<>"",B2<>""),EDATE(A2,B2)-1,"")
按CTRL+ENTER組合鍵結(jié)束錄入,此時C2:C20000就有了“預(yù)埋”函數(shù)。
在D2單元格輸入函數(shù)=IF(C2="","",DATEDIF(TODAY(),C2,"d"))
注意一點:需求中沒有說結(jié)束日期小于當前日期的情況,所以我們可以給個提示詢問一下,或者直接用IFERROR函數(shù)容錯也可以。
然后選中D2單元格,使用“條件格式”設(shè)置字體顏色,如下圖所示:
再雙擊D2單元格的填充柄,填充至D20000單元格。
選中A2:C2單元格區(qū)域,根據(jù)需求使用“條件格式”依次新增4個條件格式規(guī)則,為每個規(guī)則設(shè)置不同的填充背景色。注意公式中需要鎖定列,如下圖所示:
然后我們再把“條件格式”中的【應(yīng)用于】參數(shù)的單元格區(qū)域改成【=$A$2:$C$20000】。
及此,“可以自動的需求部分”就給大家分享完了,我們來看看效果吧!
至于最后一個需求:自動排序,常規(guī)的操作下,排序是沒有“自動”功能的。
今天我們分享個簡單的VBA代碼,可以實現(xiàn)一鍵排序,如下:
Sub 排序()
With Sheets(1)
Set rg = .Range("A1:D" & .[B65000].End(3).Row)
rg.Sort key1:="還有幾天結(jié)束", order1:=xlAscending, Header:=xlYes
End With
End Sub
按ALT+F11,打開VBE界面,在左側(cè)工程欄,鼠標右鍵——插入——模塊,然后在代碼區(qū),輸入上面的代碼。
回到工作表界面,在工具欄——“開發(fā)工具”——插入——表單控件——按鈕控件,插入一個按鈕控件,按下圖設(shè)置內(nèi)容。
及此,此需求就全部完成了,我們看看排序的效果吧!
特別提醒:使用VBA后,一定要把文件另存為.XLSM格式的文件喲!
好啦,以上就是今天的所有內(nèi)容,感謝你的觀看!
本文配套的練習課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習Excel:
相關(guān)推薦:
如何提取唯一值?試試TEXTJOIN函數(shù)搭配VBA自定義!
版權(quán)申明:
本文作者E圖表述;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!