Hướng dẫn how to compare two columns in excel and delete duplicates - cách so sánh hai cột trong excel và xóa trùng lặp

Bạn sẽ mất khoảng 10 phút để đọc bài viết này và trong 5 phút tiếp theo (hoặc thậm chí nhanh hơn nếu bạn chọn giải pháp thứ 2 được mô tả trong bài viết), bạn sẽ dễ dàng so sánh hai cột Excel cho các bản sao và xóa hoặc làm nổi bật các bản sao tìm thấy. Được rồi, đếm ngược được bắt đầu!

Excel là một ứng dụng rất mạnh mẽ và thực sự tuyệt vời để tạo và xử lý các mảng dữ liệu lớn. Bây giờ bạn có rất nhiều sổ làm việc với một nhóm dữ liệu, hoặc có thể chỉ là một bảng lớn, bạn có thể muốn so sánh 2 cột cho các bản sao và sau đó làm một cái gì đó với các mục được tìm thấy, ví dụ như xóa các hàng trùng lặp, các bản sao màu hoặc xóa nội dung của nội dung của nội dung Các tế bào trùng lặp. Hai cột này có thể được đặt trong một bảng, tiếp xúc hoặc không tiếp giáp hoặc chúng có thể cư trú trong 2 bảng tính khác nhau hoặc thậm chí là sách bài tập.

Giả sử, bạn có 2 cột có tên người - 5 tên trong cột A và 3 tên trong cột B và bạn muốn so sánh dữ liệu giữa hai cột này để tìm bản sao. Theo bạn hiểu, đây là dữ liệu không có thật chỉ để ví dụ nhanh; Trong bảng tính thực sự, bạn thường có hàng ngàn và hàng chục ngàn mục.

Biến thể A: Cả hai cột được đặt trên một tờ, trong một bảng duy nhất: Cột A và Cột B: Both columns are located on one sheet, in a single table: Column A and Column B

Biến thể B: Hai cột được đặt trên các bảng khác nhau: Cột A trong Sheet2 và Cột A trong Sheet3: Two columns are located on different sheets: Column A in Sheet2 and Column A in Sheet3

Công cụ trùng lặp Xóa tích hợp có sẵn trong Excel 2016, Excel 2013 và 2010 không thể xử lý kịch bản này vì nó không thể so sánh dữ liệu giữa 2 cột. Hơn nữa, nó chỉ có thể loại bỏ các bản sao, không có lựa chọn nào khác như làm nổi bật hoặc tô màu có sẵn, than ôi :-(.

Hơn nữa, tôi sẽ mô tả 2 cách có thể để so sánh hai cột Excel cho phép bạn tìm và xóa các mục trùng lặp:

So sánh 2 cột để tìm bản sao bằng cách sử dụng các công thức excel

Biến thể A: Cả hai cột đều nằm trong cùng một danh sách

1. Trong ô trống đầu tiên, trong ví dụ của chúng tôi, đây là ô C1, hãy viết công thức sau:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   Trong công thức của chúng tôi, A1 là ô đầu tiên của cột đầu tiên mà chúng tôi muốn sử dụng để so sánh. $ B $ 1 và $ B $ 10000 là địa chỉ của ô đầu tiên và tế bào cuối cùng của cột thứ 2 mà bạn muốn so sánh với sự chú ý của mình với tham chiếu ô tuyệt đối - dấu hiệu đô la ($) trước các chữ cái cột và số hàng. Tôi sử dụng tài liệu tham khảo tuyệt đối có chủ đích, để các địa chỉ ô không thay đổi khi sao chép công thức.A1 is the first cell of the first column that we want to use for comparison. $B$1 and $B$10000 are the addresses of the first and the last cell of the 2nd column that you want to compare against.Pay attention to the absolute cell reference - dollar signs ($) preceding the column letters and row numbers. I use the absolute reference on purpose, in order for the cell addresses to remain unchanged when copying the formula.

   Nếu bạn muốn tìm các bản sao trong cột B, hãy hoán đổi các tên cột để công thức trông như thế này:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   Thay vì "duy nhất"/"trùng lặp", bạn có thể viết nhãn của riêng mình, ví dụ: "Không tìm thấy"/"tìm thấy" hoặc chỉ để lại "sao chép" và gõ "" thay vì "duy nhất". Trong trường hợp thứ hai, bạn sẽ có các ô trống bên cạnh các ô mà các bản sao không được tìm thấy, tôi tin rằng bản trình bày như vậy thuận tiện hơn để phân tích dữ liệu.

2. Bây giờ chúng ta hãy sao chép công thức vào tất cả các ô của cột C, cho đến hàng cuối cùng chứa dữ liệu trong cột A. Để thực hiện việc này, đặt con trỏ ở góc dưới bên phải của ô C1 và con trỏ sẽ thay đổi thành một chữ thập đen, Như được hiển thị trong hình ảnh bên dưới: Nhấp vào nút chuột trái và giữ nó xuống, kéo đường viền xuống phía dưới chọn tất cả các ô nơi bạn muốn sao chép công thức. Khi tất cả các ô cần thiết được chọn, hãy giải phóng nút chuột trái:

   Click the left mouse button and holding it down drag the border downward selecting all cells where you want to copy the formula. When all needed cells are selected, release the left mouse button:

   Mẹo: Trong các bảng lớn, việc sao chép công thức bằng các phím tắt là nhanh hơn. Nhấp vào ô C1 để chọn nó và nhấn Ctrl + C (để sao chép công thức vào bảng tạm), sau đó nhấn Ctrl + Shift + End (để chọn tất cả các ô không trống trong cột C) và cuối cùng nhấn Ctrl + V (để dán công thức vào tất cả các ô được chọn). In large tables, it is faster to copy the formula using shortcuts. Click on cell C1 to select it and press Ctrl + C (to copy the formula to clipboard), then press Ctrl + Shift + End (to select all non-empty cells in Column C), and finally hit Ctrl + V (to paste the formula into all selected cells).

3. Tuyệt vời, tất cả các ô trùng lặp được gắn cờ là "trùng lặp":

Biến thể B: Hai cột nằm trên các bảng tính khác nhau (sổ làm việc)

1. Trong ô 1 của cột trống đầu tiên trong Sheet2 (cột B trong trường hợp của chúng tôi), hãy viết công thức:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   Trong đó Sheet3 là tên của trang tính mà cột thứ 2 được đặt và $ 1 $ 1: $ A $ 10000 là địa chỉ của các ô đầu tiên và cuối cùng của cột thứ 2 đó.

2. Tương tự như biến thể A.
3. Chúng tôi có kết quả như sau:

Bấm để tải xuống bảng tính với các ví dụ trên và công thức để so sánh 2 cột để tìm bản sao.

Làm việc với các bản sao được tìm thấy

Hoàn hảo, chúng tôi đã tìm thấy các mục trong cột đầu tiên (cột A) cũng tồn tại trong cột thứ hai (cột B). Bây giờ chúng ta cần phải làm điều gì đó với họ :)

Nó sẽ khá không hiệu quả và sẽ mất quá nhiều thời gian để xem qua toàn bộ bảng và xem xét các mục trùng lặp theo cách thủ công. Có nhiều cách tốt hơn.

Chỉ hiển thị các hàng trùng lặp trong cột A

Nếu các cột của bạn không có tiêu đề, bạn cần thêm chúng. Để làm điều này, hãy đặt con trỏ vào số chỉ ra hàng đầu tiên và nó sẽ thay đổi thành một mũi tên đen như trong ảnh chụp màn hình:

Nhấp chuột phải vào hàng đã chọn và chọn "Chèn" từ menu ngữ cảnh:

Đặt tên cho các cột của bạn, ví dụ: "Tên" và "Sao chép?". Sau đó chuyển sang tab dữ liệu và nhấp vào bộ lọc:Name" and "Duplicate?". Then switch to the Data tab and click Filter:

Sau đó, nhấp vào một mũi tên màu xám nhỏ bên cạnh "trùng lặp?" Để mở danh sách thả xuống, bỏ chọn tất cả các mục khác ngoài sao chép trong danh sách đó và bấm OK:Duplicate?" to open a drop down list, uncheck all items other than Duplicate in that list, and click OK:

Đó là nó, bây giờ bạn chỉ thấy những ô của cột A có các giá trị trùng lặp trong Colum B. Chỉ có ba ô như vậy trong bảng tính thử nghiệm của chúng tôi, vì bạn hiểu trong các tờ thực có khả năng sẽ có nhiều hơn, nhiều hơn trong số chúng:

Để hiển thị tất cả các hàng của cột A một lần nữa, hãy nhấp vào ký hiệu bộ lọc trong cột B bây giờ trông giống như một phễu có mũi tên nhỏ và kiểm tra "chọn tất cả". Ngoài ra, bạn có thể thực hiện tương tự thông qua tab dữ liệu -> Chọn & Bộ lọc -> Xóa, như được hiển thị trong ảnh chụp màn hình:

Màu sắc hoặc điểm nổi bật được tìm thấy trùng lặp

Nếu cờ "trùng lặp" không đủ cho mục đích của bạn và bạn muốn đánh dấu các ô trùng lặp bằng màu phông hoặc lấp đầy màu sắc hoặc theo một cách khác

Sau đó, lọc các bản sao như được giải thích ở trên, chọn tất cả các ô được lọc và nhấn Ctrl + F1 để mở hộp thoại Định dạng ô. Ví dụ, hãy thay đổi màu nền của các hàng trùng lặp thành màu vàng sáng. Tất nhiên, bạn có thể thay đổi màu nền của các ô bằng cách sử dụng tùy chọn Fill Color trên tab Home, nhưng lợi thế của hộp thoại Định dạng ô là nó cho phép bạn thực hiện tất cả các thay đổi định dạng tại một thời điểm:Ctrl + F1 to open the Format Cells dialog box. As an example, let's change the background color of duplicated rows to bright yellow. Of course, you can change the background color of cells using the Fill color option on the Home tab, but the advantage of the Format Cells dialog box is that it lets you make all formatting changes at a time:

Bây giờ bạn chắc chắn sẽ không bỏ lỡ một ô trùng lặp duy nhất:

Xóa các bản sao khỏi cột đầu tiên

Lọc bảng của bạn để chỉ các ô có giá trị trùng lặp hiển thị và chọn tất cả các ô đó.

Nếu 2 cột bạn đang so sánh được đặt trên các bảng tính khác nhau, tức là trong các bảng riêng biệt, nhấp chuột phải vào phạm vi đã chọn và chọn "Xóa hàng" khỏi menu ngữ cảnh:, i.e. in separate tables, right-click the selected range and choose "Delete Row" from the context menu:

Nhấp vào OK khi Excel sẽ yêu cầu bạn xác nhận rằng bạn thực sự muốn "xóa toàn bộ hàng tờ" và sau đó xóa bộ lọc. Như bạn có thể thấy, chỉ còn lại các hàng có giá trị duy nhất:

Nếu 2 cột được đặt trên một bảng tính, bên cạnh nhau (liền kề) hoặc không chạm vào nhau (không phù hợp), thì việc loại bỏ các bản sao sẽ phức tạp hơn một chút. Chúng ta không thể xóa toàn bộ các hàng có chứa các giá trị trùng lặp vì điều này cũng sẽ xóa các ô tương ứng trong cột thứ 2. Vì vậy, để chỉ để lại các mục duy nhất trong cột A, bạn làm như sau:, next to each other (adjacent) or not touching each other (nonadjacent), the removing duplicates is a bit more complex. We cannot delete entire rows that contain duplicate values because this would delete corresponding cells in the 2nd column too. So, in order to leave only unique entries in Column A, you do the following:

1. Lọc bảng sao cho chỉ các ô trùng lặp được hiển thị và chọn tất cả các ô đó. Nhấp chuột phải vào lựa chọn và chọn "Xóa nội dung":
2. Xóa bộ lọc.
3. Chọn tất cả các ô trong cột A bắt đầu từ ô A1 cho đến ô cuối cùng có chứa dữ liệu.
4. Chuyển đến tab Dữ liệu và nhấp vào Sắp xếp A đến Z. Trong cửa sổ hộp thoại mở ra, chọn "Tiếp tục với lựa chọn hiện tại" và nhấp vào Sắp xếp:
5. Xóa cột chứa công thức vì bạn không cần nó nữa, chỉ "Uniques" được để lại ở đó ngay bây giờ.
6. Đó là tất cả, bây giờ cột A chỉ chứa dữ liệu duy nhất không tồn tại trong cột B:now Column A contains only unique data that do not exist in Column B:

Như bạn thấy, không quá khó để loại bỏ các bản sao giữa hai cột Excel bằng các công thức. Mặc dù quá trình rất tốn thời gian và nhàm chán để viết và sao chép công thức, áp dụng và xóa bộ lọc mỗi khi bạn cần so sánh 2 cột trong bảng tính của bạn. Một giải pháp khác mà tôi sẽ mang đến sự chú ý của bạn đơn giản hơn nhiều và sẽ chỉ mất một phần thời gian chúng tôi dành cho phương pháp đầu tiên. Tôi tin rằng bạn sẽ tìm thấy những điều dễ chịu hơn để dành thời gian lưu lại;)

So sánh 2 cột Excel cho các bản sao bằng cách sử dụng Trình hướng dẫn trực quan

Và bây giờ hãy để tôi chỉ cho bạn cách so sánh hai cột cho các bản sao bằng cách sử dụng các công cụ Dedupe của chúng tôi cho Excel.

1. Mở bảng tính (hoặc bảng tính) nơi các cột bạn muốn so sánh được đặt.
2. Chọn bất kỳ ô nào trong cột 1, chuyển sang tab Dữ liệu ABleBits và nhấp vào nút so sánh các bảng:Compare Tables button:
3. Ở bước 1 của trình hướng dẫn, bạn sẽ thấy cột đầu tiên của bạn đã được chọn, vì vậy chỉ cần nhấp vào Tiếp theo.

   Ghi chú. Nếu bạn muốn so sánh không chỉ 2 cột, mà là 2 bảng, bạn cần chọn toàn bộ bảng đầu tiên trong bước này. If you want to compare not just 2 columns, but 2 tables, you need to select the entire first table in this step.

4. Ở bước 2 của trình hướng dẫn, chọn cột thứ 2 mà bạn muốn so sánh với. Chúng tôi chọn Sheet2 trong cùng một sổ làm việc. Trong hầu hết các trường hợp, trình hướng dẫn thông minh sẽ tự động chọn cột thứ 2, nếu vì một số lý do, điều này không xảy ra, hãy chọn cột đích bằng cách sử dụng chuột. Nếu bạn đang so sánh toàn bộ bảng, chọn toàn bộ bảng thứ 2.
5. Chọn để tìm các giá trị trùng lặp:Duplicate values:
6. Chọn cặp cột bạn muốn so sánh: Mẹo. Nếu bạn đang so sánh các bảng, bạn có thể chọn một số cặp cột để so sánh, ví dụ: tên đầu tiên và tên cuối cùng. Để biết thêm chi tiết, xin vui lòng xem cách loại bỏ các bản sao khỏi hai bảng tính Excel.

   Tip. If you are comparing tables, you can choose several column pairs for comparison, for example, first and last name. For more details, please see How to remove duplicates from two Excel spreadsheets.

7. Và cuối cùng, bạn phải quyết định những gì bạn muốn làm với những bản sao tìm thấy. Bạn có thể chọn xóa các mục trùng lặp, di chuyển hoặc sao chép chúng vào một bảng tính khác, thêm cột trạng thái (kết quả sẽ tương tự như giải pháp đầu tiên của chúng tôi với các công thức Excel), làm nổi bật các bản sao hoặc chỉ chọn tất cả các ô có giá trị trùng lặp: Mẹo. Không chọn xóa các bản sao, đặc biệt nếu bạn đang sử dụng công cụ lần đầu tiên. Thay vào đó, chọn để di chuyển Dupesto một bảng tính khác. Điều này sẽ loại bỏ các bản sao khỏi bảng đầu tiên, nhưng cho bạn cơ hội xem xét danh sách các mục được công nhận là trùng lặp. Khi so sánh bằng một số cột phù hợp trong các bảng lớn, có thể bạn đã vô tình quên chọn một cột chính với dữ liệu duy nhất và việc di chuyển các bản sao sẽ ngăn chặn sự mất dữ liệu không thể khắc phục được.

   Tip. Do not choose to delete duplicates, especially if you are using the tool for the first time. Instead, choose to move dupes to another worksheet. This will remove duplicates from the first table, but gives you a chance to review the list of entries recognized as duplicates. When comparing by several matching columns in large tables, it may happen that you accidentally forgot to select a key column with unique data, and moving duplicates will prevent an irretrievable loss of data.

8. Nhấp vào kết thúc và tận hưởng kết quả. Những gì chúng ta có bây giờ là một bàn đẹp, sạch sẽ không có bản sao:

Hãy nhớ rằng giải pháp trước đó và cảm nhận sự khác biệt :) Thật sự nhanh chóng và dễ dàng để dành các bảng tính của bạn với so sánh hai bảng. Trên thực tế, bạn sẽ mất ít thời gian hơn so với bạn đã dành cho việc đọc bài viết này.

Hiện tại, so sánh các bảng là một phần của Ultimate Suite cho Excel, một bộ sưu tập hơn 70 công cụ chuyên nghiệp che giấu hơn 300 trường hợp sử dụng. Đồng hồ đang tích tắc, vì vậy hãy nhanh chóng tải xuống và tải xuống ngay bây giờ!

Nếu bạn có câu hỏi hoặc một cái gì đó không rõ ràng, xin vui lòng gửi cho tôi một bình luận và tôi sẽ vui vẻ giải thích thêm. Cảm ơn bạn đã đọc!

Bạn cũng có thể quan tâm

Làm thế nào để bạn loại bỏ các bản sao bằng cách so sánh hai cột trong Excel?

Làm cách nào để loại bỏ các bản sao trong excel mà không ảnh hưởng đến các cột khác?

Làm thế nào để bạn so sánh hai cột trong Excel và tìm thấy dữ liệu bị thiếu?