如何配置Excel自定义数据验证，限定单元格为特定格式的逗号分隔唯一值列表

一、核心需求回顾 #

• 单元格内容为逗号分隔的列表
• 每个列表项必须符合「2个大写字母 + 8位数字」格式（如AB12345678）
• 列表中所有值必须唯一
• 兼容Excel网页版

二、单个值验证公式简化 #

你原来的单个值验证公式可以简化为更易读的版本，效果完全一致：

=AND(LEN(A1)=10, EXACT(UPPER(LEFT(A1,2)), LEFT(A1,2)), ISNUMBER(VALUE(RIGHT(A1,8))))

• LEN(A1)=10：验证总长度为10位
• EXACT(UPPER(LEFT(A1,2)), LEFT(A1,2))：确保前两位是大写字母（避免小写或非字母）
• ISNUMBER(VALUE(RIGHT(A1,8)))：验证后8位是纯数字

三、多值场景的完整验证公式 #

针对逗号分隔的列表，结合格式验证和唯一性检查，以下公式兼容Excel网页版（使用FILTERXML拆分列表，兼容性优于TEXTSPLIT）：

=OR(A1="",
    AND(
        SUMPRODUCT(--(LEN(TRIM(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s")))<>10))=0,
        SUMPRODUCT(--(NOT(EXACT(UPPER(LEFT(TRIM(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s")),2)), LEFT(TRIM(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s")),2))))) =0,
        SUMPRODUCT(--(NOT(ISNUMBER(VALUE(RIGHT(TRIM(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s")),8))))))=0,
        SUMPRODUCT(--(COUNTIF(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s"), FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s"))>1))=0
    )
)

公式拆解 #

1. OR(A1="", ...)：允许单元格为空（不需要的话可以删除这部分）
2. 拆分列表：FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s") 将逗号分隔的内容拆分为独立项，TRIM() 去除每个项前后的空格（避免输入时的空格干扰）
3. 格式验证部分：
   • SUMPRODUCT(--(LEN(...)<>10))=0：确保所有项的长度都是10位
   • SUMPRODUCT(--(NOT(EXACT(...))))=0：确保所有项的前两位都是大写字母
   • SUMPRODUCT(--(NOT(ISNUMBER(...))))=0：确保所有项的后8位都是数字
4. 唯一性验证：SUMPRODUCT(--(COUNTIF(...)>1))=0：检查每个项的出现次数，确保没有重复值

四、使用步骤 #

1. 选中需要设置验证的单元格/单元格区域
2. 点击「数据」选项卡 → 「数据验证」→ 选择「自定义」类型
3. 将上述公式粘贴到「公式」输入框中（注意把公式中的A1替换为你选中区域的第一个单元格，比如选中B2:B10的话，公式里用B2）
4. 可以在「出错警告」中设置提示信息，比如输入不符合要求时显示“请输入逗号分隔的唯一大写字母+8位数字列表（如AB12345678, CD09876543）”

五、注意事项 #

• Excel网页版需确保是365订阅版本，FILTERXML函数在网页版中是支持的
• 输入时避免末尾加多余逗号（会拆分出空项，触发长度验证失败）
• 如果你的Excel版本支持TEXTSPLIT（365最新版），可以把公式中的FILTERXML部分替换为TEXTSPLIT(A1, ",")，公式会更简洁

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