在 Excel 中查找、選取項目的背景色、篩選、計數、刪除重複值
在 Excel 中,當我們手動輸入資料、從其他來源複製資料或因其他原因時,重複資料經常出現。有時,這些重複值是必要且有用的,但有時卻會導致錯誤或誤解。本文將介紹如何利用公式、條件格式規則、第三方外掛等方法,快速識別、選取項目的背景色、篩選、計數及刪除重複值。
目錄
- 1. 查找並選取項目的背景色重複值
- 1.1 使用公式查找重複值
- 1.2 使用條件格式查找並選取項目的背景色重複值
- 1.3以不同顏色查找並選取項目的背景色重複值
- 1.4 使用第三方外掛查找並選取項目的背景色重複值
- 2. 篩選重複值
- 2.1 使用條件格式篩選重複值
- 2.2 使用輔助列篩選重複值
- 3. 計數重複值
- 3.1 依條件計數重複值
- 3.2 計算單一列中重複值的總數
- 3.3 計算兩列中的重複值
- 3.4 僅計算一次重複值
- 3.5 計算單一列中每個重複值的次數
- 3.6 按順序計算重複值
- 4. 刪除重複值
1. 查找並選取項目的背景色重複值
當你在一列或區域中遇到重複值時,通常會想要快速找出這些重複項。這一部分將說明如何快速查找或識別列中的重複資料,並選取項目的背景色重複儲存格、行,或根據某列的重複值選取項目的背景色行。
1.1 使用公式查找重複值
本節介紹一些公式,幫助你快速在一列或兩列中查找或識別重複值。
1.1.1 使用公式查找單一列中的重複儲存格
當只需在一列或清單中查找重複儲存格時,可以利用 COUNTIF 函數輕鬆快速找出並計算重複資料。
1. 選取要查找重複值的列旁的空白儲存格。
2. 輸入公式 =COUNTIF($C$3:$C$12, C3)>1,然後按 Enter 鍵。
3. 拖曳公式儲存格的自動填滿控點,將此公式複製到其他儲存格。
注意: 在公式 =COUNTIF($C$3:$C$12, C3)>1 中:
(1) $C$3:$C$12 是你要查找重複值的列或清單。由於複製公式時此列需保持不變,通常使用絕對參照“$”。
(2) C3 是指定列的第一個儲存格。這裡使用相對參照,因為複製公式時需自動變動。
(3) 此公式會返回 TRUE 或 FALSE。TRUE代表對應值為重複,FALSE 則表示該值在列中唯一。
(4) 有時 TRUE 或 FALSE 不易直觀理解,可將原公式與 IF 函數結合,直接返回 Duplicates。
=IF(COUNTIF($C$3:$C$12, C3)>1,"Duplicates","")
1.1.2 使用公式查找兩列中的重複儲存格
有時你需要比較兩列並找出重複值。例如,你有兩份姓名清單,想找出第二份清單中與第一份重複的姓名,可以結合 VLOOKUP 與 IFERROR 函數輕鬆完成。
1. 選取第二份姓名清單旁的空白儲存格。
2. 輸入公式 =IFERROR(VLOOKUP(D3,$B$3:$B$18,1,0),""), 然後按 Enter 鍵。
3. 拖曳公式儲存格的自動填滿控點,根據需要將公式複製到其他儲存格。
注意: 上述公式中,
(1) D3 是第二份姓名清單的第一個儲存格。複製公式時需自動變動,因此使用相對參照。
(2) $B$3:$B$18 是第一份姓名清單。由於複製公式時需保持不變,這裡使用絕對參照。
(3) 當姓名與第一份清單重複時,公式會返回該姓名,否則返回空白。
(4) 你也可以使用以下公式 =IF(COUNTIF($B$3:$B$18,D3)>0,"Duplicates","") 來找出第二份清單中與第一份重複的姓名。此公式會返回 "Duplicates" 若對應姓名為重複時。
(5) 若需跨兩個工作表查找兩列中的重複值,只需在比較列的參照前加上工作表名稱。例如,只需將 $B$3:$B$18 改為 Sheet1!$B$3:$B$18 於公式中。
1.1.3 使用公式查找區分大小寫的重複儲存格
上述公式在查找重複值時不考慮大小寫,也就是說「apple」會被視為與「APPLE」重複。你可以使用陣列公式,在單一列中查找並區分大小寫的重複值。
1. 選取要查找重複值的列旁的空白儲存格。
2. 輸入陣列公式 =IF(SUM((--EXACT($C$3:$C$12,C3)))<=1,"","Duplicate"), 並同時按下 Ctrl + Shift + Enter 鍵。
3. 拖曳公式儲存格的自動填滿控點,將陣列公式複製到其他儲存格。
注意: 上述陣列公式中:
(1) $C$3:$C$12 是你要查找重複值的列。此處為絕對參照,因為複製時需保持不變。
(2) C4 是該列的第一個儲存格。這裡為相對參照,複製時會自動變動。
(3) 若對應儲存格為重複,陣列公式會返回 "Duplicate",否則返回空白。
1.2 使用條件格式查找並選取項目的背景色重複值
有時你可能需要標記重複值或行,以提醒自己或讀者。本節將指導你如何利用條件格式規則,選取項目的背景色重複儲存格或行。
1.2.1 使用條件格式查找並選取項目的背景色重複儲存格
你可以利用條件格式功能,快速在一列或區域中選取項目的背景色重複儲存格。
1. 選取要選取項目的背景色重複儲存格的列。
2. 點擊 [常用] > [條件格式] > [醒目提示儲存格規則] > [重複值]。如下圖所示:
3. 在彈出的重複值對話框中,從第一個下拉選單選擇「重複值」,從第二個下拉選單選擇一個選取項目的背景色方案,然後點擊「確定」按鈕。
注意:如果預設的選取項目的背景色方案無法滿足需求,可以從第二個下拉選單選擇「自訂格式」,然後在彈出的設定儲存格格式對話框中自訂選取項目的背景色、字型及儲存格邊框。
此時你會看到所有重複儲存格已在所選列中被選取項目的背景色,如下圖所示。
注意:
(1) 選取項目的背景色重複儲存格後,可以輕鬆篩選出這些重複值。(點擊查看操作方法)
(2) 選取項目的背景色重複儲存格後,也可以批量刪除這些重複值。(點擊查看操作方法)
1.2.2 根據重複儲存格查找並選取項目的背景色行
有些用戶偏好根據某列的重複儲存格選取項目的背景色整行。這時可建立自訂條件格式規則來完成。
1. 選取要根據重複儲存格選取項目的背景色行的區域(不含標題列)。
2. 點擊 [常用] > [條件格式] > [新增規則]。
3. 在 [新增格式規則] 對話框中:
(1) 點選「使用公式決定要設定格式的儲存格」選項;
(2) 在「此公式為 TRUE 時設定格式的儲存格」方塊中輸入公式 =COUNTIF($C$3:$C$12,$C3)>1;
提示:公式中,$C$3:$C$12 為包含重複儲存格的列,$C3 為該列的第一個儲存格。
(3) 點擊 [格式] 按鈕。
4. 在 [設定儲存格格式] 對話框中,根據需求指定填滿顏色、字型、儲存格邊框,然後連續點擊 [確定] 儲存設定。
此時,在所選區域內,根據指定列的重複儲存格,對應行已被選取項目的背景色。如下圖所示:
1.2.3 使用條件格式查找並選取項目的背景色重複行
要在某區域選取項目的背景色重複行,也可以利用條件格式功能實現。
1. 選取區域(不含標題列)。
2. 點擊 [常用] > [條件格式] > [新增規則]。
3. 在 [新增格式規則] 對話框中:
(1) 點選「使用公式決定要設定格式的儲存格」選項;
(2) 在「此公式為 TRUE 時設定格式的儲存格」方塊中輸入公式 =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) 點擊 [格式] 按鈕。
注意:在公式 =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1 中:
(1) $B$3:$B$12 為區域的第一列,$B3 為該列的第一個儲存格;
(2) $C$3:$C$12 為第二列,$C3 為該列的第一個儲存格;
(3) $D$3:$D$12 為第三列,$D3 為該列的第一個儲存格;
(4) 若區域有更多列,可依序在公式中加入列參照及其第一個儲存格。
4. 在 [設定儲存格格式] 對話框中,根據需求指定選取項目的背景色顏色、字型、儲存格邊框等,然後連續點擊 [確定 ] 儲存設定。
此時,所選區域內的重複行已被識別並選取項目的背景色。如下圖所示:
1.2.4 查找並選取項目的背景色除第一個外的重複值
你可能注意到,以上方法會將所有重複值都識別或選取項目的背景色。有時你只想看到除第一個外的重複項。這也可以透過 條件格式 功能及不同公式實現。
1. 選取包含標題的列。
提示:若需根據單一列的重複值選取項目的背景色行(除第一個外),請選取不含標題列的區域。
2. 點擊 [常用] > [條件格式] > [新增規則]。
3. 在 [新增格式規則] 對話框中:
(1) 點選「使用公式決定要設定格式的儲存格」選項;
(2) 在「此公式為 TRUE 時設定格式的儲存格」方塊中輸入公式 =COUNTIF($C$3:$C3, C3)>1;
提示:若要根據單一列的重複值選取項目的背景色行,請輸入公式 =COUNTIF($C$3:$C3, $C3)>1。
(3) 點擊 [格式] 按鈕。
4. 在彈出的 [設定儲存格格式] 對話框中,根據需求指定選取項目的背景色顏色、字型、儲存格邊框等,然後點擊 [確定] 儲存設定。
此時你會看到所選列(或根據指定列的重複值選取項目的背景色行)中,除第一個外的重複儲存格已被選取項目的背景色。如下圖所示:
1.3以不同顏色查找並選取項目的背景色重複值
利用條件格式功能選取項目的背景色重複儲存格或行時,所有重複值會以相同顏色顯示。但若能將不同系列的重複值以不同顏色標示,會更易於閱讀與區分。這時可透過 VBA 在 Excel 中實現。
1. 同時按下 Alt + F11 鍵,開啟 Microsoft Visual Basic for Applications 視窗。
2. 在視窗中,點擊 [插入] > [模組],然後將下方程式碼貼到新模組視窗。
VBA:以不同顏色選取項目的背景色重複儲存格:
Sub HighlightDuplicatesInDifferentColors() 'Update by Extendoffice 20201013 Dim xURg, xRg, xFRg, xRgPre As Range Dim xAddress As String Dim xDt As Object Dim xFNum, xCInt As Long Dim xBol As Boolean Dim xWs As Worksheet Dim xSArr Set xRg = Application.ActiveWindow.RangeSelection If xRg.Count > 1 Then xAddress = xRg.AddressLocal Else xAddress = xRg.Worksheet.UsedRange.AddressLocal End If On Error Resume Next Set xURg = Application.InputBox("Select range:", "Kutools for Excel", xAddress, , , , , 8) If xURg Is Nothing Then Exit Sub Set xURg = Intersect(xURg.Worksheet.UsedRange, xURg) Set xDt = CreateObject("scripting.dictionary") Set xWs = xURg.Worksheet xCInt = 5 xBol = Application.ScreenUpdating Application.ScreenUpdating = False For xFNum = 1 To xURg.Count Set xFRg = xURg.Item(xFNum) If Not IsError(xFRg) Then If xFRg.Value <> "" Then If xDt.exists(xFRg.Text) Then xSArr = Split(xDt(xFRg.Text), ";") If xSArr(1) = "Only" Then xCInt = xCInt + 1 xSArr(1) = xCInt Set xRgPre = xWs.Range(xSArr(0)) xRgPre.Interior.ColorIndex = xCInt xDt(xFRg.Text) = xSArr(0) & ";" & xSArr(1) End If xFRg.Interior.ColorIndex = xSArr(1) Else xDt(xFRg.Text) = xFRg.Address & ";Only" End If End If End If Next xURg.Worksheet.Active xURg.Select Application.ScreenUpdating = xBol End Sub
3. 按下 F5 鍵或點擊 [執行] 圖示執行 VBA。
4. 在彈出的 Kutools for Excel 對話框中,選取要以不同顏色選取項目的背景色重複儲存格的列,然後點擊 [確定]。
每組重複值現在都會以不同顏色選取項目的背景色。
1.4 使用第三方外掛查找並選取項目的背景色重複值
本節將推薦一些由第三方外掛提供的簡易工具,協助你快速查找、選擇、選取項目的背景色單一列或根據重複值選取項目的背景色行。
1.4.1 查找並選取項目的背景色單一列中的重複儲存格
首先介紹的工具是 Kutools for Excel 提供的「選擇重複與唯一儲存格」功能。此功能可快速找出唯一或重複儲存格。
1. 選取要查找並選取項目的背景色重複儲存格的列或區域。
2. 點擊 Kutools > 選擇 > 選擇重複與唯一儲存格。
3. 在「選擇重複與唯一儲存格」對話框中,根據需求勾選選項,然後點擊 [確定] 完成操作。
注意:在「選擇重複與唯一儲存格」對話框中:
(1) 若需選擇或選取項目的背景色除第一個外的所有重複值,請勾選「重複值(排除第一個符合的) 」選項。否則,勾選「重複值(包括第一個符合的)」。
(2) 若需選取項目的背景色重複值,請勾選「填充背景顏色」並指定顏色。
(3) 若需根據所選列的重複值選擇或選取項目的背景色行,請勾選「選擇整行」。
(4) 若需區分大小寫選擇或選取項目的背景色重複值,請勾選「區分大小寫」。
1.4.2 查找並選取項目的背景色兩列或工作表中的重複儲存格
Kutools for Excel也提供了強大的「儲存格對比」工具,能協助我們輕鬆查找並選取項目的背景色兩列中的重複儲存格。
1. 點擊 Kutools > 儲存格對比,開啟「選擇相同與不同儲存格」對話框。
2. 在「選擇相同與不同儲存格」對話框中,分別在查找值於和根據方框指定兩列,勾選相同值選項,並根據需求勾選其他選項。
注意:
(1) 若需查找重複行,請勾選「按行」選項;若查找重複儲存格,請勾選「按單元格」選項於「方式」區段;
(2) 若需選取項目的背景色重複行或儲存格,請勾選「填充背景顏色」並指定顏色;
(3) 若需根據重複值選擇或選取項目的背景色整行,請勾選「選擇整行」;
(4) 若需區分大小寫查找或選取項目的背景色重複值,請勾選「區分大小寫」。
3.連續點擊 [確定] 完成設定。
此時你會看到在「查找值於」方框指定的列中,重複值已被識別並選取項目的背景色。
2. 篩選重複值
有時候,一列中出現重複值時,我們只想查看與重複資料相關的紀錄。因此,本部分將介紹兩種僅篩選出重複資料的解決方案。
2.1 使用條件格式篩選重複值
本方法將指導你透過條件格式規則識別並選取項目的背景色重複儲存格,然後在 Excel 中依選取項目的背景色顏色輕鬆篩選。
1.先對指定列套用條件格式以查找並選取項目的背景色重複值。(點擊查看操作方法)
2. 點擊選取指定列的標題,然後點擊 [資料] > [篩選]。
3. 接著點擊該列標題的篩選圖示 於列標題中,然後選擇 依顏色篩選, 並於下拉清單中選擇指定的條件格式顏色。如下圖所示:
此時你會看到僅含重複儲存格的行被篩選出來。如下圖所示:
2.2 使用輔助列篩選重複值
另外,我們也可以在輔助列中利用公式識別重複值,然後輕鬆透過輔助列篩選重複值。
1. 在原始資料旁新增輔助列,並將欄位標題命名為 Duplicate。
2. 選取欄位標題下的第一個空白儲存格,輸入公式 =IF(COUNTIF($C$3:$C$12,C3)>1,"Duplicate",""),然後拖曳自動填滿控點將公式複製到其他儲存格。
注意:上述公式中,$C$3:$C$12 為包含重複資料的列,C3 為該列(不含標題)的第一個儲存格。
3. 點擊選取欄位標題 – Duplicates,然後點擊 [資料] > [篩選]。
4. 接著點擊篩選圖示 於欄位標題中,只勾選 Duplicate, 並點擊 確定 按鈕。如下圖所示:
此時僅含重複值的行被篩選出來。如下圖所示:
3. 計數重複值
本部分將指導你如何在 Excel 中計算重複值的數量。內容包含依條件計數、計算重複值總數、僅計算一次重複值,以及批量計算每個重複值等方法。
3.1依條件計數重複值
一般來說,我們可以使用 =COUNTIF(range, criteria)來計算指定區域內某個值出現的總次數。例如要計算「Apple」在 A2:A10 清單中出現幾次,可用公式 =COUNTIF(A2:A10, "Apple")。
但 =COUNTIF(range, criteria)只能計算指定的重複值。若要依兩個或多個條件計算重複值,或需區分大小寫時,以下方法可協助你解決。
3.1.1依條件計數區分大小寫的重複值
我們可以使用陣列公式,在 Excel 中依條件計算區分大小寫的重複值。例如要計算「Apple」在 B2:B21 清單中(區分大小寫)出現幾次,可按以下步驟操作:
1. 選取一個空白儲存格。
2. 輸入公式 =SUM(--EXACT(B2:B20,E2))。
3. 按下 Ctrl + Shift + Enter 返回計算結果。
注意:在此陣列公式中:
(1) B2:B20 為要計算重複值的列;(2) E2 為要計算出現次數的指定值儲存格。你也可以將儲存格參照換成帶引號的值,如 "Apple"。
3.1.2 多條件計數重複值
有時你可能想依兩個或多個條件計算重複值。可使用 COUNTIFS 函數完成。
例如,以下有一份水果銷售表,需計算蘋果於2020/7/5 且銷售金額大於300 的重複次數,可依下列步驟操作:
1. 選取一個空白儲存格。
2. 輸入公式 =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,">300")。
3. 按 Enter 鍵取得計算結果。
注意:上述公式中:
(1) B3:B20 為(第一個)日期列,G4 為日期條件;
(2) C3:C20 為(第二個)水果列,G3 為水果條件;
(3) D3:D20 為(第三個)金額列,">300" 為金額條件。
(4) 若表格有更多列與條件,可依序加入列參照與條件。
3.2 計算單一列中重複值的總數
假設一列中有一系列值,你想計算清單中重複值的總數,該如何處理?本節將說明如何在 Excel 中計算單一列中重複值的總數。
3.2.1 計算列中排除第一次出現的重複值
要計算列中除第一次出現外的所有重複值,請按以下步驟操作:
1. 選取列旁的空白儲存格。
2. 輸入公式 =IF(COUNTIF($B$3:B3,B3)>1,"YES",""),然後拖曳自動填滿控點將公式複製到其他儲存格。
注意:上述公式中:
(1) $B$3:B3 為要計算重複值的區域,B3會隨複製自動變動。
(2) B3 為指定列的第一個儲存格。
(3) 此公式會返回 YES 或空白。YES代表對應值為重複,空白則為唯一值。
此時指定列中的所有重複值已被識別。我們可統計公式結果,取得重複值總數。
3. 選取一個空白儲存格。
4. 輸入公式 =COUNTIF(C3:C16,"YES"),然後按 Enter 鍵。
注意:上述公式中:
(1) C3:C16 為上一個步驟用於識別重複值的區域。
(2) YES 為上一公式返回的值。
此時即可取得指定列內重複值的總數。此總數不包含第一次出現的值。
3.2.2 計算列中包含第一次出現的重複值
若要計算包含第一次出現的所有重複值,可使用陣列公式實現。
1. 選取一個空白儲存格。
2. 輸入公式 =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16) =1,1,0))。
3. 按 Ctrl + Shift + Enter 返回計算結果。
注意:上述公式中,B3:B16 為要計算包含第一次出現的重複值的指定列。
3.2.3 計算列中包含/排除第一次出現的重複值
為簡化操作並避免記憶冗長公式,你可以嘗試 Kutools for Excel 提供的「選擇重複與唯一儲存格」功能,快速計算指定清單或列中包含或排除第一次出現的重複值數量。
1. 選取要計算重複值數量的列,點擊 Kutools > 選擇 > 選擇重複與唯一儲存格。
2. 在「選擇重複與唯一儲存格」對話框中,根據需求勾選「重複值(排除第一個符合的)」或「重複值(包括第一個符合的)」,然後點擊 [確定]。
3. 此時所有包含或排除第一次出現的重複值已被選取,並同時彈出對話框顯示已選取的儲存格數量。見上圖。
3.3 計算兩列中的重複值
3.3.1 使用公式計算兩列間的重複值
假設你想比較兩份姓名清單並計算其中的重複數量,該如何快速解決?我們也可以在 Excel 中用公式完成。
1. 選取一個空白儲存格。
2. 輸入公式 =SUMPRODUCT(--(ISNUMBER(MATCH(B3:B12,D3:D18,0))))。
3. 按 Enter 鍵。
注意:上述公式中:
(1) B3:B12 為要計算重複值的第一份姓名列。
(2) D3:D18 為根據其計算重複值的第二份姓名列。
3.3.2 使用第三方外掛計算兩列間的重複值
另外,我們也可以利用第三方外掛 Kutools for Excel,快速計算兩列間重複儲存格的總數。
1. 點擊 Kutools > 選擇 > 選擇相同與不同儲存格。
2. 在「選擇相同與不同儲存格」對話框中:
(1) 分別在「查找值於」和「根據 」方框指定兩列。
(2) 勾選「按單元格」選項。
(3) 勾選「相同值」選項。
4. 點擊 [確定]。
此時第一列中的所有重複儲存格已被選取,並同時彈出對話框顯示已選取的重複儲存格數量。如下圖所示:
注意:此功能會計算你在「查找值於」 方框指定的列中的重複值總數。若需計算第二列的重複值總數,請再次使用「選擇相同與不同儲存格」功能,並將第二列指定於「查找值於」方框。
3.4 僅計算一次重複值
有時列中存在重複值,計算時我們只需每個重複值計算一次。例如有一系列值 A, A, B, C, C, C, D, E, E,需計算唯一值數量,結果為5(A, B, C, D, E)。本節將介紹兩種公式解決此問題。
3.4.1 使用公式每個重複值僅計算一次
你可以用以下公式快速將每個重複值僅計算一次:
1. 選取一個空白儲存格。
2. 輸入公式 =SUMPRODUCT((C3:C19<>"")/COUNTIF(C3:C19,C3:C19&""))。
3. 按 Enter 鍵取得計算結果。
注意:上述公式中,C3:C19 為要計算每個重複值一次的指定列。
3.4.2 使用陣列公式區分大小寫的重複值僅計算一次
計算清單時,可利用陣列公式在 Excel 中每個重複值(區分大小寫)僅計算一次。
1. 選取一個空白儲存格。
2. 輸入陣列公式 =SUM(IFERROR(1/IF(C3:C19<>"", FREQUENCY(IF(EXACT(C3:C19, TRANSPOSE(C3:C19)), MATCH(ROW(C3:C19), ROW(C3:C19)), ""), MATCH(ROW(C3:C19), ROW(C3:C19))),0),0))。
3. 同時按下 Ctrl + Shift + Enter 返回計算結果。
注意:上述陣列公式中,C3:C19 為要區分大小寫計算每組重複值一次的指定列。
3.4.3 使用第三方外掛每個重複值僅計算一次
若已安裝 Kutools for Excel,也可利用其「提取區域中唯一值的儲存格(包含第一個重複值)」功能,快速在 Excel 中每組重複值僅計算一次。
1. 選取一個空白儲存格。
2. 點擊 Kutools >公式助手 > 計數 > 提取區域中唯一值的儲存格(包含第一個重複值)。
3. 在「公式助手」對話框中,於「區域」方框指定要計算重複值一次的列,然後點擊 [確定]。
此時計算結果會立即填入所選儲存格。
3.5 計算單一列中每個重複值
一般來說,我們可用 COUNTIF 函數一次計算一個重複值,重複操作以計算其他重複值。但對多個重複值來說,這樣會浪費大量時間。本節將介紹三種方法,讓你在 Excel 中輕鬆快速完成此工作。
3.5.1 使用 SUBTOTAL 函數計算單一列中每個重複值
我們可以利用分類彙總功能,在 Excel 中計算一列中每組重複值的次數。
1. 選取要計算每組重複值的列,點擊 [資料 ] > [升序排序 ] 或 [ 降序排序]。
2. 在彈出的排序警告對話框中,勾選展開選取範圍,然後點擊排序。
此時你會看到選取區域已依指定列的重複值排序。
3. 保持選取狀態,點擊 [資料] > [分類彙總]。
4. 在分類彙總對話框中:
(1) 從「每次變更時」下拉選單選擇指定列;
(2) 從「使用函數」下拉選單選擇「計數」;
(3) 僅勾選「將分類彙總加到」清單中的指定列;
(4) 點擊 [確定]。
此時你會看到每組重複值都已被計算,結果顯示於每組重複值下方,如上圖。
3.5.2 使用資料透視表計算單一列中每個重複值
我們也可以建立資料透視表,在 Excel 中快速批量計算每組重複值。
1. 選取包含指定列的區域,點擊 [插入 ] > [資料透視表]。
2. 在「建立資料透視表」對話框中,指定新資料透視表的位置,然後點擊 [確定]。
3. 在資料透視表欄位窗格中,將指定列拖曳至「列」及「值 」區。此時你會看到指定列中每組重複值已批量計算。如下圖:
3.5.3 使用強大工具計算單一列中每個重複值
若已安裝 Kutools for Excel,可利用其易用的「高級合併行」功能,快速批量計算指定列中每組重複值。
注意:「高級合併行」功能會修改所選區域,並根據指定關鍵列的重複值刪除行。為保護資料,建議先備份或複製資料至其他位置。
1. 在原始資料區域右側新增空白列,並將新列命名為 Count。
2. 同時選取原始資料區域及新列,點擊 Kutools > 合併與分割 > 高級合併行。
3. 在「高級合併行」對話框中:
(1) 點選要計算每組重複值的指定列,然後點擊「關鍵列」;
(2) 點選新列(Count),然後點擊「計算」>「計數」;
(3)其他列如需組合或計算,可另行指定;
(4) 點擊 [確定]。
此時你會看到指定列中每組重複值已批量計算。如下圖:
3.6 按順序計算重複值
假設一列中有一份水果清單,部分水果出現多次。現在你需要依出現順序標記每個重複水果,該如何處理?本節將介紹一個公式,讓你在 Excel 中輕鬆完成。
1. 在原始資料右側新增空白列。
2. 在新增空白列的第一個儲存格輸入公式 =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),"")。
3. 拖曳此公式儲存格的自動填滿控點,將公式複製到其他儲存格。
注意:上述公式中:
(1) $C$3:$C$14 為要按順序計算重複值的指定列。
(2) C3 為指定列的第一個儲存格。
(3) 若對應值為重複,公式會依出現順序返回序列號1、2、3…;若為唯一值則返回空白。
4. 刪除重複值
當一列或區域中堆疊了大量重複值時,有些用戶會尋找快速刪除重複值的方法。本部分將介紹多種在 Excel 中輕鬆刪除重複值的解決方案。
4.1 刪除列中除一個外的重複值
本節將說明如何快速從 Excel 的列或清單中刪除除第一次出現外的重複值。
4.1.1 使用刪除重複功能刪除除一個外的重複值
你可以利用「刪除重複」功能,直接刪除所有重複值,只保留第一次出現的項目。
1. 選取要刪除所有重複值(僅保留第一次出現)的列。
2. 點擊 [資料] > [刪除重複]。
3. 在「刪除重複警告」對話框中,勾選「繼續使用目前選取範圍」,然後點擊 [刪除重複]。
提示:若要根據選取區域的重複值刪除整行,請勾選「展開選取範圍 」。
4. 在「刪除重複」對話框中,只勾選指定列,然後點擊 [確定]。
提示:若上一步已勾選「展開選取範圍」,此處會列出所有列。即使如此,仍只需勾選指定列。
5. 此時會彈出對話框,顯示已刪除的重複值數量。點擊 [確定] 關閉。
4.1.2 使用高級篩選功能刪除除一個外的重複值
你也可以利用「高級篩選」功能,輕鬆刪除指定列中的所有重複值。
1. 點擊 [資料] > [進階]。
2. 在「高級篩選」對話框中:
(1) 勾選「複製到其他位置」;
(2) 在「清單範圍」方框選取要刪除重複值的指定列;
(3) 在「複製到」方框指定要貼上的區域;
(4) 勾選「僅限唯一記錄」;
(5) 點擊 [確定]。
此時你會看到指定列已貼到指定區域,且僅保留第一次出現的項目。如下圖:
4.1.3 使用 VBA 刪除除一個外的重複值
你也可以利用 VBA,快速從 Excel 列中刪除除第一次出現外的重複值。
1. 按下 Alt + F11 鍵,開啟 Microsoft Visual Basic for Application 視窗。
2. 點擊 [插入 ] > [模組],然後將下方 VBA 程式碼貼到新模組視窗。
VBA:刪除除第一次出現外的重複值
Sub ExtendOffice_RemoveAllDeplicate()
Dim xRg As Range
Dim xURg, xFRg, xFFRg As Range
Dim xI, xFNum, xFFNum As Integer
Dim xDc As Object
Dim xDc_keys
Dim xBol As Boolean
Dim xStr As String
Dim xWs As Worksheet
Dim xURgAddress As String
On Error Resume Next
Set xRg = Application.InputBox("Select range:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xURg = Intersect(xRg.Worksheet.UsedRange, xRg)
Set xWs = xURg.Worksheet
Set xDc = CreateObject("scripting.dictionary")
xURgAddress = xURg.Address
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If (Not IsError(xFRg)) Then
If xFRg.Value <> "" And (Not IsError(xFRg)) Then
For xFFNum = xFNum + 1 To xURg.Count
Set xFFRg = xURg.Item(xFFNum)
If Not IsError(xFFRg) Then
If xFFRg.Value = xFRg.Value Then
xDc(xFFRg.Address) = ""
End If
End If
Next
End If
End If
Next
xStr = ""
xDc_keys = xDc.Keys
For xI = 1 To UBound(xDc_keys)
If xStr = "" Then
xStr = xDc_keys(xI)
Set xURg = xWs.Range(xStr)
Else
xStr = xStr & "," & xDc_keys(xI)
Set xURg = Application.Union(xWs.Range(xDc_keys(xI)), xURg)
End If
Next
Debug.Print xStr
xWs.Activate
xURg.Select
Selection.Delete Shift:=xlUp
xWs.Range(xURgAddress).Select
Application.ScreenUpdating = xBol
End Sub
3. 按下 F5 鍵或點擊 [執行] 按鈕執行 VBA。
4. 在彈出的對話框中,指定要刪除重複值的區域,然後點擊 [確定]。
此時,指定區域內除第一次出現外的所有重複值會立即被刪除。
注意:此 VBA 程式碼區分大小寫。
4.2 刪除重複值及原始值
一般情況下,我們會找出重複值並刪除除第一次出現外的重複項。但有些情境下,用戶希望刪除所有重複值(包含原始值)。本節將介紹相關解決方案。
4.2.1 使用條件格式刪除所有重複值及原始值
我們可以先用條件格式規則選取項目的背景色包含第一次出現的所有重複值,然後依選取項目的背景色顏色篩選出所有重複值,最後批量刪除這些儲存格。
1.先套用條件格式選取項目的背景色重複值。(點擊查看操作方法)
2. 選取要刪除重複值(包含第一次出現)的列,點擊 [資料] > [篩選]。
3. 點擊篩選圖示 於指定列標題,在下拉選單中選擇 依顏色篩選, 並於子選單中指定選取項目的背景色顏色。
此時所有重複值已被篩選出來。
4. 選取所有篩選出的儲存格,右鍵點擊並選擇 [刪除行],在彈出的確認對話框中點擊 [確定 ]。
5. 此時所有重複值已批量刪除。保持篩選清單選取狀態,點擊 [篩選] > [資料]取消篩選。
此時你會看到所有包含第一次出現的重複儲存格已批量刪除,只剩唯一值。
注意:此方法會根據指定列的重複值刪除整行。
4.2.2 使用輔助列刪除所有重複值及原始值
我們也可以在輔助列用公式識別包含第一次出現的重複值,然後依公式結果篩選並批量刪除這些重複值。
1. 在指定列旁新增輔助列,於輔助列第一個儲存格輸入公式 =COUNTIF($B$3:$B$11,B3),然後拖曳自動填滿控點複製公式。如下圖:
注意:上述公式中,$B$3:$B$11 為要刪除重複值的指定列,B3 為該列的第一個儲存格。
2. 選取輔助列,點擊 [資料] > [篩選]。
3. 點擊篩選圖示 於輔助列標題,在下拉選單中勾選除 1外的所有值,然後點擊 確定 按鈕。如下圖:
4. 此時所有重複值已被篩選出來。選取輔助列中篩選出的儲存格,右鍵點擊並選擇 [刪除行]。
5. 在彈出的確認對話框中,點擊 [確定]。
6. 此時所有重複值及其行已批量刪除。繼續點擊 [資料] > [篩選]取消篩選。
此時你會看到所有包含第一次出現的重複值已批量刪除。
4.2.3 使用強大工具刪除所有重複值及原始值
若已安裝 Kutools for Excel,也可利用其「選擇重複與唯一儲存格」功能,快速選擇並刪除包含或排除第一次出現的重複值。
1. 選取要刪除重複值的列。
2. 點擊 Kutools > 選擇 > 選擇重複與唯一儲存格。
3. 在「選擇重複與唯一儲存格」對話框中,勾選「重複值(包括第一個符合的) 」,然後點擊 [確定]。
注意:
(1) 若要選擇並刪除排除第一次出現的重複值,請勾選「重複值(排除第一個符合的) 」;
(2) 若要根據指定列的重複值選擇並刪除整行,請勾選「選擇整行」;
(3) 若要區分大小寫選擇並刪除重複值,請勾選「區分大小寫」;
(4) 若要選擇、選取項目的背景色並刪除重複儲存格或行,請勾選「填充背景顏色」或「填充字體顏色」並指定顏色。
4. 此時會彈出對話框顯示已選取的儲存格數量,點擊 [確定] 關閉。
5.右鍵點擊所選儲存格,選擇 [刪除]。
6. 在彈出的刪除對話框中,勾選「上移儲存格」,然後點擊 [確定]。
此時所有包含第一次出現的重複值已批量刪除。
4.3 根據單一列中的重複值刪除行
多數情況下,我們會先在一列中識別重複值,然後根據重複值刪除整行。實際上,這與僅刪除單一列的重複值操作非常類似,因此可採用相似方法根據指定列的重複值刪除行。
第一種方法是利用內建的「刪除重複」功能,根據指定列的重複值刪除行。只需選取要刪除行的區域,點擊 [資料] > [刪除重複] 啟用功能,在「刪除重複」對話框中僅勾選指定列,然後點擊 [確定] 完成刪除。
也可以利用條件格式與篩選功能,根據指定列的重複值選取項目的背景色行。首先,利用條件格式規則選取項目的背景色重複值(點擊查看操作方法),接著依顏色篩選區域,然後批量刪除所有篩選出的行,最後清除或取消篩選,即只剩指定列唯一值的行。
另外,你可以新增輔助列,利用公式 =COUNTIF($C$3:$C$21,C3) 識別指定列的重複值。然後在輔助列篩選大於1 的數字,批量刪除所有篩選出的行。清除篩選後,只剩指定列唯一值的行。
第三方外掛 Kutools for Excel也提供非常方便的「選擇重複與唯一儲存格」功能,可快速根據指定列的重複值選擇行,然後右鍵刪除這些行。
Kutools for Excel 的「高級合併行」功能也可根據指定關鍵列的重複值快速刪除行。
4.4 刪除兩列中的重複值
有時我們需要比較兩份清單或列,然後刪除其中的重複值。本節將介紹兩種解決方案。
4.4.1 使用輔助列刪除兩列中的重複值
我們可以新增輔助列並用公式識別兩列間的重複值,然後輕鬆篩選並刪除這些重複值。
1. 在指定列旁新增空白列。
2. 在輔助列(不含標題)的第一個儲存格輸入公式 =IF(ISERROR(MATCH(C2,$A$2:$A$13,0)),"Unique","Duplicate"),然後拖曳自動填滿控點複製公式。
注意:上述公式中:
(1) C2 為要刪除重複值的指定列第一個儲存格;
(2) $A$2:$A$13 為需比較的另一列;
(3) 若對應值與另一列重複,則返回 Duplicate,否則返回 Unique。
3. 選取輔助列,點擊 [資料] > [篩選]。
4. 點擊篩選圖示 於輔助列標題,在下拉選單中僅勾選 Duplicate, 然後點擊 確定 按鈕。
5. 此時所有重複值已被篩選出來。選取篩選出的儲存格,右鍵點擊並選擇 [刪除行],然後在彈出的確認對話框中點擊 [確定]。
6. 此時所有重複值已從指定列刪除。繼續點擊 [資料] > [篩選]取消篩選。
此時你會看到指定列中只剩唯一值。可根據需求刪除輔助列。
注意:此方法會根據指定列的重複值刪除整行。
4.4.2 使用強大工具刪除兩列中的重複值
若已安裝 Kutools for Excel,可利用其強大的「選擇相同與不同儲存格」功能,快速選擇兩列間的重複值,然後輕鬆刪除。
1. 點擊 Kutools > 選擇 > 選擇相同與不同儲存格以啟用此功能。
2. 在「選擇相同與不同儲存格」對話框中,分別於「查找值於」和「根據」方框指定兩列,勾選「按單元格」及「相同值」選項,然後點擊 [確定]。如下圖:
3. 此時兩列間的所有重複值已於第一列(你在「查找值於」方框指定的列)被選取。並在彈出的對話框中點擊 [確定]。
4.你可以直接按 Delete 鍵刪除這些重複值,或右鍵點擊並選擇 [刪除] 。
更多文章 ...
最佳辦公效率工具
🤖 | Kutools AI 助手:基於智能執行方式革新數據分析:智能執行 | 生成代碼 | 創建自訂公式 | 分析數據並生成圖表 | 調用 Kutools 函數… |
熱門功能:查找、標記重複值或識別重複項 | 刪除空行 | 合併列或單元格而不丟失數據 | 四捨五入無需公式 ... | |
高級 LOOKUP: 多條件 VLookup | 多值 VLookup | 多表查找 | 模糊查找 .... | |
高級下拉列表:快速創建下拉列表 | 依賴下拉列表 | 多選下拉列表 .... | |
列管理器: 添加特定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 比較區域和列 ... | |
特色功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿與工作表管理器 | 資源庫(自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按列表發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/斜體/刪除線...) ... | |
頂級 15 種工具集: 12 個文本工具(添加文本、刪除特定字符、...) | 50+ 圖表 類型(甘特圖、...) | 40+ 實用 公式(基於生日計算年齡、...) | 19 個插入工具(插入QR碼、根據路徑插入圖片、...) | 12 個轉換工具(金額轉大寫、匯率轉換、...) | 7 個合併與分割工具(高級合併行、分割儲存格、...) | ... 還有更多 |
使用 Kutools for Excel 提升您的 Excel 技巧,體驗前所未有的高效。 Kutools for Excel 提供超過 300 種高級功能來提高生產力並節省時間。 點擊這裡獲取您最需要的功能...
Office Tab 將標籤式界面帶到 Office,讓您的工作更加輕鬆
- 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中啟用標籤式編輯和閱讀。
- 在同一窗口的新標籤中打開和創建多個文檔,而不是在新窗口中。
- 將您的生產力提高 50%,每天為您減少數百次鼠標點擊!