關(guān)鍵字：重復(fù)；excel教程；countif

全文1090字，預(yù)計(jì)3分鐘讀完

日常工作中，有時(shí)候會(huì)遇到篩選重復(fù)值出錯(cuò)的情況。

明明沒有重復(fù)，Excel卻判定數(shù)據(jù)重復(fù)，這是怎么一回事呢？

我們來看看吧！

如圖所示，我們想要篩選這一列數(shù)據(jù)的重復(fù)值。

首先選中這一列數(shù)據(jù)，點(diǎn)擊開始-【條件格式】-【突出顯示單元格規(guī)則】-【重復(fù)值】，然后選中數(shù)據(jù)，給這些數(shù)據(jù)升序排列。

大家可以看到，這一列銀行卡號(hào)（模擬數(shù)據(jù)）經(jīng)過簡單的排序后，很多明明沒有重復(fù)的數(shù)據(jù)，也被標(biāo)注成了重復(fù)。

基礎(chǔ)比較薄弱的同學(xué)會(huì)說，難道Excel也會(huì)出錯(cuò)嗎？

其實(shí)這不是Excel出了問題，而是一種比較特殊的情況。

解決方法：

不能直接使用條件格式標(biāo)注重復(fù)項(xiàng)，而是應(yīng)該新建規(guī)則。

選中E列數(shù)據(jù)，然后點(diǎn)擊【條件格式】-【突出顯示單元格規(guī)則】-【其他規(guī)則】-【使用公式確定要設(shè)置格式的單元格】，然后輸入公式=COUNTIF($E:$E,E1&"*")>1即可。

注意：若之前有設(shè)置其他規(guī)則，應(yīng)先將之前的規(guī)則取消掉。

取消步驟：選中數(shù)據(jù)，點(diǎn)擊開始-條件格式-清除規(guī)則。

經(jīng)過簡單的升序排列，我們可以看見，E列使用公式判斷重復(fù)后的結(jié)果，與直接判斷重復(fù)是有差異的。

下面，咱們用COUNTIF函數(shù)來統(tǒng)計(jì)每個(gè)卡號(hào)出現(xiàn)的次數(shù)。

直接使用公式=COUNTIF(B:B,B2)，結(jié)果如圖所示。

這個(gè)結(jié)果和直接使用條件格式標(biāo)注重復(fù)項(xiàng)的效果一致，雖然與實(shí)際數(shù)據(jù)不符。

之所以出現(xiàn)這樣的情況，是因?yàn)镃OUNTIF函數(shù)把任何類似于數(shù)字的內(nèi)容統(tǒng)統(tǒng)按照數(shù)字對(duì)待，而對(duì)于卡號(hào)這種位數(shù)比較多的數(shù)據(jù)，按照數(shù)字統(tǒng)計(jì)是有問題的。

有點(diǎn)經(jīng)驗(yàn)的同學(xué)都知道，超過15位的數(shù)字必須以文本格式呈現(xiàn)，否則就會(huì)向下圖這種形式演變。

即數(shù)據(jù)的最后幾位自動(dòng)變成0。

也就是說，在COUNTIF看來，這些卡號(hào)的第15位以后都是0，并不是表格里實(shí)際看到的數(shù)據(jù)。

為了解決這個(gè)問題，就必須將第二參數(shù)改成B2&"*"，星號(hào)的作用是通配符，這樣寫的意思是每個(gè)卡號(hào)后面不管有任何內(nèi)容，都視為同一個(gè)卡號(hào)。

修改公式為=COUNTIF(E:E,E2&"*")，現(xiàn)在，我們得到的結(jié)果是正確的，與實(shí)際數(shù)據(jù)一致。

溫馨提示：

COUNTIF函數(shù)加&"*"并不適用于任何情況，比如下面這種情況：

注意這三個(gè)卡號(hào)，尾號(hào)3831實(shí)際是2個(gè)，但是結(jié)果是3，這是因?yàn)閷?831111也看做是一樣的。

所以&"*"這種用法，并不是強(qiáng)制轉(zhuǎn)為文本的原理。

只有要判斷的數(shù)據(jù)位數(shù)是一樣的情況下，這種用法才是準(zhǔn)確的，一旦位數(shù)不同，也有可能出錯(cuò)。

總結(jié)一下：

1、用條件格式或者COUNITF、SUMIF這類支持通配符的統(tǒng)計(jì)函數(shù)時(shí)，在遇到長數(shù)字的情況要格外小心。

2、數(shù)字位數(shù)相同的時(shí)候，加&"*"可以保證正確，如果位數(shù)不同時(shí)則要留神，極有可能出錯(cuò)。

以上就是今天的所有內(nèi)容。