Excel按关键词列表搜索筛选数据的公式问题咨询

问题原因 #

• SEARCH函数参数顺序颠倒：SEARCH的语法为SEARCH(要查找的关键词, 被检索的文本)，你原公式将B列待检索内容作为第一个参数，关键词列表作为第二个参数，逻辑反向无法命中匹配项
• 缺少行维度结果汇总：直接对两个多单元格区域执行SEARCH会返回二维数组，未按行汇总匹配结果的情况下，FILTER无法正确筛选符合要求的行

解决公式 #

适用Excel 365/2021及以上版本（支持LAMBDA函数） #

直接替换原有公式即可，以下分别提供两种分隔符版本：

• 逗号分隔符版本（多数地区使用）：
  =FILTER($A$2:$C$7,BYROW($B$2:$B$7,LAMBDA(x,OR(ISNUMBER(SEARCH(mP_Keywords,x))))))
• 分号+反斜杠分隔符版本（欧洲等地区使用，适配你原公式的符号规则）：
  =FILTER(INDEX($A$2:$D$7;SEQUENCE(ROWS($A$2:$D$7));{1\2\3});BYROW($B$2:$B$7;LAMBDA(x;OR(ISNUMBER(SEARCH(mP_Keywords;x))))))

适用不支持LAMBDA的旧版本Excel #

用MMULT做矩阵运算实现按行汇总匹配结果：

• 逗号分隔符版本：
  =FILTER($A$2:$C$7,MMULT(ISNUMBER(SEARCH(TRANSPOSE(mP_Keywords),$B$2:$B$7))*1,ROW(mP_Keywords)^0)>0)
• 分号分隔符版本：
  =FILTER(INDEX($A$2:$D$7;SEQUENCE(ROWS($A$2:$D$7));{1\2\3});MMULT(ISNUMBER(SEARCH(TRANSPOSE(mP_Keywords);$B$2:$B$7))*1;ROW(mP_Keywords)^0)>0)

优化说明 #

你原公式中用INDEX($A$2:$D$7;SEQUENCE(ROWS($A$2:$D$7));{1\2\3})取前3列的写法可以简化为$A$2:$C$7，效果完全一致。

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