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

計算缺失值

假設您有兩個列表,要計算一個列表中不存在於另一個列表中的值的總數,您可以使用 SUMPRODUCT 在公式的幫助下 MATCH 和 ISNA 函數,或 COUNTIF 的功能。

計算缺失值 1

使用 SUMPRODUCT、MATCH 和 ISNA 計算缺失值
使用 SUMPRODUCT 和 COUNTIF 計算缺失值


使用 SUMPRODUCT、MATCH 和 ISNA 計算缺失值

要計算 列表 A 中缺少的列表 B 中的值的總數 如上所示,可以先使用 MATCH 函數返回一個數組,該數組包含列表 B 中的值在列表 A 中的相對位置。如果列表 A 中不存在某個值,則會返回 #N/A 錯誤。 然後 ISNA 函數將識別 #N/A 錯誤,SUMPRODUCT 將計算錯誤總數。

通用語法

=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))

  • range_to_count: 計算缺失值的範圍。 這裡指的是列表 B。
  • 查找範圍: 要比較的範圍 範圍到計數. 這裡指的是列表A。
  • 0: 匹配類型 0 強制 MATCH 執行精確匹配。

要計算 列表 A 中缺少的列表 B 中的值的總數, 請複製或在H6單元格中輸入以下公式,然後按 Enter 得到結果:

=SUMPRODUCT(--ISNA(匹配(F6:F8,B6:B10,0)))

計算缺失值 2

公式說明

=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))

  • 比賽(F6:F8,B6:B10,0,XNUMX): 匹配類型 0 強制 MATCH 函數返回數值,這些數值指示值在單元格中的相對位置 F6F8 範圍中 B6:B10. 如果列表 A 中不存在值,將返回 #N/A 錯誤。 因此,結果將在這樣的數組中: {2;3;#N/A}.
  • 國際標準協會(比賽(F6:F8,B6:B10,0,XNUMX)) = 國際標準協會({2;3;#N/A}): ISNA 會檢查某個值是否為“#N/A”錯誤。 如果是,該函數將返回一個 TURE; 如果不是,它將返回 FALSE。 因此,ISNA 公式將返回 {假;假;真}.
  • SUMPRODUCT(--國際標準協會(比賽(F6:F8,B6:B10,0,XNUMX))) = SUMPRODUCT(--{假;假;真}): 雙負號將 TRUE 轉換為 1,將 FALSE 轉換為 0: {0; 1; 0}. 然後 SUMPRODUCT 函數返回總和: 1.

使用 SUMPRODUCT 和 COUNTIF 計算缺失值

要計算 列表 A 中缺少的列表 B 中的值的總數,您還可以使用 COUNTIF 函數來判斷列表 A 中是否存在值,條件是“=0”,因為如果缺少值,則會生成 0。 然後 SUMPRODUCT 將計算缺失值的總數。

通用語法

=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))

  • 查找範圍: 要比較的範圍 範圍到計數. 這裡指的是列表A。
  • range_to_count: 計算缺失值的範圍。 這裡指的是列表 B。
  • 0: 匹配類型 0 強制 MATCH 執行精確匹配。

要計算 列表 A 中缺少的列表 B 中的值的總數, 請複製或在H6單元格中輸入以下公式,然後按 Enter 得到結果:

=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))

計算缺失值 3

公式說明

=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))

  • 計數(B6:B10,F6:F8): COUNTIF 函數通過計算單元格的出現次數 F6F8 範圍中 B6:B10. 結果將在這樣的數組中: {1; 1; 0}.
  • -(計數(B6:B10,F6:F8)= 0) = -({1; 1; 0}= 0): 片段 {1;1;0}=0 產生一個 TRUE 和 FALSE 數組 {假;假;真}. 然後,雙負號將 TRUE 變為 1,將 FALSE 變為 0。 最終的數組是這樣的: {0; 0; 1}.
  • 總和(-(計數(B6:B10,F6:F8)= 0)) = 總和({0; 0; 1}): 然後 SUMPRODUCT 返回總和: 1.

相關功能

Excel SUMPRODUCT函數

在Excel中,可以使用SUMPRODUCT函數將兩個或多個列或數組相乘,然後得到乘積之和。 實際上,SUMPRODUCT是有用的功能,可以幫助使用多個條件(例如COUNTIFS或SUMIFS函數)對單元格值進行計數或求和。 本文將介紹此SUMPRODUCT函數的函數語法和一些示例。

Excel MATCH功能

Excel MATCH 函數在單元格範圍內搜索特定值,並返回該值的相對位置。

Excel COUNTIF 函數

COUNTIF 函數是 Excel 中的一個統計函數,用於統計符合條件的單元格數量。 它支持邏輯運算符(<>、=、> 和 <),以及用於部分匹配的通配符(? 和 *)。


相關公式

查找缺失值

在某些情況下,您需要比較兩個列表以檢查 Excel 中的列表 B 中是否存在列表 A 的值。 例如,您有一個產品列表,您想檢查您的列表中的產品是否存在於您的供應商提供的產品列表中。 為了完成這項任務,我們在下面列出了三種方法,請隨意選擇您喜歡的一種。

計數等於

本文將重點介紹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)
還沒有評分。 成為第一位評論!
這裡還沒有評論
留下你的意見
以訪客身份發帖
×
評價此帖子:
0   字符
推薦地點