ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
楼主: e表格学习

[分享] 关于MATCH函数第3参数为-1时的查找策略( 在cleverzhzhf学导老师指导下完成)

[复制链接]

TA的精华主题

TA的得分主题

 楼主| 发表于 2016-8-10 15:22 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖已被收录到知识树中,索引项:MATCH
ZhouFfett 发表于 2016-8-8 14:23
还有,LOOKUP仅支持升序近似匹配。
之前很多人以为只有LOOKUP才用二分法查找,所以以为LOOKUP是一个很高效 ...

对于第一条,翟老师有明确相反说法.我现在好像越讨论越糊涂了……呵呵

TA的精华主题

TA的得分主题

发表于 2016-8-10 15:30 | 显示全部楼层
从两种不同的结果可以肯定,降序匹配不可能跟精确匹配一样从上向下,或从左向右逐个查找;因为如果是这样,结果应该完全一致。
刚才测试了一下,确实有一些与升序匹配二分法不同的分析结果。

TA的精华主题

TA的得分主题

发表于 2016-8-10 15:39 | 显示全部楼层
另外建议不要在这里使用“遍历”的字眼。

举一个例子,比如判断A列有没有包含“ABC”,
如果用=COUNTIF(A:A,"ABC"),则需要遍历整个A列,计算有几个单元格包含“ABC”。

但是,如果只需要判断有没有这个值,用=ISNUMBER(MATCH("ABC",A:A,))效率要高得多。
因为MATCH找到第一个“ABC”即停止查找了,所以很多时候无需遍历整个A列。

如果用二分法查找,MATCH的效率将更高。

TA的精华主题

TA的得分主题

发表于 2016-8-10 17:13 | 显示全部楼层
ZhouFfett 发表于 2016-8-10 15:30
从两种不同的结果可以肯定,降序匹配不可能跟精确匹配一样从上向下,或从左向右逐个查找;因为如果是这样, ...

完整的讲一下MATCH第3参数的查找策略。对于MATCH(9,A:A,-1)
首先从A1开始,依次与查找值数字9作比较:
    如果A1<9,就返回错误值:#N/A
    如果A1=9,就返回数字1
    如果A1>9,那么继续与A2作比较。
对于A2的判断,与A1的效果一致。
判断到An,直到:
    如果An=9,那么返回结果n
    如果An<9,那么返回结果n-1

就是这样遍历完成的。

评分

2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2016-8-10 17:16 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
ZhouFfett 发表于 2016-8-10 15:39
另外建议不要在这里使用“遍历”的字眼。

举一个例子,比如判断A列有没有包含“ABC”,

=COUNTIF(A:A,"ABC")
这个的解释不要误导,函数的后台代码有自适应区域大小的功能。它并不会计算完整的A列,而是,计算到数据的最后一行。

可选择验证方式,将A列104万行,全部填写数字,然后写Countif来计算。
另一个表只写10行数据,写相同的计算公式。
二者对比下计算时间。

TA的精华主题

TA的得分主题

发表于 2016-8-10 17:25 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
cleverzhzhf 发表于 2016-8-10 17:16
=COUNTIF(A:A,"ABC")
这个的解释不要误导,函数的后台代码有自适应区域大小的功能。它并不会计算完整的A ...

我知道只处理有数据的区域。
我做这个比较,只是说明MATCH找到结果通常不需遍历数据,而COUNTIF总是要遍历有数据的单元格。

TA的精华主题

TA的得分主题

发表于 2016-8-10 20:48 | 显示全部楼层
本帖最后由 ZhouFfett 于 2016-8-10 21:44 编辑
cleverzhzhf 发表于 2016-8-10 17:13
完整的讲一下MATCH第3参数的查找策略。对于MATCH(9,A:A,-1)
首先从A1开始,依次与查找值数字9作比较:
...

在翟老师的提醒下,经过测试,对MATCH的降序近似匹配方式之“查找大于或等于lookup_value的最小数值”可作如下解释:

1、首先比较第一个位置的值。如果第一个位置的值小于查找值,由于假设数据已按降序排序,所以认为大于或等于查找值的数值不存在而返回#N/A。

2、由上往下(或由左往右)逐个地查找第一个小于或等于查找值的位置。如果该位置的值等于查找值,就返回该位置;如果该位置的值小于查找值,就返回该位置的前一个位置。

3、如果查找不到小于查找值的位置,就返回最后一个位置。

TA的精华主题

TA的得分主题

 楼主| 发表于 2016-8-11 09:45 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 e表格学习 于 2016-8-11 10:00 编辑

34楼翟老师关于MATCH的-1查找,给出了准确解释(以下照录):
完整的讲一下MATCH第3参数为-1时的查找策略。例如,对于MATCH(9,A:A,-1)
首先从A1开始,依次与查找值数字9作比较:
    如果A1<9,就返回错误值:#N/A
    如果A1=9,就返回数字1
    如果A1>9,那么继续与A2作比较。
对于A2的判断,与A1的效果一致。
直到判断到An(即最后一个值)
    如果An>=9,那么返回结果n
    如果An<9,那么返回结果n-1
    注意:判断过程中,忽略空格\逻辑值\错误值\与查找值异类型值
就是这样遍历(不是二分法!)完成的。

(红色部分为楼主所补充的内容!建议在-1查找上有拨云见日感觉者去为翟老师献小花)


TA的精华主题

TA的得分主题

发表于 2016-8-11 14:03 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
e表格学习 发表于 2016-8-11 09:45
34楼翟老师关于MATCH的-1查找,给出了准确解释(以下照录):
完整的讲一下MATCH第3参数为-1时的查找策略。例 ...

有些细节补上了,就错了。
如果An>9,那么就继续判断An+1,而不能返回结果n
这里必须是An=9,或者An<9,判断才结束,并返回相应的数值。

TA的精华主题

TA的得分主题

 楼主| 发表于 2016-8-11 14:43 | 显示全部楼层
本帖最后由 e表格学习 于 2016-8-11 14:59 编辑
cleverzhzhf 发表于 2016-8-11 14:03
有些细节补上了,就错了。
如果An>9,那么就继续判断An+1,而不能返回结果n
这里必须是An=9,或者An


我之前有限定,即:n=最后一个单元格的位置:"直到判断到An(即最后一个值)"
这样做,就补充了老师没有说到的最后一个单元格的情况.
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 16:03 , Processed in 0.047585 second(s), 8 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表