Cách Tìm Và Xử Lý Các Giá Trị Trùng Lặp Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Trong môi trường làm việc hiện đại, Excel vẫn là công cụ không thể thiếu đối với mọi người làm việc với dữ liệu, từ nhân viên văn phòng, nhà phân tích tài chính, cho tới nhà quản lý dự án. Một trong những vấn đề phổ biến mà người dùng Excel thường gặp phải là các giá trị trùng lặp (duplicate values). Những bản ghi trùng lặp có thể gây ra lỗi trong việc phân tích, làm sai lệch kết quả báo cáo, thậm chí khiến quyết định kinh doanh trở nên không chính xác. Vì vậy, việc biết cách tìm và xử lý các giá trị trùng lặp trong Excel là một kỹ năng thiết yếu.

Bài viết này sẽ cung cấp cho bạn một hướng dẫn toàn diện, chi tiết và thực tiễn, bao gồm:

1. Hiểu rõ khái niệm giá trị trùng lặp và tại sao chúng lại quan trọng.
2. Các phương pháp cơ bản để phát hiện trùng lặp: Conditional Formatting, Remove Duplicates, Advanced Filter.
3. Sử dụng công thức (COUNTIF, SUMPRODUCT, MATCH, VLOOKUP, XLOOKUP) để xác định và đánh dấu các bản ghi trùng.
4. Kỹ thuật nâng cao với Power Query, PivotTable, và VBA để xử lý dữ liệu lớn.
5. Các bước thực hành từng bước kèm ví dụ thực tế, hình ảnh minh họa.
6. Mẹo tối ưu để tránh trùng lặp trong tương lai và duy trì dữ liệu sạch sẽ.

Có thể bạn quan tâm: Cách Giữ Cột Cố Định Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Giá Trị Trùng Lặp Là Gì? Tại Sao Chúng Quan Trọng?

1.1 Định Nghĩa

Trong Excel, giá trị trùng lặp đề cập tới một hoặc nhiều ô chứa cùng một dữ liệu (số, văn bản, ngày tháng, hoặc công thức) xuất hiện nhiều hơn một lần trong cùng một cột, hàng, hoặc vùng dữ liệu. Trùng lặp có thể là:

• Hoàn toàn trùng: Cùng một giá trị xuất hiện 2, 3, … lần.
• Gần giống: Giá trị có sự khác biệt nhỏ (ví dụ: khoảng trắng thừa, chữ hoa/thường khác nhau, ký tự đặc biệt).

1.2 Hậu Quả Khi Không Xử Lý Trùng Lặp

Vì vậy, đảm bảo dữ liệu sạch, không trùng lặp là bước đầu tiên và quan trọng nhất trong bất kỳ quy trình làm việc nào liên quan tới dữ liệu.

Có thể bạn quan tâm: Cách Sử Dụng Filter By Color Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

2. Các Phương Pháp Cơ Bản Để Tìm Giá Trị Trùng Lặp

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

Đây là cách nhanh nhất và trực quan nhất để đánh dấu các giá trị trùng lặp trong một cột.

Bước thực hiện:

1. Chọn vùng dữ liệu cần kiểm tra (ví dụ: A2:A1000).
2. Vào Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values….
3. Trong hộp thoại, chọn Duplicate và chọn màu hiển thị (mặc định là Light Red Fill with Dark Red Text).
4. Nhấn OK.

Lưu ý:
– Phương pháp này chỉ đánh dấu; nó không loại bỏ hay liệt kê các bản sao.
– Đối với dữ liệu lớn (hàng triệu), Conditional Formatting có thể làm chậm Excel, vì vậy chỉ nên dùng cho tập dữ liệu vừa phải.

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

Nếu mục tiêu là loại bỏ các bản sao, công cụ này rất tiện lợi.

Hướng dẫn:

1. Chọn cột hoặc vùng cần xử lý (có thể chọn nhiều cột cùng lúc nếu muốn xác định trùng lặp dựa trên nhiều tiêu chí).
2. Vào Data → Remove Duplicates.
3. Trong hộp thoại, chọn các cột là tiêu chí so sánh (đánh dấu tất cả nếu muốn toàn bộ hàng phải giống nhau mới được coi là trùng).
4. 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:
– Backup dữ liệu trước khi xóa, vì thao tác này không thể hoàn tác nếu không có Undo.
– Khi có nhiều cột, hãy cân nhắc kỹ tiêu chí để tránh xóa nhầm các bản ghi quan trọng.

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

Advanced Filter cho phép trích xuất danh sách duy nhất (unique) hoặc chỉ hiển thị các bản sao.

Các bước:

1. Đặt tiêu đề cột ở một ô trống (ví dụ: D1 = “Tên khách hàng”).
2. Nhập công thức để lọc unique:
3. Chọn Data → Advanced.
4. Chọn Copy to another location.
5. List range: vùng dữ liệu gốc.
6. Criteria range: để trống nếu không có tiêu chí phụ.
7. Copy to: ô đích (ví dụ D2).
8. Đánh dấu Unique records only.
9. Nhấn OK. Bạn sẽ có danh sách không trùng lặp ở vị trí mới.

Ứng dụng: Khi muốn tạo bảng đối chiếu (lookup table) không chứa bản sao.

Có thể bạn quan tâm: File Excel Tính Ván Khuôn: Hướng Dẫn Chi Tiết Từ A Đến Z

3. Sử Dụng Công Thức Để Xác Định Trùng Lặp

Công thức trong Excel mạnh mẽ và linh hoạt, cho phép bạn tự động phát hiện và đánh dấu các giá trị trùng lặp trong bất kỳ bối cảnh nào.

3.1 COUNTIF – Đếm Số Lần Xuất Hiện

Công thức cơ bản nhất để kiểm tra trùng lặp:

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

• Nếu kết quả là TRUE, thì giá trị trong A2 xuất hiện ít nhất 2 lần trong toàn cột.
• Áp dụng Conditional Formatting với công thức trên để tô màu các ô trùng.

Bước thực hiện:

1. Chọn vùng dữ liệu A2:A1000.
2. Vào Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Nhập công thức =COUNTIF($A$2:$A$1000, A2)>1.
4. Chọn màu nền, nhấn OK.

3.2 SUMPRODUCT – Kiểm Tra Nhiều Tiêu Chí

Khi cần kiểm tra trùng lặp dựa trên nhiều cột (ví dụ: Họ + Email).

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

• Công thức trả về TRUE nếu kết hợp giá trị trong cột A và B xuất hiện nhiều hơn một lần.

3.3 MATCH + ISNUMBER – Tìm Vị Trí Đầu Tiên

=IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),"Duplicate","Unique")

• MATCH tìm kiếm giá trị A2 trong phạm vi từ đầu danh sách tới dòng trước đó.
• Nếu tìm thấy, trả về vị trí (số), nếu không trả về lỗi #N/A.
• ISNUMBER chuyển lỗi thành FALSE, giá trị thành TRUE.

3.4 VLOOKUP / XLOOKUP – Tra Cứu Trùng Lặp

Nếu muốn trả về giá trị liên quan khi phát hiện trùng lặp:

=IFERROR(VLOOKUP(A2,$A$2:$B$1000,2,FALSE),"")

• Khi A2 trùng, VLOOKUP trả về giá trị ở cột B cùng dòng.

Trong Excel 365/2026, XLOOKUP thay thế VLOOKUP:

=IFERROR(XLOOKUP(A2,$A$2:$A$1000,$B$2:$B$1000),"")

3.5 ARRAY FORMULA (Dynamic Array) – Danh Sách Trùng Lặp

Excel 365 hỗ trợ công thức mảng động, ví dụ:

=UNIQUE(FILTER(A2:A1000,COUNTIF(A2:A1000,A2:A1000)>1))

• COUNTIF tính số lần xuất hiện của mỗi giá trị.
• FILTER giữ lại những giá trị có số lần >1.
• UNIQUE loại bỏ các bản sao trong danh sách kết quả, trả về danh sách duy nhất các giá trị trùng.

3.6 Các Công Thức Thông Dụng Khác

Kỹ thuật chuẩn hoá dữ liệu (cleaning) luôn đi kèm với việc tìm trùng lặp để giảm thiểu “false duplicates”.

4. Xử Lý Dữ Liệu Lớn: Power Query và PivotTable

Khi làm việc với hàng chục ngàn hoặc hàng trăm ngàn bản ghi, các phương pháp truyền thống có thể chậm hoặc không khả thi. Hai công cụ mạnh mẽ trong Excel là Power Query và PivotTable.

4.1 Power Query – ETL (Extract, Transform, Load) trong Excel

Power Query (còn gọi là Get & Transform) cho phép tự động hoá việc import, làm sạch, và loại bỏ trùng lặp.

Quy trình:

1. Import dữ liệu:
2. Data → Get Data → From File → From Workbook/CSV/…
3. Mở Power Query Editor.
4. Chuẩn hoá dữ liệu:
5. Sử dụng Transform → Trim, Transform → Clean, Transform → Uppercase/Lowercase.
6. Xóa bản sao:
7. Chọn cột (hoặc nhiều cột) → Remove Rows → Remove Duplicates.
8. Tạo cột đánh dấu trùng (nếu muốn giữ lại bản sao):
9. Thêm cột tùy chỉnh: = Table.Group(Source, {"Column1"}, {{"Count", each Table.RowCount(_), Int64.Type}})
10. Sau khi nhóm, lọc Count > 1 để lấy các giá trị trùng.
11. Load kết quả về Excel (Close & Load).

Ưu điểm:
– Tự động hoá: Lưu lại các bước, có thể refresh khi dữ liệu thay đổi.
– Hiệu năng: Xử lý nhanh hơn so với công thức trên dữ liệu lớn.
– Lịch sử thay đổi: Dễ dàng quay lại các bước chỉnh sửa.

4.2 PivotTable – Tóm Tắt và Đếm Trùng Lặp

PivotTable không chỉ dùng để tổng hợp, mà còn là công cụ đếm tần suất của mỗi giá trị, từ đó xác định trùng lặp.

Thực hiện:

1. Chọn vùng dữ liệu → Insert → PivotTable.
2. Kéo cột cần kiểm tra vào Rows.
3. Kéo cùng cột (hoặc bất kỳ cột nào) vào Values, chọn Count.
4. PivotTable sẽ hiển thị mỗi giá trị và số lần xuất hiện.
5. Lọc hoặc sắp xếp Count > 1 để lấy danh sách trùng.

Lưu ý: Khi dữ liệu thay đổi, nhớ Refresh PivotTable (Right‑click → Refresh).

5. Tự Động Hóa Với VBA – Khi Mọi Cách Khác Không Đủ

Với các quy trình phức tạp, lặp đi lặp lại, VBA (Visual Basic for Applications) cho phép viết macro để:

• Tìm và đánh dấu các bản sao.
• Di chuyển bản sao sang một sheet khác.
• Ghi log các bản sao vào file txt hoặc workbook khác.

5.1 Ví Dụ Macro Đánh Dấu Trùng Lặp

Sub HighlightDuplicates() Dim ws As Worksheet Dim rng As Range, cell As Range Dim dict As Object Set ws = ThisWorkbook.Sheets("Data") Set rng = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) Set dict = CreateObject("Scripting.Dictionary") Application.ScreenUpdating = False For Each cell In rng If Not IsEmpty(cell) Then If dict.Exists(cell.Value) Then cell.Interior.Color = vbYellow ' Đánh dấu trùng dict(cell.Value).Interior.Color = vbYellow ' Đánh dấu cả bản đầu Else dict.Add cell.Value, cell End If End If Next cell Application.ScreenUpdating = True MsgBox "Hoàn tất! Các giá trị trùng đã được tô màu vàng.", vbInformation
End Sub

• Giải thích:
• Sử dụng Scripting.Dictionary để lưu các giá trị đã gặp.
• Khi gặp giá trị đã tồn tại, tô màu vàng cho ô hiện tại và ô lưu trong dictionary (bản đầu).

5.2 Macro Di Chuyển Bản Sao Sang Sheet Khác

Có thể bạn quan tâm: Financial Report Format In Excel: Hướng Dẫn Chi Tiết Từ A Đến Z Để Tạo Báo Cáo Tài Chính Chuyên Nghiệp

Sub MoveDuplicates() Dim src As Worksheet, dst As Worksheet Dim rng As Range, cell As Range Dim dict As Object, lastRow As Long Set src = ThisWorkbook.Sheets("Data") Set dst = ThisWorkbook.Sheets("Duplicates") Set rng = src.Range("A2:A" & src.Cells(src.Rows.Count, "A").End(xlUp).Row) Set dict = CreateObject("Scripting.Dictionary") Application.ScreenUpdating = False For Each cell In rng If Not IsEmpty(cell) Then If dict.Exists(cell.Value) Then lastRow = dst.Cells(dst.Rows.Count, "A").End(xlUp).Row + 1 src.Rows(cell.Row).Copy Destination:=dst.Rows(lastRow) src.Rows(cell.Row).Delete Else dict.Add cell.Value, True End If End If Next cell Application.ScreenUpdating = True MsgBox "Các bản sao đã được di chuyển sang sheet 'Duplicates'.", vbInformation
End Sub

Lưu ý khi dùng VBA:
– Luôn sao lưu workbook trước khi chạy macro.
– Kiểm tra các tham chiếu (tên sheet, vùng dữ liệu) để tránh lỗi.
– Đặt macro vào Personal Macro Workbook nếu muốn dùng lại cho nhiều file.

6. Kiểm Tra Trùng Lặp Dựa Trên Nhiều Tiêu Chí (Composite Keys)

Trong thực tế, nhiều lần trùng lặp không chỉ dựa trên một cột mà trên các cột kết hợp (composite key). Ví dụ: cùng một khách hàng nhưng với hai địa chỉ email khác nhau; hoặc cùng một đơn hàng nhưng số phiếu và ngày giao trùng.

6.1 Tạo Cột Helper (Trợ Giúp)

Cách đơn giản nhất là tạo một cột tạm (helper column) chứa chuỗi kết hợp của các cột cần so sánh.

=TRIM(UPPER(A2)) & "|" & TRIM(LOWER(B2)) & "|" & TEXT(C2,"yyyymmdd")

• TRIM loại bỏ khoảng trắng thừa.
• UPPER/LOWER chuẩn hoá chữ.
• TEXT(...,"yyyymmdd") chuẩn hoá ngày tháng.
• Dấu | là ký tự phân tách để tránh nhầm lẫn.

Sau khi tạo cột này, áp dụng các công thức COUNTIF, Conditional Formatting, hoặc Remove Duplicates trên cột này.

6.2 Sử Dụng Power Query Để Nhóm (Group By)

Power Query cho phép group dữ liệu dựa trên nhiều cột:

1. Mở Power Query Editor.
2. Chọn các cột muốn làm khóa.
3. Home → Group By.
4. Đặt Operation là Count Rows.
5. Kết quả sẽ hiển thị số lần xuất hiện của mỗi tổ hợp.
6. Lọc Count > 1 để lấy các bản sao.

7. Kiểm Tra Trùng Lặp Khi Nhập Dữ Liệu (Data Validation)

Để ngăn chặn việc tạo bản sao ngay từ đầu, bạn có thể áp dụng Data Validation với công thức.

7.1 Công Thức Ngăn Nhập Trùng

Giả sử cột A là nơi nhập tên khách hàng, muốn ngăn nhập trùng:

1. Chọn cột A (hoặc phạm vi A2:A1000).
2. Data → Data Validation → Settings → Allow: Custom.
3. Nhập công thức:

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

1. Nhấn OK.

Khi người dùng cố gắng nhập một giá trị đã tồn tại, Excel sẽ hiện thông báo lỗi và không cho nhập.

Mở rộng: Bạn có thể tạo thông báo tùy chỉnh để hướng dẫn người dùng.

7.2 Kết Hợp Với Form Controls

Nếu bạn sử dụng Combo Box hoặc List Box, hãy liên kết nguồn dữ liệu đã loại bỏ trùng lặp (có thể dùng UNIQUE trong Excel 365) để người dùng chỉ chọn các giá trị hợp lệ.

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

8.1 Trùng Lặp Với Dấu Cách và Ký Tự Đặc Biệt

• Dấu cách đầu/cuối: =TRIM(A2)
• Dấu gạch ngang, dấu chấm: =SUBSTITUTE(SUBSTITUTE(A2,"-",""),".","")

Sau khi chuẩn hoá, áp dụng lại các công thức trùng lặp.

8.2 Trùng Lặp Dựa Trên Phần Con (Partial Match)

Nếu muốn tìm các giá trị chứa một chuỗi cụ thể (ví dụ: “Smith” trong họ và tên), dùng COUNTIF với ký tự đại diện .

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

8.3 Trùng Lặp Khi Dữ Liệu Được Liên Kết Từ Nguồn Ngoài (External Data)

Khi dữ liệu được import từ cơ sở dữ liệu, API, hoặc file CSV, có thể có dòng tiêu đề lặp hoặc bản ghi trùng do quá trình đồng bộ. Hãy:

1. Xóa dòng tiêu đề dư thừa (Power Query → Remove Top Rows).
2. Sử dụng Remove Duplicates ngay sau khi import.
3. Lưu lịch sử import để so sánh và phát hiện trùng lặp trong các lần tải về.

9. Thực Hành: Bài Tập Tìm Trùng Lặp Từ Đầu Đến Cuối

Mục tiêu:
– Nhận diện các bản sao trong danh sách khách hàng.
– Loại bỏ các bản sao, đồng thời lưu lại bản sao vào sheet “Duplicates”.
– Tự động hoá quy trình bằng macro.

Dữ Liệu Mẫu

Bước 1: Chuẩn Hoá Dữ Liệu

• Trim và Uppercase cho cột Email:
  =UPPER(TRIM(C2)) → copy xuống toàn cột.
• Trim cho cột Tên:
  =TRIM(B2).

Bước 2: Tạo Composite Key

Trong cột E (Helper):

=UPPER(TRIM(B2)) & "|" & UPPER(TRIM(C2)) & "|" & TEXT(D2,"yyyymmdd")

Bước 3: Đánh Dấu Trùng Lặp

• Chọn cột E → Conditional Formatting → =COUNTIF($E$2:$E$1000,E2)>1 → màu nền xanh nhạt.

Bước 4: Loại Bỏ và Lưu Bản Sao

Sử dụng Power Query:

1. Data → From Table/Range → tạo Table.
2. Trong Power Query, Duplicate Column → Composite Key.
3. Remove Duplicates dựa trên Composite Key.
4. Nhân đôi query: một cho Unique, một cho Duplicates (filter Count > 1).
5. Load cả hai query vào hai sheet.

Hoặc dùng VBA (macro trong phần 5) để tự động di chuyển.

Bước 5: Kiểm Tra Kết Quả

• Sheet “Data” chỉ còn các bản duy nhất.
• Sheet “Duplicates” chứa các bản sao (có thể có nhiều dòng cùng một khách hàng).

Kết luận: Bạn đã hoàn thành quy trình tìm, đánh dấu, loại bỏ và lưu trữ các giá trị trùng lặp một cách chuyên nghiệp.

10. Mẹo Và Thủ Thuật Để Duy Trì Dữ Liệu Không Trùng Lặp

11. Tổng Kết

Việc tìm và xử lý các giá trị trùng lặp trong Excel không chỉ là một thao tác kỹ thuật đơn thuần mà còn là một phần quan trọng của quản trị dữ liệu. Từ các công cụ nhanh như Conditional Formatting, Remove Duplicates, tới các giải pháp mạnh mẽ hơn như Power Query, PivotTable, và VBA, bạn có thể lựa chọn phương pháp phù hợp dựa trên:

• Kích thước dữ liệu (vài chục, vài trăm, vài nghìn, hoặc hàng triệu bản ghi).
• Độ phức tạp (trùng lặp dựa trên một cột hay nhiều cột).
• Yêu cầu tự động hoá (cần cập nhật thường xuyên hay chỉ một lần).

Bằng cách chuẩn hoá dữ liệu, tạo cột trợ giúp, sử dụng công thức thích hợp, và áp dụng các công cụ nâng cao, bạn sẽ:

1. Phát hiện nhanh mọi bản sao, dù là hoàn toàn giống hay chỉ gần giống.
2. Loại bỏ hoặc di chuyển bản sao một cách an toàn, không làm mất dữ liệu quan trọng.
3. Ngăn ngừa việc tạo ra các bản sao mới trong tương lai.

Hy vọng bài viết này đã cung cấp cho bạn một bộ công cụ toàn diện để làm việc hiệu quả hơn với dữ liệu trong Excel. Hãy áp dụng ngay những kỹ thuật trên vào dự án thực tế của bạn và chia sẻ kết quả với đồng nghiệp – vì một môi trường dữ liệu sạch sẽ, chính xác là nền tảng cho mọi quyết định kinh doanh thành công.

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