教你1分钟整理好考勤数据打卡机导出的考勤表怎么统计 _经验分享

每个公司都离不开员工考勤，然而每次从考勤机导出考勤数据的时候，我们都会耗费大量的时间！今天和大家分享两个公式，考勤数据一分钟就可以整理好！
我们平时从考勤机导出的考勤数据，通过Excel打开后基本都是这样的格式：

文章插图
为了进一步统计考勤数据，希望将上面的这种格式整理为这样的效果：

文章插图
每人每天的打卡记录在同一行，并且根据上下班时间比对后标注出异常情况。
上下班时间规定如下：
上午上班时间8:00，上午下班时间12:00，下午上班时间13:30，下午下班时间17:30
要实现这种效果的转换，感觉是非常麻烦的一件事，其实只要掌握两个公式和一些基本的操作技巧，一分钟就可以完成，下面就来看看如何实现吧。
1、基础数据整理
在基础数据的右边添加几列，将时间分为上午上班、上午下班、下午上班和下午下班四列，并且标注出对应的时间：

文章插图
将卡号、人员和日期三列复制到右边对应的位置，然后使用“删除重复项”功能：

文章插图
点击确定后会删除重复的内容，每人每天只保留一行：

文章插图
接下来的任务就是将对应的打卡时间填入对应的位置，并且对异常数据不显示具体时间，只显示异常两个字。为了实现这个目的，需要使用两个公式来配合，下面先看第一个公式。
2、使用公式备注打卡时间
为了便于对打卡时间进行统计，首先要根据上下班时间进行备注，实现下图中的效果：

文章插图
根据上下班时间需要分为四种情况：
1、8点以前打卡视为上午上班；
2、12点以后打卡视为上午下班，考虑到还有下午上班这个因素，人为规定12点到12点30之间打卡为上午下班；
3、同理，人为规定13点到13点30之间打卡为下午上班；
4、17点30以后打卡为下午下班；
5、除此之外的时间打卡均为无效，显示空白。
E2单元格公式为：
=IF(D2<=$K$1,$K$2,””)&IF(AND(D2>=$L$1,D2<=$O$1),$L$2,””)&IF(AND(D2>=$P$1,D2<=$M$1),$M$2,””)&IF(D2>=$N$1,$N$2,””)，双击填充可实现图中的效果。
第一个IF为：=IF(D2<=$K$1,$K$2,””)
当d2（打卡时间）小于等于k1（上午上班时间）时，if函数的结果为k2（上午上班这四个字），否则返回空值；
第二个if为：IF(AND(D2>=$L$1,D2<=$O$1),$L$2,””)

文章插图
当d2（打卡时间）大于等于L2（上午下班时间）同时小于等于o1（人为规定下班打卡截止时间）时，if函数的结果为L2（上午下班这四个字），否则返回空值。
第三个if为：IF(AND(D2>=$P$1,D2<=$M$1),$M$2,””)

文章插图
当d2（打卡时间）大于等于p2（人为规定上班打卡开始时间）同时小于等于M1（下午上班时间）时，if函数的结果为M2（下午下班这四个字），否则返回空值。
第四个if为：IF(D2>=$N$1,$N$2,””)
当d2（打卡时间）大于等于N1（下午下班时间）时，if函数的结果为N2（下午下班这四个字），否则返回空值。
完成了备注信息之后，就该把对应的时间填入对应的区域内，这时候可以用一个公式右拉下拉就能完成时间的填充，一起来看看是哪个神奇的公式吧。
3、填充时间
在K2单元格输入公式：
=TEXT(SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K$2),”hh:mm:ss;;异常;”)
右拉下拉即可完成时间的填充。

文章插图
这个公式用到了两个函数，text和sumifs，来看看公式的原理吧。
sumifs函数的结构为sumifs（要求和的数据区域，条件区域1，条件1，条件区域2，条件2……），在今天的例子里我们用了三个条件，实际求和的是D列，三个条件分别是日期、卡号和备注信息，符合三个条件的数字都是唯一的，所以求和结果和引用结果是一致的。
因此公式为：
SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K$2)

文章插图
使用公式得到结果是这样的一些数字，因为在Excel中，日期和时间的本质都是数字，整数代表日期，而小数就代表时间，将上述区域单元格格式改为时间再看看效果，数字都变成了具体的时间，如下所示：

文章插图
实际上在进行了单元格格式设置后基本就达到目的了，为了完善显示效果，同时强制显示为时间格式，我们在sumifs外面再加了一个text函数，即使在常规格式下，也是按时间来显示的，同时0所在的位置显示为异常。

文章插图
简单解释一下text的用法，text（数据，指定的格式），在本例中，第二参数格式定义为时分秒的显示方式，字母h、m和s分别表示时分秒，都是两位数字显示。
格式代码中的分号，可以按照数据类型单独设置显示方式，text规定将数据分成四种：正数;负数;零;文本。本例中正数按照时间格式显示，负数和文本没有指定格式就不显示，而零显示为异常两个字。
【教你1分钟整理好考勤数据打卡机导出的考勤表怎么统计】 End.