每到月初，讓人抓狂的事情就很多……
比如總有一些同事跑過來問：昨天發(fā)的工資怎么不對？考勤表是不是算錯了？
員工的考勤匯總，是HR小青每個月都要做的事情，也是工資發(fā)放的基礎(chǔ)工作。
做好考勤匯總隨時備查，看似簡單，里面卻隱藏著許多的Excel知識。
今天就手把手地來教大家制作一個考勤匯總表！
溫馨提示：本篇文章長達(dá)3000+字，涉及常用函數(shù)、技巧、VBA等，建議收藏起來慢慢看！
數(shù)據(jù)來源：
公司使用的是指紋考勤機(jī)，月底可以從機(jī)器里導(dǎo)出員工打卡記錄文件（xlsx格式，每天一張工作表），但是機(jī)器導(dǎo)出的數(shù)據(jù)只有員工編號和對應(yīng)的打卡時間。
原始數(shù)據(jù)：打卡記錄
小青需要將每日打卡記錄工作表中的原始數(shù)據(jù)，整理并匯總生成如下考勤匯總表：
匯總表效果
需要這個模板的同學(xué)
掃這個二維碼獲取
對比打卡記錄表和匯總表，小青需要做的事包括：
1.繪制每月的考勤匯總表
2.整理每張打卡記錄，使其滿足匯總表的需要
1）將員工編號與姓名對應(yīng)，便于按姓名匯總；
2）根據(jù)打卡時間并結(jié)合公司考勤規(guī)則判斷考勤狀態(tài)：正常、遲到/早退、加班；將打卡時間與考勤狀態(tài)組合成一個綜合考勤值，如“08:56:39（正常）”；
3）判斷打卡時間的歸屬時段：9點(diǎn)段（開始上班）、18點(diǎn)段（開始下班）、19點(diǎn)段（開始加班）；
4）將每個人每天的綜合考勤值，如08:56:39（正常），按打卡時間分別歸屬到對應(yīng)的時段（9:00、18:00、19:00）下；
5）計算當(dāng)日的出勤獎罰合計金額：遲到/早退罰款+加班獎勵。（如果您的公司在匯總階段不合計兩者，可以分別列出遲到/早退罰款、加班獎勵。）
3.將整理后的30張打卡記錄數(shù)據(jù)匯總到匯總表中
因?yàn)槊總€月導(dǎo)出打卡記錄數(shù)據(jù)非常多，又沒有模板可用，所以月底時小青總會通宵加班干這事！
下面我們來設(shè)計匯總模板實(shí)現(xiàn)一鍵匯總：
Step1：新建并保存為xlsm格式
新建一個Excel文件，將sheet 1工作表重命名為“匯總表”。保存文件，命名為“匯總表模板”，格式為xlsm（Excel啟用宏的工作簿）。
之所以選擇啟用宏的格式，是因?yàn)楹罄m(xù)我們要使用VBA代碼。
Step2：自動填充日期數(shù)據(jù)
在A1單元格輸入“2022年11月考勤匯總表”字樣，選中A1:G1，設(shè)置對齊方式為“跨列居中”，這樣可以在視覺上實(shí)現(xiàn)合并單元格的效果。
在D2單元格輸入公式“=MID($A$1,1,4)&MID($A$1,6,2)&"01"”生成與打卡記錄表名稱一樣的日期。將公式向右填充到G2單元格，完成第一天日期數(shù)據(jù)的填充。
公式解析：
用MID函數(shù)分別提取A1中前四位數(shù)字“2022”，第6、7位數(shù)字“11”，并使用“&”符號將字符“01”和它們連在一起組成“20221101”，表示第一天。
重要提醒：
匯總表中的日期與打卡記錄表名稱盡可能（非必須）保持一致！可能的話，應(yīng)根據(jù)導(dǎo)出的打卡記錄表名稱樣式來寫公式生成日期。
譬如，打卡記錄表名稱是“2021-11-01”，則D2單元格公式=MID($A$1,1,4)&"-"&MID($A$1,6,2)&"-"&"01"。
定位到H2單元格輸入公式“=D2+1”，向右拖至K2單元格，完成“20221102”的填充。選中H2:K2區(qū)域，向右拖直到生成四個“20221131”為止。生成“20221131”是為了滿足所有月的需要。
通過當(dāng)前公式設(shè)置，如果A1中的年份和月份修改了，則第二行的日期會自動更改。
Step3：填充星期數(shù)據(jù)
下面根據(jù)第二行的日期，在第三行加上對應(yīng)的星期數(shù)據(jù)。
在D3輸入公式“=TEXT(DATE(MID(D2,1,4),MID(D2,5,2),MID(D2,7,2)),"aaaa")”，向右填充。
公式解析：
用MID函數(shù)分別從D2單元格中提取3組數(shù)字，然后用DATE函數(shù)將其轉(zhuǎn)化為標(biāo)準(zhǔn)的年月日形式的日期，接著在外層套用TEXT函數(shù)獲得日期對應(yīng)的星期數(shù)。
Step4：填充第四行
第四行內(nèi)容包括姓名、編號、當(dāng)月扣款/加班、當(dāng)日扣款/加班，以及三個時段9:00、18:00、19:00。
Step5：輸入員工姓名和編號
根據(jù)花名冊錄入員工姓名和編號。（后續(xù)各月可以根據(jù)員工離入職實(shí)際，增刪、修改姓名和編號。）
保存文件。到此已經(jīng)繪制好匯總表。在完成數(shù)據(jù)整理后，我們還會返回此表中進(jìn)一步設(shè)置公式實(shí)現(xiàn)自動匯總。
Step1：新建整理模板表
繼續(xù)在當(dāng)前文件中工作。
新建一個名為“整理模板表”的工作表。接著打開打卡記錄文件“202211.xlsx”，將“20221101”表的內(nèi)容全選復(fù)制，粘貼到“整理模板表”中。關(guān)閉“202211.xlsx”文件。
Step2：建立整理后表格樣式
在I:M處建立整理后的表格樣式。C:H列暫時空著便于后續(xù)建立輔助列。
定位到I2單元格輸入公式“=IFERROR(匯總表!A5,"")”并下拉填充（下拉填充行數(shù)應(yīng)該超過當(dāng)前最大員工數(shù)20%及以上，以應(yīng)對今后可能的員工人數(shù)增長，增強(qiáng)模板的適用性），將“匯總表”中的所有員工姓名獲取過來。
Step3：將編號和員工姓名對應(yīng)
在C列創(chuàng)建“姓名”輔助列。
定位到C2單元格輸入公式“=LOOKUP(1,0/(匯總表!B$5:B$200=A2),匯總表!$A$5:$A$200)&""”，下拉公式完成編號和人名的對應(yīng)。
重要提醒：
公式下拉填充行數(shù)應(yīng)該比平常單日考勤最大行數(shù)多20%及以上，增強(qiáng)模板的適用性。切記！切記！切記！譬如，日常單日最大考勤量是150行，這里就可以下拉填充到C200。
文章后續(xù)提到的公式下拉填充都如此辦理。
公式解析：
這是一個典型的LOOKUP精確查找套路公式。不理解的小伙伴可以查看文章《LOOKUP函數(shù)用法全解（上）——LOOKUP函數(shù)的5種用法》。
Step4：打卡時段歸類
打卡時間需要?dú)w類在三個時間段中。（如果一天四次打卡，加上加班時段，則要?dú)w屬為五個時間段。）
<18點(diǎn),歸于9:00段
≥18點(diǎn)≤19點(diǎn)，歸于18:00段
其他，歸于19:00段
在D列新建“歸屬時段”輔助列。
在D2單元格輸入公式并下拉填充：
=IFS(B2=TIME(18,0,0),B2<=TIME(19,0,0)),"18:00",B2>TIME(19,0,0),"19:00")
公式解析：
采用IFS函數(shù)進(jìn)行多條件判斷。函數(shù)公式=IFS(條件1, 值1, [條件2, 值2 ], [條件3, 值3]...)。符合某個條件，就顯示某個值。
如果讀者的版本低不支持IFS函數(shù)，也可以使用IF函數(shù)，公式：
=IF(B2<time(18,0,0),"9:00",if(b2<time(19,0,1),"18:00","19:00"))< span="">。</time(18,0,0),"9:00",if(b2<time(19,0,1),"18:00","19:00"))<>
Step5：生成綜合考勤值
在E列建立“考勤狀態(tài)”輔助列。在G列和H列建立時間段與狀態(tài)對應(yīng)列，方便查找。
時間段和狀態(tài)對應(yīng)列是根據(jù)考勤規(guī)則建立的：
0:00:00到9:00:00之間打卡，正常上班；
9:00:01到17:59:59之間打卡， 遲到或者早退；
18:00:00到19:00:00之間打卡，正常下班；
19:00:01及以后打卡，加班。
定位到E2單元格輸入公式“=TEXT(B2,"hh:mm:ss")&"("&VLOOKUP(B2,$G$2:$H$5,2,1)&")"”，下拉公式生成打卡時間+考勤狀態(tài)的綜合考勤值。
公式解析：
TEXT函數(shù)將B2單元格的數(shù)值顯示為“時分秒”樣式的文本。使用VLOOKUP函數(shù)進(jìn)行分區(qū)段模糊查找得到狀態(tài)值。VLOOKUP分區(qū)段模糊查找可以看《多條件判斷，勸你用VLOOKUP函數(shù)模糊查找取代IF函數(shù)的一長串公式！》。最后使用“&”將時間和狀態(tài)以及括號連接起來。
Step6：計算當(dāng)次打卡的扣款/加班補(bǔ)助
公司考勤規(guī)定：
打卡時間在19:00:01及以后的為加班；每小時加班補(bǔ)助10元，超過1小時不足2小時的，四舍五入到整數(shù)進(jìn)行補(bǔ)貼。
遲到/早退則每次扣10元。（這里簡化了，實(shí)際可能根據(jù)遲到或早退的時長不同，扣款金額不同。）
定位到F2單元格輸入公式并下拉填充：
=IFS(VLOOKUP(B2,$G$2:$H$5,2,1)=$H$2,"",VLOOKUP(B2,$G$2:$H$5,2,1)=$H$3,-10,VLOOKUP(B2,$G$2:$H$5,2,1)=$H$5,ROUND((B2-TIME(19,0,1))*24,0)*10)
公式解析：
ROUND((B22-TIME(19,0,1))*24,0)，用來獲得四舍五入后的加班小時數(shù)。為何要乘以24？因?yàn)闀r間，如“18:00:02”，在Excel中的本質(zhì)是一個以天數(shù)為單位的小數(shù)“0.7500231”，只有乘以24后才得到小時數(shù)。
Excel低版本讀者也可以使用如下公式：
=IF(VLOOKUP(B2,$G$2:$H$5,2,1)=$H$3,-10,IF(VLOOKUP(B2,$G$2:$H$5,2,1)=$H$5,ROUND((B2-TIME(19,0,1))*24,0)*10,""))
如果公司的考勤規(guī)定遲到早退的時長不同扣款金額不同，則需要在公式中計算遲到或早退時長，然后套用Vlookup函數(shù)進(jìn)行分區(qū)查找獲得不同的扣款金額。
Step7：合計員工當(dāng)天扣款/加班補(bǔ)助金額
定位到J2單元格輸入公式并下拉填充：
=IF(SUMIF($C$2:$C$200,I2,$F$2:$F$200)<>0,SUMIF($C$2:$C$200,I2,$F$2:$F$200),"")
公式解析：
這里使用SUMIF函數(shù)，以$C$2:$C$200為條件區(qū)域，以I2的姓名作為條件，合計F列的金額。注意公式中的數(shù)據(jù)區(qū)域大小，如果公司有500人，每天打卡兩次，很顯然至少$C$2:$C$1001才合適。
外層嵌套IF函數(shù)的目的是讓沒有扣款/加班金額的顯示為空。
如果不需要顯示為空，則公式更簡單，=SUMIF($C$2:$C$200,I2,$F$2:$F$200)。
Step8:提取綜合考勤值
下面將綜合考勤值歸屬到三個時段下。
定位到K2單元格輸入數(shù)組公式按Ctrl+Shift+Enter 結(jié)束，然后向右向下填充：
=IFERROR(INDEX($E$1:$E$200,MATCH($I2&K$1,$C$1:$C$200&$D$1:$D$200,0)),"")
公式解析：
這是一個典型的INDEX嵌套MATCH函數(shù)引用數(shù)據(jù)的公式，不明白的可以看文章《INDEX:函數(shù)中的精確制導(dǎo)導(dǎo)彈，最強(qiáng)大的瘸子》。
保存文件。
至此，整理模板表已經(jīng)完成。只要將表中C~M列內(nèi)容粘貼到其他打卡記錄表中，即可自動完成整理。
為了省事，我們用VBA代碼來做這種批量復(fù)制粘貼操作。
Step9：用VBA代碼批量處理其他打卡記錄表
打開打卡記錄文件“202211.xlsx”，接著切換到打開的“匯總表模板xlsm”,按住Ctrl鍵依次選中“匯總表”和“整理模板表”，右擊選擇“移動或復(fù)制工作表”。
在彈出的“移動或復(fù)制工作表”對話框中，工作簿選擇“202211.xlsx”；位置選“20221101”，表示將上述需要移動的兩個工作表放置在此表之前；勾選下方“建立副本”。
單擊“確定”，關(guān)閉對話框后，“匯總表”和“整理模板表”附加到當(dāng)前的“202211.xlsx”文檔中。接著另存文件，將文檔保存為啟用宏的“11月匯總表.xlsm”。
關(guān)閉“匯總表模板.xlsm”文件。
按Alt+F11打開VBA編輯器，單擊“插入→模塊”命令，粘貼下列代碼，生成一個名為“遍歷工作表”的宏。
Sub 遍歷工作表()
'如果工作表名稱不包含“表”
For Each sh In Workbooks("11月匯總表.xlsm").Worksheets
If Not sh.Name Like "*" & "表" & "*" Then '如果工作表名稱不包含“表”
sh.Select
For i = 3 To ActiveWorkbook.Worksheets.Count
Sheets(2).Range("C1:M200").Copy ActiveWorkbook.Worksheets(i).Range("C1:M200")
Next
End If
Next
End Sub
代碼解釋
代碼中使用“FOR EACH”語句來遍歷工作簿，并排除名稱中包含“表”字的工作表（即匯總表和整理模板表）。設(shè)置變量“i”等于3，表示從排位第3的工作表（即每月第1天的工作表）開始粘貼；粘貼的內(nèi)容復(fù)制自排位第2的工作表（Sheets（2），即整理模板表）的“C1:M200”區(qū)域。這里的數(shù)據(jù)區(qū)域大小需要根據(jù)前面公式實(shí)際填充來修改，譬如公式填充到1000行，則修改為“C1:M1000”。
關(guān)閉VBA編輯窗口并保存文件。
然后單擊“開發(fā)工具”菜單下“宏”按鈕，在彈出的“宏”對話框中選擇“遍歷工作表”，單擊“執(zhí)行”，即可完成所有打卡表的自動整理。
宏對話框
自動整理后的“20221101”工作表
保存文件。
接下來需要做的是返回到“匯總表”工作表中進(jìn)行公式設(shè)置，完成整個模板的搭建。
由于篇幅原因，本篇內(nèi)容將講到這里，我們下篇繼續(xù)來給大家介紹。
聯(lián)系客服
