Excel工资考勤自动刷新：员工离职入职花名册的自动同步

编按：在员工变动频繁的公司，我们需要HR能及时在考勤表/工资表上删除离职员工。为了避免频繁地手工操作，我们也可以实现考勤表、工资表同花名册的自动同步，那样是不是就轻松多了呢？一起来看看这些操作是如何实现的吧！

哈喽，大家好。

现在公司员工变动都很频繁，今天小明离职，明天小美入职。

员工一旦发生变动，HR们就得手动修改考勤汇总表名单、工资表名单等。譬如小明2023年2月21日离职，小美是2月22日入职，那么HR需要做的操作包括：

※修改花名册

※依据花名册在2月的考勤汇总表、工资表中增加小美姓名

※依据花名册在3月的考勤汇总表、工资表中删除小明姓名

如果考勤汇总表、工资表名单可以与花名册自动同步，HR们是不是就轻松多了呢？！

今天的文章就教大家如何实现人员离入职后考勤汇总表、工资表名单与花名册自动同步。

创建3张工作表

创建3张工作表，分别是花名册、考勤汇总表、工资表。

Step 01

新建一个“花名册”工作表，按提示输入员工的基本信息。“状态”包括两种，在职和离职，需要根据员工离职、入职情况及时更新。（注意：此处表中只列出了与姓名自动同步相关的员工信息，其他项目按照你所在公司表格绘制。）

Step 02

定位到F2单元格输入公式“=IFERROR(IF(D2="离职","输入离职日期",""),"")”，下拉填充，然后为离职员工输入具体的离职日期。

Step 03

新建一个“考勤汇总表”工作表。定位A1输入考勤表月份第一天日期如2023/3/1，然后将其单元格的数字格式设置为“2012年3月”，再在B1单元格输入标题文本。最后添加工号、姓名等列。考勤汇总表的其他项目按照你所在公司表格绘制。

Step 04

新建“工资表”工作表，同样在A1单元格输入工资表月份第一天日期，B1输入标题内容，然后添加工号、姓名列。其他项目按照你所在公司表格绘制。

二、在考勤表中动态引用花名册数据

Step 05

切换到“花名册”工作表，定位到G2输入公式“=IFERROR(IF(AND(C2="在职",(D2-DAY(D2))<=(工资表!$A$1-DAY(工资表!$A$1))),ROW(),IF(AND(C2="离职",(E2-DAY(E2))=(工资表!$A$1-DAY(工资表!$A$1))),ROW(),"")),"")”，下拉填充。

注：因为考勤汇总表和工资表两者的A1值完全相同，所以使用工资表的A1进行判断并无不妥。

公式解析：

利用两个IF函数分别判断在职人员和离职人员是否要引用到考勤表中。

如果是在职人员，则只有入职日期的年月值小于等于考勤表年月值才被引用，用ROW()标记序号；如果是离职人员，则只有离职日期的年月值等于考勤表的年月值才被引用，同样用ROW()标记序号；其他的则不引用，标记为空。

譬如，表中“贾诩”是2月份离职，离职的年月值小于考勤的年月值，所以标记为空；同理，4月份入职的“刘备”，其入职年月值大于考勤的年月值，所以标记为空。

(D2-DAY(D2))<=(工资表!$A$1-DAY(工资表!$A$1)) 用于判断在职日期年月值是否小于等于考勤年月值；(E2-DAY(E2))=(工资表!$A$1-DAY(工资表!$A$1))用于判断离职日期的年月值与考勤年月值是否相等。

最外层嵌套IFERROR函数，如果出现错误值则显示为空。

Step 06

切换到“考勤汇总表”工作表，定位到A5单元格输入公式“=IFERROR(INDEX(花名册!A:A,SMALL(花名册!$F:$F,ROW(A1))),"")”，然后向右填充到B5单元格，选中A5:B5再向下填充，完成花名册数据的引用。

三、在工资表中动态引用花名册数据

Step 07

切换到“工资表”工作表，在A4输入公式“=IFERROR(INDEX(花名册!A:A,SMALL(花名册!$F:$F,ROW(A1))),"")”，向右填充到B4,列，选中A4:B4再向下填充，完成花名册数据的引用。

OK，完成上述操作后，以后HR只要在“花名册”表中及时更新员工的状态数据,就会自动更新其在考勤表和工资表中的姓名。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

学习交流请加微信hclhclsc进群领取资料。

工资表转工资条，VLOOKUP有绝招！

WPS表格教程：财务必学技能！手把手教你制作最实用的工资条！

Excel教程：如何制作带有层次和透视感的图表？

八大查找函数公式，轻松搞定数据中的多条件查找

版权申明：

本文作者ITFANS；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。