Cách Đối Chiếu Công Nợ Bằng Excel: So Sổ Hai Bên Bằng VLOOKUP Và COUNTIF

Trả lời nhanh: Muốn đối chiếu công nợ trên Excel, bạn chép sổ của mình và sổ do khách gửi vào hai sheet cùng cấu trúc, dùng VLOOKUP so số tiền theo số chứng từ, dùng COUNTIF tìm chứng từ chỉ có một bên, rồi SUMIF cộng ba nhóm chênh lệch. Làm đủ hai chiều thì tổng chênh lệch luôn giải thích được từng đồng.

Đến kỳ chốt công nợ, hai bên thường mỗi bên một con số. Ngồi dò từng dòng bằng mắt vừa chậm vừa sót. Bài này hướng dẫn dựng một bảng đối chiếu để Excel chỉ ra dòng nào khớp, dòng nào lệch tiền, dòng nào chỉ một bên ghi. Số liệu trong ví dụ là giả định để minh họa.

Vì sao phải đối chiếu hai chiều?

Nhiều người chỉ lấy sổ của mình rồi tra sang sổ khách. Cách đó phát hiện được dòng lệch tiền và dòng bên mình có mà khách không có, nhưng bỏ sót dòng khách ghi mà mình chưa ghi. Vì vậy cần hai lượt:

• Lượt 1: từng chứng từ trong sổ của bạn, tra sang sổ khách. Kết quả: Khớp, Lệch số tiền hoặc Khách chưa ghi.
• Lượt 2: từng chứng từ trong sổ khách, kiểm tra có trong sổ của bạn không. Kết quả: Đã có bên mình hoặc Mình chưa ghi.

Khớp theo số chứng từ (số hóa đơn, số phiếu) chứ không khớp theo tên khách hay ngày, vì số chứng từ là thứ duy nhất không trùng nhau.

Hai sheet dữ liệu và công thức so khớp

Tạo hai sheet tên SoCuaToi và SoKhach, cùng cấu trúc: cột A là số chứng từ, cột B là ngày, cột C là số tiền. Dữ liệu từ dòng 2, công thức kéo xuống đến dòng 200.

Dữ liệu giả định của một khách:

Chứng từ	Sổ của bạn	Sổ khách
HD001	5.000.000đ	5.000.000đ
HD002	3.200.000đ	3.000.000đ
HD003	1.500.000đ	không có
HD004	2.000.000đ	2.000.000đ
HD005	không có	800.000đ

Sheet SoCuaToi, thêm ba cột công thức cho dòng 2:

• D2 (số tiền bên khách): =IF($A2="","",IFERROR(VLOOKUP($A2,SoKhach!$A$2:$C$200,3,FALSE),"Không có"))
• E2 (chênh lệch): =IF($A2="","",IF(ISNUMBER($D2),$C2-$D2,""))
• F2 (kết quả): =IF($A2="","",IF(NOT(ISNUMBER($D2)),"Khách chưa ghi",IF($E2=0,"Khớp","Lệch số tiền")))

VLOOKUP tìm số chứng từ ở A2 trong cột A của sổ khách và lấy số tiền ở cột thứ 3. Nếu không tìm thấy, IFERROR trả về chữ "Không có", và cột E, F dựa vào đó để biết dòng này thuộc nhóm nào.

Sheet SoKhach, thêm một cột kết quả cho dòng 2:

• D2: =IF($A2="","",IF(COUNTIF(SoCuaToi!$A$2:$A$200,$A2)=0,"Mình chưa ghi","Đã có bên mình"))

Với dữ liệu trên, sheet SoCuaToi cho: HD001 Khớp, HD002 Lệch số tiền (chênh lệch 200.000), HD003 Khách chưa ghi, HD004 Khớp. Sheet SoKhach cho HD005 là Mình chưa ghi, các dòng còn lại là Đã có bên mình.

Tổng hợp chênh lệch thành ba nhóm như thế nào?

Tạo sheet DoiChieu với các ô tổng hợp:

Ô	Ý nghĩa	Công thức
B1	Tổng sổ của bạn	=SUM(SoCuaToi!$C$2:$C$200)
B2	Tổng sổ khách	=SUM(SoKhach!$C$2:$C$200)
B3	Chênh lệch tổng	=B1-B2
B5	Lệch số tiền	=SUMIF(SoCuaToi!$F$2:$F$200,"Lệch số tiền",SoCuaToi!$E$2:$E$200)
B6	Chỉ bên bạn ghi	=SUMIF(SoCuaToi!$F$2:$F$200,"Khách chưa ghi",SoCuaToi!$C$2:$C$200)
B7	Chỉ khách ghi	=SUMIF(SoKhach!$D$2:$D$200,"Mình chưa ghi",SoKhach!$C$2:$C$200)
B8	Phần chưa giải thích	=B3-(B5+B6-B7)

Với dữ liệu giả định: B1 là 11.700.000, B2 là 10.800.000, nên B3 là 900.000. Ba nhóm giải thích như sau: lệch số tiền 200.000 (HD002), chỉ bên bạn ghi 1.500.000 (HD003), chỉ khách ghi 800.000 (HD005). Cộng 200.000 + 1.500.000 - 800.000 ra đúng 900.000 nên B8 bằng 0.

B8 là ô kiểm tra tự thân. Nếu B8 khác 0 nghĩa là còn dòng bị bỏ sót, thường do trùng số chứng từ hoặc lỗi dữ liệu mô tả ở mục sau.

Tô màu để nhìn nhanh: chọn vùng A2:F200 của SoCuaToi, vào Conditional Formatting, nhập =AND($F2<>"",$F2<>"Khớp") rồi đặt nền vàng. Làm tương tự ở SoKhach với điều kiện =$D2="Mình chưa ghi".

Lỗi thường gặp khi đối chiếu

• Thừa dấu cách trong số chứng từ. "HD001" và "HD001 " bị coi là khác nhau nên VLOOKUP báo không có. Thêm một cột phụ =TRIM(A2) và đối chiếu theo cột đó, hoặc dọn dữ liệu bằng Find and Replace.
• Số lẫn với chữ. Một bên nhập số chứng từ là 1001 (số), bên kia là "1001" (chữ). Chọn cả cột, đổi cùng một định dạng rồi nhập lại cho giống nhau.
• Trùng số chứng từ. VLOOKUP luôn lấy dòng đầu tiên gặp. Kiểm tra trùng bằng =COUNTIF($A$2:$A$200,A2) và tô đỏ khi lớn hơn 1.
• Số tiền khách gửi là chữ. File khách xuất ra có thể có số căn trái hoặc chứa ký hiệu đ. Nếu cột E trả về lỗi hoặc toàn chữ, chuyển cột C về dạng số trước khi đối chiếu.
• Quên mở rộng vùng. Công thức đang tìm đến dòng 200. Khi sổ dài hơn, mở rộng $200 thành số lớn hơn ở mọi công thức.

Bảng đối chiếu chỉ giúp bạn tìm ra dòng cần hỏi lại khách nhanh hơn. Nó không thay cho văn bản xác nhận giữa hai bên. Nếu cần giá trị chứng từ hoặc liên quan đến kế toán, thuế, hãy hỏi kế toán hoặc người hiểu quy định.

Khi nào nên chuyển sang công cụ có sẵn?

Cách trên hợp khi bạn đối chiếu thỉnh thoảng với vài khách. Nếu công nợ phát sinh mỗi ngày với nhiều khách và nhà cung cấp, việc chép sổ sang hai sheet mỗi kỳ sẽ nhanh chóng trở thành việc tốn thời gian nhất. Lúc đó nên ghi công nợ ngay từ lúc bán hàng. Bạn có thể bắt đầu bằng Template Quản Lý Công Nợ Khách Hàng, hoặc đọc thêm bài quản lý công nợ phải thu, phải trả trên Google Sheets.

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

Đối chiếu công nợ bằng Excel dùng hàm nào?

Dùng VLOOKUP để lấy số tiền của cùng một số chứng từ ở sổ bên kia, COUNTIF để kiểm tra chứng từ có tồn tại ở bên kia hay không, và SUMIF để cộng riêng từng nhóm chênh lệch. Cách này chạy được trên mọi phiên bản Excel và Google Sheets.

Vì sao tổng hai bên lệch nhau nhưng từng dòng đều khớp?

Thường do có chứng từ chỉ một bên ghi. Khi đó VLOOKUP không tìm thấy dòng tương ứng nên dòng đó không được so sánh. Cần đối chiếu hai chiều: sổ của bạn so với sổ khách, rồi sổ khách so ngược lại với sổ của bạn.

Làm sao tránh lỗi VLOOKUP không tìm thấy dù số chứng từ nhìn giống hệt?

Nguyên nhân phổ biến là thừa dấu cách ở đầu hoặc cuối, hoặc một bên là số còn bên kia là chữ. Dùng hàm TRIM để bỏ dấu cách thừa và nhập số chứng từ cùng một kiểu dữ liệu ở cả hai sheet.

Bảng đối chiếu có thay được biên bản xác nhận công nợ không?

Không. Bảng đối chiếu chỉ giúp bạn tìm ra dòng lệch nhanh hơn. Văn bản xác nhận giữa hai bên là việc riêng, bạn nên hỏi kế toán hoặc người hiểu quy định nếu cần giá trị chứng từ.