Để xác định được những dữ liệu trùng lặp trong một tài liệu Google Sheets, cách tốt nhất là làm nổi bật chúng lên bằng màu sắc khác bằng cách sử dụng highlight.
Trong bài viết này, chúng tôi sẽ hướng dẫn bạn một vài phương pháp khác nhau để highlight dữ liệu trùng lặp trên Google Sheets.
Đánh dấu (Highlight) các ô trùng lặp trong một cột Google Trang tính
Chúng ta sẽ bắt đầu với ví dụ đơn giản nhất. Đó là khi bạn chỉ có một cột với các giá trị lặp lại:
Để highlight dữ liệu trùng lặp trong một cột của Google Trang tính, các bạn làm như sau sau:
- Chọn vùng dữ liệu trong bảng tính > Chọn Định dạng > Chọn Định dạng theo điều kiện để hiển thị bảng Quy tắc định dạng theo điều kiện.
- Tại Định dạng ô, bạn click mũi tên xuống chọn dòng Công thức tùy chỉnh là rồi nhập vào công thức dưới đây:
=COUNTIF($A$2:$A$10;$A2)>1
Chú ý: Nếu công thức trên lỗi, bạn có thể thay dấu “;” bằng dấu “ ‘ “ (lúc đó công thức sẽ là “=COUNTIF($A$2:$A$10,$A2)>1”)
- Chọn định dạng cho dữ liệu lọc được tại dòng Kiểu định dạng
- Nhấn Đã xong để hoàn thành
Công thức COUNTIF đó sẽ quét cột A của bạn và cho biết quy tắc bản ghi nào xuất hiện nhiều lần. Tất cả các ô trùng lặp này sẽ được tô màu theo cài đặt của bạn tại mục Kiểu định dạng:
Highlight dữ liệu trùng lặp ở nhiều cột Google Trang tính
Có thể xảy ra trường hợp các giá trị lặp lại sẽ nằm trong nhiều hơn một cột:
Vậy, làm cách nào để bạn có thể tìm và Highlight các dữ liệu trùng trong cả 3 cột của Google Trang tính? Sử dụng cả định dạng có điều kiện giống như trên với một vài điều chỉnh nhỏ như sau đây:
- Chọn vùng dữ liệu trong bảng tính > Chọn Định dạng > Chọn Định dạng theo điều kiện để hiển thị bảng Quy tắc định dạng theo điều kiện.
- Tại Định dạng ô, bạn click mũi tên xuống chọn dòng Công thức tùy chỉnh là rồi nhập vào công thức dưới đây:
=COUNTIF($A$2:$C$10;A2)>1
Chú ý: Nếu công thức trên lỗi, bạn có thể thay dấu “;” bằng dấu “ ‘ “ (lúc đó công thức sẽ là “=COUNTIF($A$2:$C$10,$A2)>1”)
- Chọn định dạng cho dữ liệu lọc được tại dòng Kiểu định dạng
- Nhấn Đã xong để hoàn thành
Không giống như COUNTIF đã nói ở trên, công cụ này quét tất cả 3 cột và đếm số lần mỗi giá trị từ bảng xuất hiện trong tất cả các cột. Nếu nhiều lần, định dạng có điều kiện sẽ đánh dấu các ô trùng lặp này trong bảng Google Trang tính của bạn.
Highlight cả hàng nếu dữ liệu trùng lặp nằm trong một cột
Tiếp theo là trường hợp bảng của bạn chứa các bản ghi khác nhau trong mỗi cột. Nhưng toàn bộ một hàng trong bảng này được coi là một mục dữ liệu duy nhất và bạn muốn highlight cả hàng nếu có dữ liệu trùng lặp:
Như bạn có thể thấy, có sự trùng lặp trong cột B: phần pasta & condiment đều lặp lại hai lần và bạn muốn đánh dấu cả hai hàng chứa phần này trong Trang tính.
Để làm việc đó bạn thực hiện như sau:
- Chọn vùng dữ liệu trong bảng tính (A2:C10) > Chọn Định dạng > Chọn Định dạng theo điều kiện để hiển thị bảng Quy tắc định dạng theo điều kiện.
- Tại Định dạng ô, bạn click mũi tên xuống chọn dòng Công thức tùy chỉnh là rồi nhập vào công thức dưới đây:
=COUNTIF($B$2:$B$10;$B2)>1
Chú ý: Nếu công thức trên lỗi, bạn có thể thay dấu “;” bằng dấu “‘“ (lúc đó công thức sẽ là “=COUNTIF($B$2:$B$10;$B2)>1”)
- Chọn định dạng cho dữ liệu lọc được tại dòng Kiểu định dạng
- Nhấn Đã xong để hoàn thành
Hàm COUNTIF này đếm các bản ghi trong cột B. Và sau đó làm nổi bật không chỉ các bản sao trong cột B mà còn các bản ghi có liên quan trong các cột khác.
Highlight các hàng trùng nhau tất cả các ô
Bây giờ, điều gì sẽ xảy ra nếu hàng có dữ liệu trong tất cả các cột đều giống nhau bên trong trang tính của bạn?
Làm cách nào để kiểm tra tất cả 3 cột trong bảng và đánh dấu các hàng trùng lặp tuyệt đối trong trang tính Google của bạn?
Sử dụng công thức này trong định dạng có điều kiện:
=COUNTIF(ArrayFormula($A$2:$A$10&$B$2:$B$10&$C$2:$C$10);$A2&$B2&$C2)>1
Giải thích cách hoạt động của nó:
- ArrayFormula($A$2:$A$10&$B$2:$B$10&$C$2:$C$10) : Nối 3 ô từ mỗi hàng thành một chuỗi văn bản giống như sau: VD: SpaghettiPasta9-RQQ-24
Vì vậy, trong ví dụ này, có 9 chuỗi như vậy – một chuỗi trên mỗi hàng.
- Sau đó COUNTIFS lấy từng chuỗi (bắt đầu từ chuỗi đầu tiên: $ A2 & $ B2 & $ C2) và tìm kiếm nó trong số 9 chuỗi đó.
- Nếu có nhiều hơn một chuỗi (> 1), các chuỗi trùng lặp này sẽ được đánh dấu.
Highlight dữ liệu trùng lặp lần thứ 2, 3, etc…
Giả sử bạn muốn giữ nguyên các dữ liệu đầu tiên của các hàng trùng lặp và xem tất cả các hàng khác nếu có.
Chỉ với một chút thay đổi trong công thức, bạn sẽ có thể Highlight các hàng trùng lặp này và bỏ qua hàng đầu tiên tìm thấy.
Bạn sử dụng công thức sau để hiện tất cả các hàng trùng lặp:
=COUNTIF(ArrayFormula($A$2:$A$10&$B$2:$B$10&$C$2:$C$10);$A2&$B2&$C2)>1
Và công thức sau để hiển thị các hàng trùng lặp từ thứ 2 trở đi:
=COUNTIF(ArrayFormula($A$2:$A2&$B$2:$B2&$C$2:$C2);$A2&$B2&$C2)>1
Sự khác nhau của 2 công thức trên nằm trong đối số COUNTIF đầu tiên:
$A$2:$A2&$B$2:$B2&$C$2:$C2
Thay vì đề cập đến tất cả các hàng như trong công thức đầu tiên, tôi chỉ sử dụng ô đầu tiên của mỗi cột.
Nó cho phép mỗi hàng tìm được sẽ kiểm tra phía trên xem có hàng giống với mình hay không. Nếu có sẽ Highlight. Vì vậy, chỉ có các hàng từ thứ 2 trở đi được đánh dấu lại.
Lọc dữ liệu trùng lặp bằng công cụ tiện ích
Ngoài cách sử dụng công thức như trên, vẫn còn một cách khác để bạn có thể Highlight dữ liệu trùng lặp bằng các công cụ tiện ích cài đặt thêm vào Google trang tính.
Tiện ích Remove Duplicates của Google Trang tính sẽ đánh dấu các dữ liệu trùng lặp cho bạn cho bạn.
1: Bạn chọn Tiện ích bổ sung > Chọn Tải tiện ích bổ sung
2: Bạn tìm từ khóa “remove duplicates” và chọn tiện ích của tác giả EXPAND9 như hình. Vì mình thấy của EXPAND9 là ngon và dễ dùng nhất.
3: Bạn nhấp vào nút Cài đặt.
4: Bạn nhấp lần lượt nút Tiếp tục > Chọn gmail của bạn > Cho phép > Xong.
5: Bạn quay lại trang Google Sheet có dữ liệu trùng lặp > Chọn Tiện ích bổ sung > Chọn Remove Duplicates > Chọn Start để bắt đầu lọc dữ liệu trùng lặp.
6: Bạn nhấp vào nút Select columns (Chọn cột) > Kick chọn hoặc bỏ các cột chứa dữ liệu mà bạn muốn lọc > Chọn Save (Lưu).
7: Highlight hoặc xóa dữ liệu trùng lặp Google Sheet. Nếu bạn để Highlight > nhấp Search bạn sẽ các có hàng dữ liệu bị trùng lần thứ 2 trở lên bị bôi vàng như hình này.
