Excel如何编写公式实现关键词与数据集精确匹配并返回结果

原有公式问题说明 #

你之前使用的公式存在两个核心缺陷，无法满足需求：

• SEARCH函数默认执行模糊匹配，未做单词边界校验，会出现BAT匹配到BATMAN的误判
• 公式仅检索单个单元格B2，未覆盖B列到N列的整行范围，且仅能返回首个匹配项，无法统计匹配总数、列出所有命中关键词

适配需求的可用公式 #

以下公式默认关键词存放在R2:R5区域，待匹配数据范围为对应行的B:N列，以第2行匹配为例：

适用Excel 365/2021及以上支持动态数组的版本 #

直接在结果输出单元格输入以下公式即可，无需按数组组合键：

=LET(
kw_pool, $R$2:$R$5,
scan_range, B2:N2,
hit_list, FILTER(kw_pool, BYROW(kw_pool, LAMBDA(single_kw, SUM(--ISNUMBER(SEARCH("(?<![a-zA-Z])"&single_kw&"(?![a-zA-Z])", scan_range, 1)))>0)), ""),
hit_num, COUNTA(hit_list),
IF(hit_num>=2, "True，命中关键词："&TEXTJOIN("、", TRUE, hit_list), "False，仅命中"&hit_num&"个关键词")
)

公式逻辑说明：

• 通过正则零宽断言(?<![a-zA-Z])、(?![a-zA-Z])做全词边界校验，仅匹配前后无英文字母的独立关键词，从根源避免短词匹配到长单词的问题；如果未启用正则匹配功能，可将公式里的SEARCH正则判断替换为ISNUMBER(SEARCH(" "&single_kw&" "," "&TEXTJOIN(" ",TRUE,scan_range)&" "))，通过前后补空格的方式实现独立词匹配，适配无正则的使用场景
• 自动遍历所有关键词，逐行扫描B到N列的所有单元格，收集全部命中的关键词
• 自动统计命中数量，满足≥2个关键词的判定条件时返回True，同时拼接展示所有命中项

适用不支持动态数组的旧版Excel #

先在辅助列输入以下数组公式（输入完成后按Ctrl+Shift+Enter确认）统计单行匹配关键词总数：

=SUMPRODUCT(--ISNUMBER(SEARCH("(?<![a-zA-Z])"&TRANSPOSE($R$2:$R$5)&"(?![a-zA-Z])", B2:N2, 1)))

再输入以下数组公式（同样按Ctrl+Shift+Enter确认）提取所有命中的关键词：

=TEXTJOIN("、",TRUE,IF(ISNUMBER(SEARCH("(?<![a-zA-Z])"&$R$2:$R$5&"(?![a-zA-Z])", TEXTJOIN(" ",TRUE,B2:N2),1)),$R$2:$R$5,""))

最后嵌套IF判断匹配数是否≥2即可输出对应结果。

内容的提问来源于stack exchange，提问作者Navneet Singh