提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在Excel中查找並突出顯示某個範圍內的重複行?

有時,工作表的數據范圍中可能存在一些重複記錄,現在您要查找或突出顯示該範圍內的重複行,如下面的屏幕截圖所示。 當然,您可以通過檢查行來逐個找到它們。 但如果有數百行,這不是一個好的選擇。 在這裡,我將討論一些有用的方法來處理這個任務。

DOC-發現,重複功能於range1 -2 DOC-發現,重複功能於range2

在公式範圍內查找重複的行

使用“條件格式”突出顯示某個範圍內的重複行

使用Kutools for Excel選擇或突出顯示某個範圍內的重複行


選擇並突出顯示範圍內的重複值或唯一值或行:

Kutools for Excel選擇重複和唯一單元格 可以幫助您快速選擇並突出顯示重複值,行或唯一值,同時在一個範圍內對行進行突出顯示。

DOC-發現,重複功能於range16-16

Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!



以下公式可以幫助您找到重複的記錄,請按照以下步驟操作:

1。 在這種情況下,在相鄰的空白單元格中,單元格D2請輸入公式 =IF(SUMPRODUCT(($A$2:$A$10=A2)*1,($B$2:$B$10=B2)*1,($C$2:$C$10=C2)*1)>1,"Duplicates","No duplicates"),看截圖:

DOC-發現,重複功能於range3

2. 然後按 輸入 鍵,如果在這個使用的範圍內有相同的行,它將顯示 重複,如果這一行是唯一的,它將顯示 沒有重複。 看截圖:

DOC-發現,重複功能於range4

3。 然後選擇單元格D2,然後將填充句柄拖到要包含此公式的範圍,並找到所有重複的行。 看截圖:

DOC-發現,重複功能於range5

筆記:

1。 在公式, $ A $ 2:$ A $ 10, $ B $ 2:$ B $ 10, $ C $ 2:$ C $ 10 指出您想要從中找到重複的範圍列。 您可以根據需要更改它們。 和 A2, B2, C2 指示需要應用此公式的每個數據列中的第一個單元格,您也可以更改它們。

2。 上述公式基於3列中的數據,如果數據范圍中有4列需要從第一行中找到重複值,則公式將變為: =IF(SUMPRODUCT(($A$1:$A$10=A1)*1,($B$1:$B$10=B1)*1,($C$1:$C$10=C1)*1, ($D$1:$D$10=D1)*1)>1,"Duplicates","No duplicates").


如果您無法正確應用該公式,請不要擔心, 條件格式 實用程序還可以幫助您突出顯示重複的行。 採取以下步驟:

1。 你應該使用的第一步 CONCATENATE 函數將所有數據合併到每行中的一個單元格中。 鍵入此公式: = CONCATENATE(A2,B2,C2) 在單元格D2中,看截圖:

DOC-發現,重複功能於range6

2。 然後復制公式直到最後一行數據。 看截圖:

DOC-發現,重複功能於range7

3。 選擇要查找重複行的範圍(包括D列中的公式),然後轉到 主頁 選項卡,然後單擊 條件格式 > 新規則,看截圖:

DOC-發現,重複功能於range8

4。 在 新的格式規則 對話框,單擊 使用公式來確定要格式化的單元格,並輸入這個公式 = COUNTIF($ d $ 2:$ d $ 10,$ D2)> 1 進入下面的文本框中,看截圖:

DOC-發現,重複功能於range9

備註: $ d $ 2:$ d $ 10, 是您組合了其他列值的列D.

5。 然後點擊 格式 按鈕,然後單擊 選項卡中,選擇一種需要突出顯示重複項的顏色。

DOC-發現,重複功能於range10

6。 點擊 OK > OK 關閉對話框,重複行按照您一次選擇的顏色突出顯示,請參見屏幕截圖:

DOC-發現,重複功能於range11


上述方法對你來說有點麻煩,所以在這裡,我可以為你介紹一個簡單易用的工具 - Kutools for Excel,其 選擇重複和唯一的單元格 實用程序,您可以根據需要快速選擇重複行或唯一行。

Kutools for Excel : 與超過300方便的Excel加載項,在60天免費試用沒有限制.

如果你已經安裝 Kutools for Excel請按以下步驟操作:

1。 點擊 Kutools > 選擇 > 選擇重複和唯一單元格,看截圖:

DOC-發現,重複功能於range12

2。 在 選擇重複和唯一的單元格 對話框,單擊 button1 按鈕選擇要使用的範圍,然後檢查 重複(除1st之外) or 所有重複項目(包括1st之一) 選項下 您可以根據需要最後指定背景顏色或字體顏色 處理結果,看截圖:

DOC-發現,重複功能於range13

3。 然後點擊 OK,並選擇重複的行作為以下屏幕截圖:

選擇除第一個之外的重複項 選擇重複包括第一個
DOC-發現,重複功能於range14 DOC-發現,重複功能於range15

筆記:

1。 如果你檢查 選擇整個行 選項,將選擇整個重複或唯一的行。

2。 如果您選中區分大小寫選項,文本將被比較區分大小寫。

點擊了解更多關於此選擇重複和獨特單元格功能。

下載並免費試用 Kutools for Excel Now!


Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!


相關文章:

如何在Excel中篩選選定列中的唯一記錄?

如何刪除重複項,並用Excel中的空白單元格替換?


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Elienay · 4 months ago
    The formula "Sumproduct" does not work! The values ​​keep appearing non-duplicate, and since there are duplicate values, you must have got something wrong with this formula. Because I did the same and checked several times to find the error, but I was unsuccessful
    • To post as a guest, your comment is unpublished.
      skyyang · 3 months ago
      Hello, Elienay,
      The above Sumproduct formula works well in my worksheet, could you give your problem as an image to insert here?
      Thank you!
      • To post as a guest, your comment is unpublished.
        Elienay · 3 months ago
        I had to invent another formula to check for duplicate values, in fact I created two formulas! But with this "sumproduct" I couldn't fix it. However I already solved my problem, thanks! The formula I created looked like this: =IF(CONTIF($G$53:$G$55;G53)>1;"DUPLI";"NO")
  • To post as a guest, your comment is unpublished.
    SANDEEP KADAM · 3 years ago
    at the time of data entry can i stop duplicate entries in two columns
    example :- Table A Table B
    A 1
    B 2
    at the time data entry once A & 1 is coming than i don't enter this entry, can any formula & idea for this
  • To post as a guest, your comment is unpublished.
    SANDEEP KADAM · 3 years ago
    Can i find duplicate entries in two columns at time of data entry, that can i prevent duplicate entries in two columns
    example, Table A Table B
    A 1
    B 2
    at time of data entry next A and 1 i don't enter this entry, plz give any idea
  • To post as a guest, your comment is unpublished.
    Rahul Joshi · 3 years ago
    Hi,

    suppose i have data of 15 letters (alphanumeric), and i want it to be split in different columns.

    ex - ASDFGH11WE31005 this is the 15 letters code, i want it to be spilt in different columns like - AS DF GH 11 WE 3 1005
    pls suggest any shortcut or any formula to split it
  • To post as a guest, your comment is unpublished.
    Otsile · 3 years ago
    Good day,

    I am dealing with a similar problem but one that goes beyond just checking for duplicates and I am hoping you could shed some light in as to how to tackle it.

    To illustrate I will build up onto the problem you have already illustrated above and adding some more complexities to it. Suppose after checking for and finding duplicates (ie., product, order or quantity and price), you now want to select a shop from which you can now buy your products from (I assume the duplicates tells you what products in what amount you can buy at what prices, and there is that repeat of products, orders and prices). The Shop Name given is for the shop that actually has stock of items required.

    A B C D E F
    PRODUCT ORDER PRICE SHOP NAME Distance to Shop (miles) Shop chosen to buy from
    QQQQ 50 30 Shop A 15 ?
    PPPP 60 40 Shop A 15 ?
    XXXX 45 28 Shop B 30 ?
    QQQQ 50 30 Shop A 15 ?
    VVVV 65 42 Shop A 15 ?
    BBBB 48 21 Shop A 15 ?
    XXXX 45 28 Shop B 30 ?
    QQQQ 50 30 Shop B 30 ?
    MMMM 80 35 Shop B 30 ?

    Suppose you now know you can buy a product at the given quantities (order) and at the given prices, at either Shop A or Shop B or Shop B but now you want to decide on the shop to buy from. One of the factors used in the criteria for shop selection could be how far the shop is from your own location.

    Obviously for product XXXX the only shop to buy from, where the product is available is Shop B therefore the value to return under column E would always be Shop B.
    For product QQQQ, you would have the option of buying from either Shop A or Shop B. You now want Excel to have you choose a shop to buy from. You want to select the nearest shop.


    How would you go about using a formula to solve this one?


    Regards,


    Moses
  • To post as a guest, your comment is unpublished.
    Otsile · 3 years ago
    Suppose you have now ascertained that there are duplicates and the next thing is you want to check is if these duplicates (products, orders, and prices) can be obtained are from different shops. This is tantamount to introducing another column listingshops which actaully sell these products and you want to be able to select a shop to buy from based on another criteria not listed here (knowledge of shop location,distance to the shop, etc)

    Eg for Product QQQ, you can get same order at same price at both Shop A and Shop B and you wanna return either Shop A or Shop B based on that criteria you know. How would you tell Excel to return as a value either either Shop A or Shop B?

    PRODUCT ORDER PRICE SHOP NAME
    QQQQ 50 30 Shop A
    PPPP 60 40 Shop A
    XXXX 45 28 Shop B
    QQQQ 50 30 Shop A
    VVVV 65 42 Shop A
    BBBB 48 21 Shop A
    XXXX 45 28 Shop B
    QQQQ 50 30 Shop B
    MMMM 80 35 Shop B
  • To post as a guest, your comment is unpublished.
    Mohammad Kamran · 4 years ago
    This is very very very great INFO !! i was so confused to find this kind of formula in excel sheet but today i am so happy may god give you lots of happiness and success, Great Work buddy you're a Champ !!
  • To post as a guest, your comment is unpublished.
    Abid · 4 years ago
    Great Great Great
    Really best ideas I've ever seen
  • To post as a guest, your comment is unpublished.
    Maddy · 4 years ago
    Yes!!! NASEER you can find duplicates... :)
  • To post as a guest, your comment is unpublished.
    wendy · 5 years ago
    Thank you so much for this!! Just saved hours of work!!
  • To post as a guest, your comment is unpublished.
    NASEER · 5 years ago
    HI,

    can i able to find duplicate by comparing in two sheet and two column with the help of this sum product formula,pls give ur idea
  • To post as a guest, your comment is unpublished.
    Jayashree · 5 years ago
    Excellent information! Thanks to the good soul!