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

前兩天有個(gè)小伙伴給我發(fā)了個(gè)文檔，說(shuō)自己被COUNTIF坑了，弄錯(cuò)重復(fù)被罰了，讓我?guī)退页稣_的重復(fù)值。總共有547行數(shù)據(jù)，如圖：

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

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

結(jié)果出來(lái)了。

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

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

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

COUNTIF標(biāo)注重復(fù)

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

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

這樣，重復(fù)值就被標(biāo)記出來(lái)了。

解析：

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

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

由于EXCEL中超過(guò)15位的數(shù)字只能保留15位有效數(shù)字，后面全部視為0，所以“3771000100001197”和“3771000100001192”會(huì)被認(rèn)為是重復(fù)值，因?yàn)樗鼈兌急划?dāng)成了數(shù)字“3771000100001190”。這里使用 &"*"將單元格數(shù)字后面統(tǒng)一添加*符號(hào)強(qiáng)行當(dāng)做文本進(jìn)行識(shí)別統(tǒng)計(jì)，就可以準(zhǔn)確地通過(guò)計(jì)數(shù)值是否大于1識(shí)別出數(shù)字是否重復(fù)。

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

   不過(guò)這個(gè)公式存在一個(gè)缺陷，就是當(dāng)所在區(qū)域里有空格的時(shí)候，也會(huì)自動(dòng)識(shí)別為重復(fù)。

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

那有沒(méi)有什么方法可以屏蔽空格呢？我們?cè)谠泄降幕A(chǔ)上乘以A2<>""，把條件格式的公式修改成“=(COUNTIF($A$2:$A$547,A2&"*")*(A2<>"")>1)”。若單元格為空，A2<>""返回FALSE，原有結(jié)果跟邏輯值FALSE相乘等于0，0不大于1，自然不會(huì)被標(biāo)注為重復(fù)：

SUMPRODUCT標(biāo)注重復(fù)

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

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

完成結(jié)果如下：

解析：

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

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

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

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

小編的疑惑：為何數(shù)字超過(guò)15位后用COUNTIF出現(xiàn)假重復(fù)，而用SUMPRODUCT沒(méi)有出現(xiàn)？

兩個(gè)函數(shù)，都是建立在$A$2:$A$547的值是否等于A2的判斷上進(jìn)行的，為何COUNTIF會(huì)出現(xiàn)假重復(fù)，而SUMPRODUCT不會(huì)出現(xiàn)假重復(fù)？

歡迎加入QQ群：264539405交流學(xué)習(xí)下載素材。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

快速統(tǒng)計(jì)不重復(fù)數(shù)《1分鐘搞定不重復(fù)數(shù)統(tǒng)計(jì)》

5個(gè)查重復(fù)的案例《countif函數(shù)的使用方法以及countif函數(shù)查重復(fù)等5個(gè)案例分享》