Excel公式实现：高亮不在允许列表的逗号分隔值

可以通过条件格式+自定义公式实现这个需求，以下分Excel版本给出具体实现步骤：

适用Excel 365/2021及以后版本 #

条件格式中使用以下公式：

=SUMPRODUCT(--ISNA(XMATCH(TRIM(TEXTSPLIT(B2, ",")), $A$1:$A$5, 0)))>0

公式说明 #

• TEXTSPLIT(B2, ",")：将目标单元格B2的内容按逗号拆分为独立值
• TRIM(...)：去除每个拆分后值的前后空格（避免因空格导致匹配失败）
• XMATCH(..., $A$1:$A$5, 0)：检查每个值是否存在于允许列表（示例中为A1:A5），找不到则返回错误值
• ISNA(...)：将找不到的情况标记为TRUE
• --：将TRUE/FALSE转换为1/0
• SUMPRODUCT(...)>0：只要有一个值不在允许列表，求和结果就大于0，触发高亮

适用旧版Excel（无TEXTSPLIT功能） #

条件格式中使用以下兼容公式：

=SUMPRODUCT(--ISNA(MATCH(TRIM(FILTERXML("<t><s>"&SUBSTITUTE(B2, ",", "</s><s>")&"</s></t>", "//s")), $A$1:$A$5, 0)))>0

公式说明 #

• SUBSTITUTE(B2, ",", "</s><s>")：将逗号替换为XML标签，配合FILTERXML实现拆分
• FILTERXML(...)：从构造的XML字符串中提取每个独立值
• 后续TRIM、MATCH、ISNA、--、SUMPRODUCT的作用与上方一致

设置步骤 #

1. 选中需要应用高亮的单元格区域（比如B2:B100）
2. 点击「开始」选项卡 → 「条件格式」→ 「新建规则」
3. 选择「使用公式确定要设置格式的单元格」
4. 粘贴对应版本的公式，注意将$A$1:$A$5替换为你的实际允许值列表区域
5. 点击「格式」按钮，设置高亮样式（比如填充黄色）
6. 确认保存规则即可

注意事项 #

• 允许列表区域务必使用绝对引用（加$符号），避免规则应用到其他单元格时引用偏移
• 提前清理允许列表中的空白单元格，防止误判
• 如果目标单元格为空，公式会返回FALSE，不会触发高亮

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