利用XLOOKUP快速实现培训未参加人员的扣分 NO 279

古哥计划
古哥计划

2024年03月优秀创作者

某工厂的培训专员的一张表格,分别有表1,所有员工培训扣分表;表2员工未出勤明细表。现在需要根据员工未出勤表快速进行扣分。也就是说只要没有出勤的员工都需要在表1员工培训明细表中扣3分。因员工数量比较多,所以希望可以用公式快速扣分。

源数据员工数量比较大,截取部分数据。

效果如下图1所示:

图1

需求分析

上面的需求其实就是一个查找与引用的需求,把表2员工未出勤明细中对应的每一周的人员去表1员工统计表中查找,找到了的就是没有出勤的人员,找不到的就是出勤了。

这里就需求加一个IF判断,如果找到了的话就扣3分,未找到就显示为空值。这个思路本身没有问题,就是公式比较长,需要加上IF判断,判断的同时还需要对返回结果再作一次判断,如返回的是姓名,还需要加上一个“<>”””判断。

有没有更加简单的方法呢?当然有,用XLOOKUP加上内存数组来就可以实现。

查找与引用

前面还是直接用XLOOKUP来写,XLOOKUP有6个参数,前面3个参数是必须录入的。

录入函数:

=XLOOKUP(B3:B20,I3:I7,N3:N7)

函数释义:

第1参数:B3:B20,这是要查找的值,即在这个区域内查找匹配项,这里用的动态数组的写法,可以实现批量查找。

第2参数:I3:I7:这是查找的数组,即根据这个区域中的值来确定查找的目标。也就是未出勤名单。

N3:N7:这是要返回的数组,即找到匹配项后,从这个区域中返回相应的结果。这里用是空单元格,所以如果第2参数满足的话,就会显示为空。

通过表1的员工明细表去未出勤明细表中查找,找到了的话就返回空(因为N3:N7是空单元格),没有找到,返回错误。

效果如下图2所示:

图2

屏蔽错误并转成内存数组

需求是未出勤的人员扣3分,这里返回空的话不符合需求,所以可以提前在这个区域N3:N7,录入3,并且填充所有单元格。完成后,在转到公式中,选择这个区域,并按一下F9。

这样就把刚刚的区域N3:N7转换成公式内的内存数组了{3;3;3;3;3},不需要额外占用单元格区域了。

这样就只剩下错误值#N/A了,XLOOKUP的第4参数就是未找到值,这个参数的意思是没有找到对应的查找值显示什么?不录入这个参数就是直接显示错误。

这里把第4参数录入一个空符号,代表没有找到就返回为空。

录入函数:

=XLOOKUP($B$3:$B$20,I3:I7,{3;3;3;3;3},"")

函数释义:

第1参数需要绝对锁定,第2参数不需要,当公式填充的时候,I列就变成J列了,也就是第2周到第3周的切换。

最后总结

这里巧妙的利用了XLOOKUP的屏蔽错误返回为空,同时也用了函数内的内存数组,从而实现了一个公式无需嵌套解决案例问题。

从这里可以看出公式内的内存数组的优势。

优势1:不需要额外占单元格或单元格区域;

优势2:不需要额外加美元符号进行行列锁定;

优势3:因为在函数内部,运行速度会比外部引用效率高速度快。

当然也有缺点,缺点就是数据不能太多,太多的话,公式的字符会特别长,从而影响公式的结构与阅读,速度也会受到影响。

和古哥一起学习PMC生产计划运营,一辈子够不够?

关注古哥计划

278 挑战WPS AI 写出复杂的多层嵌套公式

277 超预期,原来WPS AI也可以写出复杂的多层嵌套公式

276 巧妙用VSTACK、HSTACK实现出货货柜合并的表格变形

275 录入查询产品型号的首字,提取对应首字相同的所有型号,并按销量降序

表格中录入简称查找包含全称的所有数据 No.274

如何快速提取不同单元格的内容,并汇总到一列 No 273

如何统计指定年和月后,汇总对应产品的销售数据 No272

表格中在一个单元格内有多个条件,如何快速求和 No271

WPS新函数:巧妙利用DROP 去重UNIQUE函数留下的“0” NO 270

快速计算出每名员工参与了几个项目No.269

统计每个城市的唯一商品明细No.268

WPS更新后的TAKE函数轻松实现动态求和 No 267

WPS 新函数 EXPAND 实现工单快速分拆 No 266

WPS 新函数 CHOOSECOLS 快速对齐错乱列 No 265

WPS新函数LET让公式的长度大大的简化了. No.264

自动分配客户对应业务的奖金No.263

快速查询出销售前2的销售员和销售金额并排序 No.262

WPS 新函数 WRAPROWS 把错乱的数据整理成标准二维数据 NO 261

多表指定日期与时间的生产数据查询No.260

WPS 新函数 TOCOL将二维数组转化成一行【No.259】

WPS 新函数 VSTACK 多表查询合并【No.258】

257 一招搞定请假时间的区间转换及人数统计

广东省
浏览 264
收藏
13
分享
13 +1
5
+1
全部评论 5
 
白菜
· 安徽省
回复
 
哥玉恒
学习
· 湖南省
回复
 
花花
打卡
· 江苏省
回复
 
亂雲飛渡
学习
· 广东省
回复
 
Mustang
还得是你,来学习也
· 贵州省
回复