2016年12月13日 星期二

Excel 重複資料篩選比對 COUNTIF

探討這套Excel公式的原因只是因為一份報名表.....
爬了幾篇知識+後還是覺得這種方式不錯
比起直接把資料砍掉的不放心
可以直接變換格式讓眼睛既省得麻煩又死得明白XD
 
(本篇作者同於 http://goeatgoplay.blogspot.tw/2016/05/excel-countif.html

==========================

首先
假設我有一份名單是這樣的



那麼
假定,所有的資料都具有唯一性(也就是資料理論上最多只會出現一遍)
那麼該怎麼抓出重複的錯誤呢?

這個COUNTIF公式一定幫得上忙!!!





  現在我們先將上面表格的A2~D5全部反白後 
(但記住一定要從A2開始拉到D5,等等會說明為什麼)
選擇 "格式" > "設定格式化條件"
將第一個選項 "儲存格的值" 改成 "公式為"
貼上這個
=(COUNTIF($A$2:$D$5,A2)>1)
然後按 "格式" 選擇你想讓重複的資料的顯示法
(當然還是建議能越明顯越好。亮度高的顏色 粗體 甚至大小都可以設定)
(之後只要把重複的資料擇一刪除掉,正確的資料就會自動變回原設定格式與其他無異)

燈冷!


那麼
來解釋一下這個公式的詳細內容吧

當然外面的COUNTIF只是個函數開頭意即 "如果總數目"
後面的($A$2:$D$5,A2)
$A$2:$D$5也就是從A2開始到D5的範圍之間
換言之您也可以任意選擇範圍
G3到K100就是$G$3:$K$100
再來是逗號後面的A2
上面有提醒您一定要從A2開始拉到D5
因為您反白的第一格是您第一個設定的格子
所以從A2開始拉 在格式化條件的公式後面就會是A2
當然您懂了以後也可以從A5或D5或D2開始拉
總之貼上的公式逗號後面,一定要是您開始拉的那一格!

再來就只剩下>1
其實COUNTIF這個函數會回傳的是數字
意思就是"在指定範圍裡跟此儲存格相同的格子有幾格(包含自己)
所以如果指定範圍裡有2筆資料相同
整個函數就會給出2
因為2>1
所以儲存格就會被轉換格式

註:
如果一筆資料是 "123" 
另一筆是 "123 " < 多一個空格
那麼就無法被抓出來唷,因為算是不同的字串


 ==================================
(2016.05.27註1)
新版本的Excel已經拯救了我們這種公式笨蛋
在將所選的欄位都反白後
"設定格式化條件" > "醒目提示儲存格規則" > "重複的值"
即可完成以上的動作~~

當然
如果你好學不倦的話
也是可以利用
"設定格式化條件" > "新增規則" > "使用公式來決定要格式化那些儲存格"
然後在將上面教的COUNTIF公式貼上即可~

 =======================================
(2016.05.27註2)



Google試算表則是可以在
"格式" > "條件式格式設定" > "新增規則" > 儲存格套用以下條件時選擇公式 選擇 "自訂公式"
套用範圍選好:本例子即為A8:C10
公式貼上去即可!

沒有留言:

張貼留言