• 技术文章 >专题 >excel

    实用Excel技巧分享:快速整理考勤数据!

    青灯夜游青灯夜游2022-07-26 18:53:55转载1032
    在之前的文章《Excel图表学习之创建有目标值的多系列多条件柱状图》中,我们了解了一个Excel图表教程,而今天我们分享一篇整理考勤数据的教程,就简单两个公式,学会后,不管你公司有多少人都不带怕的。

    EXCEL手机版(内含百种各行模版):点击查看

    考勤机几乎是每个公司都会使用的,而每到月初月末,整理考勤机导出的考勤数据,需要耗费我们大量的时间,今天教给大家两个公式,一分钟就可以整理出考勤数据。

    我们平时从考勤机导出的考勤数据,通过Excel打开后基本都是这样的格式:

    1.png

    为了进一步统计考勤数据,希望将上面的这种格式整理为这样的效果:

    2.png

    每人每天的打卡记录在同一行,并且根据上下班时间比对后标注出异常情况。

    上下班时间规定如下:

    上午上班时间8:00,上午下班时间12:00,下午上班时间13:30,下午下班时间17:30

    要实现这种效果的转换,感觉是非常麻烦的一件事,其实只要掌握两个公式和一些基本的操作技巧,一分钟就可以完成,下面就来看看如何实现吧。

    一、基础数据整理

    在基础数据的右边添加几列,将时间分为上午上班、上午下班、下午上班和下午下班四列,并且标注出对应的时间:

    3.jpg

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

    4.png

    点击确定后会删除重复的内容,每人每天只保留一行:

    5.png

    接下来的任务就是将对应的打卡时间填入对应的位置,并且对异常数据不显示具体时间,只显示异常两个字。为了实现这个目的,需要使用两个公式来配合,下面先看第一个公式。

    二、使用公式备注打卡时间

    为了便于对打卡时间进行统计,首先要根据上下班时间进行备注,实现下图中的效果:

    6.png

    根据上下班时间需要分为四种情况:

    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,"")

    7.jpg

    当d2(打卡时间)大于等于L2(上午下班时间)同时小于等于o1(人为规定下班打卡截止时间)时,if函数的结果为L2(上午下班这四个字),否则返回空值。

    第三个if为IF(AND(D2>=$P$1,D2<=$M$1),$M$2,"")

    8.jpg

    当d2(打卡时间)大于等于p2(人为规定上班打卡开始时间)同时小于等于M1(下午上班时间)时,if函数的结果为M2(下午下班这四个字),否则返回空值。

    第四个if为IF(D2>=$N$1,$N$2,"")

    当d2(打卡时间)大于等于N1(下午下班时间)时,if函数的结果为N2(下午下班这四个字),否则返回空值。

    完成了备注信息之后,就该把对应的时间填入对应的区域内,这时候可以用一个公式右拉下拉就能完成时间的填充,一起来看看是哪个神奇的公式吧。

    三、填充时间

    在K2单元格输入公式:

    =TEXT(SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K$2),"hh:mm:ss;;异常;")

    右拉下拉即可完成时间的填充。

    9.jpg

    这个公式用到了两个函数,text和sumifs,来看看公式的原理吧。

    sumifs函数的结构为sumifs(要求和的数据区域,条件区域1,条件1,条件区域2,条件2……),在今天的例子里我们用了三个条件,实际求和的是D列,三个条件分别是日期、卡号和备注信息,符合三个条件的数字都是唯一的,所以求和结果和引用结果是一致的。

    因此公式为:

    SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K$2)

    10.png

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

    11.png

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

    12.png

    简单解释一下text的用法,text(数据,指定的格式),在本例中,第二参数格式定义为时分秒的显示方式,字母h、m和s分别表示时分秒,都是两位数字显示。

    格式代码中的分号,可以按照数据类型单独设置显示方式,text规定将数据分成四种:正数;负数;零;文本。本例中正数按照时间格式显示,负数和文本没有指定格式就不显示,而零显示为异常两个字。

    关于text函数今天只是了解在本例的用法即可。

    小结:

    1、合理利用辅助列:考勤数据的整理历来都是比较麻烦的问题,一步到位往往非常困难,此时合理的利用辅助列就能将问题的难度一下子降低不少。

    2、公式不能万能的:考勤的基础数据就是时间,还有相关的规定共同组成了数据之间的逻辑关系,本例适合比较规范的情况,如果是多种班次并存的情况,就不能通过这种方法来实现了,还要结合每个人的班次对应的上下班时间来综合考虑,就需要考勤机的配套软件来完成数据的统计汇总。

    相关学习推荐:excel教程

    以上就是实用Excel技巧分享:快速整理考勤数据!的详细内容,更多请关注php中文网其它相关文章!

    声明:本文转载于:部落窝教育,如有侵犯,请联系admin@php.cn删除
    专题推荐:Excel
    上一篇:Excel图表学习之创建有目标值的多系列多条件柱状图 下一篇:Excel图表学习之通过案例,聊聊怎么绘制量筒式柱形图
    手机EXCEL

    相关文章推荐

    • 【活动】充值PHP中文网VIP即送云服务器• 实用Excel技巧分享:带你搞懂“绝对引用”和“混合引用”• Excel将多个工作表的函数计算结果转换为数值• 实用Excel技巧分享:三招隔行填色,招招精彩,逼格满满!• 实用Excel技巧分享:原来统计加班费如此简单!• 实用Excel技巧分享:制作一张员工信息动态查询表!• 实用Excel技巧分享:这5个快捷键,你都会用了吗?
    1/1

    PHP中文网