有朋友就發(fā)現(xiàn)這樣一個問題，在使用COUNTIF函數(shù)統(tǒng)計身份證號碼的時候，得到的結(jié)果竟然是錯誤的。
如圖中所示，在E列使用下面的公式，判斷B列的身份證號碼是否重復(fù)。
=IF(COUNTIF($B$2:$B$11,B2)>1,"重復(fù)","")
公式中COUNTIF($B$2:$B$11,B2)部分，用來統(tǒng)計$B$2:$B$11數(shù)據(jù)區(qū)域中等于B2單元格的數(shù)量。再使用IF函數(shù)判斷，如果$B$2:$B$11數(shù)據(jù)區(qū)域中，等于B2單元格的數(shù)量大于1，就返回指定的結(jié)果1“重復(fù)”，否則返回空值。運算的結(jié)果如E列所示。
可是當我們仔細檢查時就會發(fā)現(xiàn)，B2和B11單元格的身份證號碼是完全相同的，因此函數(shù)結(jié)果判斷為重復(fù)，但是B6單元格只有前15位號碼和B2、B11單元格內(nèi)容相同，函數(shù)結(jié)果仍然判斷為重復(fù)，這顯然是不正確的。
我們來看一下究竟是什么原因呢？雖然B列中的身份證號碼為文本型數(shù)值，但是COUNTIF函數(shù)在處理時，會將文本型數(shù)值識別為數(shù)值進行統(tǒng)計。在Excel中超過15位的數(shù)值只能保留15位有效數(shù)字，后3位全部視為0處理，因此COUNTIF函數(shù)將B2、B6、B11單元格中的身份證號碼都識別為相同。
用什么辦法來解決這種誤判的問題呢？可將E2單元格公式修改為：
=IF(COUNTIF($B$2:$B$11,B2&"*")>1,"重復(fù)","")
在上面這個公式中，COUNTIF函數(shù)的第2參數(shù)使用了通配符"*"，最終得出正確結(jié)果。使用通配符"*"的目的是使其強行識別為文本進行統(tǒng)計，相當于告訴Excel“我要統(tǒng)計的內(nèi)容是以B2單元格開頭的文本”，Excel就會老老實實的去執(zhí)行任務(wù)了。所以說，Excel就像一個忠實的士兵，能不能打勝仗，關(guān)鍵還是要看我們怎么指揮的。
除了在第二參數(shù)后面加通配符的方法以外，也可使用以下數(shù)組公式完成計算：
{=IF(SUM(N(B2=$B$2:$B$11))>1,"重復(fù)","")}
這個公式中，直接使用了等式B2=$B$2:$B$11，等號就像一個天平，只有左右兩側(cè)完全一致了，等式才會成立的。
等式B2=$B$2:$B$11返回的是邏輯值TRUE或是FALSE，用N函數(shù)將邏輯值轉(zhuǎn)換為數(shù)值，TRUE轉(zhuǎn)換為1，F(xiàn)ALSE轉(zhuǎn)換為0，然后再用SUM函數(shù)求和。通過這樣迂回的方法完成是否重復(fù)的判斷。
昨天為大家留下了一個問題，運用COUNTIF函數(shù)統(tǒng)計數(shù)據(jù)區(qū)域中的不重復(fù)個數(shù)：
下面就簡單學習一下，怎么處理這個不重復(fù)數(shù)量的統(tǒng)計問題。
可以使用這個數(shù)組公式（別忘了，數(shù)組公式需要按下Shift+Ctrl Enter才可以哦）：
{=SUM(1/COUNTIF(A2:A14,A2:A14))}
怎么去理解這個公式呢？{=SUM(1/COUNTIF(區(qū)域,區(qū)域))}是計算區(qū)域中不重復(fù)值個數(shù)的經(jīng)典公式。
1、公式中“COUNTIF(A2:A14,A2:A14)”部分是數(shù)組計算，運算過程相當于：
=COUNTIF(A2:A14,A2)
=COUNTIF(A2:A14,A3)
……
=COUNTIF(A2:A14,A14)
結(jié)果為數(shù)組{2;2;1;1;2;1;1;1;1;2;2;2;1}，表示區(qū)域中等于本單元格數(shù)據(jù)的個數(shù)。
2、“1/{2;2;1;1;2;1;1;1;1;2;2;2;1}”部分的計算結(jié)果為{0.5;0.5;1;1;0.5;1;1;1;1;0.5;0.5;0.5;1}，用1除以個數(shù)，是本公式的核心，要結(jié)合前后計算才能領(lǐng)會好它的作用。為便于理解，把這一步的結(jié)果整理一下，用分數(shù)代替小數(shù)，結(jié)果為：{1/2;1/2;1;1;1/2;1;1;1;1;1/2;1/2;1/2;1}。
如果單元格的值在區(qū)域中重復(fù)出現(xiàn)兩次，這一步的結(jié)果就有兩個1/2。如果單元格的值在區(qū)域中重復(fù)出現(xiàn)3次，結(jié)果就有3個1/3，如此類推。
3、最后用SUM函數(shù)求和，計算結(jié)果為10。
怎么樣，你學會了嗎？
在實際工作中，如果數(shù)據(jù)量比較大的情況下，往往會讓我們眼花繚亂，難免將數(shù)據(jù)張冠李戴，出現(xiàn)錯誤。如下圖所示，不同部門的數(shù)據(jù)如果用顏色突出顯示，可以很方便我們區(qū)分，讓數(shù)據(jù)看起來更加清晰明了。
那這樣的效果如何實現(xiàn)呢？就把這個問題留給大家來思考吧。（可不要告訴我，目測后設(shè)置顏色哦）
* 本教程部分內(nèi)容選自Excel Home編著的《Excel 2010函數(shù)與公式實戰(zhàn)技巧精粹》
|
|
