257 一招搞定请假时间的区间转换及人数统计
优秀创作者
某工厂的人事部门根据员工的请假单已经登记汇总到一张表格,里面包含请假类型,请假的开始时间和结束时间,现在的需求就是需要把表1转换成表2的显示方式,并统计实际每天的请假人数的汇总人数。
如下图图1所示:
图1
需求分析
根据需求初步判定是一个一维报表转二维报表显示的需求,只是其中的难点就是请假时间的区间范围显示。也就是表1里面员工姓名为里洲的请假时间是1月4日到1月8日,需要转换成一个区间,分别显示1月1日、2日、3日、一直到8日的效果,并在这里返回请假类型,是事假。
要实现这种区间的显示效果,关键点就是日期范围,有了日期范围后可以根据这个范围来扩展,当然这里只是常规思路,其实这个需求可以用一个冷门函数VDB来实现。而且简单方便,一键转换
如下图2所示:
图2
日期区间
一维报表转二维报表的话,首先需要的就是一个水平方向的标题,也就是需要把日期转换成水平方向并动态展开。生成类似这样的标题可以用函数SEQUENCE来生成,关键点是判断最小日期和最大日期。有了这个就可以计算出区间天数了。
最大日期:MAX(E4:E10),这里是1月10日
最小日期:MIN(D4:D10),这里是1月9日
区间:MAX(E4:E10)-MIN(D4:D10)+1,也就是列的数量;10列;
开始日期:MIN(D4:D10),也就是最小日期
增量:不录入的话就是1
根据上面的结果套入函数中:
=SEQUENCE(,MAX(E4:E10)-MIN(D4:D10)+1,MIN(D4:D10))
效果如下图3所示:
图3
转换二维
有了水平方向的标题后,就开始转二维报表了,为了方便大家理解,分步写公式,先录入公式:
=VDB(0,0,E4:E10,D4:D10,F3#)
公式释义:
动态运用了VDB函数中的参数“截止日期”,这是一个范围数组,也就是1月1日到1月10日。
因为原值和残值都是0,所以在起始时间(D列)到折旧期限(结束时间)中符合截止时间的日期,如没有的话就返回错误值。如1月1日不在范围内返回错误值。
效果如下图4所示:
图4
有了这一步就相对简单了,从上图中可以看到,只需要把0替换成年假类型,错误值替换为空就可以了。
录入函数:
=T(VDB(0,0,E4:E10,D4:D10,F3#))&C4:C10,把0通过函数T转换成数值后就不会显示了,再连接类型,就实现了把0替换成类型了。
录入函数:
=IFERROR(T(VDB(0,0,E4:E10,D4:D10,F3#))&C4:C10,"")
把错误值返回空值。
效果如下图5所示:
图5
统计请假人数
返回了二维的区间报表后,就非常容易统计请假人数了,在上方向录入统计函数:
=ROWS(FILTER(F4:F10,F4:F10<>""))
具体函数解释如下:
FILTER函数的作用是筛选出满足特定条件的单元格。在这里,它筛选了F4到F10范围内的所有单元格,条件是这些单元格的值不能为空(<>""表示不等于空字符串)。
ROWS(...):这个函数用来计算括号内所包含的单元格的行数。在这个例子中,它计算了由FILTER函数筛选出的非空单元格所占的行数。
因此,整个函数=ROWS(FILTER(F4:F10,F4:F10<>""))的作用是计算F4到F10范围内非空单元格的行数,并将结果返回。
我是古哥:
从事制造行本业20年,在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化,柔性化计划运营管理专家,擅长通过企业流程优化规范,企业管理、导入计划运营提升企业效率;对提高企业准时交货率,降低企业库存,输出智能制造人才有丰富的经验。学习PMC生产计划,关注古哥计划!
优秀创作者
优秀创作者