Google Sheets基于逗号分隔多条件提取指定列数据的公式问题

原公式问题分析 #

• 未拆分Sheet2第一列中逗号分隔的多个关键词，仅将整行规则文本作为正则匹配项，无法命中多关键词组合
• 规则排序逻辑错误，未优先匹配关键词更多的规则，导致apple pie is better先命中单关键词pie而非优先级更高的apple, pie规则
• 未做全关键词命中校验，仅做单片段匹配，无法满足多条件同时满足的需求

修复后公式 #

将以下公式粘贴到Sheet1的B2单元格，下拉填充即可：

=IFNA(
  BYROW(
    SORT(Sheet2!A:B, LEN(Sheet2!A:A)-LEN(SUBSTITUTE(Sheet2!A:A, ",", "")), FALSE),
    LAMBDA(r,
      IF(
        AND(
          ARRAYFORMULA(
            ISNUMBER(SEARCH(TRIM(SPLIT(INDEX(r,1), ",")), LOWER(A2)))
          )
        ),
        INDEX(r,2),
        NA()
      )
    )
  ),
  "无匹配"
)

如果你的Google Sheets区域设置使用分号作为参数分隔符，把公式里的逗号替换为分号即可

公式逻辑说明 #

1. 规则排序：按每条规则的关键词数量倒序排列，关键词越多的规则越先校验，保证更精准的长规则优先命中
2. 关键词拆分：自动拆分每条规则中逗号分隔的多个关键词，自动去除关键词前后空格
3. 全匹配校验：逐一检查待匹配文本是否包含当前规则的全部关键词，不依赖空格分隔，支持模糊匹配
4. 结果返回：返回第一个完全命中的规则对应的返回值，无匹配则返回无匹配（可自行修改默认值）

测试匹配效果验证 #

对应你给出的测试短语，返回结果如下：

• fruit bat → Animal - Bats
• fruit bats → Animal - Bats
• fruitbat → Animal - Bats
• big fruit bat → Animal - Bats
• fruit bat eating orange → Animal - Orange bat
• orange fruit → Food - Orange
• apple is a good fruit → Food - Apple
• i like pie → Food - Pie
• pies for lunch → Food - Pie
• apple pie is better → Food - Apple Pie
• juice from apples → Beverage - Apple

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