Hướng Dẫn Xóa Dữ Liệu Trùng Lặp Trong Một Ô Excel: Các Phương Pháp Hiệu Quả Nhất

Thời gian đọc ước tính: 12 phút

Những điểm chính

• Hiểu bản chất vấn đề: Dữ liệu trùng lặp trong một ô thường do quá trình nhập liệu thủ công hoặc xuất dữ liệu từ các hệ thống khác, gây khó khăn cho việc phân tích.
• Giải pháp cho Excel 365/2021+: Sử dụng tổ hợp hàm mảng động mạnh mẽ gồm TEXTSPLIT, UNIQUE và TEXTJOIN để xử lý tự động.
• Xử lý dữ liệu “bẩn”: Kết hợp hàm TRIM để loại bỏ khoảng trắng thừa và xử lý nhiều loại ký tự phân cách (dấu phẩy, dấu gạch ngang, v.v.) cùng lúc.
• Giải pháp cho Excel đời cũ: Sử dụng VBA (Visual Basic for Applications) để tạo hàm tự định nghĩa (UDF) giúp lọc trùng linh hoạt.
• Phương pháp Power Query: Một kỹ thuật nâng cao không dùng công thức để làm sạch dữ liệu hàng loạt quy mô lớn.

Việc làm sạch dữ liệu là một bước không thể thiếu đối với bất kỳ ai làm việc với bảng tính. Thông thường, chúng ta quen thuộc với tính năng “Remove Duplicates” có sẵn trên thanh công cụ Data để xử lý các hàng trùng nhau. Tuy nhiên, một thách thức lớn hơn xuất hiện khi các giá trị trùng lặp nằm bên trong một ô duy nhất.

Ví dụ, bạn có một danh sách khách hàng và trong cột “Sản phẩm đã mua”, một khách hàng có thể xuất hiện dữ liệu dạng: “Laptop, Chuột, Laptop, Bàn phím”. Yêu cầu đặt ra là phải đưa về dạng chuẩn: “Laptop, Chuột, Bàn phím”.

Dữ liệu mẫu chứa các giá trị trùng lặp trong cùng một ô Excel cần được xử lý

Trước đây, việc này đòi hỏi nhiều bước thủ công hoặc các đoạn mã phức tạp. Nhưng với sự ra đời của các hàm mảng động (Dynamic Array Functions) trong Excel 365 và Excel 2021, quy trình này đã trở nên đơn giản hơn rất nhiều. Bài viết này sẽ hướng dẫn bạn chi tiết từng bước để giải quyết vấn đề này, từ cơ bản đến nâng cao.

1. Sử Dụng Công Thức Mảng Động (Dành cho Excel 365/2021 trở lên)

Nếu bạn đang sử dụng phiên bản Excel mới nhất (Microsoft 365 hoặc Excel 2021), Microsoft đã cung cấp một bộ công cụ cực kỳ mạnh mẽ để xử lý văn bản. Chúng ta sẽ kết hợp ba hàm chính: TEXTSPLIT, UNIQUE, và TEXTJOIN.

Nguyên lý hoạt động của công thức

Để loại bỏ giá trị trùng trong một chuỗi văn bản, tư duy logic của chúng ta sẽ đi theo 3 bước:

1. Tách: Cắt chuỗi văn bản thành các phần riêng biệt dựa trên ký tự phân cách (ví dụ: dấu phẩy).
2. Lọc: Loại bỏ các giá trị xuất hiện nhiều hơn một lần trong danh sách vừa tách.
3. Gộp: Nối các giá trị duy nhất còn lại thành một chuỗi văn bản mới.

Dưới đây là ví dụ minh họa với tập dữ liệu chứa Tên vùng, Tên sản phẩm hoặc Tên người bị trùng lặp trong vùng dữ liệu từ A2:A4.

Dữ liệu mẫu Excel chứa các chuỗi văn bản bị trùng lặp cần làm sạch bằng công thức

Công thức chuẩn

Giả sử dữ liệu của bạn nằm ở ô A2, và các giá trị được phân cách bằng dấu phẩy và một khoảng trắng (,). Công thức sẽ như sau:

=TEXTJOIN(", ", TRUE, UNIQUE(TEXTSPLIT(A2, , ", ")))

Phân tích chi tiết từng thành phần:

• TEXTSPLIT(A2, , ", "): Hàm này đóng vai trò “cắt nhỏ” nội dung trong ô A2. Tham số thứ 3 (col_delimiter) được thiết lập là ", " giúp tách chuỗi thành một mảng ngang gồm các phần tử riêng biệt.
• UNIQUE(...): Nhận kết quả mảng từ TEXTSPLIT và loại bỏ các giá trị trùng lặp, chỉ giữ lại các giá trị duy nhất.
• TEXTJOIN(", ", TRUE, ...): Nhận mảng các giá trị duy nhất từ UNIQUE và nối chúng lại thành một chuỗi văn bản hoàn chỉnh. Tham số đầu tiên quy định ký tự nối (ở đây ta dùng lại dấu phẩy và khoảng trắng để đảm bảo thẩm mỹ).

Kết quả sau khi áp dụng công thức TEXTJOIN kết hợp UNIQUE và TEXTSPLIT

Như bạn thấy trong hình trên, các giá trị trùng lặp như “North” hay “Printer” đã được loại bỏ hoàn toàn, trả về một danh sách gọn gàng.

Mở rộng: Sắp xếp kết quả

Đôi khi, việc lọc trùng là chưa đủ, bạn còn muốn danh sách kết quả được sắp xếp theo thứ tự bảng chữ cái để dễ theo dõi. Bạn chỉ cần lồng thêm hàm SORT:

=TEXTJOIN(", ", TRUE, SORT(UNIQUE(TEXTSPLIT(A2, , ", "))))

Việc thêm SORT giúp dữ liệu đầu ra không chỉ sạch mà còn chuyên nghiệp hơn, đặc biệt hữu ích khi làm báo cáo.

2. Xử Lý Dữ Liệu “Bẩn” Và Không Nhất Quán

Trong thực tế, dữ liệu hiếm khi “sạch” ngay từ đầu. Bạn sẽ gặp các trường hợp khoảng trắng thừa lộn xộn hoặc các ký tự phân cách không đồng nhất.

Loại bỏ khoảng trắng thừa với hàm TRIM

Nếu dữ liệu nguồn có các khoảng trắng không đồng đều (ví dụ: “A, B , A” thay vì “A, B, A”), hàm UNIQUE có thể hiểu nhầm “B ” và “B” là hai giá trị khác nhau. Để khắc phục, chúng ta sử dụng hàm TRIM.

Công thức cải tiến:

=TEXTJOIN(", ", TRUE, UNIQUE(TRIM(TEXTSPLIT(A2, , ","))))

Công thức Excel sử dụng hàm TRIM để xử lý khoảng trắng thừa khi lọc trùng

Hàm TRIM sẽ bao bọc TEXTSPLIT, đảm bảo rằng mọi khoảng trắng ở đầu hoặc cuối mỗi phần tử sau khi tách đều được loại bỏ trước khi đưa vào hàm UNIQUE.

Xử lý nhiều ký tự phân cách (Multiple Delimiters)

Đây là một trường hợp khó hơn: Dữ liệu của bạn được nhập từ nhiều nguồn khác nhau, dẫn đến việc trong cùng một ô có chỗ dùng dấu phẩy, chỗ dùng dấu gạch ngang (-), chỗ lại dùng dấu gạch đứng (|) hoặc chấm phẩy (;).

Ví dụ dữ liệu hỗn hợp:

Dữ liệu Excel chứa nhiều loại ký tự phân cách khác nhau như dấu phẩy gạch ngang và gạch đứng

Để giải quyết vấn đề này, hàm TEXTSPLIT cho phép chúng ta khai báo một hằng số mảng (array constant) chứa tất cả các ký tự phân cách có thể xuất hiện.

Công thức xử lý đa ký tự phân cách:

=TEXTJOIN(", ", TRUE, UNIQUE(TRIM(TEXTSPLIT(A2, , {",", "-", "|", ";"}))))

Giải thích:

• {",", "-", "|", ";"}: Cặp ngoặc nhọn {} báo hiệu cho Excel biết đây là một danh sách các điều kiện. Hàm TEXTSPLIT sẽ quét qua chuỗi và cắt tại bất kỳ vị trí nào xuất hiện một trong các ký tự này.
• Kết quả sau đó vẫn đi qua TRIM để làm sạch khoảng trắng và UNIQUE để lọc trùng như bình thường.

Kết quả công thức xử lý nhiều ký tự phân cách trong một ô Excel

Lưu ý quan trọng: Công thức này mặc định không phân biệt chữ hoa chữ thường (case-insensitive) theo nghĩa là nó giữ nguyên định dạng gốc của lần xuất hiện đầu tiên. Tuy nhiên, hàm UNIQUE trong Excel mặc định coi “Apple” và “apple” là khác nhau. Nếu bạn muốn coi chúng là trùng lặp, bạn cần chuẩn hóa dữ liệu về cùng một dạng (ví dụ dùng hàm PROPER hoặc LOWER) bên trong công thức.

3. Sử Dụng VBA (Dành Cho Excel 2010, 2013, 2016, 2019)

Không phải ai cũng có quyền truy cập vào Excel 365. Nếu bạn đang làm việc trên các phiên bản Excel cũ (trước 2021) không hỗ trợ các hàm mảng động, giải pháp tối ưu nhất là sử dụng VBA (Visual Basic for Applications) để tạo một Hàm Tự Tạo (User Defined Function – UDF).

Phương pháp này giúp bạn tạo ra một hàm mới (tương tự như VLOOKUP hay SUM) mà bạn có thể dùng đi dùng lại.

Mã VBA tạo hàm loại bỏ trùng lặp

Dưới đây là đoạn mã VBA chất lượng cao, sử dụng đối tượng Scripting.Dictionary để lọc duy nhất với tốc độ xử lý nhanh.

Function DeDupCells(Cellref As String, Optional Delimiter As String = ", ") As String
    Dim Item As Variant
    ' Tạo đối tượng Dictionary để lưu trữ các giá trị duy nhất
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare ' So sánh không phân biệt hoa thường

        ' Tách chuỗi và duyệt qua từng phần tử
        For Each Item In Split(Cellref, Delimiter)
            Dim cleanItem As String
            cleanItem = Trim(Item) ' Loại bỏ khoảng trắng thừa

            ' Kiểm tra nếu giá trị chưa tồn tại trong Dictionary thì thêm vào
            If cleanItem <> "" And Not .exists(cleanItem) Then
                .Add cleanItem, Nothing
            End If
        Next

        ' Nếu có dữ liệu, nối lại thành chuỗi kết quả
        If .Count > 0 Then
            DeDupCells = Join(.keys, Delimiter)
        End If
    End With
End Function

Hướng dẫn cài đặt mã VBA

Để sử dụng đoạn mã trên, bạn cần thực hiện các bước sau:

1. Mở cửa sổ soạn thảo VBA bằng cách nhấn tổ hợp phím Alt + F11. Hoặc vào thẻ Developer > chọn Visual Basic.
2. Trong cửa sổ VBA, chọn menu Insert > Module. Một trang trắng sẽ hiện ra.

Thao tác chèn Module mới trong cửa sổ VBA Editor

3. Sao chép và dán toàn bộ đoạn mã trên vào cửa sổ Module vừa tạo.

Dán mã VBA vào Module để tạo hàm tùy chỉnh DeDupCells

4. Đóng cửa sổ VBA và quay trở lại bảng tính Excel.

Cách sử dụng hàm DeDupCells trong Excel

Sau khi dán mã, bạn có thể sử dụng hàm =DeDupCells() giống như bất kỳ hàm Excel nào khác.

Cú pháp: =DeDupCells(Ô_chứa_dữ_liệu, [Ký_tự_phân_cách])

• Trường hợp 1: Dùng ký tự phân cách mặc định (dấu phẩy + khoảng trắng)
  Bạn chỉ cần nhập: =DeDupCells(A2)

Sử dụng hàm tự tạo DeDupCells trong bảng tính Excel

• Trường hợp 2: Dùng ký tự phân cách tùy chỉnh
  Ví dụ dữ liệu của bạn ngăn cách bởi dấu gạch đứng |, bạn nhập:
  =DeDupCells(A2, "|")

Sử dụng hàm DeDupCells với ký tự phân cách tùy chỉnh là dấu gạch đứng

Lưu ý khi lưu file: Vì file Excel của bạn hiện có chứa mã VBA (Macro), bạn bắt buộc phải lưu file dưới định dạng Excel Macro-Enabled Workbook (.xlsm). Nếu lưu dưới dạng .xlsx thông thường, toàn bộ mã VBA sẽ bị xóa.

4. Giải Pháp Power Query: Chuẩn Hóa Dữ Liệu Quy Mô Lớn

Ngoài công thức và VBA, Power Query là một công cụ mạnh mẽ khác để xử lý vấn đề này, đặc biệt khi bạn cần làm sạch hàng ngàn dòng dữ liệu một cách tự động và có quy trình. Mặc dù bài viết gốc không đề cập sâu, nhưng đây là phương pháp được các chuyên gia phân tích dữ liệu ưu tiên vì tính ổn định và khả năng tái sử dụng mà không cần viết code phức tạp.

Quy trình cơ bản với Power Query:

1. Chọn vùng dữ liệu > Thẻ Data > From Table/Range.
2. Trong Power Query Editor: Chọn cột cần xử lý > Thẻ Home > Split Column > By Delimiter > Chọn Rows (Tách xuống dòng) trong phần Advanced Options. Bước này cực kỳ quan trọng, nó biến 1 ô chứa nhiều giá trị thành nhiều dòng.
3. Chọn cột vừa tách > Remove Rows > Remove Duplicates.
4. Để gộp lại: Sử dụng tính năng Group By, và trong phần thao tác, sử dụng hàm Text.Combine để nối các giá trị lại.

Phương pháp này tuy nhiều bước hơn công thức nhưng cực kỳ hiệu quả khi bạn muốn kết hợp việc xóa trùng với các bước làm sạch dữ liệu khác (như viết hoa chữ cái đầu, xóa dấu tiếng Việt, v.v.).

So Sánh Các Phương Pháp

Để giúp bạn chọn giải pháp phù hợp nhất, dưới đây là bảng so sánh tóm tắt:

Các Câu Hỏi Thường Gặp (FAQ)

1. Hàm TEXTSPLIT báo lỗi #NAME? nghĩa là gì?

Lỗi #NAME? xuất hiện khi phiên bản Excel của bạn không hỗ trợ hàm này. TEXTSPLIT, UNIQUE, TEXTJOIN chỉ có trên Microsoft 365 và Excel 2021 trở lên. Nếu gặp lỗi này, bạn hãy chuyển sang dùng phương pháp VBA được hướng dẫn ở mục 3.

2. Làm thế nào để phân biệt chữ hoa và thường khi xóa trùng?

Mặc định, hàm UNIQUE phân biệt chữ hoa/thường (ví dụ: “Excel” khác “excel”). Nếu bạn muốn coi chúng là giống nhau để xóa triệt để, hãy dùng hàm LOWER hoặc UPPER bọc lấy dữ liệu trước khi đưa vào UNIQUE.
Ví dụ: =TEXTJOIN(", ", TRUE, UNIQUE(LOWER(TEXTSPLIT(A2,,", ")))). Lưu ý là kết quả trả về sẽ là chữ thường toàn bộ.

3. Tôi có thể xóa trùng và đếm số lượng giá trị duy nhất không?

Hoàn toàn được. Thay vì dùng TEXTJOIN để nối lại, bạn hãy dùng hàm COUNTA bao quanh hàm UNIQUE.
Ví dụ: =COUNTA(UNIQUE(TEXTSPLIT(A2,,", "))). Kết quả sẽ trả về con số đếm các giá trị không trùng lặp.

4. Tại sao file chứa VBA của tôi bị cảnh báo bảo mật?

Đây là cơ chế bảo vệ của Microsoft. Các file chứa Macro (.xlsm) có thể tiềm ẩn rủi ro. Để sử dụng hàm tự tạo, bạn cần nhấn “Enable Content” trên thanh cảnh báo màu vàng khi mở file, hoặc thêm thư mục chứa file vào vùng tin cậy (Trusted Locations) trong cài đặt Excel.

Kết Luận

Việc xử lý dữ liệu trùng lặp trong một ô Excel không còn là “ác mộng” nhờ vào sự cải tiến liên tục của Microsoft. Với người dùng Excel 365, tổ hợp hàm TEXTJOIN, UNIQUE, và TEXTSPLIT mang lại giải pháp nhanh chóng và thanh lịch. Đối với những phiên bản cũ hơn, VBA vẫn là một người trợ lý đắc lực giúp bạn giải quyết vấn đề một cách triệt để.

Việc nắm vững các kỹ thuật này không chỉ giúp bảng tính của bạn gọn gàng, chính xác hơn mà còn nâng cao đáng kể hiệu suất làm việc khi xử lý các tập dữ liệu thô. Hãy lựa chọn phương pháp phù hợp nhất với phiên bản phần mềm và nhu cầu cụ thể của bạn.

Nguồn Tham Khảo

1. Microsoft Support. “TEXTSPLIT function”. Microsoft.com.
2. Microsoft Support. “UNIQUE function”. Microsoft.com.
3. Microsoft Support. “TEXTJOIN function”. Microsoft.com.
4. TrumpExcel. “Find and Remove Duplicates in Excel”. Trumpexcel.com.