办公小技巧:用好Excel 特殊日期提醒更智能

CFan 电脑爱好者 2021-06-24 10:01应用 标签:日期 技巧 智能 办公

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

201924zntx1

图1 提醒效果示例

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

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

201924zntx2

图2 新建表

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

201924zntx3

图3 调用数据

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

201924zntx4

图4 计算入职工龄

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

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

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

201924zntx5

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

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

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

201924zntx6

图6 添加提醒语句

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

201924zntx7

图7 条件格式设置

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

201924zntx8

图8 最终效果

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

QQ图片20210420165237