Quản lý các bản ghi trùng lặp là rất quan trọng để phân tích dữ liệu chính xác trong Excel. Dữ liệu trùng lặp trong Excel có thể khiến các phép tính không chính xác, làm tăng kích thước tệp và gây nhầm lẫn. Excel cung cấp nhiều cách để loại bỏ trùng lặp.
Trong hướng dẫn này, chúng tôi sẽ chỉ ra 8 cách để loại bỏ các bản sao trong Excel mà không làm mất dữ liệu.
1. Xóa công cụ trùng lặp
Tính năng Xóa trùng lặp 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 với người dùng.
Các bước:
- Chọn phạm vi dữ liệu của bạn, bao gồm cả tiêu đề.
- Đi tới Dữ liệu tab>> từ Công cụ dữ liệu nhóm>> chọn Xóa nội dung trùng lặp .
- Chọn/bỏ chọn các cột để xác định những kết hợp nào tạo ra các bản sao.
- Nhấp vào OK .
Trường hợp:
- Nếu chúng tôi chọn tất cả các cột thì chỉ hàng 6 (OrderID 1002) sẽ bị xóa vì đây là bản sao chính xác của hàng 1.
- Excel sẽ hiển thị thông báo cho biết đã tìm thấy và xóa 1 giá trị trùng lặp.
- Nếu chúng tôi bỏ chọn OrderID và kiểm tra tất cả các cột khác.
- Các hàng 5, 6 và 8 sẽ bị xóa vì trùng lặp.
Ưu điểm:
- Sử dụng đơn giản 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 nào 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ữ phiên bản đầu tiên của mỗi bản ghi.
- Không thể khôi phục dữ liệu gốc trừ khi bạn tạo bản sao lưu.
Mẹo chuyên nghiệp: Luôn sao chép dữ liệu của bạn sang một trang tính khác trước khi sử dụng công cụ này nếu bạn cần bảo tồn tập dữ liệu gốc.
2. Bộ lọc nâng cao (Chỉ bản ghi duy nhất)
Bộ lọc nâng cao cung cấp nhiều quyền 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 sang một vị trí mới.
Các bước:
- Sắp xếp dữ liệu của bạn bằng tiêu đề.
- Đi tới Dữ liệu tab>> từ Sắp xếp &Lọc nhóm>> chọn Nâng cao .
- Chọn Sao chép sang vị trí khác .
- Chọn phạm vi dữ liệu của bạn trong Phạm vi danh sách: A1:G12 .
- Trong Sao chép vào:J1 .
- Kiểm tra Chỉ các bản ghi duy nhất .
- Nhấp vào OK .
Hàng 6 trùng lặp (OrderID 1002) sẽ bị loại khỏi kết quả. Dữ liệu sẽ được sao chép vào ô J1 trở xuống, không bao gồm hàng trùng lặp.
Ví dụ nâng cao: Để xác định các 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 Bộ lọc nâng cao với phạm vi tiêu chí này để tìm các giao dịch giống hệt nhau.
Ưu điểm:
- Bảo toàn dữ liệu gốc.
- Chỉ sao chép các bản ghi duy nhất sang một vị trí khác.
- Hoạt động với các tiêu chí phức tạp nếu cần.
Cân nhắc:
- Cần có không gian trống cho các kết quả được lọc.
- Cần làm mới thủ công khi dữ liệu nguồn thay đổi.
3. Power Query (Nhận &Chuyển đổi)
Power Query cung cấp giải pháp mạnh mẽ giúp bảo tồn dữ liệu gốc và có thể xử lý các tập dữ liệu lớn.
Các bước:
- Chọn dữ liệu của bạn.
- Đi tới Dữ liệu tab>> từ Nhận và chuyển đổi dữ liệu nhóm>> chọn Từ Bảng/Phạm vi .
- Chọn các cột để so sánh (bạn có thể chọn tất cả các cột hoặc chỉ những cột cụ thể).
- Truy cập Trang chủ tab>> Xóa hàng nhóm>> Xóa nội dung trùng lặp .
- Nhấp vào Đóng và tải để nhập kết quả vào một trang tính mới.
Ví dụ:
- Nếu chúng tôi xóa các bản sao dựa trên tất cả các cột thì chỉ hàng 6 (OrderID 1002) mới bị xóa.
- Nếu chúng tôi xóa các bản sao chỉ dựa trên Khách hàng, Sản phẩm, Số lượng, Giá và Ngày (không bao gồm ID đơn hàng), cả hai 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ể được làm mới.
- Xử lý tập dữ liệu lớn một cách 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 chuyển đổi nâng cao.
Cân nhắc:
- Cần có hiểu biết cơ bản về Power Query.
- Sử dụng nhiều tài nguyên hơn cho tập dữ liệu rất lớn.
- Cần thêm các bước cho logic loại bỏ trùng lặp phức tạp.
4. Hàm ĐỘC ĐÁO (Excel 365/2021)
Đối với các phiên bản Excel mới hơn, hàm UNIQUE sẽ tự động trích xuất các giá trị riêng biệt.
Xóa các 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 phạm vi tràn với tất cả các hàng duy nhất. Danh sách này sẽ 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 tôi, ngoại trừ hàng 6 trùng lặp.
Sản phẩm độc đáo theo danh mục: Để trích xuất các kết hợp độc đáo 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ề một phạm vi tràn chứa tất cả các danh sách sản phẩm duy nhất.
Danh sách khách hàng duy nhất: Để có được 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ề một phạm vi tràn chứa tất cả tên khách hàng duy nhất.
Ưu điểm:
- Tạo kết quả động cập nhật tự động 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 chức năng khác.
- Trả về các kết hợp duy nhất trên nhiều cột.
Cân nhắc:
- Chỉ có trong Excel 365 và Excel 2021.
- Tạo công thức mảng tràn (tự động mở rộng).
- Có thể yêu cầu lập kế hoạch cho không gian làm việc.
- Không thể xử lý logic loại bỏ trùng lặp rất phức tạp.
5. Đị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 nội dung trùng lặp.
Các bước:
- Chọn phạm vi dữ liệu của bạn.
- Truy cập Trang chủ tab>> từ Định dạng có điều kiện>> chọn Đánh dấu các quy tắc ô>> chọn Giá trị trùng lặp .
- Chọn kiểu định dạng:Đỏ nhạt với văn bản màu đỏ đậm .
- Nhấp vào OK .
- Excel đánh dấu các ô trùng lặp (theo cột).
- Đi tới Dữ liệu tab>> chọn Bộ lọc .
- Lọc theo màu ô để hiển thị các giá trị trùng lặp hoặc duy nhất.
- Đối với lựa chọn duy nhất Tự động để trùng lặp, chọn Màu .
Duy nhất:
Trùng lặp:
Nếu bạn muốn đánh dấu toàn bộ các hàng trùng lặp, hãy sử dụng quy tắc công thức như:
- Truy cập Trang chủ tab>> từ Định dạng có điều kiện>> chọn>> Quy tắc mới .
- Chọn Sử dụng công thức để xác định ô nào cần định dạng .
- 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 tô.
- Nhấp vào OK .
Ưu điểm:
- Xác định trực quan các nội dung trùng lặp trước khi xóa.
- Bảo toàn tất cả dữ liệu.
- Cho phép loại bỏ có chọn lọc.
- Hoạt động trong tất cả các phiên bản Excel.
- Giúp hiểu các mẫu trùng lặp.
Cân nhắc:
- Quy trình gồm 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 được áp dụng lại nếu dữ liệu thay đổi.
6. Phương pháp bảng tổng hợp
Bảng tổng hợp tổng hợp dữ liệu một cách tự nhiên, loại bỏ trùng lặp trong quy trình một cách hiệu quả.
Các bước:
- Chọn dữ liệu của bạn.
- Đi tới Chèn tab>> chọn PivotTable .
- Chọn Bảng tính hiện có và vị trí.
- Nhấp vào OK .
- Từ Danh sách trường PivotTable;
- Kéo các trường này vào Hàng :
- ID đơn hàng, khách hàng, sản phẩm, danh mục, ngày .
- Kéo các trường này vào Giá trị :
- Số lượng, giá cả .
Các hàng trùng lặp (3 và 6) đã được kết hợp, hiển thị tổng Số lượng (5+5=10) và Giá (129,5+129,5=259).
Nếu bạn muốn loại trừ OrderID để hiển thị các sản phẩm và khách hàng trùng lặp:
- Xóa ID đơn hàng từ Hàng khu vực đầu tiên.
- Bảng tổng hợp kết quả sẽ chỉ hiển thị các giao dịch trong đó mọi thứ đều giống nhau ngoại trừ OrderID.
Ưu điểm:
- Có thể tóm tắt dữ liệu trùng lặp thay vì chỉ xóa dữ liệu đó.
- Xử lý tập dữ liệu lớn một cách hiệu quả.
- Tự động tổng hợp các giá trị số.
Cân nhắc:
- Cần có hiểu biết cơ bản về Bảng tổng hợp.
- Có thể cần định dạng thêm sau khi trích xuất.
- Tổng hợp các giá trị số theo mặc định (có thể không mong muốn đối với một số trường).
7. Cột trợ giúp 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 trợ giúp (cột H) với tiêu đề “Kiểm tra trùng lặp”.
- Sử dụng công thức để xác định các hàng duy nhất.
- Lọc các giá trị thích hợp để chỉ xem các bản ghi duy nhất.
Xác định các bản sao hoàn chỉnh:
- 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 các bản sao trong đó toàn bộ hàng là trùng lặp.
Bỏ qua ID đơn hàng: Để xác định các bản sao 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 các hàng 2,3,5,6 và 10 là "Trùng lặp" 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ữ nguyên dữ liệu gốc.
- Có thể được tùy chỉnh theo các đ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 một cột bổ sung.
- Công thức có thể trở nên phức tạp đối với nhiều cột.
- Cần điều chỉnh nếu dữ liệu thay đổi.
- Phải được sao chép xuống để có 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, sự kết hợp của INDEX, MATCH và các hàm khác có thể trích xuất các giá trị duy nhất.
Sử dụng INDEX/MATCH (phiên bản Excel cũ hơn): Để trích xuất tên khách hàng duy nhất cho một 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 LỌC (Excel 365/2021): Để trích xuất các bản ghi duy nhất trong khi vẫn giữ tất cả cá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:
- Có khả năng tùy chỉnh cao.
- Hoạt động khi các phương pháp khác không thành công.
- 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 khi có thay đổi về dữ liệu nguồn.
Cân nhắc:
- Yêu cầu kiến thức Excel nâng cao.
- Việc triển khai và bảo trì phức tạp hơn.
- Có thể yêu cầu công thức mảng trong các phiên bản Excel cũ hơn.
- Có thể tốn nhiều tài nguyên cho các tập dữ liệu lớn.
Kết luận
Việc loại bỏ các bản sao trong Excel không hề nguy hiểm hay phức tạp. Cho dù bạn đang làm việc với các báo cáo nhỏ hay tập dữ liệu lớn, Excel đều cung cấp nhiều phương pháp an toàn và linh hoạt để xác định và loại bỏ các bản sao. Dựa vào loại dữ liệu của mình, bạn có thể sử dụng 8 cách sau để loại bỏ trùng lặp trong Excel mà không làm mất dữ liệu. Phương pháp tốt nhất tùy thuộc vào phiên bản Excel, kích thước dữ liệu của bạn và việc bạn thích công thức hay công cụ hơn. Nắm vững 8 kỹ thuật này đảm bảo bạn đã sẵn sàng cho mọi thử thách làm sạch trùng lặp. Luôn sao lưu dữ liệu của bạn trước khi xóa các bản sao, đặc biệt khi sử dụng các phương pháp sửa đổi trực tiếp tập dữ liệu gốc của bạn.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!