用Excel做考勤统计的详细方法

用 Excel 做考勤统计的详细方法花了整整五天的时间，终于完成了。虽然还不是很完善，但从零开始，边学边做，到最后终 于可以用了，还是有那么点小小的成就感的。下面将具体方法详述如下，欢迎提出建议： 上班安排： 假设某公司有甲、乙、丙、丁四个部门，以甲部门为例(因为其他部门的方法是一样的，只 不过时间设置不同而已)，其上下班时间安排是：A 班：7 ： 30-16： 30B 班： 12： 00-21： 00 C 班： 10： 00-19： 00D 班：8 ： 30-17： 30 考勤规则：上班时间后5 分钟内打卡不算迟到，加班半小时以下不计加班。 首先，按名称整理好每个人的上下班的打卡时间(有电子打卡机的可以直接导入数据，手动 打卡钟的就只能手动输入时间了) 第二步，在整理好的上下班时间工作表的第一行依次输入姓名(即 A1 格)、日期(即 B1 格)、 排班(即 C1 格)、上班时间(后面的以此类推)、下班时间、考勤结果(上班)、考勤结果 (下班)，标准下班时间、加班时间、加班时间修正等行名 第三步，如果有几个部门，且每个部门的上下班时间不一致，则最好按部门将员工分类在同 一个工作表的不同工作薄里， 第四步，设置单元格的格式1、凡是用时间表示的，都用“ hh:m m”的格式2、记得在输入时间的时候关闭输入法 第五步，下面将进行具体的计算公式设置(以甲部门的 A 班为例，其他部门的不同班次， 只是公式里的时间不同)1、上班的考勤结果计算公式：=IF(AND(D2>=VALUE("07:35")=TRUE,"迟到",IF(D2=0,"未打卡","") 此公式的意思是，如果“ D2 ”格，即上班时间列中的时间大于等于“ 7：3 5 ” ，则显示“迟 到”，如果“D2”格中无数据，即为“0”的时候，贝y显示“未打卡”，以上两个条件都不符 合的时候，则显示为空白，即正常上班的意思；2、下班的考勤结果计算公式：=IF(AND(E2>=VALUE("16:30")=TRUE,"加班",IF(E2=0,"未打卡"，”早退")此公式的意思是，如果“E2”格，即下班时间列中的时间大于等于“16： 30”，则显示为“加 班”，如果“E2”格中无数据，即为“0”的时候，贝y显示“未打卡”，以上两个条件都不符 合的时候，贝显示为“早退”3、加班时间的计算公式 =IF(E2-H2)<0,"0",E2-H2)此计算结果本来只需要“E2-H2”即可，即用“下班打卡时间”减去“标准下班时间”即可, 但这样的计算结果有可能会产生负数，比方说员工早退的时候，以至于造成后面的计算产生 错误，因此需要调整一下公式。此公式的意思是：如果“E2-H2”的计算结果小于“0”，则 将计算结果显示为“0”，否则显示“E2-H2”的计算结果。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)需要修正加班时间数的主要原因是，在统计公式里，会将每天的小于半小时的时间累加起来， 导致计算结果偏大，违背了考勤规则，即半小时以内不计入加班，所以需要此公式来进行修 正。此公式比较复杂，因为有几层意思，分别解释如下：“IF(E2-H2)*24)>=1,16,16.5) ”的意思是，如果“ E2-H2 ”即“下班打卡时间”减去“标准 下班时间”的计算结果乘上24后大于等于1，则其计算结果为“16”，否则为“16.5”。这里 乘上24的原因是需要将计算结果从时间数转换为小时数；“IF(HOUR(E2)-IF(E2-H2)*24)>=1,16,16.5)”的意思是，将“E2” 格即“下班打卡时间” 中的小时位上的数减去16或16.5“IF(MINUTE(I2)>=30,0.5,0)”的意思是，如果“I2”即“加班时间”列中的分钟数大于等 于30分钟，则计算结果为“0.5”，否则为“0”，此公式就是考勤规则的修正公式；“ (HOUR(E2)-IF(E2-H2)*24)>=1,16,16.5)+(IF(MINUTE(I2)>=30,0.5,0) ”的意思就是将下班 打卡时间上的小时数减去16或16.5(标准下班时间)再加上分钟数上的修正公式所得到的 计算结果“0”或者“0.5”。这样就会使计算结果符合考勤规则。整个公式的意思，如果 “ (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)” 的计算结果显示。 这个公式是最复杂的，而且实际运用当中，也是有错误的。还需要继续学习来修正。第六步：所有的计算到此都已经完成了，其他班次的只要在公式中将时间改一下就可以了， 如B班，在计算上班的考勤结果时，将公式改成=IF(AND(D2>=VALUE("10:05")=TRUE," 迟到",IF(D2=0,"未打卡","")就可以了。第七步：将各个班次的计算公式复制到每个人每天的那一行单元格中就可以了，计算结果会 自动显示出来，这里要注意的是，在复制的时候要对应好单元格，否则也会产生错误，而且 也会影响后面的考勤统计。第八步，下面将进行统计公式的设置1、统计“迟到“的计算公式=COUNTI F(厅面!F219:F249,"迟到")这里是在同一个工作表中的不同工作薄中进行统计。公式的意思是计算“厅面”工作薄里“F219”到“F249”这个数据区域个单元格，代表31天)里“迟到”这个字符的数量 有几个。“事假”、“病假”、“例休”的计算公式一样，只需把“迟到”改成“事假”或“病假”或“休” 即可，而且都是在“F”列中取数据；但是“例休”的数据要在“C”列即“排班”列中取 数据。2、统计“未打上班卡”和“未打下班卡”的计算公式=SUM(COUNTI F(厅面! F219:F249,"未打卡 ”)-J6)公式的意思是计算“厅面”工作薄里“F219”到“F249”这个数据区域(31个单元格，代 表31天)里“未打卡”这个字符的数量有几个，再将计算结果减去J6”格中的数据，“J6” 格是指每位员工例休的天数。因为按“考勤结果”的计算公式计算，员工在例休的时候，也 会显示“未打卡”的记录。“未打上班卡”在“考勤结果(上班)列中取数据，“未打下班卡” 在“考勤结果(下班)列中取数据。3、统计“出勤天数”的公式=SUM(31-J6-D6-E6-F6)这个公式比较简单，只是个合计公式，用总天数减去“事假”、“病假”和“例休”的天数即 可。要注意的是，在做统计公式的设置的时候，必须对应好每位员工打卡时间的单元格区域，否 则就会出错。到此，全部工作就算完成了，所有的设置只需一次，以后在统计其他月份的考勤的时候，只 需将打卡时间重新整理，复制粘贴或者重新输入就可以了，但是在这样操作之前，应该先“另 存为”一份，保持原始文件的可用性，并且可以留档。总体来讲，整个过程还算满意，但得不到满分，主要有两个方面的问题：1、不能自动识别不同班次选择不同的计算公式，还需要人工按照排班表，选择不同的计算 公式进行计算，这会有些麻烦。2、“加班时间修正”的计算公式还存在错误的地方，会使计算结果偏大，出现错误的时候， 一般都会偏大0.5 小时。以上两个问题还需要进一步的学习才能进行修正，敬请期待，也请高手指教。