基于EXCEL的指纹考勤数据处理方法研究

霍颖瑜

摘 要：指纹考勤数据往往由于企业制定的打卡规则不同而难以统计。鉴于EXCEL 的通用性和强大的数据处理能力，提出一种基于EXCEL 的考勤数据处理方法。相较于人工计算方式而言，该方法具有普适性、易操作的特点，能够提高工作效率，减少计算错误。

关键词：指纹考勤；EXCEL；数据收集；数据处理

DOI：10.11907/rjdk.171226

中图分类号：TP3-0 文献标识码：A 文章编号：1672-7800（2017）009-0032-02

Abstract：Fingerprint attendance data are often due to the different enterprises to formulate rules of punch to statistics， in view of the versatility and strong data processing ability of EXCEL， proposes an attendance data processing method based on EXCEL. Compared with the manual calculation method， this method has the characteristics of universality and easy operation， which can improve the work efficiency and reduce the calculation error.

Key Words：fingerprint attendance； EXCEL； data collection； data processing

0 引言

目前很多企业都在大力推进信息化管理进程，考勤管理作为企业信息化管理的一个重要环节，为企业员工考核提供了重要的基础数据来源。指纹打卡系统是企业为规范考勤管理而通过指纹录入记录考勤时间的管理系统。由于各企业制定的考勤规则不同，指纹打卡系统提供的考勤数据不一定能满足该具体规则，且随着员工数量的增多以及用户行为的增加，数据往往呈现“爆炸式”增长，有效数据与无效数据混杂。传统的人工统计方式会带来以下问题：①耗费大量人力、物力、财力，工作效率极低；②庞杂的数据令人眼花缭乱，容易出现差错。如何快速地对考勤数据进行自动化处理，已成为一项亟待解决的难题[1]。

尽管有很多专业的统计软件可进行考勤处理，但是这些软件购买价格较高、操作过程复杂，很难进行广泛推广。EXCEL是微软公司的办公组件之一，主要用于各种数据处理、统计分析和辅助决策，功能十分强大，且办公人员易于掌握。因此，本文介绍一种基于EXCEL的指纹考勤数据处理方法，办公人员可快速学习掌握该方法，对于实际数据统计类问题也能起到借鉴作用，从而节约时间、提高工作效率[2]。

1 考勤规则分析

某企业分为一部和二部两个办公地点，分别设置了考勤打卡机，并于每月集中进行一次数据采集，以统计和备份员工当月有效的考勤数据，用于考评。为方便员工工作，允许员工在当前办公地点打卡，如一部员工因为企业事务需到二部，则可以在二部直接打卡。企业规定上午上班时间为8：30～12：00，下午上班时间为14：00～17：30，两个工作时间段必须分别签到、签离，即一天内会有4条打卡记录，且13：00前必須对上午工作进行签离。

数据采集后的考勤表实际上是一个txt记事本文件，如图1所示。该文件内容可直接复制粘贴到EXCEL文件中，每行/列数据分别对应一行/列，如图2所示。

分析上述考勤清单可发现如下问题：①标题栏的“Name”、“DateTime”分别对应“姓名”和“刷卡时间”，且均为文本格式。为便于企业统计迟到早退现象，需将刷卡时间转换为日期时间型；②“姓名”中的数据含有空格，会对后续统计匹配的准确性造成障碍；③每人每次打卡数据均对应一行记录，未建立对应关系；④对两部卡机的数据清单合并（若字段、记录格式相同，只需简单的全选、复制粘贴即可合并）后进行数据分析，发现个人用户存在一天内分别在不同卡机上打卡，且由于卡机原因，有时会出现重复记录的情况。

2 考勤数据收集

2.1 数据清洗

数据清洗是在数据库中去除冗余、清除噪音、消除错误和不一致数据的过程，包括对数据的删除、添加、分解、重组等。其将多余重复的数据筛选清除，将缺失的数据补充完整，并纠正或删除错误数据[3]。具体操作步骤如下：

（1）对记录按姓名（D列）和刷卡时间（G列）进行排序。通过排序，为后续数据匹配作准备。

（2）添加H列，设置字段名为“筛重”，用于判断筛选出重复记录。对于考勤记录表中的重复记录去重可以利用内置的COUNTIFS函数进行判断，判断条件为姓名和打卡时间。在H2单元格中添加公式=COUNTIFS（DMYM1：D2，D2，GMYM1：G2，G2），向下拖动填充柄实现公式的复制，此时每条记录将显示该记录重复出现的顺序号。

（3）利用数据筛选功能，对记录进行“无重复”数据筛选。筛选出筛重值大于1的记录，如图3所示。对重复记录进行删除，得到有效记录表。

2.2 数据变换

数据转换是将数据从一种表示形式转变为另一种形式的过程，以便于后续的数据处理和数据统计[4]。具体操作如下：①将G列的打卡时间转换为时间格式；②利用TEXT函数提取G列时间打卡日期和时间，分别填入I列和J列。提取日期的函数写法为：=--TEXT（G2，"yyyy/mm/dd"）；提取时间的函数写法为：=TEXT（G2，"h：mm"）；③利用TRIM函数将姓名列中的多余空格清除，并填入K列，函数具体写法为：=TRIM（D2）。endprint

3 考勤数据处理

3.1 数据匹配

将清洗转换后的有用数据，包括姓名、日期和时间，复制粘贴到新表，如图4所示。

对考勤记录进行签到签离时间匹配，具体操作如下：

（1）利用数组公式查找当天最近的后续记录时间。在E2单元格中填入=INDEX（C：C，MATCH（1，（A：A=A2）*（B：B=B2）*（C：C>C2），）），并按Ctrl+Shift+Enter进行确认，使其成为数组公式，如图5所示。

（2）向下拖动填充柄实现公式的复制，此时发现当天最后一条记录因不存在后续时间记录，导致公式出错。

（3）利用IFERROR函数对公式出错的记录和不符合考勤规则的记录进行处理。在D2单元格中填入=IFERROR（IF（AND（C2<=TIME（9，30，），E2TIME（9，30，），E2，0）），0），并向下拖动填充柄实现公式的复制。

3.2 数据抽取

最终的数据统计是基于有效数据，通过上述处理后的有效记录并不是真正的“数据”，有价值的指纹数据是个人每天每次符合规则的数据，即上午上班与下午上班的各一次有效数据，因此需要把有价值的数据抽取出来，回收到一张新的数据表中。根据企业考勤规则，具体操作步骤如下：①筛选到达时间为9：30之前、离开时间为0或者9：30之后的记录，进行有效数据的复制粘贴；②筛选到达时间为13：00～15：00的记录，进行有效数据的复制粘贴。

所有有效数据复制粘贴至新表后，此时新表中则是真正需要处理的有效记录，如图6所示。

4 结语

本文仅利用EXCEL的基础功能，介绍了一种基于EXCEL的指纹考勤数据处理方法。实践证明，EXCEL办公软件的功能非常强大，可以快速、准确地对相关工作数据进行统计与分析，减少因手工操作等造成的人为误差，从而使企业管理人员从繁琐枯燥的数据统计工作中解脱出来，提高了工作效率。

参考文献：

[1] 张丽英，易燕. Excel在人事管理中的应用[J].电脑知识与技术：学术交流，2009，5（21）：5844-5844.

[2] 朱扬清，林秋明. Excel在商务中的应用[M].北京：中国铁道出版社，2016.

[3] 叶振西.浅谈利用EXCEL制作工资表的方法与技巧[J].福建电脑，2012，28（3）：153-154.

[4] 陈大银.使用Office制作成績表、单之技巧[J].软件导刊，2012，11（8）：17-19.

（责任编辑：黄 健）endprint