多管齊下　在Excel中定時“銷毀”數據

俞木發

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

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

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

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

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

用好函數 過期數據自動隱藏和提醒

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

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

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

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

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

隱藏更高效 使用VBA批量隱藏數據

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

首先到“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函數對E2單元格（需要在這里輸入截止時間）和當前時間進行計算，如果大于0（表示當前時間超過截止時間），那么就將保存報價數據的B列隱藏。同時下行代碼表示符合超時條件時在E3單元格中輸入“注意：報價已失效”作為提醒語句。

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