明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
作者:老菜鳥來源:部落窩教育發(fā)布時(shí)間:2022-05-31 17:35:43點(diǎn)擊:46193
編按:
日常工作中,有時(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)?span>COUNTIF函數(shù)把任何類似于數(shù)字的內(nèi)容統(tǒng)統(tǒng)按照數(shù)字對待,而對于卡號(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)閷?span>3831111也看做是一樣的。
所以&"*"這種用法,并不是強(qiáng)制轉(zhuǎn)為文本的原理。
只有要判斷的數(shù)據(jù)位數(shù)是一樣的情況下,這種用法才是準(zhǔn)確的,一旦位數(shù)不同,也有可能出錯(cuò)。
最后,總結(jié)一下,用條件格式或者COUNITF、SUMIF這類支持通配符的統(tǒng)計(jì)函數(shù)時(shí),在遇到長數(shù)字的情況要格外小心,數(shù)字位數(shù)相同的時(shí)候,加&"*"可以保證正確,如果位數(shù)不同時(shí)則要留神,極有可能出錯(cuò)。
好啦,以上就是今天的所有內(nèi)容,感謝你的觀看。
本文配套的練習(xí)課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
提取不重復(fù)值并統(tǒng)計(jì)數(shù)量的三個(gè)方法,一秒完成!
如何提取唯一值?試試TEXTJOIN函數(shù)搭配VBA自定義!
氣泡圖和條形圖如何做組合圖,這個(gè)Excel圖表太適合年終匯報(bào)!
版權(quán)申明:
本文作者老菜鳥;同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請收好!