软件下载 点击激活 软件下载

敢和XLOOKUP函数PK的查找函数

小伙伴们好啊,我又来了。
近日由于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系列前最强的查找函数。

给TA打赏
共{{data.count}}人
人已打赏
办公技巧

你做的柱形太丑了,连体图表来了!

2022-8-8 21:26:23

办公技巧

6个Excel进阶小技巧!

2022-8-8 21:53:24

文章版权声明 1、本网站名称:office自学网
2、本站永久网址:https://www.officezxw.com/
3、本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长QQ:1241926466进行删除处理。
4、本站一切资源不代表本站立场,并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息,访客发现请向站长举报
6、本站资源大多存储在云盘,如发现链接失效,请联系我们我们会第一时间更新。

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索