俞木發(fā)

用活條件格式 過期報價自動“消失”

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

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

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

如此一來，如果這個報價工作表打開的時間超過1月31日（比如2月1日打開），那么報價區(qū)域的數(shù)據(jù)就會“消失”，同時由于設置了工作表的保護，用戶也無法對其格式進行修改了（圖4）。

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

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

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

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

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

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

隱藏更高效 使用VBA批量隱藏數(shù)據(jù)

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

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

代碼解釋：

這里的代碼使用了Workbook_open（）事件，這樣每次在打開工作簿時都會運行VBA代碼。

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

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