Cách làm nổi bật các ô và các hàng trùng lặp trong Excel

Trong hướng dẫn này, Học Excel Online sẽ hướng dẫn các bạn cách hiển thị các dữ liệu lặp trong Excel. Chúng ta sẽ có cái nhìn cận cảnh về các phương pháp khác nhau để ẩn các ô trùng lặp, toàn bộ các hàng hoặc các dữ liệu lặp liên tiếp bằng cách sử dụng định dạng có điều kiện.

Cách nhanh nhất để tìm và đánh dấu các dữ liệu lặp trong Excel là sử dụng định dạng có điều kiện. Ưu điểm lớn nhất của phương pháp này là nó không chỉ hiển thị cho bạn các dữ liệu lặp hiện có mà còn phát hiện và tô màu các dữ liệu lặp mới khi bạn nhập, chỉnh sửa hoặc chèn dữ liệu.

Ngoài ra, bạn cũng học được từ bài viết này một số cách để làm nổi bật dữ liệu lặp tùy thuộc vào nhiệm vụ cụ thể. Các kỹ thuật này hoạt động trong tất cả các phiên bản của Excel 2016, Excel 2013, Excel 2010 và thấp hơn.

Cách làm nổi bật các dữ liệu lặp trong Excel bằng cách sử dụng quy tắc built-in (với lần lặp đầu tiên)

Đối với người mới bắt đầu, trong tất cả các phiên bản Excel, có một quy tắc được xác định trước để làm nổi bật các ô trùng lặp. Để sử dụng quy tắc này trong các bảng tính, hãy thực hiện theo các bước sau:

1. Chọn dữ liệu bạn muốn kiểm tra dữ liệu lặp. Đó có thể là một cột, một hàng hoặc một dải ô.
2. Trên tab Trang chủ, trong nhóm Styles, chọn Conditional Formatting > Highlight Cells Rules > Duplicate Values…

3. Cửa sổ hộp thoại Duplicate Values sẽ mở ra với định dạng Light Red Fill and Dark Red Text được chọn theo mặc định. Để áp dụng định dạng mặc định, chỉ cần nhấp vào OK.

Ngoài việc điền vào màu đỏ và định dạng văn bản, một số ít các định dạng được xác định trước có sẵn trong danh sách thả xuống. Để làm nổi bật các dữ liệu lặp bằng cách sử dụng một số màu khác, hãy nhấp vào Custom Format… (mục cuối cùng trong trình đơn thả xuống) và chọn màu điền và / hoặc màu chữ theo ý thích.

Mẹo. Để đánh dấu các giá trị duy nhất, hãy chọn Unique ở hộp thoại bên tay trái.

Sử dụng quy tắc inbuilt, bạn có thể làm nổi bật các bản sao trong một cột hoặc trong một vài cột như thể hiện trong ảnh chụp màn hình sau:

Chú thích. Khi áp dụng quy tắc nhân bản được xây dựng trong hai hoặc nhiều cột, Excel không so sánh các giá trị trong các cột đó, nó chỉ đơn giản làm nổi bật tất cả các trường hợp trùng lặp.

Khi sử dụng quy tắc inbuilt của Excel để làm nổi bật giá trị lặp, xin lưu ý hai điều sau:

– Nó chỉ hoạt động cho các ô riêng lẻ. Để đánh dấu các hàng trùng lặp, bạn cần tạo các quy tắc của riêng mình dựa trên các giá trị trong một cột cụ thể hoặc bằng cách so sánh các giá trị trong một vài cột.
– Nó làm mờ các ô trùng lặp kể cả lần xuất hiện đầu tiên của dữ liệu lặp đó. Để làm nổi bật tất cả các bản sao trừ các trường hợp xuất hiện lần đầu tiên, hãy tạo một quy tắc định dạng có điều kiện dựa trên công thức này.

Xem thêm: các hàm excel cơ bản

Cách làm nổi bật các dữ liệu lặp mà không có lần xuất hiện đầu tiên

Để làm nổi bật các dữ liệu lặp tính từ lần xuất hiện thứ 2 trở đi, hãy chọn các ô mà bạn muốn tô màu và tạo quy tắc dựa trên công thức theo cách này:

1. Trên tab Trang chủ, trong nhóm Styles, chọn Conditional Formatting > New rule > Use a formula to determine which cells to format.
2. Trong hộp Format values where this formula is true, nhập một công thức tương tự như sau:
=COUNTIF($A$2:$A2,$A2)>1
Trong đó A2 là ô trên cùng của dải ô được chọn.
3. Nhấp vào nút Format… và chọn màu điền bôi đậm và / hoặc màu chữ mà bạn muốn.
4. Cuối cùng, nhấn OK để lưu và áp dụng quy tắc.

Kết quả là, các ô sao chép trừ các trường hợp đầu tiên sẽ được làm nổi bật với màu sắc bạn chọn:

Cách hiển thị các dữ liệu lặp xuất hiện lần thứ 3, thứ 4 và tất cả các dữ liệu lặp tiếp theo

Để xem các dữ liệu lặp bắt đầu với lần xuất hiện thứ N, ta cần tạo một quy tắc định dạng có điều kiện dựa trên công thức như trong ví dụ trước, với sự khác biệt duy nhất là thay thế > 1 ở cuối công thức bằng một số theo yêu cầu. Ví dụ:

Để làm nổi bật dữ liệu lặp thứ ba và tất cả các trường hợp trùng lặp tiếp theo, tạo một quy tắc định dạng có điều kiện dựa trên công thức này:

=COUNTIF($A$2:$A2,$A2)>=3

Để làm nổi bật dữ liệu lặp thứ 4 và tất cả các dữ liệu lặp tiếp theo, sử dụng công thức này:

=COUNTIF($A$2:$A2,$A2)>=4

Để làm nổi bật một giá trị lặp nhất định bạn sử dụng toán tử bằng (=). Ví dụ: để chỉ các dữ liệu lặp lần thứ 2, bạn sẽ làm theo công thức sau:

=COUNTIF($A$2:$A2,$A2)=2
Cách làm nổi bật các dữ liệu lặp trong một dải (nhiều cột)
Khi bạn muốn kiểm tra các dữ liệu lặp trên nhiều cột, không phải bằng cách so sánh các cột với nhau, bạn hãy sử dụng một trong các giải pháp sau.

Làm nổi bật các dữ liệu lặp trong nhiều cột bao gồm cả lần xuất hiện đầu tiên

Nếu lần xuất hiện đầu tiên của dữ liệu bị lặp nhiều lần cũng được coi là một bản sao thì cách dễ nhất là xây dựng công thức dữ liệu lặp trong Excel.

Hoặc, tạo một quy tắc định dạng có điều kiện với công thức này:

COUNTIF(range, top_cell)>1
Ví dụ: để làm nổi bật các bản sao trong dải A2:C8, công thức sẽ như sau:

=COUNTIF($A$2:$C$8, A2)>1

Hãy chú ý đến việc sử dụng các tham chiếu ô tuyệt đối cho dải ($A$2:$C$8), và các tham chiếu tương đối cho ô trên cùng (A2).

Học excel nhanh cực kỳ hiệu quả

Làm nổi bật các bản sao trong nhiều cột mà không có lần xuất hiện lần đầu

Các giải pháp cho trường hợp này sẽ có chút phức tạp hơn vì Excel không xây dựng quy tắc sẵn.

Để làm nổi bật các mục trùng lặp trong một số cột bỏ qua lần xuất hiện đầu tiên, bạn sẽ phải tạo ra 2 quy tắc với các công thức sau:

Nguyên tắc 1. Áp dụng cho cột đầu tiên

Ở đây bạn sử dụng chính xác cùng một công thức như chúng ta đã sử dụng để làm nổi bật các dữ liệu lặp mà không có lần xuất hiện đầu tiên trong một cột.

Trong ví dụ này, chúng ta đang tạo ra một quy tắc cho A2:A8 với công thức:

=COUNTIF($A$2:$A2,$A2)>1

Kết quả là các mục trùng lặp không gồm lần xuất hiện đầu tiên được đánh dấu sát cột bên trái của dải ô (chỉ có một mục như vậy trong trường hợp của ví dụ này):

Quy tắc 2. Áp dụng cho tất cả các cột tiếp theo

Để làm nổi bật các bản sao trong các cột còn lại (B2: C8), sử dụng công thức sau:

=COUNTIF(A$2:$A$8,B2)+COUNTIF(B$2:B2,B2)>1

Trong công thức trên, chức năng COUNTIF đếm những dữ liệu lặp trong cột đầu tiên được chỉ định sẵn, và COUNTIF sẽ thực hiện công việc tương tự với các cột tiếp theo.

Do đó, tất cả các mục lặp trừ lần xuất hiện đầu tiên của chúng được tìm thấy và được đánh dấu:

Làm nổi bật các dữ liệu lặp trong tất cả các cột với một quy tắc
Một giải pháp khác có thể sử dụng là thêm cột trống vào bên trái tập dữ liệu của bạn và kết hợp các công thức trên thành một công thức đơn lẻ như sau:

=IF(COLUMNS($B2:B2)>1,COUNTIF(A$2:$B$8,B2),0)+COUNTIF(B$2:B2,B2)>1

Trong đó B2 là ô đầu có dữ liệu trong cột thứ 2 của dải đích.

Để hiểu rõ hơn về công thức, hãy chia ra làm 2 phần chính:

– Đối với cột đầu tiên (B), điều kiện IF không thể đáp ứng được yêu cầu, vì vậy chỉ sử dụng chức năng COUNTIF thứ hai, là công thức thông thường mà chúng ta đã sử dụng để tìm các bản sao mà không có lần xuất hiện lần đầu tiên trong một cột.
– Đối với tất cả các cột tiếp theo (C2:D8), điểm mấu chốt là việc sử dụng các tham chiếu tuyệt đối và tương đối một cách thông minh trong hai hàm COUNTIF. Để làm cho mọi thứ dễ hiểu hơn,các bạn có thể thấy công thức thay đổi như thế nào khi áp dụng cho các ô