今天世杰老师给大家讲解一些关于查找匹配的函数组合,能够提高工作效率至少90%以上。 如下图所示,查询右侧员工编号为“45424”的所有的销售数量。 对于查询类的问题,大家第一时间可能想到的会是VLOOKUP函数。在A列前插入一列辅助列,在B2单元格中输入公式: =COUNTIF(B$2:B2,B2)&'-'&B2 然后按Enter键完成后向下填充。 然后在G4单元格中输入公式: =IFERROR(VLOOKUP(ROW(A1)&'-'&$H$2,$A$2:$E$13,5,0),'') 按Enter键后向下填充至没有内容为止。 INDEX函数也能实现VLOOKUP与LOOKUP的十字交叉查询,并且还很实用。 查找:工号的所有的信息。 在G3单元格中输入公式: =INDEX($B$2:$D$9,MATCH($F3,$A$2:$A$9,0),MATCH(G$2,$B$1:$D$1,0)) 按Enter键后向下填充。 套路:=INDEX(所在区域范围,目标所在行号,目标所有的列号) 对于下面的两个例题,同样是一个十字交叉查找的案例,根据例子的不同情况,选择相应的解决方法才是最合适的。 关于十字交叉查询的方法,那么这里来使用HLOOKUP函数做十字交叉查询,然后再使用VLOOKUP函数做同样的解决方法。 HLOOKUP查找: 在B7单元格中输入公式: =HLOOKUP(B$6,$A$1:$E$4,MATCH($A7,$A$1:$A$5,0),0) 按Enter键向下向右填充即可。 VLOOKUP查找: 在B7单元格中输入公式: =VLOOKUP($A7,$A$1:$E$4,MATCH(B$6,$A$1:$E$1,0),0) 按Enter键向下向右填充即可。 对比:在这个问题上,两个函数都能配合MATCH函数获取对应的列号与行号,以来来完成十字交叉查询的目的。 SUMIFS与SUMPRODUCT函数同样地具有LOOKUP函数的功能。 【SUMIFS函数】 在I5单元格中输入公式: =SUMIFS(D2:D9,B2:B9,G5,C2:C9,H5) 按Enter键完成。 查找套路:SUMIFS(求和区域,条件区域1,条件2,条件区域1,条件2……) 【SUMPRODUCT函数】 在H5单元格中输入公式: =SUMPRODUCT((G5=B2:B9)*(H5=C2:C9)*D2:D9) 按Enter键完成。 查找套路: SUMPRODUCT((条件1=条件区域1)*(条件2=条件区域2)*……*(求和区域)) 友情提示:以上查询只能为查询结果为数值的情况才能使用,如果查找结果为文本请使用VLOOKUP函数,LOOKUP函数或者INDEX函数。 |
|