Excel公式需求：跨工作表检查指定区域是否含非允许值

核心公式（兼容Excel 365/2021及旧版） #

=IF(SUMPRODUCT(--(NOT(ISNUMBER(IFERROR(MATCH(SheetB!A1:C10,{"","Y","Good","N/A"},0),FALSE))))) + SUMPRODUCT(--(NOT(ISNUMBER(IFERROR(MATCH(SheetC!A1:C10,{"","Y","Good","N/A"},0),FALSE)))))>0,"Contains","Does not contain")

公式逻辑拆解 #

• MATCH(单元格区域, {"","Y","Good","N/A"}, 0)：逐个检查单元格值是否属于允许的内容列表，匹配成功返回对应位置，失败返回#N/A
• IFERROR(..., FALSE)：将匹配失败的#N/A转为FALSE，避免公式因错误值中断计算
• ISNUMBER(...)：将MATCH结果转为布尔值，匹配成功为TRUE，否则为FALSE
• NOT(...)：反转布尔值，标记出不在允许列表的单元格（返回TRUE）
• --(...)：将布尔值转为数字（TRUE→1，FALSE→0）
• SUMPRODUCT(...)：统计单个区域中不符合要求的单元格数量，两个区域的统计结果相加
• IF(总数>0, ...)：如果存在不符合的内容，返回"Contains"，否则返回"Does not contain"

简化版（仅适用于区域无错误值的场景） #

=IF(SUMPRODUCT(--(NOT(ISNUMBER(MATCH(SheetB!A1:C10,{"","Y","Good","N/A"},0))))) + SUMPRODUCT(--(NOT(ISNUMBER(MATCH(SheetC!A1:C10,{"","Y","Good","N/A"},0)))))>0,"Contains","Does not contain")

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