Note: The other languages of the website are Google-translated. Back to English

計算不等於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中處理這個任務的公式。

最佳辦公效率工具

Kutools for Excel - 助您脫穎而出

您想快速完美地完成日常工作嗎? Kutools for Excel 帶來 300 項強大的高級功能(合併工作簿、按顏色求和、拆分單元格內容、轉換日期等...),為您節省 80% 的時間。

  • 專為1500種工作場景設計,幫你解決80%的Excel問題。
  • 每天減少數千次鍵盤和鼠標的點擊,減輕您疲倦的眼睛和手的疲勞。
  • 在3分鐘內成為Excel專家。 不再需要記住任何痛苦的公式和VBA代碼。
  • 30天無限制免費試用。 60天退款保證。 免費升級和支持2年。
Excel的功能區(與 Kutools for Excel 已安裝)

Office選項卡-在Microsoft Office(包括Excel)中啟用選項卡式閱讀和編輯

  • 一秒鐘即可在數十個打開的文檔之間切換!
  • 每天為您減少數百次鼠標單擊,告別鼠標手。
  • 查看和編輯多個文檔時,將您的工作效率提高 50%。
  • 像Chrome,Firefox和新的Internet Explorer一樣,為Office(包括Excel)帶來高效選項卡。
Excel的屏幕截圖(已安裝Office選項卡)
按評論排序
留言 (0)
還沒有評分。 成為第一位評論!
這裡還沒有評論

關注我們

版權所有©2009 - 萬維網。extendoffice.com。 | 版權所有。 供電 ExtendOffice。 |
Microsoft和Office徽標是Microsoft Corporation在美國和/或其他國家的商標或註冊商標。
受Sectigo SSL保護