|
藍字發(fā)送【目錄】
送你200篇獨家Excel精華教程 
編按： 根據(jù)考勤機數(shù)據(jù)統(tǒng)計考勤表是每個考勤人員的磨難之一。有的考勤機導(dǎo)出的數(shù)據(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ù)考勤機數(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"),"上午","下午")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早退","正常"))通過IF函數(shù)，判斷打卡是遲到、早退，還是正常。如果打卡記錄是“上午”，那么判斷是否大于8:00:00，是就是“遲到”，不是就是“正常”；“下午”，則判斷是否小于17:00:00，是就是“早退”，不是就是“正?！薄?/span>最后這樣處理源數(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))多條件索引的方式有很多、很多，筆者在這里采用的是LOOKUP。LOOKUP(1,0/…)是一個很常用的多條件索引方法，之前的教程中也有涉及，這里就不再贅述。給大家一個公式以后可以套用。=LOOKUP（1，0/（（條件1）*（條件2）*（條件3）*…*（條件n）），（被查詢目標(biāo)列）） 通過上圖我們可以看到很多錯誤碼#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ù)Left直接提取日期部分，再用兩個符號【--】轉(zhuǎn)換為數(shù)值，最后調(diào)整單元格格式為日期就可以了。在F2單元格輸入函數(shù)：=--RIGHT(D2,LEN(D2)-11)用文本函數(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)計。
|
