Làm báo cáo tuổi nợ phải thu tự chạy mỗi tháng bằng Excel

ExcelNâng caoMiễn phí
1HÀM, CHỨC NĂNG

LOOKUP · SUMIFS · F4 · Ctrl+Enter

2TƯ DUY, PHƯƠNG PHÁP

Lấy một ô ngày chốt trừ hạn thanh toán ra số ngày quá hạn, xếp nhóm bằng LOOKUP theo bảng mốc, rồi cộng tiền theo nhóm bằng SUMIFS.

3TÌNH HUỐNG

Công nợ phải thu: báo cáo tuổi nợ cuối tháng

Bài toán

Cuối tháng sếp hỏi: khách nào nợ quá hạn, quá bao lâu, tổng bao nhiêu tiền. Làm tay thì tháng nào cũng phải đếm lại ngày.

Làm xong được gì: Mỗi hóa đơn có số ngày quá hạn và nhóm tuổi nợ, bảng tổng tiền theo 5 nhóm. Tháng sau chỉ đổi ô ngày chốt là báo cáo tự chạy lại.

File thực hành

Sheet TuoiNo: ô C3 là ngày chốt sổ; bảng A5:E17 gồm 12 hóa đơn còn nợ; bảng mốc I5:J10; bảng tổng hợp I13:J18 còn trống.

Bộ file gồm file đề để làm theo video và file đáp án để so kết quả.

👉 Vào nhóm Zalo để tải file

Các bước làm

1Số ngày quá hạn

Bước một, số ngày quá hạn bằng ngày chốt trừ hạn thanh toán. Bấm F4 để khóa ô ngày chốt. Số âm là chưa đến hạn. Số dương là đã trễ bấy nhiêu ngày.

=$C$3-D6

2Xếp nhóm tuổi nợ

Bước hai, xếp nhóm. Bên phải là bảng mốc: từ một ngày là nhóm một đến ba mươi, từ ba mươi mốt là nhóm tiếp theo. Hàm LOOKUP lấy số ngày quá hạn, dò trong cột mốc, trả về tên nhóm tương ứng.

=LOOKUP(F6,$I$6:$I$10,$J$6:$J$10)

3Cộng tiền theo nhóm

Bước ba, cộng tiền theo từng nhóm bằng SUMIFS: cộng cột còn nợ, với điều kiện cột nhóm bằng tên nhóm bên trái. Dòng tổng phải bằng tổng còn nợ của bảng hóa đơn, như vậy là không sót tờ nào.

=SUMIFS($E$6:$E$17,$G$6:$G$17,I14)

4Tháng sau tự chạy

Sang tháng sau, chỉ sửa ô ngày chốt thành ngày cuối tháng mới. Số ngày, nhóm và bảng tổng đều tự tính lại. Tiền dồn dần sang các nhóm quá hạn lâu hơn.

=DATE(2026,11,30)

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

  • Bảng mốc phải xếp tăng dần ở cột Từ ngày thì LOOKUP mới tra đúng.
  • Muốn đổi cách chia nhóm (ví dụ thêm nhóm trên 180 ngày) chỉ cần thêm dòng vào bảng mốc.
  • Ô ngày chốt nên gõ bằng hàm DATE để không bị nhầm ngày tháng giữa các máy.
Muốn học Excel bài bản cùng AI?

Khóa AI EXCEL PRO học trực tiếp qua Zoom với Thầy Đạt: làm quen công cụ AI, tự động hóa báo cáo, xử lý dữ liệu nhanh gấp nhiều lần.

Xem khóa họcNhắn Zalo Thầy 0396.523.865

Bài học khác

Dữ liệu trong bài là dữ liệu minh họa. Xem tất cả bài học.

Lên đầu trang