計算缺失值

假設您有兩個列表，要計算一個列表中不存在於另一個列表中的值的總數，您可以使用 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(要計數的範圍,查找範圍,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 函數來判斷某個值是否存在於列表 A 中，條件為 “=0”，因為當某個值缺失時會生成 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。

相關函數

相關公式