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

1、用 Excel 做考勤统计的详细方法 花了整整五天的时间，终于完成了。虽然还不是很完善，但从零开始，边学边做，到最后终 于可以用了，还是有那么点小小的成就感的。下面将具体方法详述如下，欢迎提出建议：上班安排： 假设某公司有甲、乙、丙、丁四个部门，以甲部门为例（因为其他部门的方法是一样的，只 不过时间设置不同而已），其上下班时间安排是：A 班： 7： 30-16 ： 30B 班： 12： 00-21 ： 00C 班： 10： 00-19 ： 00 D 班： 8： 30-17 ： 30 考勤规则：上班时间后 5 分钟内打卡不算迟到，加班半小时以下不计加班。 首先， 按名称整理好每个人的上下班的打卡

2、时间 （有电子打卡机的可以直接导入数据， 手动 打卡钟的就只能手动输入时间了） 第二步，在整理好的上下班时间工作表的第一行依次输入姓名（即 A1 格）、日期（即 B1 格）、排班（即 C1 格）、上班时间（后面的以此类推）、下班时间、考勤结果（上班）、 考勤结果（下班），标准下班时间、加班时间、加班时间修正等行名第三步， 如果有几个部门， 且每个部门的上下班时间不一致， 则最好按部门将员工分类在同 一个工作表的不同工作薄里，第四步，设置单元格的格式1、 凡是用时间表示的，都用“hh:mm的格式2、记得在输入时间的时候关闭输入法 第五步，下面将进行具体的计算公式设置（以甲部门的 A 班为例，其他

3、部门的不同班次， 只是公式里的时间不同）1 、上班的考勤结果计算公式：=IF（AND（D2>=VALUE（"07:35"）=TRUE," 迟到",IF（D2=0,"未打卡",""）此公式的意思是，如果“D2格，即上班时间列中的时间大于等于“：35”则显示迟到”如果“D2格中无数据，即为“0勺时候，则显示 未打卡”，以上两个条件都不符合的时候，则显示为空白，即正常上班的意思；2、下班的考勤结果计算公式：=IF（AND（E2>=VALUE（"16:30"）=TRUE," 加班&

4、quot;,IF（E2=0,"未打卡"，”早退"）此公式的意思是，如果“E2”，即下班时间列中的时间大于等于“ 16 30”则显示为 加班”,如果“E2格中无数据，即为“0的时候，则显示 未打卡”，以上两个条件都不符合的时候，则显示为 “早退 ”3、加班时间的计算公式=IF（E2-H2）<0,"0",E2-H2）此计算结果本来只需要“E2H2'即可，即用 下班打卡时间”减去 标准下班时间”即可，但这样的计算结果有可能会产生负数，比方说员工早退的时候，以至于造成后面的计算产生错误， 因此需要调整一下公式。此公式的意思是：如果“ E2

5、H2的计算结果小于 “ 0,”则将计算结果显示为“0”否则显示“ E2H2的计算结果。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)需要修正加班时间数的主要原因是” 在统计公式里” 会将每天的小于半小时的时间累加起来” 导致计算结果偏大” 违背了考勤规则” 即半小时以内不计入加班” 所以需要此公式来进行修

6、正。此公式比较复杂”因为有几层意思”分别解释如下：“IF(E2H2)*24)>=1,16,16.5) 的意思是，如果“ E2H2”即下班打卡时间”减去 标准下班时间 的计算结果乘上 24 后大于等于 1 ”则其计算结果为 “16”否则为 “16.5。”这里乘上 24 的原 因是需要将计算结果从时间数转换为小时数；“IF(HOUR(E2)-IF(E2- H2)*24)>=1,16,16.5) 的意思是，将 “E2格即 下班打卡时间”中的小 时位上的数减去 16 或 16.5“IF(MINUTE(l2)>=30,0.5,0)的意思是，如果“ 12即加班时间”列中的分钟数大于等于3

7、0分钟”则计算结果为 “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”则显示为空白”否则

8、按“(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," 未打卡 ","")就可以了。第七步： 将各个班次的计

9、算公式复制到每个人每天的那一行单元格中就可以了”计算结果会自动显示出来” 这里要注意的是”在复制的时候要对应好单元格” 否则也会产生错误” 而且 也会影响后面的考勤统计。第八步”下面将进行统计公式的设置1 、统计 “迟到 “的计算公式厅面 ”工作薄里=COUNTIF( 厅面 !F219:F249," 迟到 ") 这里是在同一个工作表中的不同工作薄中进行统计。公式的意思是计算“F219到“F249”个数据区域（31个单元格，代表31天）里迟到”这个字符的数量有几个。 事假”、病假”、例休”的计算公式一样，只需把迟到'改成 事假”或病假”或休”即可，而且都是在“F”中取

10、数据；但是 例休”的数据要在“C”即排班”列中取数据。2、统计未打上班卡”和未打下班卡”的计算公式=SUM（COUNTIF（厅面！F219:F249,"未打卡 ”）-J6）公式的意思是计算 厅面”工作薄里“F219到“F249这个数据区域（31个单元格，代表31天） 里未打卡”这个字符的数量有几个，再将计算结果减去aJ6格中的数据，“J6格是指每位员工例休的天数。因为按 考勤结果”的计算公式计算，员工在例休的时候，也会显示未打卡”的记录。未打上班卡”在考勤结果（上班）列中取数据，未打下班卡”在考勤结果（下班）列中取数据。3、统计出勤天数”的公式=SUM（31-J6-D6-E6-F6）

11、这个公式比较简单，只是个合计公式，用总天数减去事假” 病假”和例休”的天数即可。要注意的是，在做统计公式的设置的时候，必须对应好每位员工打卡时间的单元格区域，否则就会出错。到此，全部工作就算完成了，所有的设置只需一次，以后在统计其他月份的考勤的时候，只需将打卡时间重新整理，复制粘贴或者重新输入就可以了，但是在这样操作之前，应该先另存为”一份，保持原始文件的可用性，并且可以留档。总体来讲，整个过程还算满意，但得不到满分，主要有两个方面的问题：1、不能自动识别不同班次选择不同的计算公式，还需要人工按照排班表，选择不同的计算 公式进行计算，这会有些麻烦。2、加班时间修正”的计算公式还存在错误的地方，

12、会使计算结果偏大，出现错误的时候， 一般都会偏大0.5小时。以上两个问题还需要进一步的学习才能进行修正，敬请期待，也请高手指教。EXCEL制作考勤表，用公式计算出每天出勤工时！浏览次数：485次悬赏分：0|解决时间：2010-9-29 15:41 |提问者：Christine QQ制作工时考勤表，用什么公式可以自动计算出每天的出勤小时，C1是上班时间，D1是下班时间，e1要得出一天的出勤小时，请告知公式，谢 谢！最佳答案将C1,D1,e1的格式均设为时间，例如 13:30。公式：e仁D1-C1。单位传统的考勤登记方式有工作量大、 出错几率高、 统计分析麻烦等特点， 现在已经进入了 信息时代，为

13、何不用电脑来帮我们进行考勤呢？用 Excel 2002 制作简单、快捷、准确的考 勤表，你就能切身体会到信息时代的方便与快捷。一、基本框架设置首先要进行的工作当然就是新建工作簿， 在工作表中输入姓名、 日期， 制订考勤表的基 本框架。1. 启动 Excel 2002 ，新建一个工作簿，命名为 “2004年员工考勤表 ”，在工作簿中建立12 个工作表。2. 在当月工作表的 A1、B1单元格中分别输入 姓名”日期”，在A2单元格中输入第1 个员工的姓名，接着选择 A2、 A3 单元格，然后单击 “合并及居中 ”按钮将两个单元格合并为 1 个单元格，按照以上方法录入其他员工的姓名（如图 1）。图一3

14、. 在单元格 C1 中输入 “-71”，程序将自动转成 “7月 1 日”这（里假定考勤从 7月 1 日开 始）。用鼠标点选单元格 C1 ，并移动鼠标到单元格的右下角， 待出现实心 “十”字的填充柄后， 按住左键拖动到需要的位置，这样在 7月 1 日后便形成了一个日期序列。4. 单击日期序列最后的 “自动填充选项 ”智能填充标记， 在弹出来的菜单中点选 “以工作日 填充”项，程序便自动将星期六、日去掉，不记入考勤日期。5. 选中整个日期行， 在“单元格格式 ”对话框中点选 “数字”选项卡。在“分类”列表中点选 “自定义'项，在类型框中输入“d日" ”然后单击 确定”按钮。二、录

15、制宏1. 点选工作表中的任意一个单元格， 然后单击 工具t宏t录制新宏菜单项，进入 录制 新宏”对话框。 在“宏名”框中输入准备录制的宏的名称 -“返回当前时间 ”，“确定”后即进入宏的 录制阶段 （如图 2） 。2. 输入公式 “=now（ ）”，然后按回车键，接着单击 “停止录制 ”工具栏中的 “停止录制 ”按钮 结束录制。三、制作宏按钮1. 单击视图T工具栏T绘图'菜单项，调出 绘图”工具栏，接着选择 绘图”工具栏中的 绘 图T对齐T对齐网格项。2. 单击 视图t工具栏t窗体'菜单项，调出 窗体”工具栏，然后单击 窗体”工具栏上的 按 钮”，接着在单元格 B2 中拖动鼠标

16、绘制一个按钮， Excel 将自动打开 “指定宏 ”对话框，点选 其中的 “返回当前时间 ”宏，并单击 “确定 ”按钮（如图 3）。图 3 选择 “返回当前时间 ”宏3. 单击按钮， 将按钮上的文字更改为 “上班”，并对文字大小、 字体进行设置。 然后单击 “绘 图”工具栏上的 选择对象”按钮，并按住“Ctr键，用鼠标将刚才制作的按钮拖到 B3中，即将 制作好的按钮复制到 B3 中，并将按钮上的问题更改为 “下班”。4. 确认绘图”工具栏上的选择对象”按钮被选中，单击 下班”按钮，然后按住 “Shift键再单击 上班”按钮，将两个按钮同时选中。单击“Ctr键，用鼠标将选中的两个按钮拖动复制到B

17、 列的其余单元格中。最后效果（如图 4） 。图四四、记录出勤选中待插入上下班时间的单元格，单击同一行中的 “上班 ”或“下班 ”按钮即可。在单击 “上 班”、“下班 ”按钮时会返回很长一串数字，其实我们只需输入诸如“13:30这”样的时间即可。单击格式t单元格”菜单项，在弹出来的 单元格格式”对话框中点选 数字”选项卡，在分类框 中点选 “时间 ”，在 “类型”框中点选 “13:30，”最后单击 “确定 ”即可。否则当公式被重新计算后， 其结果将复制”，点选编辑t选择性粘贴”菜单项,要把出勤记录由公式结果转换成具体的时间数值，更改。选中表中的所有数据单元格，单击右键选择 在“选择性粘贴 ”对话框中选中 “数值 ”项，然后单击 “确定”。五、统计结果1. 在日期行之后的单元格中依次输入 “迟到 ”、“早退 ”、“病假”、“事假 ”等需要统计的项目。 并将这几列中的单元格上下两两合并，使之对应于姓名行（如图 5）。图 5 创建相关统计项目2. 单击单元格 Y2，然后键入公式 “=COUNTIF（C2:X2,">8 : 30"） ”（这里假设上班时间 为 8:30 ），并按回车键， Z2 单元格中便会出现选中员工所有迟于 8:30 上班的工作日天数。 同理在 Z2 单元格中输入公式 “=COUNTIF（C3: