用Excel做考勤統(tǒng)計的詳細教程

1、用Excel做考勤統(tǒng)計的詳細方法花了整整五天的時間，終于完成了。雖然還不是很完善，但從零開始，邊學邊做，到最后終于可以用了，還是有那么點小小的成就感的。下面將具體方法詳述如下，歡迎提出建議：上班安排：假設某公司有甲、乙、丙、丁四個部門，以甲部門為例（因為其他部門的方法是一樣的，只不過時間設置不同而已），其上下班時間安排是：A班：7：30-16：30B班：12：00-21：00C班：10：00-19：00D班：8：30-17：30考勤規(guī)則：上班時間后5分鐘內打卡不算遲到，加班半小時以下不計加班。首先，按名稱整理好每個人的上下班的打卡時間（有電子打卡機的可以直接導入數(shù)據，手動打卡鐘的就只能手動輸入

2、時間了）第二步，在整理好的上下班時間工作表的第一行依次輸入姓名（即A1格）、日期（即B1格）、排班（即C1格）、上班時間（后面的以此類推）、下班時間、考勤結果（上班）、考勤結果（下班），標準下班時間、加班時間、加班時間修正等行名第三步，如果有幾個部門，且每個部門的上下班時間不一致，則最好按部門將員工分類在同一個工作表的不同工作薄里，第四步，設置單元格的格式1、凡是用時間表示的，都用“hh:mm”的格式2、記得在輸入時間的時候關閉輸入法第五步，下面將進行具體的計算公式設置（以甲部門的A班為例，其他部門的不同班次，只是公式里的時間不同）1、上班的考勤結果計算公式：=IF(AND(D2>=VA

3、LUE("07:35")=TRUE,"遲到",IF(D2=0,"未打卡","")此公式的意思是，如果“D2”格，即上班時間列中的時間大于等于“7：35”，則顯示“遲到”，如果“D2”格中無數(shù)據，即為“0”的時候，則顯示“未打卡”，以上兩個條件都不符合的時候，則顯示為空白，即正常上班的意思；2、下班的考勤結果計算公式：=IF(AND(E2>=VALUE("16:30")=TRUE,"加班",IF(E2=0,"未打卡","早退")此公

4、式的意思是，如果“E2”格，即下班時間列中的時間大于等于“16：30”，則顯示為“加班”，如果“E2”格中無數(shù)據，即為“0”的時候，則顯示“未打卡”，以上兩個條件都不符合的時候，則顯示為“早退”3、加班時間的計算公式=IF(E2-H2)<0,"0",E2-H2)此計算結果本來只需要“E2-H2”即可，即用“下班打卡時間”減去“標準下班時間”即可，但這樣的計算結果有可能會產生負數(shù)，比方說員工早退的時候，以至于造成后面的計算產生錯誤，因此需要調整一下公式。此公式的意思是：如果“E2-H2”的計算結果小于“0”，則將計算結果顯示為“0”，否則顯示“E2-H2”的計算結果。4

5、、加班時間修正的計算公式=IF(HOUR(E2)-IF(E2-H2)*24)>=1,16,16.5)+(IF(MINUTE(I2)>=30,0.5,0)<0,"",HOUR(E2)-IF(E2-H2)*24)>=1,16,16.5)+(IF(MINUTE(I2)>=30,0.5,0)需要修正加班時間數(shù)的主要原因是，在統(tǒng)計公式里，會將每天的小于半小時的時間累加起來，導致計算結果偏大，違背了考勤規(guī)則，即半小時以內不計入加班，所以需要此公式來進行修正。此公式比較復雜，因為有幾層意思，分別解釋如下：“IF(E2-H2)*24)>=1,16,16.

6、5)”的意思是，如果“E2-H2”即“下班打卡時間”減去“標準下班時間”的計算結果乘上24后大于等于1，則其計算結果為“16”，否則為“16.5”。這里乘上24的原因是需要將計算結果從時間數(shù)轉換為小時數(shù)；“IF(HOUR(E2)-IF(E2-H2)*24)>=1,16,16.5)”的意思是，將“E2”格即“下班打卡時間”中的小時位上的數(shù)減去16或16.5“IF(MINUTE(I2)>=30,0.5,0)”的意思是，如果“I2”即“加班時間”列中的分鐘數(shù)大于等于30分鐘，則計算結果為“0.5”，否則為“0”，此公式就是考勤規(guī)則的修正公式；“(HOUR(E2)-IF(E2-H2)*24

7、)>=1,16,16.5)+(IF(MINUTE(I2)>=30,0.5,0)”的意思就是將下班打卡時間上的小時數(shù)減去16或16.5（標準下班時間）再加上分鐘數(shù)上的修正公式所得到的計算結果“0”或者“0.5”。這樣就會使計算結果符合考勤規(guī)則。整個公式的意思，如果“(HOUR(E2)-IF(E2-H2)*24)>=1,16,16.5)+(IF(MINUTE(I2)>=30,0.5,0)”的計算結果小于“0”，則顯示為空白，否則按“(HOUR(E2)-IF(E2-H2)*24)>=1,16,16.5)+(IF(MINUTE(I2)>=30,0.5,0)”的計算結

8、果顯示。這個公式是最復雜的，而且實際運用當中，也是有錯誤的。還需要繼續(xù)學習來修正。第六步：所有的計算到此都已經完成了，其他班次的只要在公式中將時間改一下就可以了，如B班，在計算上班的考勤結果時，將公式改成=IF(AND(D2>=VALUE("10:05")=TRUE,"遲到",IF(D2=0,"未打卡","")就可以了。第七步：將各個班次的計算公式復制到每個人每天的那一行單元格中就可以了，計算結果會自動顯示出來，這里要注意的是，在復制的時候要對應好單元格，否則也會產生錯誤，而且也會影響后面的考勤統(tǒng)計。第八步，

9、下面將進行統(tǒng)計公式的設置1、統(tǒng)計“遲到“的計算公式=COUNTIF(廳面!F219:F249,"遲到")這里是在同一個工作表中的不同工作薄中進行統(tǒng)計。公式的意思是計算“廳面”工作薄里“F219”到“F249”這個數(shù)據區(qū)域（31個單元格，代表31天）里“遲到”這個字符的數(shù)量有幾個?！笆录佟?、“病假”、“例休”的計算公式一樣，只需把“遲到”改成“事假”或“病假”或“休”即可，而且都是在“F”列中取數(shù)據；但是“例休”的數(shù)據要在“C”列即“排班”列中取數(shù)據。2、統(tǒng)計“未打上班卡”和“未打下班卡”的計算公式=SUM(COUNTIF(廳面!F219:F249,"未打卡&quo

10、t;)-J6)公式的意思是計算“廳面”工作薄里“F219”到“F249”這個數(shù)據區(qū)域（31個單元格，代表31天）里“未打卡”這個字符的數(shù)量有幾個，再將計算結果減去“J6”格中的數(shù)據，“J6”格是指每位員工例休的天數(shù)。因為按“考勤結果”的計算公式計算，員工在例休的時候，也會顯示“未打卡”的記錄?！拔创蛏习嗫ā痹凇翱记诮Y果（上班）列中取數(shù)據，“未打下班卡”在“考勤結果（下班）列中取數(shù)據。3、統(tǒng)計“出勤天數(shù)”的公式=SUM(31-J6-D6-E6-F6)這個公式比較簡單，只是個合計公式，用總天數(shù)減去“事假”、“病假”和“例休”的天數(shù)即可。要注意的是，在做統(tǒng)計公式的設置的時候，必須對應好每位員工打卡時間的單元格區(qū)域，否則就會出錯。到此，全部工作就算完成了，所有的設置只需一次，以后在統(tǒng)計其他月份的考勤的時候，只需將打卡時間重新整理，復制粘貼或者重新輸入就可以了，但是在這樣操作之前，應該先“另存為”一份，保持原始文件的可用性，并且可以留檔?？傮w來講，整個過程