大家好,今天给大家介绍一个老式打卡机的问题,数据源和模拟结果如下:这个小伙伴想算这个月的加班天数,以便发加班补贴。他们的加班是每天21:00以后到次日6点前的打卡算作加班卡(时间后面含有外勤两个字的不计入)。模拟结果如AN列所示。举个例子,刘烁绯这个人仅在4号和7号两天在21:00以后打卡,所以AN6=2。这个表就是上次展示的那个表,我已经把数据源手工调好了。这个问题相信很多人一看到数据源啥规律都找不到就想砸电脑了。大家不要急,我们把某个单元格取消自动换行看看。咦?这个竟然是有“空格”隔开的数据,而且时间都是五位。观察完数据以后,我想到一个思路:我们判断每天的最后一个时间(即去掉“空格”的最后五位)是不是在21:00以后到次日6点前,如果是就标记1,不是就标记0。我们可以用SUBSTITUTE函数把所有单元格中的“空格”(这个“空格”需要从单元格复制,不能打键盘上的空格键)去掉,然后用RIGHT函数取最后五位,转换为数值以后如果大于21:00或者小于6点,则记为1在EXCEL中,对逻辑值进行或运算可以用加法,故我们可以把这个思路写成(IFERROR(--RIGHT(SUBSTITUTE(D4:AH4,' ',''),5),0)>TIME(21,0,0))+(IFERROR(--RIGHT(SUBSTITUTE(D4:AH4,' ',''),5),0)注:套IFERROR函数是为了排除文本空的错误,因为文本空形成的错误不能和时间比较。由于空白单元格按照0计算,而0肯定小于6点,这样结果就会把空白单元格的个数计算进去。故我们要对上面的式子加一个并且的判断条件:D4:AH4<>'' =SUM(((IFERROR(--RIGHT(SUBSTITUTE(D4:AH4,' ',''),5),0)>TIME(21,0,0))+(IFERROR(--RIGHT(SUBSTITUTE(D4:AH4,' ',''),5),0)'')) 效果如下图:(这个公式需要按CTRL+SHIFT+ENTER三键结束)当然,也可以把SUM换成SUMPRODUCT(如文件的模拟结果AP列所示),这样按ENTER结束就可以了哦。根据这个思路,我们还可以计算每个人的上班总时长,有兴趣的可以思考一下,然后下载文件看模拟结果的AO列。写在最后:这种样式的考勤表处理数据比较麻烦。这个打卡机虽然说有BUG,但是因为人名均在第一列,所以这样的数据可以对某些计算结果进行透视汇总之类的操作。我们下面介绍的一个考勤问题,考勤表的样式是这样的:这样的考勤表比较适合打印分给每个员工,不太适合处理数据。后面我会花一个篇幅的时间写下关于这个表的一个问题,敬请期待。
|