用Excel做考勤統(tǒng)計的詳細(xì)方法

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

2、時間 （有電子打卡機(jī)的可以直接導(dǎo)入數(shù)據(jù)， 手動 打卡鐘的就只能手動輸入時間了） 第二步，在整理好的上下班時間工作表的第一行依次輸入姓名（即 A1 格）、日期（即 B1 格）、排班（即 C1 格）、上班時間（后面的以此類推）、下班時間、考勤結(jié)果（上班）、 考勤結(jié)果（下班），標(biāo)準(zhǔn)下班時間、加班時間、加班時間修正等行名第三步， 如果有幾個部門， 且每個部門的上下班時間不一致， 則最好按部門將員工分類在同 一個工作表的不同工作薄里，第四步，設(shè)置單元格的格式1、 凡是用時間表示的，都用“hh:mm的格式2、記得在輸入時間的時候關(guān)閉輸入法 第五步，下面將進(jìn)行具體的計算公式設(shè)置（以甲部門的 A 班為例，其他

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

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

5、H2的計算結(jié)果小于 “ 0,”則將計算結(jié)果顯示為“0”否則顯示“ E2H2的計算結(jié)果。4、加班時間修正的計算公式=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)計公式里” 會將每天的小于半小時的時間累加起來” 導(dǎo)致計算結(jié)果偏大” 違背了考勤規(guī)則” 即半小時以內(nèi)不計入加班” 所以需要此公式來進(jìn)行修

6、正。此公式比較復(fù)雜”因?yàn)橛袔讓右馑肌狈謩e解釋如下：“IF(E2H2)*24)>=1,16,16.5) 的意思是，如果“ E2H2”即下班打卡時間”減去 標(biāo)準(zhǔn)下班時間 的計算結(jié)果乘上 24 后大于等于 1 ”則其計算結(jié)果為 “16”否則為 “16.5?！边@里乘上 24 的原 因是需要將計算結(jié)果從時間數(shù)轉(zhuǎn)換為小時數(shù)；“IF(HOUR(E2)-IF(E2- H2)*24)>=1,16,16.5) 的意思是，將 “E2格即 下班打卡時間”中的小 時位上的數(shù)減去 16 或 16.5“IF(MINUTE(l2)>=30,0.5,0)的意思是，如果“ 12即加班時間”列中的分鐘數(shù)大于等于3

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

8、按“(HOUR(E2)-IF(E2- H2)*24)>=1,16,16.5)+(IF(MINUTE(I2)>=30,0.5,0) 的”計算結(jié)果顯示。 這個公式是最復(fù)雜的”而且實(shí)際運(yùn)用當(dāng)中”也是有錯誤的。還需要繼續(xù)學(xué)習(xí)來修正。第六步：所有的計算到此都已經(jīng)完成了”其他班次的只要在公式中將時間改一下就可以了”如 B 班”在計算上班的考勤結(jié)果時”將公式改成 =IF(AND(D2>=VALUE("10:05")=TRUE," 遲到 ",IF(D2=0," 未打卡 ","")就可以了。第七步： 將各個班次的計

9、算公式復(fù)制到每個人每天的那一行單元格中就可以了”計算結(jié)果會自動顯示出來” 這里要注意的是”在復(fù)制的時候要對應(yīng)好單元格” 否則也會產(chǎn)生錯誤” 而且 也會影響后面的考勤統(tǒng)計。第八步”下面將進(jìn)行統(tǒng)計公式的設(shè)置1 、統(tǒng)計 “遲到 “的計算公式廳面 ”工作薄里=COUNTIF( 廳面 !F219:F249," 遲到 ") 這里是在同一個工作表中的不同工作薄中進(jìn)行統(tǒng)計。公式的意思是計算“F219到“F249”個數(shù)據(jù)區(qū)域（31個單元格，代表31天）里遲到”這個字符的數(shù)量有幾個。 事假”、病假”、例休”的計算公式一樣，只需把遲到'改成 事假”或病假”或休”即可，而且都是在“F”中取

10、數(shù)據(jù)；但是 例休”的數(shù)據(jù)要在“C”即排班”列中取數(shù)據(jù)。2、統(tǒng)計未打上班卡”和未打下班卡”的計算公式=SUM（COUNTIF（廳面！F219:F249,"未打卡 ”）-J6）公式的意思是計算 廳面”工作薄里“F219到“F249這個數(shù)據(jù)區(qū)域（31個單元格，代表31天） 里未打卡”這個字符的數(shù)量有幾個，再將計算結(jié)果減去aJ6格中的數(shù)據(jù)，“J6格是指每位員工例休的天數(shù)。因?yàn)榘?考勤結(jié)果”的計算公式計算，員工在例休的時候，也會顯示未打卡”的記錄。未打上班卡”在考勤結(jié)果（上班）列中取數(shù)據(jù)，未打下班卡”在考勤結(jié)果（下班）列中取數(shù)據(jù)。3、統(tǒng)計出勤天數(shù)”的公式=SUM（31-J6-D6-E6-F6）

11、這個公式比較簡單，只是個合計公式，用總天數(shù)減去事假” 病假”和例休”的天數(shù)即可。要注意的是，在做統(tǒng)計公式的設(shè)置的時候，必須對應(yīng)好每位員工打卡時間的單元格區(qū)域，否則就會出錯。到此，全部工作就算完成了，所有的設(shè)置只需一次，以后在統(tǒng)計其他月份的考勤的時候，只需將打卡時間重新整理，復(fù)制粘貼或者重新輸入就可以了，但是在這樣操作之前，應(yīng)該先另存為”一份，保持原始文件的可用性，并且可以留檔?？傮w來講，整個過程還算滿意，但得不到滿分，主要有兩個方面的問題：1、不能自動識別不同班次選擇不同的計算公式，還需要人工按照排班表，選擇不同的計算 公式進(jìn)行計算，這會有些麻煩。2、加班時間修正”的計算公式還存在錯誤的地方，

12、會使計算結(jié)果偏大，出現(xiàn)錯誤的時候， 一般都會偏大0.5小時。以上兩個問題還需要進(jìn)一步的學(xué)習(xí)才能進(jìn)行修正，敬請期待，也請高手指教。EXCEL制作考勤表，用公式計算出每天出勤工時！瀏覽次數(shù)：485次懸賞分：0|解決時間：2010-9-29 15:41 |提問者：Christine QQ制作工時考勤表，用什么公式可以自動計算出每天的出勤小時，C1是上班時間，D1是下班時間，e1要得出一天的出勤小時，請告知公式，謝 謝！最佳答案將C1,D1,e1的格式均設(shè)為時間，例如 13:30。公式：e仁D1-C1。單位傳統(tǒng)的考勤登記方式有工作量大、 出錯幾率高、 統(tǒng)計分析麻煩等特點(diǎn)， 現(xiàn)在已經(jīng)進(jìn)入了 信息時代，為

13、何不用電腦來幫我們進(jìn)行考勤呢？用 Excel 2002 制作簡單、快捷、準(zhǔn)確的考 勤表，你就能切身體會到信息時代的方便與快捷。一、基本框架設(shè)置首先要進(jìn)行的工作當(dāng)然就是新建工作簿， 在工作表中輸入姓名、 日期， 制訂考勤表的基 本框架。1. 啟動 Excel 2002 ，新建一個工作簿，命名為 “2004年員工考勤表 ”，在工作簿中建立12 個工作表。2. 在當(dāng)月工作表的 A1、B1單元格中分別輸入 姓名”日期”，在A2單元格中輸入第1 個員工的姓名，接著選擇 A2、 A3 單元格，然后單擊 “合并及居中 ”按鈕將兩個單元格合并為 1 個單元格，按照以上方法錄入其他員工的姓名（如圖 1）。圖一3

14、. 在單元格 C1 中輸入 “-71”，程序?qū)⒆詣愚D(zhuǎn)成 “7月 1 日”這（里假定考勤從 7月 1 日開 始）。用鼠標(biāo)點(diǎn)選單元格 C1 ，并移動鼠標(biāo)到單元格的右下角， 待出現(xiàn)實(shí)心 “十”字的填充柄后， 按住左鍵拖動到需要的位置，這樣在 7月 1 日后便形成了一個日期序列。4. 單擊日期序列最后的 “自動填充選項(xiàng) ”智能填充標(biāo)記， 在彈出來的菜單中點(diǎn)選 “以工作日 填充”項(xiàng)，程序便自動將星期六、日去掉，不記入考勤日期。5. 選中整個日期行， 在“單元格格式 ”對話框中點(diǎn)選 “數(shù)字”選項(xiàng)卡。在“分類”列表中點(diǎn)選 “自定義'項(xiàng)，在類型框中輸入“d日" ”然后單擊 確定”按鈕。二、錄

15、制宏1. 點(diǎn)選工作表中的任意一個單元格， 然后單擊 工具t宏t錄制新宏菜單項(xiàng)，進(jìn)入 錄制 新宏”對話框。 在“宏名”框中輸入準(zhǔn)備錄制的宏的名稱 -“返回當(dāng)前時間 ”，“確定”后即進(jìn)入宏的 錄制階段 （如圖 2） 。2. 輸入公式 “=now（ ）”，然后按回車鍵，接著單擊 “停止錄制 ”工具欄中的 “停止錄制 ”按鈕 結(jié)束錄制。三、制作宏按鈕1. 單擊視圖T工具欄T繪圖'菜單項(xiàng)，調(diào)出 繪圖”工具欄，接著選擇 繪圖”工具欄中的 繪 圖T對齊T對齊網(wǎng)格項(xiàng)。2. 單擊 視圖t工具欄t窗體'菜單項(xiàng)，調(diào)出 窗體”工具欄，然后單擊 窗體”工具欄上的 按 鈕”，接著在單元格 B2 中拖動鼠標(biāo)

16、繪制一個按鈕， Excel 將自動打開 “指定宏 ”對話框，點(diǎn)選 其中的 “返回當(dāng)前時間 ”宏，并單擊 “確定 ”按鈕（如圖 3）。圖 3 選擇 “返回當(dāng)前時間 ”宏3. 單擊按鈕， 將按鈕上的文字更改為 “上班”，并對文字大小、 字體進(jìn)行設(shè)置。 然后單擊 “繪 圖”工具欄上的 選擇對象”按鈕，并按住“Ctr鍵，用鼠標(biāo)將剛才制作的按鈕拖到 B3中，即將 制作好的按鈕復(fù)制到 B3 中，并將按鈕上的問題更改為 “下班”。4. 確認(rèn)繪圖”工具欄上的選擇對象”按鈕被選中，單擊 下班”按鈕，然后按住 “Shift鍵再單擊 上班”按鈕，將兩個按鈕同時選中。單擊“Ctr鍵，用鼠標(biāo)將選中的兩個按鈕拖動復(fù)制到B

17、 列的其余單元格中。最后效果（如圖 4） 。圖四四、記錄出勤選中待插入上下班時間的單元格，單擊同一行中的 “上班 ”或“下班 ”按鈕即可。在單擊 “上 班”、“下班 ”按鈕時會返回很長一串?dāng)?shù)字，其實(shí)我們只需輸入諸如“13:30這”樣的時間即可。單擊格式t單元格”菜單項(xiàng)，在彈出來的 單元格格式”對話框中點(diǎn)選 數(shù)字”選項(xiàng)卡，在分類框 中點(diǎn)選 “時間 ”，在 “類型”框中點(diǎn)選 “13:30，”最后單擊 “確定 ”即可。否則當(dāng)公式被重新計算后， 其結(jié)果將復(fù)制”，點(diǎn)選編輯t選擇性粘貼”菜單項(xiàng),要把出勤記錄由公式結(jié)果轉(zhuǎn)換成具體的時間數(shù)值，更改。選中表中的所有數(shù)據(jù)單元格，單擊右鍵選擇 在“選擇性粘貼 ”對話框中選中 “數(shù)值 ”項(xiàng)，然后單擊 “確定”。五、統(tǒng)計結(jié)果1. 在日期行之后的單元格中依次輸入 “遲到 ”、“早退 ”、“病假”、“事假 ”等需要統(tǒng)計的項(xiàng)目。 并將這幾列中的單元格上下兩兩合并，使之對應(yīng)于姓名行（如圖 5）。圖 5 創(chuàng)建相關(guān)統(tǒng)計項(xiàng)目2. 單擊單元格 Y2，然后鍵入公式 “=COUNTIF（C2:X2,">8 : 30"） ”（這里假設(shè)上班時間 為 8:30 ），并按回車鍵， Z2 單元格中便會出現(xiàn)選中員工所有遲于 8:30 上班的工作日天數(shù)。 同理在 Z2 單元格中輸入公式 “=COUNTIF（C3: