Cách Tô Màu Dữ Liệu Trùng 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 hầu hết các doanh nghiệp và cá nhân khi xử lý dữ liệu. Một trong những nhu cầu phổ biến nhất khi làm việc với bảng tính là phát hiện và đánh dấu những giá trị trùng lặp. Việc tô màu dữ liệu trùng giúp người dùng nhanh chóng nhận diện những mục cần kiểm tra, loại bỏ hoặc hợp nhất, đồng thời giảm thiểu lỗi nhập liệu và tăng hiệu quả phân tích. 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, từ các phương pháp cơ bản đến các kỹ thuật nâng cao, giúp bạn tô màu dữ liệu trùng trong Excel một cách nhanh chóng và chính xác.

Có thể bạn quan tâm: Cách Làm Bảng Chấm Công Bằng Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Mục Lục

1. Tại sao cần tô màu dữ liệu trùng?
2. Chuẩn bị dữ liệu trước khi thực hiện
3. Cách tô màu dữ liệu trùng bằng Conditional Formatting (định dạng có điều kiện)#
4. 3.1. Sử dụng công thức COUNTIF
5. 3.2. Sử dụng công thức COUNTIFS cho nhiều tiêu chí
6. 3.3. Định dạng dựa trên giá trị duy nhất (Unique)
7. Sử dụng tính năng “Remove Duplicates” để xác định và tô màu
8. Sử dụng Power Query để phát hiện trùng lặp và tô màu tự động
9. Vòng lặp VBA: Tự động tô màu trùng lặp cho hàng hoặc cột lớn
10. Kỹ thuật nâng cao: Tô màu dựa trên nhiều cột, so sánh giữa hai sheet, và highlight phần trăm trùng lặp
11. Mẹo tối ưu hiệu suất khi làm việc với dữ liệu lớn
12. Câu hỏi thường gặp (FAQ)
13. Kết luận và lời khuyên cuối cùng

Có thể bạn quan tâm: Bút Vẽ Trong Excel 2026: Khám Phá, Sử Dụng Và Tối Ưu Hóa Để Tạo Những Đồ Thị, Sơ Đồ Sáng Tạo

1. Tại sao cần tô màu dữ liệu trùng?

• Tiết kiệm thời gian: Thay vì cuộn qua hàng ngàn dòng để tìm giá trị lặp, màu sắc giúp bạn “nhìn thấy” ngay lập tức.
• Giảm lỗi: Khi dữ liệu trùng xuất hiện trong các danh sách quan trọng (mã khách hàng, số hóa đơn, SKU…), việc đánh dấu giúp ngăn ngừa việc nhập dữ liệu sai hoặc xử lý sai.
• Hỗ trợ quyết định: Trong phân tích dữ liệu, việc biết có bao nhiêu bản ghi trùng lặp giúp bạn xác định mức độ sạch sẽ của dữ liệu và quyết định có nên làm sạch (cleaning) hay không.
• Tăng tính thẩm mỹ: Bảng tính được tô màu hợp lý sẽ dễ đọc, chuyên nghiệp hơn khi trình bày với đồng nghiệp hoặc khách hàng.

Có thể bạn quan tâm: Báo Cáo Tuyển Dụng Excel: Hướng Dẫn Chi Tiết Từ Khởi Đầu Đến Thành Công

2. Chuẩn bị dữ liệu trước khi thực hiện

Trước khi áp dụng bất kỳ phương pháp nào, bạn nên:

1. Xác định phạm vi dữ liệu: Đánh dấu cột hoặc vùng dữ liệu mà bạn muốn kiểm tra trùng lặp (có thể là một cột, nhiều cột hoặc toàn bộ bảng).
2. Kiểm tra định dạng: Đảm bảo các ô có cùng kiểu dữ liệu (số, văn bản, ngày tháng). Ví dụ, “00123” và “123” sẽ được xem là khác nhau nếu một ô là văn bản và một ô là số.
3. Loại bỏ khoảng trắng thừa: Sử dụng hàm TRIM để xóa khoảng trắng đầu/cuối nếu cần.
4. Sao lưu file: Đối với các file quan trọng, luôn tạo bản sao lưu trước khi thực hiện thay đổi lớn (đặc biệt là khi dùng VBA).

3. Cách tô màu dữ liệu trùng bằng Conditional Formatting (Định dạng có điều kiện)

3.1. Sử dụng công thức COUNTIF

Đây là phương pháp đơn giản nhất và phù hợp cho hầu hết các trường hợp.

Bước 1: Chọn phạm vi cần kiểm tra (ví dụ: A2:A1000).
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:

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

Bước 5: Nhấn Format, chọn màu nền (ví dụ: màu vàng nhạt) và OK.

Giải thích: COUNTIF($A$2:$A$1000, A2) đếm số lần giá trị trong ô A2 xuất hiện trong toàn bộ phạm vi. Nếu kết quả lớn hơn 1 → có trùng lặp → áp dụng định dạng.

3.2. Sử dụng công thức COUNTIFS cho nhiều tiêu chí

Khi bạn cần phát hiện trùng lặp dựa trên nhiều cột (ví dụ: cùng tên khách hàng và cùng ngày sinh), hãy dùng COUNTIFS.

Giả sử cột A là Tên khách hàng, cột B là Ngày sinh.

Công thức:

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

Áp dụng tương tự như trên, chọn phạm vi A2:B1000 (hoặc toàn bộ bảng) và đặt công thức này trong Conditional Formatting.

3.3. Định dạng dựa trên giá trị duy nhất (Unique)

Nếu mục tiêu của bạn là đánh dấu những giá trị duy nhất (không trùng) để dễ dàng nhìn thấy những mục “một lần duy nhất”, bạn có thể dùng công thức ngược lại:

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

Sau khi áp dụng, các ô duy nhất sẽ được tô màu, phần còn lại (các bản sao) sẽ không đổi.

4. Sử dụng tính năng “Remove Duplicates” để xác định và tô màu

Mặc dù “Remove Duplicates” thường được dùng để xóa các bản sao, nhưng chúng ta có thể tận dụng nó để đánh dấu trước khi xóa.

1. Sao chép cột dữ liệu sang một cột phụ (ví dụ: sao chép A sang C).
2. Chọn cột phụ → Data → Remove Duplicates → Chỉ để đánh dấu (không xóa). Khi bạn bật Filter, Excel sẽ hiển thị “Unique” và “Duplicate”.
3. Áp dụng Conditional Formatting cho cột gốc dựa trên giá trị “Duplicate” trong cột phụ (sử dụng công thức =C2="Duplicate").

Cách này hữu ích khi bạn muốn so sánh nhanh giữa bản gốc và bản đã loại bỏ trùng.

5. Sử dụng Power Query để phát hiện trùng lặp và tô màu tự động

Power Query (Get & Transform) là công cụ mạnh mẽ để làm sạch dữ liệu. Dưới đây là quy trình:

1. Load dữ liệu vào Power Query: Chọn bảng → Data → From Table/Range.
2. Trong Power Query Editor, chọn cột (hoặc các cột) cần kiểm tra.
3. Home → Keep Rows → Keep Duplicates – này sẽ tạo một bảng chỉ chứa các bản sao.
4. Close & Load → Tạo một bảng mới trong Excel chứa các giá trị trùng.
5. Quay lại sheet gốc, sử dụng VLOOKUP hoặc XLOOKUP để kiểm tra xem mỗi ô có nằm trong bảng trùng không, rồi dùng Conditional Formatting dựa trên công thức:

=NOT(ISERROR(VLOOKUP(A2, Trùng!$A$2:$A$500, 1, FALSE)))

Lưu ý: Power Query cho phép tự động cập nhật khi dữ liệu thay đổi – chỉ cần nhấn Refresh để cập nhật màu sắc.

Có thể bạn quan tâm: Bảng Cửu Chương File Excel: Hướng Dẫn Tạo, Sử Dụng Và Ứng Dụng Thực Tiễn

6. Vòng lặp VBA: Tự động tô màu trùng lặp cho hàng hoặc cột lớn

Khi làm việc với hàng ngàn, hàng chục ngàn hoặc muốn tự động áp dụng màu cho các hàng có bất kỳ cột nào trùng, VBA là giải pháp nhanh.

6.1. Mã VBA cơ bản tô màu cột A

Sub HighlightDuplicatesInColumnA() Dim rng As Range, cell As Range Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") Set rng = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row) Application.ScreenUpdating = False For Each cell In rng If Not IsEmpty(cell) Then If dict.Exists(cell.Value) Then cell.Interior.Color = RGB(255, 200, 200) 'Màu hồng nhạt dict(cell.Value).Interior.Color = RGB(255, 200, 200) 'tô màu ô đầu tiên cũng Else dict.Add cell.Value, cell End If End If Next cell Application.ScreenUpdating = True
End Sub

6.2. Mã VBA tô màu toàn bộ hàng nếu bất kỳ cột nào trùng

Sub HighlightRowsIfAnyDuplicate() Dim ws As Worksheet Dim lastRow As Long, lastCol As Long Dim i As Long, j As Long Dim dict As Object Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column Set dict = CreateObject("Scripting.Dictionary") Application.ScreenUpdating = False For i = 2 To lastRow For j = 1 To lastCol Dim key As String key = CStr(ws.Cells(i, j).Value) & "|" & j 'kết hợp giá trị + vị trí cột If Len(Trim(ws.Cells(i, j).Value)) > 0 Then If dict.Exists(key) Then ws.Rows(i).Interior.Color = RGB(255, 230, 150) 'màu vàng nhạt ws.Rows(dict(key)).Interior.Color = RGB(255, 230, 150) Else dict.Add key, i End If End If Next j Next i Application.ScreenUpdating = True
End Sub

Lưu ý: Khi chạy macro, luôn lưu file dưới dạng .xlsm để giữ lại mã VBA.

7. Kỹ thuật nâng cao

7.1. Tô màu dựa trên nhiều cột (cùng 2-3 cột)

Giả sử bạn muốn kiểm tra trùng lặp dựa trên cột A (Mã khách hàng) + cột B (Ngày giao dịch).

• Conditional Formatting công thức:

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

• VBA nhanh (đánh dấu cả 2 cột):

Sub HighlightDupMultiColumns() Dim rng As Range, arr, i As Long Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") Set rng = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row) arr = rng.Value For i = 1 To UBound(arr, 1) Dim key As String key = Trim(arr(i, 1)) & "|" & Trim(arr(i, 2)) If dict.Exists(key) Then rng.Rows(i).Interior.Color = RGB(200, 255, 200) 'xanh nhạt rng.Rows(dict(key)).Interior.Color = RGB(200, 255, 200) Else dict.Add key, i End If Next i
End Sub

7.2. So sánh dữ liệu giữa hai sheet và tô màu trùng

Bạn có Sheet1 (dữ liệu gốc) và Sheet2 (dữ liệu mới). Muốn tô màu các giá trị trong Sheet1 đã xuất hiện trong Sheet2.

Công thức Conditional Formatting trong Sheet1:

=NOT(ISERROR(MATCH(A2, Sheet2!$A$2:$A$5000, 0)))

Áp dụng cho cột A trong Sheet1, chọn màu nền tùy ý.

7.3. Highlight phần trăm trùng lặp (đánh dấu mức độ trùng)

Khi muốn biết mức độ “độ phổ biến” của một giá trị, bạn có thể dùng Data Bars trong Conditional Formatting:

1. Chọn cột dữ liệu.
2. Home → Conditional Formatting → Data Bars → Chọn kiểu màu.
3. Data Bars sẽ tự động kéo dài dựa trên số lần xuất hiện (được tính bằng COUNTIF).

Bạn cũng có thể tạo Rule mới với công thức:

=COUNTIF($A$2:$A$1000, A2)/COUNTA($A$2:$A$1000) > 0.1 'Nếu xuất hiện >10%

và tô màu nền xanh đậm cho những giá trị chiếm hơn 10% tổng.

8. Mẹo tối ưu hiệu suất khi làm việc với dữ liệu lớn

• Giới hạn phạm vi: Đừng áp dụng Conditional Formatting cho toàn bộ cột (A:A) nếu chỉ có 10.000 dòng; giới hạn tới dòng cuối cùng thực tế (A2:A10000).
• Sử dụng “Formula” thay vì “Duplicate Values”: Công thức COUNTIF thường nhanh hơn và cho phép tùy chỉnh.
• Tắt tính năng “Automatic Calculation” khi chạy VBA: Application.Calculation = xlCalculationManual và bật lại sau khi macro kết thúc.
• Xóa các quy tắc Conditional Formatting không cần thiết: Home → Conditional Formatting → Clear Rules → Clear Rules from Entire Sheet.
• Sử dụng “Tables”: Khi dữ liệu được chuyển thành Table (Ctrl+T), các công thức và định dạng tự cập nhật khi thêm dòng mới, giảm lỗi và tăng tốc độ.

9. Câu hỏi thường gặp (FAQ)

10. Kết luận và lời khuyên cuối cùng

Việc tô màu dữ liệu trùng trong Excel không chỉ là một thao tác thẩm mỹ mà còn là một bước quan trọng trong quy trình làm sạch và kiểm tra dữ liệu. Từ các phương pháp đơn giản như Conditional Formatting với COUNTIF, tới công cụ mạnh như Power Query và tự động hoá bằng VBA, bạn đều có thể lựa chọn giải pháp phù hợp với khối lượng và độ phức tạp của dữ liệu.

Những điểm cần ghi nhớ:

1. Xác định mục tiêu: Bạn muốn chỉ đánh dấu, hay muốn loại bỏ các bản sao? Điều này quyết định công cụ và cách thực hiện.
2. Chuẩn hoá dữ liệu trước khi kiểm tra trùng lặp (loại bỏ khoảng trắng, đồng nhất định dạng).
3. Giới hạn phạm vi và xóa các quy tắc không cần để duy trì hiệu suất.
4. Lưu ý bảo mật khi dùng VBA: luôn bật macro từ nguồn tin cậy và lưu file dưới định dạng .xlsm.
5. Kiểm tra lại: Sau khi tô màu, luôn rà soát một vài mẫu để chắc chắn công thức hoặc macro hoạt động đúng.

Nếu bạn áp dụng đúng các bước và công cụ đã nêu, việc phát hiện và đánh dấu dữ liệu trùng sẽ trở nên nhanh chóng, chính xác và ít gây phiền toái. Hãy thử ngay trên file của mình, điều chỉnh màu sắc, và cảm nhận sự khác biệt trong quá trình làm việc với Excel!

Chúc bạn thành công trong việc làm sạch và quản lý dữ liệu! Nếu có bất kỳ thắc mắc nào, đừng ngần ngại để lại bình luận hoặc hỏi trực tiếp – mình luôn sẵn sàng hỗ trợ.

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