一起認識COUNTIF函數(應用篇)
05-28
有朋友就發現這樣一個問題,在使用COUNTIF函數統計身份證號碼的時候,得到的結果竟然是錯誤的。如圖中所示,在E列使用下面的公式,判斷B列的身份證號碼是否重複。=IF(COUNTIF($B$2:$B$11,B2)>1,"重複","")
我們來看一下究竟是什麼原因呢?雖然B列中的身份證號碼為文本型數值,但是COUNTIF函數在處理時,會將文本型數值識別為數值進行統計。在Excel中超過15位的數值只能保留15位有效數字,後3位全部視為0處理,因此COUNTIF函數將B2、B6、B11單元格中的身份證號碼都識別為相同。 用什麼辦法來解決這種誤判的問題呢?可將E2單元格公式修改為:=IF(COUNTIF($B$2:$B$11,B2&"*")>1,"重複","")在上面這個公式中,COUNTIF函數的第2參數使用了通配符"*",最終得出正確結果。使用通配符"*"的目的是使其強行識別為文本進行統計,相當於告訴Excel「我要統計的內容是以B2單元格開頭的文本」,Excel就會老老實實的去執行任務了。所以說,Excel就像一個忠實的士兵,能不能打勝仗,關鍵還是要看我們怎麼指揮的。除了在第二參數後面加通配符的方法以外,也可使用以下數組公式完成計算: {=IF(SUM(N(B2=$B$2:$B$11))>1,"重複","")}這個公式中,直接使用了等式B2=$B$2:$B$11,等號就像一個天平,只有左右兩側完全一致了,等式才會成立的。等式B2=$B$2:$B$11返回的是邏輯值TRUE或是FALSE,用N函數將邏輯值轉換為數值,TRUE轉換為1,FALSE轉換為0,然後再用SUM函數求和。通過這樣迂迴的方法完成是否重複的判斷。昨天為大家留下了一個問題,運用COUNTIF函數統計數據區域中的不重複個數:下面就簡單學習一下,怎麼處理這個不重複數量的統計問題。 可以使用這個數組公式(別忘了,數組公式需要按下Shift+Ctrl Enter才可以哦):{=SUM(1/COUNTIF(A2:A14,A2:A14))}怎麼去理解這個公式呢?{=SUM(1/COUNTIF(區域,區域))}是計算區域中不重複值個數的經典公式。1、公式中「COUNTIF(A2:A14,A2:A14)」部分是數組計算,運算過程相當於: =COUNTIF(A2:A14,A2)=COUNTIF(A2:A14,A3)……=COUNTIF(A2:A14,A14)結果為數組{2;2;1;1;2;1;1;1;1;2;2;2;1},表示區域中等於本單元格數據的個數。2、「1/{2;2;1;1;2;1;1;1;1;2;2;2;1}」部分的計算結果為{0.5;0.5;1;1;0.5;1;1;1;1;0.5;0.5;0.5;1},用1除以個數,是本公式的核心,要結合前後計算才能領會好它的作用。為便於理解,把這一步的結果整理一下,用分數代替小數,結果為:{1/2;1/2;1;1;1/2;1;1;1;1;1/2;1/2;1/2;1}。如果單元格的值在區域中重複出現兩次,這一步的結果就有兩個1/2。如果單元格的值在區域中重複出現3次,結果就有3個1/3,如此類推。3、最後用SUM函數求和,計算結果為10。怎麼樣,你學會了嗎?在實際工作中,如果數據量比較大的情況下,往往會讓我們眼花繚亂,難免將數據張冠李戴,出現錯誤。如下圖所示,不同部門的數據如果用顏色突出顯示,可以很方便我們區分,讓數據看起來更加清晰明了。那這樣的效果如何實現呢?就把這個問題留給大家來思考吧。(可不要告訴我,目測後設置顏色哦)* 本教程部分內容選自Excel Home編著的《Excel 2010函數與公式實戰技巧精粹》 |
推薦閱讀:
※羅盤的認識
※七政四餘三王的基本認識
※他為什麼不喜歡你認識新朋友?
※教大家認識八字看哪些人註定會發大財!
※紫微14主星-認識 貪狼星