统计缺失值

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

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

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

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

通用语法

=SUMPRODUCT(--ISNA(MATCH(要统计的范围,查找范围,0)))

要统计列表 B 中缺失于列表 A 的值的总数，请复制或在单元格 H6 中输入以下公式，然后按 Enter 获取结果：

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

公式解释

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

• MATCH(F6:F8,B6:B10,0)：匹配类型 0 强制 MATCH 函数返回表示 F6 至 F8 单元格中的值在范围 B6:B10 中的相对位置的数值。如果某个值不存在于列表 A 中，则会返回 #N/A 错误。因此，结果将是一个类似这样的数组：{2;3;#N/A}。
• ISNA(ISNA(MATCH(F6:F8,B6:B10,0))) = ISNA(ISNA({2;3;#N/A}))： ISNA 用于判断某个值是否为“#N/A”错误。如果是，该函数将返回 TRUE；如果不是，则返回 FALSE。因此，ISNA 公式将返回 {FALSE;FALSE;TRUE}。
• SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0))) = SUMPRODUCT(--{FALSE;FALSE;TRUE})：双负号将 TRUE 转换为 1，将 FALSE 转换为 0：{0;1;0}。然后 SUMPRODUCT 函数返回总和：1。

使用 SUMPRODUCT 和 COUNTIF 统计缺失值

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

通用语法

=SUMPRODUCT(--(COUNTIF(查找范围,要统计的范围)=0))

要统计列表 B 中缺失于列表 A 的值的总数，请复制或在单元格 H6 中输入以下公式，然后按 Enter 获取结果：

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

公式解释

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

• COUNTIF(B6:B10,F6:F8)：COUNTIF 函数统计 F6 至 F8 单元格中的值在范围 B6:B10 中出现的次数。结果将是一个类似这样的数组：{1;1;0}。
• --(--(COUNTIF(B6:B10,F6:F8)=0)=0) = --(--({1;1;0}=0)=0)：片段 {1;1;0}=0 产生一个 TRUE 和 FALSE 数组 {FALSE;FALSE;TRUE}。双负号随后将 TRUE 转换为 1，将 FALSE 转换为 0。最终数组如下：{0;0;1}。
• SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0)) = SUMPRODUCT({0;0;1}):SUMPRODUCT 然后返回总和：1。

相关函数

相关公式