Excel如何判断Cell A2是否仅恰好包含指定子串列表中的某一个一次

核心需求 #

判断A2单元格是否仅包含Apple、Banana、Orange三个指定子串中的某一个，且仅出现一次，不允许重复出现同一子串，也不允许同时出现多个不同的指定子串。

具体实现 #

方案1：Excel 365/2021 版本（公式简洁易读） #

直接在判定单元格输入以下公式即可：

=LET(
target_list,{"Apple","Banana","Orange"},
count_arr,SUMPRODUCT(LEN(A2)-LEN(SUBSTITUTE(A2,target_list,"")))/LEN(target_list),
AND(MAX(count_arr)=1,SUMPRODUCT(--(count_arr>0))=1)
)

公式逻辑：

• 先定义三个目标子串为数组target_list
• 计算每个子串在A2中的实际出现次数，存入count_arr数组
• 同时满足两个条件时返回TRUE（符合要求）：
  • 所有子串的最高出现次数为1，无重复出现的情况
  • 出现次数大于0的子串仅有1个，无同时出现多个目标子串的情况

方案2：兼容全Excel版本（适配2019及更早版本） #

如果没有LET函数权限，使用以下通用公式：

=AND(
SUMPRODUCT(--(LEN(A2)-LEN(SUBSTITUTE(A2,{"Apple","Banana","Orange"},""))>0))=1,
SUMPRODUCT((LEN(A2)-LEN(SUBSTITUTE(A2,{"Apple","Banana","Orange"},"")))/LEN({"Apple","Banana","Orange"}))=1
)

附加说明 #

如果需要不区分大小写匹配，将公式中的SUBSTITUTE(A2,xxx,"")替换为SUBSTITUTE(UPPER(A2),UPPER(xxx),"")即可。

效果校验示例 #

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