處理所有Excel資料重複相關問題，看這一篇就夠了

我們經常遇到Eexcel資料重複的問題，需要解決，但能找到的教程都很零散，往往只提供了一種方法解決了某一類重複問題。

但是Excel資料重複問題，看似簡單，但是深究起來，有很多值得思考和挖掘的細節。在這篇文章中，我試圖梳理出所有我們會遇到的Excel資料重複問題，並給出多種解決辦法。

Excel重複資料如何界定？

要解決Excel重複資料問題，第一步是界定如何界定資料是否重複？或者說，我們依據什麼來判斷資料是否重複？

我能想到的有三種資料重複的情況：

【單欄位重複】：判斷某個關鍵欄位是否重複，如姓名是否重複、手機號是否重複、郵箱是否重複等；

【多欄位重複】：需要綜合若干個欄位才能判定重複，比如單單透過姓名列是無法判定重複的，因為可能有同名同姓的，所以需要姓名再加上手機號或郵箱來判定是否重複；

【全欄位重複】。比如，我們需要判定兩條記錄在所有欄位上是否完全一模一樣。

而根據重複值可能出現的地方，又可以分為兩種情況：

重複值出現在同一張表（即同一個sheet或同一個table）中，為敘述方便，簡稱為【單表重複】；

重複值出現在不同表（這些表可能在同一個sheet的不同tables或regions中，也可能在不同的sheets中，乃至在不同的檔案中，處理思路都是一樣的，因此不單列）中，為敘述方便，簡稱為【多表重複】。

尋找到重複資料後如何處理？

當我們找到重複資料（無論屬於前面所列舉的哪種情況）後，我們通常需要做如下處理：

標記。將找到的重複值以醒目的方式標識出來，如用顏色標記，如用公式結果顯示匹配結果。

刪除與保留。可能是刪除重複值，保留唯一值；或者刪除唯一值，保留重複值。

提取。將匹配結果提取到新的表格。

下面一一梳理重複資料處理在Excel中的具體操作步驟。

用Excel的條件格式標記非重複值（唯一值）

如果屬於【單欄位重複】情況，只需要選擇該欄位，然後選擇條件格式-突出顯示單元格規則-重複值

這時會彈出下面的視窗：

左邊下拉框可選擇是標記重複值呢還是標記唯一值；右邊下拉框則可選擇要應用的格式，如果選擇了自定義，則會彈出詳細的格式設定框，可以進行更細緻的格式（數字、字型、邊框和填充效果）設定：

標記後效果如下（我用了預設填充效果標記重複值）：

如果屬於【多欄位重複】或【全欄位重複】，則標記之前需要有額外動作：新增一個輔助列，連線作為判斷重複依據的列欄位，然後在該輔助列上應用條件格式：

如上圖所示，我需要透過【姓名】、【性別】和【姓名拼音】三個欄位連線之後才能判定是否重複，因此我增加了一個輔助列【判斷是否重複】，然後用公式連線那三個欄位，再在輔助列上應用條件格式進行標記。可以看到兩個陳婷雖然姓名和性別一樣，但是郵箱不一樣，最後結果是這兩條記錄作為兩條不同的記錄體現出來（無填充色）。

Excel條件格式無法刪除唯一值並保留重複值，只能先標識出重複值，然後透過顏色篩選來保留重複值

：

如果要提取重複值或唯一值，需要透過篩選後手動將重複值或唯一值提取到新的表格。

透過Excel【資料】選項卡上的【刪除重複值】命令刪除重複值

這裡還是要區分是屬於【單欄位重複】/【多欄位重複】/【全欄位重複】中的哪一種。

如果屬於【單欄位重複】，只需要選定作為判斷依據的列，然後點選【刪除重複值】按鈕，並在彈出視窗中選擇【以當前選定區域排序】：

如果屬於【多欄位重複】/【全欄位重複】，則可以選中任意一列後，點選【刪除重複值】按鈕，在彈出視窗中選擇【擴充套件選定區域】：

這時Excel會彈出區域選擇視窗，如果是【多欄位重複】的情況，那麼選擇作為判定依據的列，然後確定；如果是【全欄位重複】，則全選所有列之後點選確定。

直接用Excel的【刪除重複值】命令，只能刪除重複值，不能標記重複值（唯一值），也不能提取重複值，但是能保留唯一值。

vlookup函式查詢重複值

vlookup函式估計是Excel中使用率最高的函式之一。其基本形式是：

=VLOOKUP （lookup_value， table_array， col_index_num，［range_lookup］）

引數說明見下表：

圖片來自於Excel官方幫助

Vlookup函式的使用需注意以下關鍵點：

要查詢的值，也被稱為查閱值。我見過很多人忘記寫查詢的值。

查閱值所在的區域。請記住，查閱值應該始終位於所在區域的第一列，這樣 VLOOKUP 才能正常工作。例如，如果查閱值位於單元格 C2 內，那麼您的區域應該以 C 開頭。這個查閱值所在的區域可以在任意位置：同一個sheet的不同區域或不同tables，或者在不同sheet上，或者在不同的檔案上都可以的。區別只在於引用不同物件的語法不同。實際應用中記不住沒關係，輸入公式時可以滑鼠去選定查閱值所在的區域或table或sheet或檔案路徑（需要你開啟該檔案）。

區域中包含返回值的列號。例如，如果指定 B2： D11 作為區域，則應將 B 作為第一列，將 C 作為第二列進行計數，依此類推。

（可選）如果需要返回值的近似匹配，可以指定 TRUE；如果需要返回值的精確匹配，則指定 FALSE。如果沒有指定任何內容，預設值將始終為 TRUE 或近似匹配。雖然是可選，但是我還是建議指定，畢竟絕大多數時候我們要查詢的是精確值。並且這個值有點違揹我們的直覺——查詢精確值是FALSE而模糊匹配是TRUE。

有時候我們會到要查詢的值在需要查詢區域的左側，這時可以採取下面類似的辦法：

=VLOOKUP（D2，IF（{1，0}，D2：D28，B2：B28），2，0））

公式中的1和0不是實際意義上的數字，而是1相關於TRUE，0相當於FALSE，當為1時，它會返回IF的第一個引數（D列），為0時返回第二個引數（B列）。根據陣列運算返回陣列，所以使用IF後的結果返回一個數組（非單元格區域）：該陣列由姓名列和姓名拼音列的值成對組成。

一、標記重複值或唯一值

vlookup查詢結果即對重複值（匹配成功）或唯一值（匹配失敗）進行了標記。

二、刪除和提取重複值（唯一值）

對vlookup查詢結果進行篩選後，即可刪除重複值（唯一值），或將篩選結果複製到新的表格中。

PowerBI處理重複值，節省海量時間

如果查詢重複值的操作只需要做一次，那麼我建議用前面的幾種方法就行了。

如果你日常工作中總是涉及查詢重複資料並進行相應處理，那麼，我建議用PowerBI來幫你節省海量時間。用我的口頭禪就是——

辛苦一次，幸福一生

。

為什麼這麼說呢？因為不論你查詢和處理重複值的步驟有多複雜，資料量有多大（幾百萬上千萬乃至更大的資料應該輪不到我們用Excel來處理），只要建立好PowerBI資料模型後：你每次更新要做的要麼只是開啟模型檔案重新整理一下（PowerQuery），耗時幾分鐘；要麼PowerBI以固定頻率自動重新整理結果（需要PowerBI賬號且結果不會自動新增到Excel中），零耗時。

假設：

你是總公司的一名小員工，你的任務是每天接收下面單位發上來的報名表。

但是呢，下面單位的人做事不動腦筋，每天都給你發一個客戶報名表上來。

這些表吧，有時候是包括該單位全部的報名資訊，有時候又是隻有新增的；而且有可能一個檔案裡邊的多個sheets裡邊都有報名資訊；這些Excel檔名稱又是千奇百怪；最可氣的是，讓他們早上提交，他們有的非要快下班了才提交；唯一值得欣慰的是這些表格的欄位都是一樣的。

你現在需要從提交上來的N個表格中，剔除重複報名資訊，形成一個完整的不重複的報名表。然後，你每天要在下班前把完整報名表給上級看。

很簡單的一項工作，但是你用前面的方法做，我百分之一百肯定你每天都要加班——因為每天都有人17：55才把表發給你，而你18：00就要把報名表給上級看。

來看用PowerBI怎麼把這個無聊工作縮短到5分鐘乃至0分鐘之內。

首先，新建一個Excel，名字叫《報名表彙總》；

引入來自於資料夾的源，找到“報名表”這個資料夾；

3。找到目標資料夾——“報名表”：

4。然後我們不要PowerQuery自作聰明地幫我們合併這些表啊sheets之類，我們自己操作，點選【轉換資料】，：

5。這時就進入查詢介面了：

6。我們刪除不必要的列，只保留【Content】列，因為資料都在這裡邊。然後新增一列，呼叫Excel。Workbook（）函式，從Binary中獲取資料：

7。然後我們刪除【Content】列，保留【自定義】列了，因為資料被我們提取到【自定義】列。點選該列右上角的展開圖示，只保留其中的【Data】列（因為表格在這裡面）：

8。展開【Data】列之後，我們要做個小動作，就是把每一個表格的第一行都作為標題。這一步不是必須，可以將來再做，但是先做這一步可以節省一些步驟和時間，何樂而不為呢。呼叫Table。TransformColumns（）函式和Table。PromoteHeaders（）函式提升了表格標題後，就可以點選【Data】列旁邊的展開符號，勾選全部三列，進行展開操作了：