跳到主要內容

計算不等於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 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項  |  刪除空白行  |  合併列或儲存格而不遺失數據  |  沒有公式的回合 ...
超VLookup: 多重標準  |  多重價值  |  跨多頁  |  模糊查詢...
副詞。 下拉清單: 簡易下拉列表  |  依賴下拉列表  |  多選下拉列表...
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  將列與 選擇相同和不同的單元格 ...
特色功能: 網格焦點  |  設計圖  |  大方程式酒吧  |  工作簿和工作表管理器 | 資源庫 （自動文字）  |  日期選擇器  |  合併工作表  |  加密/解密單元格  |  按清單發送電子郵件  |  超級濾鏡  |  特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符 ...）  |  50+ 圖表 類型 (甘特圖 ...）  |  40+ 實用 公式 (根據生日計算年齡 ...）  |  19 插入 工具 (插入二維碼, 從路徑插入圖片 ...）  |  12 轉化 工具 (數字到單詞, 貨幣兌換 ...）  |  7 合併與拆分 工具 (高級合併行, 拆分 Excel 儲存格 ...）  |  ... 和更多

Kutools for Excel 擁有超過 300 個功能， 確保只需點擊一下即可獲得您所需要的...

產品描述

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

  • 一秒鐘即可在數十個打開的文檔之間切換！
  • 每天為您減少數百次鼠標單擊，告別鼠標手。
  • 查看和編輯多個文檔時，將您的工作效率提高 50%。
  • 為 Office（包括 Excel）帶來高效的選項卡，就像 Chrome、Edge 和 Firefox 一樣。
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations