15使用條件格式標(biāo)記重復(fù)值為啥會(huì)出錯(cuò)？

asaser 2022-05-14

展開全文

社群里有朋友提了一個(gè)問題，一個(gè)在工作中很常見的問題；如下圖所示的數(shù)據(jù)，為啥使用條件格式標(biāo)記重復(fù)值后，不重復(fù)的數(shù)據(jù)也被填充了顏色？

B列是一堆文本型數(shù)值，長(zhǎng)度不盡相同。執(zhí)行條件格式重復(fù)值功能后，明顯不重復(fù)的數(shù)據(jù)，都被填充了紅色。比如B2、B3等單元格的數(shù)據(jù)。

Excel為啥這么憨憨？

這是Excel的Bug嗎？

Bug談不上，但問題確實(shí)是存在的。

問題在于條件格式的【重復(fù)值】是內(nèi)置的COUNTIF函數(shù)。COUNTIF在計(jì)算的過程中，會(huì)自動(dòng)將文本數(shù)值轉(zhuǎn)換為數(shù)值。而在Excel單元格的地盤上，能夠保存的最大有效數(shù)值是15位。數(shù)值超過15位，就會(huì)喪失準(zhǔn)確度，被轉(zhuǎn)換成0。

在以上數(shù)據(jù)中，B2:B4等單元格的值，都會(huì)被COUNTIF轉(zhuǎn)換為：

1234567891234560000

這么一折騰，COUNTIF的計(jì)算結(jié)果就難免出錯(cuò)了。

……

怎么解決這個(gè)問題呢？

推薦使用條件格式的自定義規(guī)則。

選中B2:B11區(qū)域，依次單擊【條件格式】→【新建規(guī)則】→【使用公式確定要設(shè)置格式的單元格】,在編輯框中輸入以下公式：

=SUMPRODUCT(1*($B$2:$B$12=B2))>1

此時(shí)條件格式會(huì)返回正確的結(jié)果，如下圖所示：

打個(gè)響指，解釋下公式的含義。

$A$2:$A$12=A2，等號(hào)運(yùn)算不會(huì)改變數(shù)據(jù)的類型，它可以準(zhǔn)確判斷A2:A12區(qū)域的值是否等于A2。如果相等，則返回邏輯值TRUE，否則返回FALSE。然后*1，將TRUE轉(zhuǎn)換為1，F(xiàn)ALSE轉(zhuǎn)換為0，再使用SUMPRODUCT統(tǒng)計(jì)求和，如果大于1，則說明重復(fù)。

……

有的朋友也可能會(huì)使用以下公式計(jì)算號(hào)碼是否重復(fù)：

=COUNTIF(B:B,B2&"*")>1

這個(gè)公式看起來很簡(jiǎn)單很實(shí)在的樣子，不過不大靠譜。

C2&"*"，星號(hào)是通配符，可以代替0到多個(gè)字符，和數(shù)值搭配后，會(huì)將數(shù)值強(qiáng)迫轉(zhuǎn)換為文本值，這樣就可以強(qiáng)制COUNTIF按文本類型對(duì)數(shù)值進(jìn)行匹配計(jì)數(shù)了，也就避免了COUNTIF函數(shù)將文本數(shù)值轉(zhuǎn)換為數(shù)值的問題。

但本例數(shù)據(jù)長(zhǎng)度不一致，并不適合該方法，此時(shí)該解法返回結(jié)果如下：

由于*星號(hào)是通配符，可以代替0到多個(gè)字符，因此系統(tǒng)會(huì)認(rèn)為A2單元格的值和A11相等，A5單元格和A7相等……

當(dāng)然，如果數(shù)據(jù)的長(zhǎng)度一致，比如身份證，均為18位數(shù)值，通配符*星號(hào)也就不存在代替誰的問題，該函數(shù)也就可以返回正確的結(jié)果。

綜上所述，相比之下，還是推薦大家使用SUMPRODUCT函數(shù)，計(jì)算效果更穩(wěn)定更安全。

今天和大家分享的內(nèi)容就這些，有啥問題可在VIP會(huì)員群中提問交流，揮揮手，咱們明天再見。

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自： asaser > 《No3:可視化》

舉報(bào)/認(rèn)領(lǐng)