Lọc Dữ Liệu Từ 2 File Excel Khác Nhau

Bởi

Trong thế giới kinh doanh, dữ liệu luôn là tài sản quan trọng nhất. Để đưa ra những quyết định chính xác, chúng ta cần phải thu thập, xử lý và phân tích dữ liệu một cách hiệu quả. Một trong những công việc phổ biến nhất khi làm việc với dữ liệu là lọc dữ liệu từ hai file Excel khác nhau, nhằm tìm ra những thông tin chung, tìm ra những điểm khác nhau, hoặc kết hợp dữ liệu để tạo ra báo cáo tổng hợp. Mặc dù Excel có thể xử lý mọi thao tác dữ liệu một cách trực quan, nhưng khi dữ liệu trở nên phức tạp, dung lượng lớn, hoặc khi bạn cần tự động hoá quy trình toget, việc lọc dữ liệu từ hai file có thể trở nên khó khăn.

Bài viết này sẽ cung cấp một hướng dẫn chi tiết, từng bước, từ cách chuẩn bị dữ liệu, các phương pháp lọc dữ liệu thủ công (sử dụng công thức, hàm, công cụ lọc nâng cao), tới các phương pháp tự động hoá (Power Query, VBA, Python). Bạn sẽ được trang bị đầy đủ công cụ, kĩ thuật và bí quyết để làm việc hiệu quả, tiết kiệm thời gian và tránh sai sót.

Có thể bạn quan tâm: Làm Thế Nào Để Xóa Dòng Kẻ Trong Excel

1. Hiểu rõ mục tiêu lọc dữ liệu

Trước khi bắt đầu wish, bạn cần xác định rõ mục tiêu của mình. Có ba mục tiêu chính khi lọc dữ liệu từ hai file Excel:

  1. Tìm dữ liệu chung (intersection): Xác định những dòng hoặc giá trị xuất hiện trong cả hai file.
  2. Tìm dữ liệu khác nhau (difference): Xác định những dòng hoặc giá trị xuất hiện trong một file mà không có trong file còn lại.
  3. Kết hợp dữ liệu (union): Tập hợp tất cả các dòng trong cả hai file, loại bỏ trùng lặp nếu cần.

Ví dụ: Bạn có file khachhang.xlsx chứa danh sách khách hàng hiện tại và file mua_hang.xlsx chứa danh sách khách hàng đã mua hàng trong tháng vừa qua. Bạn muốn lọc ra những khách hàng đã mua hàng nhưng chưa có trong danh sách khách hàng hiện tại, hay ngược lại, để gửi email khuyến mãi.

Có thể bạn quan tâm: Làm Thế Nào Để In Excel Trong 1 Trang: Hướng Dẫn Chi Tiết Và Các Mẹo Nâng Cao

2. Chuẩn bị dữ liệu

2.1 Kiểm tra tính nhất quán

  • Cột dữ liệu: Đảm bảo rằng hai file có cùng cấu trúc cột (đều có cột ID, Tên, Email, v.v.). Nếu không, bạn cần chuẩn bị một bảng chuyển đổi để đồng nhất.
  • Định dạng dữ liệu: Kiểm tra dữ liệu kiểu ngày, số, chuỗi, tránh lỗi do định dạng không đồng nhất.
  • Xóa dữ liệu trùng lặp: Sử dụng tính năng Remove Duplicates trong Excel để loại bỏ trùng lặp trong mỗi file.

2.2 Đặt tên cột và vùng dữ liệu

  • Đặt tên cho cột (ví dụ ID, Tên, Địa chỉ) và đặt tên cho vùng dữ liệu (Range) bằng cách chọn vùng và gõ tên trong ô Name Box_server (người dùng có thể đặt tên như KhachHangData và MuaHangData).
  • Giúp trong công thức và Power Query.

Có thể bạn quan tâm: Làm Sao Để Xuống Dòng Trong 1 Ô Excel

3. Phương pháp lọc dữ liệu thủ công (Excel)

3.1 Sử dụng công thức VLOOKUP và MATCH

itso dụng VLOOKUP

Ví dụ: Bạn muốn tìm khách hàng trong khachhang.xlsx mà không có trong mua_hang.xlsx:

=IF(ISNA(VLOOKUP(A2, mua_hang.xlsxSheet1!$A:$A, 1, FALSE)), "Không có", "Có")
  • A2: ID khách hàng trong file hiện tại.
  • mua_hang.xlsxSheet1!$A:$A: Mảng dữ liệu ID trong file mua hàng.

Kết quả “Không có” nghĩa là khách hàng chưa mua hàng.

Sử dụng MATCH

=IF(ISNUMBER(MATCH(A2, mua_hang.xlsxSheet1!$A:$A, 0)), "Có", "Không có")

Cả hai công thức đều cho phép lọc dữ liệu ngoài.

3.2 Sử dụng hàm XLOOKUP (Excel 365 / 2026)

XLOOKUP cung cấp tính linh hoạt hơn:

精神=IF(ISNA(XLOOKUP(A2, mua_hang.xlsxSheet1!$A:$A, mua_hang.xlsxSheet1!$B:$B, "Không có")), "Không có", "Có")

3.3 Sử dụng công cụ “Advanced Filter”

  1. Chọn vùng dữ liệu trong file nguồn.
  2. Vào Data > Advanced.
  3. Chọn Copy to another location.
  4. Đặt Criteria range là cột ID trong file mục tiêu.
  5. Chọn Unique records only để lọc dữ liệu duy nhất.

3.4 Sử dụng PivotTable để tìm dữ liệu chung

  • Tạo PivotTable từ cả hai file (đặt ID vào Rows, Count vào Values).
  • Các ID có Count ≥ 2 là dữ liệu chung.

3.5 Sử dụng Conditional Formatting

  • Định dạng màu sắc cho ID xuất hiện trong cả hai file để trực quan dễ nhận.

4. Phương pháp tự động hoá với Power Query

Power Query (Get & Transform) là công cụ mạnh mẽ trong Excel, giúp nhập, chuyển đổi, và kết hợp dữ liệu mà không cần viết mã. Đối với lọc dữ liệu từ hai file, Power Query cung cấp các bước rõ ràng:

4.1 Nhập dữ liệu từ file

  1. Vào Data > Get Data > From File > From Workbook.
  2. Chọn khachhang.xlsx, chọn sheet và vùng dữ liệu.
  3. Lặp lại cho mua_hang.xlsx.

4.2 Chuẩn bị dữ liệu

  • Sử dụng các thao tác trong Power Query như Remove Duplicates, Trim, Replace Values.
  • Đặt cột ID làm Key.

4.3 Thực hiện Merge (Kết hợp)

  • Vào Home > Merge Queries.
  • Chọn KhachHangData làm bảng chính, MuaHangData làm bảng phụ.
  • Chọn cột ID làm khóa.
  • Chọn loại kết nối: Left Outer, Right Outer, Full Outer, hoặc Inner tùy mục tiêu.

Inner Join

  • Trả về dữ liệu có ID trong cả hai file (tìm dữ liệu chung).

Left Outer

-嘎

Left Outer: Lấy tất cả dữ liệu từ bảng chính và dữ liệu phù hợp từ bảng phụ. Nếu không có dữ liệu phù hợp, sẽ trả về null. Đây là cách tìm khách hàng chưa mua hàng.

Right Outer

  • Ngược lại, lấy toàn bộ dữ liệu từ bảng phụ và phù hợp từ bảng chính: khách hàng đã mua hàng nhưngाएँ không có trong danh sách khách hàng hiện tại.

Full Outer

  • Kết hợp cả hai. Trả về toàn bộ dữ liệu, với null cho cột không có trong bảng kia.

4.4 Thêm cột tùy chỉnh

  • Sau khi merge, bạn có thể thêm cột tùy chỉnh để đánh dấu trạng thái:
= if MuaHangData.ID = null then "Chưa mua" else "Đã mua"

4.5 Xuất dữ liệu

  • Sau khi hoàn thiện, nhấn Close & Load để đưa dữ liệu vào sheet mới. Bạn có thể lưu trữ kết quả dưới dạng bảng.

4.6 Lập lịch tự động (Power Query Refresh)

Lọc Dữ Liệu Từ 2 File Excel Khác Nhau
Lọc Dữ Liệu Từ 2 File Excel Khác Nhau

Có thể bạn quan tâm: Lọc Dữ Liệu Theo Ngày Tháng Năm Trong Excel

  • Khi dữ liệu trong file nguồn thay đổi, bạn có thể refresh query bằng Data > Refresh All. Nếu muốn tự động refresh khi mở file, vào Data > Properties > Refresh data when opening the file.

5. Tự động hoá bằng VBA

Nếu bạn cần tính linh hoạt cao hơn, VBA (Visual Basic for Applications) là công cụ mạnh mẽ. Dưới đây là một ví dụ mã VBA để lọc dữ liệu chung và khác nhau từ hai file.

Sub FilterDataFromTwoWorkbooks() Dim wb1 As Workbook, wb2 As Workbook Dim ws1 As Worksheet, ws2 As Worksheet Dim dict As Object Dim rng1 As Range, rng2 As Range Dim cell As Range Dim outputRow As Long ' Mở hai workbook (giả sử đã mở) Set wb1 = Workbooks("khachhang.xlsx") Set wb2 = Workbooks("mua_hang.xlsx") Set ws1 = wb1.Sheets("Sheet1") Set ws2 = wb2.Sheets("Sheet1") ' Định nghĩa vùng dữ liệu (giả sử ID ở cột A) Set rng1 = ws1.Range("A2", ws1.Cells(ws1.Rows.Count, "A").End(xlUp)) Set rng2 = ws2.Range("A2", ws2.Cells(ws2.Rows.Count, "A").End(xlUp)) ' Tạo đối tượng Dictionary để lưu ID Set dict = CreateObject("Scripting.Dictionary") ' Lưu ID từ file 1 For Each cell In rng1 dict(cell.Value) = "InFile1" Next cell ' Kiểm tra ID trong file 2 For Each cell In rng2 If dict.Exists(cell.Value) Then dict(cell.Value) = "InBoth" Else dict(cell.Value) = "InFile2" End If Next cell ' Tạo sheet mới để xuất kết quả Dim wsOut As Worksheet Set wsOut = wb1.Sheets.Add(After:=wb1.Sheets(wb1.Sheets.Count)) wsOut.Name = "FilterResult" wsOut.Cells(1, 1).Value = "ID" wsOut.Cells(1, 2).Value = "Status" outputRow = 2 Dim key As Variant For Each key In dict.Keys wsOut.Cells(outputRow, 1).Value = key wsOut.Cells(outputRow, 2).Value = dict(key) outputRow = outputRow + 1 Next key MsgBox "Lọc dữ liệu hoàn thành!"
End Sub

Giải thích mã

  • Dictionary dùng để lưu trữ ID và trạng thái.
  • Kiểm tra từng ID trong file thứ hai, cập nhật trạng thái Truth.
  • Kết quả được xuất vào một sheet mới với cột ID và Status (InFile1, InFile2, InBoth).

Bạn có thể mở rộng mã để thực hiện các loại lọc khác nhau (Inner, Left, Right Outer).

6. Sử dụng Python (pandas) để lọc dữ liệu

Python, với thư viện pandas, là công cụ mạnh mẽ để xử lý dữ liệu lớn, đặc biệt khi làm việc với nhiều file Excel. Dưới đây là một ví dụ đơn giản:

import pandas as pd # Đọc dữ liệu từ hai file
df_khachhang = pd.read_excel('khachhang.xlsx')
df_mua_hang = pd.read_excel('mua_hang.xlsx') # Giả sử ID là cột 'ID'
# Định danh dữ liệu chung
df_common = pd.merge(df_khachhang, df_mua_hang, on='ID', how='inner') # Định danh dữ liệu khác nhau
df_khach;+df_mua = pd.merge(df_khachhang, df_mua_hang, on='ID', how='left', indicator=True)
df_khach_ngoa_mua = df_khach_ngoa_muadfsspiel'_merge' == 'left_only' df_mua_ngoa_khach = pd.merge(df_khachhang, df_mua_hang, on='ID', how='right', indicator=True)
df_mua_ngoa_khach = df_mua_ngoa_khachdf_mua_ngoa_khach'_merge' == 'right_only' # Xuất kết quả
df_common.to_excel('common.xlsx', index=False)
df_khach_ngoa_mua.to_excel('khach_ngoa_mua.xlsx', index=False)
df_mua_ngoa_khach.to_excel('mua_ngoa_khach.xlsx', index=False)

Ưu điểm khi dùng Python

  • Độ linh hoạt: Bạn có thể thực hiện các phép biến đổi dữ liệu phức tạp (group by, pivot, thống kê).
  • Xử lý dữ liệu FLOOR: Dễ dàng đọc, ghi nhiều file Excel, визуализация.
  • Tự động hoá: Viết script và chạy hàng ngày bằng cron hoặc Task Scheduler.

7. Hướng dẫn từng bước: Từ dữ liệu thô đến báo cáo

Giả sử bạn có hai file: DSSV_DaThanhToan.xlsx và DSSV_DangHoc.xlsx. Bạn muốn lọc ra những sinh viên đã thanh toán nhưng chưa đăng ký học, và ngược lại. Dưới đây là quy trình chi tiết:

7.1 Mở hai file và chuẩn bị dữ liệu

  1. Xóa trùng lặp: Remove Duplicates trong cả hai file.
  2. Định dạng dữ liệu: Kiểm tra cột MSSV (code) có dạng số.
  3. Đặt tên cột: MSSV, Tên, Email.

7.2 Sử dụng Power Query

  • Nhập dữ liệu: Data > Get Data > From Workbook > DSSV_DaThanhToan.xlsx > ση.
  • Merge: Merge Queries > DSSV_DaThanhToan (left) và DSSV_DangHoc (right) > MSSV > Left Outer.
  • Thêm cột tùy chỉnh: MSSV trong bảng DangHoc là null => Chưa đăng ký.

7.3 Xem kết quả

  • Tạo một bảng trong Sheet1 chứa MSSV, Tên, Email, Trạng thái.

8. Kỹ thuật nâng cao: Lọc dữ liệu phức tạp

8.1 Lọc theo nhiều điều kiện

Bạn có thể sử dụng formula array như FILTER (Excel 365):

=FILTER(KhachHang!A:B, (KhachHang!C:C="Email") (KhachHang!D:D="Active"))

8.2 Tạo báo cáo tóm tắt

  • Sử dụng PivotTable: Rows = Trạng thái, Values = COUNT của ID.
  • Thêm slicer để lọc theo năm, tháng.

8.3 Sử dụng Conditional Formatting nâng cao

  • Tạo quy tắc: Use a formula to determine which cells to format.
=AND($B2="Chưa đăng ký", $C2="Active")

9. Kiểm tra và bảo trì

  • Kiểm tra tính đầy đủ: Dùng công thức COUNTIF để đếm số dòng trong kết quả và so sánh với tổng số.
  • Kiểm tra lỗi: Sử dụng CLEAN và TRIM để loại bỏ ký tự không mong muốn.
  • Back-up: Định kỳ lưu bản sao dữ liệu gốc.

10. Tương lai: Tự động hoá quy trình

  • Microsoft Power Automate: Kết nối Excel Online, tự động chạy Power Query khi dữ liệu thay đổi.
  • SQL Server / Azure: Nhập dữ liệu vào database, dùng JOIN để lọc.
  • Big Data: Khi dữ liệu lớn hơn, chuyển sang Hadoop / Spark.

11. Tổng kết

Lọc dữ liệu từ hai file Excel khác nhau là một kỹ năng cốt lõi, nhưng cũng phức tạp nếu dữ liệu lớn. Bằng cách chuẩn bị dữ liệu, sử dụng công cụ phù hợp (công thức, Power Query, VBA ustan hoặc Python), bạn có thể tự động hoá quy trình, giảm thời gian và tránh sai sót.

  • Công thức: Thích hợp cho dữ liệu nhỏ, nhanh chóng.
  • Power Query: Đối với dữ liệu vừa và lớn, dễ dàng thao tác ngắn gọn.
  • VBA: Nhiều tùy biến, tự động hoá.
  • Python: Dùng khi dữ liệu lớn, cần tính toán phức tạp.

Hãy lựa chọn công cụ phù hợp với nhu cầu và quy mô dữ liệu của mình. Khi đã nắm vững các phương pháp, bạn sẽ nhanh chóng trở thành người có thể xử lý dữ liệu một cách chuyên nghiệp, đưa ra quyết định chính xác và kịp thời cho doanh nghiệp.

Chúc bạn thành công trong việc lọc dữ liệu từ hai file Excel và đưaาสิโน ra những quyết định kinh doanh thông minh!

⚠️
LƯU Ý QUAN TRỌNG: Miễn trừ trách nhiệm: Mặc dù chúng tôi luôn nỗ lực để đảm bảo tính chính xác và cập nhật của thông tin, nhưng các quy định về giáo dục và tuyển sinh có thể thay đổi theo thời gian. BigSchool không chịu trách nhiệm về bất kỳ sai sót nào hoặc kết quả phát sinh từ việc sử dụng thông tin trên trang này. Độc giả nên liên hệ trực tiếp với nhà trường hoặc cơ quan chức năng để có thông tin mới nhất. Ngoài ra, BigSchool tuyệt đối không bán sản phẩm, khóa học hay cung cấp dịch vụ được nhắc đến trong bài.

Viết một bình luận