如何编写实现单元格值查找并按规则返回结果的Excel公式？

规则说明：检查给定值是否存在于指定单元格区域，满足以下任一情况返回n/a：

1. 该值是所在单元格的唯一值
2. 该值是所在单元格的最后一个值
   其余存在的情况返回TRUE

适用新版本（Excel 365/2021、最新版WPS）公式 #

假设待匹配目标值放在D1单元格，待检查区域为A2:A10，单元格内多值分隔符为英文逗号，可根据实际场景替换参数：

=LET(
    全部值, TEXTSPLIT(TEXTJOIN(",",TRUE,A2:A10), ","),
    匹配次数, COUNTIF(全部值, D1),
    末位值, XLOOKUP("*", 全部值, 全部值, "", 2, -1),
    IF(匹配次数=0, "不存在", IF(OR(匹配次数=1, XLOOKUP(D1,全部值,全部值,"",0,-1)=末位值), "n/a", TRUE))
)

逻辑说明 #

• 先把区域内所有单元格的内容按分隔符拼接后拆分，得到所有值的扁平化数组
• 统计目标值出现次数：次数为0返回不存在，次数为1直接返回n/a
• 匹配目标值最后一次出现的位置，判断是否等于整个数组的最后一位：是则返回n/a
• 排除以上情况后返回TRUE

适用旧版本Excel公式 #

=IF(COUNTIF(A2:A10,"*"&D1&"*")=0,"不存在",IF(OR(SUMPRODUCT(LEN(A2:A10)-LEN(SUBSTITUTE(A2:A10,D1,"")))/LEN(D1)=1,COUNTIF(A2:A10,"*,"&D1)+COUNTIF(A2:A10,D1)>0),"n/a",TRUE))

注意事项 #

• 旧版本公式仅支持单元格内多值用英文逗号分隔的场景
• 若使用其他分隔符，可把公式里的","替换为你实际用的分隔符即可

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