敢和XLOOKUP函数PK的查找函数

郭浩
郭浩
郭浩
186
文章
2
评论
2019年9月24日23:48:55 评论 40 1748字阅读5分49秒


小伙伴们好啊,我又来了。
近日由于Excel新出的X系列函数,XLOOKUP和XMATCH,在论坛和QQ群都引起很大反响,因为这2个函数的出现简化了之前一些查找公式的难度,造福新手。
然而,哪怕出现了这2个函数,还是有一个查找函数能与之比肩,就是MATCH 。
因为强大的LOOKUP本质只有一种查找方式(即目前习惯称为二分法的默认升序下的查找方式),VLOOKUP有2种,二分法和遍历法(后者在查找区域里从上往下挨个找,找到对应内容即终止,这也是常规VLOOKUP查找时结果为对应第1个值的原因),用4参指定查找方式。
然而如果熟悉MATCH的话你会发现,除了最新的的XLOOKUP和XMATCH出现所谓降序排序的查找外,早期版本只有MATCH具备这个查找方式。
也就是说,目前已知的3种查找方式,除了X系列的2个新函数,MATCH也同时具备,这也成就了这个函数在一些特殊方面的应用。
下面就是MATCH的一些用法举例。
1 常规查找

敢和XLOOKUP函数PK的查找函数

MATCH只具有查找的能力,不具备引用的能力,所以这个函数经常配合VLOOKUP/INDEX和OFFSET等配合使用。
例如上面的VLOOKUP的多列查找问题,核心在自动获取对应列数。这是MATCH的强项。
因为MATCH的主要用途就是帮你数数。
 
2 通配符查找
当MATCH的3参为0时,上面已经提过使用的是遍历法的查找方式,这种查找方式下支持通配符。
所以可以使用MATCH处理一些涉及包含关系字眼的查找。

敢和XLOOKUP函数PK的查找函数

?指代任意1个字符, *指代任意多个字符(0-255范围)。

3 升序查找方式
缺省3参数或者3参数为1时的查找方式为升序查找,微软更新XLOOKUP的重要原因之一就是很多人没有弄清所谓升序排列时的模糊查找的原理。
模糊查找这个词坑了不少新手,这种查找效果更接近范围取值效果(2参升序时),例如这个常见的日期转季度问题。

敢和XLOOKUP函数PK的查找函数


4 “1,0/” 结构的用法
案例3是二分法的正常使用方式,然而这个查找方式早就被前辈们挖掘透彻创造出了 1,0/ 结构的用法。
这个结构下的结果为最后1个对应内容(注意此时3参为1或者直接缺省)。
(新出的XLOOKUP和XMATCH貌似有找最后1个的效果,但二分法的 1,0/ 结果下一样能实现)。

敢和XLOOKUP函数PK的查找函数

注意数组公式需要使用CTRL+SHIFT+ENTER三键完成,下同。
 
5 涉及数组
涉及数组后用MATCH多条件查找也是个常见问题。

敢和XLOOKUP函数PK的查找函数


6 字符提取
由于MATCH的2参数支持数组,因此在字符提取等很多需要获取对应位置的相关问题里经常有MATCH的身影。

敢和XLOOKUP函数PK的查找函数


问题
介绍到这里必须提MATCH在去重类问题的重要意义,由于在3参为0时找对应第1个的性质,闯出了MATCH在去重问题的一番作为。

敢和XLOOKUP函数PK的查找函数


指定次数重复
按指定次数重复也能用MATCH试试。而且上述的2种查找方式都能实现。 

敢和XLOOKUP函数PK的查找函数


指定第N个的位置
借助多维,MATCH可以找指定第N个的位置,这是XMATCH目前也不能直接实现的效果。

敢和XLOOKUP函数PK的查找函数


10 加权排名
MATCH支持单列数组并返回位置的特性成就了这个函数在一些加权排名里的作为。

敢和XLOOKUP函数PK的查找函数


11 数学计算
如果能看懂案例9,那么你会发现MATCH是可以用来算一些数学题的。

敢和XLOOKUP函数PK的查找函数

(这里的公式是为了举例故未简化,数学好的童鞋肯定发现了这个解法下部分参数可以简化,譬如4个4个拿剩1个则2个2个拿必然剩1个)。

11个案例,介绍了下MATCH的一些常见和不常见的套路,然而如果你以为到这里就结束了,那就大错特错了。
之所以介绍MATCH,是为了介绍3参数为-1时的特殊查找方式,这种查找在论坛主流认为并不是降序方式的二分法而是一种特殊遍历查找方式。
常规用法当然是降序时使用。
降序下MATCH的查找效果。

敢和XLOOKUP函数PK的查找函数

但是在乱序状态下,这个查找方式在一些特殊环境下有奇效!
MATCH在合并单元格与类似结构下的特殊应用:
12 分摊奖金

敢和XLOOKUP函数PK的查找函数

这是群里一个经典题,难点在于不使用辅助列时定位最后1组合并单元格的个数。
因为很多公式不能直接解决最后1组合并单元格的单元格个数,要单独处理。而MATCH -1用法在这里成为经典。这是目前MATCH -1的一个经典应用。
由于在XMATCH与XLOOKUP前 降序下的查找是MACTH的专利,所以说这才是X系列前最强的查找函数。

图文作者:流浪铁匠
 

敢和XLOOKUP函数PK的查找函数

专业的职场技能充电站

本篇文章来源于微信公众号: Excel之家ExcelHome

继续阅读
郭浩
  • 本文由 发表于 2019年9月24日23:48:55
  • 除非特殊声明,本站文章均为原创,转载请务必保留本文链接
能自杀和通杀的工作簿 Excel

能自杀和通杀的工作簿

1,……Long Long Ago……山的那边海的那边……有位Excel高手……他在一家电子商务公司上班。他有一份Excel工作簿,通过VBA编程将日常工作通通自动化了。他每天闲的除了喝王者农药就是剁...
MATCH+INDEX函数详解 Excel

MATCH+INDEX函数详解

  小伙伴们好啊,今天和大家分享一个数据查询的最佳搭档——INDEX函数和MATCH函数。这两个函数组合,能够完成VLOOKUP函数和HLOOKUP函数的全部查找功能,并且可以实现任意方向...
一组实用接地气的Excel技巧,值得收藏 Excel

一组实用接地气的Excel技巧,值得收藏

小伙伴们好啊,今天老祝给大家带来了一组非常实用接地气的Excel技巧,让我们一起开心学起来~~表格中那么多####怎么办?如下图,表格中出现了很多的####,小伙伴们不用担心,这是Excel在说:艹,...
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: