自動提醒產(chǎn)品還有多少天過期的5種Excel方法
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2020-07-06 14:51:49點(diǎn)擊:15903
編按:
哈嘍,大家好!產(chǎn)品還有多少天過期?合同還有多久到期?距離高考還有多少天?關(guān)于這些何時到期的自動提醒,我們可以使用excel中的到期提醒功能實(shí)現(xiàn)。今天將給大家提供5種制作到期提醒的方法,第一種最簡單,最后一種最人性化并且能實(shí)現(xiàn)篩選控制。趕緊來看看吧!
在平時的工作應(yīng)用中,我們都喜歡用Excel表格來記錄整理數(shù)據(jù),數(shù)據(jù)里會包含到期時間,比如訂單到期日期、合同到期日期、產(chǎn)品到期日期等。
對于這類數(shù)據(jù),我們希望能夠有一個到期提醒功能來幫助我們管理數(shù)據(jù),這也是很多讀者經(jīng)常提到的一類問題。
如何在Excel里實(shí)現(xiàn)這么一個日期到期提醒功能,就是我們今天要討論的話題,由淺入深分為五層境界,以下用一個藥品的有效期數(shù)據(jù)為例,下面和大家一層一層來了解,原始數(shù)據(jù)如圖所示。
第一層境界:備注列計算到期天數(shù)
這個方法是最簡單最初級的,只需要用到一個函數(shù)TODAY,該函數(shù)不需要參數(shù),可以直接返回當(dāng)前的系統(tǒng)日期,用到期日期-當(dāng)前日期即可得到還有多少天到期,公式為:=D4-TODAY()
注意兩點(diǎn):
1.如果輸入公式后顯示不正確,可以將單元格格式修改為常規(guī);
2.因?yàn)槭褂昧?span>TODAY()函數(shù),所以每天打開表格時備注欄的數(shù)字都是會發(fā)生變化的,表示距到期日還有多少天,如果是負(fù)數(shù)說明已經(jīng)過期了。
第二層境界:更加人性化的備注信息
這一次需要用到TEXT來實(shí)現(xiàn)我們想要的效果,對負(fù)數(shù)統(tǒng)一顯示為“已過期”,對正數(shù)顯示為“還有多少天到期”,公式為:=TEXT(D4-TODAY(),"還有0天到期;已過期;;")
只是利用了TEXT函數(shù)對第一層得到的天數(shù)做了一些處理,關(guān)于TEXT函數(shù)的用法,可以參閱以往的相關(guān)教程《996和955到底差了多少小時,你會算嗎?》。
第三層境界:只顯示需要關(guān)注的信息
備注欄如果顯示全部的結(jié)果,其實(shí)并不能突出需要關(guān)注的重點(diǎn)數(shù)據(jù),假如只對未來30天以內(nèi)到期的數(shù)據(jù)做提示,超過30天的不顯示任何內(nèi)容,這樣的結(jié)果看起來會更加直觀,此時讓IF出馬更為合適,公式進(jìn)一步優(yōu)化為:=IF(D4-TODAY()<31,TEXT(D4-TODAY(),"還有0天到期;已過期;;"),"")
使用公式也就只能做到這個程度了,如果還要提升境界,就必須條件格式登場。
第四層境界:條件格式控制顯示效果
這一次要實(shí)現(xiàn)的效果是讓30天內(nèi)到期的數(shù)據(jù)整行顯示黃色,已過期的整行顯示紅色,這個要求涉及到兩條規(guī)則,設(shè)置步驟如下。
首先選中數(shù)據(jù)區(qū)域,再打開條件格式中的新建規(guī)則,選擇使用公式確定要設(shè)置格式的單元格,公式輸入=$D4
操作要點(diǎn):只選擇需要應(yīng)用條件格式的區(qū)域,也就是從第4行開始選。
重復(fù)這個過程,再次設(shè)置條件格式,區(qū)別就是公式變成=$D4-TODAY()<31,再看一次動畫演示。
操作要點(diǎn):設(shè)置完成后在條件格式的管理規(guī)則中調(diào)整一下兩條規(guī)則的順序。
公式中的<31也可以改成<=30,這個很好理解。
如果還需要對60天內(nèi)到期的數(shù)據(jù)做提醒,對應(yīng)增加規(guī)則就可以了。
比這個效果更高級的那就是增加了按鈕來控制到期提醒,這就要用到控件了。
第五層境界:利用按鈕控制到期提醒
該設(shè)置分為兩部分操作,控件按鈕的添加和條件格式的設(shè)置,先來看看控件怎么用。
添加控件不是很難,選擇選項(xiàng)按鈕后在表格里拖動一個矩形框就可以完成添加。
添加后修改說明文字,再根據(jù)自己的需要復(fù)制幾個選項(xiàng)按鈕,做成這種效果。
在其中一個控件上面點(diǎn)右鍵,設(shè)置控件格式,然后選擇表格空白區(qū)域的一個單元格。
完整的操作看動畫演示。
這一步的原理就是利用控件在單元格得到對應(yīng)選項(xiàng)的數(shù)字,為下一步的設(shè)置提供一個條件。
接下來設(shè)置條件格式,過程和第四部分的一樣,只是公式有所不同,因?yàn)檫@里有三個選項(xiàng),所以需要設(shè)置三次規(guī)則,公式分別為:
規(guī)則1:=AND($K$2=1,$D4TODAY())
規(guī)則2:=AND($K$2=2,$D4-30TODAY(),$D4>
規(guī)則3:=AND($K$2=3,$D4-60TODAY(),$D4-30>
如果還需要設(shè)置更多選項(xiàng),按照對應(yīng)的條件修改公式添加即可。
最終效果如圖所示。
小結(jié):任何看上去高大上的應(yīng)用都是從最基本的功能一點(diǎn)一點(diǎn)優(yōu)化出來的,而且需要各種功能相互配合才能實(shí)現(xiàn),今天的例子,從最開始很簡單的一個公式,到最后應(yīng)用了條件格式以及控件,就是最好的一個實(shí)證。
萬丈高樓平地起,跟著平臺的老師們好好學(xué)習(xí)基礎(chǔ)知識,總有一天你也可以利用Excel這個工具實(shí)現(xiàn)你的一切想法。
本文配套的練習(xí)課件請加入QQ群:1043683754下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
EDATE函數(shù)的應(yīng)用《到期日計算,EDATE甩DATE函數(shù)兩條街!》
用條件格式制圖《新同事用條件格式制作的圖表,竟然比我的還好看?》
關(guān)于時間日期的公式《20個可分別提取年月日時分秒數(shù)據(jù)等的excel公式》
TEXT函數(shù)的應(yīng)用《5分鐘,學(xué)會文本函數(shù)之王——TEXT的常用套路》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(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單元格中的算式,四種求和方法請收好!