今天和大家分享一组简单高效的Excel函数公式:
1、XLOOKUP函数查询数据
如下图,要根据F列的姓名,在左侧数据表中查找对应的门店信息。
G2单元格参考公式如下:
XLOOKUP(F2,B:B,A:A,”无记录”)
XLOOKUP函数的查询区域和返回区域是分开的两个参数,不用再考虑查询的方向问题,不仅能实现从左到右,还能从右到左、从下到上、从上到下等任意方向的查询。
2、LAMBDA函数填充数据
如下图,B列是一些合并单元格,使用以下公式可以创建一个内存数组,将B列的数据填充完整。
=SCAN(“”,B2:B9,LAMBDA(a,b,lF(b=””,a,b)))
SCAN函数用于遍历数组中的每个值,并且能够记录LAMBDA函数运算体每次运算的值,在下一次运算中调用这个值。
本例中,SCAN函数将空文本作为累加器的初始值,B2:B9单元格是要遍历的数组。
第一次遍历时,a为空文本,b为B2单元格的“1005-2”,遍历结束后,IF表达式的结果为“1005-2”。这个结果在第二次遍历时会作为新的a。
第二次遍历,a为“1005-2”,b为B3单元格的空文本,第二次遍历结束后,IF表达式的结果仍然为“1005-2”。该结果在第三次遍历时继续作为新的a。
到第四次遍历时,a为“1005-2”,b为B4单元格的“1006-3”,遍历结束后,IF表达式的结果变成“1006-3”,该结果在第五次遍历时作为新的a……。
3、TOCOL函数多表提取不重复姓名
如下图,有三个月的员工考勤记录,每个月都有新入职和离职人员。
需要在另一个表里获取所有的不重复员工名单:
参考公式为:
=UNIQUE(TOCOL(‘1月:3月’!B:B,1))
TOCOL函数将1月到3月之间的各个工作表B列合并为一列,第二参数使用1,表示忽略空白单元格。
再使用UNIQUE函数提取出不重复值即可。
4、WRAPROWS函数一列转多列
如下图,希望将A列的姓名转换为五列。C2单元格输入以下公式即可:
=WRAPROWS(A2:A38,5,””)
WRAPROWS用于将一列内容转换为多列,第1参数是要处理的数据区域,第二参数指定转换的列数。
如果转换后的行列区域大于实际的数据元素个数,第三参数可将这些多出的区域显示成指定的字符,本例显示为空文本。