Danh sách khách hàng gộp từ nhiều nguồn thường có một người xuất hiện vài lần, nên số đếm và số tiền tổng hợp có thể bị sai. Bài viết này hướng dẫn 4 cách lọc dữ liệu trùng trong Excel, từ cách chỉ tô màu để xem đến cách xóa hẳn dòng lặp.

Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh, nên phiên bản khác có thể khác đôi chút về vị trí nút. Ở các ảnh có thao tác, con trỏ chuột chỉ vào chỗ bạn cần bấm hoặc nằm ngay cạnh ô bạn cần xem. Nếu máy của bạn dùng dấu chấm phẩy trong công thức, bạn thay dấu phẩy phân cách bằng dấu chấm phẩy.

Tải file thực hành: [Dữ liệu mẫu] - Danh sách khách hàng thực hành lọc dữ liệu trùng (file Excel trên OneDrive)

Bạn dùng tài liệu này mà bị vướng ở đâu? Bạn có thể đăng câu hỏi trong nhóm Vườn ươm nhà Nghề để cùng trao đổi và được giải đáp. Vào nhóm hỏi đáp

File thực hành có 16 dòng khách hàng trong vùng A1:C17, với các cột Họ tên, Số điện thoại và Khu vực. Cột Số điện thoại lưu ở dạng chữ để giữ số 0 ở đầu, nên Excel của bạn có thể hiện tam giác xanh ở góc ô. Bạn có thể bỏ qua dấu này và không chọn Convert to Number. Dữ liệu trong file có 3 số điện thoại bị lặp như sau:

Số điện thoại

Khách hàng

Các dòng chứa số này

Điểm khác nhau

0900000101

Nguyễn Văn An

2 và 9

Hai dòng giống hệt nhau

0900000102

Trần Thị Bình

3, 6 và 15

Ba dòng giống hệt nhau

0900000103

Lê Văn Cường

4 và 12

Khu vực của hai dòng khác nhau

Bạn có thể chọn cách phù hợp theo bảng sau:

Cách

Công cụ

Kết quả

Bảng gốc có đổi không?

Cách 1

Duplicate Values

Tô màu các ô bị trùng

Bảng gốc giữ nguyên dữ liệu

Cách 2

Hàm COUNTIF

Đếm số lần xuất hiện

Bảng gốc giữ nguyên dữ liệu

Cách 3

Hàm UNIQUE

Tạo danh sách không trùng ở chỗ khác

Bảng gốc giữ nguyên dữ liệu

Cách 4

Remove Duplicates

Xóa dòng bị lặp

Bảng gốc bị xóa bớt dòng

Cách 1: Bạn tô màu dữ liệu trùng bằng Duplicate Values

Cách này chỉ đánh dấu các ô bị trùng, nên bạn có thể xem trước rồi mới quyết định xử lý.

Đầu tiên, bạn quét chọn vùng B2:B17. Sau đó, bạn mở tab Home và chọn Conditional Formatting, Highlight Cells Rules, Duplicate Values.

Lệnh Duplicate Values trong menu Conditional Formatting
Con trỏ chuột chỉ vào lệnh Duplicate Values trong menu Highlight Cells Rules.

Hộp thoại Duplicate Values hiện ra, và ô bên trái đang chọn sẵn Duplicate. Bạn giữ nguyên màu mặc định rồi bấm OK.

Hộp thoại Duplicate Values với màu mặc định
Excel xem trước màu tô trên vùng B2:B17 trong lúc hộp thoại đang mở.

Excel tô màu 7 ô thuộc 3 số điện thoại bị lặp.

Các số điện thoại bị trùng được tô màu đỏ nhạt
Bảy ô ở cột Số điện thoại được tô màu đỏ nhạt.

Cách tô màu không phân biệt lần đầu với lần lặp, nên cả 7 ô đều có màu. Nếu cần biết dòng nào là lần lặp, bạn dùng cách 2. Để xóa màu trước khi thử cách tiếp theo, bạn chọn Conditional Formatting, Clear Rules, Clear Rules from Entire Sheet.

Cách 2: Bạn đếm số lần xuất hiện bằng hàm COUNTIF

Cách này cho bạn biết rõ mỗi giá trị xuất hiện mấy lần. Bạn dùng 2 cột phụ ở bên phải bảng, với tiêu đề là Số lần xuất hiện và Lần thứ mấy.

Cột 1: Bạn đếm tổng số lần xuất hiện. Bạn gõ công thức sau vào ô E2 rồi kéo xuống ô E17. Để kéo công thức, bạn đưa chuột vào ô vuông nhỏ ở góc dưới bên phải của ô cho đến khi con trỏ thành dấu cộng màu đen, rồi giữ chuột kéo xuống.

=COUNTIF($B$2:$B$17,B2)

Công thức COUNTIF đếm tổng số lần xuất hiện ở ô E2
Ô E2 trả về 2 vì số điện thoại ở ô B2 xuất hiện 2 lần trong danh sách.

Vùng $B$2:$B$17 được khóa bằng dấu $, nên công thức luôn đếm trên cả danh sách khi bạn kéo xuống.

Cột 2: Bạn đánh dấu lần xuất hiện thứ mấy. Bạn gõ công thức sau vào ô F2 rồi kéo xuống ô F17:

=COUNTIF($B$2:B2,B2)

Công thức COUNTIF đếm lần xuất hiện thứ mấy ở ô F3
Công thức ở ô F3 đếm từ đầu danh sách đến dòng 3.

Công thức này chỉ khóa điểm đầu của vùng, nên vùng đếm dài dần khi bạn kéo xuống. Những dòng có kết quả từ 2 trở lên là các lần lặp. File thực hành có 4 dòng như vậy, gồm các dòng 6, 9, 12 và 15.

Nếu bạn muốn hiểu thêm về cách đếm, bài đếm tần suất xuất hiện trong Excel có thêm nhiều ví dụ.

Cách 3: Bạn tạo danh sách không trùng bằng hàm UNIQUE

Hàm UNIQUE tạo một danh sách mới và không đụng đến bảng gốc. Hàm này có trong Excel 365 và Excel 2021 trở lên.

Trước khi làm cách này, bạn xóa 2 cột phụ E và F của cách 2, vì vùng kết quả phải đang trống. Bạn có thể gõ tiêu đề Bảng không trùng vào ô E1 cho dễ nhìn. Sau đó, bạn gõ công thức sau vào ô E2, và kết quả tự tràn ra các ô bên dưới và bên phải:

=UNIQUE(A2:C17)

Hàm UNIQUE trả về bảng 13 dòng không trùng
Hàm UNIQUE trả về 13 dòng không trùng trong vùng E2:G14.

Kết quả có 13 dòng vì hàm so sánh cả 3 cột. Hai dòng của Lê Văn Cường khác khu vực, nên hàm vẫn giữ cả hai. Nếu chỉ cần danh sách số điện thoại không trùng, bạn dùng =UNIQUE(B2:B17), và kết quả có 12 dòng.

Kết quả của hàm UNIQUE tự cập nhật khi bảng gốc thay đổi. Để công thức chạy được, các ô mà kết quả tràn vào phải đang trống.

Cách 4: Bạn xóa dòng trùng bằng Remove Duplicates

Cách này xóa thẳng các dòng bị lặp trong bảng, nên bạn nên sao lưu file trước khi làm. Bạn cũng cần xóa cột E, F, G của cách 3 để bảng gốc đứng riêng.

Bạn bấm một ô trong bảng, mở tab Data và bấm nút Remove Duplicates trong nhóm Data Tools.

Nút Remove Duplicates trên tab Data
Con trỏ chuột chỉ vào nút Remove Duplicates trong nhóm Data Tools.

Hộp thoại Remove Duplicates hiện ra với cả 3 cột đều được tích. Khi cả 3 cột được tích, Excel chỉ coi hai dòng là trùng nếu cả 3 cột đều giống nhau. Bạn bấm OK.

Hộp thoại Remove Duplicates tích đủ 3 cột
Excel chọn sẵn vùng dữ liệu và tích cả 3 cột trong hộp thoại.

Excel báo đã xóa 3 dòng và còn lại 13 dòng, và bạn bấm OK để đóng thông báo. Excel giữ lại dòng xuất hiện đầu tiên và xóa các dòng lặp phía sau.

Thông báo đã xóa 3 dòng trùng và còn lại 13 dòng
Thông báo cho biết Excel đã xóa 3 dòng trùng và còn lại 13 dòng.

Bảng vẫn còn 2 dòng của Lê Văn Cường vì hai dòng này khác khu vực. Nếu mỗi số điện thoại chỉ được xuất hiện một lần, bạn mở lại Remove Duplicates, bấm Unselect All và chỉ tích cột Số điện thoại.

Hộp thoại Remove Duplicates chỉ tích cột Số điện thoại
Excel chỉ so sánh cột Số điện thoại khi bạn chỉ tích cột này.

Sau khi bạn bấm OK, Excel xóa thêm 1 dòng và bảng còn 12 khách hàng. Excel giữ dòng Lê Văn Cường ở Miền Trung vì dòng này đứng trước.

Bảng còn 12 khách hàng sau khi xóa dòng trùng
Bảng cuối cùng còn 12 dòng, và mỗi số điện thoại chỉ xuất hiện một lần.

Người mới thường hỏi gì về lọc dữ liệu trùng trong Excel?

Vì sao hai ô nhìn giống nhau mà Excel không coi là trùng?

Một ô có thể chứa khoảng trắng thừa mà mắt thường không thấy. Ví dụ, Remove Duplicates coi An và An (có khoảng trắng ở cuối) là hai giá trị khác nhau. Bạn có thể tạo một cột phụ bằng hàm TRIM để bỏ khoảng trắng thừa rồi mới lọc trùng.

Excel có phân biệt chữ hoa và chữ thường khi lọc trùng không?

Remove Duplicates và COUNTIF đều không phân biệt chữ hoa và chữ thường. Ví dụ, An và an được tính là cùng một giá trị.

Tôi xóa nhầm thì làm sao?

Bạn nhấn Ctrl + Z ngay sau khi xóa để lấy lại dữ liệu. Nếu đã lưu và đóng file, bạn cần khôi phục từ bản sao lưu hoặc từ lịch sử phiên bản của OneDrive, nên bạn nên giữ một bản sao trước khi dùng Remove Duplicates.

Bạn muốn dùng Excel thành thạo cho công việc văn phòng?

Lọc trùng là bước nên làm trước khi tính tổng, vì một dòng bị lặp có thể làm sai cả báo cáo.

Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.

Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.