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

asaser 2022-05-14

展開全文

社群里有朋友提了一個問題，一個在工作中很常見的問題；如下圖所示的數(shù)據(jù)，為啥使用條件格式標記重復(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位，就會喪失準確度，被轉(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ù)的類型，它可以準確判斷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ù)值進行匹配計數(shù)了，也就避免了COUNTIF函數(shù)將文本數(shù)值轉(zhuǎn)換為數(shù)值的問題。

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