2013年3月14日 星期四

Excel-快速找出儲存格是否有重覆內容(COUNTIF,陣列公式)

有同仁問到:如何能快速在一堆數值資料中判斷其中是否有數值重覆出現?(參考下圖)

首先:

如果想要以一個儲存格公式即能判斷,則必須使用陣列公式:

儲存格E2:{=IF(SUM(COUNTIF(A2:C10,A2:C10))>COUNT(A2:C10),"有","無")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

SUM(COUNTIF(A2:C10,A2:C10)):利用陣列公式計算每一個儲存格數值在整個陣列中的個數總和,若有重覆的數值,則該數值的 COUNTIF 結果會大有於 1。

如果沒有任何重覆的數值,則 SUM(COUNTIF(A2:C10,A2:C10)) 會和 COUNT(A2:C10) 相等。

其次:

如果想要讓數值中有重覆的數值以紅色表示,則可以利用設定格式化條件的方式。

1. 選取所有數值的儲存格。

2. 在[常用/設定格式化條件]中新增一個規則,選取[使用公式來決定要格式化哪些儲存格]項。

3. 輸入公式:=COUNTIF($A$2:$C$10,A2)>1

4. 設定紅色字。

8 則留言:

  1. 你好~想請問 這方法是立即的嗎??
    我的資料有200萬筆要做比對
    我將=IF(SUM(COUNTIF(A1:T100000,A1:T100000))>COUNT(A1:T100000),"有","無")
    直接貼上 他顯示無 不知道是不是正確的XDD

    回覆刪除
    回覆
    1. 這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

      刪除
  2. 結果我 Ctrl+Shift+Enter 就當機了!!!!
    不知道是不是資料太多
    200萬筆資料

    回覆刪除
    回覆
    1. 200萬筆的確是太多了...,你應該不要使用陣列公式,用其他方式一樣可以判定。

      刪除
    2. 作者已經移除這則留言。

      刪除
  3. 還有什麼方法 可以否教一下?

    回覆刪除
  4. 如果無法使用陣列公式, 可以改採
    =IF(SUMPRODUCT(COUNTIF(範圍,範圍))>COUNT(範圍),"有","無")
    不過200萬筆不管用任何公式都會很慢,你要有心理準備。

    回覆刪除

好康東東