Có thể bạn quan tâm: Cách Đổi Inch Sang Cm Trong Excel 2026
Giới thiệu
Trong thế giới dữ liệu ngày nay, việc quản lý và phân tích dữ liệu là một phần không thể thiếu của mọi tổ chức, doanh nghiệp, và cá nhân. Khi dữ liệu được nhập vào bảng tính Excel, một trong những thách thức lớn nhất thường gặp phải là việc trùng lặp dữ liệu. Trùng lặp không chỉ làm giảm tính chính xác của các báo cáo, mà còn làm tăng chi phí lưu trữ transforme và làm cho việc phân tích dữ liệu trở nên khó khăn hơn. Vì vậy, việc phát hiện và xử lý trùng lặp trở thành một kỹ năng quan trọng đối với bất kỳ ai làm việc với Excel.
Trong bài viết này, chúng ta sẽ khám phá chi tiết các công thức và phương pháp138 phát hiện trùng lặp trong Excel, từ những công thức cơ bản nhất như COUNTIF và COUNTIFS cho tới các hàm dynamic array như UNIQUE, FILTER, SORT, và cách kết hợp chúng để tạo ra giải pháp toàn diện. Ngoài ra, chúng ta cũng sẽ tìm hiểu cách sử dụng Conditional Formatting để trực quan hóa dữ liệu trùng lặp, cùng với một số mẹo và lưu ý khi làm việc với dữ liệu lớn và phức tạp.
Có thể bạn quan tâm: Cách Để Chữ Ở Giữa Ô Trong Excel: Hướng Dẫn Chi Tiết 2026
1. Tại sao việc phát hiện trùng lặp lại quan trọng?
- Đảm bảo tính toàn vẹn dữ liệu – Trùng lặp có thể dẫn đến sai lệch trong tính toán, thống kê, và báo cáo. Ví dụ, nếu bạn tính tổng doanh thu từ một danh sách đơn hàng, các đơn hàng trùng lặp sẽ làm tăng sai số.
- Tiết kiệm không gian lưu trữ – Dữ liệu trùng lặp chiếm dung lượng không cần thiết, đặc biệt khi làm việc với các bảng tính lớn.
- Cải thiện hiệu suất – Khi dữ liệu sạch sẽ và không có trùng lặp, các hàm tính toán sẽ chạy nhanh hơn, đặc biệt là khi sử dụng các công thức phức tạp.
- Hỗ trợ quyết định chính xác hơn – Dữ liệu không trùng lặp giúp bạn đưa ra quyết định dựa trên thông tin chính xác, tránh những sai lầm do dữ liệu bị lặp.
Có thể bạn quan tâm: Cách Đặt Mật Khẩu Cho Sheet Trong Excel: Hướng Dẫn Chi Tiết Và Các Mẹo Bảo Mật
2. Các công cụ đơn giản nhất trong Excel để phát hiện trùng lặp
2.1 Conditional Formatting
- Bước 1: Chọn dải ô cần kiểm tra trùng lặp.
- Bước 2: Vào tab Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Bước 3: Chọn màu sắc hiển thị và nhấn OK.
Khi hoàn thành, Excel sẽ tô màu cho tất cả các ô có giá trị trùng lặp. Đây là cách nhanh nhất để trực quan نتي.
2.2 Công thức COUNTIF
Công thức COUNTIF cho phép bạn đếm số lần xuất hiện của một giá trị nhất định trong một phạm vi. Khi kết hợp với IF, bạn có thể xác định xem ô nào có giá trị trùng lặp.
Công thức mẫu:
=IF(COUNTIF($A$1:$A$100, A1) > 1, "Trùng lặp", "")
$A$1:$A$100là phạm vi dữ liệu.A1là ô hiện tại.- Nếu số lần xuất hiện lớn hơn 1, thì hiển thị “Trùng lặp”.
Bạn có thể copy công thức này sang các ô khác trong cột để đánh dấu trùng lặp.
2.3 Công thức COUNTIFS
Khi cần kiểm tra trùng lặp dựa trên nhiều tiêu chí (ví dụ: tên và ngày), COUNTIFS là công cụ phù hợp.
Công thức mẫu:
=IF(COUNTIFS($A$1:$A$100, A1, $B$1:$B$100, B1) > 1, "Trùng lặp", "")
- Dòng này sẽ kiểm tra trùng lặp dựa trên cột A và B cùng lúc.
3. Sử dụng Dynamic Array Functions cho Excel 365/Excel 2026 trở lên
3.1 Hàm UNIQUE
UNIQUE trả về một mảng các giá trị duy nhất từ một dải ô. Khi bạn muốn loại bỏ trùng lặp và chỉ giữ lại các giá trị duy nhất, công thức rất hữu ích.
Công thức mẫu:
=UNIQUE(A1:A100)
- Kết quả trả về là một mảng động chứa các giá trị duy nhất.
- Bạn có thể sử dụng
SORThoặcFILTERkết hợp vớiUNIQUEđể sắp xếp hoặc lọc thêm.
3.2 Hàm FILTER
FILTER cho phép lọc dữ liệu dựa trên điều kiện cụ thể. Khi kết hợp với UNIQUE, bạn có thể lọc dữ liệu trùng lặp một cách linh hoạt.
Công thức mẫu:
=FILTER(A1:A100, COUNTIF(A зроб A1:A100, A1:A100)=1)
- Công thức này lọc ra các giá trị chỉ xuất hiện một lần.
3.3 Hàm SORT
Khi bạn đã có một dải dữ liệu bất kể trùng lặp, SORT giúp sắp xếp dữ liệu một cách nhanh chóng.
Công thức mẫu:
=SORT(UNIQUE(A1:A100))
- Kết quả là danh sách duy nhất đã được sắp xếp.
3.4 Kết hợp UNIQUE, SORT, FILTER
Bạn có thể kết hợp ba hàm này để tạo ra một giải pháp toàn diện:
=FILTER(SORT(UNIQUE(A1:A100)), COUNTIF(A1:A100, UNIQUE(A1:A100)) > 1)
- Công thức trên trả về danh sách các giá trị trùng lặp, đã được sắp xếp.
4. Phân tích chi tiết từng công thức
4.1 COUNTIF
Có thể bạn quan tâm: Công Thức Bỏ Dấu Gạch Ngang Trong Excel: Hướng Dẫn Chi Tiết Và Ứng Dụng Thực Tiễn
- Cú pháp:
COUNTIF(range, criteria) - Ví dụ: Nếu bạn có danh sách tên trong cột A và muốn đếm số lần mỗi tên xuất hiện:
=COUNTIF($A$1:$A$100, A1)
- Kết quả: Nếu tên “Alice” xuất hiện 3 lần, công thức trả về 3.
4.2 COUNTIFS
- Cú pháp:
COUNTIFS(range1, criteria1, range2, criteria2, ...) - Ví dụ: Kiểm tra trùng lặp dựa trên tên và ngày:
=COUNTIFS($A$1:$A$100, A1, $B$1:$B$100, B1)
- Kết quả: Trả về số lần xuất hiện của cặp tên và ngày cụ thể.
4.3 UNIQUEట్ట
- Cú pháp:
UNIQUE(array, by_col, exactly_once) - Ví dụ: Lấy danh sách duy nhất từ cột A:
=UNIQUE(A1:A100)
- Tham số:
by_col(mặc định FALSE) – Xác định xem dữ liệu được xử lý theo cột hay hàng.exactly_once(mặc định FALSE) – Nếu TRUE, chỉ trả về những giá trị xuất hiện một lần duy nhất.
4.4 FILTER
- Cú pháp:
FILTER(array, include, if_empty) - Ví dụ: Lọc các giá trị trùng lặp:
=FILTER(A1:A100, COUNTIF(A1:A100, A1:A100) > 1)
4.5 SORT
- Cú pháp:
SORT(array, sort_index, sort_order, by_col) - Ví dụ: Sắp xếp danh sách duy nhất:
=SORT(UNIQUE(A1:A100))
5. Kỹ thuật nâng cao: Xử lý trùng lặp với Helper Columns
Khi dữ liệu phức tạp và cần thực hiện nhiều điều kiện, việc sử dụng một cột trợ giúp (helper column) là một cách tiếp cận hiệu quả. Dưới đây là một ví dụ:
5.1 Tạo cột trợ giúp
Giả sử bạn có dữ liệu trong cột A (Tên) và B (Ngày sinh). Bạn muốn kiểm tra trùng lặp dựa trên cả hai cột.
Cột C जुट: Tạo một chuỗi kết hợp tên và ngày:
excel =A2 & "|" & TEXT(B2, "yyyy-mm-dd")Cột D: Đếm số lần xuất hiện của chuỗi kết hợp:
excel =COUNTIF($C$2:$C$100, C2)Cột E: Kiểm tra trùng lặp:
excel =IF(D2>1, "Trùng lặp", "")
Khi áp dụng, bạn sẽ có một cột E hiển thị “Trùng lặp” cho các dòng có dữ liệu trùng lặp.
6. Sử dụng Conditional Formatting với công thức tùy chỉnh
Nếu bạn muốn làm cho Conditional Formatting hiển thị trùng lặp theo một cách tùy chỉnh (ví dụ: chỉ hiển thị trùng lặp nếu xuất hiện nhiều hơn 3 lần), bạn có thể làm như sau:
- Bước 1: Chọn dải ô cần áp dụng.
- Bước 2: Vào Home > Conditional Formatting > New Rule.
- Bước 3: Chọn Use a formula to determine which cells to format.
- Bước 4: Nhập công thức:
excel =COUNTIF($A$1:$A$100, A1) > 3 - Bước 5: Đặt kiểu định dạng (định dạng màu, font, …) và nhấn OK.
7. Xử lý dữ liệu lớn: Tối ưu hóa công thức
Khi làm việc với dữ liệu hàng triệu dòng, các công thức như COUNTIF có thể làm chậm tính toán. Dưới đây là một số mẹo:
- Sử dụng bảng (Table): Khi dữ liệu được định dạng thành một bảng, Excel tự động quản lý phạm vi dữ liệu. Bạn có thể dùng tên cột trong công thức, ví dụ
=COUNTIFS(Table1Name, @Name, Table1Date, @Date). - Sử dụng công thức mảng động: Các hàm dynamic array như
UNIQUEvàFILTERđược thiết kế để xử lý dữ liệu lớn nhanh hơn. - Sử dụng tính năng Remove Duplicate: Nếu bạn chỉ cần loại bỏ trùng lặp mà không cần phân tích sâu, chức năng Data > Remove Duplicates là nhanh và hiệu quả.
- Sử dụng VBA: Nếu công thức vẫn chậm, viết một macro VBA để quét dữ liệu và đánh dấu trùng lặp có thể giúp tăng tốc độ.
8. VBA để phát hiện trùng lặp FAR
Dưới đây là một đoạn mã VBA đơn giản để tìm trùng lặp trong một cột:
Sub HighlightDuplicates() Dim rng As Range Dim cell As Range Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") ' Giả sử dữ liệu nằm trong cột A từ A2 đến A100 Set rng = ThisWorkbook.Sheets("Sheet1").Range("A2:A100") For Each cell In rng If Not IsEmpty(cell) Then If dict.exists(cell.Value) Then cell.Interior.Color = vbYellow dict(cell.Value مسیر) = dict(cell.Value) + 1 Else dict.Add cell.Value, 1 End If End If Next cell
End Sub
Chạy macro này sẽ tô màu vàng tất cả các ô trùng lặp trong cột A.
9. Các lưu ý khi làm việc với dữ liệu trùng lặp
- Kiểm tra dữ liệu trước khi nhập: Sử dụng công cụ
Data Validationđể tránh nhập dữ liệu trùng lặp ngay từ đầu. - Dùng chuẩn hóa dữ liệu: Đảm bảo dữ liệu có định dạng chuẩn (ví dụ: ngày tháng, chữ hoa/thường) để tránh trùng lặp do sai lệch định dạng.
- Kiểm tra dữ liệu nhập từ nguồn khác: Khi nhập dữ liệu từ CSV, XML, hoặc dữ liệu từ hệ thống khác, hãy kiểm tra kỹ lưỡng.
- Sử dụng cột ID duy nhất: Thêm một cột ID duy nhất (GUID hoặc ID tự động) để dễ dàng xác định và xử lý trùng lặp.
- Xác định mục tiêu: Đôi khi trùng lặp có thể chấp nhận được (ví dụ: một khách hàng mua nhiều lần). Xác định rõ ràng mục tiêu của bạn trước khi loại bỏ trùng lặp.
10. Tổng kết
Phát hiện trùng lặp trong Excel không chỉ là một kỹ thuật đơn giản mà còn là một phần quan trọng của quản lý dữ liệu. Bằng cách sử dụng những công thức cơ bản như COUNTIF, COUNTIFS và các công cụ mạnh mẽ hơn như UNIQUE, FILTER, SORT, bạn có thể nhanh chóng kiểm tra và xử lý dữ liệu trùng lặp một cách hiệu quả.
Ngoài ra, việc kết hợp Conditional Formatting, helper columns, và thậm chí là VBA mở rộng khả năng xử lý dữ liệu của bạn, đặc biệt khi làm việc với các bảng lớn hoặc dữ liệu phức tạp. Cuối cùng, nhớ rằng việc quản lý dữ liệu sạch sẽ và chính xác là chìa khóa để đưa ra những quyết định kinh doanh đúng đắn và hiệu quả.
Hy vọng bài viết này đã giúp bạn nắm vững cách phát hiện và xử lý trùng lặp trong Excel. Chúc bạn thành công trong việc làm sạch dữ liệu và nâng cao chất lượng báo cáo của mình!
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ẻ.