excel函数考勤.docx

excel函数考勤.docx

《excel函数考勤.docx》由会员分享，可在线阅读，更多相关《excel函数考勤.docx（15页珍藏版）》请在冰豆网上搜索。

excel函数考勤

excel函数考勤

篇一：

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

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

花了整整五天的时间，终于完成了。

虽然还不是很完善，但从零开始，边学边做，到最后终于可以用了，还是有那么点小小的成就感的。

下面将具体方法详述如下，欢迎提出建议：

上班安排：

假设某公司有甲、乙、丙、丁四个部门，以甲部门为例（因为其他部门的方法是一样的，只不过时间设置不同而已），其上下班时间安排是：

a班：

7：

30-16：

30

B班：

12：

00-21：

00

c班：

10：

00-19：

00

d班：

8：

30-17：

30

考勤规则：

上班时间后5分钟内打卡不算迟到，加班半小时以下不计加班。

首先，按名称整理好每个人的上下班的打卡时间（有电子打卡机的可以直接导入数据，手动打卡钟的就只能手动输入时间了）

第二步，在整理好的上下班时间工作表的第一行依次输入姓名（即a1格）、日期（即B1格）、排班（即c1格）、上班时间（后面的以此类推）、下班时间、考勤结果（上班）、考勤结果（下班），标准下班时间、加班时间、加班时间修正等行名

第三步，如果有几个部门，且每个部门的上下班时间不一致，则最好按部门将员工分类在同一个工作表的不同工作薄里，

第四步，设置单元格的格式

1、凡是用时间表示的，都用“hh:

mm”的格式

2、记得在输入时间的时候关闭输入法

第五步，下面将进行具体的计算公式设置（以甲部门的a班为例，其他部门的不同班次，只是公式里的时间不同）

1、上班的考勤结果计算公式：

=iF（and（d2>=VaLUE（“07:

35”））=TRUE,”迟到”,iF（d2=0,”未打卡”,”“））

此公式的意思是，如果“d2”格，即上班时间列中的时间大于等于“7：

35”，则显示“迟到”，如果“d2”格中无数据，即为“0”的时候，则显示“未打卡”，以上两个条件都不符合的时候，则显示为空白，即正常上班的意思；

2、下班的考勤结果计算公式：

=iF（and（E2>=VaLUE（“16:

30”））=TRUE,”加班”,iF（E2=0,”未打卡”,”早退”））

此公式的意思是，如果“E2”格，即下班时间列中的时间大于等于“16：

30”，则显示为“加班”，如果“E2”格中无数据，即为“0”的时候，则显示“未打卡”，以上两个条件都不符合的时候，则显示为“早退”

3、加班时间的计算公式

=iF（（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））=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、统计“迟到“的计算公式

=coUnTiF（厅面!

F219:

F249,”迟到”）

这里是在同一个工作表中的不同工作薄中进行统计。

公式的意思是计算“厅面”工作薄里

“F219”到“F249”这个数据区域（31个单元格，代表31天）里“迟到”这个字符的数量有几个。

“事假”、“病假”、“例休”的计算公式一样，只需把“迟到”改成“事假”或“病假”或“休”即可，而且都是在“F”列中取数据；但是“例休”的数据要在“c”列即“排班”列中取数据。

2、统计“未打上班卡”和“未打下班卡”的计算公式

=SUm（coUnTiF（厅面!

F219:

F249,”未打卡”）-J6）

公式的意思是计算“厅面”工作薄里“F219”到“F249”这个数据区域（31个单元格，代表31天）里“未打卡”这个字符的数量有几个，再将计算结果减去“J6”格中的数据，“J6”格是指每位员工例休的天数。

因为按“考勤结果”的计算公式计算，员工在例休的时候，也会显示“未打卡”的记录。

“未打上班卡”在“考勤结果（上班）列中取数据，“未打下班卡”在“考勤结果（下班）列中取数据。

3、统计“出勤天数”的公式

=SUm（31-J6-d6-E6-F6）

这个公式比较简单，只是个合计公式，用总天数减去“事假”、“病假”和“例休”的天数即可。

要注意的是，在做统计公式的设置的时候，必须对应好每位员工打卡时间的单元格区域，否则就会出错。

到此，全部工作就算完成了，所有的设置只需一次，以后在统计其他月份的考勤的时候，只需将打卡时间重新整理，复制粘贴或者重新输入就可以了，但是在这样操作之前，应该先“另存为”一份，保持原始文件的可用性，并且可以留档。

总体来讲，整个过程还算满意，但得不到满分，主要有两个方面的问题：

1、不能自动识别不同班次选择不同的计算公式，还需要人工按照排班表，选择不同的计算公式进行计算，这会有些麻烦。

2、“加班时间修正”的计算公式还存在错误的地方，会使计算结果偏大，出现错误的时候，一般都会偏大0.5小时。

以上两个问题还需要进一步的学习才能进行修正，敬请期待，也请高手指教。

EXcEL制作考勤表，用公式计算出每天出勤工时！

浏览次数：

485次悬赏分：

0|解决时间：

20XX-9-2915:

41|提问者：

christine_QQ

制作工时考勤表，用什么公式可以自动计算出每天的出勤小时，

c1是上班时间，d1是下班时间，e1要得出一天的出勤小时，请告知公式，谢谢！

最佳答案

将c1,d1,e1的格式均设为时间，例如13:

30。

公式：

e1=d1-c1。

单位传统的考勤登记方式有工作量大、出错几率高、统计分析麻烦等特点，现在已经进入了信息时代，为何不用电脑来帮我们进行考勤呢？

用Excel20XX制作简单、快捷、准确的考勤表，你就能切身体会到信息时代的方便与快捷。

一、基本框架设置

首先要进行的工作当然就是新建工作簿，在工作表中输入姓名、日期，制订考勤表的基本框架。

1.启动Excel20XX，新建一个工作簿，命名为“20XX年员工考勤表”，在工作簿中建立12个工作表。

2.在当月工作表的a1、B1单元格中分别输入“姓名”、“日期”，在a2单元格中输入第1个员工的姓名，接着选择a2、a3单元格，然后单击“合并及居中”按钮将两个单元格合并为1个单元格，按照以上方法录入其他员工的姓名（如图1）。

图一

3.在单元格c1中输入“7-1”，程序将自动转成“7月1日”（这里假定考勤从7月1日开始）。

用鼠标点选单元格c1，并移动鼠标到单元格的右下角，待出现实心“十”字的填充柄后，按住左键拖动到需要的位置，这样在7月1日后便形成了一个日期序列。

4.单击日期序列最后的“自动填充选项”智能填充标记，在弹出来的菜单中点选“以工作日填充”项，程序便自动将星期六、日去掉，不记入考勤日期。

5.选中整个日期行，在“单元格格式”对话框中点选“数字”选项卡。

在“分类”列表中点选“自定义”项，在类型框中输入“d”日””，然后单击“确定”按钮。

二、录制宏

1.点选工作表中的任意一个单元格，然后单击“工具→宏→录制新宏”菜单项，进入“录制新宏”对话框。

在“宏名”框中输入准备录制的宏的名称--“返回当前时间”，“确定”后即进入宏的录制阶段（如图2）。

图二

2.输入公式“=now（）”，然后按回车键，接着单击“停止录制”工具栏中的“停止录制”按钮结束录制。

三、制作宏按钮

1.单击“视图→工具栏→绘图”菜单项，调出“绘图”工具栏，接着选择“绘图”工具栏中的“绘图→对齐→对齐网格”项。

2.单击“视图→工具栏→窗体”菜单项，调出“窗体”工具栏，然后单击“窗体”工具栏上的“按钮”，接着在单元格B2中拖动鼠标绘制一个按钮，Excel将自动打开“指定宏”对话框，点选其中的“返回当前时间”宏，并单击“确定”按钮（如图3）。

图3选择“返回当前时间”宏

3.单击按钮，将按钮上的文字更改为“上班”，并对文字大小、字体进行设置。

然后单击“绘图”工具栏上的“选择对象”按钮，并按住“ctrl”键，用鼠标将刚才制作的按钮拖到B3中，即将制作好的按钮复制到B3中，并将按钮上的问题更改为“下班”。

4.确认“绘图”工具栏上的“选择对象”按钮被选中，单击“下班”按钮，然后按住“Shift”键再单击“上班”按钮，将两个按钮同时选中。

单击“ctrl”键，用鼠标将选中的两个按钮拖动复制到B列的其余单元格中。

最后效果（如图4）。

图四

四、记录出勤

选中待插入上下班时间的单元格，单击同一行中的“上班”或“下班”按钮即可。

在单击“上班”、“下班”按钮时会返回很长一串数字，其实我们只需输入诸如“13:

30”这样的时间即可。

单击“格式→单元格”菜单项，在弹出来的“单元格格式”对话框中点选“数字”选项卡，在分类框中点选“时间”，在“类型”框中点选“13:

30”，最后单击“确定”即可。

要把出勤记录由公式结果转换成具体的时间数值，否则当公式被重新计算后，其结果将更改。

选中表中的所有数据单元格，单击右键选择“复制”，点选“编辑→选择性粘贴”菜单项，在“选择性粘贴”对话框中选中“数值”项，然后单击“确定”。

五、统计结果

1.在日期行之后的单元格中依次输入“迟到”、“早退”、“病假”、“事假”等需要统计的项目。

并将这几列中的单元格上下两两合并，使之对应于姓名行（如图5）。

图5创建相关统计项目

2.单击单元格Y2，然后键入公式“=coUnTiF（c2:

X2,”>8：

30”）”（这里假设上班时间为8:

30），并按回车键，z2单元格中便会出现选中员工所有迟于8:

30上班的工作日天数。

同理在z2单元格中输入公式“=coUnTiF（c3:

X3,”>17：

00”）”（假设下班时间为17:

00），并按回车键，z2单元格中便会出现选中员工所有早于17:

00下班的工作日天数。

3.单击aB2单元格输入公式“=coUnTiF（c2:

X3,”事假”）”，并按回车键确认，aB2单元

篇二：

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

用Excel做考勤统计的详细方法花了整整五天的时间，终于完成了。

虽然还不是很完善，但从零开始，边学边做，到最后终于可以用了，还是有那么点小小的成就感的。

下面将具体方法详述如下，欢迎提出建议：

上班安排：

假设某公司有甲、乙、丙、丁四个部门，以甲部门为例（因为其他部门的方法是一样的，只不过时间设置不同而已），其上下班时间安排是：

a班：

7：

30-16：

30

B班：

12：

00-21：

00

c班：

10：

00-19：

00

d班：

8：

30-17：

30

考勤规则：

上班时间后5分钟内打卡不算迟到，加班半小时以下不计加班。

首先，按名称整理好每个人的上下班的打卡时间（有电子打卡机的可以直接导入数据，手动打卡钟的就只能手动输入时间了）

第二步，在整理好的上下班时间工作表的第一行依次输入姓名（即a1格）、日期（即B1格）、排班（即c1格）、上班时间（后面的以此类推）、下班时间、考勤结果（上班）、考勤结果（下班），标准下班时间、加班时间、加班时间修正等行名

第三步，如果有几个部门，且每个部门的上下班时间不一致，则最好按部门将员工分类在同一个工作表的不同工作薄里，

第四步，设置单元格的格式

1、凡是用时间表示的，都用“hh:

mm”的格式

2、记得在输入时间的时候关闭输入法

第五步，下面将进行具体的计算公式设置（以甲部门的a班为例，其他部门的不同班次，只是公式里的时间不同）

1、上班的考勤结果计算公式：

=iF（and（d2>=VaLUE（“07:

35”））=TRUE,”迟到”,iF（d2=0,”未打卡”,”“））

此公式的意思是，如果“d2”格，即上班时间列中的时间大于等于“7：

35”，则显示“迟到”，如果“d2”格中无数据，即为“0”的时候，则显示“未打卡”，以上两个条件都不符合的时候，则显示为空白，即正常上班的意思；

2、下班的考勤结果计算公式：

=iF（and（E2>=VaLUE（“16:

30”））=TRUE,”加班”,iF（E2=0,”未打卡”,”早退”））

此公式的意思是，如果“E2”格，即下班时间列中的时间大于等于“16：

30”，则显示为“加班”，如果“E2”格中无数据，即为“0”的时候，则显示“未打卡”，以上两个条件都不符合的时候，则显示为“早退”

3、加班时间的计算公式

=iF（（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）））-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）+50px”width=“450px”alt=“excel函数考勤”title=“excel函数考勤”/>

（iF（minUTE（i2）>=30,0.5,0）”的计算结果显示。

这个公式是最复杂的，而且实际运用当中，也是有错误的。

还需要继续学习来修正。

第六步：

所有的计算到此都已经完成了，其他班次的只要在公式中将时间改一下就可以了，如B班，在计算上班的考勤结果时，将公式改成=iF（and（d2>=VaLUE（“10:

05”））=TRUE,”迟到”,iF（d2=0,”未打卡”,”“））就可以了。

第七步：

将各个班次的计算公式复制到每个人每天的那一行单元格中就可以了，计算结果会自动显示出来，这里要注意的是，在复制的时候要对应好单元格，否则也会产生错误，而且也会影响后面的考勤统计。

第八步，下面将进行统计公式的设置

1、统计“迟到“的计算公式

=coUnTiF（厅面!

F219:

F249,”迟到”）

这里是在同一个工作表中的不同工作薄中进行统计。

公式的意思是计算“厅面”工作薄里“F219”到“F249”这个数据区域（31个单元格，代表31天）里“迟到”这个字符的数量有几个。

“事假”、“病假”、“例休”的计算公式一样，只需把“迟到”改成“事假”或“病假”或“休”即可，而且都是在“F”列中取数据；但是“例休”的数据要在“c”列即“排班”列中取数据。

2、统计“未打上班卡”和“未打下班卡”的计算公式

=SUm（coUnTiF（厅面!

F219:

F249,”未打卡”）-J6）

公式的意思是计算“厅面”工作薄里“F219”到“F249”这个数据区域（31个单元格，代表31天）里“未打卡”这个字符的数量有几个，再将计算结果减去“J6”格中的数据，“J6”格是指每位员工例休的天数。

因为按“考勤结果”的计算公式计算，员工在例休的时候，也会显示“未打卡”的记录。

“未打上班卡”在“考勤结果（上班）列中取数据，“未打下班卡”在“考勤结果（下班）列中取数据。

3、统计“出勤天数”的公式

=SUm（31-J6-d6-E6-F6）

这个公式比较简单，只是个合计公式，用总天数减去“事假”、“病假”和“例休”的天数即可。

要注意的是，在做统计公式的设置的时候，必须对应好每位员工打卡时间的单元格区域，否则就会出错。

到此，全部工作就算完成了，所有的设置只需一次，以后在统计其他月份的考勤的时候，只需将打卡时间重新整理，复制粘贴或者重新输入就可以了，但是在这样操作之前，应该先“另存为”一份，保持原始文件的可用性，并且可以留档。

总体来讲，整个过程还算满意，但得不到满分，主要有两个方面的问题：

1、不能自动识别不同班次选择不同的计算公式，还需要人工按照排班表，选择不同的计算公式进行计算，这会有些麻烦。

2、“加班时间修正”的计算公式还存在错误的地方，会使计算结果偏大，出现错误的时候，一般都会偏大0.5小时。

以上两个问题还需要进一步的学习才能进行修正，敬请期待，也请高手指教。

篇三：

怎么用Excel做考勤表

第一步：

画表格

打开一张空白的EXcEL工作表，先按下图所示画出样子。

图中m1:

P1是合并单元格，用于填写“年”，S1:

T1是合并单元格，用于填写“月”，为了醒目设置成浅蓝色底纹。

第二步：

设置公式

为了让第二行中的“星期”能自动显示，需要设置公式，如下：

在d2单元格中输入公式=iF（wEEKdaY（daTE（$m$1,$S$1,d3）,2）=7,”日”,wEEKdaY（daTE（$m$1,$S$1,d3）,2））

这时就能看到d2单元格中出现一个表示星期的“日”字（这说明20XX年5月1号是星期日）。

公式含义：

先用daTE函数把m1格中的“年”、S1格中的“月”、d3格中的“日”组成一个电脑能识别的“日期”；再用wEEKdaY函数把这个“日期”变成星期所代表的数字。

wEEKdaY函数后面加了参数“2”，是为了让星期一显示为“1”，让星期二显示为“2”...让星期日显示为“7”。

由于我们不习惯把星期日叫成“星期7”，所以最后用iF函数做判断，把显示为“7”的自动改成“日”。

提示：

函数daTE与wEEKdaY在EXcEL自带的帮助中有详细的用法介绍，想了解它们的朋友可以参考。

为了方便我们中国人的习惯，还要把显示为阿拉伯小写数字的星期变成中文数字，即“

星期

1”变成“星期一”这种格式。

这就需要通过定义单元格格式来实现。

选中d2单元格，按鼠标右键“单元格格式”，在出现的格式窗口中选“数字”选项卡，在左侧的“分类”框中选“特殊”，在右侧的“类型”框中选“中文小写数字”，按“确定”退出。

这些完成后就可以用鼠标选中d2单元格，按住它右下角的“填充柄”拖动复制aH2单元格，效果如下图：

在ai单元格中可以通过公式显示当月的总天数，公式=daY（daTE（m1,S1+1,1）-1）

公式含义：

先由daTE函数“daTE（m1,S1+1,1）”得到本月的下一个月月初1号的日期。

本例中，本月是5月份，下一月的月初一号就是6月1日。

再用减1得到本月最后一天的日期，即5月31日，最后再由daY函数取出表示当月天数的“31”。

下面就是考勤录入方面了

第三步：

考勤符号

先设定一些考勤符号，放在aR列中，如图所示：

这些符号并非统一规定，可根据习惯及喜好来自己设定，也可用汉字来代表，总之自己看着习惯就行了。

怎么把这些符号方便快捷的输入到考勤表中d4:

aH33区域中呢？

我们采用下拉框的方法。

选中d4:

aH33区域，按上面工具栏中的“数据-有效性”，弹出有效性设置对话框，选“设置”选项卡，在“允许”中选“序列”，在“来源”中点击右端的折叠按钮，然后用鼠标在表格中选取aR1:

aR8区域，再次点击折叠按钮，回到有效性设置窗口，按“确定”退出。

完成后，在考勤表的d4:

aH33区域中任意一个单元格选中时，都会出现一个下拉框按钮，点击按钮就弹出下拉框，可用鼠标方便的选取要输入的考勤符号。

第四步：

考勤统计

考勤已经可以输入了，怎样统计每个人的出勤情况呢？

还是通过公式自动来统计。

先划出一块区域来做考勤统计，如下图红圈范围：

这个区域中要设置多个合并单元格，aK4:

aK5是合并的，aL4:

aL5是合并的......aP4:

aP5是合并的。

也就是每一个姓名对应的上下两行需要合并，这样方便把上午与下午统计在一个格中。

做完aL4:

aP5区域的合并操作后，选中区域右下角填充柄，按住鼠标左键向下拉动，一直到aP33单元格再松开鼠标左键，就能快速的把下面单元格分别也变成合并状态。

（其实就是复制了aL4:

aP5的样式）

由于第一个人的考