Ragic 是一個可以無縫整合 Excel 的雲端資料庫平台(支援匯入匯出 Excel ),這使得我們在協助客戶排除障礙時,有時會跟 Excel 打打交道,也累積了一些 Excel 常見問題和疑難雜症處理的小技巧。
我們先前陸續跟大家分享了Excel 檔原本的英文 A,B,C 欄變成數字 1,2,3的原因和處理方法、以及「如何刪除 Excel 的空白列」,這次要來談談「如何在 Excel 移除重複資料」。
Excel 提供了快速的方式,可以簡單幾步驟把重複資料刪掉、或把重複資料標示出來(讓人檢視過再決定是否刪除),方法本身不難,但還是有各自要注意的地方。以下逐一說明:
標籤名稱 (工具列上的分類) | 功能名稱 | 效果 | 適合情境 |
資料 | ① 移除重複項![]() | 直接刪掉選取欄位值重複的資料(保留第一筆) | 適合規則明確、不需要另外檢視、經常需要執行刪除的流程 |
常用 | ② 條件式格式: 標示重複值 ![]() | 把欄位值重複的地方標示上色 | 最常見:適合需要額外檢視、決定去留的狀況;適合單一欄位比對 |
資料 | ③ 進階篩選: 篩出唯一值 ![]() | 隱藏重複值,只顯示第一筆(但沒有刪掉重複值) | 適合只需要「看起來不雜亂」的狀況 |
第一種是最直接、方便的「移除重複值」方法。只要用滑鼠選取好表格範圍、在「資料」標籤找到「移除重複值」按鈕、按「確定」,三步驟就清掉重複值了。
不過,最方面的方法有時也最「危險」,因為在資料被刪掉之前,你其實沒什麼機會去檢視系統認定的「重複值資料」是哪些,萬一刪除範圍選得不對、比對重複規則沒選對,不該刪的資料就會不小心消失了。
「移除重複項」功能要注意的地方主要有三項:
一,起初框選欄位範圍時,選的是「之後移除重複值時的刪除範圍」(不是「比對重複值依據」)。以上圖為例,假如你想把重複報名的學生資料刪掉,框選範圍要包括 A, B, C, D 欄,才能把重複的學生資料整筆刪掉。不能只選「B 欄」(學號),這樣只有學號那欄會被刪掉、對應的其他欄位(例如重複的學生姓名等)都還會在,會資料大亂(部分學號跟姓名的對應錯誤)。
二、點「移除重複項」後會跳出視窗讓你確認「比對重複值」的範圍。此時,若要比對的只有單欄(例如學號),但你卻維持預設的 A,B,C,D,...欄全都勾選的狀態的話,會變成「A, B, C, D 全部欄位值都是重複的,才移除」。同樣以上圖的例子來說,我們想刪掉的是「重複報名的學生」,這些學生重複報名時,每次會取得不同的報名編號,但他們是同一個人,是我們認定的「重複資料(報名編號不一樣,但學生是同一個人」,此時如果要求「A欄 報名編號」也要一樣才能認定為重複資料的話,就會有誤了。
三、「移除重複項」基本上會直接留下重複值的第一筆資料,移除其他後面的。這時假如希望依照特定的規則來決定資料去留(例如比較完整的一筆資料,如備註欄位有值),或希望一筆一筆檢視決定的話,就比較不適用於這個方法。
個人建議,要使用「移除重複值」這個功能,最好在你已經對此功能的運作機制很了解,或是常常需要執行同樣一種操作,SOP已經建立好的前提下做。做之後,記得檢視一下有無問題,有的話趕快 Control + Z 恢復資料。如果要更萬無一失,可以在做之前先備份一份原本的資料。
第二種方法,是比較迂迴一些,但一般人比較不會出錯的方式,如果沒有什麼特別的想法、重複資料量也不大的話,建議可以用這個方法來做。這個方式是運用 Excel 很常見的「條件式格式」,先把重複值標註上色。接著就可以依照自己的需求,一筆筆把資料處理(刪除)掉。這個方法也適用於不確定自己有沒有重複資料的狀況,設定之後有欄位上色就代表有重複值,沒有就代表沒有重複值。
這裡要注意的是:在選取「條件式格式設定」之前,一樣要先框選執行這個功能的範圍(也就是「條件式格式設定」的範圍。而條件式格式設定中的「重複值」指的是單一欄位的重複值,不提供多個欄位綜合比較是否都重複的功能,因此如果你框選的是多個欄位,會像下圖這樣,每個欄位有重複值的都標起來,這不一定是你想要的。
因此,如果使用「條件式格式設定」的方法,又希望比對的重複值是「多個欄位值都重複才標示」的話,建議多開一個「判斷用欄位」,用 Excel 的字串公式(&)把多個欄位值連在一起,然後框選判斷用欄位來設定條件式格式即可。
第三種方法,嚴格來說不是刪除重複資料的方法,而是一種「篩選、隱藏重複資料」的方法。選擇要比對重複值的欄位後,在「資料」標籤「篩選」區塊點「進階」,勾選「不選重複的紀錄」,就可以篩選出該欄位不重複的資料(隱藏該欄含有重複值的整列資料)。
此時,被篩選掉 / 被隱藏的資料並沒有被刪掉,只要點一下「清除篩選」或在隱藏的列與列之間點「取消隱藏」,就會再現身,適合需要保留原始資料、公式計算時需要原始資料,但不想看到太雜亂資料的狀況。
前面的教學裡,不管你選擇哪一種方法,其實都需要在操作步驟中,決定要怎麼比對「重複資料」,以及「刪誰留誰」的判準,以下針對這些部分做一些補充說明。
不同情境下,「資料重複」的判準可能有所不同。有時候,你想處理的重複資料,是某個特定欄位欄位值重複的資料,但有時候,你想找的是「每一個欄位都重複、或是特定幾個欄位都重複」的「重複資料」。
在 Excel 執行「找出重複資料」和「移除重複資料」時,系統會依據你選取資料的範圍,決定是要認「單一欄位的重複值」還是「一組欄位是否全都重複」,因此你必須先弄清楚自己要找的是哪一種。如果沒想清楚就隨便去框「重複值」的範圍,很可能反而把資料搞亂了。以下舉例說明不同情境下兩者的差異。
假設某活動開放學生報名參加,因為報名管道多元,學生可以跟導師報名、透過所屬社團報名,彙整報名資料時發現同一個學生透過不同管道重複報名,導致學生資料重複。此時,要刪重複資料,只要把報名資料裡「學號」這個欄位的重複值找出來,刪掉含有重複值的整筆資料(留下一筆)就可以了。
(如下圖:只要「學號」這欄重複,就代表資料重複了,只要抓出單一欄位值重複的就好)
假設今天處理的是訂單資料,負責彙整訂單的員工不小心重複從同一個來源貼上了某些資料,要刪除重複訂單資料。此時,這些資料的「訂單編號」可能是重複的,找出「訂單編號」欄位重複的資料來處理就好:
但假如今天是負責打單的員工不小心重複登打重複的訂單,重複資料的訂單編號是不同的,那麼如果從重複訂單編號下手,你會一筆都找不出來;而單憑「業務」、「客戶名稱」或「訂單日期」欄位,也沒辦法判斷訂單是否重複,因為同一個業務接到複數訂單、同一個客戶多次下單、同一天有多筆訂單,都是有可能的,任意拿單一欄位當判準,可能把業務的業績都刪掉了。
這時,我們必須依照實際情境,設計多個欄位的比對方式。假設這家公司同一天同個業務不會接到兩筆同個客戶下的單,那麼,可以認定當「訂單日期」、「客戶名稱」、「業務」三個欄位值全都一樣時,這是誤 KEY 的重複訂單,要處理。
此時,我要做的就會是一次比對不同筆(列)資料的多個欄位(例如前面講到移除重複值方法時,選擇比對重複值欄位的範圍要包括多個欄位),或是用字串公式把這些欄位串成一個輔助欄位(例如這裡的說明),比對輔助欄位的值是否重複來判斷重複資料。
(從上面的討論其實可以看出,當一筆資料含有「獨特值欄位」如學生編號、訂單編號時,很多時候比對或辨認資料時以那個欄位為準就可以了;反之如果沒有的話,就得自己用字串公式或其他方式,另外製造出一個獨特值欄位來當判斷基準。可以看出處理資料時,獨特值欄位的好用之處)
找出重複值並刪除的過程中,「哪一筆資料該刪」其實也是該注意的事。Excel預設的「移除重複值」功能,預設是保留重複值的第一筆資料、移除之後出現的。
如果「重複資料」是兩筆完全一樣內容的東西,那麼不管留哪一筆下來都可以,這樣用沒有問題。但如果是基本資訊重複,但有的資料是完整版(有比較詳細的備註或地址等資訊)、有的是精簡版,要留比較完整的資料的話,就得注意,因為 Excel 「移除重複值」功能的規則就是「留第一筆」,此時你就不該直接「移除重複值」,可能要考慮標記重複值之後手動處理(也就是利用條件式格式來處理)。
標籤: Excel