計算缺失值

假設您有兩個列表，要計算一個列表中不存在於另一個列表中的值的總數，您可以使用 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。
查找範圍： 要比較的範圍 範圍到計數. 這裡指的是列表A。
0: 匹配類型 0 強制 MATCH 執行精確匹配。

要計算 列表 A 中缺少的列表 B 中的值的總數, 請複製或在H6單元格中輸入以下公式，然後按 Enter 得到結果：

=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))

查找範圍： 要比較的範圍 範圍到計數. 這裡指的是列表A。
range_to_count： 計算缺失值的範圍。這裡指的是列表 B。
0: 匹配類型 0 強制 MATCH 執行精確匹配。

要計算 列表 A 中缺少的列表 B 中的值的總數, 請複製或在H6單元格中輸入以下公式，然後按 Enter 得到結果：

=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.