带包含/排除规则的Google Sheets多文本查找替换技术问询

根据你的需求，以下是符合规则的Google Sheets批量查找替换方案，分两种常见场景提供公式：

场景1：全局统一站点归属（如A5为当前站点） #

适用于所有文本行共用同一个归属站点的情况，公式放在K8单元格：

=BYROW(J8:J, LAMBDA(txt, 
  IF(txt="", "", 
    REDUCE(txt, 
      FILTER(B8:C, 
        (LEN(D8:D)=0 OR REGEXMATCH(D8:D, "\b" & $A$5 & "\b")) 
        AND (LEN(E8:E)=0 OR NOT REGEXMATCH(E8:E, "\b" & $A$5 & "\b"))
      ), 
      LAMBDA(prev, rule, 
        SUBSTITUTE(prev, INDEX(rule, 1), INDEX(rule, 2))
      )
    )
  )
))

场景2：每行文本对应独立站点归属（如I列为每行的归属站点） #

适用于每行文本有不同归属站点的情况，公式放在K8单元格：

=MAP(J8:J, I8:I, LAMBDA(txt, site, 
  IF(txt="", "", 
    LET(
      valid_rules, FILTER(B8:C, 
        (LEN(D8:D)=0 OR REGEXMATCH(D8:D, "\b" & site & "\b")) 
        AND (LEN(E8:E)=0 OR NOT REGEXMATCH(E8:E, "\b" & site & "\b"))
      ),
      REDUCE(txt, valid_rules, LAMBDA(prev, rule, SUBSTITUTE(prev, INDEX(rule,1), INDEX(rule,2))))
    )
  )
))

关键说明 #

1. 规则过滤逻辑：

   • 若包含站点列（D）非空，仅当归属站点在包含列表中时启用规则
   • 若排除站点列（E）非空，归属站点在排除列表中时跳过规则
   • 若包含/排除列均为空，规则始终生效
   • \b 正则确保匹配完整站点名称（避免"Site1"误匹配"Site10"）

2. 替换行为：

   • 使用SUBSTITUTE进行字面量替换（无正则冲突），若需正则替换，替换为REGEXREPLACE(prev, REGEXQUOTE(INDEX(rule,1)), INDEX(rule,2))
   • 按规则在表格中的顺序依次执行替换

3. 扩展调整：

   • 若需仅应用指定列表集（如A5为目标列表集），在FILTER中添加A8:A = $A$5条件即可

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