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

假设有两个值列表，您希望判断单元格 B3 是否包含 E3:E5 区域中的任意一个值，并且同时不包含 F3:F4 区域中的任意一个值，如下图所示。本教程将为您提供一个公式，帮助您在 Excel 中快速实现这一操作，并详细解释公式中的各个参数。

通用公式：

参数说明

返回值：

公式返回 1 或 0. 当单元格中包含任意需包含的值且不含需排除的任何值时，返回 1，否则返回 0. 在该公式中，1 和 0 将被视为逻辑值 TRUE 和 FALSE 进行处理。

公式解析

假设您需要判断单元格 B3 是否包含 E3:E5 区域中的任意值，但要同时排除 F3:F4 区域中的值，请使用以下公式

按下 Enter 键即可获取检查结果。

释义

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

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。

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

公式 SEARCH($F$3:$F$4,B3) 会在 B3 单元格中依次查找 F3:F4 区域的每个值，并返回每个字符串在 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。

第 3 部分：将两个公式结果相乘

=(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 中的文本，可以在 Excel 中使用结合 COUNT、SEARCH 和 AND 函数的数组公式
• 检查单元格是否包含多个值中的任意一个
  本教程为您提供了一个可在 Excel 中判断单元格是否包含多个值中任意一个的公式，并详细解析公式参数及其工作原理。
• 检查单元格是否包含某些值
  假设在 Excel 中，E 列有一组值，您需要判断 B 列中的单元格是否包含 E 列的所有值，并返回 TRUE 或 FALSE。
• 检查单元格是否包含数字
  有时，您可能需要判断单元格中是否包含数字字符。本教程为您提供了一个公式，若单元格包含数字则返回 TRUE，否则返回 FALSE。