Có thể bạn quan tâm: Tính Tổng Từ Ngày Đến Ngày Trong Excel: Hướng Dẫn Chi Tiết
Giới thiệu
Trong công việc quản lý dữ liệu, kiểm tra và xử lý các giá trị trùng lặp luôn là một nhiệm vụ quan trọng. Dữ liệu trùng lặp có thể gây ra nhiều rắc rối: làm sai lệch báo cáo, lãng phí bộ nhớ lưu trữ, hoặc làm mất tính toàn vẹn khi thực hiện phân tích. Vì vậy, kỹ năng tìm và đánh dấu các ô trùng lặp trong Excel là một công cụ thiết yếu cho những người làm việc với bảng tính. Mặc dù Excel đã tích hợp sẵn một số tính năng để tìm trùng, nhưng cách tô màu ô trùng lặp trực quan nhất vẫn được ưa chuộng vì nó giúp nhanh chóng nhận diện sai sót ngay tại giao diện. Bài viết này sẽ hướng dẫn chi tiết cách tô màu ô trùng lặp trong Excel bằng nhiều phương pháp khác nhau, từ công cụ có sẵn tới công thức nâng cao, macro và thậm chí là VBA. Bạn sẽ có thể chọn phương án phù hợp nhất với nhu cầu và môi trường làm việc của mình.
Chú ý technic: Để đạt hiệu quả cao nhất, hãy cân nhắc kích thước dữ liệu (số hàng, cột) và các tính năng bổ trợ như “Conditional Formatting” (định dạng có điều kiện) hay “Advanced Filter” (lọc nâng cao). Các phương pháp được đề cập sẽ được minh hoạ bằng ví dụ thực tế để bạn có thể áp dụng ngay.
Có thể bạn quan tâm: Công Thức Tính Tổng Của Tích 2 Cột Trong Excel
1. Sử dụng Conditional Formatting (Định dạng có điều kiện)
1.1. Các bước cơ bản
- Chọn vùng dữ liệu: Đầu tiên, bạn cần xác định vùng dữ liệu mà bạn muốn kiểm tra trùng lặp, ví dụ: cột A từ A2 đến A1000.
- Mở Conditional Formatting: Vào tab Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values….
- Chọn màu sắc: Trong hộp thoại, chọn màu bạn muốn dùng để tô các ô trùng lặp. Bạn có thể chọn màu mặc định hoặc tạo màu tuỳ chỉnh.
- Xác nhận: Nhấn OK. Excel sẽ tự động tô màu các giá trị xuất hiện nhiều hơn một lần trong vùng đã chọn.
1.2. Đặt điều kiện nâng cao: Tô màu theo tiêu chí tùy chỉnh
Nếu bạn muốn tô màu các ô trùng lặp dựa trên một điều kiện cụ thể (ví dụ: trùng lặp trong một cột nhưng chỉ khi giá trị trong cột khác có độ lớn nhất), bạn có thể sử dụng công thức tùy chỉnh trong Conditional Formatting.
- Chọn vùng dữ liệu (giả sử A2:A1000).
- Mở Conditional Formatting → New Rule… → Use a formula to determine which cells to format. مرب
- Nhập công thức:
=COUNTIF($A$2:$A$1000,$A2)>1
Công thức này sẽ trả vềTRUEnếu giá trị ở ô hiện tại xuất hiện nhiều hơn một lần trong vùng. - Đặt màu tô mong muốn và nhấn OK.
Lưu ý: Khi sử dụng công thức tùy chỉnh, luôn đặt dấu $ đúng chỗ để tránh lỗi khi áp dụng cho toàn bộ vùng.
1.3. Tô màu trùng lặp trong nhiều cột
Để tô màu ô trùng lặp trong một bảng rộng hơn, ví dụ: cột A, B và C, bạn có thể áp dụng Conditional Formatting trên toàn bộ vùng A2:C1000 với công thức:
=COUNTIF($A$2:$C$1000, A2)>1
Điều này sẽ tô màu mọi ô có giá trị trùng lặp trong vùng toàn bộ, bất kể cột nào. Nếu bạn muốn phân biệt trùng lặp trong từng cột riêng lẻ, hãy tạo quy tắc riêng cho từng cột.
1.4. Kiểm tra trùng lặp 2 chiều: Giá trị trùng nhau với cùng một điều kiện
Ví dụ: Bạn muốn tìm các dòng có cùng giá trị trong cột A và B, nhưng chỉ khi cột C có giá trị lớn hơn 50.
- Chọn vùng dữ liệu (A2:C1000).
- Mở Conditional Formatting → New Rule → Use a formula.
- Công thức:
=AND(COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2)>1,$C2>50) - Đặt màu tô và xác nhận.
Công thức này đếm số lần xuất hiện của cặp giá trị A và B, và chỉ áp dụngimba khi cột C > 50.
Có thể bạn quan tâm: Tính Tổng Các Ô Xen Kẽ Trong Excel
2. Sử dụng Advanced Filter (Lọc nâng cao)
2.1. Phương pháp lọc dữ liệu trùng lặp
Đôi khi bạn muốn loại bỏ trùng lặp thay vì chỉ tô màu. Advanced Filter cung cấp một cách nhanh chóng để lọc dữ liệu duy nhất hoặc trùng lặp.
- Sao chép dữ liệu: Lưu ý rằng Advanced Filter không thay đổi dữ liệu gốc, vì vậy bạn có thể sao chép vùng dữ liệu vào một vùng mới.
- Vào tab Data → Advanced (có thể trong group Sort & Filter).
- Chọn Copy to another location, nhập List range (ví dụ:
$A$2:$A$1000), chọn Copy to (ví dụ:$B$1). - Chọn Unique records only. Excel sẽ sao chép các giá trị duy nhất vào vùng mới.
2.2. Lọc trùng lặp
Nếu bạn muốn lọc và hiển thị chỉ những giá trị trùng lặp:
- Ở ô Criteria range, nhập tiêu đề cột (ví dụ:
A). - લાખ
- Trong ô bên dưới tiêu đề, nhập công thức:
=COUNTIF($A$2:$A$1000,A2)>1
hoặc bạn có thể nhập=A2và sử dụng Copy to column để lọc tạm thời. - Làm theo các bước trong Advanced Filter để sao chép kết quả.
Mặc dù Advanced Filter không tô màu, nhưng kết quả lọc giúp bạn dễ dàng імвидо kiểm tra và sau đó áp dụng Conditional Formatting nếu cần.
3. Sử dụng công thức và hàm COUNTIF, COUNTIFS
3.1. Tạo cột phụ để đánh dấu trùng lặp
Bạn có thể tạo một cột phụ (ví dụ: cột D) để ghi nhận trạng thái trùng lặp:
=IF(COUNTIF($A$2:$A$1000, A2)>1, "Duplicate", "Unique")
Khi có nhiều cột tham chiếu, sử dụng COUNTIFS:
=IF(COUNTIFS($A$2:$A$1000, A2, $B$2:$B$1000, B2)>1, "Duplicate", "Unique")
Sau đó, áp dụng Conditional Formatting dựa trên nội dung của cột D. Ví dụ: chọn vùngனா A2:C1000, tạo quy ming:
=$D2="Duplicate"
đặt màu tô.
3.2. Tổ chức dữ liệu với hàm FILTER (Excel 365)
Nếu bạn làm việc với Office 365 hoặc Excel 2026, hàm FILTER cho phép bạn trích filt dữ liệu trùng lặp một cách động:
Có thể bạn quan tâm: Tính Điểm Trung Bình Cao Nhất Trong Excel: Hướng Dẫn Chi Tiết Và Các Công Cụ Hiệu Quả
=FILTER(A2:A1000, COUNTIF(A2:A1000, A2:A1000)>1)
Công thức này trả về một mảng các giá trị trùng lặp. Bạn có thể áp dụng Conditional Formatting hoặc biến đổi dữ liệu ngay tức thì.
4. VBA: Macro để tô màu ô trùng lặp
4.1. Macro đơn giản: Tô màu đỏ fossil
Sub HighlightDuplicates() Dim rng As Range Dim cell As Range Dim dict As Object ' Đặt vùng cần kiểm tra Set rng = Range("A2:A1000") ' Tạo dictionary để lưu trbr Set dict = CreateObject("Scripting.Dictionary") For Each cell In rng If Not IsEmpty(cell.Value) Then If dict.Exists(cell.Value) Then ' Nếu đã tồn tại, tô màu đỏ cell.Interior.Color = vbRed dict(cell.Value).Interior.Color = vbRed Else dict.Add cell.Value, cell End If End If Next cell
End Sub
Macro này duyệt qua từng ô trong vùng A2:A1000. Khi phát hiện một giá trị đã tồn tại trong Dictionary, cả ô hiện tại và ô đã lưu trong dictionary sẽ được tô màu đỏ. Bạn có thể thay đổi vbRed thành màu khác, hoặc sử dụng các hàm RGB() để tùy chỉnh.
4.2. Macro linh hoạt: Tô màu tùy vị trí và nhiều cột
Sub HighlightDupMultiColumns() Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim key As String Dim dict As Object Set ws = ActiveSheet Set rng = ws.Range("A2:C1000") ' Chỉnh vùng Set dict = CreateObject("Scripting.Dictionary") For Each cell In rng If Not IsEmpty(cell.Value) Then ' Tạo key dựa vào giá trị ô key = CStr(cell.Value) If dict.Exists(key) Then cell.Interior.Color = vbYellow dict(key).Interior.Color = vbYellow Else dict.Add key, cell End If End If Next cell
End Sub
Macro này áp dụng cho nhiều cột và tô màu vàng cho các ô trùng lặp. Bạn có thể mở rộng logic để tạo key phức tạp hơn (bằng cách ghép nhiều cột) nếu cần.
4.3. Tự động chạy macro khi dữ liệu thay đổi
Bạn có thể đặt macro vào sự kiện Worksheet_Change để tự động áp dụng định dạng khi người dùng nhập dữ liệu:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:A1000")) Is Nothing Then Call HighlightDuplicates End If
End Sub
Bản thân macro sẽ được kích hoạt mỗi khi có sự thay đổi trong vùng đã định.
5. Kỹ thuật nâng cao: Sử dụng Conditional Formatting với hàm UNIQUE và FILTER
5.1. Trích trùng lặp với hàm UNIQUE
Bạn có thể dùng hàm UNIQUE để tách dữ liệu duy nhất và COUNTIF để xác định trùng lặp sau đó tô màu.
Ví dụ:
- Dùng
UNIQUE(A2:A1000)để lấy danh sách duy nhất. - Dùng
COUNTIF(A2:A1000, A2:A1000)để đếm số lần xuất hiện.
Ta có thể áp gê:
=IF(COUNTIF($A$2:$A$1000, A2)>1, "Dup", "Uni")
Sau đó, Conditional Formatting sẽ tô màu cho cột Dup.
5.2. Tô màu theo điều kiện phức tạp: Trùng lặp trong một cột nhưng chỉ khi giá trị trong cột khác vượt ngưỡng
Giả sử bạn có cột A (ID), B (Loại), C (Số lượng). Bạn muốn tô màu ID trùng khi số lượng > 100.
- Conditional Formatting → New Rule → Use a formula.
- Công thức:
=AND(COUNTIF($A$2:$A$1000, $A2)>1, $ cấp2 > 100)
Thí nghiệm này cho phép bạn áp dụng logic tinh vi, phù hợp với các báo cáo kinh doanh.
6. Thực hành: Ví dụ thực tế
6.1. Kiểm tra trùng lặp trong danh sách khách hàng
- Dữ liệu: Cột A – Tên khách hàng, B – Email, C – Số điện thoại.
- Mục tiêu: Tô màu đỏ các khách hàng có email trùng lặp.
Bước:
1. Chọn vùng A2:C1000.
2. Conditional Formatting → New Rule → Use a formula155.
3. Công thức:=COUNTIF($B$2:$B$1000, $B2)>1
4. Đặt màu đỏ và áp dụng.
6.2. Đánh dấu trùng lặp dữ liệu bán hàng
- Dữ liệu: Cột A – Mã sản phẩm, B – Ngày bán, C – Số lượng bán.
- Mục tiêu: Tô màu xanh lá cây các dòng có cùng mã sản phẩm và ngày bán trùng lặp.
Bước:
1. Chọn vùng A2:C1000.
2. Conditional Formatting → New Rule → Use a formula.
3 اساس:=COUNTIFS($A$2:$A$1000, $A2, $B$2:$B$1000, $B2)>1
4. Đặt màu xanh lá và áp dụng.
7. Lưu ý khi tô màu ô trùng lặp
- Hiệu suất: Khiöhỏ dữ liệu lớn (trên 100k dòng), Conditional Formatting có thể làm chậm Excel. Phải cân nhắc chuyển sang macro hoặc hàm tùy chỉnh.
- Cập nhật dữ liệu: Nếu dữ liệu được cập nhật thường xuyên, hãy đảm bảo Conditional Formatting được áp dụng cho toàn bộ vùng mới.
- Định dạng trùng lặp đa chiều: Đôi khi bạn cần tô màu trùng lặp dựa trên nhiều cột. Khi đó, hãy dùng công thức trong Conditional Formatting mà không chỉ dựa vào một cột.
- Xóa định dạng: Khi xóa dữ liệu, hãy nhớ xóa hoặc cập nhật quy tắc Conditional Formatting, tránh trường hợp ô vẫn giữ màu mặc dù dữ liệu đã thay đổi.
- Bảo mật: Macro có thể bị chặn bởi cài đặt bảo mật. Đảm bảo rằng người》。
8. So sánh các phương pháp
| Phương pháp | Độ phức tạp | Khả năng mở rộng | Tốc độ | Độ tin cậy | Độ linh hoạt |
|---|---|---|---|---|---|
| Conditional Formatting | Thấp | Trung bình | Nhanh | Cao | Trung bình |
| Advanced Filter | Trung bình | Thấp | Nhanh | Trung bình | Thấp |
| Công thức + COUNTIF | Thấp | Trung bình | Nhanh | Cao | Cao |
| VBA Macro | Cao | Rất cao | Nhìn chung nhanh | Rất cao | Rất cao |
| Hàm UNIQUE & FILTER | Trung bình | Cao | Nhanh | Cao | Rất cao |
- Nếu bạn chỉ cần tô màu nhanh, Conditional Formatting là lựa chọn dễ nhất.
- Nếu cần thao tác dữ liệu tiếp theo (xóa trùng, lọc), Advanced Filter có thể hữu ích.
- Khi cần tính linh hoạt cao (định dạng tùy theo nhiều điều kiện), công thức hoặc VBA là lựa chọn tốt.
- Với Office 365, hàm UNIQUE & FILTER cung cấp khả năng động mà không cần macro.
9. Bảo trì và tối ưu
- Định kỳ kiểm tra: Kiểm tra lại các quy tắc Conditional Formatting khi mở tệp lớn.
- Sử dụng Named Ranges: Đặt tên vùng dữ liệu để công thức trở nên rõ ràng và tránh lỗi khi vùng thay đổi.
- Tối ưu hóa VBA: Khi viết macro, sử dụng
Application.ScreenUpdating = False/Trueđể giảm thời gian thực thi. - Sử dụng
Range.Find: Đối với dữ liệu cực lớn,Findnhanh hơnFor Eachtrong vòng lặp.
10. Kết luận
Tô màu ô trùng lặp trong Excel là một kỹ thuật cốt lõi giúp người dùng nhanh chóng nhận diện và khắc phục các lỗi dữ liệu. Bằng cách kết hợp các phương pháp Conditional Formatting, công thức nâng cao, Advanced Filter và macro VBA, bạn có thể tùy chỉnh theo nhu cầu cụ thể của từng dự án.
- Khi làm việc với dữ liệu nhỏ và cần thao tác nhanh, Conditional Formatting đủ khả năng.
- Đối với dữ liệu lớn hoặc khi cần xử lý phức tạp, macro VBA hay hàm UNIQUE & FILTER sẽ mang lại hiệu quả cao hơn.
- Luôn nhớ kiểm tra và điều chỉnh quy tắc khi dữ liệu thay đổi để tránh sai sót.
Hy vọng bài viết này đã cung cấp cho bạn một cái nhìn toàn diện và chi tiết về cách tô màu ô trùng lặp trong Excel. Hãy thử áp dụng những kỹ thuật trên vào công việc hàng ngày của bạn và cảm nhận sự khác biệt trong quy trình xử lý dữ liệu. Nếu có thắc mắc hoặc muốn chia sẻ kinh nghiệm, đừng ngần ngại để lại bình luận hoặc liên hệ trực tiếp. Chúc bạn thành công với Excel!
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ẻ.