基于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