[excel也可以很好玩：职场故事版]第8章excel为考勤统计提速

《[excel也可以很好玩：职场故事版]第8章excel为考勤统计提速》由会员分享，可在线阅读，更多相关《[excel也可以很好玩：职场故事版]第8章excel为考勤统计提速（18页珍藏版）》请在金锄头文库上搜索。

1、菜鸟的 Excel 智慧职场路174第 8 章Excel 为考勤统计提速2012 年春节马上过完了，结束休假，马上又要开始正常的上下班作息时间了，春节大 假休息了这么长时间，李璐对上班真有点期待了。对于新的一年，李璐有很多计划，首先 当然是做好本职工作，然后继续钻研 Excel。李璐感觉 Excel 的功能真的太强大了，当然也 就很难学，例如有几百个函数，可自己掌握的还只是常用的那几个。8.1新年新制度新年复工的第一天，所有后勤管理人员召开大会。 在大会上，老总对人资部首先安排了一个工作：就是采购指纹打卡机，代替原来的纸 卡打卡机。原来，春节前很多同事忙着办年货，上下班打卡请同事帮忙，人资部忙

2、着年底 绩效考评工作，也没有监管到打卡的事。结果被老总多次发现代打卡的情况。因此，要求 换为指纹打卡机，从 2 月份开始使用指纹打卡。 散会后回到办公室，张新开始安排工作： “李璐，你抓紧时间把指纹打卡机的申购单 填好，我签好字后就去采购。只有三天时间了，争取今天下午就买回来。时间紧，手续就 简化一下。 ” 李璐马上打开电脑，先上网查询了一下指纹打卡机的品牌、型号，了解指纹打卡机的 特点。指纹打卡机通过每位员工的指纹签到签退，无须卡片，可解决代打卡问题，也可减 少每个月的卡片消耗。特别对人资部的人员来说，减少了每月制作卡片的工作，也不再需 要专人根据卡片统计员工上下班时间，可为人资部节省不少时

3、间。李璐想，早就该用指纹 考勤了哟，有了先进技术不用，还一直用那台卡片打卡机。 李璐在网上看好型号，给供应商打电话，还好，供应商处有货，下午可以送来。 收到打卡机后，李璐根据说明书的介绍开始采集指纹，每个员工左右手各采集两个指 纹，如果一只手受伤不能打指纹的话，还可以用另一只手的指纹。这样，一般情况下就不 会影响正常的考勤了。另外，针对有特殊情况不能打卡的员工，仍然采用以前的考勤异常 登记表进行手动登记。 在 1 月 31 日这天，终于将所有同事的指纹都采集完了，李璐将打卡机挂在前台原来 放纸卡打卡机处，并请前台接待人员提醒上、下班的同事别忘了打卡。8.2提取打卡数据经过一个月的指纹打卡，3

4、月初就要从打卡机中提取数据进行统计了。想起原来统计第 8 章Excel 为考勤统计提速175打卡卡片时的情况：桌上放着一大堆卡片，要逐张检查每个人的打卡时间，然后再登记到 电脑中，总是要花几天时间才能弄完。现在用指纹打卡，终于不用再逐张查看卡片了，李 璐马上觉得眼睛都轻松了不少。李璐想：高科技就是好啊，可解放我的双眼。 现在好了，把打卡机的数据读入电脑，一切 OK。8.2.1导出数据到 Excel李璐马上拿出打卡机的光盘，将程序安装在电脑中。接上打卡机，导入数据。一切都 很顺利。 可是，这个考勤管理软件也太复杂了点吧，要打印出每个员工的考勤情况还需要这么 多的步骤进行设置啊。 李璐决定还是找外

5、援，马上呼叫马波。 马波： “考勤机软件我也没接触，不熟悉，你可以看说明书啊。 ” 李璐： “可是，说明书说也没介绍清楚，又没有操作案例。有没有什么简便的方法？” 马波： “那就还是用 Excel 啊，这个我熟悉。考勤管理软件应该有将数据导出为 Excel 的功能。 ” 李璐： “好吧，我试试。 ” 还好，考勤管理软件有一个导出为 Excel 的功能，李璐将数据保存为 Excel 文件“2 月考勤数据.xlsx” ，打开该文件如图 8-1 所示。图 8- 1导出的考勤数据李璐一看图 8-1 所示的数据就懵了，这个表格看起来有 8 列，可是只有“姓名”和“日 期时间”列是有用的，其他都没什么用。

6、并没有想像中的上班、下班打卡时间，所有打卡菜鸟的 Excel 智慧职场路176时间都保存在“日期时间”这一列中，别说判断迟到、早退，连哪个是上班打卡，哪个是 下班打卡的数据都要去逐个分辨啊。 李璐把导出的考勤数据发给马波，让他帮助分析下，看该怎么处理， 生成考勤统计表。8.2.2分析考勤数据等一会，马波说： “我看了一下打卡的数据，发现有几个方面的问题需要解决。 ” 首先，上班打卡和下班打卡的分辨。 其次，漏打问题，如某个人一天一次都没打，则表中没有数据。 第三，多打问题，如某个人在上班时连续打了两次，表中就有两个数据。 李璐： “我们是早上 9 点上班，下午 17:30 下班，可不可以这样：

7、在表格中新建两列， 一列保存上班打卡数据，一列保存下班打卡数据。用一个函数对打卡的时间进行判断，当 打卡时间在 9:30 分之前的，将其放在上班打卡这一列，当打卡时间在 17:00 之后的，将其 放在下班打卡这一列。 ” 说着，李璐就开始在工作表上操作起来。 （1）单击选中 I1 单元格，输入“上班打卡” ，选中 J1 单元格，输入“下班打卡” ， 如 图 8-2 所示。图 8- 2增加表头字段（2）单击选择 I2 单元格，在其中输入以下公式：=IF(TIME(HOUR(D2),MINUTE(D2),SECOND(D2)TIME(17,0,0),D2,“) （4）选中 I2 和 J2 单元格，

8、向下拖动填充柄，即可将所有数据分为上班打卡和下班打 卡数据了，如图 8-3 所示。第 8 章Excel 为考勤统计提速177图 8- 3分列数据马波： “你这是一种方法，不过，当打卡的时间在 9:30 至 17:00 之间的数据就没办法 归类在上班打卡或下班打卡中了，如图 8-3 中第 13、14 行就是这种情况。其实可根据公司 的情况，定一个时间作为分界线，在此时间之前打卡算上班打卡，在此时间之后打卡，算 下班打卡。这样就不会漏掉数据了。 ” 于是，李璐又修改公式将 13:00 作为上、下班打卡的分界线，重新将公式向下拖动复 制，得到如图 8-4 所示的结果。图 8- 4修改公式后分列数据菜

9、鸟的 Excel 智慧职场路1788.3用 Excel 判断迟到早退李璐： “这样将数据分列后，就可根据上班打卡或下班打卡列中的数据判断 迟到或早退了。 ” 马波： “恩，迟到、早退可以方便地判断了。 ” 接着李璐又开始在工作表中进行操作。 （1）在工作表右边增加了两列，分别记录迟到和早退，如图 8-5 所示。图 8- 5增加表头字段（2）选中 K2 单元格，输入以下公式：=IF(I2=“,“,IF(TIME(HOUR(I2),MINUTE(I2),SECOND(I2)TIME(9,0,0),1,“) 以上公式中首先用 IF 函数判断，如果 I2 单元格为空，则在当前单元格（K2）也显示 空，

10、否则就判断 I2 单元格的日期，如果大于 9:00，则为迟到，在当前单元格（K2）显示 数字 1，否则显示空。 （3）类似地，在 L2 单元格输入以下公式：=IF(J2=“,“,IF(TIME(HOUR(J2),MINUTE(J2),SECOND(J2)+即可完成数组公式的定义， 这时 Excel 自动在公式两边加上大括号“” ，如图 8-8 右图所示。图 8- 8用数组公式进行算术运算李璐： “这样操作还没有直接给每个单元格定义公式方便哟。 ” 马波： “从这个例子来看是这样的，但是在实际应用中的案例都不是这么简单的，在 很多情况下用简单公式解决不了问题，就必须使用数组公式。 ”2比较运算马

11、波： “再看一个比较运算的例子，如图 8-9 所示，通过以下的数组公式可比较对应单 元格的值。 ”第 8 章Excel 为考勤统计提速181=A1:A3+组合键可快速到表格的最后一行。最后一行行号是 390。 ” 马波： “好，记住这几个数据，在定义公式时需要使用。下面是具体操作步骤。 ” （1）在“考勤统计表”中单击选择 B2 单元格。 （2）在 B2 单元格中输入以下公式：=IF(SUMPRODUCT(2 月!$C$2:$C$390=$A2)*(2 月!$D$2:$D$390=B$1), IF(SUMPRODUCT(2 月!$C$2:$C$390=$A2)*(2 月!$D$2:$D$390

12、=B$1)* (2 月!$E$2:$E$39017.5/24),“正常“,“早退“),“迟到“),“旷工“) （3）这个公式看起来很长，下面分步给你讲解一下。首先看下面这一部分：SUMPRODUCT(2 月!$C$2:$C$390=$A2)*(2 月!$D$2:$D$390=B$1) 这是使用 SUMPRODUCT 函数的多条件计数功能，$A2 单元格保存的是员工的姓名， B$1 单元格保存的是考勤的某一天。上面这部分函数的功能是根据员工姓名、考勤日期进 行计数，若 2012/2/1 日这天“张新”没有打卡数据，则计数返回为 0，否则返回大于 0 的 值。这里的返回值供 IF 函数进行判断，若

13、返回值为 0，则输出“旷工” ，若返回值不为 0， 则进行迟到、早退、正常这 3 种状态的判断。由于判断这 3 种状态的代码比较长，先把这 3 种状态统称为“上班” ，则 B2 单元格的公式可简化为以下情况：=IF(SUMPRODUCT(2 月!$C$2:$C$390=$A2)*(2 月!$D$2:$D$390=B$1), “上班“,“旷工“) （4）类似地，在“上班”状态中，又通过 IF 函数进行判断，这部分代码如下：SUMPRODUCT(2 月!$C$2:$C$390=$A2)*(2 月!$D$2:$D$390=B$1)* (2 月!$E$2:$E$39017.5/24) 这里的 17.5

14、/24 表示 17:30 分。 （6）这个公式经过这几层嵌套，最终完成了复杂的判断。这个公式的整个流程如图 8-21 所示。 （7）在 B2 单元格输入公式后得到如图 8-22 所示的计算结果，根据计算结果可看出， “张新”在 2012/2/1 日这天上班是“正常”状态。第 8 章Excel 为考勤统计提速189当天有考勤数据?旷 工9:0 0前有考勤数据?迟 到1 7:3 0后有考勤数据?正 常早 退否否否是是是图 8- 21考勤状态判断流程图 8- 22公式及计算结果（8）向右拖动 B2 单元格的填充柄到 V2 单元格，可看到“张新”当月的考勤状态就 生成完成了，如图 8-23 所示。图

15、8- 23拖动复制一行公式菜鸟的 Excel 智慧职场路190（9）确保 B2:V2 这部分单元格区域处于选中状态，向下拖动选中区域的填充柄到第 13 行，得到如图 8-24 所示的结果。图 8- 24向下拖动复制公式到此，就完成了每一天的考勤状态的统计。 对于这种用拖动的方式快速复制公式，是由于在 B2 单元格定义公式时使用了单元格 的绝对引用、混合引用。如对于姓名（A 列） ，使用的是$A2 的方式，表示不管公式复制 到哪里都使用 A 列的数据，只是行会随公式的位置而变化。这种混合引用需要多用心去体 会一下。 李璐： “这几种引用方式是容易把人搞昏，有这个公式，我正好可以参照学习一下单 元

16、格的引用方式。 ”8.6.5汇总考勤数据马波： “有了上班的这 4 种状态，要汇总每位员工的出勤数据就很简单了。 ” 李璐： “恩，这个我会了，我先把这些数据汇总出来，再去研究上面这个公式。 ” （1）在 X1、Y1、Z1 单元格分别输入“迟到” 、 “早退” 、 “旷工” 。 （2）在 X2 单元格定义如下公式：=COUNTIF(B2:V2,“迟到“) （3）在 Y2 单元格定义如下公式：=COUNTIF(B2:V2,“早退“) （4）在 Z2 单元格定义如下公式：=COUNTIF(B2:V2,“旷工“) （5）选择 X2:Z2 单元格区域，向下拖动填充柄复制公式，最后得到如图 8-25 所示的 汇总数据。第 8 章Excel 为考勤统计提速191图 8- 25考勤汇总数据从最终汇总的数据看，这个月忘记打卡的人很多，可能很多同事还不习惯这种指纹打 卡方式。不过，这只是打卡机中的数据统计的结果，还要和考勤异常登记表中的登记情况 进行对比，可能有些同事由于出差、公休等原因没有