Báo cáo tuổi nợ phải thu bằng Excel: 1 công thức ra cả bảng, tháng sau tự chạy

Làm xong được gì

Báo cáo tuổi nợ 5 nhóm; tháng sau đổi một ô ngày là cả bảng chạy lại.

Dùng trong bài

File thực hành gồm

• de.xlsx

  Sheet CongNo là danh sách hoá đơn còn nợ xuất từ phần mềm kế toán: 196 hoá đơn của 19 khách, mỗi dòng có hạn thanh toán và số còn phải thu. Sheet TuoiNo có ô ngày báo cáo B2 (đang là 30/09/2026), bảng mốc tuổi nợ BangMoc ở J6:K10 và khung báo cáo chưa có công thức.

• dap_an.xlsx

  Bản đã làm xong, mở ra để so kết quả.

Các bước làm

1. 1

   Biến danh sách hoá đơn thành Table

   Ở sheet CongNo, bấm một ô bất kỳ trong vùng dữ liệu rồi bấm Ctrl+T. Excel tự nhận đúng vùng và dòng tiêu đề, bấm OK. Sang tab Table Design, ô Table Name gõ HoaDon (viết liền, không dấu) rồi Enter.

2. 2

   Đặt tên cho ô ngày báo cáo

   Sang sheet TuoiNo, bấm ô B2. Bấm vào Name Box ở góc trái thanh công thức, gõ NgayBaoCao rồi Enter. Từ đây sheet nào cũng gọi được ô này bằng tên.

3. 3

   Thêm cột Số ngày quá hạn

   Về sheet CongNo, ô J4 gõ tiêu đề Số ngày quá hạn rồi Enter. Bảng tự nới thêm một cột. Ô J5 gõ =NgayBaoCao- rồi bấm vào ô hạn thanh toán cùng dòng (F5), Enter. Cả cột tự điền, không cần kéo công thức. Số âm là hoá đơn chưa tới hạn.

   =NgayBaoCao-[@[Hạn thanh toán]]
4. 4

   Thêm cột Nhóm tuổi nợ bằng XLOOKUP

   Ô K4 gõ tiêu đề Nhóm tuổi nợ, ô K5 gõ công thức dưới đây. Giá trị cần tìm là số ngày quá hạn của dòng. Tìm trong cột Từ ngày của bảng mốc, trả về cột Nhóm. Không tìm thấy thì ghi Chưa đến hạn. Tham số cuối -1 nghĩa là không có số khớp đúng thì lấy mốc nhỏ hơn gần nhất. Hoá đơn ở dòng 6 quá hạn 40 ngày, mốc nhỏ hơn gần nhất là 31 nên rơi vào nhóm 31–60 ngày.

   =XLOOKUP([@[Số ngày quá hạn]],BangMoc[Từ ngày],BangMoc[Nhóm],"Chưa đến hạn",-1)
5. 5

   Danh sách khách bằng UNIQUE và SORT

   Sang sheet TuoiNo. Ô A7 gõ công thức dưới đây: UNIQUE bỏ mã trùng, SORT xếp theo thứ tự. Enter, 19 mã khách bung xuống tới A25. Ô B7 gõ =XLOOKUP(A7#,HoaDon[Mã KH],HoaDon[Tên khách hàng]) để lấy tên. Dấu # sau A7 nghĩa là lấy cả vùng kết quả bung ra từ ô A7: danh sách dài bao nhiêu, cột tên dài theo bấy nhiêu.

   =SORT(UNIQUE(HoaDon[Mã KH]))
6. 6

   Một công thức SUMIFS ra cả ma trận

   Ô C7 gõ công thức dưới đây. Cột cần cộng là Còn phải thu. Điều kiện một: cột Mã KH bằng danh sách A7#, xếp theo chiều dọc. Điều kiện hai: cột Nhóm tuổi nợ bằng dòng tiêu đề C6:G6, xếp theo chiều ngang. Enter, 19 khách nhân 5 nhóm ra 95 ô số cùng lúc.

   =SUMIFS(HoaDon[Còn phải thu],HoaDon[Mã KH],A7#,HoaDon[Nhóm tuổi nợ],C6:G6)
7. 7

   Các ô tổng và ô kiểm tra

   Ô H7 (tổng theo khách) gõ =SUMIFS(HoaDon[Còn phải thu],HoaDon[Mã KH],A7#). Ô C5 (tổng theo nhóm) gõ =SUMIFS(HoaDon[Còn phải thu],HoaDon[Nhóm tuổi nợ],C6:G6). Dòng tổng này nằm phía trên tiêu đề: đặt bên dưới thì danh sách dài ra sẽ đụng vùng bung mảng và báo lỗi #SPILL!. Ô H5 gõ =SUM(HoaDon[Còn phải thu]) để lấy tổng từ dữ liệu gốc. Ô E2 gõ công thức kiểm tra dưới đây, ra 0 là không sót hoá đơn nào.

   =SUM(C7#)-H5
8. 8

   Tô màu nợ trên 90 ngày và thanh dữ liệu cho cột tổng

   Gõ G7:G60 vào Name Box rồi Enter để chọn cột Trên 90 ngày, chọn rộng ra để khách mới của tháng sau cũng được tô. Vào Home › Conditional Formatting › Highlight Cells Rules › Greater Than, nhập 0, Enter. Khách nào còn nợ trên 90 ngày thì ô đỏ lên. Chọn tiếp H7:H60, vào Home › Conditional Formatting › Data Bars, chọn màu xanh.

9. 9

   Đọc báo cáo và thử đổi ngày

   Tổng còn phải thu là 15.079.460.000, trong đó 2.693.045.000 đã quá hạn trên 90 ngày. Hai khách Hoá chất Tân Khoa (KH13) và Cơ khí Đông Lâm (KH05) chiếm hơn 84% số nợ trên 90 ngày. Thử bấm ô B2, gõ 2026-10-31 rồi Enter: cả báo cáo tính lại, nợ trên 90 ngày tăng lên 4.222.779.000.

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

• Ngày báo cáo gõ theo kiểu năm-tháng-ngày (2026-10-31) để máy cài kiểu ngày nào cũng hiểu là ngày.
• Tháng sau dán hoá đơn mới nối vào cuối bảng HoaDon rồi đổi ngày báo cáo. Có khách mới thì danh sách và ma trận tự dài thêm.

Bài dùng XLOOKUP, UNIQUE, SORT và công thức bung mảng nên cần Excel 365 hoặc Excel 2021 trở lên.

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

Bài học khác

• Phân bổ chi phí chung cho từng cửa hàng theo doanh thu bằng Excel, xem cửa hàng nào lãi thậtHướng dẫn kế toán phân bổ chi phí chung cho từng cửa hàng theo doanh thu
• Tách hoá đơn bán ra theo thuế suất 0%, 5%, 8%, 10% để soát tờ khai thuế GTGT bằng ExcelHướng dẫn kế toán tách hoá đơn bán ra theo thuế suất để soát tờ khai thuế GTGT
• Bắt hoá đơn đầu vào nhập trùng bằng Excel: tô đỏ ngay khi gõHướng dẫn kế toán tự tô đỏ hoá đơn đầu vào nhập trùng
• Phần mềm báo âm kho khi tính giá xuất? Tìm đúng phiếu làm âm bằng ExcelHướng dẫn kế toán tìm đúng phiếu làm âm kho trước khi tính giá xuất kho