Đối Chiếu 2 File 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, việc xử lý dữ liệu trên Excel đã trở thành một kỹ năng thiết yếu cho nhiều ngành nghề: tài chính, kế toán, marketing, nhân sự, quản lý dự án, và thậm chí cả trong các công việc nghiên cứu khoa học. Một trong những nhu cầu phổ biến nhất là đối chiếu 2 file Excel – so sánh, kiểm tra sự khác biệt, tìm kiếm dữ liệu trùng lặp hoặc thiếu sót giữa hai bảng tính. Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từ những bước cơ bản nhất đến các kỹ thuật nâng cao, giúp bạn tự tin thực hiện việc đối chiếu một cách nhanh chóng và chính xác.

Có thể bạn quan tâm: Định Dạng Văn Bản Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Vì sao cần đối chiếu 2 file Excel?

1.1. Đảm bảo tính toàn vẹn dữ liệu

Khi dữ liệu được nhập vào từ nhiều nguồn khác nhau (hệ thống ERP, CRM, các file CSV, hay thậm chí là nhập tay), khả năng xảy ra lỗi, trùng lặp hoặc thiếu sót là điều không thể tránh khỏi. Việc đối chiếu giúp phát hiện sớm các vấn đề này, tránh hậu quả nghiêm trọng như báo cáo sai lệch, quyết định kinh doanh không chính xác.

1.2. So sánh các phiên bản

Trong quá trình làm việc nhóm, cùng một file Excel có thể được cập nhật bởi nhiều người. Khi muốn hợp nhất các thay đổi, việc đối chiếu phiên bản là cách tốt nhất để biết được ai đã thay đổi gì và quyết định giữ lại phiên bản nào.

1.3. Kiểm tra dữ liệu nhập khẩu/ xuất khẩu

Các doanh nghiệp thường phải nhập khẩu dữ liệu từ các hệ thống bên ngoài hoặc xuất khẩu dữ liệu cho đối tác. Đối chiếu giúp xác nhận rằng dữ liệu đã được chuyển đúng, không bị mất mát hay sai lệch.

Có thể bạn quan tâm: Cách Xóa Định Dạng Bảng Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Các phương pháp đối chiếu 2 file Excel

2.1. Sử dụng hàm VLOOKUP / XLOOKUP (hoặc HLOOKUP)

2.1.1. Nguyên tắc hoạt động

• VLOOKUP (Vertical Lookup) tìm kiếm một giá trị trong cột đầu tiên của một bảng và trả về giá trị tương ứng trong một cột khác của cùng một hàng.
• XLOOKUP (từ Excel 365/2026 trở lên) là phiên bản nâng cấp, cho phép tìm kiếm theo cả chiều dọc và chiều ngang, hỗ trợ tìm kiếm ngược, trả về giá trị mặc định khi không tìm thấy.

2.1.2. Ví dụ thực tế

Giả sử có File A chứa danh sách khách hàng và File B chứa danh sách đơn hàng. Bạn muốn kiểm tra xem mỗi khách hàng trong File A có tồn tại trong File B hay không.

Bước 1: Mở cả hai file, sao chép cột “Mã KH” từ File B vào một sheet tạm thời trong File A (hoặc ngược lại).
Bước 2: Trong File A, tại cột mới (ví dụ cột D), nhập công thức:

=IFERROR(VLOOKUP(A2, SheetB!$A$2:$A$1000, 1, FALSE), "Không tồn tại")

Hoặc nếu dùng XLOOKUP:

=XLOOKUP(A2, SheetB!$A$2:$A$1000, SheetB!$A$2:$A$1000, "Không tồn tại", 0)

Kéo công thức xuống để áp dụng cho toàn bộ danh sách. Kết quả sẽ cho bạn biết mỗi mã khách hàng có xuất hiện trong danh sách đơn hàng hay không.

2.2. Sử dụng Conditional Formatting (Định dạng có điều kiện)

2.2.1. Khi nào dùng?

Khi bạn muốn đánh dấu trực quan các giá trị trùng lặp hoặc khác nhau giữa hai cột mà không cần tạo cột công thức phụ.

2.2.2. Hướng dẫn

1. Chọn cột (hoặc dải ô) trong File A mà bạn muốn so sánh, ví dụ A2:A1000.
2. Vào Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Nhập công thức:

excel =COUNTIF(Book2.xlsxSheet1!$A$2:$A$1000, A2)=0

(Giải thích: Hàm COUNTIF sẽ đếm số lần xuất hiện của giá trị trong A2 trong dải ô của File B. Nếu kết quả bằng 0 → không tồn tại, sẽ được định dạng.)

1. Chọn định dạng màu nền (ví dụ: màu đỏ) và OK.
2. Các ô không tồn tại trong File B sẽ được tô màu, giúp bạn nhận diện nhanh.

2.3. Sử dụng Power Query (Get & Transform)

2.3.1. Lý do nên dùng Power Query

• Xử lý dữ liệu lớn: Power Query có thể làm việc với hàng triệu dòng mà không làm chậm Excel.
• Tự động hoá: Bạn có thể lưu lại các bước chuyển đổi và chỉ cần refresh khi dữ liệu thay đổi.
• Kết hợp nhiều nguồn: Kết nối trực tiếp tới các file CSV, database, web API, v.v.

2.3.2. Quy trình cơ bản

1. Data → Get Data → From File → From Workbook và chọn File A.
2. Chọn bảng hoặc sheet cần đối chiếu, nhấn Transform Data.
3. Lặp lại bước 1‑2 cho File B.
4. Trong Power Query Editor, sử dụng Merge Queries (nối bảng) để so sánh.
5. Chọn Home → Merge Queries.
6. Chọn cột chung (ví dụ “Mã KH”) ở cả hai bảng.
7. Chọn kiểu Left Anti Join để lấy các bản ghi chỉ tồn tại trong bảng đầu (File A) mà không có trong bảng thứ hai (File B).
8. Nhấn OK, sau đó Close & Load để đưa kết quả trở lại Excel.

2.3.3. Các kiểu Join thường dùng

2.4. Sử dụng VBA (Macro)

2.4.1. Khi nào cần VBA?

• Khi công việc lặp lại nhiều lần, yêu cầu tự động hoá hoàn toàn.
• Khi muốn tùy biến giao diện, ví dụ tạo một nút “Đối chiếu” trên thanh công cụ.
• Khi cần xử lý logic phức tạp mà hàm Excel không hỗ trợ (ví dụ: so sánh nhiều cột cùng lúc, áp dụng nhiều tiêu chí).

2.4.2. Mã mẫu: Đối chiếu 2 cột và đánh dấu màu

Sub CompareTwoSheets() Dim wsA As Worksheet, wsB As Worksheet Dim rngA As Range, rngB As Range Dim cell As Range, foundCell As Range Set wsA = ThisWorkbook.Worksheets("Sheet1") 'File A Set wsB = ThisWorkbook.Worksheets("Sheet2") 'File B Set rngA = wsA.Range("A2:A" & wsA.Cells(wsA.Rows.Count, "A").End(xlUp).Row) Set rngB = wsB.Range("A2:A" & wsB.Cells(wsB.Rows.Count, "A").End(xlUp).Row) Application.ScreenUpdating = False For Each cell In rngA Set foundCell = rngB.Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole) If foundCell Is Nothing Then cell.Interior.Color = RGB(255, 199, 199) 'Màu đỏ nhạt cho không tồn tại Else cell.Interior.Color = RGB(199, 255, 199) 'Màu xanh nhạt cho tồn tại End If Next cell Application.ScreenUpdating = True MsgBox "Đối chiếu hoàn tất!", vbInformation
End Sub

• Cách chạy: Nhấn ALT + F11 → Insert → Module → Dán code → chạy macro (F5).
• Lưu ý: Đảm bảo macro được bật (File → Options → Trust Center → Trust Center Settings → Macro Settings).

2.5. Sử dụng Add‑ins và công cụ bên thứ ba

• Ablebits Duplicate Remover: Giúp tìm và xóa dữ liệu trùng lặp nhanh chóng.
• Kutools for Excel: Cung cấp hàng trăm tiện ích, trong đó có “Compare Sheets” cho phép so sánh toàn bộ sheet chỉ trong vài cú click.
• Excel Compare (Microsoft): Dành cho nhà phát triển, so sánh nội dung file .xlsx, .xlsm, .xlsb.

Có thể bạn quan tâm: Đếm Dấu “x” Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

3. Bước chuẩn bị trước khi đối chiếu

3.1. Kiểm tra cấu trúc dữ liệu

• Định dạng cột: Đảm bảo các cột dùng để so sánh có cùng kiểu dữ liệu (text, number, date). Nếu không, Excel có thể coi “00123” và “123” là khác nhau.
• Xóa khoảng trắng thừa: Dùng hàm TRIM để loại bỏ khoảng trắng ở đầu/cuối chuỗi.
• Chuẩn hoá chữ hoa/thường: Dùng UPPER hoặc LOWER để đồng nhất.

3.2. Xác định “khóa chính” (Primary Key)

Có thể bạn quan tâm: Định Lượng Món Ăn Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

• Đối chiếu dựa trên một hoặc nhiều cột duy nhất để xác định mỗi bản ghi. Ví dụ: “Mã KH + Ngày giao dịch”.
• Nếu không có khóa duy nhất, bạn có thể tạo một cột mới bằng công thức hợp nhất: =A2&B2&C2.

3.3. Lưu bản sao (Backup)

• Trước khi thực hiện bất kỳ thao tác nào (đặc biệt là VBA), luôn sao lưu file gốc để tránh mất mát dữ liệu.

4. Thực hành: Một kịch bản thực tế

4.1. Bối cảnh

Bạn là nhân viên kế toán, cần đối chiếu bảng chi phí thực tế (File “ChiPhi.xlsx”) với bảng dự toán (File “DuToan.xlsx”). Mỗi bảng có các cột:
– Mã dự án (ProjectID)
– Ngày (Date)
– Hạng mục (Category)
– Số tiền (Amount)

Yêu cầu:
1. Xác định các mục chi phí thực tế chưa có trong dự toán.
2. Tìm các mục dự toán chưa được chi thực tế.
3. Tổng hợp chênh lệch số tiền.

4.2. Bước 1: Chuẩn bị dữ liệu

• Mở cả hai file, tạo một sheet “Temp” trong “ChiPhi.xlsx”.
• Sao chép cột ProjectID, Date, Category từ “DuToan.xlsx” vào “ChiPhi.xlsx” (Sheet “Temp”).
• Thêm cột “Key” ở cả hai bảng: =A2&B2&C2 (nối ProjectID, Date, Category). Đảm bảo không có khoảng trắng.

4.3. Bước 2: Đối chiếu bằng Power Query

1. Data → Get Data → From Workbook → Chọn “ChiPhi.xlsx”, chọn sheet “ChiPhi”.
2. Lặp lại cho “DuToan.xlsx”, chọn sheet “DuToan”.
3. Trong Power Query, tạo cột “Key” cho cả hai bảng (Add Column → Custom Column).
4. Merge Queries:
5. Merge “ChiPhi” (left) với “DuToan” (right) bằng cột “Key”.
6. Chọn Full Outer Join để lấy tất cả bản ghi.
7. Kết quả sẽ có các cột:
8. Key
9. Amount_ChiPhi (từ bảng chi phí)
10. Amount_DuToan (từ bảng dự toán)
11. Thêm cột tính chênh lệch: = Amount_ChiPhi - Amount_DuToan.
12. Lọc các hàng:
13. Chi phí chưa dự toán: Amount_DuToan = null.
14. Dự toán chưa chi: Amount_ChiPhi = null.
15. Close & Load → tạo bảng tổng hợp trên một sheet mới.

4.4. Bước 3: Đánh dấu bằng Conditional Formatting

• Trên sheet kết quả, áp dụng định dạng màu xanh cho các dòng có Amount_ChiPhi > Amount_DuToan (chi phí vượt dự toán) và màu đỏ cho ngược lại.

4.5. Bước 4: Báo cáo nhanh

• Dùng PivotTable để tổng hợp:
• Rows: ProjectID
• Values: Sum of Amount_ChiPhi, Sum of Amount_DuToan, Sum of Difference
• Thêm slicer cho Date hoặc Category để lọc nhanh.

Kết quả cuối cùng: Bạn có một báo cáo trực quan, cho biết dự toán đã thực hiện tới mức nào, những khoản chưa được chi, và tổng chênh lệch.

5. Những lỗi thường gặp và cách khắc phục

6. Tips và thủ thuật nâng cao

6.1. Sử dụng hàm FILTER (Excel 365)

=FILTER(A2:C1000, COUNTIF(D2:D500, A2:A1000)=0, "Không có")

• Lọc ra các bản ghi trong A2:C1000 mà không xuất hiện trong danh sách D2:D500.

6.2. Dùng LET để tối ưu công thức dài

=LET( key, A2&B2&C2, lookupRange, SheetB!$D$2:$D$1000, IF(COUNTIF(lookupRange, key)=0, "Missing", "Exists")
)

• Giúp công thức dễ đọc và tăng tốc tính toán.

6.3. Kết hợp Dynamic Arrays và XMATCH

=IFERROR( INDEX(SheetB!$E$2:$E$1000, XMATCH(A2, SheetB!$A$2:$A$1000, 0)), "Không tồn tại"
)

• XMATCH trả về vị trí chính xác, nhanh hơn VLOOKUP trong nhiều trường hợp.

6.4. Tự động refresh Power Query khi file nguồn thay đổi

• Trong Power Query Editor, vào Query Properties → Tick “Refresh data when opening the file”.
• Hoặc dùng VBA:

Private Sub Workbook_Open() ThisWorkbook.RefreshAll
End Sub

6.5. Đối chiếu nhiều cột cùng lúc

• Tạo cột “Key” bằng cách nối tất cả các cột cần so sánh: =TEXTJOIN("|",TRUE,A2:C2).
• Sau đó thực hiện VLOOKUP/XLOOKUP hoặc Merge Queries dựa trên cột “Key”.

7. Kết luận

Việc đối chiếu 2 file Excel không chỉ là một thao tác đơn thuần mà còn là một quá trình quan trọng trong quản lý dữ liệu, giúp nâng cao độ chính xác và tin cậy của các báo cáo kinh doanh. Từ các hàm cơ bản như VLOOKUP/XLOOKUP, Conditional Formatting, đến các công cụ mạnh mẽ như Power Query, VBA, và các add‑ins chuyên dụng, bạn có thể lựa chọn phương pháp phù hợp nhất với khối lượng công việc và mức độ phức tạp của dữ liệu.

Hãy nhớ rằng, chuẩn bị dữ liệu sạch sẽ, xác định khóa chính rõ ràng và luôn sao lưu bản gốc là những yếu tố then chốt để quá trình đối chiếu diễn ra suôn sẻ. Khi đã nắm vững các kỹ thuật trên, bạn sẽ giảm thiểu tối đa thời gian xử lý, tránh được các sai sót đáng tiếc và có thể tập trung hơn vào việc phân tích, đưa ra quyết định chiến lược dựa trên dữ liệu.

Chúc bạn thành công trong mọi dự án đối chiếu dữ liệu! 🚀

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