统计不包含多个值的单元格数量

通常，您可以通过 COUNTIF 函数轻松统计不包含某一特定值的单元格数量。本文将以 Excel 的指定区域为例，详细介绍如何统计不包含多个值的单元格数。

如何统计不包含多个指定值的单元格数量？

如下图所示，如果您需要统计 B3:B11 区域中不包含 D3:D4 列表值的单元格数量，可按照以下方法操作。

通用公式

{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>0))}

参数说明

注意：此公式需以数组公式方式输入。输入后，若公式被花括号包裹，即表示数组公式已创建成功。

如何使用该公式？

1. 选择一个空白单元格，以便输出结果。

2. 在该单元格中输入以下公式，并同时按下 Ctrl+Shift+Enter 组合键，即可得到结果。

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

这些公式是如何运作的？

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

1）--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11)))：

• TRANSPOSE(D3:D4)：TRANSPOSE 函数将 D3:D4 的方向进行旋转，并返回 {“count”，“blank”}；
• SEARCH({“count”,”blank”},B3:B11)：此处的 SEARCH 函数用于查找区域 B3:B11 中子字符串“count”和“blank”的位置，并以数组形式返回结果，如 {#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,
  #VALUE!;1,7}。
• 在本例中，B3:B11 区域内的每个单元格都会被检索两次，因为在计数时需要排除两个值，因此您将在数组中获得 18 个结果。数组中的每个数字代表 B3:B11 区域内每个单元格中“count”或“blank”首字母出现的位置。
• ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
  #VALUE!;1,#VALUE!;1,7}：ISNUMBER 函数在数组中遇到数字时返回 TRUE，遇到错误时返回 FALSE。此处返回的结果为 {FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;
  TRUE,TRUE}。
• --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,）
  FALSE;TRUE,TRUE})：两个减号可将“TRUE”转为 1，将“FALSE”转为 0. 此时您将得到新的数组 {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}。

2）ROW(D3:D4)^0：ROW 函数返回所选单元格的行号：{3;4}，随后幂运算符（^）将 3 和 4 分别计算为 0 次幂，最终结果为 {1;1}。

3）MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1})MMULT 函数对这两个数组进行矩阵乘积运算，结果为：{0;0;1;1;1;0;0;1;2}，与原始数据相符。数组中任意非零数字表示已找到至少一个需排除的字符串，若为零则表示未找到需排除的字符串。

4）SUM(1-{0;0;1;1;1;0;0;1;2}>0)：

• {0;0;1;1;1;0;0;1;2}>0：此处会检查数组中的每个数字是否大于 0. 大于 0 时返回 TRUE，否则返回 FALSE。最终，您将获得新的数组 {FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}。
• 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}：由于我们只需统计不包含指定值的单元格，因此需要用 1 对数组中的这些值取反。此时，数学运算会自动将 TRUE 和 FALSE 转换为 1 和 0，最终结果如下 {1;1;0;0;0;1;1;0;0}。
• SUM{1;1;0;0;0;1;1;0;0}：SUM 函数会对数组中的所有数字进行求和，并将最终结果返回为 4.

相关函数

相关公式