計算不含多個指定值的儲存格數量

一般來說，使用 COUNTIF 函數計算不含單一特定值的儲存格數量相當簡單。本文將逐步說明如何在 Excel 的指定區域中，計算不含多個特定值的儲存格數量。

如何計算不包含多個指定值的儲存格？

如下圖所示，若要計算 B3:B11 範圍中不包含 D3:D4 所列值的儲存格數量，請依下列步驟操作。

通用公式

{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>0))}

引數

注意：此公式必須以陣列公式方式輸入。套用後，若公式前後出現大括號，即表示已成功建立陣列公式！

如何使用此公式？

1. 選取一個空白儲存格，用來輸出結果。

2. 輸入下列公式，然後同時按下 Ctrl+Shift+Enter 鍵，立即取得結果！

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

這些公式是如何運作的？

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

1) --(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11)))：

• TRANSPOSE(D3:D4)：TRANSPOSE 函數會旋轉 D3:D4 的方向，並傳回 {“count”,”blank”}；
• SEARCH({“count”,”blank”},B3:B11)：此處的 SEARCH 函數會在範圍 B3:B11 中搜尋子字串「count」與「blank」的位置，並傳回以下陣列：{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,
  #VALUE!;1,7}。
• 在此情況下，由於計數時需排除兩個值，B3:B11 中的每個儲存格將被搜尋兩次，因此您會在陣列中得到 18 個值。陣列中的每個數字代表「count」或「blank」在 B3:B11 各儲存格中首個字元的位置。
• ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
  #VALUE!;1,#VALUE!;1,7}：ISNUMBER 函數在陣列中遇到數字時傳回 TRUE，遇到錯誤時傳回 FALSE。此處傳回的結果為 {FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;
  TRUE,TRUE}。
• --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,）
  FALSE;TRUE,TRUE})：這兩個負號會將「TRUE」轉換為 1，並將「FALSE」轉換為 0. 此處您將得到一個新陣列：{0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}。

2)ROW(D3:D4)^0：ROW 函數會傳回儲存格參照所對應的列號 {3;4}，接著指數運算子（^）會將 3 與 4 分別取 0 次方，最終傳回結果：{1;1}。

3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1})：MMULT 函數會傳回這兩個陣列的矩陣乘積：{0;0;1;1;1;0;0;1;2}，以符合原始資料。陣列中任何非零數值代表至少找到一個排除字串，而零則表示未找到任何排除字串。

4) SUM(1-{0;0;1;1;1;0;0;1;2}>0)：

• {0;0;1;1;1;0;0;1;2}>0：此處會檢查陣列中的每個數字是否大於 0. 若數字大於 0，則傳回 TRUE；否則傳回 FALSE，並生成一個新陣列：{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE}。
• 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}：由於我們只需計算不含指定值的儲存格數量，因此必須將陣列中的 TRUE/FALSE 值反轉——做法是用 1 減去這些值。此處的數學運算子會自動將 TRUE 和 FALSE 轉換為 1 和 0，最終傳回結果：{1;1;0;0;0;1;1;0;0}。
• SUM{1;1;0;0;0;1;1;0;0}SUM 函數會將陣列中的所有數字加總，並傳回最終結果 4.

相關函數

相關公式