Excel Match() 函数的通配特性

今天遇到一个奇怪的情况,在使用形如:

=Match(A2,A:A,0)

的 Excel 公式查找时,居然返回了 #N/A 的结果。逻辑上来说,在自己所在的数组里查找自己,不应该得到错误结果,事实上这个公式的值只可能是 1 或者 2 才对。

经过检查,发现问题出在字符 ~ (波浪号)上,即键盘上 ESC 键的下方,数字 1 的左边那个键的 Shift 上档符号。

Excel 官方帮助文档 中,找到了相关的解释。

如果 match_type 为 0 且 lookup_value 为文本字符串,您可在 lookup_value 参数中使用通配符 – 问号 (?) 和星号 (*) 。问号匹配任意单个字符;星号匹配任意一串字符。如果要查找实际的问号或星号,请在字符前键入波形符 (~)。

这里提到了,当使用 Match() 函数进行精确的文本查找时可以使用通配符 ? 和 *,当用户确实需要查找问号/星号时,则可以用 ~? 和 ~* 来表示。这里没有提到的是,如果用户需要查找 ~,其实也需要通过两个波浪号 ~~ 来表示。转义符自身也需要转义表达,也算是一般规则了。

所以,如果一个单元格中包含了波浪号 ~,则当这个单元格作为被查找的数据的一部分时,是正常的。但当它同时作为 lookup_value 时,则会在转义后变得无实际意义。于是导致了 Match() 函数查找自身所在数组时产生的错误结果。

遇到这个问题时,我正在处理由家用下载机长年积累下的大量动画片资源。用 Bash 获得所有的动画目录名、文件名,并试图尽量删减一些重复资源。文件名系统中并不存在『 ? * : < > | / \ ” 』等符号,但偏偏允许波浪号 ~ 的存在。又因为在 Excel 的默认字体中,波浪号并不显示为扭曲的波浪形状,而是略长的横线,如图,于如是尴尬便发生了。

解决办法:

给 lookup_value 添加一个 SUBSTITUTE() 函数进行修正,即:

即可得到期望结果。

一句话总结:

当 match_type 为 0 且 lookup_value 为文本字符串时,使用 =Match() 函数时需要注意 lookup_value 是否包含 ~ 、?、* 并根据需要预先做处理。

发表评论

电子邮件地址不会被公开。 必填项已用*标注