Skip to main content

在 Excel 中查找、選取項目的背景色、篩選、計數、刪除重複值

Author: Kelly Last Modified: 2025-05-12

在 Excel 中,當我們手動輸入資料、從其他來源複製資料或因其他原因時,重複資料經常出現。有時,這些重複值是必要且有用的,但有時卻會導致錯誤或誤解。本文將介紹如何利用公式、條件格式規則、第三方外掛等方法,快速識別、選取項目的背景色、篩選、計數及刪除重複值。

目錄


1. 查找並選取項目的背景色重複值

當你在一列或區域中遇到重複值時,通常會想要快速找出這些重複項。這一部分將說明如何快速查找或識別列中的重複資料,並選取項目的背景色重複儲存格、行,或根據某列的重複值選取項目的背景色行。

1.1 使用公式查找重複值

本節介紹一些公式,幫助你快速在一列或兩列中查找或識別重複值。

1.1.1 使用公式查找單一列中的重複儲存格

當只需在一列或清單中查找重複儲存格時,可以利用 COUNTIF 函數輕鬆快速找出並計算重複資料。
1. 選取要查找重複值的列旁的空白儲存格。

2. 輸入公式 =COUNTIF($C$3:$C$12, C3)>1,然後按 Enter 鍵。

3. 拖曳公式儲存格的自動填滿控點,將此公式複製到其他儲存格。

A screenshot of using the COUNTIF formula to find duplicates in one column in Excel

注意: 在公式 =COUNTIF($C$3:$C$12, C3)>1 中:
(1) $C$3:$C$12 是你要查找重複值的列或清單。由於複製公式時此列需保持不變,通常使用絕對參照“$”。
(2) C3 是指定列的第一個儲存格。這裡使用相對參照,因為複製公式時需自動變動。
(3) 此公式會返回 TRUEFALSETRUE代表對應值為重複,FALSE 則表示該值在列中唯一。
(4) 有時 TRUE 或 FALSE 不易直觀理解,可將原公式與 IF 函數結合,直接返回 Duplicates
=IF(COUNTIF($C$3:$C$12, C3)>1,"Duplicates","")
A screenshot of using IF and COUNTIF formulas to label duplicate cells in Excel

1.1.2 使用公式查找兩列中的重複儲存格

有時你需要比較兩列並找出重複值。例如,你有兩份姓名清單,想找出第二份清單中與第一份重複的姓名,可以結合 VLOOKUP 與 IFERROR 函數輕鬆完成。

1. 選取第二份姓名清單旁的空白儲存格。

2. 輸入公式 =IFERROR(VLOOKUP(D3,$B$3:$B$18,1,0),""), 然後按 Enter 鍵。

3. 拖曳公式儲存格的自動填滿控點,根據需要將公式複製到其他儲存格。

A screenshot of using the VLOOKUP formula to find duplicate names across two columns in Excel

注意: 上述公式中,
(1) D3 是第二份姓名清單的第一個儲存格。複製公式時需自動變動,因此使用相對參照。
(2) $B$3:$B$18 是第一份姓名清單。由於複製公式時需保持不變,這裡使用絕對參照。
(3) 當姓名與第一份清單重複時,公式會返回該姓名,否則返回空白。
(4) 你也可以使用以下公式 =IF(COUNTIF($B$3:$B$18,D3)>0,"Duplicates","") 來找出第二份清單中與第一份重複的姓名。此公式會返回 "Duplicates" 若對應姓名為重複時。
A screenshot of using COUNTIF to identify duplicates in two columns in Excel
(5) 若需跨兩個工作表查找兩列中的重複值,只需在比較列的參照前加上工作表名稱。例如,只需將 $B$3:$B$18 改為 Sheet1!$B$3:$B$18 於公式中。
A screenshot of referencing another sheet to find duplicates in Excel

1.1.3 使用公式查找區分大小寫的重複儲存格

上述公式在查找重複值時不考慮大小寫,也就是說「apple」會被視為與「APPLE」重複。你可以使用陣列公式,在單一列中查找並區分大小寫的重複值。

1. 選取要查找重複值的列旁的空白儲存格。

2. 輸入陣列公式 =IF(SUM((--EXACT($C$3:$C$12,C3)))<=1,"","Duplicate"), 並同時按下 Ctrl + Shift + Enter 鍵。

3. 拖曳公式儲存格的自動填滿控點,將陣列公式複製到其他儲存格。

A screenshot of using an array formula to find case-sensitive duplicates in Excel

注意: 上述陣列公式中:
(1) $C$3:$C$12 是你要查找重複值的列。此處為絕對參照,因為複製時需保持不變。
(2) C4 是該列的第一個儲存格。這裡為相對參照,複製時會自動變動。
(3) 若對應儲存格為重複,陣列公式會返回 "Duplicate",否則返回空白。

1.2 使用條件格式查找並選取項目的背景色重複值

有時你可能需要標記重複值或行,以提醒自己或讀者。本節將指導你如何利用條件格式規則,選取項目的背景色重複儲存格或行。

1.2.1 使用條件格式查找並選取項目的背景色重複儲存格

你可以利用條件格式功能,快速在一列或區域中選取項目的背景色重複儲存格。

1. 選取要選取項目的背景色重複儲存格的列。

2. 點擊 [常用] > [條件格式] > [醒目提示儲存格規則] > [重複值]。如下圖所示:

A screenshot of applying Conditional Formatting to highlight duplicate cells in Excel

3. 在彈出的重複值對話框中,從第一個下拉選單選擇「重複值」,從第二個下拉選單選擇一個選取項目的背景色方案,然後點擊「確定」按鈕。

注意:如果預設的選取項目的背景色方案無法滿足需求,可以從第二個下拉選單選擇「自訂格式」,然後在彈出的設定儲存格格式對話框中自訂選取項目的背景色、字型及儲存格邊框。

此時你會看到所有重複儲存格已在所選列中被選取項目的背景色,如下圖所示。
A screenshot of duplicate cells highlighted with Conditional Formatting in Excel

注意
(1) 選取項目的背景色重複儲存格後,可以輕鬆篩選出這些重複值。(點擊查看操作方法
(2) 選取項目的背景色重複儲存格後,也可以批量刪除這些重複值。(點擊查看操作方法

1.2.2 根據重複儲存格查找並選取項目的背景色行

有些用戶偏好根據某列的重複儲存格選取項目的背景色整行。這時可建立自訂條件格式規則來完成。

1. 選取要根據重複儲存格選取項目的背景色行的區域(不含標題列)。

2. 點擊 [常用] > [條件格式] > [新增規則]。

A screenshot showing accessing the New Rule option in the Conditional Formatting menu

3. 在 [新增格式規則] 對話框中:
(1) 點選「使用公式決定要設定格式的儲存格」選項;
(2) 在「此公式為 TRUE 時設定格式的儲存格」方塊中輸入公式 =COUNTIF($C$3:$C$12,$C3)>1
提示:公式中,$C$3:$C$12 為包含重複儲存格的列,$C3 為該列的第一個儲存格。
(3) 點擊 [格式] 按鈕。

A screenshot of setting up a Conditional Formatting rule to highlight duplicate rows in Excel

4. 在 [設定儲存格格式] 對話框中,根據需求指定填滿顏色、字型、儲存格邊框,然後連續點擊 [確定] 儲存設定。

A screenshot of formatting settings in Conditional Formatting for duplicates

此時,在所選區域內,根據指定列的重複儲存格,對應行已被選取項目的背景色。如下圖所示:

A screenshot of rows highlighted based on duplicates in a column in Excel

1.2.3 使用條件格式查找並選取項目的背景色重複行

要在某區域選取項目的背景色重複行,也可以利用條件格式功能實現。

1. 選取區域(不含標題列)。

2. 點擊 [常用] > [條件格式] > [新增規則]。

A screenshot showing accessing the New Rule option in the Conditional Formatting menu

3. 在 [新增格式規則] 對話框中:
(1) 點選「使用公式決定要設定格式的儲存格」選項;
(2) 在「此公式為 TRUE 時設定格式的儲存格」方塊中輸入公式 =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1
(3) 點擊 [格式] 按鈕。

A screenshot of identifying duplicate rows using Conditional Formatting in Excel

注意:在公式 =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. 在 [設定儲存格格式] 對話框中,根據需求指定選取項目的背景色顏色、字型、儲存格邊框等,然後連續點擊 [確定 ] 儲存設定。

A screenshot of the Format Cells dialog in Excel

此時,所選區域內的重複行已被識別並選取項目的背景色。如下圖所示:

A screenshot of rows highlighted based on duplicate rows in Excel

1.2.4 查找並選取項目的背景色除第一個外的重複值

你可能注意到,以上方法會將所有重複值都識別或選取項目的背景色。有時你只想看到除第一個外的重複項。這也可以透過 條件格式 功能及不同公式實現。

1. 選取包含標題的列。
提示:若需根據單一列的重複值選取項目的背景色行(除第一個外),請選取不含標題列的區域。

2. 點擊 [常用] > [條件格式] > [新增規則]。

A screenshot showing accessing the New Rule option in the Conditional Formatting menu

3. 在 [新增格式規則] 對話框中:
(1) 點選「使用公式決定要設定格式的儲存格」選項;
(2) 在「此公式為 TRUE 時設定格式的儲存格」方塊中輸入公式 =COUNTIF($C$3:$C3, C3)>1
提示:若要根據單一列的重複值選取項目的背景色行,請輸入公式 =COUNTIF($C$3:$C3, $C3)>1
(3) 點擊 [格式] 按鈕。
A screenshot of the New Formatting Rule dialog in Excel for finding duplicates except the first instances

4. 在彈出的 [設定儲存格格式] 對話框中,根據需求指定選取項目的背景色顏色、字型、儲存格邊框等,然後點擊 [確定] 儲存設定。

此時你會看到所選列(或根據指定列的重複值選取項目的背景色行)中,除第一個外的重複儲存格已被選取項目的背景色。如下圖所示:

A screenshot showing duplicate cells except the first instances highlighted in Excel

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 對話框中,選取要以不同顏色選取項目的背景色重複儲存格的列,然後點擊 [確定]。

A screenshot of the dialog for selecting a range to highlight duplicates in different colors

每組重複值現在都會以不同顏色選取項目的背景色。

A screenshot of duplicate series highlighted in different colors using VBA

1.4 使用第三方外掛查找並選取項目的背景色重複值

本節將推薦一些由第三方外掛提供的簡易工具,協助你快速查找、選擇、選取項目的背景色單一列或根據重複值選取項目的背景色行。

1.4.1 查找並選取項目的背景色單一列中的重複儲存格

首先介紹的工具是 Kutools for Excel 提供的「選擇重複與唯一儲存格」功能。此功能可快速找出唯一或重複儲存格。

Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

1. 選取要查找並選取項目的背景色重複儲存格的列或區域。

2. 點擊 Kutools > 選擇 > 選擇重複與唯一儲存格

3. 在「選擇重複與唯一儲存格」對話框中,根據需求勾選選項,然後點擊 [確定] 完成操作。
A screenshot of Kutools' Select Duplicate & Unique Cells feature for highlighting duplicate cells in one column

注意:在「選擇重複與唯一儲存格」對話框中:
(1) 若需選擇或選取項目的背景色除第一個外的所有重複值,請勾選「重複值(排除第一個符合的) 」選項。否則,勾選「重複值(包括第一個符合的)」。
(2) 若需選取項目的背景色重複值,請勾選「填充背景顏色」並指定顏色。
(3) 若需根據所選列的重複值選擇或選取項目的背景色行,請勾選「選擇整行」。
(4) 若需區分大小寫選擇或選取項目的背景色重複值,請勾選「區分大小寫」。

1.4.2 查找並選取項目的背景色兩列或工作表中的重複儲存格

Kutools for Excel也提供了強大的「儲存格對比」工具,能協助我們輕鬆查找並選取項目的背景色兩列中的重複儲存格。

Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

1. 點擊 Kutools > 儲存格對比,開啟「選擇相同與不同儲存格」對話框。

2. 在「選擇相同與不同儲存格」對話框中,分別在查找值於根據方框指定兩列,勾選相同值選項,並根據需求勾選其他選項。

注意:

(1) 若需查找重複行,請勾選「按行」選項;若查找重複儲存格,請勾選「按單元格」選項於「方式」區段;
(2) 若需選取項目的背景色重複行或儲存格,請勾選「填充背景顏色」並指定顏色;
(3) 若需根據重複值選擇或選取項目的背景色整行,請勾選「選擇整行」;
(4) 若需區分大小寫查找或選取項目的背景色重複值,請勾選「區分大小寫」。

3.連續點擊 [確定] 完成設定。

此時你會看到在「查找值於」方框指定的列中,重複值已被識別並選取項目的背景色。

A screenshot of Kutools' Select Duplicate & Unique Cells feature for highlighting duplicate cells in two columns


2. 篩選重複值

有時候,一列中出現重複值時,我們只想查看與重複資料相關的紀錄。因此,本部分將介紹兩種僅篩選出重複資料的解決方案。

2.1 使用條件格式篩選重複值

本方法將指導你透過條件格式規則識別並選取項目的背景色重複儲存格,然後在 Excel 中依選取項目的背景色顏色輕鬆篩選。

1.先對指定列套用條件格式以查找並選取項目的背景色重複值。(點擊查看操作方法

2. 點擊選取指定列的標題,然後點擊 [資料] > [篩選]。
A screenshot showing how to apply a filter in Excel

3. 接著點擊該列標題的篩選圖示 A screenshot of the filter icon in Excel's column header  於列標題中,然後選擇 依顏色篩選, 並於下拉清單中選擇指定的條件格式顏色。如下圖所示:

A screenshot of filtering duplicates by color in Excel

此時你會看到僅含重複儲存格的行被篩選出來。如下圖所示:

A screenshot showing rows with duplicate values filtered in Excel

2.2 使用輔助列篩選重複值

另外,我們也可以在輔助列中利用公式識別重複值,然後輕鬆透過輔助列篩選重複值。

1. 在原始資料旁新增輔助列,並將欄位標題命名為 Duplicate

2. 選取欄位標題下的第一個空白儲存格,輸入公式 =IF(COUNTIF($C$3:$C$12,C3)>1,"Duplicate",""),然後拖曳自動填滿控點將公式複製到其他儲存格。

A screenshot of using a helper column to identify duplicates in Excel

注意:上述公式中,$C$3:$C$12 為包含重複資料的列,C3 為該列(不含標題)的第一個儲存格。

3. 點擊選取欄位標題 – Duplicates,然後點擊 [資料] > [篩選]。

A screenshot showing how to apply a filter in Excel

4. 接著點擊篩選圖示 A screenshot of the filter icon in Excel's column header  於欄位標題中,只勾選 Duplicate, 並點擊 確定 按鈕。如下圖所示:

A screenshot of filtering helper column values to isolate duplicates in Excel

此時僅含重複值的行被篩選出來。如下圖所示:

A screenshot of rows with duplicates filtered using a helper column in Excel


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 返回計算結果。

A screenshot of counting case-sensitive duplicates in Excel using an array formula

注意:在此陣列公式中:
(1) B2:B20 為要計算重複值的列;(2) E2 為要計算出現次數的指定值儲存格。你也可以將儲存格參照換成帶引號的值,如 "Apple"

3.1.2 多條件計數重複值

有時你可能想依兩個或多個條件計算重複值。可使用 COUNTIFS 函數完成。
例如,以下有一份水果銷售表,需計算蘋果於2020/7/5 且銷售金額大於300 的重複次數,可依下列步驟操作:

A screenshot of a fruit sales table used for counting duplicates with multiple criteria

1. 選取一個空白儲存格。

2. 輸入公式 =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,">300")

3. 按 Enter 鍵取得計算結果。

A screenshot of using COUNTIFS to count duplicates based on multiple criteria in Excel

注意:上述公式中:
(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",""),然後拖曳自動填滿控點將公式複製到其他儲存格。
A screenshot of using a formula to count duplicates excluding first occurrences in Excel

注意:上述公式中:
(1) $B$3:B3 為要計算重複值的區域,B3會隨複製自動變動。
(2) B3 為指定列的第一個儲存格。
(3) 此公式會返回 YES 或空白。YES代表對應值為重複,空白則為唯一值。

此時指定列中的所有重複值已被識別。我們可統計公式結果,取得重複值總數。

3. 選取一個空白儲存格。

4. 輸入公式 =COUNTIF(C3:C16,"YES"),然後按 Enter 鍵。
A screenshot of calculating the total number of duplicates excluding first occurrences in Excel

注意:上述公式中:
(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 返回計算結果。

A screenshot of using an array formula to count duplicates including first occurrences in Excel

注意:上述公式中,B3:B16 為要計算包含第一次出現的重複值的指定列。

3.2.3 計算列中包含/排除第一次出現的重複值

為簡化操作並避免記憶冗長公式,你可以嘗試 Kutools for Excel 提供的「選擇重複與唯一儲存格」功能,快速計算指定清單或列中包含或排除第一次出現的重複值數量。

Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

1. 選取要計算重複值數量的列,點擊 Kutools > 選擇 > 選擇重複與唯一儲存格

2. 在「選擇重複與唯一儲存格」對話框中,根據需求勾選「重複值(排除第一個符合的)」或「重複值(包括第一個符合的)」,然後點擊 [確定]。

A screenshot of the Kutools Select Duplicate & Unique Cells tool used to count duplicates in Excel

3. 此時所有包含或排除第一次出現的重複值已被選取,並同時彈出對話框顯示已選取的儲存格數量。見上圖。

3.3 計算兩列中的重複值

3.3.1 使用公式計算兩列間的重複值

假設你想比較兩份姓名清單並計算其中的重複數量,該如何快速解決?我們也可以在 Excel 中用公式完成。

1. 選取一個空白儲存格。

2. 輸入公式 =SUMPRODUCT(--(ISNUMBER(MATCH(B3:B12,D3:D18,0))))

3. 按 Enter 鍵。

A screenshot showing how to count duplicates between two columns using a formula

注意:上述公式中:
(1) B3:B12 為要計算重複值的第一份姓名列。
(2) D3:D18 為根據其計算重複值的第二份姓名列。

3.3.2 使用第三方外掛計算兩列間的重複值

另外,我們也可以利用第三方外掛 Kutools for Excel,快速計算兩列間重複儲存格的總數。

Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

1. 點擊 Kutools > 選擇 > 選擇相同與不同儲存格

2. 在「選擇相同與不同儲存格」對話框中:
(1) 分別在「查找值於」和「根據 」方框指定兩列。
(2) 勾選「按單元格」選項。
(3) 勾選「相同值」選項。

A screenshot of the Kutools Select Same & Different Cells tool for counting duplicates

4. 點擊 [確定]。

此時第一列中的所有重複儲存格已被選取,並同時彈出對話框顯示已選取的重複儲存格數量。如下圖所示:

A screenshot showing the Kutools dialog displaying the count of duplicates between two columns

注意:此功能會計算你在「查找值於」 方框指定的列中的重複值總數。若需計算第二列的重複值總數,請再次使用「選擇相同與不同儲存格」功能,並將第二列指定於「查找值於」方框。

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 鍵取得計算結果。

A screenshot of counting each duplicate value only once using a formula in Excel

注意:上述公式中,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 返回計算結果。

A screenshot of counting case-sensitive duplicate values only once with an array formula

注意:上述陣列公式中,C3:C19 為要區分大小寫計算每組重複值一次的指定列。

3.4.3 使用第三方外掛每個重複值僅計算一次

若已安裝 Kutools for Excel,也可利用其「提取區域中唯一值的儲存格(包含第一個重複值)」功能,快速在 Excel 中每組重複值僅計算一次。

Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

1. 選取一個空白儲存格。

2. 點擊 Kutools >公式助手 > 計數 > 提取區域中唯一值的儲存格(包含第一個重複值)

3. 在「公式助手」對話框中,於「區域」方框指定要計算重複值一次的列,然後點擊 [確定]。

A screenshot showing the Kutools Count Cells with Unique Values feature

此時計算結果會立即填入所選儲存格。

3.5 計算單一列中每個重複值

一般來說,我們可用 COUNTIF 函數一次計算一個重複值,重複操作以計算其他重複值。但對多個重複值來說,這樣會浪費大量時間。本節將介紹三種方法,讓你在 Excel 中輕鬆快速完成此工作。

3.5.1 使用 SUBTOTAL 函數計算單一列中每個重複值

我們可以利用分類彙總功能,在 Excel 中計算一列中每組重複值的次數。

1. 選取要計算每組重複值的列,點擊 [資料 ] > [升序排序 ] 或 [ 降序排序]。
A screenshot of sorting a column to count duplicate values in Excel

2. 在彈出的排序警告對話框中,勾選展開選取範圍,然後點擊排序

此時你會看到選取區域已依指定列的重複值排序。
A screenshot of the sorted data after applying a sort command in Excel

3. 保持選取狀態,點擊 [資料] > [分類彙總]。
A screenshot of the Subtotal option on the Data tab in Excel

4. 在分類彙總對話框中:
(1) 從「每次變更時」下拉選單選擇指定列;
(2) 從「使用函數」下拉選單選擇「計數」;
(3) 僅勾選「將分類彙總加到」清單中的指定列;
(4) 點擊 [確定]。
A screenshot of the Subtotal dialog box and the result after applying the Subtotal function

此時你會看到每組重複值都已被計算,結果顯示於每組重複值下方,如上圖。

3.5.2 使用資料透視表計算單一列中每個重複值

我們也可以建立資料透視表,在 Excel 中快速批量計算每組重複值。

1. 選取包含指定列的區域,點擊 [插入 ] > [資料透視表]。

A screenshot of inserting a PivotTable in Excel

2. 在「建立資料透視表」對話框中,指定新資料透視表的位置,然後點擊 [確定]。

A screenshot of the Create PivotTable dialog in Excel

3. 在資料透視表欄位窗格中,將指定列拖曳至「」及「」區。此時你會看到指定列中每組重複值已批量計算。如下圖:

A screenshot of the PivotTable counting duplicate values in Excel

3.5.3 使用強大工具計算單一列中每個重複值

若已安裝 Kutools for Excel,可利用其易用的「高級合併行」功能,快速批量計算指定列中每組重複值。

Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

注意:「高級合併行」功能會修改所選區域,並根據指定關鍵列的重複值刪除行。為保護資料,建議先備份或複製資料至其他位置。

1. 在原始資料區域右側新增空白列,並將新列命名為 Count

A screenshot of preparing data for Advanced Combine Rows in Kutools

2. 同時選取原始資料區域及新列,點擊 Kutools > 合併與分割 > 高級合併行

3. 在「高級合併行」對話框中:
(1) 點選要計算每組重複值的指定列,然後點擊「關鍵列」
(2) 點選新列(Count),然後點擊「計算」>「計數」
(3)其他列如需組合或計算,可另行指定;
(4) 點擊 [確定]
A screenshot of the Advanced Combine Rows dialog

此時你會看到指定列中每組重複值已批量計算。如下圖:

A screenshot of the result after using Advanced Combine Rows

3.6 按順序計算重複值

假設一列中有一份水果清單,部分水果出現多次。現在你需要依出現順序標記每個重複水果,該如何處理?本節將介紹一個公式,讓你在 Excel 中輕鬆完成。

1. 在原始資料右側新增空白列。

2. 在新增空白列的第一個儲存格輸入公式 =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),"")

3. 拖曳此公式儲存格的自動填滿控點,將公式複製到其他儲存格。

A screenshot of counting duplicates in order using a formula in Excel

注意:上述公式中:
(1) $C$3:$C$14 為要按順序計算重複值的指定列。
(2) C3 為指定列的第一個儲存格。
(3) 若對應值為重複,公式會依出現順序返回序列號1、2、3…;若為唯一值則返回空白。


4. 刪除重複值

當一列或區域中堆疊了大量重複值時,有些用戶會尋找快速刪除重複值的方法。本部分將介紹多種在 Excel 中輕鬆刪除重複值的解決方案。

4.1 刪除列中除一個外的重複值

本節將說明如何快速從 Excel 的列或清單中刪除除第一次出現外的重複值。

4.1.1 使用刪除重複功能刪除除一個外的重複值

你可以利用「刪除重複」功能,直接刪除所有重複值,只保留第一次出現的項目。

1. 選取要刪除所有重複值(僅保留第一次出現)的列。

2. 點擊 [資料] > [刪除重複]。

Screenshot showing the Remove Duplicates option in the Excel Data tab

3. 在「刪除重複警告」對話框中,勾選「繼續使用目前選取範圍」,然後點擊 [刪除重複]

Screenshot of the Remove Duplicates Warning dialog box

提示:若要根據選取區域的重複值刪除整行,請勾選「展開選取範圍 」。

4. 在「刪除重複」對話框中,只勾選指定列,然後點擊 [確定]。

Screenshot of the Remove Duplicates dialog in Excel with column selection options

提示:若上一步已勾選「展開選取範圍」,此處會列出所有列。即使如此,仍只需勾選指定列。

5. 此時會彈出對話框,顯示已刪除的重複值數量。點擊 [確定] 關閉。

Screenshot of the confirmation dialog showing the count of removed duplicates

4.1.2 使用高級篩選功能刪除除一個外的重複值

你也可以利用「高級篩選」功能,輕鬆刪除指定列中的所有重複值。

1. 點擊 [資料] > [進階]。

Screenshot showing the Advanced Filter option in the Excel Data tab

2. 在「高級篩選」對話框中:
(1) 勾選「複製到其他位置」;
(2) 在「清單範圍」方框選取要刪除重複值的指定列;
(3) 在「複製到」方框指定要貼上的區域;
(4) 勾選「僅限唯一記錄」;
(5) 點擊 [確定]。

Screenshot of the Advanced Filter dialog with unique records option selected

此時你會看到指定列已貼到指定區域,且僅保留第一次出現的項目。如下圖:

Screenshot of filtered results after using Advanced Filter to remove duplicates in Excel

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. 在彈出的對話框中,指定要刪除重複值的區域,然後點擊 [確定]。

Screenshot of the dialog asking for the range to remove duplicates in Excel

此時,指定區域內除第一次出現外的所有重複值會立即被刪除。

注意:此 VBA 程式碼區分大小寫。

4.2 刪除重複值及原始值

一般情況下,我們會找出重複值並刪除除第一次出現外的重複項。但有些情境下,用戶希望刪除所有重複值(包含原始值)。本節將介紹相關解決方案。

4.2.1 使用條件格式刪除所有重複值及原始值

我們可以先用條件格式規則選取項目的背景色包含第一次出現的所有重複值,然後依選取項目的背景色顏色篩選出所有重複值,最後批量刪除這些儲存格。

1.先套用條件格式選取項目的背景色重複值。(點擊查看操作方法

2. 選取要刪除重複值(包含第一次出現)的列,點擊 [資料] > [篩選]。

A screenshot showing the Filter option in the Excel Data tab

3. 點擊篩選圖示 A screenshot of the filter icon in Excel's column header 於指定列標題,在下拉選單中選擇 依顏色篩選, 並於子選單中指定選取項目的背景色顏色。

Screenshot of Excel's filter options for duplicate values based on color

此時所有重複值已被篩選出來。

4. 選取所有篩選出的儲存格,右鍵點擊並選擇 [刪除行],在彈出的確認對話框中點擊 [確定 ]。

5. 此時所有重複值已批量刪除。保持篩選清單選取狀態,點擊 [篩選] > [資料]取消篩選。

Screenshot of Excel showing filtered duplicate rows deleted and filter cleared

此時你會看到所有包含第一次出現的重複儲存格已批量刪除,只剩唯一值。

注意:此方法會根據指定列的重複值刪除整行。

4.2.2 使用輔助列刪除所有重複值及原始值

我們也可以在輔助列用公式識別包含第一次出現的重複值,然後依公式結果篩選並批量刪除這些重複值。

1. 在指定列旁新增輔助列,於輔助列第一個儲存格輸入公式 =COUNTIF($B$3:$B$11,B3),然後拖曳自動填滿控點複製公式。如下圖:

Screenshot of a helper column identifying duplicates with a COUNTIF formula in Excel

注意:上述公式中,$B$3:$B$11 為要刪除重複值的指定列,B3 為該列的第一個儲存格。

2. 選取輔助列,點擊 [資料] > [篩選]。

A screenshot showing the Filter option in the Excel Data tab

3. 點擊篩選圖示 A screenshot of the filter icon in Excel's column header 於輔助列標題,在下拉選單中勾選除 1外的所有值,然後點擊 確定 按鈕。如下圖:

Screenshot showing duplicates filtered using a helper column in Excel

4. 此時所有重複值已被篩選出來。選取輔助列中篩選出的儲存格,右鍵點擊並選擇 [刪除行]。

Screenshot of rows selected for deletion in Excel after filtering duplicates

5. 在彈出的確認對話框中,點擊 [確定]。

6. 此時所有重複值及其行已批量刪除。繼續點擊 [資料] > [篩選]取消篩選。

Screenshot of results after deleting duplicate rows and removing the filter

此時你會看到所有包含第一次出現的重複值已批量刪除。

4.2.3 使用強大工具刪除所有重複值及原始值

若已安裝 Kutools for Excel,也可利用其「選擇重複與唯一儲存格」功能,快速選擇並刪除包含或排除第一次出現的重複值。

Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

1. 選取要刪除重複值的列。

2. 點擊 Kutools > 選擇 > 選擇重複與唯一儲存格

3. 在「選擇重複與唯一儲存格」對話框中,勾選「重複值(包括第一個符合的) 」,然後點擊 [確定]。

Screenshot of the Kutools Select Duplicate & Unique Cells dialog in Excel

注意:
(1) 若要選擇並刪除排除第一次出現的重複值,請勾選「重複值(排除第一個符合的) 」;
(2) 若要根據指定列的重複值選擇並刪除整行,請勾選「選擇整行」;
(3) 若要區分大小寫選擇並刪除重複值,請勾選「區分大小寫」;
(4) 若要選擇、選取項目的背景色並刪除重複儲存格或行,請勾選「填充背景顏色」或「填充字體顏色」並指定顏色。

4. 此時會彈出對話框顯示已選取的儲存格數量,點擊 [確定] 關閉。

5.右鍵點擊所選儲存格,選擇 [刪除]。

Screenshot of selecting the Delete option after identifying duplicates with Kutools and the Delete dialog with the Shift cells up option seleccted

6. 在彈出的刪除對話框中,勾選「上移儲存格」,然後點擊 [確定]。

此時所有包含第一次出現的重複值已批量刪除。

4.3 根據單一列中的重複值刪除行

多數情況下,我們會先在一列中識別重複值,然後根據重複值刪除整行。實際上,這與僅刪除單一列的重複值操作非常類似,因此可採用相似方法根據指定列的重複值刪除行。

第一種方法是利用內建的「刪除重複」功能,根據指定列的重複值刪除行。只需選取要刪除行的區域,點擊 [資料] > [刪除重複] 啟用功能,在「刪除重複」對話框中僅勾選指定列,然後點擊 [確定] 完成刪除。

Screenshot of the Remove Duplicates feature in Excel used to remove rows based on duplicates in a column

也可以利用條件格式與篩選功能,根據指定列的重複值選取項目的背景色行。首先,利用條件格式規則選取項目的背景色重複值(點擊查看操作方法),接著依顏色篩選區域,然後批量刪除所有篩選出的行,最後清除或取消篩選,即只剩指定列唯一值的行。

Screenshot of Excel showing rows highlighted using conditional formatting to identify duplicates for deletion

另外,你可以新增輔助列,利用公式 =COUNTIF($C$3:$C$21,C3) 識別指定列的重複值。然後在輔助列篩選大於1 的數字,批量刪除所有篩選出的行。清除篩選後,只剩指定列唯一值的行。

Screenshot of Excel showing a helper column with formula results to identify duplicates for deletion

第三方外掛 Kutools for Excel也提供非常方便的「選擇重複與唯一儲存格」功能,可快速根據指定列的重複值選擇行,然後右鍵刪除這些行。

Screenshot of Kutools' Select Duplicate & Unique Cells tool used to highlight and remove duplicate rows in Excel

Kutools for Excel 的「高級合併行」功能也可根據指定關鍵列的重複值快速刪除行。

Screenshot of Kutools' Advanced Combine Rows feature for advanced duplicate removal based on primary key

4.4 刪除兩列中的重複值

有時我們需要比較兩份清單或列,然後刪除其中的重複值。本節將介紹兩種解決方案。

4.4.1 使用輔助列刪除兩列中的重複值

我們可以新增輔助列並用公式識別兩列間的重複值,然後輕鬆篩選並刪除這些重複值。

1. 在指定列旁新增空白列。

2. 在輔助列(不含標題)的第一個儲存格輸入公式 =IF(ISERROR(MATCH(C2,$A$2:$A$13,0)),"Unique","Duplicate"),然後拖曳自動填滿控點複製公式。

Screenshot showing a helper column with formula identifying duplicates between two columns in Excel

注意:上述公式中:
(1) C2 為要刪除重複值的指定列第一個儲存格;
(2) $A$2:$A$13 為需比較的另一列;
(3) 若對應值與另一列重複,則返回 Duplicate,否則返回 Unique

3. 選取輔助列,點擊 [資料] > [篩選]。

A screenshot showing the Filter option in the Excel Data tab

4. 點擊篩選圖示 A screenshot of the filter icon in Excel's column header  於輔助列標題,在下拉選單中僅勾選 Duplicate, 然後點擊 確定 按鈕。

Screenshot of Excel displaying rows filtered based on duplicates between two columns

5. 此時所有重複值已被篩選出來。選取篩選出的儲存格,右鍵點擊並選擇 [刪除行],然後在彈出的確認對話框中點擊 [確定]。

6. 此時所有重複值已從指定列刪除。繼續點擊 [資料] > [篩選]取消篩選。

此時你會看到指定列中只剩唯一值。可根據需求刪除輔助列。

Screenshot showing the final unique rows after removing duplicates between two columns

注意:此方法會根據指定列的重複值刪除整行。

4.4.2 使用強大工具刪除兩列中的重複值

若已安裝 Kutools for Excel,可利用其強大的「選擇相同與不同儲存格」功能,快速選擇兩列間的重複值,然後輕鬆刪除。

Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

1. 點擊 Kutools > 選擇 > 選擇相同與不同儲存格以啟用此功能。

2. 在「選擇相同與不同儲存格」對話框中,分別於「查找值於」和「根據」方框指定兩列,勾選「按單元格」及「相同值」選項,然後點擊 [確定]。如下圖:

Screenshot of Kutools Select Same & Different Cells tool for comparing and identifying duplicates between columns

3. 此時兩列間的所有重複值已於第一列(你在「查找值於」方框指定的列)被選取。並在彈出的對話框中點擊 [確定]。

Screenshot of duplicates selected using Kutools for deletion in Excel

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%,每天為您減少數百次鼠標點擊!