利用XLOOKUP快速实现培训未参加人员的扣分 NO 279
优秀创作者
某工厂的培训专员的一张表格,分别有表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生产计划运营,一辈子够不够?
关注古哥计划
277 超预期,原来WPS AI也可以写出复杂的多层嵌套公式
276 巧妙用VSTACK、HSTACK实现出货货柜合并的表格变形
275 录入查询产品型号的首字,提取对应首字相同的所有型号,并按销量降序
WPS新函数:巧妙利用DROP 去重UNIQUE函数留下的“0” NO 270
WPS 新函数 EXPAND 实现工单快速分拆 No 266
WPS 新函数 CHOOSECOLS 快速对齐错乱列 No 265
WPS 新函数 WRAPROWS 把错乱的数据整理成标准二维数据 NO 261