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

asaser >《No3:可視化》

2022.05.14

關(guān)注

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

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

Excel為啥這么憨憨？

這是Excel的Bug嗎？

Bug談不上，但問題確實是存在的。

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

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

1234567891234560000

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

……

怎么解決這個問題呢？

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

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

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

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

打個響指，解釋下公式的含義。

$A$2:$A$12=A2，等號運算不會改變數(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)計求和，如果大于1，則說明重復(fù)。

……

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

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

這個公式看起來很簡單很實在的樣子，不過不大靠譜。

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

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

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

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

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

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

本站僅提供存儲服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點擊舉報。

打開APP，閱讀全文并永久保存查看更多類似文章

不重復(fù)數(shù)值的計算(sumproduct函數(shù)的運用）

常用函數(shù)公式及技巧搜集

Excel只會加減乘除?其實就已經(jīng)很溜了！

Excel技巧

EXCEL實用操作技巧 - postzsh的日志 - 網(wǎng)易博客

?這樣標(biāo)記項目進(jìn)度，驚不驚喜？意不意外？

更多類似文章 >>

免费视频淫片aa毛片_日韩高清在线亚洲专区vr_日韩大片免费观看视频播放_亚洲欧美国产精品完整版