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

考勤是每個(gè)公司主抓制度之一，每個(gè)公司都會(huì)安排1~2個(gè)人，甚至更多的人來(lái)負(fù)責(zé)考勤。學(xué)習(xí)更多技巧，請(qǐng)收藏關(guān)注部落窩教育excel圖文教程。

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

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

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

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

一、打卡時(shí)間獨(dú)立型

特點(diǎn)：每一行就是一次打卡記錄，而且打卡日期和打卡時(shí)間是分開(kāi)的兩列字段。

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

筆者解法：

首先增加上午/下午輔助列，并用公式自動(dòng)判定填寫(xiě)上午或下午。

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

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

然后增加遲到/早退輔助列，并用公式自動(dòng)判定填寫(xiě)。

F2單元格輸入函數(shù)：=IF(E2="上午",IF(D2>VALUE("8:00:00"),"遲到","正常"),IF(D2<value("17:00:00"),"< span="" style="margin-top: 0px; margin-bottom: 0px; padding: 0px;">早退","正常"))

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

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

在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/…)是一個(gè)很常用的多條件索引方法，之前的教程中也有涉及，這里就不再贅述。給大家一個(gè)公式以后可以套用。

=LOOKUP（1，0/（（條件1）*（條件2）*（條件3）*…*（條件n）），（被查詢(xún)目標(biāo)列））

通過(guò)上圖我們可以看到很多錯(cuò)誤碼#N/A，那些就需要其他的判斷了。例如L2單元格應(yīng)該是“上午漏打卡”，O2和P2單元格則是“公休”。這不是本文的重點(diǎn)，也不算難點(diǎn)，無(wú)外乎用IF函數(shù)判斷條件再返回需要的說(shuō)明即可，我們就不介紹了。學(xué)習(xí)更多技巧，請(qǐng)收藏關(guān)注部落窩教育excel圖文教程。

二、日期和打卡時(shí)間混合型

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

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

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

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

首先獲得日期列。

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

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

然后獲得時(shí)間列。

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

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

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

三、同一單元格多個(gè)打卡時(shí)間型

特點(diǎn)：同一人當(dāng)天的多次打卡時(shí)間都在一個(gè)單元格中。

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

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

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

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

說(shuō)明：每行代碼藍(lán)色引號(hào)'''后的灰色部分是筆者對(duì)代碼的注解，方便大家了解代碼的含義。藍(lán)色引號(hào)在代碼中表示注釋?zhuān)浜髢?nèi)容不參與運(yùn)行。

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

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

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

??? a = .[A1].End(4).Row?'''找到數(shù)據(jù)末行行號(hào)，賦值給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í)間列，并賦值給一維數(shù)組s

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

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

??????? 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的值逐個(gè)賦值到數(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語(yǔ)句

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

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

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

End Sub

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

如果你考勤統(tǒng)計(jì)的其他需要，請(qǐng)留言——不留言，筆者可猜不出你的需求。學(xué)習(xí)更多技巧，請(qǐng)收藏關(guān)注部落窩教育excel圖文教程。

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

＊＊＊＊部落窩教育-excel不同時(shí)間格式的考勤統(tǒng)計(jì)＊＊＊＊

原創(chuàng)：E圖表述/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）

更多教程：部落窩教育

微信公眾號(hào)：exceljiaocheng，+v：blwjymx2

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

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

相關(guān)推薦：

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

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

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

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