Excel函数判断G列逗号分隔单元格是否包含B1:E1任意值

基础信息 #

• 运行环境：Microsoft Excel 2204（Build 15128.20280）
• 数据结构：G列存储逗号分隔的单位数数值
• 需求：识别G列单元格是否包含B1:E1区域中的任意值，禁止使用VBA类编程方案
• 预期返回规则：
  • G2（值：1）→ FALSE：1不在{2,3,4,5}集合内
  • G3（值：1,2）→ TRUE：2属于目标集合
  • G4（值：3,4）→ TRUE：3、4属于目标集合
  • G5（值：1,5）→ TRUE：5属于目标集合

原有公式问题 #

之前使用的=NOT(ISERROR(SEARCH(B1:E1,G2:G5)))匹配逻辑本身有效，但返回的是4行（对应G2-G5）4列（对应B1-E1）的二维布尔数组，缺少按行执行OR逻辑聚合的步骤，无法直接得到每行对应的一维判定结果。

解决方案 #

在结果列的首个单元格（比如与G2对齐的H2）输入下面的动态数组公式，按回车后会自动溢出得到G2到G5对应的全部判定结果：

=BYROW(NOT(ISERROR(SEARCH(B$1:E$1,G2:G5))),LAMBDA(x,OR(x)))

如果需要兼容不支持LAMBDA函数的Excel版本，可以改用MMULT聚合的写法，效果完全一致：

=MMULT(--NOT(ISERROR(SEARCH(B$1:E$1,G2:G5))),{1;1;1;1})>0

逻辑说明 #

1. 核心匹配段NOT(ISERROR(SEARCH(B$1:E$1,G2:G5)))：逐个检查G列每个单元格是否包含B1到E1的每个目标值，匹配成功返回TRUE，失败返回FALSE，生成二维布尔数组
2. 聚合段：
   • BYROW写法：按行遍历二维数组，对每一行的布尔值做OR运算，只要该行存在一个TRUE，最终结果就为TRUE
   • MMULT写法：把布尔值转成1/0的数值后按行求和，求和结果大于0就代表该行存在匹配值，等价于OR逻辑

提示：当前场景所有数值都是单位数，用SEARCH不会出现多位数部分匹配的误判；如果后续有多位数值的匹配需求，把匹配逻辑改成按逗号拆分后精确比对即可。

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