如何用Excel公式限制单元格邮箱域名并校验有效性？

核心逻辑拆解 #

要实现需求，需完成四个关键动作：拆分单元格内的多邮箱、提取域名、去重、校验域名合法性并返回状态。以下是基于Excel 365/2021（支持动态数组）和旧版Excel的具体方案：

假设前提 #

• 原始邮箱数据列：A列（A1至A150）
• 允许的域名列表：$D$1:$D$5（绝对引用，可根据实际范围调整）

方案一：带辅助列（清晰易维护） #

1. 提取并去重域名（辅助列B） #

在B1单元格输入公式，下拉填充至B150：

=UNIQUE(TEXTAFTER(TEXTSPLIT(A1,"; "),"@",1,,TRUE))

• TEXTSPLIT(A1,"; ")：按「分号+空格」拆分单元格内的多个邮箱
• TEXTAFTER(..., "@",1,,TRUE)：提取每个邮箱@后的域名部分，TRUE确保无@的无效内容也会被保留（后续判定为无效）
• UNIQUE(...)：对提取的域名去重，避免重复校验

2. 生成状态标记（结果列C） #

在C1单元格输入公式，下拉填充至C150：

=IF(AND(ISNUMBER(XMATCH(B1,$D$1:$D$5))),"Pass","Fail")

• XMATCH(B1,$D$1:$D$5)：检查每个域名是否在允许列表中，存在返回位置、不存在返回错误
• ISNUMBER(...)：将位置转为TRUE，错误转为FALSE
• AND(...)：所有域名都合法则返回TRUE，否则FALSE
• IF(...)：根据结果返回Pass或Fail

方案二：无辅助列（一键生成结果） #

直接将两个公式合并，在C1输入后下拉：

=IF(AND(ISNUMBER(XMATCH(UNIQUE(TEXTAFTER(TEXTSPLIT(A1,"; "),"@",1,,TRUE)),$D$1:$D$5))),"Pass","Fail")

注：此公式依赖Excel动态数组功能，仅支持365/2021版本。

方案三：旧版Excel兼容（无动态数组） #

若使用Excel 2019及更早版本，输入以下数组公式（输入后按Ctrl+Shift+Enter触发数组计算）：

=IF(SUM(--ISERROR(MATCH(MID(SUBSTITUTE(A1,"; ","@"),FIND("@",SUBSTITUTE(A1,"; ","@"),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,"; ",""))+1)))+1,LEN(A1)),$D$1:$D$5,0)))=0,"Pass","Fail")

• 通过字符串替换和位置计算模拟拆分邮箱，提取域名
• 统计无效域名数量，为0则返回Pass，否则Fail

内容的提问来源于stack exchange，提问作者Benjamin Roder