Excel函数在考勤表制作中的应用

2022-06-16匡煜

现代计算机 2022年7期

关键词：记录表表达式考勤

匡煜

（雅安职业技术学院，四川 625000）

0 引言

考勤记录是登记职工出勤情况的原始记录，甚至直接关系到职工的工资结算问题，然而考勤机导出的打卡数据相对比较冗杂，如何将考勤机导出的打卡数据制作成考勤表却成了很多考勤记录员的一大难题。

1 数据表准备

1.1 打卡记录表

本文数据来自某公司11 月份考勤打卡机导出的打卡数据，命名为“打卡记录表”，其Excel 数据表结构如表1 所示，不同的打卡机导出的打卡记录的数据格式可能会略有差异，稍做处理即可。

表1 打卡记录表

1.2 考勤记录表

本文需要制作的考勤表结构如表2所示，在同一工作簿中新建工作表并命名为“考勤记录表”，并按表2所示的结构制作备用。

表2 考勤记录表

2 相关函数介绍

2.1 IF函数

IF 函数执行过程是根据指定条件的不同计算结果来执行返回不同的值，其语法和应用说明如下。

语法格式：IF（logical_test，value_if_true，value_if_false）。

使用说明：根据参数logical_test的计算结果来决定IF 函数的执行结果，如果logical_test 计算的结果为真（true），IF 函数则执行表达式value_if_true 的结果，如果logical_test 计算的结果为假（false），IF 函数则执行表达式value_if_false 的结果。值得注意的是，IF 函数中的三个参数都可以是表达式，因此可以将其他函数和IF 函数进行嵌套使用，以便完成更多、更复杂的数据操作。

例如：在函数表达式IF（A2>=60，“及格”，“不及格”）中，如果A2 单元格中的值大于等于60，则显示“及格”字样，否则显示“不及格”字样。

2.2 DAY函数

DAY 函数返回指定日期中对应“年月日”中“日”的天数日期，其语法和应用说明如下。

语法格式：DAY（serial_number）

使用说明：参数serial_number 必须是一个日期值，其中包含要查找的天数日期。

例如：函数表达式DAY（“2021-11-1”）的结果为1。

2.3 COUNTIF函数

COUNTIF 函数是用来统计指定区域中满足指定条件的单元格的个数，其语法和应用说明如下。

语法格式：COUNTIF（range，criteria）

使用说明：参数range 表示需要统计的单元区域，参数criteria表示统计的条件。

例如：函数表达式COUNTIF（B2:B5，“>55”）返回的结果表示在单元格区域B2～B5 中值大于55的单元格的个数。

2.4 COUNTIFS函数

COUNTIFS 函数是用来统计指定区域中满足多个指定条件的单元格的数量，其语法和应用说明如下。

语法格式：COUNTIFS（criteria_range1，criteria1，criteria_range2，criteria2，…）。

使用说明：参数criteria_range1 表示第一个条件关联的第一个区域，参数criteria1则表示统计的第一个条件，参数criteria_range2 表示第二个条件关联的第二个区域，参数criteria2则表示统计的第二个条件，以此类推，需要注意的是每一个附加的区域必须与参数criteria_range1 具有相同的行数和列数。

例如：函数表达式COUNTIFS（A2:A7，“>80”，B2:B7，“<100”）返回的结果表示在单元格区域A2～A7 中包含大于80 且同时满足在单元格区域B2～B7中包含小于100的数的行数。

3 制作过程

3.1 新建辅助列

辅助列用于获取每位职工的具体出勤日期。考勤记录表中需要体现每个职工的具体出勤情况，因此需要在打卡记录的工作表中添加一列辅助列来标记职工打卡的日期，用于与考勤记录表中的具体日期相对应，也便于后期用COUNTIFS 函数来统计，本文命名该辅助列为“打卡日期”，则表1变为表3。

表3 打卡记录表（含辅助列）

3.2 新建辅助表

辅助表用于获取所有打卡且非重复职工姓名。在考勤记录表的准备过程中，需要将导出的打卡记录表中的姓名填写到考勤记录表中的姓名处，但打卡记录表中的数据会有很多重复记录数据，因此可以新建一个工作表来作为辅助，将打卡记录表中的姓名列和证件编号列复制到该辅助表中并以证件编号为基准删除重复项，之后将姓名列按升序排序，排序后将姓名列复制到考勤记录表的姓名处并对序号列重新编号。此处排序的目的是为了方便职工在签字时可以更快地找到对应的考勤信息，完成考勤记录表中的姓名编辑后可以删除该辅助表。

3.3 函数实现

第一步：计算打卡记录中职工的打卡日期。在打卡记录的辅助列“打卡日期”列对应的单元格（如G2）中应用函数表达式IF（E2>0，DAY（E2），DAY（F2）），并向下填充至最后一条打卡记录，得到所有职工打卡的具体日期，与“考勤记录表”中具体出勤情况中的具体日期相对应，结果如图1所示。

图1 考勤记录

第二步：计算考勤记录表中职工的考勤日期。在考勤记录表中第一位职工对应1号日期所在的单元格（如C4）中应用函数表达式COUNTIFS（打卡记录!$B:$B,考勤记录!$B4,打卡记录!$G:$G,考勤记录!C$3）并向右填充至31号日期，得到该职工在对应日期的打卡记录数。这里的结果我们只做两种情况讨论，一种是值为0的情况，表示该职工在对应这一天没有打卡，视为缺勤，另一种是值大于0的情况，表示该职工当天完成打卡，视为出勤。再以第一位职工的具体出勤情况为基准，向下填充至最后一位职工，得到所有职工的具体出勤情况，结果如图2所示。

图2 出勤情况

第三步：计算考勤记录表中每位职工的合计出勤天数。在考勤记录表中第一位职工对应合计出勤天数所在的单元格（如AH4）中应用函数表达式COUNTIF（C3:AG4,">0"）并向下填充至最后一位职工，得到所有职工的合计出勤天数，结果如图3所示。

图3 合计出勤天数

第四步：设置出勤的显示方式，即用符号“√”来表示出勤，缺勤不显示任何符号。自定义设置具体出勤情况栏目下对应的单元格区域（如C4:AG16）的单元格格式，将其单元格格式设置为自定义，并在自定义“类型”设置为“√;;”（格式为：“正数；负数；零”，此处简单说明一下这样设置的作用，即表示在该单元格区域正数用“√”表示，负数和零则忽略，不用任何符号表示），设置完毕后结果如图4所示。