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

計算Excel中兩列之間的所有匹配/重複項

比較兩列數據併計算兩列中的所有匹配項或重複項可能是我們大多數人的常見任務。 例如,您有兩列名稱,有些名稱同時出現在第一列和第二列中,現在,您要計算兩列之間所有匹配的名稱(位於兩列內任何位置的匹配項),如下圖所示,本教程將介紹一些在 Excel 中實現這一目標的公式。


使用 SUMPRODUCT 和 COUNTIF 函數計算兩列之間的所有匹配項

要計算兩列之間的所有匹配項,SUMPRODUCT 和 COUNTIF 函數的組合可以幫助您,通用語法是:

=SUMPRODUCT(COUNTIF(range1,range2))
  • range1, range2:這兩個範圍包含要計算所有匹配項的數據。

現在,請將以下公式輸入或複製到空白單元格中,然後按 Enter 獲得結果的關鍵:

=SUMPRODUCT(COUNTIF(A2:A12,C2:C12))


公式說明:

=SUMPRODUCT(COUNTIF(A2:A12,C2:C12))

  • 計數(A2:A12,C2:C12): 這個 COUNTIF 函數檢查 C 列中的每個名稱是否存在於 A 列中。如果名稱存在,則顯示數字 1,否則顯示數字 0。 該函數將返回結果如下:{1;1;0;0;0;1;0;0;1;0;1}。
  • SUMPRODUCT(COUNTIF(A2:A12,C2:C12))=SUMPRODUCT({1;1;0;0;0;1;0;0;1;0;1}):SUMPRODUCT 函數對這個數組中的所有項求和並得到結果:5。

使用 COUNT 和 MATCH 函數計算兩列之間的所有匹配項

通過 COUNT 和 MATCH 函數的組合,還可以獲取兩列之間的匹配數,通用語法為:

{=COUNT(MATCH(range1,range2,0))}
Array formula, should press Ctrl + Shift + Enter keys together.
  • range1, range2:這兩個範圍包含要計算所有匹配項的數據。

請輸入或複制以下公式到空白單元格中,然後按 Ctrl + Shift + Enter 鍵在一起以獲得正確的結果,請參見屏幕截圖:

=COUNT(MATCH(A2:A12,C2:C12,0))


公式說明:

=計數(匹配(A2:A12,C2:C12,0))

  • 匹配(A2:A12,C2:C12,0): 這個 MATCH 函數將在 C 列中查找 A 列中的名稱,並返回每個匹配值的位置。 如果未找到值,將顯示錯誤值。 所以,你會得到這樣的數組列表:{11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A; 9}。
  • COUNT(MATCH(A2:A12,C2:C12,0))= COUNT({11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A;9}): COUNT 函數會計算數組列表中的數字,得到結果:5。

使用 SUMPRODUCT、ISNUMBER 和 MATCH 函數計算兩列之間的所有匹配項

在 Excel 中,您可以嘗試在兩列中查找匹配項併計數,然後使用 SUMPRODUCT、ISNUMBER 和 MATCH 函數,通用語法為:

=SUMPRODUCT(--(ISNUMBER(MATCH(range1,range2,0))))
  • range1, range2:這兩個範圍包含要計算所有匹配項的數據。

將下面的公式輸入或複製到空白單元格中以輸出結果,然後按 Enter 返回計算的鍵,見截圖:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A12,C2:C12,0))))


公式說明:

= SUMPRODUCT(-(ISNUMBER(MATCH(A2:A12,C2:C12,0)))))

  • 匹配(A2:A12,C2:C12,0): 這個 MATCH 函數將在 C 列中查找 A 列中的名稱,並返回每個匹配值的位置。 如果未找到該值,將顯示錯誤值。 所以,你會得到這樣的數組列表:{11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A; 9}。
  • ISNUMBER(MATCH(A2:A12,C2:C12,0))= ISNUMBER({11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A;9}):這裡,ISNUMBER 函數將數組中的數字轉換為 TRUE,將其他值轉換為 FALSE。 所以,你會得到一個這樣的數組:{TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}。
  • --(ISNUMBER(匹配(A2:A12,C2:C12,0)))=--({TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}): --這個雙負號用於將TRUE值轉換為1,將False值轉換為0並返回結果如下:{1;1;0;0;0;1;1;0;0;0; 1}。
  • SUMPRODUCT(--(ISNUMBER(MATCH(A2:A12,C2:C12,0))))=SUMPRODUCT({1;1;0;0;0;1;1;0;0;0;1}):最後,SUMPRODUCT 函數將把這個數組中的所有項相加並得到結果:5。

使用的相對功能:

  • SUMPRODUCT:
  • SUMPRODUCT函數可用於將兩個或多個列或數組相乘,然後獲得乘積之和。
  • COUNTIF:
  • COUNTIF函數是Excel中的統計函數,用於計算符合條件的單元格數。
  • COUNT:
  • COUNT 函數用於計算包含數字的單元格的數量,或計算參數列表中的數字。
  • MATCH:
  • Microsoft Excel MATCH函數在一系列單元格中搜索特定值,然後返回該值的相對位置。
  • 編號:
  • 當單元格包含數字時,ISNUMBER函數返回TRUE,否則返回FALSE。

更多文章:

  • 計算兩列之間的匹配
  • 例如,我在 A 列和 C 列中有兩個數據列表,現在,我想比較兩列併計算 A 列中的值是否在 C 列的同一行中找到,如下面的屏幕截圖所示。 在這種情況下,SUMPRODUCT 函數可能是您在 Excel 中解決此任務的最佳函數。
  • 計算Excel中包含特定文本的單元格數
  • 假設您有一個文本字符串列表,並且可能想要查找包含某些文本作為其內容一部分的單元格的數量。 在這種情況下,您可以在應用 COUNTIF 函數時使用表示條件中的任何文本或字符的通配符 (*)。 這篇文章,我將介紹如何在Excel中使用公式來處理這項工作。
  • 計算不等於Excel中許多值的單元格數量
  • 在 Excel 中,您可以使用 COUNTIF 函數輕鬆獲取不等於特定值的單元格數量,但是您是否嘗試過計算不等於多個值的單元格? 例如,我想獲取 A 列中產品的總數,但排除 C4:C6 中的特定項目,如下圖所示。 這篇文章,我將介紹一些在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   字符
推薦地點