当前位置:首页 > 办公 > Excel教程 > 正文内容

教你几分钟搞定的考勤数据!

酷网1个月前 (10-22)Excel教程30

面对新的统计需求,很多人会一下变懵,不知如何办。如果涉及的统计有一千多行数据,哭的心思都有了:什么时候才能下班哟!今天老菜鸟通过考勤统计分析表实例分享自己面对新统计需求的解决方法:简化数据、找数据规律、做辅助列。任何复杂的统计,只要采取这样的方法,大多几分钟就会搞定。

考勤管理,是企业劳动纪律管理的最基本工作,公司领导要求人力资源每周按部门汇总一份考勤打卡情况通报,格式如下:

 

练习课件到QQ群:264539405下载

通报表可以更为宏观的看到考勤纪律的执行情况,是非常好的一个管理工具,但是负责制作报表的伙伴却是异常烦恼。不为别的,就是不知道该如何下手,无奈之下,找到老菜鸟求助。

我们先来看看从考勤软件导出的数据源吧:

 

整个表有二十多列,一千多行,如果仅靠手工统计,难度确实不小。如果是你接到这个任务时,会如何着手开始统计呢?

其实问题并没有看上去那么复杂,关键是找到思路。思路决定出路,最终完成这个统计表并没有用什么高大上的函数,也不是像有些伙伴猜的那样,用VBA去解决,只是用了几个常用的函数IF、COUNTIF、SUM和SUMIF就搞定了。是不是很想知道方法呢?

拿到数据,切忌盲目动手,而是首先整理数据。就当前通报表来说,数据源中对我们有用处的列并不多,只需要保留部门、工号、上下班的打卡结果这四列即可:

 

看上去清爽了很多吧?删去了无用数据之后,就需要分析具体的统计思路,同时在数据源中寻找可以利用的规律。很容易看到,每个工号对应的打卡结果都是6行2列(12个单元格)的一个数据区域:

 

我们需要根据这12个单元格中的信息进行分类统计,按照要求分为正常和异常两类。其中异常包括了:缺卡、请假、迟到和早退四种情况,只要没有异常即可视为正常。

根据统计要求添加辅助列。辅助列的作用就是简化问题的难度系数,在这个问题中我们可以这样设置:

 

在继续往下前,需要理清楚这里统计的6个数据的关系。通报表是按人数而非次数统计的,因此可以得出这些结论:

◎同一工号一周内不管缺卡多少次,都算缺卡1人;

◎同一工号一周内不管请假多少次,都算请假1人;

◎迟到、早退规则同上;

◎同一工号,一周内不管缺卡+请假+迟到+早退有多少人,只要大于0,都算异常1人;

◎同一工号,一周内正常人数等于1-异常。

很多时候我们是不建议使用合并单元格的,因为合并单元格会带来很多不方便。但是这个例子中,每6行数据(一个工号的数据)统计出一行结果,所以使用合并单元格会更方便。分别把E2:E7、F2:F7、G2:G7、H2:H7、I2:I7、J2:J7合并,然后在G2中输入公式:

=IF(COUNTIF($C2:$D7,G$1)>0,1,0)

右拉填充公式,4项异常都进行了统计。

 

从结果来看,这四项异常数据的统计结果是正确的。

这个公式的核心是COUNTIF,当区域中符合条件的个数大于0时,得到1,否则得到0。注意区域和条件的写法,$C2:$D7锁定了列,G$1锁定了行,这样公式在右拉和下拉的时候就不用再去修改了。

异常的统计,只要后面四项之和大于0就算异常1人,公式为:=IF(SUM(G2:J7)>0,1,0)。这个公式很简单了,相信大家都能明白。

 

到这一步,正常的统计更没什么难度:

 

选中统计好的6个单元格,双击J2单元格的右下角,公式会自动向下填充至数据的最后一行,可以浏览一下结果:

 

咦!早退和迟到的统计结果出现了错误,怎么解决?

有些朋友可能想到了用通配符,没错,就是通配符,不过不用修改公式,只需要将表头修改一下就好了:

 

看明白了吧,在表头“迟到”和“早退”的两边都加上*,统计结果就正确了。

数据源经过以上处理以后,要做出最终的统计表,已经完全不是问题了:

 

在职人数公式:=COUNTIF(数据源!A:A,汇总!B4)/6。至于为什么要除以6,不难理解。

正常打卡人数:=SUMIF(数据源!A:A,B4,数据源!E:E),SUMIF最基本的用法哦;

占比:=D4/C4

后面的几列都是用SUMIF统计人数,用除法计算占比。

最后总结一下:

今天的通报表乍一看很棘手,平时工作可能也会遇到类似的情况。解决的办法就是:首先排除无用的数据,然后是寻找数据规律,根据统计需要使用辅助列,最后通过一些基本的操作,例如累计、排序、合并,还有填充等,完成任务。

今天的分享重在体会问题的解决过程,涉及到的公式都非常简单,相信大家在动手练习的过程中一定会有很多收获的。

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/40284.html

分享给朋友:

相关文章

条件格式666666啊. | 四大小朋友生存手册

条件格式666666啊. | 四大小朋友生存手册

嘿嘿我们的四大小朋友生存手册系列又回来了! 欢迎小伙伴们投稿,有出路君大红包准备! 以下我想了几个选题,有认领想投稿的小伙伴来吱一声呀。救救孩纸把。 1 银行询证函要怎么发。 2 应收应付往来科目我该做些什么。 3 存货...

7 个表格函数技巧,让你少做 80% 重复工作

7 个表格函数技巧,让你少做 80% 重复工作

日常办公时,我们经常会用表格处理数据。虽然能掌握基础的操作,但是涉及「函数」时,很多人就开始头疼了。 毕竟在实现精通 Office 的路上,函数一直都是绕不过的一道坎。 今天,WPS君就给大家整理了「常用的 7 个函数」...

MAX、MIN与IF结合,统计众多部门中同一部门数据最大值与最小值

MAX、MIN与IF结合,统计众多部门中同一部门数据最大值与最小值

问题来源 一位做电商数据分析的朋友说,他要对所管理的六个仓库的销售额进行对比统计,统计出每个仓库的最高与最低销售额。他有几万行的数据,简化到下面几行,以方便清楚统计公式。 关键操作公式实现 在E2单元格输入公式:“=M...

Excel怎么输入部分相同的数据?Excel输入部分相同数据

Excel怎么输入部分相同的数据?Excel输入部分相同数据

Excel怎么输入部分相同的数据?Excel输入部分相同数据原标题:Excel怎么输入部分相同的数据?,Excel输入部分相同数据,Excel输入部分相同数据的方法技巧是什么?  1.自动填充  如果数据含有固定内容,但同时又是有规律的数据...

INDIRECT函数汇总各仓库的合计到销售总表

INDIRECT函数汇总各仓库的合计到销售总表

问题情境 如下样表: 分仓库数据表中,既有销售额明细,又有合计。 要求:只汇总各仓库的合计到“销售合计”表中。 公式实现 在销售合计”表B2单元格输入公式: =MAX(INDIRECT(A2&”!B...

Excel中如何使用数据透视表快速汇总?

Excel中如何使用数据透视表快速汇总?

  在我们利用Excel表格办公时,经常会需要对表格的数据进行汇总,小伙伴们不要再去用计算器一个一个计算了,今天就教大家一个简便快速的汇总方法。就是利用Excel表格中的数据透视表,就...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。