计算缺失值

假设您有两个列表，要计算一个列表中不存在于另一个列表中的值的总数，您可以使用 SUMPRODUCT 在公式的帮助下 MATCH 和 ISNA 函数，或 COUNTIF 功能。

计算缺失值 1

使用 SUMPRODUCT、MATCH 和 ISNA 计算缺失值
使用 SUMPRODUCT 和 COUNTIF 计算缺失值

使用 SUMPRODUCT、MATCH 和 ISNA 计算缺失值

要计算 列表 A 中缺少的列表 B 中的值的总数 如上所示，可以先使用 MATCH 函数返回一个数组，该数组包含列表 B 中的值在列表 A 中的相对位置。如果列表 A 中不存在某个值，则会返回 #N/A 错误。然后 ISNA 函数将识别 #N/A 错误，SUMPRODUCT 将计算错误总数。

通用语法

=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))

range_to_count： 计算缺失值的范围。这里指的是列表 B。
查找范围： 要比较的范围 range_to_count. 这里指的是列表A。
0: 冥界 匹配类型 0 强制 MATCH 执行精确匹配。

要计算 列表 A 中缺少的列表 B 中的值的总数, 请复制或在H6单元格中输入以下公式，然后按输入得到结果：

=SUMPRODUCT(--ISNA(匹配(F6：F8,B6：B10，0）））

计算缺失值 2

公式说明

=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))

比赛（F6：F8，B6：B10,0，XNUMX）: 冥界 匹配类型 0 强制 MATCH 函数返回数值，这些数值指示值在单元格中的相对位置 F6 至 F8 范围中 B6：B10. 如果列表 A 中不存在值，将返回 #N/A 错误。因此，结果将在这样的数组中： {2;3;#N/A}.
国际标准协会(比赛（F6：F8，B6：B10,0，XNUMX）) = 国际标准协会({2;3;#N/A}): ISNA 会检查某个值是否为“#N/A”错误。如果是，该函数将返回一个 TURE；如果不是，它将返回 FALSE。因此，ISNA 公式将返回 {假；假；真}.
SUMPRODUCT(--国际标准协会(比赛（F6：F8，B6：B10,0，XNUMX）)) = SUMPRODUCT(--{假；假；真}): 双负号将 TRUE 转换为 1，将 FALSE 转换为 0： {0; 1; 0}. 然后 SUMPRODUCT 函数返回总和： 1.

使用 SUMPRODUCT 和 COUNTIF 计算缺失值

要计算 列表 A 中缺少的列表 B 中的值的总数，您还可以使用 COUNTIF 函数来判断列表 A 中是否存在值，条件是“=0”，因为如果缺少值，则会生成 0。然后 SUMPRODUCT 将计算缺失值的总数。

通用语法

=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))

查找范围： 要比较的范围 range_to_count. 这里指的是列表A。
range_to_count： 计算缺失值的范围。这里指的是列表 B。
0: 冥界 匹配类型 0 强制 MATCH 执行精确匹配。

要计算 列表 A 中缺少的列表 B 中的值的总数, 请复制或在H6单元格中输入以下公式，然后按输入得到结果：

=SUMPRODUCT(--(COUNTIF(B6：B10,F6：F8)=0))

计算缺失值 3

公式说明

=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))

计数(B6:B10,F6:F8): COUNTIF 函数通过计算单元格的出现次数 F6 至 F8 范围中 B6：B10. 结果将在这样的数组中： {1; 1; 0}.
-（计数(B6:B10,F6:F8)= 0） = -（{1; 1; 0}= 0）: 片段 {1;1;0}=0 产生一个 TRUE 和 FALSE 数组 {假；假；真}. 然后，双负号将 TRUE 变为 1，将 FALSE 变为 0。最终的数组是这样的： {0; 0; 1}.
总和(-（计数(B6:B10,F6:F8)= 0）) = 总和({0; 0; 1}): 然后 SUMPRODUCT 返回总和： 1.