利用Excel 特殊日期提示-英雄云拓展知识分享

在公司文化建设中，为了提升员工凝聚力，很多公司都会为员工提供诸如生日福利、入职周年记念日等活动。不过这些活动需要在指定日子对特定的员工实行，现在我们可以借助Excel快捷添加这些特殊日期的提示。下面以Excel 2016为例，提示的时间是在某某入职周年日前两天，在提示单元格中自动添加提示文字，并且单元格填充为黄色底色。

图1 提示效果示例

可以看到示例的数据非常多，而实际提示只要部份的数据便可，因此我们可以先为智能提示制作一个专门工作表，新表中的数据借助VLOOKUP函数从原始表中ABCD列的数据提取。按提示新建一个“提示表”，复制A1～D1表头数据，接着定位到B2，输入公式“=IF(LEN($A2)=0,"",VLOOKUP($A2,Sheet1!$A$1:$D$15,COLUMN(B2),0))”，然后将其向右填充到D列，向下填充到15行。

小提示：这里使用COLUMN函数返回值所在的列，经过VLOOKUP函数以A2单元格的值在原始工作表A1:D15区域中查找，最后经过IF函数的嵌套（避免出现N/A提示）来进行判断。

图2 新建表

这样我们只要在A2单元格中开始填充员工的工号数据，上述函数就会自动援用原来表格数据。以后在原始数据表中添加其他员工信息后，我们只要在这里继续填充相应员工的工号便可自动调用数据。固然这还是个查找器，如果员工很多，只要在A列输入工号便可快捷找到该员工的信息。

图3 调用数据

由于是员工入职周年提示，我们需要先计算员工的工龄，工龄借助INT函数来计算，定位到E2单元格并输入公式“=INT((TODAY()-D2)/365)”，向下填充后就能够自动显示员工的工龄了。

图4 计算入职工龄

小提示：这里先使用TODAY函数和员工入职日期相减得到实际入职天数，然后除以365天得到实际入职工龄。最后使用INT函数取整（它是舍尾法直接去除小数，比如9.1→9.9年工龄都取9），这样就能够取得员工从入职到今天的整数工作年限了。不过需要注意的是，由于有闰年（366天）的存在，如果要精确计算员工的年限就需要斟酌这一因素，本文只是为了计算方便，一致以365天为基准。

示例要求是需要在员工入职周年日前两天进行提示，周年是依照员工入职月份为准，因此还要计算截至今天为止，员工入职月份和今天日期的时间差。这个时间差经过DAYS函数计算（入职月日-本日的月日），定位到F2单元格并输入公式“=DAYS(TEXT(D2,"mm-dd"),TODAY())”，下拉便可。

小提示：公式先使用TEXT函数提取D2单元格日期的月和日格式，并将其作为DAYS函数的结束日期。使用TODAY()（表示本日的月日）作为DAYS函数的开始日期，然后相减得到两个日期相差天数。由于在示例中需要提早2天通知，所以相减天数为2则需要进行通知，假定今天是2019/11/14，那末2016/11/16入职的张梅就应当得到提示（11/16⑴1/14=2），负数表示比当前月份小，已过了周年日的天数。

图5 计算本日距入职周年日天数

最后是提示语句的设置，语句可以借助IF函数设置，定位到G2单元格并输入公式“=IF(F2=2,C2&"的"&B2&"后天入职"&E2&"周年","")”然后下拉，这样符合条件的员工在G列就会出现提示语句。

小提示：IF函数第一个条件判断时间差是不是距离入职时间2天，如果符合那末就针对示例输出提示语句，语句的组合使用“&”字符将指定的字符显示在G2单元格，否则显示为空。

图6 添加提示语句

在示例中仍需要为提示语句填充底色，这里借助条件格式完成，选中G列数据，点击“开始→条件格式→突出显示单元格规则→为包括以下文本的单元格设置格式”，在打开的窗口中包括文本输入“周年”，点击自定义内涵格式，选择填充黄色底色的设置。

图7 条件格式设置

完成上述设置后，以后我们只要每天打开这个文档，如果有符合条件的员工，那末在G列就会自动出现填充黄色底色的提示语句，针对这个提示安排相应的活动便可。为了方便书写，这里还可以只保存G列提示并在G2单元格直接使用IF嵌套E、F函数，只要将函数更改成“=IF(DAYS(TEXT(D15,"mm-dd"),TODAY())=2,C15&"的"&B15&"后天入职"&INT((TODAY()-D15)/365)&"周年","") ”便可。

图8 终究效果

由于TODAY日期是动态变化的，上述提示借助函数和条件格式可以实现动态计算日期。大家可以举一反3，制作类似员工生日、公司周年庆、高考倒计时等智能提示。