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

wufei123 2024-05-24 阅读:59 评论:0
在之前的文章《excel图表学习之创建有目标值的多系列多条件柱状图》中,我们了解了一个excel图表教程,而今天我们分享一篇整理考勤数据的教程,就简单两个公式,学会后,不管你公司有多少人都不带怕的。 考勤机几乎是每个公司都会使用的,而每到...

在之前的文章《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=$L$1,D2=$P$1,D2=$N$1,$N$2,""),双击填充可实现图中的效果。

第一个IF为:=IF(D2

当d2(打卡时间)小于等于k1(上午上班时间)时,if函数的结果为k2(上午上班这四个字),否则返回空值;

第二个if为:IF(AND(D2>=$L$1,D2

7.jpg

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

第三个if为:IF(AND(D2>=$P$1,D2

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技巧分享:快速整理考勤数据!的详细内容,更多请关注知识资源分享宝库其它相关文章!

版权声明

本站内容来源于互联网搬运,
仅限用于小范围内传播学习,请在下载后24小时内删除,
如果有侵权内容、不妥之处,请第一时间联系我们删除。敬请谅解!
E-mail:dpw1001@163.com

分享:

扫一扫在手机阅读、分享本文

发表评论
热门文章
  • BioWare埃德蒙顿工作室面临关闭危机,龙腾世纪制作总监辞职引关注(龙腾.总监.辞职.危机.面临.....)

    BioWare埃德蒙顿工作室面临关闭危机,龙腾世纪制作总监辞职引关注(龙腾.总监.辞职.危机.面临.....)
    知名变性人制作总监corrine busche离职bioware,引发业界震荡!外媒“smash jt”独家报道称,《龙腾世纪:影幢守护者》制作总监corrine busche已离开bioware,此举不仅引发了关于个人职业发展方向的讨论,更因其可能预示着bioware埃德蒙顿工作室即将关闭而备受关注。本文将深入分析busche离职的原因及其对bioware及游戏行业的影响。 Busche的告别信:挑战与感激并存 据“Smash JT”获得的内部邮件显示,Busche离职原...
  • 闪耀暖暖靡城永恒怎么样-闪耀暖暖靡城永恒套装介绍(闪耀.暖暖.套装.介绍.....)

    闪耀暖暖靡城永恒怎么样-闪耀暖暖靡城永恒套装介绍(闪耀.暖暖.套装.介绍.....)
    闪耀暖暖钻石竞技场第十七赛季“华梦泡影”即将开启!全新闪耀性感套装【靡城永恒】震撼来袭!想知道如何获得这套精美套装吗?快来看看吧! 【靡城永恒】套装设计理念抢先看: 设计灵感源于夜色中的孤星,象征着淡然、漠视一切的灰色瞳眸。设计师希望通过这套服装,展现出在虚幻与真实交织的夜幕下,一种独特的魅力。 服装细节考究,从面料的光泽、鞋跟声响到裙摆的弧度,都力求完美还原设计初衷。 【靡城永恒】套装设计亮点: 闪耀的绸缎与金丝交织,轻盈的羽毛增添华贵感。 这套服装仿佛是从无尽的黑...
  • boss直聘怎么取消面试预约 boss直聘上面试爽约了会怎么样(面试.爽约.预约.取消.boss.....)

    boss直聘怎么取消面试预约 boss直聘上面试爽约了会怎么样(面试.爽约.预约.取消.boss.....)
    求职宝典:boss直聘面试技巧及取消预约方法 各位求职者注意啦!在Boss直聘上,随意取消面试预约会留下爽约记录,影响后续求职!本文将指导您如何避免爽约,以及如何取消已预约的面试。 如何取消Boss直聘面试预约? 打开Boss直聘APP,进入“我的”页面。 点击“待面试”,查看面试日程。 选择需要取消的面试,点击“取消面试”按钮即可。 Boss直聘面试爽约的后果? 爽约行为会在HR端留下记录,影响您的求职成功率。其他HR也能看到您的不良记录,所以务必重视面试预约。...
  • 蛋仔派对2025最新皮肤兑换码汇总 最新皮肤兑换码一览(兑换.皮肤.最新.派对.汇总.....)

    蛋仔派对2025最新皮肤兑换码汇总 最新皮肤兑换码一览(兑换.皮肤.最新.派对.汇总.....)
    蛋仔派对2025最新皮肤兑换码大放送!游戏内新增多款皮肤兑换码,包含最新、福利和通用三种类型,助你轻松获取精美奖励! 赶紧来看看如何兑换吧! 兑换码列表: 最新兑换码: ccewndj4k4k、cdkqdfm4fh、peetnmp4ef、cdxymk8f67 福利兑换码: cca863ywtfa、eggy2310am、eggy2311gz、eggyeggy9wz 通用兑换码: pec74dkcty、jsrqkrrjmh、cd3wt7wrph、ccepn7d8cjf...
  • python怎么调用其他文件函数

    python怎么调用其他文件函数
    在 python 中调用其他文件中的函数,有两种方式:1. 使用 import 语句导入模块,然后调用 [模块名].[函数名]();2. 使用 from ... import 语句从模块导入特定函数,然后调用 [函数名]()。 如何在 Python 中调用其他文件中的函数 在 Python 中,您可以通过以下两种方式调用其他文件中的函数: 1. 使用 import 语句 优点:简单且易于使用。 缺点:会将整个模块导入到当前作用域中,可能会导致命名空间混乱。 步骤:...