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

面对新的统计需求,很多人会突然变得迷茫,不知道该怎么办。如果涉及到的统计有1000多行数据,你会有一种哭的心态:什么时候下班?今天老菜鸟们通过考勤统计分析表的例子,分享一下他们对新的统计需求的解决方案:简化数据,寻找数据规律,制作辅助栏目。任何复杂的统计,只要采用这种方法,多半几分钟就能搞定。

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

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

练习课件到QQ群:264539405下载

报表可以更宏观的看到考勤纪律的执行情况,这是一个非常好的管理工具,但是负责做报表的合伙人却极为恼火。不为别的,就是不知道怎么做。无奈之下,我找到老菜鸟求助。

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

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

整个表格有20多列和1000多行。如果只手算的话,真的很难。如果你接到这个任务,你会怎么开始数数?

其实问题并没有看起来那么复杂。关键是要找到思路。决定出路,最终完成这张统计表没有使用任何高级函数,也没有像有些朋友猜测的那样使用VBA求解,只是使用了IF、COUNTIF、SUM、SUMIF几个常用函数。你真的想知道方法吗?

当你拿到数据的时候,不要盲目去做,先把数据整理好。就目前的总报表来看,数据源中对我们有用的列并不多。我们只需要保留这四列:部门、工号和上下班打卡结果:

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

看起来焕然一新,不是吗?删除无用数据后,需要分析具体的统计思路,同时在数据源中寻找可用的规律。很容易看出,每个工号对应的打卡结果是一个6行2列(12个单元格)的数据区:

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

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

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

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

在继续之前,我们需要澄清这里的六个统计数据之间的关系。报告是基于人数而不是次数,所以可以得出这些结论:

◎同一工号一周内无论缺卡多少次,都视为缺卡一次;

◎同一工号一周内无论请假多少次,都算作一人请假;

◎迟到早退规则同上;

◎对于同一个工号,无论一周内缺卡+请假+迟到+早退的人数是多少,只要大于0,就视为1人异常;

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

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

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

向右拉填充公式,四个异常都算。

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

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

这个公式的核心是COUNTIF。当符合条件的区域数大于0时,将得到1;否则,它将得到0。注意地域和条件的书写。$C2:$D7锁定列,G$1锁定行,这样在向右和向下拖动时就不必修改公式。

对于异常统计,只要以下四项之和大于0,则一人异常,公式为:= if(sum(G2:J7)>;0,1,0)。这个公式很简单。相信大家都能理解。

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

在这一点上,正常的统计更加困难:

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

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

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

啧啧!早退晚到的统计结果有误差。如何解决它们?

有些朋友可能想到过使用通配符。是的,它们是通配符,但是不用修改公式,只需修改标题:

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

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

数据源经过以上处理后,做最终的统计表就不再是问题了:

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

在职公式:=COUNTIF(数据来源!答:答,总结一下!B4)/6 .至于为什么要除以6,就不难理解了。

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

比例:=D4/C4

以下各栏都用SUMIF统计人数,用除法计算比例。

最后,总结一下:

今天的总汇报乍一看很难,你在日常工作中可能也会遇到类似的情况。解决方法是:先剔除无用数据,然后寻找数据规则,根据统计需要使用辅助列,最后通过一些基本操作,如累加、排序、归并、填充等,完成任务。

今天的分享侧重于理解解题过程,涉及的公式很简单。相信在动手实践中你会收获很多。