Trong bài viết này, Tin Học Văn Phòng sẽ hướng dẫn các bạn cách lọc trùng trong Excel nhanh chóng bằng cách dùng công thức để tìm, tô màu ô hoặc loại bỏ các dữ liệu trùng nhau trong tập dữ liệu của bạn.
Vì dữ liệu trùng trong Excel có thể có nhiều dạng khác nhau, nên cũng sẽ có nhiều cách để tìm, cũng như xóa các dữ liệu trùng đó. Trong hướng dẫn này sẽ tập trung vào 2 cách đơn giản nhất.
Nội dung bài viết
Lọc trùng trong Excel bằng cách tô màu ô chứa dữ liệu trùng nhau
Để lọc trùng trong Excel bằng cách tô màu các ô chứa giá trị trùng lặp, chúng ta thường sử dụng chức năng Conditional Formatting trong Excel.
Trong trường hợp đơn giản nhất, bạn có thể áp dụng quy tắc được xây dựng sẵn bởi Excel; trong các trường hợp phức tạp hơn, bạn sẽ phải tạo quy tắc của riêng mình dựa trên công thức. Các ví dụ dưới đây sẽ minh họa cho cả hai trường hợp đó.
Tô màu ô trùng bao gồm cả những ô chứa dữ liệu xuất hiện đầu tiên
Trong ví dụ này, chúng ta sẽ sử dụng quy tắc được xây dựng sẵn trong tất cả các phiên bản Excel.
Như bạn có thể thấy từ tiêu đề, quy tắc chúng ta áp dụng trong ví dụ này sẽ đánh dấu tất cả các ô chứa giá trị trùng lặp, bao gồm cả ô chứa giá trị đầu tiên.
Ví dụ cho bảng dữ liệu như sau:
Để thực hiện tô màu các ô chứa dữ liệu bị trùng nhau, các bạn làm theo các bước hướng dẫn sau:
- Bước 1: Chọn các ô mà bạn muốn lọc dữ liệu trùng.
- Bước 2: Trong tab Home, các bạn chọn Conditional Formatting -> chọn tiếp Highlight Cells Rules -> cuối cùng chọn vào Duplicate Values…
- Bước 3: Trong hộp thoại Dubplicate Values hiện ra, để đơn giản, các bạn cứ giữ nguyên các tùy chọn, rồi nhấn OK. Khi đó, các ô chứa dữ liệu trùng nhau sẽ được tô màu đỏ nhạt.
Kết quả sẽ tương tự như hình bên dưới:
Định dạng mặc định cho các ô chứa dữ liệu trùng đó là ô sẽ được tô màu đỏ nhạt, còn chữ sẽ là màu đỏ đậm. Nếu không muốn định dạng như vậy, các bạn có thể tùy chọn định dạng theo ý mình bằng cách sau:
- Bước 1: Tại hộp thoại Duplicate Values, thay vì để mặc định rồi chọn OK. Các bạn hãy chọn vào menu xổ xuống bên phải.
- Bước 2: Trong menu hiện ra, các bạn chọn vào Custom Format…
- Bước 3: Trong hộp thoại Format Cells, các bạn hãy chọn các tùy chỉnh format cho ô chứa dữ liệu trùng mà bạn muốn. Sau đó nhấn OK.
Tô màu ô trùng loại trừ ô chứa dữ liệu xuất hiện đầu tiên
Giả sử bạn có một bảng dữ liệu, và bạn muốn tô màu các ô trùng mà loại trừ ô chứa dữ liệu xuất hiện đầu tiên.
Như hình trên, ô chứ số 1 đầu tiên (A2) sẽ không được tô màu, chỉ ô chứ số 1 thứ 2 (C4) mới được tô màu. Tương tự, ô chứ số 2 đầu tiên (A3) không được tô màu, mà ô chứ số 2 thứ 2 (B2) và thứ 3 (B2) mới được tô màu.
Để có thể thực hiện như trên, các bạn cần phải tạo 2 quy tắc theo các bước hướng dẫn sau đây:
Quy tắc 1: Áp dụng cho cột đầu tiên
Theo ví dụ trong bài viết này, các bạn hãy áp dụng quy tắc bên dưới cho các ô A2:A5 (cột đầu tiên).
=COUNTIF($A$2:$A2,$A2)>1
Các bạn lưu ý: hãy thay ô A2 trong công thức trên bằng ô đầu tiên trên cùng của dãy ô mà bạn chọn nhé. Ví dụ bạn chọn dãy ô B1:B10 thì thay ô A2 trong công thức trên bằng ô B1.
Các bước thực hiện như sau:
- Bước 1: Chọn các ô A2:A4, sau đó mở hộp thoại New Formatting Rule bằng cách chọn vào mục Conditional Formatting ở tab Home -> chọn New Rule…
- Bước 2: Chọn Use a formula to determine which cells to format trong phần Select a Rule Type.
- Bước 3: Các bạn nhập công thức trên vào ô trong phần Format values where this formula is true. Hãy nhớ thay ô A2 bằng ô thực tế theo dữ liệu của các bạn.
- Bước 4: Tiếp theo, các bạn chọn vào nút Format…
- Bước 5: Thiết lập định dạng cho các ô chứ dữ liệu trùng, như kiểu chữ, màu ô,… sau đó nhấn OK.
- Bước 6: Cuối cùng, các bạn chọn vào nút OK để xác nhận tạo quy tắc mới.
Quy tắc 2: Áp dụng cho cột còn lại
Sau khi áp dụng quy tắc 1 cho các ô A2:A4 (cột đầu tiên). Tiếp theo, chúng ta sẽ tạo quy tắc thứ 2 để áp dụng cho các ô B2:C4 thuộc các cột còn lại.
Công thức sử dụng cho quy tắc thứ 2 như bên dưới.
=COUNTIF(A$2:$A$4,B2)+COUNTIF(B$2:B2,B2)>1Trong đó:
- A$2:$A$4: dãy ô thuộc cột đầu tiên khi chúng ta áp dụng quy tắc 1.
- B2: ô đầu tiên thuộc cột thứ 2
- B$2:B2: dãy ô thuộc cột thứ 2
Bạn hãy thay thế các ô trong công thức trên bằng các ô theo dữ liệu thật của các bạn.
Các bước tạo quy tắc 2 cũng tương tự như lúc các bạn tạo quy tắc 1, chỉ có 2 điểm khác biệt là:
- Tại bước 1, thay vì chọn ô A2:A4, các bạn hãy chọn dãy ô B2:C4 như trong ví dụ.
- Tại bước 3, các bạn hãy nhập công thức cho quy tắc 2.
Sau khi hoàn thành tạo 2 quy tắc trên, thì các bạn sẽ có được kết quả như sau:
Dùng công thức tìm các ô trùng trong Excel
Để tìm các ô chứa dữ liệu trùng nhau trên cùng một cột, các bạn có thể thực hiện bằng cách kết hợp các hàm COUNTIF và IF trong Excel như bên dưới.
Tìm tất cả các ô chứa dữ liệu trùng
Ví dụ chúng ta có bảng dữ liệu gồm một cột chứa tên như hình bên dưới:
Mong muốn của chúng ta là kiểm tra xem từng tên trong ô A2:A8 có bị trùng hay không.
Công thức áp dụng:
=IF(COUNTIF($A$2:$A$8, A2)>1, "Trùng", "Không trùng")Trong đó: A2 là ô đầu tiên, A8 là ô cuối cùng trong trong dãy ô chứa dữ liệu cần kiểm tra. Các bạn hãy thay các ô trong công thức trên thành các ô thực tế theo dữ liệu của bạn.
Các bước thực hiện:
- Bước 1: Tạo 1 cột mới kế bên cột tên để chứa kết quả kiểm tra xem có trùng hay không.
- Bước 2: Nhập công thức vào ô B2.
- Bước 3: Áp dụng công thức có tất cả các ô còn lại trong cột B2.
Tìm các ô chứa dữ liệu trùng trừ ô trùng đầu tiên
Để thực hiện tìm các ô chứa dữ liệu trùng trừ ô chứa dữ liệu trùng đầu tiên ra, các bạn áp dụng công thức sau:
=IF(COUNTIF($A$2:$A2, $A2)>1, "Trùng", "Không trùng")
Trong đó: A2 là ô đầu tiên trong dãy ô mà bạn cần kiểm tra dữ liệu trùng. Hãy thay ô A2 bằng ô theo dữ liệu thực tế của các bạn.
Các bước thực hiện thì tương tự như cách Tìm tất cả các ô chứa dữ liệu trùng, chỉ khác nhau ở công thức sử dụng.
Xóa dữ liệu trùng trong Excel
Để xóa dữ liệu trùng trong Excel, chúng ta sẽ dụng công cụ Remove Duplicate được tích hợp sẵn trong Excel.
Một số lưu ý trước khi sử dụng công cụ Remove Duplicate:
- Công cụ này là sẽ xóa toàn bộ các hàng dựa trên các giá trị trùng lặp trong một hoặc nhiều cột mà bạn chỉ định chứ không phải là xóa riêng lẽ từng ô.
- Không xóa hàng chứa giá trị trùng đầu tiên.
Để sử dụng công cụ Remove Duplicate, các bạn làm theo các bước hướng dẫn sau:
- Bước 1: Chọn vùng dữ liệu chứa các ô trùng mà bạn muốn xóa.
- Bước 2: Chọn vào công cụ Remove Duplicates trong tab Data.
- Bước 3: Trong hộp thoại Remove Duplicates, các bạn hãy chọn những cột mà bạn muốn kiểm tra dữ liệu trùng -> sau đó chọn OK.
Tùy theo cột mà bạn chọn, Excel sẽ tìm ra các hàng cần xóa.
Trong ví dụ này, mình chỉ chọn cột Tên, nên Excel sẽ tìm giá trị trùng dựa trên cột Tên. Khi đó, sẽ tìm thấy 2 tên trùng đó là Trung và Hải và sẽ xóa đi hàng 6 và 7.
Trên đây là hướng dẫn các bạn cách lọc trùng trong Excel cũng như cách xóa dữ liệu trùng bằng công cụ Remove Duplicate. Hy vọng bài viết sẽ giúp ích cho các bạn.
Chúc các bạn thành công!
Xem thêm: