計算遺漏值數量

假設您有兩個清單，若要計算其中一個清單中不存在於另一個清單的值總數，可使用結合 SUMPRODUCT 函數與 MATCH 及 ISNA 函數的公式，或直接運用 COUNTIF 函數，輕鬆達成目標！

使用 SUMPRODUCT、MATCH 與 ISNA 計算遺漏值數量
使用 SUMPRODUCT 與 COUNTIF 計算遺漏值數量

使用 SUMPRODUCT、MATCH 與 ISNA 計算遺漏值數量

若要計算 清單 B 中遺漏自清單 A 的值總數（如上圖所示），可先運用 MATCH 函數，取得清單 B 各值在清單 A 中的相對位置陣列。若某值不存在於清單 A 中，MATCH 將傳回 #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;0;1}。接著，SUMPRODUCT 函數會傳回總和：1.

使用 SUMPRODUCT 與 COUNTIF 計算遺漏值數量

若要計算 清單 B 中遺漏自清單 A 的值總數，也可運用 COUNTIF 函數，透過條件「=0」判斷某個值是否不存在於清單 A 中——因為當該值遺漏時，COUNTIF 會傳回 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.

相關函數

相關公式