论文部分内容阅读
用活条件格式 过期报价自动“消失”
条件格式可以设置指定单元格字体的颜色。因此,要让过时的数据自动隐藏,我们需要设置一个当前时间和指定时间进行比较的条件,符合设置条件时指定数据的字体变为白色(与背景色一致),这样便可以实现数据自动“消失”的效果。比如下面的例子,这是某公司产品的报价文档,报价有效期截至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)。对于公司负责发送销售价格的统计员来说,可以在所有产品的报价文件中使用上述的代码,这样便可以保证发送给业务员的都是有效的报价。