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

通常，使用 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 个值。数组中的每个数字表示 “count” 或 “blank” 在 B3:B11 每个单元格中的第一个字符的位置。
• 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。

相关函数

相关公式