Đối chiếu công nợ tự động bằng Power Query: 6 bước chi tiết

Cuối năm, mùa kiểm toán. Trên bàn là một chồng biên bản xác nhận công nợ khách hàng vừa gửi về — mỗi bản một kiểu: file Excel, bản scan, có bản còn viết tay. Nhiệm vụ của bạn: đối chiếu từng khách với bảng kê công nợ phải thu trên sổ công ty, tìm ra khách nào khớp, khách nào lệch số, khách nào gửi xác nhận mà sổ mình... không thấy đâu.

Cách quen thuộc của nhiều bạn kế toán: mở hai file cạnh nhau, kéo VLOOKUP qua rồi kéo ngược lại cho chắc, chỗ nào #N/A thì dò tay. Vài chục khách còn cố được. Vài trăm khách, lặp lại mỗi tháng, thì việc đối chiếu công nợ trở thành cơn ác mộng chiếm trọn mấy ngày đóng sổ.

Bài viết này hướng dẫn bạn cách làm khác: dùng Power Query — công cụ có sẵn trong Excel — để đối chiếu hai bảng công nợ tự động. Làm một lần, tháng sau chỉ cần thay file mới và bấm Refresh, danh sách lệch tự hiện ra.

Đối chiếu công nợ thủ công: quen tay nhưng đầy rủi ro

Cách dò bằng mắt kết hợp VLOOKUP hai chiều có mấy điểm yếu mà ai làm rồi cũng gặp:

• VLOOKUP chỉ nhìn một chiều. Kéo từ sổ công ty sang bảng xác nhận, bạn bắt được khách "có ở sổ nhưng khách không xác nhận". Nhưng khách "có trong xác nhận mà sổ không có" thì phải kéo ngược lại lần nữa — quên một chiều là sót.
• Mã khách hàng gõ không thống nhất. Bên sổ ghi KH001, bên khách gửi ghi kh001 kèm một khoảng trắng thừa — VLOOKUP trả #N/A, bạn tưởng khách "không có", trong khi thực ra chỉ là lệch định dạng.
• Một khách nhiều dòng. Sổ chi tiết theo hóa đơn, mỗi khách chục dòng, trong khi biên bản xác nhận chỉ có một số tổng — so dòng với dòng là sai ngay từ cách đặt vấn đề.
• Công thức dễ vỡ mà không ai biết. Ai đó chèn dòng, sort lại bảng, công thức trượt tham chiếu. Nghiên cứu của EuSpRIG (European Spreadsheet Risks Interest Group) cho thấy khoảng 50–86% bảng tính được kiểm tra có chứa lỗi — với số liệu mang đi đối chiếu cùng khách và kiểm toán, đó là rủi ro không nhỏ.

Power Query xử lý cả bốn vấn đề trên trong một luồng: làm sạch mã trước khi so, gộp dòng trước khi đối chiếu, so hai chiều cùng lúc bằng một lần Merge, và toàn bộ các bước được ghi lại để chạy lại y hệt mỗi kỳ.

Chuẩn bị 2 bảng dữ liệu

Kịch bản cụ thể chúng ta sẽ làm:

• Bảng 1 — Sổ công ty: mã khách hàng, tên khách hàng, số dư phải thu theo sổ.
• Bảng 2 — Xác nhận của khách: mã khách hàng, số dư khách xác nhận (tổng hợp từ các biên bản gửi về).

Hai lưu ý trước khi bắt đầu:

• Mỗi bảng nên là một vùng dữ liệu sạch: dòng đầu là tiêu đề cột, không gộp ô (merge cells), không dòng tổng chen giữa.
• Bảng xác nhận của khách bạn gom về một sheet — gõ tay từ biên bản giấy hoặc copy từ file khách gửi đều được, miễn có đủ hai cột mã và số dư xác nhận.

6 bước đối chiếu công nợ bằng Power Query

Bước 1: Nạp 2 bảng vào Power Query

Với từng bảng: click vào vùng dữ liệu, nhấn Ctrl + T để tạo Table (đặt tên gợi nhớ, ví dụ SoCongTy và XacNhanKH). Sau đó vào Data → From Table/Range để mở Power Query Editor. Với bảng thứ nhất, chọn Close & Load To… → Only Create Connection để query nằm chờ, chưa cần đổ ra sheet.

Bước 2: Làm sạch mã khách hàng ở cả 2 bảng

Đây là bước nhiều bạn bỏ qua rồi ngồi thắc mắc "sao hai mã giống hệt mà không khớp". Trong Power Query Editor, chọn cột mã khách hàng, vào Transform → Format và áp lần lượt:

1. Trim — cắt khoảng trắng thừa đầu/cuối;
2. Clean — loại ký tự ẩn (hay dính khi copy từ phần mềm kế toán, email);
3. UPPERCASE — đưa hết về chữ hoa, để kh001 và KH001 là một.

Làm giống nhau cho cột mã ở cả hai query. Merge trong Power Query phân biệt hoa thường và khoảng trắng — sạch trước, khớp sau.

Bước 3: Group By nếu một khách có nhiều dòng

Nếu sổ công ty đang chi tiết theo hóa đơn (một khách nhiều dòng), phải gộp về một dòng mỗi khách trước khi đối chiếu. Trong query SoCongTy, vào Home → Group By: nhóm theo cột mã khách hàng, phần Aggregation chọn Sum trên cột số dư. Kết quả: mỗi khách một dòng, số dư tổng — cùng "đơn vị" với số tổng trên biên bản xác nhận.

Bước 4: Merge Full Outer theo mã khách hàng

Đây là trái tim của bài. Vào Home → Merge Queries → Merge Queries as New:

1. Bảng trên chọn SoCongTy, bảng dưới chọn XacNhanKH;
2. Click chọn cột mã khách hàng ở cả hai bảng;
3. Ở Join Kind, chọn Full Outer (all rows from both) — lấy toàn bộ dòng của cả hai bên, kể cả khách chỉ xuất hiện ở một bảng.

Sau khi OK, bảng mới có một cột chứa dữ liệu bảng kia đang "gấp" lại — bấm nút mũi tên đôi trên tiêu đề cột đó (Expand), tick chọn cột mã và số dư xác nhận. Nếu bạn đã quen dùng Merge thay VLOOKUP thì bước này rất tự nhiên — còn nếu chưa, bạn có thể đọc thêm bài dùng Merge Queries thay VLOOKUP của đội ngũ EzTech.

Cách khác: Merge hai lần với Join Kind Left Anti (mỗi chiều một lần) để tách riêng "chỉ có ở sổ" và "chỉ có ở xác nhận" — nhưng phải quản ba query, nên với đối chiếu định kỳ, một lần Full Outer gọn hơn.

Bước 5: Thêm cột chênh lệch

Trước tiên, xử lý null: khách chỉ có một bên thì số dư bên kia đang trống. Chọn hai cột số dư, vào Transform → Replace Values, thay null bằng 0 (lưu ý: chỉ thay ở hai cột số dư, giữ nguyên null ở cột mã để bước sau còn phân loại).

Sau đó vào Add Column → Custom Column, đặt tên Chênh lệch, công thức:

= [Số dư sổ] - [Số dư xác nhận]

Bước 6: Phân loại 3 nhóm và đổ kết quả ra sheet

Vào Add Column → Conditional Column, đặt tên Kết quả, khai báo lần lượt:

1. Nếu cột mã bên xác nhận equals null → "Chỉ có ở sổ công ty";
2. Nếu cột mã bên sổ equals null → "Chỉ có ở xác nhận KH";
3. Nếu Chênh lệch equals 0 → "Khớp";
4. Còn lại (Else) → "Lệch số".

Cuối cùng Home → Close & Load để đổ bảng kết quả ra sheet. Bật filter ở cột Kết quả là bạn có ngay danh sách từng nhóm.

Đọc kết quả 3 nhóm và hướng xử lý

• Khớp — chênh lệch bằng 0, có mặt cả hai bên: lưu hồ sơ, xong. Thường đây là phần lớn danh sách — và bạn không phải nhìn lại từng dòng của nhóm này nữa.
• Lệch số — hai bên cùng có nhưng số dư khác nhau: nhóm cần soi. Nguyên nhân hay gặp: khác thời điểm chốt, hàng trả lại/chiết khấu một bên chưa hạch toán, tiền đang đi trên đường. Cột Chênh lệch giúp bạn ưu tiên: lệch lớn xử trước.
• Chỉ có một bên — "chỉ có ở sổ công ty": khách chưa gửi xác nhận (đòi biên bản) hoặc mã lệch chưa bắt được; "chỉ có ở xác nhận KH" đáng chú ý hơn: khách nói có nợ mà sổ không thấy — kiểm tra nhầm mã, nhầm công ty trong nhóm, hay sót nghiệp vụ.

Và đây là điểm ăn tiền của cả quy trình: tháng sau, quý sau, bạn không làm lại gì cả. Dán số liệu kỳ mới vào hai Table (hoặc trỏ query sang file mới), bấm Data → Refresh All — toàn bộ chuỗi làm sạch, Group By, Merge, phân loại chạy lại tự động, danh sách lệch mới hiện ra sau vài giây.

Cùng một cách, đối chiếu được rất nhiều thứ khác

Bộ khung "làm sạch khóa → Group By → Merge Full Outer → cột chênh lệch → phân loại" không chỉ dành cho công nợ phải thu. Bạn dùng y nguyên cho:

• Sổ phụ ngân hàng vs sổ quỹ tiền gửi — bắt các khoản ngân hàng đã ghi mà sổ chưa hạch toán (phí, lãi) và ngược lại;
• Bảng kê hóa đơn đầu vào vs dữ liệu tra cứu thuế — tìm hóa đơn kê thiếu, kê trùng;
• Tồn kho sổ sách vs kiểm kê thực tế — merge theo mã hàng, cột chênh lệch chính là thừa/thiếu kiểm kê;
• Công nợ phải trả — đảo vai, đối chiếu sổ mình với bảng kê nhà cung cấp.

Học một kỹ thuật, dùng cho cả chục nghiệp vụ đối chiếu — đó là lý do Power Query đáng để bạn đầu tư thời gian một cách nghiêm túc.

Câu hỏi thường gặp

Tôi không biết lập trình, có làm được không?

Được. Cả 6 bước đều thao tác bằng chuột qua menu — Power Query tự ghi lại thành Applied Steps. Công thức duy nhất phải gõ là phép trừ ở Bước 5. Không cần biết ngôn ngữ M hay VBA.

Mỗi tháng khách gửi file tên khác nhau, có phải làm lại query không?

Không. Cách đơn giản nhất: giữ nguyên file đối chiếu, mỗi kỳ dán dữ liệu mới đè vào hai Table rồi Refresh. Nếu muốn tự động hơn, bạn có thể dùng nguồn Data → Get Data → From Folder — thả file mới vào thư mục, query tự gom, không quan tâm tên file.

Sao hai mã nhìn giống hệt nhau mà Power Query vẫn báo lệch?

Gần như chắc chắn là ký tự ẩn hoặc hoa/thường — Merge trong Power Query so sánh tuyệt đối. Kiểm tra lại Bước 2: đã áp đủ Trim, Clean, UPPERCASE cho cột mã ở cả hai query chưa. Một thủ phạm khác: mã bên là số (123), bên là chữ ("123") — đưa cả hai cột về cùng kiểu Text trước khi Merge.

Dữ liệu vài chục nghìn dòng, Excel có kham nổi không?

Thoải mái. Power Query xử lý hàng trăm nghìn dòng nhẹ hơn nhiều so với bảng VLOOKUP tương đương, vì chỉ tính khi Refresh thay vì tính lại liên tục trên sheet. Dữ liệu lớn hơn nữa, cũng chính query này chuyển lên Power BI gần như nguyên vẹn.

Từ bảng đối chiếu đến bức tranh công nợ hoàn chỉnh

Đối chiếu xong mới chỉ là một nửa câu chuyện — nửa còn lại là nhìn được toàn cảnh: khách nào chiếm dụng vốn lâu nhất, nợ quá hạn đang phình ở nhóm tuổi nợ nào, thu tiền kỳ này nhanh hay chậm hơn kỳ trước. Bạn có thể xem hai báo cáo mẫu chạy trực tiếp của đội ngũ EzTech để hình dung: báo cáo công nợ phải thu (AR) và báo cáo quản lý công nợ — đều dựng từ chính loại dữ liệu bạn vừa xử lý bằng Power Query ở trên.

Còn nếu bạn muốn đi bài bản từ Power Query chuyên sâu trong Excel đến dựng báo cáo tài chính — quản trị trên Power BI, khóa DATA FINANCE của EzTech được thiết kế riêng cho người làm kế toán — tài chính: học trên nghiệp vụ thật, từ chuẩn hóa dữ liệu, đối chiếu, đến ra báo cáo tự động cập nhật. Kỳ đóng sổ tới, thay vì dò từng dòng, bạn chỉ cần bấm Refresh.