统计缺失值

假如您有两个列表，需要统计其中一个列表中所有不在另一个列表中的值的总数，您可以使用 SUMPRODUCT 公式，结合 MATCH 和 ISNA 函数，或使用 COUNTIF 函数轻松实现。

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

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

统计 列表 B 中在列表 A 中缺失的所有值的总数。如上图所示，您可以先用 MATCH 函数返回列表 B 中每个值在列表 A 中的相对位置，如果某个值未出现在列表 A 中，则会返回#N/A 错误。随后，ISNA 函数可识别这些#N/A 错误，SUMPRODUCT 则会统计这些错误的总数。

通用语法

=SUMPRODUCT(--ISNA(MATCH(）））range_to_count,lookup_range,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)：match_type 0 可让 MATCH函数返回 F6 到 F8 中每个值在 B6:B10 范围内的相对位置。如果某个值在列表 A 中不存在，则会返回#N/A 错误。因此，结果将得到如下数组：{2;3;#N/A}。
• ISNA(）MATCH(F6:F8,B6:B10,0))=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(）））lookup_range,range_to_count)=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)=——-（{1;1;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.

相关函数

相关公式