Kiểm Tra Trùng Dữ Liệu Trên Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Hướng Dẫn Chi Tiết Cách Hợp Nhất Ô Trong Excel: Từ Cơ Bản Đến Nâng Cao

Mở Đầu

Trong môi trường làm việc hiện đại, Excel đã trở thành một công cụ không thể thiếu đối với hầu hết các ngành nghề. Từ quản lý tài chính, nhân sự, đến phân tích dữ liệu, Excel giúp chúng ta thực hiện các tác vụ phức tạp một cách nhanh chóng và hiệu quả. Tuy nhiên, một trong những vấn đề phổ biến mà người dùng thường gặp phải là dữ liệu trùng lặp. Việc có dữ liệu trùng có thể gây ra những hậu quả không mong muốn như:

• Sai lệch trong báo cáo: Khi tính tổng, trung bình hay các chỉ số khác, dữ liệu trùng sẽ làm sai lệch kết quả.
• Mất thời gian khi xử lý: Người dùng phải mất công kiểm tra, lọc và loại bỏ các bản ghi trùng lặp.
• Tăng dung lượng file: Các bản ghi không cần thiết làm tăng kích thước file, gây khó khăn trong việc chia sẻ và lưu trữ.

Để giải quyết vấn đề này, Excel cung cấp một loạt các công cụ và hàm mạnh mẽ giúp kiểm tra trùng một cách nhanh chóng và chính xác. Bài viết dưới đây sẽ hướng dẫn bạn chi tiết cách thực hiện kiểm tra trùng trên Excel, từ các phương pháp đơn giản cho người mới bắt đầu đến những kỹ thuật nâng cao dành cho người dùng chuyên nghiệp.

Có thể bạn quan tâm: Hàm Thay Thế (substitute) Trong Excel: Hướng Dẫn Toàn Diện, Ứng Dụng Thực Tế Và Mẹo Tối Ưu

1. Hiểu Rõ Vấn Đề Trùng Lặp Trong Excel

1.1. Trùng Lặp Là Gì?

Trùng lặp (duplicate) trong Excel đề cập đến các hàng (records) hoặc cột (fields) có cùng một hoặc nhiều giá trị giống nhau. Tùy vào mục đích sử dụng, bạn có thể quan tâm đến:

• Trùng lặp toàn bộ hàng: Khi mọi cột trong một hàng đều giống nhau.
• Trùng lặp một hoặc một số cột: Khi chỉ một hoặc một vài cột có giá trị giống nhau (ví dụ: cùng một mã khách hàng, email, số điện thoại…).

1.2. Khi Nào Nên Loại Bỏ Dữ Liệu Trùng?

• Khi tổng hợp báo cáo: Để tránh tính gấp đôi các giá trị.
• Khi xây dựng cơ sở dữ liệu: Đảm bảo tính duy nhất của các khóa chính (primary key).
• Khi phân tích dữ liệu: Đảm bảo các mẫu thống kê, mô hình dự báo không bị lệch.

Có thể bạn quan tâm: Cách Hiện Thanh Công Cụ (toolbar) Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

2. Các Công Cụ Kiểm Tra Trùng Trên Excel

2.1. Conditional Formatting (Định Dạng Có Điều Kiện)

2.1.1. Bước 1: Chọn vùng dữ liệu cần kiểm tra

• Ví dụ: Chọn cột A (A2:A1000) chứa danh sách email.

2.1.2. Bước 2: Vào Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values…

• Hộp thoại sẽ xuất hiện, cho phép bạn chọn màu hiển thị cho các giá trị trùng.

2.1.3. Bước 3: Xác nhận và xem kết quả

• Các ô trùng lặp sẽ được tô màu, giúp bạn nhanh chóng nhận diện.

2.1.4. Ưu và Nhược điểm

• Ưu điểm: Nhanh, không cần công thức, trực quan.
• Nhược điểm: Chỉ hiển thị màu, không thể lọc hoặc xóa trực tiếp.

2.2. Remove Duplicates (Xóa Bản Sao)

2.2.1. Quy trình thực hiện

1. Chọn toàn bộ bảng dữ liệu (hoặc chỉ một phần).
2. Vào Data → Remove Duplicates.
3. Trong hộp thoại, chọn các cột cần kiểm tra trùng (có thể chọn nhiều cột).
4. Nhấn OK, Excel sẽ thông báo số bản ghi đã bị xóa và số bản ghi còn lại.

2.2.2. Lưu ý quan trọng

• Sao lưu file trước khi thực hiện để tránh mất dữ liệu quan trọng.
• Nếu muốn giữ lại một bản sao của các bản ghi trùng, hãy sao chép dữ liệu sang một sheet khác trước khi xóa.

2.3. Advanced Filter (Bộ Lọc Nâng Cao)

Advanced Filter cho phép bạn lọc ra các bản ghi duy nhất (Unique records) hoặc tạo một danh sách trùng.

2.3.1. Cách lọc duy nhất

1. Đặt con trỏ vào bất kỳ ô nào trong bảng.
2. Vào Data → Advanced.
3. Chọn Copy to another location, đánh dấu Unique records only.
4. Xác định vùng Copy to (ví dụ: Sheet2!A1) và nhấn OK.

2.3.2. Lọc ra các bản ghi trùng

• Không có tùy chọn trực tiếp, nhưng bạn có thể sử dụng công thức để tạo cột trợ giúp (xem phần 3.2).

2.4. Các Hàm Hỗ Trợ Kiểm Tra Trùng

2.4.1. Hàm COUNTIF / COUNTIFS

• COUNTIF(range, criteria): Đếm số lần xuất hiện của một giá trị trong một phạm vi.
• COUNTIFS(range1, criteria1, range2, criteria2, …): Đếm dựa trên nhiều điều kiện.

Ví dụ: Kiểm tra xem email trong ô A2 có trùng hay không:

=IF(COUNTIF($A$2:$A$1000, A2)>1, "Trùng", "Độc nhất")

2.4.2. Hàm MATCH và ISNUMBER

• MATCH(value, range, 0) trả về vị trí của giá trị trong phạm vi, nếu không tìm thấy trả về #N/A.
• Kết hợp với ISNUMBER để kiểm tra tồn tại.

=IF(ISNUMBER(MATCH(A2, $A$1:A1, 0)), "Trùng", "Độc nhất")

• Công thức này so sánh giá trị hiện tại với các giá trị đã xuất hiện ở trên, rất hữu ích khi bạn muốn đánh dấu lần xuất hiện đầu tiên là duy nhất, các lần sau là trùng.

2.4.3. Hàm EXACT

• So sánh độ chính xác (case-sensitive). Dùng khi bạn cần phân biệt chữ hoa/chữ thường.

=IF(COUNTIF($A$2:$A$1000, EXACT(A2, $A$2:$A$1000))>1, "Trùng", "Độc nhất")

2.4.4. Hàm FILTER (Excel 365/2026)

• Trích xuất các bản ghi thỏa mãn điều kiện.

=FILTER(A2:C1000, COUNTIF(A2:A1000, A2:A1000)>1, "Không có trùng")

• Kết quả là một mảng chứa tất cả các dòng có ít nhất một giá trị trùng trong cột A.

3. Thực Hành Kiểm Tra Trùng Dựa Trên Các Kịch Bản Thực Tế

3.1. Kiểm Tra Trùng Đơn Giản Trong Một Cột

Kịch bản: Bạn có danh sách mã khách hàng (cột B) và muốn biết có mã nào bị lặp không.

Bước thực hiện:
1. Sử dụng Conditional Formatting để nhanh chóng nhìn thấy các ô trùng.
2. Thêm cột trợ giúp (cột C) với công thức:
excel =IF(COUNTIF($B$2:$B$5000, B2)>1, "Trùng", "Độc nhất")
3. Lọc cột C để chỉ hiển thị “Trùng”, sau đó xóa hoặc đánh dấu các bản ghi không mong muốn.

3.2. Kiểm Tra Trùng Dựa Trên Nhiều Cột (Composite Key)

Kịch bản: Bạn có bảng đơn hàng với các cột: Mã Đơn, Mã Khách Hàng, Ngày Đặt. Bạn muốn xác định các đơn hàng bị nhập lặp (cùng Mã Đơn, cùng Khách Hàng, cùng Ngày).

Cách 1 – Tạo Cột Kết Hợp (Helper Column)
1. Thêm cột mới (ví dụ: cột D) với công thức:
excel =B2 & "|" & C2 & "|" & D2 // giả sử B= Mã Đơn, C= Mã KH, D= Ngày
2. Sau đó dùng COUNTIF:
excel =IF(COUNTIF($D$2:$D$10000, D2)>1, "Trùng", "Độc nhất")
3. Lọc hoặc xóa các dòng “Trùng”.

Cách 2 – Sử dụng COUNTIFS
Nếu không muốn tạo cột phụ:

=IF(COUNTIFS($B$2:$B$10000, B2, $C$2:$C$10000, C2, $D$2:$D$10000, D2)>1, "Trùng", "Độc nhất")

• Công thức này kiểm tra đồng thời ba cột, trả về “Trùng” nếu ít nhất một bản ghi khác có cùng ba giá trị.

3.3. Tự Động Loại Bỏ Trùng Khi Nhập Dữ Liệu (Data Validation)

Bạn có thể ngăn chặn việc nhập dữ liệu trùng lặp ngay từ đầu bằng Data Validation:

1. Chọn cột cần kiểm soát (ví dụ: A2:A1000).
2. Vào Data → Data Validation → Settings.
3. Trong Allow, chọn Custom.
4. Nhập công thức:
   excel =COUNTIF($A$2:$A$1000, A2)=1
5. Tùy chỉnh Error Alert để hiển thị thông báo khi người dùng cố gắng nhập giá trị đã tồn tại.

3.4. Sử Dụng Power Query Để Kiểm Tra và Xóa Trùng

Power Query (Get & Transform) là công cụ mạnh mẽ cho việc làm sạch dữ liệu.

3.4.1. Bước thực hiện

1. Chọn bảng → Data → From Table/Range.
2. Power Query Editor mở ra. Chọn các cột muốn kiểm tra trùng (Shift + click).
3. Vào Home → Remove Rows → Remove Duplicates.
4. Nhấn Close & Load để tải dữ liệu đã làm sạch về Excel.

3.4.2. Ưu điểm

• Lưu lại các bước (query) để tái sử dụng khi có dữ liệu mới.
• Xử lý lượng lớn dữ liệu nhanh hơn so với công thức truyền thống.

3.5. Kiểm Tra Trùng trong PivotTable

Có thể bạn quan tâm: Khóa Học Excel Nâng Cao: Bứt Phá Kỹ Năng Và Tăng Giá Trị Nghề Nghiệp

Khi tạo PivotTable, Excel mặc định sẽ tổng hợp các giá trị trùng. Tuy nhiên, nếu bạn muốn đánh dấu các dòng trùng trong nguồn dữ liệu để phân tích sâu hơn, có thể:

1. Thêm cột trợ giúp với công thức COUNTIFS (như phần 3.2).
2. Khi tạo PivotTable, kéo cột “Trạng thái” (Trùng/Độc nhất) vào Filters để chỉ xem các bản ghi trùng hoặc không trùng.

4. Những Lỗi Thường Gặp Khi Kiểm Tra Trùng Và Cách Khắc Phục

5. Tối Ưu Hóa Kiểm Tra Trùng Với VBA (Macro)

Khi cần thực hiện kiểm tra trùng lặp thường xuyên hoặc trên nhiều file, VBA là giải pháp tự động hoá.

5.1. Mã VBA Đơn Giản Đánh Dấu Trùng

Sub MarkDuplicates() Dim ws As Worksheet Dim lastRow As Long Dim rng As Range Set ws = ThisWorkbook.Sheets("Data") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Xóa màu nền cũ ws.Range("A2:A" & lastRow).Interior.ColorIndex = xlNone ' Đánh dấu trùng trong cột A For Each rng In ws.Range("A2:A" & lastRow) If Application.WorksheetFunction.CountIf(ws.Range("A2:A" & lastRow), rng.Value) > 1 Then rng.Interior.Color = vbYellow End If Next rng
End Sub

• Chức năng: Đánh dấu màu vàng cho các ô trùng trong cột A.
• Lưu ý: Thay đổi ws.Sheets("Data") và cột A tùy theo nhu cầu.

5.2. Mã VBA Xóa Trùng Tự Động

Sub DeleteDuplicates() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Data") ' Giả sử dữ liệu có tiêu đề ở hàng 1 ws.Range("A1").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes MsgBox "Đã xóa các bản ghi trùng dựa trên cột 1,2,3!", vbInformation
End Sub

• Columns:=Array(1, 2, 3) chỉ định các cột (A, B, C) là tiêu chí kiểm tra trùng.
• Header:=xlYes cho biết dữ liệu có tiêu đề.

5.3. Khi Nào Nên Dùng VBA?

• Lặp lại quy trình: Kiểm tra trùng trên nhiều file hoặc nhiều sheet.
• Xử lý dữ liệu lớn: VBA thường nhanh hơn công thức khi thực hiện trên hàng triệu.
• Tự động hoá quy trình: Kết hợp với các thao tác khác (import, export, gửi email).

6. Thực Hành Thực Tế: Dự Án Mini – Quản Lý Danh Sách Khách Hàng

6.1. Mô tả dự án

Bạn là nhân viên marketing, có một file Excel chứa 10.000 bản ghi khách hàng với các cột:
– A: Mã KH
– B: Họ tên
– C: Email
– D: Số điện thoại

Mục tiêu:
1. Loại bỏ các bản ghi trùng dựa trên Email hoặc Số điện thoại (nếu một trong hai trùng, coi là trùng).
2. Đánh dấu các bản ghi còn lại là “Độc nhất”.
3. Tạo một sheet mới chỉ chứa các bản ghi không trùng.

6.2. Quy trình chi tiết

Bước 1: Chuẩn bị dữ liệu

=TRIM(C2) // loại bỏ khoảng trắng thừa trong Email
=TRIM(D2) // loại bỏ khoảng trắng thừa trong SĐT

Áp dụng công thức vào cột phụ (E, F) rồi Copy → Paste Values để thay thế.

Bước 2: Tạo cột kiểm tra trùng (cột G)

=IF( OR( COUNTIF($C$2:$C$10001, C2)>1, COUNTIF($D$2:$D$10001, D2)>1 ), "Trùng", "Độc nhất")

• Hàm OR cho phép kiểm tra bất kỳ một trong hai cột.

Bước 3: Lọc và sao chép

• Lọc cột G chọn “Độc nhất”.
• Copy toàn bộ các hàng đã lọc → Paste vào Sheet mới “Khách Hàng Độc Nhất”.

Bước 4: Xóa các bản ghi trùng (nếu muốn làm sạch trực tiếp)

• Sử dụng Remove Duplicates → Chọn cột C (Email) và D (SĐT) → Tick “My data has headers”.
• Excel sẽ thông báo số bản ghi đã xóa.

Bước 5: Tự động hoá bằng VBA (tùy chọn)

Sub CleanCustomerList() Dim ws As Worksheet, wsClean As Worksheet Dim lastRow As Long, i As Long Set ws = ThisWorkbook.Sheets("Customers") Set wsClean = ThisWorkbook.Sheets.Add(After:=ws) wsClean.Name = "Customers_Unique" lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ws.Range("A1:D1").Copy wsClean.Range("A1") For i = 2 To lastRow If Application.WorksheetFunction.CountIf(ws.Range("C2:C" & lastRow), ws.Cells(i, "C").Value) = 1 _ And Application.WorksheetFunction.CountIf(ws.Range("D2:D" & lastRow), ws.Cells(i, "D").Value) = 1 Then ws.Rows(i).Copy wsClean.Cells(wsClean.Rows.Count, "A").End(xlUp).Offset(1) End If Next i MsgBox "Hoàn tất! Đã tạo sheet Customers_Unique với các bản ghi độc nhất.", vbInformation
End Sub

6.3. Kiểm tra kết quả

• Đảm bảo không còn bản ghi nào có email hoặc số điện thoại trùng bằng cách chạy lại Conditional Formatting.
• Kiểm tra tổng số bản ghi ở sheet mới, so sánh với số bản ghi “Độc nhất” ban đầu.

7. Những Thủ Thuật Nâng Cao Khi Kiểm Tra Trùng

7.1. Ví dụ: Liệt Kê Các Dòng Trùng Trong Một Ô

Giả sử cột A chứa mã sản phẩm, bạn muốn trong ô B1 liệt kê tất cả các mã trùng:

=TEXTJOIN(", ", TRUE, IF(COUNTIF($A$2:$A$1000, $A$2:$A$1000)>1, $A$2:$A$1000, ""))

• Công thức mảng (Ctrl+Shift+Enter trong Excel phiên bản cũ, hoặc tự động trong Excel 365).

8. Tổng Kết

Kiểm tra trùng trên Excel không chỉ là một thao tác “xóa sạch” đơn thuần; nó là bước quan trọng trong quy trình làm sạch dữ liệu, ảnh hưởng trực tiếp tới độ tin cậy của các báo cáo, phân tích và quyết định kinh doanh. Trong bài viết này, chúng ta đã:

1. Hiểu rõ khái niệm và tầm quan trọng của dữ liệu trùng.
2. Khám phá các công cụ tích hợp sẵn: Conditional Formatting, Remove Duplicates, Advanced Filter.
3. Áp dụng các hàm Excel mạnh mẽ: COUNTIF, COUNTIFS, MATCH, EXACT, FILTER.
4. Thực hành trên các kịch bản thực tế: trùng đơn cột, trùng nhiều cột, trùng khi nhập dữ liệu.
5. Giải quyết các lỗi thường gặp và cách phòng ngừa.
6. Tự động hoá quy trình bằng VBA và Power Query.
7. Áp dụng vào dự án thực tế – quản lý danh sách khách hàng.
8. Nâng cao kỹ năng với các thủ thuật và công cụ bổ trợ như Power Pivot, XLOOKUP, TEXTJOIN.

Hãy lựa chọn phương pháp phù hợp với khối lượng dữ liệu và mức độ phức tạp của bài toán. Đối với dữ liệu vừa và nhỏ, Conditional Formatting kết hợp với công thức COUNTIF thường đủ. Đối với dữ liệu lớn hoặc công việc lặp lại, Power Query và VBA sẽ là lựa chọn tối ưu.

Cuối cùng, luôn luôn sao lưu dữ liệu trước khi thực hiện các thao tác xóa hoặc thay đổi quan trọng. Việc này không chỉ bảo vệ dữ liệu mà còn giúp bạn yên tâm thử nghiệm các phương pháp mới mà không sợ mất mát thông tin.

Chúc bạn thành công trong việc kiểm tra và loại bỏ trùng trên Excel, nâng cao chất lượng dữ liệu và đưa ra những quyết định kinh doanh chính xác hơn!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.