TIN HỌC VĂN PHÒNG

【Hướng Dẫn】2 Cách lọc trùng trong Excel

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.

lọc trùng trong excel
lọc trùng trong excel

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:

lọc trùng trong excel
lọc trùng trong excel – bảng dữ liệu mẫu

Để 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.
lọc trùng trong excel
lọc trùng trong excel – chọn vùng dữ liệu

  • 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…
lọc trùng trong excel
lọc trùng trong excel – chọn quy tắt được dựng sẵn

  • 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.
lọc trùng trong excel
lọc trùng trong excel – chọn OK

Kết quả sẽ tương tự như hình bên dưới:

lọc trùng trong excel
lọc trùng trong excel – kết quả

Đị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.
lọc trùng trong excel

  • Bước 2: Trong menu hiện ra, các bạn chọn vào Custom Format…
lọc trùng trong excel
lọc trùng trong excel – chọn 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

lọc trùng trong excel
lọc trùng trong excel – bảng dữ liệu mẫu

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…
lọc trùng trong excel
lọc trùng trong excel – tạo quy tắc mới

  • Bước 2: Chọn Use a formula to determine which cells to format trong phần Select a Rule Type.
lọc trùng trong excel
lọc trùng trong excel – chọn loại quy tắc

  • 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.
lọc trùng trong excel
lọc trùng trong excel – nhập công thức

  • Bước 4: Tiếp theo, các bạn chọn vào nút Format…
lọc trùng trong excel
lọc trùng trong excel – chọn 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.
lọc trùng trong excel
lọc trùng trong excel – chọ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.
lọc trùng trong excel
lọc trùng trong excel – xem trước định dạng

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)>1

Trong đó:

  • 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:

lọc trùng trong excel
lọc trùng trong excel
lọc trùng trong excel – kết quả

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:

lọc trùng trong excel
lọc trùng trong excel – bảng dữ liệu mẫu

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.
lọc trùng trong excel
lọc trùng trong excel – thêm cột mới

  • Bước 2: Nhập công thức vào ô B2.
lọc trùng trong excel
lọc trùng trong excel – nhập công thức

  • Bước 3: Áp dụng công thức có tất cả các ô còn lại trong cột B2.
lọc trùng trong excel
lọc trùng trong excel – áp dụng công thức cho các ô còn lại

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")
lọc trùng trong excel

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.

lọc trùng trong excel

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.
lọc trùng trong excel
lọc trùng trong excel – mở hộp thoại remove duplicate

  • 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.
lọc trùng trong excel
lọc trùng trong excel – chọn cột

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 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: