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

2020年9月11日07:32:45你没见过,别样的Excel不规范查找匹配 数组公式应用 图文已关闭评论 45 views
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页

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。

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

欢迎留言分享。

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

  • 郑广学老师微信号
  • EXCEL880B
  • weinxin
  • 我的微信公众号
  • EXCEL880
  • weinxin