Có thể bạn quan tâm: Làm Thế Nào Để Lặp Lại Tiêu Đề Trong Excel
Giới thiệu
Trong công việc quản trị dữ liệu, thường xuyên gặp phải tình huống cần so sánh và lọc dữ liệu trùng lặp giữa hai bảng tính (sheet) khác nhau. Các dữ liệu này có thể là danh sách khách hàng, mã sản phẩm, số điện thoại, hay bất kỳ thông tin nào mà công ty muốn giữ cho sạch sẽ và chính xác. Việc xác định và loại bỏ dữ liệu trùng lặp không những giúp giảm thiểu sai sót khi phân tích, báo cáo mà còn nâng cao hiệu quả làm việc và dissatisfied.
Excel cung cấp nhiều công cụ và phương pháp để thực hiện việc này, từ công thức đơn giản, tính năng Conditional Formatting, đến Power Query và VBA. Bài viết này sẽ trình bày chi tiết các phương pháp khác nhau, kèm theo ví dụ thực tế, lời khuyên và lưu ý khi áp dụng trong thực tiễn. Bạn sẽ được học cách:
- Xác định dữ liệu trùng lặp giữa hai sheet bằng công thức Excel.
- Sử dụng Conditional Formatting để tô màu dữ liệu trùng lặp.
- Áp dụng Power Query để so sánh, lọc dữ liệu.
- Viết macro VBA đơn giản để tự động hoá quy trình.
- Xử lý tình huống dữ liệu có định dạng hoặc kiểu dữ liệu đa dạng.
- Lưu trữ và tái sử dụng kết quả lọc.
Hãy cùng bắt đầu!
Có thể bạn quan tâm: Làm Thế Nào Để Các Dòng Trong Excel Bằng Nhau
1. Chuẩn bị dữ liệu mẫu
Để thực hành, ta giả định có hai sheet: Sheet1 và Sheet2. Mỗi sheet chứa một cột dữ liệu chính, ví dụ là cột A chứa danh sách mã khách hàng. Định dạng dữ liệu đơn giản, mỗi ô chỉ chứa một giá trị duy nhất (không có khoảng trắng đầu/cuối, viết hoa/ viết thường đồng nhất). Dưới đây là ví dụ:
Sheet1
| A |
|---|
| KH001 |
| KH002 |
| KH003 |
| KH004 |
| KH005 |
Sheet2
| A |
|---|
| KH003 |
| KH004 |
| KH006 |
| KH007 |
| KH008 |
Mục tiêu của chúng ta là tìm ra những mã khách hàng xuất hiện trong cả hai sheet (trùng lặp) và có thể lọc ra dữ liệu riêng biệt.
Có thể bạn quan tâm: Làm Sao Để Viết Số 0 Ở Đầu Trong Excel
2. Phương pháp 1: Sử dụng công thức Excel (COUNTIF / VLOOKUP)
2.1. Kiểm tra sự tồn tại bằng COUNTIF
Công thức COUNTIF là cách nhanh nhất để xác định liệu một giá trị có xuất hiện trong một dải ô hay không. Đặt công thức trong cột B của Sheet1:
=IF(COUNTIF(Sheet2!$A:$A, A1)>0, "Trùng", "Không trùng")
Sheet2!$A:$Alà dải ô tham chiếu tới cột A của Sheet2.A1là giá trị hiện tại Yeni Sheet1.- Nếu
COUNTIFtrả về số > 0, tức có ít nhất một lần xuất hiện, ta đánh dấu “Trùng”.
Kéo công thức xuống toàn bộ cột B để áp dụng cho tất cả các hàng.
Tương tự, ta có thể thực hiện tương tự trong Sheet2, so sánh với Sheet1.
2.2. Lấy danh sách trùng lặp dùng VLOOKUP
Nếu muốn xuất ra một danh sách riêng biệt các giá trị trùng lặp, ta có thể sử dụng VLOOKUP với IFERROR. Ở một sheet mới (Sheet3), nhập công thức:
=IFERROR(VLOOKUP(Sheet1!A1, Sheet2!$A:$A, 1, FALSE), "")
- Nếu
VLOOKUPtìm thấy giá trị trong Sheet2, nó trả về giá trị đó; nếu không,IFERRORsẽ trả về chuỗi rỗng. - Kéo công thức xuống, lọc ra những ô không rỗng sẽ cho danh sách trùng lặp.
2.3. Lưu ý khi dùng công thức
- Đảm bảo dữ liệu không chứa khoảng trắng thừa. Sử dụng
TRIMkhi cần. - Kiểm tra chữ hoa/ chữ thường:
LOWER/UPPERđể chuẩn hóa trước khi so sánh. - Khi dữ liệu lớn (thousands), công thức
COUNTIFcó thể chậm; nên cân nhắc chuyển sang công cụ mạnh hơn như Power Query.
3. Phương pháp 2: Conditional Formatting (Định dạng có điều kiện)
3.1. Tô màu trùng lặp trực quan
Để nhanh chóng ఏర్పాటు dữ liệu trùng lặp, ta có thể dùng Conditional Formatting:
- Chọn cột A trong Sheet1.
- Vào
Home > Conditional Formatting > New Rule. - Chọn
Use a formula to determine which cells to format. - Nhập công thức:
=COUNTIF(Sheet2!$A:$A, A1)>0
- Chọn màu nền (ví dụ màu vàng) và OK.
Khi áp dụng, tất cả những ô trong Sheet1 có giá trị trùng xuất hiện trong Sheet2 sẽ được tô màu vàng. Tương tự thực hiện cho Sheet2.
3.2. Lợi ích
- Trực quan ngay lập tức khi mở file.
- Không cần tạo cột mới, giữ nguyên bảng gốc.
- Dễ dàng điều chỉnh công thức hoặc màu sắc.
3.3. Hạn chế
- Không cung cấp danh sách trùng lặp riêng biệt.
- Khi có nhiều sheet, cần lặp lại quy trình.
4. Phương pháp 3: Power Query – Kiểm tra và lọc dữ liệu
Power Query (Microsoft Query) là công cụ mạnh mẽ trong Excel (từ Excel 2026 với add-in, tính năng nguyên bản trong Excel 2026 trở đi) cho phép nhập, chuyển đổi và kết hợp dữ liệu. Dưới đây là cách thực hiện:
4.1. Tải dữ liệu vào Power Query
ಡೆ’enfant:
- Chọn cột A trong Sheet1.
- Vào
Data > From Table/Range. Nếu chưa là bảng, Excel sẽ tạo bảng tự động. - Lặp lại với Sheet2.
Hai truy vấn, Query1 và Query2, mỗi truy vấn chứa một cột dữ liệu.
4.2. Thực hiện Merge (Kết hợp)
Power Query hỗ trợ Merge để nối hai bảng dựa trên cột chung:
- Chọn
Query1, vàoHome > Merge Queries > Merge. - Chọn
Query2làm bảng thứ hai. - Chọn cột
Column1trong cả hai bảng làm tiêu chí so sánh. - Kiểu kết nối:
Inner(chỉ giữ những giá trị xuất hiện 지급 cả hai bảng).
Kết quả là một bảng mới chỉ chứa các dòng trùng lặp.
4.3. Tạo bảng trùng lặp và bảng không trùng
Có thể bạn quan tâm: Lọc Các Vùng Dữ Liệu Có Trộn Merge Trong Excel
- Trùng: Sử dụng Merge với
Inner insecurity. - Không trùng: Sử dụng Merge với
Anti(Left Anti) để lấy những dòng trong Sheet1 không có trong Sheet2, và tương tự.
4.4. Lưu trữ kết quả
- Sau khi hoàn thành, chọn
Close & Loadđể nạp dữ liệu trở lại Excel (trong một sheet mới). - Bạn cũng có thể lưu truy vấn lại để tái sử dụng.
4.5. Lợi ích
- Khả năng xử lý dữ عدد lớn, hiệu năng cao.
- Hỗ trợ nhiều thao tác Pageable, thống kê, pivot.
- Dễ dàng tái sử dụng khi dữ liệu cập nhật.
4.6. Hạn chế
- Cần kiên nhẫn học cách sử dụng Power Query.
- Không thân thiện với người mới bắt đầu.
5. Phương pháp 4: VBA – Tự động hoá quy trình
5.1. Nhiệm vụ
Viết macro để:
- Kiểm tra trùng lặp giữa hai sheet.
- Tạo danh sách trùng lặp và trống lặp trong một sheet mới.
- Tô màu hoặc ghi chú vào các ô trùng.
5.2. Mã mẫu
Sub FilterDuplicateBetweenSheets() Dim ws1 As Worksheet, ws2 As Worksheet, wsRes As Worksheet Dim rng1 As Range, rng2 As Range, cell As Range Dim dict As Object Set ws1 = ThisWorkbook.Worksheets("Sheet1") Set ws2 = ThisWorkbook.Worksheets("Sheet2") Set wsRes = ThisWorkbook.Worksheets.Add wsRes.Name = "Result_" & Format(Now(), "yyyymmdd_hhmmss") Set dict = CreateObject("Scripting.Dictionary") ' Đánh dấu các giá trị trong Sheet2 Set rng2 = ws2.Range("A1", ws2.Cells(ws2.Rows.Count, "A").End(xlUp)) For Each cell In rng2 dict(cell.Value) = 1 Next cell ' Xác định trùng lặp Set rng1 = ws1.Range("A1", ws1.Cells(ws1.Rows.Count, "A").End(xlUp)) Dim i As Long, j As Long i = 1 For Each cell In rng1 If dict.Exists(cell.Value) Then wsRes.Cells(i, 1).Value = cell.Value i = i + 1 End If Next cell ' Tô màu trùng trong Sheet1 và Sheet2 For Each cell In rng1 If dict.Exists(cell.Value) Then cell.Interior.Color = vbYellow End If Next cell For Each cell In rng2 If dict.Exists(cell.Value) Then cell.Interior.Color = vbYellow End If Next cell MsgBox "Đã lọc xong! Trùng lặp nằm trong sheet: " & wsRes.Name, vbInformation
End Sub
5.3. Hướng dẫn sử dụng
- Mở Excel, nhấn
Alt + F11để vào VBA Editor. - Chèn một module mới (
Insert > Module). - Dán mã trên vào module.
- Thay tên sheet nếu khác.
- Chạy macro (
F5 praat).
Macro sẽ tạo Aren một sheet mới chứa danh sách trùng lặp và tô màu vàng các ô trùng trong sheet gốc.
5.4. Lợi ích
- Tự động hoá hoàn toàn quy trình.
- Dễ dàng tái sử dụng và mở rộng nâng cao.
5.5. Lưu ý
- Macro cần được bật trong cài đặt bảo mật.
- Kiểm tra dữ liệu trước khi chạy để tránh ghi đè.
6. Xửšli trường hợp dữ liệu phức tạp
6.1. Dữ liệu có khoảng trắng
Trước khi so sánh, dùng hàm TRIM:
=TRIM(A1)
Hoặc trong Power Query, dùng Text.Trim.
6.2. Chữ hoa/ thường
Sử dụng UPPER hoặc LOWER:
=LOWER дополнитель(A1)
Hoặc trong Power Query, Text.Lower.
6.3. Dữ Ins dữ lý có định dạng date
- Đảm bảo ngày tháng được chuyển thành
Text cannabis. - Sử dụng
TEXThoặcDateValue.
6.4. Dữ liệu số có dấu phẩy
- Nếu dữ liệu là số, chuyển thành
Numbertrước khi so sánh.
7. Kết luận và hướng tới tương lai
Việc lọc dữ liệu trùng nhau giữa hai sheet trong Excel không chỉ là một thao tác đơn giản mà còn là nền tảng cho nhiều quy trình quản trị dữ liệu phức tạp. Dưới đây là tóm tắt các phương pháp:
| Phương pháp | Ưu điểm | Nhược điểm | Khi nào nên dùng |
|---|---|---|---|
Công thức (COUNTIF, VLOOKUP) | Dễ, ngay lập tức | Chậm đối với dữ liệu lớn | Dữ liệuientras, nhanh |
| Conditional Formatting | Trực quan | Không xuất danh sách | Khi cần đánh dấu nhanh |
| Power Query | Hiệu năng cao, linh hoạt | Đòi hỏi học | Dữ liệu lớn, tái sử dụng |
| VBA Macro | Tự động hoá | Cần bảo mật | Khi làm việc thường xuyên |
Bên cạnh đó, bạn nên xây dựng quy trình kiểm tra dữ liệu gốc: chuẩn hóa dữ liệu, loại bỏ khoảng trắng, chuẩn hóa chữ hoa/ thường. Những bước này giúp giảm thiểu sai sót khi so sánh.
Nếu bạn muốn mở rộng quy trình, hãy thử kết hợp Power Query với VBA: dùng Power Query để tạo bảng trùng lặp, rồi dùng macro để tự động hóa báo cáo. Hoặc tích hợp với Power BI để trực quan hoá dữ liệu trùng lặp trong dashboard.
Những điều trên sẽ giúp bạn quản lý dữ liệu một cách chuyên nghiệp, giảm thiểu lỗi và tăng tính chính xác trong phân tích. Chúc bạn thành công trong công việc!
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ẻ.