Đối chiếu công nợ với bảng kê của nhà cung cấp: tìm ra hoá đơn lệch bằng SUMIFS

ExcelNâng cao Video 2:49

Bài toán

Nhà cung cấp gửi bảng kê công nợ, họ đòi nhiều hơn sổ mình 37 triệu, và bạn dò từng hoá đơn hai bên.

Làm xong được gì

Ba nhóm hiện ra: hoá đơn lệch tiền, hoá đơn họ có mà mình chưa ghi, hoá đơn mình có mà họ chưa ghi; cộng lại đúng 37 triệu.

Dùng trong bài

SUMIFSCOUNTIFSIF lồngConditional Formatting

File thực hành gồm

DoiChieuCongNo_Q3.xlsx
Sheet BangKe là bảng kê công nợ quý 3 nhà cung cấp gửi sang (16 tờ hoá đơn), đã kẻ sẵn ba cột Sổ mình, Chênh, Tình trạng và bảng tổng hợp bên phải. Sheet SoMinh là sổ chi tiết công nợ phải trả xuất từ phần mềm, mỗi tờ hoá đơn hai dòng: tiền hàng và thuế.
DoiChieuCongNo_Q3_dap_an.xlsx
Bản đã gắn công thức, mở ra để so kết quả.

Các bước làm

1
Chốt số lệch giữa hai bên
Nhà cung cấp đòi 227.383.200, sổ mình còn nợ 190.382.400. Ở sheet BangKe, ô K13 (Số lệch hai bên) gõ công thức dưới đây, ra 37.000.800. Việc còn lại là chỉ ra 37 triệu này nằm ở những tờ nào.
=E29-SoMinh!H40
2
Cột Sổ mình: lấy tiền của từng tờ từ sổ sang
Ô F9 gõ SUMIFS: cộng cột phát sinh Có bên sổ mình, điều kiện là cột số hoá đơn bằng số ở dòng này. Enter, bấm lại F9 rồi nháy đúp nút fill ở góc ô để đổ xuống đủ 16 tờ. Sổ mình tách mỗi tờ thành hai dòng, SUMIFS gom cả hai về một số. Tờ bên họ ghi 00000844 vẫn ra đủ tiền dù sổ mình ghi 844, vì SUMIFS so theo con số.
=SUMIFS(SoMinh!G:G,SoMinh!C:C,C9)
3
Cột Chênh
Ô G9 lấy tiền bên họ trừ tiền sổ mình, rồi nháy đúp nút fill.
=E9-F9
4
Cột Tình trạng: gắn nhãn cho từng tờ
Ô H9 lồng hai hàm IF: chênh bằng 0 là Khớp, không thì xét sổ mình bằng 0 là Mình chưa ghi, còn lại là Lệch tiền. Đổ xuống xong có 13 tờ Khớp. Một tờ Lệch tiền ở dòng 17, chênh 2.700.000: họ ghi 36.396.000, sổ mình 33.696.000, bên họ gõ đảo số 3 với số 6. Hai tờ Mình chưa ghi xuất ngày 29/09 và 30/09, hàng về rồi mà hoá đơn chưa về tới kế toán.
=IF(G9=0,"Khớp",IF(F9=0,"Mình chưa ghi","Lệch tiền"))
5
Dò chiều ngược lại bên sổ mình
Sang sheet SoMinh, ô I5 (cột Bên NCC có) đếm số hoá đơn của dòng này trong bảng kê, rồi đổ xuống tới I38. Hàm IF bọc ngoài để dòng không có số hoá đơn (số dư đầu kỳ, các lần trả tiền) được để trống. Không chặn thì những dòng đó cũng ra 0, trông y như tờ bên họ chưa ghi.
=IF(C5="","",COUNTIFS(BangKe!C:C,C5))
6
Tô đỏ tờ bên họ sót
Bôi I5:I38, vào Home › Conditional Formatting › Highlight Cells Rules › Equal To, gõ 0. Hai dòng của tờ 860 ngày 14/07 đỏ lên: 9.763.200, bên họ sót không kê.
7
Bảng tổng hợp ba nhóm và ô kiểm
Về sheet BangKe. Ô K9 gõ =SUMIFS(G:G,H:H,J9) rồi kéo xuống K10 cho nhóm thứ hai. Ô K11 (nhóm họ chưa ghi) dùng công thức dưới đây, có dấu trừ đằng trước vì tờ họ sót làm số họ đòi thấp đi. Ô K12 bấm Alt+= để cộng ba nhóm, ra 37.000.800. Ô K14 gõ =K12-K13, ra 0: ba nhóm đã giải thích hết số lệch.
=-SUMIFS(SoMinh!G:G,SoMinh!I:I,0)

Lưu ý khi làm với file thật

SUMIFS, COUNTIFS so số hoá đơn theo con số nên 00000844 vẫn khớp 844. Ctrl+F thì không.
Lệch nằm ở tiền đã trả (tiền đi đường) hay hoá đơn điều chỉnh, thay thế thì đối chiếu thêm theo chứng từ thanh toán.

Các hàm trong bài có từ Excel 2010: Excel 2016, 2019, 2021, 365 đều làm được y hệt.

File thực hành của bài này

EzTech sẽ cập nhật link tải sau.

Dữ liệu trong bài là dữ liệu minh hoạ.

Bài học khác

Tất cả bài học