多管齊下　在Excel中定時(shí)“銷毀”數(shù)據(jù)

俞木發(fā)

用活條件格式 過(guò)期報(bào)價(jià)自動(dòng)“消失”

條件格式可以設(shè)置指定單元格字體的顏色。因此，要讓過(guò)時(shí)的數(shù)據(jù)自動(dòng)隱藏，我們需要設(shè)置一個(gè)當(dāng)前時(shí)間和指定時(shí)間進(jìn)行比較的條件，符合設(shè)置條件時(shí)指定數(shù)據(jù)的字體變?yōu)榘咨ㄅc背景色一致），這樣便可以實(shí)現(xiàn)數(shù)據(jù)自動(dòng)“消失”的效果。比如下面的例子，這是某公司產(chǎn)品的報(bào)價(jià)文檔，報(bào)價(jià)有效期截至2021年1月31日，現(xiàn)在需要實(shí)現(xiàn)當(dāng)日期超過(guò)1月31日時(shí)，B列的報(bào)價(jià)數(shù)據(jù)就不再顯示（圖1）。

選中B2：B19數(shù)據(jù)區(qū)域，然后依次點(diǎn)擊“開(kāi)始→條件格式→新建條件規(guī)則→使用公式確定要設(shè)置單元格的格式”，在公式欄中輸入“=$D$2

同時(shí)，為了限制員工對(duì)報(bào)價(jià)區(qū)域進(jìn)行修改，選中B2：B19數(shù)據(jù)區(qū)域并右擊，然后依次點(diǎn)擊“設(shè)置單元格格式→保護(hù)”，勾選“隱藏”和“鎖定”。返回工作表后依次點(diǎn)擊“審閱→保護(hù)工作表”并勾選“選定鎖定單元格”、“選定解除鎖定的單元格”和“設(shè)置單元格格式”，然后設(shè)置保護(hù)密碼（圖3）。

如此一來(lái)，如果這個(gè)報(bào)價(jià)工作表打開(kāi)的時(shí)間超過(guò)1月31日（比如2月1日打開(kāi)），那么報(bào)價(jià)區(qū)域的數(shù)據(jù)就會(huì)“消失”，同時(shí)由于設(shè)置了工作表的保護(hù)，用戶也無(wú)法對(duì)其格式進(jìn)行修改了（圖4）。

用好函數(shù) 過(guò)期數(shù)據(jù)自動(dòng)隱藏和提醒

上述的方法是通過(guò)設(shè)置字體的顏色來(lái)實(shí)現(xiàn)過(guò)期數(shù)據(jù)的隱藏的。如果要實(shí)現(xiàn)更醒目的提醒，我們可以結(jié)合lF函數(shù)對(duì)其進(jìn)行判斷，當(dāng)時(shí)間過(guò)期后直接添加醒目的文字提示，更能方便員工的查看。

同上，在工作表中的B列前插入一個(gè)新列，接著在B2單元格中輸入公式“=IF（$E$2>NOW（），C2，"報(bào)價(jià)已過(guò)期"）”。表示使用IF函數(shù)對(duì)E2單元格所代表的日期同當(dāng)前時(shí)間進(jìn)行對(duì)比，如果E2大于當(dāng)前時(shí)間（即時(shí)間未到期），那么就顯示C2的報(bào)價(jià)數(shù)據(jù)，否則顯示“報(bào)價(jià)已過(guò)期”的提示（圖5）。

選中B2：B19數(shù)據(jù)區(qū)域，然后依次點(diǎn)擊“開(kāi)始→條件格式→當(dāng)文本包含”，設(shè)置一個(gè)當(dāng)文本包含“文件已過(guò)期”時(shí)使用淺紅色進(jìn)行填充，同上再進(jìn)行工作表的保護(hù)操作，并將C列的數(shù)據(jù)進(jìn)行隱藏（圖6）。

這樣，只要當(dāng)前日期小于E2的值，B列仍然顯示正常的報(bào)價(jià)。如果當(dāng)前日期大于E2的值，那么在B列就會(huì)顯示以紅色填充的“報(bào)價(jià)已過(guò)期”的文字提示。同時(shí)由于設(shè)置了工作表的保護(hù)，用戶也看不到原始報(bào)價(jià)數(shù)據(jù)和公式，當(dāng)然也無(wú)法對(duì)C列的原始報(bào)價(jià)進(jìn)行取消隱藏的操作（圖7）。

此外，大家還可以使用IF函數(shù)實(shí)現(xiàn)更多的判斷。比如可以在F2單元格中輸入公式“=IF（$E$2-TODAY（）<0，”報(bào)價(jià)已無(wú)效”，“報(bào)價(jià)還有"&$E$2-TODAY（）&"天過(guò)期"）”。這樣如果時(shí)間未到，F(xiàn)2單元格中會(huì)提示價(jià)格有效期還有幾天，過(guò)期后則直接提示“報(bào)價(jià)已無(wú)效”。

隱藏更高效 使用VBA批量隱藏?cái)?shù)據(jù)

如果需要設(shè)置的工作簿有很多工作表，比如有多個(gè)產(chǎn)品的報(bào)價(jià)，每個(gè)產(chǎn)品一個(gè)工作表，上述的方法需在每個(gè)工作表里都進(jìn)行設(shè)置，使用VBA腳本則可以實(shí)現(xiàn)自動(dòng)隱藏所有工作表的報(bào)價(jià)數(shù)據(jù)列。

首先到“https：∥share.weiyun.com/nqlcgakx”下載所需的腳本，然后按下“Alt+F11”組合鍵，在打開(kāi)的代碼編輯窗口中點(diǎn)擊“插入→模塊”，接著將下載到的代碼粘貼到代碼框中（圖8）。

代碼解釋：

這里的代碼使用了Workbook_open（）事件，這樣每次在打開(kāi)工作簿時(shí)都會(huì)運(yùn)行VBA代碼。

If Date Diff（"d"，Range（"e2"），NOW）>0 Then Columns（"b：b"）.Hidden=True：表示使用DateDiff函數(shù)對(duì)E2單元格（需要在這里輸入截止時(shí)間）和當(dāng)前時(shí)間進(jìn)行計(jì)算，如果大于0（表示當(dāng)前時(shí)間超過(guò)截止時(shí)間），那么就將保存報(bào)價(jià)數(shù)據(jù)的B列隱藏。同時(shí)下行代碼表示符合超時(shí)條件時(shí)在E3單元格中輸入“注意：報(bào)價(jià)已失效”作為提醒語(yǔ)句。

接著將文檔保存為啟用宏的工作簿文件。這樣，當(dāng)用戶每次打開(kāi)上述的工作簿后，后臺(tái)的VBA腳本會(huì)自動(dòng)將當(dāng)前時(shí)間和指定的截止時(shí)間進(jìn)行比較，如果超期則會(huì)將每個(gè)工作表的B列自動(dòng)隱藏（沒(méi)有超時(shí)則保持默認(rèn)顯示），同時(shí)在E3單元格中顯示報(bào)價(jià)失效的提示（圖9）。對(duì)于公司負(fù)責(zé)發(fā)送銷售價(jià)格的統(tǒng)計(jì)員來(lái)說(shuō)，可以在所有產(chǎn)品的報(bào)價(jià)文件中使用上述的代碼，這樣便可以保證發(fā)送給業(yè)務(wù)員的都是有效的報(bào)價(jià)。