計算不等於Excel中許多值的單元格數量
在 Excel 中,您可以使用 COUNTIF 函數輕鬆獲取不等於特定值的單元格數量,但是您是否嘗試過計算不等於多個值的單元格? 例如,我想獲取 A 列中產品的總數,但排除 C4:C6 中的特定項目,如下圖所示。 這篇文章,我將介紹一些在Excel中解決這個工作的公式。
使用公式1計算不等於許多值的單元格數量
要計算不等於許多指定值的單元格,可以使用 MATCH、ISNA 和 SUMPRODUCT 函數的組合,通用語法為:
= SUMPRODUCT ( -- ( ISNA ( MATCH ( range, exclude_values, 0)))
- range:要計數的單元格列表;
- exclude_values:要從範圍列表中忽略的值。
1。 將以下公式輸入或複製到要輸出結果的空白單元格中:
=SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))
2。 然後按 Enter 鍵以獲取正確的結果,請參見屏幕截圖:
公式說明:
- 匹配(A2:A13,C2:C4,0):此 MATCH 函數以數字形式返回每個匹配值的位置,並在未找到的情況下返回 #N/A 錯誤值。 這意味著,當 A2:A13 中的值等於 C2:C4 中的值時,它將返回一個數字,否則返回一個 #NA 錯誤。 這裡的結果是:{1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #不適用; #不適用;}。
- ISNA(MATCH(A2:A13,C2:C4,0))= ISNA({1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}):此 ISNA 函數強制錯誤值變為 TRUE,數字變為 FASLE,因此您將得到:{FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE} .
- --(ISNA(MATCH(A2:A13,C2:C4,0))))=--({假;真;真;假;假;假;真;真;假;真;真;真}): -- 雙負運算符將所有 TRUE 轉換為 1,將 FALSE 轉換為 0,如下所示: SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))=SUMPRODUCT({0;1;1 ;0;0;0;1;1;0;1;1;1;})。
- 然後使用 SUMPRODUCT 函數對數組中的數字求和,得到結果:7。
使用公式2計算不等於許多值的單元格數量
要在 Excel 中解決此工作,您還可以應用 COUNTA、SUMPRODUCT 和 COUNTIF 函數的組合,通用語法為:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude_values))
- range:要計數的單元格列表;
- exclude_values:要從範圍列表中忽略的值。
1. 請將以下公式輸入或複製到空白單元格中以放置結果:
=COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))
2。 然後按 Enter 獲得結果的鍵,如下圖所示:
公式說明:
- 計數(A2:A13):此 COUNTA 函數返回範圍 A2:A13 中所有非空單元格的數量。 它會得到結果:12。
- 計數(A2:A13,C2:C4): 這個 COUNTIF 函數計算區域 A2:A13 中與 C2:C4 中的條件相等的單元格,所以,你會得到這樣的結果:{2;1;2} 這意味著 Apple 出現兩次,Grape 出現一次,Lemon 出現兩次。
- SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=SUMPRODUCT({2;1;2}):此 SUMPRODUCT 函數對數組中的所有項求和並返回最終結果:5。
- COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=12-5 :SUMPRODUCT 函數的結果 (5) 從所有非空單元格 (12) 的計數中減去,以獲得您需要的最終結果。
注意:以上兩個公式都不區分大小寫。
使用的相對功能:
- MATCH:
- MATCH 函數在單元格範圍內搜索特定值,並返回該值的相對位置。
- 伊納:
- ISNA 函數用於檢查公式是否返回 #N/A 錯誤。
- SUMPRODUCT:
- SUMPRODUCT函數可用於將兩個或多個列或數組相乘,然後獲得乘積之和。
- COUNTIF:
- COUNTIF函數是Excel中的統計函數,用於計算符合條件的單元格數。
- COUNTA:
- COUNTA 函數返回除空單元格之外的單元格數。
更多文章:
- 計算單元格數等於 Excel 中的多個值之一
- 假設,我在 A 列中有一個產品列表,現在,我想從 A 列中獲取列在 C4:C6 範圍內的特定產品 Apple、Grape 和 Lemon 的總數,如下圖所示。 通常,在 Excel 中,簡單的 COUNTIF 和 COUNTIFS 函數在這種情況下不起作用。 這篇文章,我將談談如何通過 SUMPRODUCT 和 COUNTIF 函數的組合快速輕鬆地解決這個工作。
- 計算Excel中兩個值/日期之間的單元格數
- 您是否嘗試過獲取或計算 Excel 中兩個給定數字或日期之間的單元格數量,如下圖所示? 本文將討論一些處理它的有用公式。
- 計算不在Excel中兩個給定數字之間的單元格數
- 計算兩個數字之間的單元格數量是我們在 Excel 中的一項常見任務,但在某些情況下,您可能想要計算不在兩個給定數字之間的單元格。 例如,我有一個從周一到週日銷售的產品列表,現在,我需要獲取不在特定低值和高值之間的單元格數量,如下圖所示。 這篇文章,我將介紹一些在Excel中處理這個任務的公式。
最佳辦公效率工具
Kutools for Excel - 助您脫穎而出
您想快速完美地完成日常工作嗎? Kutools for Excel 帶來 300 項強大的高級功能(合併工作簿、按顏色求和、拆分單元格內容、轉換日期等...),為您節省 80% 的時間。
- 專為1500種工作場景設計,幫你解決80%的Excel問題。
- 每天減少數千次鍵盤和鼠標的點擊,減輕您疲倦的眼睛和手的疲勞。
- 在3分鐘內成為Excel專家。 不再需要記住任何痛苦的公式和VBA代碼。
- 30天無限制免費試用。 60天退款保證。 免費升級和支持2年。

Office選項卡-在Microsoft Office(包括Excel)中啟用選項卡式閱讀和編輯
- 一秒鐘即可在數十個打開的文檔之間切換!
- 每天為您減少數百次鼠標單擊,告別鼠標手。
- 查看和編輯多個文檔時,將您的工作效率提高 50%。
- 像Chrome,Firefox和新的Internet Explorer一樣,為Office(包括Excel)帶來高效選項卡。
