Excel 重複資料篩選比對 COUNTIF

探討這套Excel公式的原因只是因為一份報名表.....
爬了幾篇知識+後還是覺得這種方式不錯
比起直接把資料砍掉的不放心
可以直接變換格式讓眼睛既省得麻煩又死得明白XD
 

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

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

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

這個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這個函數會回傳的是數字
意思就是＂在指定範圍裡跟此儲存格相同的格子有幾格（包含自己）＂
所以如果指定範圍裡有２筆資料相同
整個函數就會給出２
因為２＞１
所以儲存格就會被轉換格式

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


 ==================================
(2016.05.27註1)
新版本的Excel已經拯救了我們這種公式笨蛋

在將所選的欄位都反白後
"設定格式化條件" > "醒目提示儲存格規則" > "重複的值"
即可完成以上的動作~~

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

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

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