Cách lọc và xoá dữ liệu trùng lặp trong Google Sheets: tô màu, UNIQUE, xoá bản sao, chặn nhập trùng

Danh sách khách gom từ nhiều nguồn (Zalo, Facebook, sổ tay, nhân viên mỗi người một file) gần như luôn có người bị nhập hai lần. Hậu quả rất cụ thể: gọi một khách hai lần, đếm sai số khách mới trong tháng, gửi hai tin nhắn khuyến mãi cho cùng một người, hay tệ hơn là công nợ của một khách bị chia ra hai dòng.

Các ví dụ trong bài dùng bảng dưới đây: cột A là Họ tên, cột B là SĐT đúng như nhân viên đã gõ, dữ liệu ở dòng 2–11. Nhìn bằng mắt bạn sẽ thấy 4 người bị nhập 2 lần, tức chỉ có 6 khách thật.

Tô màu ô trùng

Định dạng có điều kiện + COUNTIF

Danh sách không trùng

=UNIQUE(…)

Xoá hẳn dòng trùng

Dữ liệu › Dọn dẹp dữ liệu

Chặn nhập trùng

Xác thực dữ liệu, công thức tuỳ chỉnh

COUNTIF đếm xem giá trị của ô hiện tại xuất hiện bao nhiêu lần trong cả cột. Lớn hơn 1 nghĩa là có trùng. Đặt công thức đó vào định dạng có điều kiện thì Google tự tô màu, dữ liệu không bị thay đổi gì.

1. Chọn vùng B2:B1000 (cột SĐT).
2. Vào Định dạng › Định dạng có điều kiện.
3. Ở mục "Định dạng ô nếu", chọn Công thức tuỳ chỉnh là và dán công thức bên dưới.
4. Chọn màu nền (ví dụ cam nhạt) rồi bấm Xong.

=COUNTIF($B$2:$B$1000; B2)>1

Muốn tô cả dòng thay vì một ô, chọn A2:B1000 và đổi thành =COUNTIF($B$2:$B$1000; $B2)>1 (thêm $ trước chữ B để mọi ô trong dòng đều nhìn vào cột B).

Thử lại: với bảng ví dụ, B2 = 0909123456 xuất hiện 2 lần (dòng 2 và 5) nên được tô. B3 = 0912345678 chỉ xuất hiện 1 lần vì dòng 7 là "0912 345 678" có dấu cách — với Google đó là một chuỗi khác. Kết quả: chỉ 2 dòng được tô (2 và 5), trong khi thực tế có 8 dòng thuộc người bị nhập trùng. Phần tiếp theo sửa đúng chỗ này.

Chỉ muốn đánh dấu lần xuất hiện thứ hai trở đi (những dòng nên xoá), giữ nguyên dòng đầu: cho vùng đếm mở rộng dần từ dòng 2 tới dòng hiện tại.

=COUNTIF($B$2:B2; B2)>1

Cùng công thức này đặt vào một cột phụ (ví dụ D2 rồi kéo xuống) sẽ ra TRUE/FALSE. Bạn bật bộ lọc và lọc TRUE để xem riêng các dòng thừa. Nếu chỉ cần đếm số lần xuất hiện của mỗi khách, xem thêm COUNTIF và COUNTIFS.

Google so sánh từng ký tự. Muốn bắt được "0912 345 678" và "0912345678" là một người, bạn tạo một cột khoá chỉ giữ lại phần cốt lõi của số, rồi tìm trùng trên cột khoá thay vì cột gốc. Với số di động Việt Nam, phần cốt lõi là 9 chữ số cuối: bỏ hết ký tự không phải số, rồi lấy 9 số bên phải để 0912… và +84 912… ra cùng một kết quả.

=RIGHT(REGEXREPLACE(B2&""; "\D"; ""); 9)

Thử lại: B7 = "0912 345 678" → bỏ dấu cách còn 0912345678 → 9 số cuối là 912345678, giống hệt C3. B11 = "+84 938 111 222" → còn 84938111222 → 9 số cuối là 938111222, giống C6. B9 = "0987.654.321" → 987654321, giống C4.

Giờ đổi định dạng có điều kiện sang cột khoá: =COUNTIF($C$2:$C$11; C2)>1 (hoặc $C$2:$C$1000 cho cả cột). Kết quả: 8 dòng được tô (2, 3, 4, 5, 6, 7, 9, 11), chỉ dòng 8 và 10 là khách xuất hiện một lần. Đúng với những gì bạn thấy bằng mắt.

Cùng ý tưởng cho email: khoá là =LOWER(TRIM(D2)) — bỏ khoảng trắng hai đầu và đưa về chữ thường. Cho họ tên: =LOWER(TRIM(A2)), nhưng đừng chỉ dựa vào họ tên vì hai người khác nhau trùng tên là chuyện bình thường.

UNIQUE trả về một danh sách mới, mỗi giá trị một lần, theo thứ tự xuất hiện đầu tiên. Dữ liệu gốc giữ nguyên, nên đây là cách an toàn để lấy danh sách gửi tin, đếm số khách thật hay làm nguồn cho danh sách thả xuống.

=UNIQUE(A2:B11)

Hai lưu ý. Một: nếu vùng chọn có ô trống, UNIQUE trả thêm một dòng trống và COUNTA đếm cả nó; dùng =UNIQUE(FILTER(B2:B; B2:B<>"")) khi chọn cả cột. Hai: UNIQUE phân biệt chữ hoa và chữ thường, nên "Lan.Nguyen@gmail.com" và "lan.nguyen@gmail.com" ra hai dòng — chuẩn hoá bằng LOWER trước.

Muốn lấy danh sách không trùng mà vẫn kèm các cột khác (họ tên, ngày tạo), dùng hàm QUERY gom nhóm theo cột khoá, hoặc tra lại từng khoá bằng XLOOKUP — XLOOKUP lấy dòng khớp đầu tiên, đúng là bản ghi cũ nhất.

Khi đã chắc chắn muốn xoá, Google Sheets có công cụ xoá bản sao trong menu Dữ liệu › Dọn dẹp dữ liệu (mục Xoá bản sao; giao diện tiếng Anh là Remove duplicates). Công cụ giữ lại dòng xuất hiện đầu tiên và xoá các dòng sau.

1. Tạo bản sao trang tính trước (chuột phải vào tên trang › Sao chép) để còn đường lui.
2. Chọn cả bảng, gồm mọi cột của dòng (ở ví dụ là A1:C11), không chỉ cột SĐT — chọn một cột thì công cụ chỉ xử lý ô trong cột đó, các cột khác có thể bị lệch dòng.
3. Mở công cụ xoá bản sao, đánh dấu "Dữ liệu có hàng tiêu đề" nếu dòng 1 là tiêu đề.
4. Ở phần chọn cột để phân tích, chỉ tích cột khoá (cột C). Tích cả cột B thì các dòng gõ khác kiểu sẽ không bị coi là trùng.
5. Bấm xoá. Google báo đã xoá bao nhiêu dòng trùng và còn bao nhiêu dòng.

Thử lại: với cột khoá C, dòng 5 (trùng dòng 2), dòng 7 (trùng dòng 3), dòng 9 (trùng dòng 4) và dòng 11 (trùng dòng 6) bị xoá. Còn lại 6 dòng: Lan, Minh, Hà, Bảo, Mai Anh, Long. Nếu thay vào đó chỉ tích cột B, Google chỉ xoá 1 dòng (dòng 5).

Nếu sau khi xoá các ô còn khoảng trắng thừa, mục Cắt bớt khoảng trắng nằm ngay cạnh trong cùng menu Dọn dẹp dữ liệu.

Có lúc một giá trị lặp lại là bình thường: cùng một khách mua nhiều lần. Cái cần bắt là đơn bị nhập hai lần — trùng cả ngày, SĐT và số tiền. Ví dụ sổ đơn: A là Ngày, B là SĐT, C là Số tiền.

=COUNTIFS($A$2:$A$1000; A2; $B$2:$B$1000; B2; $C$2:$C$1000; C2)>1

Thử lại: dòng 2 và 4 cùng ngày 15/10, cùng số, cùng 350.000 nên COUNTIFS ra 2 → TRUE. Dòng 5 cùng số, cùng tiền nhưng khác ngày (khách mua lại hôm sau) → chỉ khớp chính nó → FALSE. Muốn chỉ đánh dấu dòng thừa (dòng 4), đổi vùng thành $A$2:A2, $B$2:B2, $C$2:C2 như ở phần COUNTIF.

Cách khác là ghép một cột khoá: =A2&"|"&B2&"|"&C2 rồi dùng COUNTIF hoặc công cụ xoá bản sao trên cột đó. Dấu | ngăn trường hợp ghép nhầm như "1"&"23" và "12"&"3" ra cùng chuỗi. Ngày phải là ngày thật, không phải chữ — nếu nghi ngờ, đọc cách sửa lỗi ngày tháng.

Dọn trùng một lần chưa đủ nếu ngày mai nhân viên lại nhập trùng. Xác thực dữ liệu kiểm tra ngay lúc gõ.

1. Chọn B2:B1000.
2. Vào Dữ liệu › Xác thực dữ liệu › Thêm quy tắc.
3. Ở phần Tiêu chí, chọn Công thức tuỳ chỉnh là và dán công thức bên dưới.
4. Trong tuỳ chọn nâng cao, chọn từ chối dữ liệu nhập (chặn hẳn) hoặc hiển thị cảnh báo (cho nhập nhưng đánh dấu ô).

=COUNTIF($B$2:$B$1000; B2)=1

Thử lại: đã có 0909123456 ở B2, nhân viên gõ 0909123456 vào B12 → COUNTIF ra 2, khác 1 → bị từ chối. Gõ 0911222333 → ra 1 → được nhận.

File nhiều người cùng nhập thì nên khoá các cột công thức như cột khoá, để không ai vô tình xoá công thức rồi gõ đè.

• Dấu cách thừa ở đầu hoặc cuối. Thường gặp khi dán từ Zalo hay web. Kiểm tra bằng =LEN(D2): "lan.nguyen@gmail.com" có 20 ký tự, ra 21 là có ký tự thừa. Sửa bằng =TRIM(D2) hoặc Cắt bớt khoảng trắng.
• Chữ hoa, chữ thường. COUNTIF không phân biệt hoa thường nên vẫn đếm đúng, nhưng UNIQUE thì có. Chuẩn hoá bằng =LOWER(TRIM(D2)) cho email; với họ tên có thể dùng PROPER để viết hoa chữ đầu.
• Dấu cách không ngắt CHAR(160). Dữ liệu dán từ trang web có loại dấu cách này, TRIM không xoá được. Dùng =TRIM(SUBSTITUTE(D2; CHAR(160); " ")).
• Số và chữ lẫn lộn. Cùng một mã nhưng ô này là số, ô kia là chữ (thường do mất số 0 đầu, hoặc dán từ phần mềm khác). Cách nhận ra và sửa giống hệt lỗi SUM bằng 0. Với số điện thoại, cột khoá ở trên đã xử lý luôn chuyện này.

Một bẫy nữa: COUNTIF coi dấu * và ? trong giá trị là ký tự đại diện. Giá trị chứa các dấu này (mã hàng kiểu "A*01") có thể bị đếm sai; trường hợp đó dùng =SUMPRODUCT(--($B$2:$B$1000=B2))>1 để so sánh chính xác từng ký tự.

Các công thức trên đủ cho một danh sách vài trăm dòng do một người giữ. Khi đội bán hàng 5–10 người cùng thêm khách mỗi ngày từ form, Facebook, Zalo, việc trùng xảy ra liên tục và việc gộp bằng tay (giữ dòng nào, chuyển lịch sử chăm sóc sang đâu) mới là phần tốn thời gian. Cách tổ chức một danh sách như vậy được nói kỹ trong bài quản lý khách hàng tiềm năng bằng Google Sheets.

• Cách dùng hàm SUMIFS, COUNTIFSĐếm và cộng theo nhiều điều kiện
• Cách dùng hàm XLOOKUP trong Google SheetsTra thông tin khách theo cột khoá
• Quản lý khách hàng tiềm năng bằng Google SheetsTổ chức danh sách khách cho đội bán hàng
• Sửa lỗi SUM bằng 0 trong Google SheetsNhận ra số đang ở dạng chữ