你没见过,别样的Excel不规范查找匹配 数组公式应用 图文

2020年9月11日07:32:45你没见过,别样的Excel不规范查找匹配 数组公式应用 图文已关闭评论 320 views
微信公众号 【EXCEL880】 QQ群【165159540】
课程咨询 加我微信EXCEL880B试学网址http://v.excel880.com

Excel查找匹配在工作中经常遇到,很多时候都不是很规范的查找,如下所示

下表是王者公司组队竞赛数据

1、根据不完整的组合名查找匹配战绩

2、根据组员名字查找对应战绩

你没见过,别样的Excel查找匹配

我们都知道VLOOKUP函数查找匹配要求第1个参数的值,在查找数据区域内在第一列,并且保持一字不差,甚至都不能有空格,如果在H2简单的使用VLOOKUP(G2,A:E,5,0),返回的结果是错误值。而G列的组员在原始数据源中分布在多列,,遇到这种情况时,如何进行查找匹配?

求组合战绩

第一想到的就是将G列的组合名字修改成和A列的名字保持一致,后面加个“组”字,然后再使用VLOOKUP函数求解

你没见过,别样的Excel查找匹配

这个是数据源和需要查找的数据比较少的情况下可以这么修改,但是如果数据很多时,则需要使用公式=VLOOKUP(G2*,A:E,5,0)计算

你没见过,别样的Excel查找匹配

其中*号是通配符,也就是万能字符,G2*表示查找开头是巾帼对应的战绩。假如A2单元格内容是巾帼英雄666,也能查找到对应的战绩。

求组员战绩

第一反应可能是将所有的组员放至一列,然后再查找匹配,但这个工作量是复杂并且巨大的

你没见过,别样的Excel查找匹配

辅助列虽然容易解决问题,但是建立辅助列太复杂的话,不如不要,直接在H7使用数组公式:

=INDEX(E:E,MAX(IF(G7=$B$2:$D$6,ROW($B$2:$D$6),1))),输入完之后使用CTRL+SHIFT+ENTER进行计算。

你没见过,别样的Excel查找匹配

我知道每次列出一个公式,都有同志不理解,今天来解释下数组公式,这也是理解数组公式有效的方法,以H7单元格的公式为例

你没见过,别样的Excel查找匹配

框选区域,然后按F9键

你没见过,别样的Excel查找匹配

什么意思呢?在表格中理解,就是B2:D6哪些等于吕布,得到的结果就是下面的数组

你没见过,别样的Excel查找匹配

然后框选ROW(B2:D6),按F9,得到的是:你没见过,别样的Excel查找匹配

然后框选IF()函数了,如果是TRUE,返回对应的({2,3,4,5,6}),如果错误返回1,则在数组中运算就是:

你没见过,别样的Excel查找匹配

在表格中的体现就是:对的返回吕布所在行的行号3,错误返回1

你没见过,别样的Excel查找匹配

然后使用max()函数,这些数组最大的值就是3了

然后就是INDEX(E:E,3),就是求E列的第3行,即数据为90。

通过这样的计算解释,你理解了吗?

欢迎留言分享。

-----------------