編按：真沒想到！如果不是看了這篇文章，小編絕想不到Excel里還有真假重復(fù)。一直被大家喜歡的查重利器COUUTIF函數(shù)這次失手了，把不是重復(fù)的當成了重復(fù)統(tǒng)計……

前兩天有個小伙伴給我發(fā)了個文檔，說自己被COUNTIF坑了，弄錯重復(fù)被罰了，讓我?guī)退页稣_的重復(fù)值?？偣灿?47行數(shù)據(jù)，如圖：

我一看這不是很簡單嗎？選中A列數(shù)據(jù)，點擊【開始】選項卡下的【條件格式】下拉菜單的“突出顯示單元格規(guī)則”里的“重復(fù)值”。

在【重復(fù)值】窗口進行設(shè)置。

結(jié)果出來了。

很快我就發(fā)現(xiàn)不對了。按住Ctrl+F打開【查找和替換】窗口，查找A2單元格的值，但是系統(tǒng)提示只有“1個單元格被找到”。可是標記紅色的不是重復(fù)值嗎？

這是什么原因呢？我研究了一下發(fā)現(xiàn)：EXCEL默認數(shù)據(jù)只有15位，所以長于15位的部分在數(shù)據(jù)比對時全部視作0。由于這列數(shù)據(jù)長度是16位，那么第16位的數(shù)據(jù)系統(tǒng)統(tǒng)一都會認為是 0，因此出現(xiàn)了這么多重復(fù)值。這就屬于EXCEL中的假重復(fù)。這種情況下，直接按COUNTIF公式常規(guī)用法=COUNTIF($A$2:$A$547,A2)查重肯定失手，這就是小伙伴認為自己被COUNTIF坑的原因。

那長于15位的數(shù)據(jù)如何判斷重復(fù)值呢？接下來給大家介紹幾個使用公式的條件格式來解決這個問題。

1、COUNTIF標注重復(fù)

選中要判斷重復(fù)項的數(shù)據(jù)區(qū)域A2:A547, 點擊【開始】選項卡下的【條件格式】下拉菜單的“新建規(guī)則”。

在【新建格式規(guī)則】窗口選擇“使用公式確定要設(shè)置格式的單元格”，在下方的“編輯規(guī)則說明”里輸入公式“= COUNTIF($A$2:$A$547,A2&"*")>1”，點擊“格式”，選擇填充色為藍色。點擊確定。

這樣，重復(fù)值就被標記出來了。

解析：

= COUNTIF($A$2:$A$547,A2&"*")>1

COUNTIF（指定區(qū)域，條件）對指定區(qū)域中符合條件的單元格進行計數(shù)。指定區(qū)域是對單元格進行計數(shù)的區(qū)域。條件的形式可以是數(shù)字、表達式或文本等。"*"是通配符，代表任意多個字符的文本。

由于EXCEL中超過15位的數(shù)字只能保留15位有效數(shù)字，后面全部視為0，所以“3771000100001197”和“3771000100001192”會被認為是重復(fù)值，因為它們都被當成了數(shù)字“3771000100001190”。這里使用 &"*"將單元格數(shù)字后面統(tǒng)一添加*符號強行當做文本進行識別統(tǒng)計，就可以準確地通過計數(shù)值是否大于1識別出數(shù)字是否重復(fù)。

需要注意的是，該條件格式應(yīng)用的區(qū)域必須從A2開始，同時由于應(yīng)用的是整列單元格所以COUNTIF指定區(qū)域必須加絕對應(yīng)用，而A2則為相對引用。

不過這個公式存在一個缺陷，就是當所在區(qū)域里有空格的時候，也會自動識別為重復(fù)。

這是由于當單元格為空,如上圖A9，A9&"*"的結(jié)果為"*"，公式就變成在$A$2:$A$547區(qū)域中查找"*"，*作為通配符代表任意一個字符，所以使用COUNTIF會統(tǒng)計出所有不為空的單元格的計數(shù)，當然結(jié)果會大于1 ，被標注為重復(fù)。

那有沒有什么方法可以屏蔽空格呢？我們在原有公式的基礎(chǔ)上乘以A2""，把條件格式的公式修改成“=(COUNTIF($A$2:$A$547,A2&"*")*(A2"")>1)”。若單元格為空，A2""返回FALSE，原有結(jié)果跟邏輯值FALSE相乘等于0，0不大于1，自然不會被標注為重復(fù)：

2、SUMPRODUCT標注重復(fù)

選中要判斷重復(fù)項的數(shù)據(jù)區(qū)域A2:A547, 點擊【開始】選項卡下的【條件格式】下拉菜單的“新建規(guī)則”。

在【新建格式規(guī)則】窗口 選擇“使用公式確定要設(shè)置格式的單元格”，在下方的“編輯規(guī)則說明”里輸入公式“=SUMPRODUCT(($A$2:$A$547=A2)*(A2""))>1”，點擊“格式”，選擇填充色為藍色。點擊確定。

完成結(jié)果如下：

解析：

=SUMPRODUCT(($A$2:$A$547=A2)*(A2""))>1

SUMPRODUCT返回多個參數(shù)乘積之和，參數(shù)可以是數(shù)組或者對應(yīng)的區(qū)域。第一個參數(shù)是$A$2:$A$547=A2，表示從A2到A547如果等于A2返回TRUE否則返回FALSE，第二個參數(shù)是A2""，同樣為空則返回FALSE否則返回TRUE，而TRUE代表1，F(xiàn)ALSE代表0，多項乘積之和就是最后得到的該單元格在該區(qū)域的重復(fù)個數(shù)。

同樣該條件格式應(yīng)用的區(qū)域從A2開始，同時由于應(yīng)用的是整列單元格所以$A$2:$A$547必須加絕對應(yīng)用，而A2則為相對引用。

這就是EXCEL中的真假重復(fù)問題，不知道的小伙伴們很容易被系統(tǒng)所迷惑，覺得有用的話趕緊收藏起來吧！

小編的疑惑：為何數(shù)字超過15位后用COUNTIF出現(xiàn)假重復(fù)，而用SUMPRODUCT沒有出現(xiàn)？兩個函數(shù)，都是建立在$A$2:$A$547的值是否等于A2的判斷上進行的，為何COUNTIF會出現(xiàn)假重復(fù)，而SUMPRODUCT不會出現(xiàn)假重復(fù)？

＊＊＊＊部落窩教育－excel長數(shù)值的有效位數(shù)＊＊＊＊

原創(chuàng)：夏雪/部落窩教育（未經(jīng)同意，請勿轉(zhuǎn)載）

更多教程：部落窩教育（www.itblw.com）

微信公眾號：exceljiaocheng