編按：

如何快速將考勤打卡機導出的打卡記錄表整理匯總成考勤統(tǒng)計表，讓每位員工的出勤天數(shù)、遲到、早退、加班情況一目了然？你需要設(shè)計制作一個考勤匯總模板文檔——有了它就可以一鍵生成需要的匯總統(tǒng)計表。本篇介紹如何繪制匯總表、如何整理打卡數(shù)據(jù)，位自動統(tǒng)計打下基礎(chǔ)。

每到月底，讓人抓狂的事情就很多……

比如每個月底最讓小青頭疼的就是公司的考勤表匯總。她是一家小公司的HR，每個月都要制作考勤匯總表。

公司使用的是指紋考勤機，月底雖然可以從機器里導出員工打卡記錄文件（xlsx格式，每天一張工作表），但是機器導出的數(shù)據(jù)只有員工編號和對應的打卡時間。

原始數(shù)據(jù)：打卡記錄

小青需要將每日打卡記錄工作表中的原始數(shù)據(jù)，整理并匯總生成如下考勤匯總表，直接反映出員工的遲到早退、加班金額：

匯總表效果

對比打卡記錄表和匯總表，小青需要做的事包括：

1.繪制每月的考勤匯總表

2.整理每張打卡記錄，使其滿足匯總表的需要

1）將員工編號與姓名對應，便于按姓名匯總；

2）根據(jù)打卡時間并結(jié)合公司考勤規(guī)則判斷考勤狀態(tài)：正常、遲到/早退、加班；將打卡時間與考勤狀態(tài)組合成一個綜合考勤值，如“08:56:39（正常）”；

3）判斷打卡時間的歸屬時段：9點段（開始上班）、18點段（開始下班）、19點段（開始加班）；

4）將每個人每天的綜合考勤值，如08:56:39（正常），按打卡時間分別歸屬到對應的時段（9:00、18:00、19:00）下；

5）計算當日的出勤獎罰合計金額：遲到/早退罰款+加班獎勵。（如果您的公司在匯總階段不合計兩者，可以分別列出遲到/早退罰款、加班獎勵。）

3.將整理后的30張打卡記錄數(shù)據(jù)匯總到匯總表中

因為每個月導出打卡記錄數(shù)據(jù)非常多，又沒有模板可用，所以月底時小青總會通宵加班干這事！

下面我們來設(shè)計匯總模板實現(xiàn)一鍵匯總，徹底告別加班。

匯總表繪制

Step1：新建并保存為xlsm格式

新建一個Excel文件，將sheet 1工作表重命名為“匯總表”。保存文件，命名為“匯總表模板”，格式為xlsm（Excel啟用宏的工作簿）。

之所以選擇啟用宏的格式，是因為后續(xù)我們要使用VBA代碼。

Step2：自動填充日期數(shù)據(jù)

在A1單元格輸入“2022年11月考勤匯總表”字樣，選中A1:G1，設(shè)置對齊方式為“跨列居中”，這樣可以在視覺上實現(xiàn)合并單元格的效果。注意：“2022年11月考勤匯總表”中的年必須是四位數(shù)，月必須是兩位數(shù)，不足位數(shù)的在前面加0占位，如2月，就寫成02月。

在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”，表示第一天。

重要提醒：

匯總表中的日期與打卡記錄表名稱盡可能（非必須）保持一致！可能的話，應根據(jù)導出的打卡記錄表名稱樣式來寫公式生成日期。

譬如，打卡記錄表名稱是“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”是為了滿足所有月的需要。

通過當前公式設(shè)置，如果A1中的年份和月份修改了，則第二行的日期會自動更改。

Step3：填充星期數(shù)據(jù)

下面根據(jù)第二行的日期，在第三行加上對應的星期數(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)化為標準的年月日形式的日期，接著在外層套用TEXT函數(shù)獲得日期對應的星期數(shù)。

Step4：填充第四行

第四行內(nèi)容包括姓名、編號、當月扣款/加班、當日扣款/加班，以及三個時段9:00、18:00、19:00。

Step5：輸入員工姓名和編號

根據(jù)花名冊錄入員工姓名和編號。（后續(xù)各月可以根據(jù)員工離入職實際，增刪、修改姓名和編號。）

保存文件。到此已經(jīng)繪制好匯總表。在完成數(shù)據(jù)整理后，我們還會返回此表中進一步設(shè)置公式實現(xiàn)自動匯總。

建立打卡數(shù)據(jù)整理模板

Step1：新建整理模板表

繼續(xù)在當前文件中工作。

新建一個名為“整理模板表”的工作表。接著打開打卡記錄文件“202211.xlsx”，將“20221101”表的內(nèi)容全選復制，粘貼到“整理模板表”中。關(guān)閉“202211.xlsx”文件。

Step2：建立整理后表格樣式

在I:M處建立整理后的表格樣式。C:H列暫時空著便于后續(xù)建立輔助列。

定位到I2單元格輸入公式“=IFERROR(匯總表!A5,"")”并下拉填充（下拉填充行數(shù)應該超過當前最大員工數(shù)20%及以上，以應對今后可能的員工人數(shù)增長，增強模板的適用性），將“匯總表”中的所有員工姓名獲取過來。

Step3：將編號和員工姓名對應

在C列創(chuàng)建“姓名”輔助列。

定位到C2單元格輸入公式“=LOOKUP(1,0/(匯總表!B$5:B$200=A2),匯總表!$A$5:$A$200)&""”，下拉公式完成編號和人名的對應。

重要提醒：

公式下拉填充行數(shù)應該比平常單日考勤最大行數(shù)多20%及以上，增強模板的適用性。切記！切記！切記！譬如，日常單日最大考勤量是150行，這里就可以下拉填充到C200。

文章后續(xù)提到的公式下拉填充都如此辦理。

公式解析：

這是一個典型的LOOKUP精確查找套路公式。不理解的小伙伴可以查看文章《LOOKUP函數(shù)用法全解（上）——LOOKUP函數(shù)的5種用法》。

Step4：打卡時段歸類

打卡時間需要歸類在三個時間段中。（如果一天四次打卡，加上加班時段，則要歸屬為五個時間段。）

<18點,歸于9:00段（上午）

≥18點≤19點，歸于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ù)進行多條件判斷。函數(shù)公式=IFS(條件1, 值1, [條件2, 值2 ], [條件3, 值3]...)。符合某個條件，就顯示某個值。

如果讀者的版本低不支持IFS函數(shù)，也可以使用IF函數(shù)，公式：

Step5：生成綜合考勤值

在E列建立“考勤狀態(tài)”輔助列。在G列和H列建立時間段與狀態(tài)對應列，方便查找。

時間段和狀態(tài)對應列是根據(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ù)進行分區(qū)段模糊查找得到狀態(tài)值。VLOOKUP分區(qū)段模糊查找可以看《多條件判斷，勸你用VLOOKUP函數(shù)模糊查找取代IF函數(shù)的一長串公式！》。最后使用“&”將時間和狀態(tài)以及括號連接起來。

Step6：計算當次打卡的扣款/加班補助

公司考勤規(guī)定：

打卡時間在19:00:01及以后的為加班；每小時加班補助10元，超過1小時不足2小時的，四舍五入到整數(shù)進行補貼。

遲到/早退則每次扣10元。（這里簡化了，實際可能根據(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？因為時間，如“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ù)進行分區(qū)查找獲得不同的扣款金額。

Step7：合計員工當天扣款/加班補助金額

定位到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ù)中的精確制導導彈，最強大的瘸子》。

保存文件。

至此，整理模板表已經(jīng)完成。只要將表中C~M列內(nèi)容粘貼到其他打卡記錄表中，即可自動完成整理。

為了省事，我們用VBA代碼來做這種批量復制粘貼操作。

Step9：用VBA代碼批量處理其他打卡記錄表

打開打卡記錄文件“202211.xlsx”，接著切換到打開的“匯總表模板xlsm”,按住Ctrl鍵依次選中“匯總表”和“整理模板表”，右擊選擇“移動或復制工作表”。

在彈出的“移動或復制工作表”對話框中，工作簿選擇“202211.xlsx”；位置選“20221101”，表示將上述需要移動的兩個工作表放置在此表之前；勾選下方“建立副本”。

單擊“確定”，關(guān)閉對話框后，“匯總表”和“整理模板表”附加到當前的“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)容復制自排位第2的工作表（Sheets（2），即整理模板表）的“C1:M200”區(qū)域。這里的數(shù)據(jù)區(qū)域大小需要根據(jù)前面公式實際填充來修改，譬如公式填充到1000行，則修改為“C1:M1000”。

關(guān)閉VBA編輯窗口并保存文件。

然后單擊“開發(fā)工具”菜單下“宏”按鈕，在彈出的“宏”對話框中選擇“遍歷工作表”，單擊“執(zhí)行”，即可完成所有打卡表的自動整理。

宏對話框

自動整理后的“20221101”工作表

保存文件。

接下來需要做的是返回到“匯總表”工作表中進行公式設(shè)置，完成整個模板的搭建。由于篇幅原因，我們下篇再來給大家講解。

本文配套的練習課件請加入QQ群：902294808下載。

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

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

相關(guān)推薦：

別怕，VBA入門級教程來了，條件語句很簡單！

優(yōu)秀員工組別查找？INDEX、OFFSET、LOOKUP……我有100個函數(shù)可以解決這個問題

Excel教程：如何制作帶有層次和透視感的圖表？

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權(quán)申明：

本文作者ITfans；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。