打卡時間都在同一個單元格內(nèi)的考勤統(tǒng)計方法
?
作者:E圖表述來源:部落窩教育發(fā)布時間:2020-10-23 16:59:52點(diǎn)擊:17907
根據(jù)考勤機(jī)數(shù)據(jù)統(tǒng)計考勤表是每個考勤人員的磨難之一。有的考勤機(jī)導(dǎo)出的數(shù)據(jù)打卡時間都在一個單元格里,有的考勤機(jī)導(dǎo)出的數(shù)據(jù)日期和打卡時間在一個單元格里。如何由這些數(shù)據(jù)快速生成考勤統(tǒng)計表呢?
考勤是每個公司主抓制度之一,每個公司都會安排1~2個人,甚至更多的人來負(fù)責(zé)考勤。
考勤也和每一個打工者息息相關(guān),準(zhǔn)確的說是和打工者的工資有著密切關(guān)系。早上總會看到為了不遲到而奔跑的人,晚上快下班的時候,大家做得最多的一件事情,就是看時間。
無論是遲到還是早退,其實它的衡量標(biāo)準(zhǔn)很簡單,就是“打卡時間”,如果打卡時間晚于上班時間就是“遲到”,早于下班時間就是“早退”。在EXCEL中,我們用函數(shù)或者VBA按這條標(biāo)準(zhǔn)進(jìn)行判斷,可以智能地、快速地依據(jù)考勤機(jī)數(shù)據(jù)得到考勤統(tǒng)計表。
筆者E圖表述接觸過多種考勤機(jī)數(shù)據(jù),發(fā)現(xiàn)考勤機(jī)導(dǎo)出的數(shù)據(jù)依據(jù)打卡時間的記錄方式主要分為三類:打卡時間獨(dú)立型、日期和打卡時間混合型、同一單元格多個打卡時間型。我們分別來說明一下。
說明:以下數(shù)據(jù)統(tǒng)一按照上班時間8:00,下班時間17:00來計算遲到、早退情況。
打卡時間獨(dú)立型
特點(diǎn):
每一行就是一次打卡記錄,而且打卡日期和打卡時間是分開的兩列字段。
這種考勤應(yīng)該是最常見,也是處理最簡單的一類數(shù)據(jù)源。
筆者解法:
首先增加上午/下午輔助列,并用公式自動判定填寫上午或下午。
E2單元格輸入函數(shù):=IF(D2<=VALUE("12:00:00"),"上午","下午")
函數(shù)解析:
VALUE("12:00:00")將12:00:00轉(zhuǎn)化成時間格式,然后與D列的時間進(jìn)行對比,得到記錄條中打卡時間是“上午”還是“下午”,向下填充函數(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)),(被查詢目標(biāo)列))
通過上圖我們可以看到很多錯誤碼#N/A,那些就需要其他的判斷了。例如L2單元格應(yīng)該是“上午漏打卡”,O2和P2單元格則是“公休”。這不是本文的重點(diǎn),也不算難點(diǎn),無外乎用IF函數(shù)判斷條件再返回需要的說明即可,我們就不介紹了。
日期和打卡時間混合型
特點(diǎn):
日期和時間在一個單元格中,導(dǎo)出的數(shù)據(jù)基本都是文本格式。
日期有兩種常見格式:一種帶分隔符,如2020/09/01;一種不帶分隔符,如20200901。
日期都是雙位數(shù)顯示,如1月——01,12月——12;1日——01,8日——08。
處理這樣的內(nèi)容,完全可以先將打卡記錄處理成常規(guī)的【獨(dú)立時間型】之后,然后再來處理考勤。以“打卡記錄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)計表》中也和上例一樣,是不是融會貫通了?
同一單元格多個打卡時間型
特點(diǎn):
同一人當(dāng)天的多次打卡時間都在一個單元格中。
對于考勤統(tǒng)計來說,這種數(shù)據(jù)是最難處理的。而且我們上圖是模擬數(shù)據(jù),實際情況中,一個單元格中可能不止一兩條記錄。
處理這樣的數(shù)據(jù),筆者的總體思路還是先將其轉(zhuǎn)化成第一種數(shù)據(jù)類型那樣的表,然后再來統(tǒng)計。
這里用函數(shù)處理,難度比較大,運(yùn)行效率也比較低(數(shù)據(jù)量大了后),所以推薦給大家一個VBA的做法吧。(不要談VBA就色變,VBA很實用而且不難學(xué),有興趣的同學(xué)可以通過我們的QQ群找到輔導(dǎo)老師。)
簡單的幾行代碼就把數(shù)據(jù)A2:D7處理到F2:I13單元格區(qū)域了,那么接下的操作就回到了我們第一例中了。
代碼如下:
說明:灰色部分是筆者對代碼的注解,方便大家了解代碼的含義。藍(lán)色的引號在代碼中表示注釋,其后內(nèi)容不參與運(yùn)行。
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)計的其他需要,請留言——不留言,筆者可猜不出你的需求。
最后,點(diǎn)分享按鈕鼓勵一下筆者吧,我將寫出更多的教程來。
本文配套的練習(xí)課件請加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
做一張智能的人工考勤表:智能考勤表,MM你再也不用加班了!
一天打四次卡的考勤機(jī)數(shù)據(jù)整理:學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
如何整理考勤機(jī)導(dǎo)出的數(shù)據(jù):別怕:只要會簡化拆分,幾分鐘搞定考勤統(tǒng)計分析表!
看懂VBA的循環(huán)語句:VBA實戰(zhàn)入門教程(三):循環(huán)語句
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!