Báo cáo tuổi nợ phải thu tự cập nhật bằng Power Query: thả file tháng mới, Refresh là xong

Power QueryNâng cao Video 3:06

Bài toán

Sổ chi tiết công nợ phải thu xuất phần mềm mỗi tháng một file; hoá đơn tháng sáu mà tháng chín khách mới trả là nợ một file, tiền một file. Muốn biết khách nào nợ quá 90 ngày là mở từng file, cộng nợ, trừ tiền, đếm ngày, dán tay.

Làm xong được gì

Bảng tuổi nợ mỗi khách một dòng, 5 nhóm trong hạn / 1-30 / 31-60 / 61-90 / trên 90 ngày (chốt 30/09); thả sổ tháng mới vào thư mục, Refresh All là tiền khách vừa trả tự trừ đúng hoá đơn.

Dùng trong bài

From FolderRemove EmptyCustom ColumnSortRemove Other ColumnsPivot Column

File thực hành gồm

KeToan\CongNo131\So cong no 131 T01-2026.xlsx … So cong no 131 T08-2026.xlsx
Tám sổ chi tiết công nợ phải thu theo hoá đơn, xuất từ phần mềm, mỗi tháng một file, 8 khách. Sheet SoChiTiet: 3 dòng tiêu đề gộp ô, dòng 4 là tên cột, cuối có dòng Tổng cộng. Dòng thu tiền mang sẵn số và hạn thanh toán của hoá đơn được trừ.
KeToan\CongNoT09\So cong no 131 T09-2026.xlsx
Sổ tháng 9. Dùng ở bước cuối.
KeToan\Tuoi no phai thu 2026.xlsx
Báo cáo tuổi nợ đã dựng sẵn, mỗi khách một dòng, nợ chia năm nhóm. Mở ra bấm Data › Refresh All là chạy, dùng để so kết quả.

Giải nén gói file vào ổ C:\ để ra đúng thư mục C:\KeToan. Query trong Tuoi no phai thu 2026.xlsx trỏ vào C:\KeToan\CongNo131.

Các bước làm

1
Lấy dữ liệu từ thư mục sổ công nợ
Mở một file Excel trắng. Vào Data › Get Data › From File › From Folder, dán C:\KeToan\CongNo131 rồi bấm Open. Ở hộp xem trước bấm mũi tên ở nút Combine, chọn Combine & Transform Data. Ở hộp Combine Files bấm sheet SoChiTiet rồi OK.
2
Dọn đầu sổ trên file mẫu
Trong Power Query Editor, ở khung Queries bấm Transform Sample File. Vào Home › Remove Rows › Remove Top Rows, gõ 2 rồi OK: lúc mở Excel đã tự đưa dòng đầu lên làm tiêu đề, còn 2 dòng nằm trên dòng tên cột. Bấm Home › Use First Row as Headers. Bước Changed Type Excel tự thêm lần này để nguyên, vì sổ phần mềm tháng nào cũng một khuôn cột.
3
Bỏ dòng Tổng cộng cuối sổ
Vẫn ở file mẫu, bấm mũi tên lọc ở cột Ngày hạch toán, chọn Remove Empty. Dòng Tổng cộng để trống ngày nên bị loại.
4
Sang bảng gộp, xoá Changed Type
Ở khung Queries bấm query CongNo131. Bước Changed Type cuối cùng, do Excel thêm lúc Combine, còn nhớ khuôn cột cũ. Bấm dấu X ở khung Applied Steps để xoá.
5
Cột Còn nợ
Vào Add Column › Custom Column, đặt tên Còn nợ và gõ công thức dưới đây: phát sinh Nợ trừ phát sinh Có. Hai số gói trong List.Sum để ô trống không làm hỏng phép trừ. Dòng thu tiền ra số âm và mang đúng số hoá đơn, hạn thanh toán của hoá đơn nó trừ, nên tiền trả tháng sau vẫn rơi vào hoá đơn của tháng trước.
List.Sum({[Phát sinh Nợ], -[Phát sinh Có]})
6
Cột Số ngày quá hạn
Thêm Custom Column thứ hai, đặt tên Số ngày quá hạn: ngày chốt 30/09/2026 trừ hạn thanh toán. Ngày chốt gõ thẳng trong công thức.
Duration.Days(#date(2026, 9, 30) - Date.From([Hạn thanh toán]))
7
Cột Nhóm tuổi nợ
Thêm Custom Column thứ ba, đặt tên Nhóm tuổi nợ, chia nhóm bằng if then else theo các mốc 0, 30, 60, 90 ngày.
if [Số ngày quá hạn] <= 0 then "Trong hạn" else if [Số ngày quá hạn] <= 30 then "1-30 ngày" else if [Số ngày quá hạn] <= 60 then "31-60 ngày" else if [Số ngày quá hạn] <= 90 then "61-90 ngày" else "Trên 90 ngày"
8
Sắp xếp rồi chỉ giữ ba cột
Bấm tiêu đề cột Số ngày quá hạn, bấm Home › Sort Ascending để lát nữa các nhóm đứng đúng thứ tự. Bấm tiêu đề cột Tên khách hàng, giữ Ctrl bấm Còn nợ và Nhóm tuổi nợ, chuột phải rồi chọn Remove Other Columns. Bảng còn 3 cột.
9
Pivot Column theo nhóm tuổi nợ
Bấm tiêu đề cột Nhóm tuổi nợ, vào Transform › Pivot Column. Ô Values Column chọn Còn nợ, phần Advanced options để Sum, OK. Bảng còn 8 dòng, mỗi khách một dòng, năm nhóm thành năm cột từ Trong hạn tới Trên 90 ngày. Bấm Home › Close & Load, bảng về sheet mới CongNo131. Với tám sổ tháng 1 tới tháng 8, cột Trên 90 ngày cộng lại 90.180.000.
10
Tháng sau thả sổ mới và Refresh
Kéo file So cong no 131 T09-2026.xlsx từ thư mục CongNoT09 sang thư mục CongNo131. Về Excel bấm Data › Refresh All. Tiền khách trả trong tháng 9 tự trừ vào đúng hoá đơn, cột Trên 90 ngày còn 70.180.000, bớt đúng 20.000.000 khách vừa trả. Sang kỳ chốt khác thì mở Power Query Editor, sửa ngày trong công thức cột Số ngày quá hạn ở bước Added Custom1.

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

Ngày chốt 30/09/2026 nằm trong công thức cột Số ngày quá hạn (bước Added Custom1). Mỗi kỳ báo cáo phải sửa ngày này trước khi Refresh.
Cách làm này cần dòng thu tiền trong sổ ghi rõ số hoá đơn và hạn thanh toán của hoá đơn được trừ. Có vậy tiền thu mới trừ đúng hoá đơn.
Bài chỉ chia tuổi nợ trên số liệu sổ đang có, không đề cập trích lập dự phòng.

Excel 2019, 2021, 365 trên Windows có menu giống hệt trong bài. Excel 2016 cũng có Power Query nhưng menu hơi khác.

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