跳到主要內容

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

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

 

使用公式在多列中查找重複行

使用條件格式突出顯示多列中的重複行

使用方便的功能選擇或突出顯示跨多列的重複行


使用公式在多列中查找重複行

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

1. 在相鄰的空白單元格中,本例中的單元格 D2,請輸入以下公式:

=IF(COUNTIFS($A$2:$A$12,$A2,$B$2:$B$12,$B2,$C$2:$C$12,$C2)>1, "Duplicate row", "")

2. 然後將填充手柄向下拖動到單元格以應用此公式,現在,您可以看到,如果在此使用範圍內有相同的行,它將顯示 行重複,請參見屏幕截圖:

  • 筆記:
  • 1.在公式中 $ A $ 2:$ A $ 12, $ B $ 2:$ B $ 12, $ C $ 2:$ C $ 12 指示要從中查找重複項的範圍列。 您可以根據需要更改它們。 和 A2, B2, C2 指出需要應用此公式的數據每一列中的第一個單元格,您也可以更改它們。
  • 2. 上述公式基於 3 列數據,如果您的數據范圍中有 4 列或更多列需要從第一行中找到重複值,您只需添加列引用,如下公式所示: =IF(COUNTIFS($A$2:$A$12,$A2,$B$2:$B$12,$B2,$C$2:$C$12,$C2,$D$2:$D$12,$D2)>1, "Duplicate row", "").

保養竅門:如果要查找沒有第一次出現的重複行,請應用以下公式:

=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2) >1, "Duplicate row", "")


使用條件格式突出顯示多列中的重複行

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

1。 第一步,您應該使用 CONCATENATE 函數將所有數據合併到每一行的一個單元格中。 在單元格 D2 中鍵入以下公式,然後將公式向下複製,直到最後一行數據查看屏幕截圖:

=CONCATENATE(A2,B2,C2)

2. 然後,選擇要查找重複行(包括 D 列中的公式)的範圍,然後轉到 首頁 選項卡,然後單擊 條件格式 > 新規則,請參見屏幕截圖:

3。 在 新格式規則 對話框,請執行以下操作:

  • 點擊 使用公式來確定要格式化的單元格 來自 選擇規則類型 列錶框;
  • 然後輸入這個公式 = COUNTIF($ D $ 2:$ D $ 12,$ D2)> 1 (突出顯示第一次出現的重複行) 要么 =COUNTIF($D$2:$D2,$D2)>1 (突出顯示沒有第一次出現的重複行) 進入 格式化此公式為真的值 文本框;
  • 最後點擊 格式 按鈕。

備註:在以上公式中, $ D $ 2:$ D $ 12 是您已合併其他列值的列D。

4。 在彈出 單元格格式 對話框,單擊 選項卡,然後選擇一種您需要突出顯示重複項的顏色。

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

用第一行突出顯示重複行 突出顯示沒有第一行的重複行

使用方便的功能選擇或突出顯示跨多列的重複行

上面的方法對您來說有些麻煩,因此,在這裡,我可以向您介紹一個簡單方便的工具-Excel的Kutools,其 選擇重複的和唯一的單元格 實用程序,您可以根據需要快速選擇重複的行或唯一的行。

注意:要應用此 選擇重複的和唯一的單元格,首先,您應該下載 Excel的Kutools,然後快速輕鬆地應用該功能。

安裝後 Excel的Kutools,請這樣做:

1。 點擊 庫工具 > 選擇 > 選擇重複和唯一單元格,請參見屏幕截圖:

2。 在 選擇重複的和唯一的單元格 對話框,請執行以下操作:

  • 點擊 button1  按鈕選擇您要使用的範圍;
  • 然後,選擇 每一行 來自 基於 部分;
  • 然後,檢查 重複(第一個除外) or 全部重複(包括第一個) 選項下 您需要的部分;
  • 最後,您可以為下面的重複行指定背景顏色或字體顏色 結果處理.

3。 然後點擊 OK,並選擇以下行的重複行:

選擇包括第一行在內的重複行 選擇不包括第一行的重複行
  • 筆記:
  • 1.如果您檢查 選擇整行 選項,將選擇整個重複或唯一行。
  • 2.如果選中區分大小寫選項,則將比較文本是否區分大小寫。

 下載並免費試用 Kutools for Excel現在!


更多相關文章:

  • 合併重複的行並求和Excel中的值
  • 在Excel中,當您具有一系列包含某些重複項的數據時,您可能總是會遇到此問題,現在您想要合併重複數據並在另一列中將相應的值求和,如下面的屏幕截圖所示。 您如何解決這個問題?
  • 在Excel中以不同的顏色突出顯示重複的值
  • 在Excel中,通過使用條件格式設置,我們可以輕鬆地用一種顏色突出顯示一列中的重複值,但是有時,我們需要突出顯示不同顏色的重複值,以便快速,輕鬆地識別重複項,如下面的屏幕快照所示。 您如何在Excel中解決此任務?
  • 對齊兩列中的重複項或匹配值
  • 如果您有兩個名稱列表,並且想要比較這兩列並查找兩者中的重複名稱,然後根據新列中的第一列對齊或顯示匹配的名稱,如下面的屏幕快照所示。 為了列出兩列中都存在的重複值,本文可能會介紹一些解決方法。

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Fórmulas lixo, nenhuma funciona!
This comment was minimized by the moderator on the site
Hello Carlos,
So sorry to hear that. But I did try all of the formulas in the article and they all works fine. Please see the screenshots I upload. And I would love to help you on this matter if only you could provide the details of the errors. Thanks.Sincerely,Mandy
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Hello, Elienay,
The above Sumproduct formula works well in my worksheet, could you give your problem as an image to insert here?
Thank you!
This comment was minimized by the moderator on the site
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")
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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 !!
This comment was minimized by the moderator on the site
Great Great Great Really best ideas I've ever seen
This comment was minimized by the moderator on the site
Yes!!! NASEER you can find duplicates... :)
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations