Excel公式：检查单元格是否包含多个值中的一个但排除其他值

假设有两个值列表，您想检查单元格B3是否包含范围E3:E5中的某个值，但同时不包含范围F3:F4中的任何值，如下截图所示。本教程将提供一个公式来快速处理此任务，并解释公式的参数。

通用公式：

参数

返回值：

公式返回1或0。当单元格包含需要包含的值之一且不包含任何需要排除的值时，返回1，否则返回0。在此公式中，1和0被视为逻辑值TRUE和FALSE。

公式工作原理

假设您要检查单元格B3是否包含范围E3:E5中的某个值，但同时排除范围F3:F4中的值，请使用以下公式：

按下 Enter 键以获取检查结果。

解释

第一部分：(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) 检查单元格是否包含E3:E5中的值

SEARCH函数：SEARCH函数返回一个文本字符串在另一个文本字符串中的第一个字符的位置。如果SEARCH函数找到匹配的文本，则返回相对位置；如果没有找到，则返回#VALUE! 错误。例如，这里的公式SEARCH($E$3:$E$5,B3)将在单元格B3中搜索范围E3:E5中的每个值，并返回每个文本字符串在单元格B3中的位置。它将返回这样的数组结果：{1;7;12}。

ISNUMBER函数：ISNUMBER函数在单元格为数字时返回TRUE。因此ISNUMBER(SEARCH($E$3:$E$5,B3))将返回数组结果{true,true,true}，因为SEARCH函数找到了3个数字。

--ISNUMBER(SEARCH($E$3:$E$5,B3)) 将TRUE值转换为1，将FALSE值转换为0，因此该公式将数组结果更改为{1;1;1}。

SUMPRODUCT函数：用于将多个范围相乘或将数组相加并返回乘积的总和。SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) 返回 1+1+1=3。

最后，比较左侧公式 SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) 与0进行比较，只要左侧公式的结果大于0，结果将为TRUE，否则返回FALSE。这里返回TRUE。

第二部分：(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) 检查单元格是否不包含F3:F4中的值

公式SEARCH($F$3:$F$4,B3)将在单元格B3中搜索范围E3:E5中的每个值，并返回每个文本字符串在单元格B3中的位置。它将返回这样的数组结果：{#VALUE!;#VALUE!}。

ISNUMBER(SEARCH($F$3:$F$4,B3)) 将返回数组结果{false;false}，因为SEARCH函数未找到任何数字。

--ISNUMBER(SEARCH($F$3:$F$4,B3)) 将TRUE值转换为1，将FALSE值转换为0，因此该公式将数组结果更改为{0;0}。

SUMPRODUCT函数：用于将多个范围相乘或将数组相加并返回乘积的总和。 SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) 返回 0+0=0。

最后，比较左侧公式 SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) 与0进行比较，只要左侧公式的结果等于0，结果将返回TRUE，否则返回FALSE。这里返回TRUE。

第三部分：将两个公式相乘

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

在此公式中，1和0被视为逻辑值TRUE和FALSE。

示例文件

点击下载示例文件

相关公式

• 检查单元格是否包含特定文本，若要检查单元格是否包含范围A中的某些文本但不包含范围B中的文本，您可以使用结合COUNT、SEARCH和AND函数的数组公式。
• 检查单元格是否包含多个内容之一，本教程提供了一个公式，用于检查单元格是否包含Excel中的多个值之一，并解释了公式中的参数以及公式的工作原理。
• 检查单元格是否包含多个内容之一，假设在Excel中，列E中有一组值，您想检查列B中的单元格是否包含列E中的所有值，并返回TRUE或FALSE。
• 检查单元格是否包含数字，有时您可能希望检查单元格是否包含数字字符。本教程提供了一个公式，如果单元格包含数字则返回TRUE，如果单元格不包含数字则返回FALSE。