Quản lý bản ghi trùng lặp rất quan trọng cho phân tích dữ liệu chính xác trong Excel. Dữ liệu trùng lặp trong Excel có thể gây ra tính toán không chính xác, làm phồng kích thước tệp và tạo sự nhầm lẫn. Excel cung cấp nhiều cách để xóa trùng lặp.
Trong hướng dẫn này, chúng tôi sẽ chỉ 8 cách xóa trùng lặp trong Excel mà không làm mất dữ liệu.
1. Công Cụ Remove Duplicates (Xóa Trùng Lặp)
Tính năng Remove Duplicates tích hợp sẵn của Excel cung cấp giải pháp nhanh chóng với giao diện thân thiện.
Các Bước:
- Chọn phạm vi dữ liệu của bạn, bao gồm tiêu đề.
- Chuyển đến tab Data >> từ nhóm Data Tools >> chọn Remove Duplicates.
- Chọn/bỏ chọn cột để xác định tổ hợp nào tạo thành trùng lặp.
- Nhấp OK.
Trường Hợp:
- Nếu chúng ta chọn tất cả các cột, chỉ hàng 6 (OrderID 1002) sẽ bị xóa vì nó là bản sao chính xác của hàng 1.
- Excel sẽ hiển thị thông báo rằng 1 giá trị trùng lặp đã được tìm thấy và xóa.
- Nếu chúng ta bỏ chọn OrderID và chọn tất cả các cột khác.
- Hàng 5, 6 và 8 sẽ bị xóa như trùng lặp.
Ưu Điểm:
- Đơn giản sử dụng với giao diện trực quan.
- Hoạt động trực tiếp trên dữ liệu của bạn.
- Có thể chỉ định cột cần kiểm tra.
Cân Nhắc:
- Xóa vĩnh viễn các hàng trùng lặp.
- Chỉ giữ lại thể hiện đầu tiên của mỗi bản ghi.
- Dữ liệu gốc không thể khôi phục trừ khi bạn đã sao lưu.
Mẹo Chuyên Nghiệp: Luôn sao chép dữ liệu của bạn sang sheet khác trước khi sử dụng công cụ này nếu bạn cần bảo toàn tập dữ liệu gốc.
2. Advanced Filter (Bộ Lọc Nâng Cao - Chỉ Bản Ghi Duy Nhất)
Advanced Filter cung cấp nhiều kiểm soát hơn bằng cách cho phép bạn sao chép các bản ghi duy nhất đến vị trí mới.
Các Bước:
- Tổ chức dữ liệu của bạn với tiêu đề.
- Chuyển đến tab Data >> từ nhóm Sort & Filter >> chọn Advanced.
- Chọn Copy to another location (Sao chép đến vị trí khác).
- Chọn phạm vi dữ liệu trong List range: A1:G12.
- Trong Copy to: J1.
- Chọn Unique records only (Chỉ bản ghi duy nhất).
- Nhấp OK.
Hàng trùng lặp 6 (OrderID 1002) sẽ bị loại khỏi kết quả. Dữ liệu sẽ được sao chép vào ô J1 và bên dưới, loại trừ hàng trùng lặp.
Ví Dụ Nâng Cao: Để xác định giao dịch có thông tin sản phẩm trùng lặp bất kể OrderID, bạn có thể:
- Thiết lập phạm vi tiêu chí với tiêu đề (B1:G1).
- Áp dụng Advanced Filter với phạm vi tiêu chí này để tìm giao dịch giống hệt nhau.
Ưu Điểm:
- Bảo toàn dữ liệu gốc.
- Sao chép chỉ bản ghi duy nhất đến vị trí khác.
- Hoạt động với tiêu chí phức tạp nếu cần.
Cân Nhắc:
- Yêu cầu không gian có sẵn cho kết quả lọc.
- Cần làm mới thủ công khi dữ liệu nguồn thay đổi.
3. Power Query (Get & Transform)
Power Query cung cấp giải pháp mạnh mẽ bảo toàn dữ liệu gốc và có thể xử lý tập dữ liệu lớn.
Các Bước:
- Chọn dữ liệu của bạn.
- Chuyển đến tab Data >> từ nhóm Get & Transform Data >> chọn From Table/Range.
- Chọn cột để so sánh (bạn có thể chọn tất cả cột hoặc chỉ các cột cụ thể).
- Chuyển đến tab Home >> nhóm Remove Rows >> Remove Duplicates.
- Nhấp Close & Load để nhập kết quả vào sheet mới.
Ví Dụ:
- Nếu chúng ta xóa trùng lặp dựa trên tất cả các cột, chỉ hàng 6 (OrderID 1002) sẽ bị xóa.
- Nếu chúng ta xóa trùng lặp chỉ dựa trên Customer, Product, Quantity, Price và Date (loại trừ OrderID), cả hàng 4 và 8 sẽ được xác định là trùng lặp.
Đầu Ra:
Ưu Điểm:
- Tạo tập kết quả riêng biệt có thể làm mới.
- Xử lý tập dữ liệu lớn hiệu quả.
- Bảo toàn dữ liệu gốc.
- Có thể là một phần của quy trình lặp lại.
- Khả năng biến đổi nâng cao.
Cân Nhắc:
- Yêu cầu hiểu biết cơ bản về Power Query.
- Sử dụng nhiều tài nguyên cho tập dữ liệu rất lớn.
- Cần thêm bước cho logic khử trùng lặp phức tạp.
4. Hàm UNIQUE (Excel 365/2021)
Đối với các phiên bản Excel mới hơn, hàm UNIQUE trích xuất động các giá trị riêng biệt.
Xóa Hàng Trùng Lặp:
- Chọn ô I1 và chèn công thức sau.
Công Thức:
Bạn nhận được một spill range với tất cả các hàng duy nhất. Danh sách này cập nhật nếu bạn thay đổi dữ liệu nguồn. Điều này trả về tất cả các hàng duy nhất từ tập dữ liệu của chúng ta, loại trừ hàng trùng lặp hàng 6.
Sản Phẩm Duy Nhất Theo Danh Mục: Để trích xuất tổ hợp duy nhất của Sản phẩm và Danh mục.
- Chọn một ô và chèn công thức sau.
Công Thức:
Công thức này sẽ trả về spill range với tất cả danh sách sản phẩm duy nhất.
Danh Sách Khách Hàng Duy Nhất: Để lấy danh sách khách hàng duy nhất.
- Chọn một ô và chèn công thức sau.
Công Thức:
Công thức này sẽ trả về spill range với tất cả tên khách hàng duy nhất.
Ưu Điểm:
- Tạo kết quả động tự động cập nhật khi dữ liệu nguồn thay đổi.
- Không phá hủy dữ liệu nguồn.
- Có thể kết hợp với các hàm khác.
- Trả về tổ hợp duy nhất trên nhiều cột.
Cân Nhắc:
- Chỉ có sẵn trong Excel 365 và Excel 2021.
- Tạo công thức mảng tràn (mở rộng tự động).
- Có thể yêu cầu lập kế hoạch không gian làm việc.
- Không thể xử lý logic khử trùng lặp rất phức tạp.
5. Conditional Formatting + Filter (Định Dạng Có Điều Kiện + Bộ Lọc)
Cách tiếp cận trực quan này giúp làm nổi bật và sau đó lọc các bản trùng lặp.
Các Bước:
- Chọn phạm vi dữ liệu của bạn.
- Chuyển đến tab Home >> từ Conditional Formatting >> chọn Highlight Cells Rules >> chọn Duplicate Values.
- Chọn kiểu định dạng: Light Red Fill with Dark Red Text.
- Nhấp OK.
- Excel làm nổi bật các ô trùng lặp (theo cột).
- Chuyển đến tab Data >> chọn Filter.
- Lọc theo màu ô để hiển thị giá trị trùng lặp hoặc duy nhất.
- Để duy nhất chọn Automatic, cho trùng lặp chọn Color.
Duy Nhất:
Trùng Lặp:
Nếu bạn muốn làm nổi bật toàn bộ hàng trùng lặp, hãy dùng quy tắc công thức như:
- Chuyển đến tab Home >> từ Conditional Formatting >> chọn New Rule.
- Chọn Use a formula to determine which cells to format.
- Chèn công thức sau:
=COUNTIFS($A$2:$A$12, $A2, $B$2:$B$12, $B2, $C$2:$C$12, $C2, $D$2:$D$12, $D2, $E$2:$E$12, $E2, $F$2:$F$12, $F2, $G$2:$G$12, $G2)>1
- Chọn màu nền.
- Nhấp OK.
Ưu Điểm:
- Nhận diện trùng lặp trực quan trước khi xóa.
- Bảo toàn tất cả dữ liệu.
- Cho phép xóa chọn lọc.
- Hoạt động trong tất cả phiên bản Excel.
- Giúp hiểu mẫu trùng lặp.
Cân Nhắc:
- Quy trình nhiều bước.
- Yêu cầu lọc thủ công.
- Không tự động cập nhật.
- Bộ lọc cần áp dụng lại nếu dữ liệu thay đổi.
6. Phương Pháp Pivot Table
Pivot Tables tự nhiên tổng hợp dữ liệu, hiệu quả loại bỏ trùng lặp trong quá trình.
Các Bước:
- Chọn dữ liệu của bạn.
- Chuyển đến tab Insert >> chọn PivotTable.
- Chọn Existing Worksheet và vị trí.
- Nhấp OK.
- Từ PivotTable Fields List;
- Kéo các trường này vào Rows:
- Order ID, Customer, Product, Category, Date.
- Kéo các trường này vào Values:
- Quantity, Price.
Các hàng trùng lặp (3 và 6) đã được kết hợp, hiển thị tổng Quantity (5+5=10) và Price (129.5+129.5=259).
Nếu bạn muốn loại trừ OrderID để hiển thị sản phẩm và khách hàng trùng lặp:
- Xóa OrderID khỏi khu vực Rows trước.
- Bảng Pivot kết quả sẽ chỉ hiển thị giao dịch nơi mọi thứ tương tự trừ OrderID.
Ưu Điểm:
- Có thể tóm tắt dữ liệu trùng lặp thay vì chỉ xóa nó.
- Xử lý tập dữ liệu lớn hiệu quả.
- Tự động tổng hợp giá trị số.
Cân Nhắc:
- Yêu cầu hiểu biết cơ bản về Pivot Tables.
- Có thể cần định dạng thêm sau khi trích xuất.
- Tổng hợp giá trị số mặc định (có thể không mong muốn cho một số trường).
7. Cột Hỗ Trợ COUNTIF
Phương pháp này thêm một cột để xác định lần xuất hiện đầu tiên của mỗi bản ghi.
Các Bước:
- Thêm cột hỗ trợ (cột H) với tiêu đề "Duplicate Check".
- Sử dụng công thức để xác định hàng duy nhất.
- Lọc cho các giá trị phù hợp để chỉ thấy bản ghi duy nhất.
Xác Định Trùng Lặp Hoàn Toàn:
- Chọn ô H2, và nhập công thức này.
Công Thức:
=IF((COUNTIFS($A$2:$A$12, $A2, $B$2:$B$12, $B2, $C$2:$C$12, $C2, $D$2:$D$12, $D2, $E$2:$E$12, $E2, $F$2:$F$12, $F2, $G$2:$G$12, $G2))=1, "Unique","Duplicate")
Công thức này đánh dấu trùng lặp nơi toàn bộ hàng là trùng lặp.
Bỏ Qua OrderID: Để xác định trùng lặp dựa trên chi tiết giao dịch, bất kể OrderID:
=IF((COUNTIFS($B$2:$B$12, $B2, $C$2:$C$12, $C2, $D$2:$D$12, $D2, $E$2:$E$12, $E2, $F$2:$F$12, $F2, $G$2:$G$12, $G2))=1, "Unique","Duplicate")
Công thức này đánh dấu hàng 2,3,5,6 và 10 là "Duplicate" vì chúng trùng lặp chi tiết giao dịch.
Ưu Điểm:
- Hiển thị bản ghi nào là trùng lặp và giữ dữ liệu gốc.
- Có thể tùy chỉnh cho điều kiện phức tạp.
- Xác định hàng nào là trùng lặp.
Cân Nhắc:
- Yêu cầu cột bổ sung.
- Công thức có thể trở nên phức tạp cho nhiều cột.
- Cần điều chỉnh nếu dữ liệu thay đổi.
- Phải sao chép xuống cho dữ liệu mới.
8. Trích Xuất Dựa Trên Công Thức (INDEX/MATCH hoặc FILTER)
Đối với người dùng nâng cao, các tổ hợp INDEX, MATCH và các hàm khác có thể trích xuất giá trị duy nhất.
Sử Dụng INDEX/MATCH (Phiên Bản Excel Cũ): Để trích xuất tên khách hàng duy nhất cho vị trí riêng biệt.
- Chọn một ô và chèn công thức sau.
Công Thức:
=IFERROR(INDEX($B$2:$B$12,MATCH(0,COUNTIF($I$1:I1,$B$2:$B$12),0)),"")
Sử Dụng FILTER (Excel 365/2021): Để trích xuất bản ghi duy nhất đồng thời giữ tất cả cột.
- Chọn một ô và chèn công thức sau.
Công Thức:
=FILTER(A2:G12, MATCH(A2:A12&B2:B12&C2:C12&D2:D12&E2:E12&F2:F12&G2:G12, A2:A12&B2:B12&C2:C12&D2:D12&E2:E12&F2:F12&G2:G12, 0)=ROW(A2:A12)-ROW(A2)+1)
Ưu Điểm:
- Tùy chỉnh cao.
- Hoạt động khi các phương pháp khác thất bại.
- Có thể kết hợp logic phức tạp.
- Không phá hủy dữ liệu nguồn.
- Cập nhật động với thay đổi dữ liệu nguồn.
Cân Nhắc:
- Yêu cầu kiến thức Excel nâng cao.
- Phức tạp hơn để triển khai và bảo trì.
- Có thể yêu cầu công thức mảng trong Excel cũ.
- Có thể tốn nhiều tài nguyên cho tập dữ liệu lớn.
Kết Luận
Xóa trùng lặp trong Excel không phải rủi ro hay phức tạp. Cho dù bạn làm việc với báo cáo nhỏ hay tập dữ liệu lớn, Excel cung cấp nhiều phương pháp an toàn và linh hoạt để xác định và loại bỏ trùng lặp. Dựa trên loại dữ liệu của bạn, bạn có thể sử dụng 8 cách này để xóa trùng lặp trong Excel mà không làm mất dữ liệu. Phương pháp tốt nhất phụ thuộc vào phiên bản Excel, kích thước dữ liệu và xem bạn ưu tiên công thức hay công cụ. Làm chủ 8 kỹ thuật này đảm bảo bạn sẵn sàng cho bất kỳ thách thức khử trùng lặp nào. Luôn sao lưu dữ liệu trước khi xóa trùng lặp, đặc biệt khi dùng phương pháp sửa đổi trực tiếp tập dữ liệu gốc.
Nhận Bài Tập Excel Nâng Cao Miễn Phí Kèm Giải Pháp!