計算不包含很多值的單元格數量

通常，使用 COUNTIF 函數很容易對不包含一個特定值的單元格進行計數。這篇文章將逐步介紹如何計算 Excel 中指定範圍內不包含許多值的單元格數量。

如下圖所示，要計算 B3:B11 中不包含 D3:D4 中列出的值的單元格，您可以執行以下操作。

通用公式

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

參數

範圍（必需）：要計算不包含很多值的單元格的範圍。

標準範圍 （必需）：該範圍包含您在計算單元格時要排除的值。

備註： 此公式必須作為數組公式輸入。應用公式後，如果公式周圍有大括號，則成功創建數組公式。

如何使用這個公式？

1. 選擇一個空白單元格輸出結果。

2.在其中輸入以下公式，然後按 按Ctrl + 轉移 + 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 的方向並返回 {“計數”，“空白”};
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 個值。數組中的每個數字表示 B3:B11 的每個單元格中“count”或“blank”的第一個字符的位置。
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}):這兩個減號將“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。然後你會得到一個新的數組 {假；假；真；真；真；假，假，真；真}.
1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}:由於我們只需要計算不包含指定值的單元格，我們需要通過從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。