Kiểm Tra Và Xử Lý Dòng Trùng Lặp Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Cách Thay Đổi Nhãn Trục (axis Label) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Mở Đầu

Trong môi trường làm việc hiện đại, Excel là công cụ không thể thiếu để quản lý, xử lý và phân tích dữ liệu. 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òng trùng lặp (duplicate rows). Dòng trùng lặp không chỉ làm mất thời gian khi phân tích dữ liệu mà còn có thể gây ra các sai sót nghiêm trọng trong báo cáo, dự báo và quyết định kinh doanh. Vì vậy, việc nắm vững cách kiểm tra và loại bỏ dòng trùng lặp trong Excel là kỹ năng thiết yếu cho mọi nhân viên văn phòng, nhà phân tích dữ liệu và nhà quản lý.

Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từ những phương pháp cơ bản nhất cho tới các kỹ thuật nâng cao, giúp bạn:

• Nhận diện nhanh chóng các dòng trùng lặp.
• Loại bỏ hoặc gắn nhãn các bản sao một cách an toàn.
• Sử dụng công cụ và hàm có sẵn trong Excel (Conditional Formatting, Remove Duplicates, Advanced Filter, Power Query, VBA).
• Áp dụng các giải pháp tự động hoá cho các tập dữ liệu lớn và phức tạp.

Hãy cùng khám phá từng bước một để trở thành “bậc thầy” trong việc xử lý dữ liệu trùng lặp trong Excel!

Có thể bạn quan tâm: Cách Capitalize Mỗi Từ Trong Excel: Hướng Dẫn Chi Tiết Và Thực Hành

1. Hiểu Rõ Về Dòng Trùng Lặp Trong Excel

1.1. Định Nghĩa Dòng Trùng Lặp

Trong Excel, một dòng trùng lặp là một hàng (row) có cùng giá trị ở một hoặc nhiều cột so với một hoặc nhiều hàng khác. Tùy vào mục đích và yêu cầu, bạn có thể:

• Xem xét toàn bộ hàng (tất cả các cột) để xác định trùng lặp.
• Chỉ quan tâm đến một phần cột (ví dụ: mã khách hàng, số điện thoại, email) để xác định các bản sao.

1.2. Nguyên Nhân Gây Ra Dòng Trùng Lặp

• Nhập liệu thủ công: Người dùng sao chép và dán dữ liệu mà không kiểm tra.
• Kết hợp dữ liệu: Khi hợp nhất nhiều bảng dữ liệu, các bản ghi có thể xuất hiện nhiều lần.
• Lỗi hệ thống: Các phần mềm thu thập dữ liệu (CRM, ERP) có thể tạo ra bản sao do lỗi đồng bộ.
• Quy trình không chuẩn: Thiếu quy trình kiểm soát dữ liệu đầu vào.

1.3. Hậu Quả Khi Không Xử Lý Dòng Trùng Lặp

• Sai lệch số liệu: Tổng số, trung bình, tỷ lệ phần trăm… bị sai.
• Mất thời gian: Khi phân tích, bạn phải lọc và kiểm tra lại nhiều lần.
• Quyết định sai lầm: Dựa trên dữ liệu không chính xác, dẫn đến quyết định kinh doanh không tối ưu.
• Chi phí tài nguyên: Lưu trữ và xử lý dữ liệu thừa làm tăng chi phí hệ thống.

Có thể bạn quan tâm: Cách Đổi Chữ In Hoa (capitalize All Letters) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Các Phương Pháp Kiểm Tra Dòng Trùng Lặp Cơ Bản

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

Bước 1: Chọn vùng dữ liệu cần kiểm tra (ví dụ: A1:D1000).
Bước 2: Vào Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values…
Bước 3: Trong hộp thoại, chọn “Duplicate” và định dạng màu (ví dụ: nền đỏ). Nhấn OK.

Lưu ý: Phương pháp này mặc định kiểm tra trùng lặp các ô riêng lẻ. Nếu muốn kiểm tra toàn bộ dòng, bạn cần tạo công thức tùy chỉnh.

Công thức tùy chỉnh:
Giả sử muốn kiểm tra trùng lặp dựa trên cột A và B (điều kiện là cả hai cột đều giống nhau).
– Chọn vùng dữ liệu (A2:B1000).
– Vào Conditional Formatting → New Rule → Use a formula to determine which cells to format.
– Nhập công thức:

=COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2)>1

• Đặt định dạng (màu nền xanh) và OK.

2.2. Sử Dụng Hàm COUNTIF / COUNTIFS

COUNTIF dùng để đếm số lần xuất hiện của một giá trị trong một cột.

=COUNTIF($A$2:$A$1000, A2) > 1

Nếu công thức trả về TRUE → dòng hiện tại là bản sao.

COUNTIFS cho phép kiểm tra nhiều tiêu chí đồng thời.

=COUNTIFS($A$2:$A$1000, A2, $B$2:$B$1000, B2) > 1

Bạn có thể tạo một cột phụ (ví dụ: cột E) để nhập công thức trên, sau đó lọc hoặc sắp xếp dựa trên giá trị TRUE/FALSE.

2.3. Sử Dụng Remove Duplicates (Xóa Dòng Trùng Lặp)

Excel cung cấp công cụ Data → Remove Duplicates để nhanh chóng loại bỏ các bản sao.

Bước thực hiện:

1. Chọn toàn bộ bảng dữ liệu (hoặc một phần).
2. Vào Data → Remove Duplicates.
3. Trong cửa sổ hiện ra, chọn các cột mà bạn muốn xem là tiêu chí trùng lặp.
4. Nếu muốn xét toàn bộ hàng, để tất cả các cột được chọn.
5. Nếu chỉ xét một vài cột, chỉ tick các cột đó.
6. Nhấn OK. Excel sẽ thông báo số lượng bản ghi bị xóa và số bản ghi còn lại.

Cảnh báo: Công cụ này xóa trực tiếp các dòng trùng lặp, không có bản sao. Trước khi thực hiện, luôn sao lưu dữ liệu hoặc tạo một bản sao của bảng.

2.4. Advanced Filter (Lọc Nâng Cao)

Advanced Filter cho phép lọc ra bản ghi duy nhất (unique records) mà không thay đổi dữ liệu gốc.

Cách thực hiện:

1. Đặt tiêu đề cột ở hàng đầu tiên (ví dụ: A1:D1).
2. Chọn Data → Advanced.
3. Chọn “Copy to another location”.
4. Đặt List range là vùng dữ liệu (A1:D1000).
5. Đánh dấu Unique records only.
6. Chọn Copy to (ví dụ: F1).
7. Nhấn OK → dữ liệu duy nhất sẽ được sao chép vào cột F trở đi.

3. Xử Lý Dòng Trùng Lặp Với Power Query (Get & Transform)

Power Query là công cụ mạnh mẽ cho phép xử lý dữ liệu một cách linh hoạt, đặc biệt hữu ích với các tập dữ liệu lớn.

3.1. Nhập Dữ Liệu Vào Power Query

1. Chọn bảng dữ liệu → Data → From Table/Range.
2. Nếu dữ liệu chưa ở dạng Table, Excel sẽ yêu cầu tạo Table.
3. Power Query Editor sẽ mở ra.

3.2. Xóa Dòng Trùng Lặp

Trong Power Query Editor:

• Chọn Home → Remove Rows → Remove Duplicates.
• Mặc định sẽ xét toàn bộ các cột đã chọn. Nếu muốn chỉ xét một số cột, trước tiên bỏ chọn các cột không cần thiết (chuột phải → Remove Columns) hoặc chọn các cột cần → Remove Duplicates.

3.3. Thêm Cột Đánh Dấu Bản Sao

Nếu bạn muốn giữ lại các bản sao và chỉ đánh dấu chúng:

1. Duplicate Column: Chọn toàn bộ cột (hoặc các cột tiêu chí) → Add Column → Duplicate Column.
2. Đổi tên cột mới thành “Key”.
3. Group By: Home → Group By →
4. Group by: “Key”.
5. New column name: “Count”.
6. Operation: Count Rows.
7. Kết quả sẽ là bảng tóm tắt số lần xuất hiện của mỗi “Key”.
8. Merge Queries: Quay lại bảng gốc, Merge với bảng tóm tắt dựa trên “Key”.
9. Thêm cột “Count” vào bảng gốc → các dòng có Count > 1 là bản sao.

3.4. Tự Động Hóa Với “Refresh”

Sau khi thiết lập xong, bạn chỉ cần Refresh (Data → Refresh All) để cập nhật dữ liệu mới và tự động loại bỏ/đánh dấu bản sao mà không cần thao tác lại.

4. Sử Dụng VBA Để Kiểm Tra và Loại Bỏ Dòng Trùng Lặp

Với các quy trình lặp đi lặp lại hoặc khi cần tích hợp vào macro, VBA (Visual Basic for Applications) là giải pháp tối ưu.

4.1. Macro Loại Bỏ Dòng Trùng Lặp Toàn Bộ

Sub RemoveAllDuplicates() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ' Thay tên sheet phù hợp With ws Dim lastRow As Long lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' Giả sử dữ liệu bắt đầu ở cột A .Range("A1:D" & lastRow).RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlYes MsgBox "Đã xóa các dòng trùng lặp!" End With
End Sub

• Columns:=Array(1,2,3,4) chỉ định các cột cần xét trùng lặp (cột A=1, B=2,…).
• Header:=xlYes nếu dòng đầu là tiêu đề.

4.2. Macro Đánh Dấu Bản Sao Với Màu Nền

Có thể bạn quan tâm: Cách Chuyển Đổi Số Thành Văn Bản Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Sub HighlightDuplicates() Dim ws As Worksheet, rng As Range, cell As Range Set ws = ThisWorkbook.Sheets("Sheet1") Dim lastRow As Long, lastCol As Long lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column Set rng = ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, lastCol)) ' Bắt đầu từ hàng 2 (bỏ tiêu đề) Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") Dim key As String For Each cell In rng.Rows key = Join(Application.Transpose(Application.Transpose(cell.Value)), "|") If dict.Exists(key) Then cell.Interior.Color = RGB(255, 199, 206) ' Màu hồng nhạt Else dict.Add key, 1 End If Next cell MsgBox "Hoàn thành đánh dấu bản sao."
End Sub

• Hàm Join(..., "|") tạo một chuỗi duy nhất từ toàn bộ giá trị trong một hàng, dùng làm key cho dictionary.
• Nếu key đã tồn tại → đánh dấu màu nền.

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

• Khi quy trình phải chạy thường xuyên (hàng ngày, hàng tuần).
• Khi cần tự động hoá trong một workbook phức tạp với nhiều sheet.
• Khi muốn tùy biến sâu hơn (ví dụ: chỉ xóa bản sao nếu một cột nhất định thỏa mãn điều kiện).

5. Kiểm Tra Trùng Lặp Dựa Trên Nhiều Tiêu Chí

Trong thực tế, thường có trường hợp cần xét trùng lặp dựa trên kết hợp nhiều cột (ví dụ: “Tên khách hàng” + “Ngày sinh” + “Số điện thoại”). Dưới đây là các cách thực hiện.

5.1. Sử Dụng Công Thức CONCATENATE (Hoặc TEXTJOIN)

Tạo một cột phụ để nối các giá trị lại thành một chuỗi duy nhất.

=TEXTJOIN("|",TRUE, A2, B2, C2)

Sau đó áp dụng COUNTIF hoặc Conditional Formatting trên cột này.

5.2. Sử Dụng Power Query “Group By”

Trong Power Query, chọn Group By → Advanced → Thêm các cột cần nhóm (Tên, Ngày sinh, SĐT) → tạo cột “Count” → lọc “Count > 1”.

5.3. Sử Dụng Hàm SUMPRODUCT

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

Công thức này trả về TRUE nếu có ít nhất một dòng khác có cùng ba giá trị.

6. Xử Lý Dòng Trùng Lặp Khi Dữ Liệu Được Cập Nhật Định Kỳ

6.1. Thiết Lập Dynamic Named Ranges

Sử dụng công thức OFFSET + COUNTA để tạo vùng dữ liệu động, giúp các công cụ (Conditional Formatting, Data Validation) luôn áp dụng cho toàn bộ dữ liệu mới.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4)

6.2. Sử Dụng Table (Bảng) và Structured References

Khi dữ liệu được chuyển thành Table (Ctrl+T), Excel tự động mở rộng công thức, định dạng và các rule khi thêm hàng mới.

• Conditional Formatting sẽ tự động áp dụng cho các hàng mới.
• Remove Duplicates → Table → Design → Remove Duplicates.

6.3. Tự Động Hóa Với Power Automate (Flow)

Nếu dữ liệu được nhập từ các nguồn (SharePoint, SQL, Form), bạn có thể tạo Power Automate Flow:

1. Trigger: Khi có file Excel mới hoặc dữ liệu thay đổi.
2. Action: “List rows present in a table”.
3. Apply Select và Union để loại bỏ bản sao (Union trong Power Automate tự động loại bỏ trùng lặp).
4. Ghi lại kết quả vào file đích.

7. Kiểm Tra Trùng Lặp Trong Các Tình Huống Đặc Biệt

7.1. Trùng Lặp Với Khoảng Cách (Fuzzy Matching)

Đôi khi dữ liệu không hoàn toàn giống nhau do lỗi nhập (VD: “John Smith” vs “Jon Smith”). Đối với trường hợp này, Exact Match không đủ.

• Sử dụng Add-in “Fuzzy Lookup” (Microsoft) để tính độ tương đồng.
• Power Query: Thêm cột “Levenshtein Distance” (có thể dùng custom function) để đo khoảng cách giữa chuỗi.
• VBA: Viết hàm Levenshtein và lọc các giá trị có khoảng cách ≤ 2.

7.2. Trùng Lặp Với Dữ Liệu Thời Gian (Date/Time)

Khi dữ liệu chứa ngày giờ, đôi khi chỉ cần so sánh ngày mà không quan tâm tới thời gian.

=COUNTIFS($A$2:$A$1000, INT(A2))>1 'Giả sử cột A chứa ngày giờ

INT chuyển ngày giờ thành chỉ ngày.

7.3. Trùng Lặp Với Giá Trị Null/Blank

Một số bản ghi có trường trống, chúng cũng có thể được tính là “trùng lặp”.

=COUNTIFS($A$2:$A$1000, "", $B$2:$B$1000, "")>1

Hoặc trong Power Query, sử dụng Remove Duplicates sau khi thay thế các giá trị null bằng một ký tự đặc biệt (ví dụ “NULL”).

8. Thực Hành: Bài Tập Từ Đầu Đến Cuối

8.1. Dữ Liệu Mẫu

8.2. Bước 1: Đánh Dấu Bản Sao Dựa Trên Mã KH

• Conditional Formatting → New Rule → Use formula:
  =COUNTIF($A$2:$A$7, $A2)>1 → Đánh dấu màu vàng.

8.3. Bước 2: Loại Bỏ Dòng Trùng Lặp Dựa Trên Tên + Email

• Thêm cột phụ E: =TEXTJOIN("|",TRUE, B2, C2)
• Sử dụng Remove Duplicates → Chọn cột E → Xóa.

Kết quả: chỉ còn 4 dòng duy nhất.

8.4. Bước 3: Tự Động Hóa Với Power Query

1. Chọn Data → From Table/Range.
2. Trong Power Query, chọn cột Tên và Email → Remove Duplicates.
3. Nhấn Close & Load → Được bảng mới không trùng lặp.

8.5. Bước 4: Tạo Macro VBA Đánh Dấu Tự Động

Sub MarkDupByNameEmail() Dim ws As Worksheet, lastRow As Long, i As Long Set ws = ThisWorkbook.Sheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary") Dim key As String For i = 2 To lastRow key = ws.Cells(i, "B").Value & "|" & ws.Cells(i, "C").Value If dict.Exists(key) Then ws.Rows(i).Interior.Color = RGB(255, 199, 206) Else dict.Add key, 1 End If Next i
End Sub

Chạy macro → các bản sao sẽ được tô màu hồng.

9. Lời Khuyên & Thực Hành Tốt Nhất

1. Luôn sao lưu dữ liệu trước khi thực hiện bất kỳ thao tác xóa nào.
2. Sử dụng Table để dữ liệu mở rộng tự động, giảm lỗi công thức.
3. Khi dữ liệu lớn (> 100,000 dòng), ưu tiên Power Query hoặc VBA thay vì Conditional Formatting vì hiệu suất tốt hơn.
4. Kiểm tra lại sau khi loại bỏ bản sao: tổng số, trung bình, hoặc các KPI quan trọng để đảm bảo không mất dữ liệu quan trọng.
5. Nếu dữ liệu được nhập từ nhiều nguồn, thiết lập quy trình chuẩn (validation, dropdown list) để giảm khả năng tạo bản sao ngay từ đầu.
6. Đối với độ trễ thời gian (real‑time), cân nhắc sử dụng Power Automate hoặc SQL để lọc trùng lặp trước khi đưa vào Excel.

10. Kết Luận

Việc kiểm tra và xử lý dòng trùng lặp trong Excel không chỉ đơn giản là một thao tác kỹ thuật mà còn là một phần quan trọng trong quản lý chất lượng dữ liệu. Từ các công cụ cơ bản như Conditional Formatting, Remove Duplicates, cho tới các giải pháp mạnh mẽ như Power Query, VBA và Power Automate, mỗi phương pháp đều có ưu và nhược điểm riêng, phù hợp với các tình huống và quy mô dữ liệu khác nhau.

Bằng cách nắm vững các kỹ thuật trên, bạn sẽ:

• Tiết kiệm thời gian và công sức trong việc làm sạch dữ liệu.
• Đảm bảo độ chính xác và tin cậy của các báo cáo, phân tích.
• Tạo ra quy trình làm việc chuyên nghiệp, có thể tự động hoá và mở rộng.

Hãy áp dụng ngay những kiến thức này vào công việc hằng ngày của bạn, và bạn sẽ cảm nhận được sự khác biệt rõ rệt trong hiệu suất làm việc và chất lượng dữ liệu. Chúc bạn thành công!

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ẻ.