根據(jù)考勤機數(shù)據(jù)統(tǒng)計考勤表是每個考勤人員的磨難之一。有的考勤機導(dǎo)出的數(shù)據(jù)打卡時間都在一個單元格里，有的考勤機導(dǎo)出的數(shù)據(jù)日期和打卡時間在一個單元格里。如何由這些數(shù)據(jù)快速生成考勤統(tǒng)計表呢？

考勤是每個公司主抓制度之一，每個公司都會安排1~2個人，甚至更多的人來負責(zé)考勤。

考勤也和每一個打工者息息相關(guān)，準確的說是和打工者的工資有著密切關(guān)系。早上總會看到為了不遲到而奔跑的人，晚上快下班的時候，大家做得最多的一件事情，就是看時間。

無論是遲到還是早退，其實它的衡量標準很簡單，就是“打卡時間”，如果打卡時間晚于上班時間就是“遲到”，早于下班時間就是“早退”。在EXCEL中，我們用函數(shù)或者VBA按這條標準進行判斷，可以智能地、快速地依據(jù)考勤機數(shù)據(jù)得到考勤統(tǒng)計表。

筆者E圖表述接觸過多種考勤機數(shù)據(jù)，發(fā)現(xiàn)考勤機導(dǎo)出的數(shù)據(jù)依據(jù)打卡時間的記錄方式主要分為三類：打卡時間獨立型、日期和打卡時間混合型、同一單元格多個打卡時間型。我們分別來說明一下。

說明：以下數(shù)據(jù)統(tǒng)一按照上班時間8:00，下班時間17:00來計算遲到、早退情況。

打卡時間獨立型

特點：

每一行就是一次打卡記錄，而且打卡日期和打卡時間是分開的兩列字段。

這種考勤應(yīng)該是最常見，也是處理最簡單的一類數(shù)據(jù)源。

筆者解法：

首先增加上午/下午輔助列，并用公式自動判定填寫上午或下午。

E2單元格輸入函數(shù)：=IF(D2<=VALUE("12:00:00"),"上午","下午")

函數(shù)解析：

VALUE("12:00:00")將12:00:00轉(zhuǎn)化成時間格式，然后與D列的時間進行對比，得到記錄條中打卡時間是“上午”還是“下午”，向下填充函數(shù)到E2:E12單元格區(qū)域。

然后增加遲到/早退輔助列，并用公式自動判定填寫。

F2單元格輸入函數(shù)：=IF(E2="上午",IF(D2>VALUE("8:00:00"),"遲到","正常"),IF(D2早退","正常"))

函數(shù)解析：

通過IF函數(shù)，判斷打卡是遲到、早退，還是正常。如果打卡記錄是“上午”，那么判斷是否大于8:00:00，是就是“遲到”，不是就是“正?！?；“下午”，則判斷是否小于17:00:00，是就是“早退”，不是就是“正?！?。

最后這樣處理源數(shù)據(jù)后，我們就可以使用“索引函數(shù)”將這些內(nèi)容匹配到《考勤統(tǒng)計表》中，如下所示：

在K2單元格輸入函數(shù)：=LOOKUP(1,0/(($B$2:$B$12=$I2)*($C$2:$C$12=K$1)*($E$2:$E$12=$J2)),($F$2:$F$12))

函數(shù)解析：

多條件索引的方式有很多、很多，筆者在這里采用的是LOOKUP。LOOKUP(1,0/…)是一個很常用的多條件索引方法，之前的教程中也有涉及，這里就不再贅述。給大家一個公式以后可以套用。

=LOOKUP（1，0/（（條件1）*（條件2）*（條件3）*…*（條件n）），（被查詢目標列））

通過上圖我們可以看到很多錯誤碼#N/A，那些就需要其他的判斷了。例如L2單元格應(yīng)該是“上午漏打卡”，O2和P2單元格則是“公休”。這不是本文的重點，也不算難點，無外乎用IF函數(shù)判斷條件再返回需要的說明即可，我們就不介紹了。

日期和打卡時間混合型

特點：

日期和時間在一個單元格中，導(dǎo)出的數(shù)據(jù)基本都是文本格式。

日期有兩種常見格式：一種帶分隔符，如2020/09/01；一種不帶分隔符，如20200901。

日期都是雙位數(shù)顯示，如1月——01，12月——12；1日——01，8日——08。

處理這樣的內(nèi)容，完全可以先將打卡記錄處理成常規(guī)的【獨立時間型】之后，然后再來處理考勤。以“打卡記錄2”為例，如下：

首先獲得日期列。

在E2單元格輸入函數(shù)：=--LEFT(D2,10)

函數(shù)解析：

用文本函數(shù)Left直接提取日期部分，再用兩個符號【--】轉(zhuǎn)換為數(shù)值，最后調(diào)整單元格格式為日期就可以了。

然后獲得時間列。

在F2單元格輸入函數(shù)：=--RIGHT(D2,LEN(D2)-11)

函數(shù)解析：

用文本函數(shù)Right提取文本右側(cè)的時間。LEN(D2)得到文本的總長度， 11是日期長度10加一個空格長度，差值就是時間部分的長度。

G列、H列的處理同上例，索引到《考勤統(tǒng)計表》中也和上例一樣，是不是融會貫通了？

同一單元格多個打卡時間型

特點：

同一人當(dāng)天的多次打卡時間都在一個單元格中。

對于考勤統(tǒng)計來說，這種數(shù)據(jù)是最難處理的。而且我們上圖是模擬數(shù)據(jù)，實際情況中，一個單元格中可能不止一兩條記錄。

處理這樣的數(shù)據(jù)，筆者的總體思路還是先將其轉(zhuǎn)化成第一種數(shù)據(jù)類型那樣的表，然后再來統(tǒng)計。

這里用函數(shù)處理，難度比較大，運行效率也比較低（數(shù)據(jù)量大了后），所以推薦給大家一個VBA的做法吧。（不要談VBA就色變，VBA很實用而且不難學(xué)，有興趣的同學(xué)可以通過我們的QQ群找到輔導(dǎo)老師。）

簡單的幾行代碼就把數(shù)據(jù)A2:D7處理到F2:I13單元格區(qū)域了，那么接下的操作就回到了我們第一例中了。

代碼如下：

說明：灰色部分是筆者對代碼的注解，方便大家了解代碼的含義。藍色的引號在代碼中表示注釋，其后內(nèi)容不參與運行。

Sub 數(shù)據(jù)清理()

  Dim arr, brr '''定義兩個變量，作為數(shù)組使用

  With Sheets("多個時間型") '''指定被操作的工作表《多個時間型》

    a = .[A1].End(4).Row '''找到數(shù)據(jù)末行行號，賦值給a

    arr = .Range("A2:D" & a) '''將數(shù)據(jù)區(qū)域賦值到數(shù)組arr中

    ReDim brr(1 To 500, 1 To 4) '''重新定義brr為二維數(shù)組，數(shù)組大小為500行，4列

    For i = 1 To UBound(arr) '''循環(huán)數(shù)組arr

      s = Split(arr(i, 4), Chr(10)) '''拆分arr數(shù)組的第4列，打卡時間列，并賦值給一維數(shù)組s

      For j = 0 To UBound(s) '''循環(huán)一維數(shù)組s

        k = k + 1 '''計數(shù)器效果，得到每次操作brr數(shù)組的行號

        brr(k, 1) = arr(i, 1) '''將數(shù)組arr的第1列的值，賦值給數(shù)組brr的第1列

        brr(k, 2) = arr(i, 2) '''將數(shù)組arr的第2列的值，賦值給數(shù)組brr的第2列

        brr(k, 3) = arr(i, 3) '''將數(shù)組arr的第3列的值，賦值給數(shù)組brr的第3列

        brr(k, 4) = s(j) '''將一維數(shù)組s的值逐個賦值到數(shù)組brr的第4列

      Next j '''結(jié)束循環(huán)變量j

    Next i '''結(jié)束循環(huán)變量i

    .[F2].Resize(UBound(brr), UBound(brr, 2)) = brr '''將數(shù)組brr的值賦值到單元格區(qū)域

  End With '''結(jié)束with語句

  Erase arr '''清空數(shù)組arr

  Erase brr '''清空數(shù)組brr

  Erase s '''清空數(shù)組s

End Sub

考勤表是日常EXCEL工作中比較典型的套表系統(tǒng)之一，對于這類數(shù)據(jù)的統(tǒng)計，我們可以講的還有很多。例如【每天四次打卡】的處理，例如【婚喪病事】請假的工資核算，例如【個稅抵扣】。如果這些都會了，同學(xué)們就可以給自己的公司制作一套DIY的考勤及工資核算模板，這樣既可以提高工作的效率，還可以在領(lǐng)導(dǎo)心中樹立一個好的形象。

如果你考勤統(tǒng)計的其他需要，請留言——不留言，筆者可猜不出你的需求。

最后，點分享按鈕鼓勵一下筆者吧，我將寫出更多的教程來。

本文配套的練習(xí)課件請加入QQ群：264539405下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

做一張智能的人工考勤表：智能考勤表，MM你再也不用加班了！

一天打四次卡的考勤機數(shù)據(jù)整理：學(xué)會這2個公式，整理考勤數(shù)據(jù)只要一分鐘

如何整理考勤機導(dǎo)出的數(shù)據(jù)：別怕：只要會簡化拆分，幾分鐘搞定考勤統(tǒng)計分析表！

看懂VBA的循環(huán)語句：VBA實戰(zhàn)入門教程（三）：循環(huán)語句