🔑 Các Phương Pháp So Sánh 2 Bảng Dữ Liệu Lớn trong Excel

By

Việc so sánh hai bảng dữ liệu lớn (hàng chục ngàn dòng) để kiểm tra sự ăn khớp là một nghiệp vụ rất phổ biến và quan trọng. Dưới đây là các phương pháp hiệu quả nhất, kèm theo ví dụ và thao tác cụ thể.

Khi làm việc với hàng chục ngàn dòng, hiệu suất và độ chính xác là ưu tiên hàng đầu.

1. Phương pháp Dùng Hàm VLOOKUP/XLOOKUP (Phổ biến nhất)

Đây là cách cơ bản và mạnh mẽ nhất để kiểm tra từng dòng dữ liệu có tồn tại ở bảng còn lại hay không, dựa trên một khóa chính (ví dụ: Mã đơn hàng, Mã khách hàng).

  • Trường hợp ví dụ thường gặp: So sánh Bảng A (dữ liệu bán hàng mới) với Bảng B (dữ liệu tồn kho) để xem tất cả các mặt hàng bán ra đã được cập nhật tồn kho hay chưa.
  • Mục tiêu: Kiểm tra sự tồn tại của Khóa chính.

Thao tác & Giải thích:

Giả sử cả hai bảng đều có cột “Mã sản phẩm”. Ta so sánh từ Bảng A sang Bảng B.

  1. Tại cột trống bên cạnh Bảng A (ví dụ: cột E), nhập công thức VLOOKUP hoặc XLOOKUP.
  2. Công thức (Excel cũ):=VLOOKUP(A2, Bảng_B!A:B, 2, FALSE)
    • A2: Giá trị cần tìm (Mã sản phẩm ở Bảng A).
    • Bảng_B!A:B: Phạm vi tìm kiếm ở Bảng B.
    • 2: Cột thứ 2 trong phạm vi tìm kiếm (chỉ cần lấy 1 cột bất kỳ)
    • FALSE: Yêu cầu tìm kiếm chính xác.
  3. Công thức (Excel 365/2019+):=XLOOKUP(A2, Bảng_B!A:A, Bảng_B!A:A, “Không có”, 0)
    • Nếu tìm thấy, công thức trả về Mã sản phẩm (hoặc giá trị khác) từ Bảng B.
    • Nếu không tìm thấy, công thức trả về lỗi #N/A hoặc giá trị “Không có”.
  • Kết quả: Dùng chức năng Filter (Lọc) cột kết quả này và lọc ra các giá trị #N/A (hoặc “Không có”) để xác định các dòng KHÔNG ăn khớp.

2. Phương pháp Dùng Hàm COUNTIF (Kiểm tra tần suất)

Hàm này rất nhanh và hiệu quả để đếm xem Khóa chính của Bảng A xuất hiện bao nhiêu lần trong Bảng B, giúp phát hiện dòng bị thiếu hoặc dòng bị trùng lặp.

  • Trường hợp ví dụ thường gặp: Kiểm tra xem Khóa chính (ví dụ: Mã Invoice) trong Bảng A có phải là duy nhất và đã có mặt đầy đủ trong Bảng B (Bảng thanh toán) chưa.
  • Mục tiêu: Kiểm tra tần suất và sự thiếu sót của Khóa chính.

Thao tác & Giải thích:

  1. Tại cột trống bên cạnh Bảng A, nhập công thức:=COUNTIF(Bảng_B!A:A, A2)
    • Bảng_B!A:A: Phạm vi đếm (Cột Khóa chính ở Bảng B).
    • A2: Điều kiện đếm (Mã sản phẩm ở Bảng A).
  2. Kết quả:
    • Nếu kết quả là 0: Dòng này ở Bảng A KHÔNG có mặt trong Bảng B (bị thiếu).
    • Nếu kết quả là 1: Dòng này ăn khớp hoàn toàn (và là duy nhất).
    • Nếu kết quả là >1: Dòng này ở Bảng A TRÙNG LẶP nhiều lần trong Bảng B (dữ liệu Bảng B bị lỗi).

3. Phương pháp Dùng Định Dạng Có Điều Kiện (Conditional Formatting)

Đây là cách trực quan và nhanh nhất để tô màu các giá trị KHÔNG trùng khớp giữa hai cột Khóa chính.

  • Trường hợp ví dụ thường gặp: So sánh hai cột danh sách ID (ở 2 bảng) đặt cạnh nhau trên cùng một sheet để tìm nhanh các ID không khớp.
  • Mục tiêu: Nhận diện bằng mắt thường các Khóa chính không trùng nhau.

Thao tác & Giải thích:

  1. Đảm bảo hai cột Khóa chính cần so sánh nằm trên cùng một sheet hoặc dễ dàng tham chiếu. Giả sử là cột A và cột B.
  2. Chọn toàn bộ cột A.
  3. Vào Home $\rightarrow$ Conditional Formatting $\rightarrow$ New Rule.
  4. Chọn Use a formula to determine which cells to format.
  5. Nhập công thức (để tô màu những ô không có trong cột B):=ISNA(MATCH(A1,$B:$B,0))
    • MATCH(A1,$B:$B,0): Tìm A1 trong toàn bộ cột B. Nếu không tìm thấy trả về #N/A.
    • ISNA(…): Kiểm tra xem kết quả có phải là #N/A không. Nếu Đúng (không tìm thấy) thì tô màu.
  6. Chọn Format để thiết lập màu.
  7. Lặp lại tương tự cho cột B (để tô màu những ô không có trong cột A).

4. Phương pháp Dùng Power Query (Tốt nhất cho dữ liệu lớn)

Đối với dữ liệu hàng chục ngàn dòng và cần so sánh nhiều cột hoặc thực hiện phép toán hợp nhất phức tạp (Full/Left/Right Outer Join), Power Query (Get & Transform Data) là công cụ tối ưu nhất trong Excel (từ phiên bản 2016 trở lên).

  • Trường hợp ví dụ thường gặp: Hợp nhất và so sánh cả hai bảng, kiểm tra sự ăn khớp của Khóa chính VÀ các cột giá trị (ví dụ: Số lượng, Giá trị).
  • Mục tiêu: Kiểm tra tính toàn vẹn (Integration) của cả cấu trúc bảng.

Thao tác & Giải thích:

  1. Tải cả hai bảng dữ liệu vào Power Query (Data $\rightarrow$ Get & Transform Data $\rightarrow$ From Table/Range).
  2. Vào thẻ Data $\rightarrow$ Get Data $\rightarrow$ Combine Queries $\rightarrow$ Merge.
  3. Trong cửa sổ Merge:
    • Bảng 1: Chọn bảng thứ nhất.
    • Bảng 2: Chọn bảng thứ hai.
    • Chọn cột Khóa chính (ví dụ: Mã sản phẩm) ở cả hai bảng.
    • Chọn Join Kind (Loại kết hợp) quan trọng nhất:
      • Left Anti Join: Trả về các dòng CHỈ có ở Bảng 1, không có ở Bảng 2. (Tìm dòng bị thiếu)
      • Right Anti Join: Trả về các dòng CHỈ có ở Bảng 2, không có ở Bảng 1. (Tìm dòng bị thiếu)
      • Full Outer Join: Trả về TẤT CẢ các dòng và điền null cho những dòng không khớp ở bảng đối diện. (So sánh toàn diện)
  4. Nhấn OK. Power Query sẽ hiển thị kết quả.
  • Kết quả: Dễ dàng lọc ra các dòng có giá trị null ở cột của bảng đối diện để xác định những dòng không ăn khớp. Phương pháp này rất nhanh và ít gây lỗi.
Scroll to Top