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生产计划,关注古哥计划!

256 快速匹配不同的采购量对应的结算量

255 WPS新函数案例:复杂的产品欠料运算

254 WPS新函数案例:灵活多变的万年日历

253 WPS新函数案例:指定工号快速筛选

252 WPS新函数案例:员工姓名与工号快速分离

251 WPS新函数案例:对客户快速分列并统计

250 WPS新函数案例:快速分类统计员工生日数

249 WPS新函数案例:多条件统计订单数

248 WPS新函数案例:快速统计记件人员工资

浙江省
浏览 612
收藏
9
分享
9 +1
7
+1
全部评论 7
 
幸福春
学习
· 山东省
回复
 
1231393578237
学习
· 四川省
回复
 
HC.旋
跟着老师学习
· 江苏省
回复
 
WPS_1703072781
学习
· 江苏省
回复
古哥计划
古哥计划

优秀创作者

感谢您的支持,您的支持是我坚持创作的动力。
· 浙江省
回复
 
亂雲飛渡
学习
· 广东省
回复
古哥计划
古哥计划

优秀创作者

感谢您的支持,您的支持是我坚持创作的动力。
· 浙江省
1
回复