Excel刪除重復項與給它們標記顏色，查找篩選兩列或整行都相同的重復數(shù)據(jù)

在 excel 中，如果要給重復項（重復數(shù)據(jù)）標記顏色，可以用條件格式；如果要刪除重復數(shù)據(jù)，既可以用excel刪除重復項功能又可以用高級篩選；如果要查找（篩選）兩列或以上同時相同的數(shù)據(jù)與一行都相同的數(shù)據(jù)，需要用公式。

Excel刪除重復項功能既可以只刪除一列的重復項又可以把整行重復的都刪除。而要查找兩列或以上甚至一行都相同的數(shù)據(jù)，需用幾個函數(shù)組合實現(xiàn)，例如用 IF + Sum + Value 函數(shù)組合實現(xiàn)。

一、Excel重復項標記顏色

（一）用“突出顯示單元格規(guī)則”標記

1、單擊列號 A，選中第一列，選擇“開始”選項卡，單擊“條件格式”，在彈出的菜單中依次選擇“突出顯示單元格規(guī)則”→ 重復值，打開“重復值”窗口，單擊“確定”，則 A 列所有重復值用“淺紅填充色深紅色文本”標記，操作過程步驟，如圖1所示：

圖1

2、如果要用另一種顏色填充，可單擊“重復值”窗口右邊的下拉列表框，選擇一種顏色；如果沒有滿足要求的顏色，選擇最后的“自定義格式”，打開“設置單元格格式”窗口，選擇“填充”選項卡，選擇一種顏色。

提示：打開“重復值”窗口，也可以用快捷鍵 Alt + H + L + H + D，按鍵方法為：按住 Alt，分別按一次 H、L、H 和 D。

（二）用“僅對唯一值和重復值設置格式”標記

1、單擊列號 A，選中 A 列，當前選項卡為“開始”，單擊“條件格式”，在彈出的菜單中選擇“新建規(guī)則”，打開“新建格式規(guī)則”窗口，選擇“僅對唯一值和重復值設置格式”，“全部設置格式”選擇“重復”，單擊“格式”，打開“設置單元格格式”窗口，選擇“填充”選項卡，選擇一種顏色（如“綠色”），單擊“確定”兩次，則 A 列用綠色標出所有重復項；操作過程如圖2所示：

圖2

2、打開“新建格式規(guī)則”窗口也可以用快捷鍵 Alt + H + L + N，按鍵方法為：按住 Alt，依次按一次 H、L 和 N。

提示：這個標記方法只能標記一列中的重復數(shù)據(jù)，不能標記一行都重復的數(shù)據(jù)。

（三）去掉標記顏色的方法

1、選中 A 列，當前選項卡為“開始”，單擊“條件格式”，在彈出的菜單中依次選擇“清除規(guī)則”→ 清除所選單元格的規(guī)則，則 A 列所有顏色標記被清除，操作過程步驟，如圖3所示：

圖3

2、直接按快捷鍵 Alt+ H + L + C + S 也可以清除所選單元格的標記顏色，按鍵方法為：按住 Alt，分別按一次 H、L、C 和 S。如果要清除整個表格的顏色標記，可以按快捷鍵  Alt+ H + L + C + E，按鍵方法與上面一樣。

二、Excel刪除重復項，合并重復數(shù)據(jù)

（一）用“刪除重復項”刪除

1、刪除一行的重復項。選中表格其中一個單元格，選擇“數(shù)據(jù)”選項卡，單擊“刪除重復項”，打開“刪除重復項”窗口，列出有三個字段，勾選的表示把該字段計入重復項，保持勾選表格所有字段以把重復行都刪除，單擊“確定”，彈出一個提示兩個重復項被刪除小窗口，單擊“確定”，則重復的兩行（第 8 和 11 行）都被刪除，操作過程步驟，如圖4所示：

圖4

2、只刪除一列的重復項。單擊列號 A 選中 A 列，按住 Alt，依次按 A 和 M，打開“刪除重復項警告”窗口，選擇“以當前選區(qū)域排序”，單擊“刪除重復項”，打開“刪除重復項”窗口，單擊“確定”，在彈出的提示已刪除重復項窗口中單擊“確定”，則只有 A 列的重復項被刪除，與 A 列對應的其它列數(shù)據(jù)并沒有被刪除；操作過程步驟，如圖5所示：

圖5

提示：如果選擇“擴展選定區(qū)域”，則刪除一行的重復項，相當于圖4的操作。

3、以兩列計重復項，刪除一行的重復項。選中表格其中一個單元格，按住 Alt，分別按一次 A 和 M，打開“刪除重復項”窗口，單擊“姓名”不勾選它，只勾選“部門和職務”，單擊“確定”，彈出 4 個重復項被刪除提示小窗口，單擊“確定”，則 B 和 C 兩列都重復的項被刪除，分別是第 3 和 第 8 行與第 6 和第 9 行；操作過程步驟，如圖6所示：

圖6

提示：如果同時勾選兩個以上字段，則只有勾選的字段相同才算相同，并且是刪除一行而不是僅刪除所選字段。

（二）用“高級篩選”刪除

1、單擊列號 A 選中 A 列，并按住左鍵拖到 C 列，選中表格，選擇“數(shù)據(jù)”選項卡，單擊“排序和篩選”右上角的“高級”，打開“高級篩選”窗口，單擊“選擇不重復的記錄”勾選它，單擊“確定”，則重復項（重復行）被刪除；操作過程步驟，如圖7所示：

圖7

2、打開“高級篩選”窗口也可以按快捷鍵 Alt + A + Q，按鍵方法為：按住 Alt，依次按一次 A 和 Q。

三、Excel用公式查找重復項（篩選相同數(shù)據(jù)）

（一）僅查找一列的重復項

1、假如要查找 A 列員工“姓名”是否有重復項。雙擊 D2 單擊格，把公式 =IF(COUNTIF(A:A,A2)>1,"重復","") 復制到 D2，按回車，返回空；選中 D2，把鼠標移到 D2 右下角的單元格填充柄上，鼠標變?yōu)楹谏犹柡螅p擊左鍵，則把剩余行都標記出是否重復；操作過程步驟，如圖8所示：

圖8

2、公式 =IF(COUNTIF(A:A,A2)>1,"重復","") 說明：

A、A2 表示對列和行都是相對引用，往下拖時，A2 會自動變?yōu)?A3、A4、……；往右拖時，A2 會自動變?yōu)?B2、C2、……。

B、A 表示對列相對引用、對行絕對引用，往下拖時，A2 不會變?yōu)?A3、A4、……；但往右拖時，A2 會自動變?yōu)?B2、C2、……；A 與A 是一個意思。A:A 作用是：無論怎么往下拖，始終確保在 A2:A11 中統(tǒng)計。

C、COUNTIF(A:A,A2)>1 為 If 的條件，當公式在 D2 時，統(tǒng)計 A2 在 A2 至 A11 中的個數(shù)；當公式在 D3 時，它變?yōu)?COUNTIF(A:A,A3)>1，即統(tǒng)計 A3 在 A2 到 A11 中的個數(shù)，其它的以此類推。

D、由于 COUNTIF(A:A,A2) 的統(tǒng)計結(jié)果為 1，1>1 不成立，因此返回 If 的第三個參數(shù) ""，即返回空。而 COUNTIF(A:A,A3) 的統(tǒng)計結(jié)果為 2，2>1 成立，因此返回 If 的第二個參數(shù)，即返回“重復”。

提示：如果要統(tǒng)計兩列或以上都相同的重復項，不能這么寫公式 =IF(COUNTIFS(A:A,A2,B:B)>1,"重復","")，因為這樣是統(tǒng)計每列中的重復項，而不是先統(tǒng)計出 A 列的重復項，再在 B 列統(tǒng)計與 A 列重復項對應的值。

　　（二）查找兩列或整行同時相同的重復項（兩列對應行或整行重復才算重復項）

1、先查找兩列同時相同的重復項，例如：A3 與 A11 重復且 B3 與 B11 重復。雙擊 D2 單元格，把公式 =IF(SUM(--(A:A&B:B=A2&B2))>1,"重復","") 復制到 D2，按 Ctrl + Shift + 回車，返回空，雙擊 D2 的單元格填充柄，返回剩余行的重復標記結(jié)果；操作過程步驟，如圖9所示：

圖9

2、公式 =IF(SUM(--(A:A&B:B=A2&B2))>1,"重復","") 說明：

A、公式為數(shù)組公式，所以要按 Ctrl + Shift + 回車。

B、A:A 以數(shù)組形式返回 A2:A11 中的值，B:B 以數(shù)組形式返回 B2:B11 中的值。A:A&B:B 把 A2:A11 返回的值與B2:B11 返回的值連接起來，例如第一次返回 A2 和 B2，A2&B2 為"李秀麗財務部"，第二次返回 A3 和 B3,A3&B3 為"林語彤銷售部"，其它的以此類推，最后返回一個 A 列與 B 列對應行合并的數(shù)組。

C、則 A:A&B:B=A2&B2 變?yōu)?{"李秀麗財務部";"林語彤銷售部";"黃子辛銷售部";"王青瑗行政部";"趙云祥財務部";"黃子辛銷售部";"劉月芹行政部";"黃晨昊銷售部";"林語彤銷售部"}=A2&B2，接著，第一次取數(shù)組的第一個元素 "李秀麗財務部" 與 A2&B2（即"李秀麗財務部"）比較，由于它們相等，因此返回 True；第二次取數(shù)組的第二個元素 "林語彤銷售部" 與 A2&B2，由于不相等，因此返回 False，其它的以此類推，最后返回 {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}。

D、則 SUM(--(A:A&B:B=A2&B2)) 變?yōu)?SUM(--({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}))，進一步計算，把數(shù)組中的邏輯值轉(zhuǎn)為數(shù)值并把它們相加，結(jié)果為 1，-- 作用是把文本或邏輯值轉(zhuǎn)為數(shù)值以便計算，它相當于 Value 函數(shù)。Sum函數(shù)在這里用于統(tǒng)計兩列對應行都相同的個數(shù)。

E、則公式變?yōu)?=IF(1>1,"重復","")，1>1 為 If 的條件，由于它不成立，所以返回 If 的第三個參數(shù) ""（即空）。

3、查找整行都重復的重復項。只需把每列用 & 連接起來即可實現(xiàn)，公式可以這樣寫：=IF(SUM(--(A:A&B:B&C:C=A2&B2&C2))>1,"重復","")。

4、把上面的公式改 =SUM(--($A:$A&$B:$B&$C:$C=A1&B1&C1))>1，可以用顏色標記一行都重復的項，操作過程步驟，如圖10所示：

圖10

操作過程步驟說明：單擊列號 A 選中 A 列，并拖到 C 列，選中表格，按住 Alt，依次按一次 H、L 和 N，打開“新建格式規(guī)則”窗口，選擇“使用公式確定要設置格式的單元格”，把公式 =SUM(--($A:$A&$B:$B&$C:$C=A1&B1&C1))>1 復制到“為符合此公式的值設置格式”下的輸入框中，單擊“格式”，打開“設置單元格格式”窗口，選擇“填充”選項卡，選擇“綠色”，單擊兩次“確定”，則 A 列的重復項用綠色標記，雙擊 B8，輸入 0，A8 和 A5 的標記顏色被去掉，說明只有一行中的所有字段相同才標記綠色。

提示：由于公式合并了每列對應行的數(shù)據(jù)，雖然只標記了 A 列的重復數(shù)據(jù)，但所標記的都是一行都重復的。

（三）重復項第一項標記 1，第二項后標記大于 1

1、同樣以查找兩列和整行同時相同的數(shù)據(jù)為例。雙擊 D2 單元格，把公式 =SUM(--($A:A2&$B:B2=A2&B2)) 復制到 D2，按回車，返回1，雙擊 D2 的單元格填充柄，則返回剩余行的統(tǒng)計結(jié)果，數(shù)值大于 1 的為重復項；操作過程步驟，如圖11所示：

圖11

2、公式 =SUM(--(A:A2&B:B2=A2&B2)) 說明：

A、A:A2 用于統(tǒng)計到當前行，當公式在 D2 時，A:A2 返回 A2，B:B2 返回 B2，A:A2&B:B2=A2&B2 變?yōu)?A2&B2=A2&B2，等式成立，返回 True，則公式變?yōu)?=SUM(--(TRUE))，進一步計算變?yōu)?=SUM(1)，因此結(jié)果返回 1。-- 的作用上面已經(jīng)介紹。

B、當公式在 D3 時，公式變?yōu)?=SUM(--(A:A3&B:B3=A3&B3))，與上面“（二）查找兩列或整行同時相同的重復項”的公式是一個意思。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。