Excel多单元格字符串含入排除查询需求（BYROW/LAMBDA诉求）

问题背景 #

正在开展兽医病理学大样本病例研究，需在Excel中对每行指定单元格单独检查是否包含目标字符串。目前使用数组公式：

=IF((COUNT(SEARCH({"fat atrophy,""inanition","serous atrophy of fat","negative energy balance","emaciation"},$E:$3))>0),"Y","N")

（以营养疾病关键词为例），通过CTRL+SHIFT+ENTER输入，但大量行未正确识别目标字符串，尝试多种公式均无效。

额外需求：

• 添加排除条件：例如区分"肉芽肿（未提及细菌）"和"肉芽肿（提及细菌）"，前者需排除含"bacteria"的单元格。
• 需对每行单元格检查是否包含至少一个含入列表的字符串，同时排除排除列表中的任意字符串；因数据超6000行，优先使用BYROW/LAMBDA方案。

需求示例 #

示例1：细菌性结肠炎/肠炎识别 #

输出"Y"代表细菌性结肠炎或肠炎，规则：含入条件为（诊断1或诊断2包含colitis/enteritis）且（诊断1或诊断2包含bacterial），无排除条件。

示例2：未明确病因的肠炎/结肠炎识别 #

输出"Y"代表肠炎或结肠炎未明确病因（无真菌/霉菌或细菌提及），规则：含入条件为（诊断1或诊断2包含colitis/enteritis），排除条件为（诊断1或诊断2包含fungal/mycotic/bacterial）。

公式方案 #

通用BYROW/LAMBDA框架 #

针对每行指定单元格（如诊断1=B2、诊断2=C2），结合含入列表、排除列表的通用公式结构：

=BYROW(B2:C6001,LAMBDA(row,
  LET(
    include_list, {"enteritis","colitis"}, // 替换为你的含入关键词列表
    exclude_list, {"fungal","mycotic","bacterial"}, // 替换为你的排除关键词列表
    cell_text, TEXTJOIN(" ",TRUE,row), // 合并当前行指定单元格内容
    has_include, SUMPRODUCT(--ISNUMBER(SEARCH(include_list,cell_text)))>0,
    has_exclude, SUMPRODUCT(--ISNUMBER(SEARCH(exclude_list,cell_text)))>0,
    // 根据需求调整判断逻辑
    IF(AND(has_include, NOT(has_exclude)), "Y", "N")
  )
))

示例1对应公式 #

匹配含入关键词+指定关键词（细菌性结肠炎/肠炎）：

=BYROW(B2:C6001,LAMBDA(row,
  LET(
    core_terms, {"enteritis","colitis"},
    bacterial_term, {"bacterial"},
    cell_text, TEXTJOIN(" ",TRUE,row),
    has_core, SUMPRODUCT(--ISNUMBER(SEARCH(core_terms,cell_text)))>0,
    has_bacterial, SUMPRODUCT(--ISNUMBER(SEARCH(bacterial_term,cell_text)))>0,
    IF(AND(has_core, has_bacterial), "Y", "N")
  )
))

示例2对应公式 #

匹配含入关键词且无排除关键词（未明确病因的肠炎/结肠炎）：

=BYROW(B2:C6001,LAMBDA(row,
  LET(
    core_terms, {"enteritis","colitis"},
    exclude_terms, {"fungal","mycotic","bacterial"},
    cell_text, TEXTJOIN(" ",TRUE,row),
    has_core, SUMPRODUCT(--ISNUMBER(SEARCH(core_terms,cell_text)))>0,
    has_exclude, SUMPRODUCT(--ISNUMBER(SEARCH(exclude_terms,cell_text)))>0,
    IF(AND(has_core, NOT(has_exclude)), "Y", "N")
  )
))

原公式问题分析 #

原公式中$E:$3是错误引用：$E:$3表示E列从第1行到第3行的所有单元格，而不是当前行的指定单元格（如E3）。正确的引用应该是针对当前行的单个/多个单元格（比如E2或B2:C2），范围错误导致大量行匹配失效。

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