Cách tô màu dữ liệu trùng lặp trong Google Sheets bằng COUNTIF

Tô màu ô trùng lặp trong một cột

1. Mở bảng tính trên trình duyệt, bôi đen cột cần kiểm tra, ví dụ A2:A100. Nên bỏ dòng tiêu đề để tránh tô nhầm.
2. Chọn menu Định dạng (Format) > Định dạng có điều kiện (Conditional formatting). Bảng Quy tắc định dạng có điều kiện mở ở cạnh phải.
3. Kiểm tra ô Áp dụng cho dải ô đã đúng vùng. Trong mục Định dạng ô nếu…, kéo xuống cuối danh sách và chọn Công thức tùy chỉnh là (Custom formula is).
4. Nhập công thức =COUNTIF($A$2:$A$100;A2)>1, chọn màu nền ở phần Kiểu định dạng, rồi nhấn Xong.

Công thức này đếm xem giá trị của mỗi ô xuất hiện bao nhiêu lần trong vùng $A$2:$A$100. Nếu nhiều hơn 1 lần, ô được tô màu. Dấu $ giữ cố định vùng đếm, còn A2 không có $ để Google Sheets tự dịch xuống A3, A4… cho từng ô.

Nếu muốn áp dụng cho cả cột kể cả dữ liệu thêm sau này, hãy dùng vùng mở $A$2:$A (không ghi số dòng cuối).

Công thức cho các trường hợp khác

• Chỉ tô bản lặp, giữ nguyên lần xuất hiện đầu tiên: =COUNTIF($A$2:A2;A2)>1. Vùng đếm mở rộng dần theo từng dòng, nên ô đầu tiên không bị tô, rất tiện khi muốn biết dòng nào nên xoá.
• Tô cả dòng khi cột A trùng: chọn vùng A2:D100 và dùng =COUNTIF($A$2:$A$100;$A2)>1. Dấu $ trước cột A buộc mọi ô trong dòng cùng xét cột A.
• Trùng trên nhiều cột cùng lúc (ví dụ cùng họ tên và cùng ngày sinh): =COUNTIFS($A$2:$A$100;$A2;$B$2:$B$100;$B2)>1.
• Trùng ở bất kỳ đâu trong bảng: chọn A2:D100 và dùng =COUNTIF($A$2:$D$100;A2)>1.

Lưu ý về dấu phân cách: nếu bảng tính đặt khu vực Việt Nam (Tệp > Cài đặt > Khu vực), các đối số ngăn cách bằng dấu chấm phẩy ;. Nếu khu vực là Hoa Kỳ, dùng dấu phẩy ,. Nhập sai dấu, Google Sheets sẽ báo công thức không hợp lệ.

Tô màu dữ liệu đã có ở trang tính khác

Định dạng có điều kiện không cho tham chiếu trực tiếp sang trang tính khác, nên bạn cần bọc vùng đó bằng hàm INDIRECT. Ví dụ, để tô các tên ở Trang tính 1 đã có trong cột A của Trang tính 2:
=COUNTIF(INDIRECT("'Trang tính 2'!A2:A");A2)>0

Cách này hữu ích khi đối chiếu danh sách đăng ký với danh sách đã thanh toán, hoặc kiểm tra khách hàng mới có trùng với tệp khách cũ hay không.

Cách xoá dữ liệu trùng lặp

1. Bôi đen vùng dữ liệu (nên chọn cả bảng để các cột đi cùng nhau).
2. Chọn Dữ liệu (Data) > Dọn dẹp dữ liệu (Data cleanup) > Xoá bản sao (Remove duplicates).
3. Đánh dấu Dữ liệu có hàng tiêu đề nếu có, chọn các cột dùng để xét trùng, rồi nhấn Xoá bản sao.

Google Sheets giữ lại dòng xuất hiện đầu tiên và báo số dòng đã xoá. Nếu không muốn đụng tới dữ liệu gốc, hãy dùng hàm =UNIQUE(A2:A100) ở một cột trống để tạo danh sách không trùng riêng.

Nếu bạn dùng Excel, quy trình có đôi chút khác biệt, xem thêm cách xử lý dữ liệu trùng lặp trong Excel.

Vì sao công thức không tô màu?

• Có khoảng trắng thừa: “An” và “An ” (có dấu cách cuối) bị coi là khác nhau. Dùng Dữ liệu > Dọn dẹp dữ liệu > Cắt bớt khoảng trắng trước khi kiểm tra.
• Số lưu dạng văn bản: mã 0123 dạng văn bản và 123 dạng số không được tính là trùng. Thống nhất định dạng cột qua menu Định dạng > Số.
• Sai vùng áp dụng: công thức phải viết theo ô đầu tiên của vùng. Vùng bắt đầu từ A1 thì công thức dùng A1, không phải A2.
• Quy tắc khác đè lên: nếu ô có nhiều quy tắc, quy tắc đứng trên được ưu tiên. Kéo quy tắc tô trùng lên đầu danh sách.