Tóm tắt nhanh
- File quản lý công nợ bằng Excel nên tách 4 sheet: Danh mục khách hàng, Phát sinh hoá đơn, Thu tiền và Tổng hợp tuổi nợ; mỗi khoản tiền chỉ nhập một lần.
- Số đã thu của một hoá đơn tính bằng
SUMIFStừ sheet Thu tiền theo số hoá đơn; số còn nợ bằng số tiền hoá đơn trừ số đã thu. - Số ngày quá hạn nên tính bằng
MAX(0, TODAY() - ngày đến hạn); hàmDATEDIFtrả lỗi#NUM!khi hoá đơn chưa đến hạn. - Tuổi nợ thường chia 5 nhóm: chưa đến hạn, quá hạn 1–30, 31–60, 61–90 và trên 90 ngày; tổng 5 nhóm phải bằng tổng còn nợ của khách.
- Excel đủ dùng khi số hoá đơn ít và một người cập nhật; khi phải dò sao kê ngân hàng hằng ngày hoặc nhiều người cùng sửa, nên cân nhắc phần mềm.
Cấu trúc file quản lý công nợ bằng Excel: 4 sheet
Lỗi phổ biến của bảng công nợ Excel là dồn mọi thứ vào một sheet: mỗi lần khách trả tiền lại sửa đè cột "đã thu". Cách bền hơn là ghi hoá đơn và tiền thu thành hai danh sách riêng, rồi để công thức ghép lại. Khi cần kiểm tra, bạn luôn biết số dư đến từ những dòng nào.
| Sheet | Mục đích | Ai nhập, khi nào |
|---|---|---|
| DM_KH (Danh mục khách hàng) | Mỗi khách một dòng, có mã riêng, số ngày được nợ, hạn mức | Kế toán, khi có khách mới hoặc đổi điều khoản |
| PS_HD (Phát sinh hoá đơn) | Mỗi hoá đơn bán hàng một dòng; tính số đã thu, còn nợ, tuổi nợ | Kế toán, ngay trong ngày xuất hoá đơn |
| THU (Thu tiền) | Mỗi lần nhận tiền một dòng, gắn với số hoá đơn được thu | Kế toán, ngay khi tiền về tài khoản hoặc quỹ |
| TONG_HOP (Tổng hợp tuổi nợ) | Mỗi khách một dòng: tổng phát sinh, đã thu, còn nợ, chia theo nhóm tuổi nợ | Không nhập tay; toàn bộ là công thức |
Sheet DM_KH: Danh mục khách hàng
| Cột | Nội dung | Ghi chú |
|---|---|---|
| A · Mã KH | Mã ngắn, không trùng, ví dụ KH001 | Dùng mã để tra cứu, không dùng tên |
| B · Tên khách hàng | Tên theo hợp đồng hoặc hoá đơn | Chỉ gõ ở đây, các sheet khác tự tra ra |
| C · Mã số thuế | MST của khách | Định dạng Text để giữ số 0 ở đầu |
| D · Người liên hệ | Người nhận thông báo thanh toán | Thường là kế toán bên khách |
| E · Điện thoại / Zalo | Số để nhắc thanh toán | Định dạng Text |
| F · Số ngày được nợ | Ví dụ 30, 45 | Theo hợp đồng hoặc chính sách bán hàng |
| G · Hạn mức công nợ | Số dư nợ tối đa cho phép | Để trống nếu không áp hạn mức |
| H · Người phụ trách | Nhân viên kinh doanh hoặc kế toán theo dõi | Dùng để lọc danh sách cần nhắc |
Sheet PS_HD: Phát sinh hoá đơn
| Cột | Nội dung | Nhập tay hay công thức |
|---|---|---|
| A · Số hoá đơn | Số hoá đơn điện tử, không trùng | Nhập tay |
| B · Ngày hoá đơn | Ngày lập hoá đơn, định dạng dd/mm/yyyy | Nhập tay |
| C · Mã KH | Chọn từ danh sách thả xuống | Nhập tay (có kiểm tra dữ liệu) |
| D · Tên khách hàng | Tra từ DM_KH theo mã | Công thức |
| E · Diễn giải | Đơn hàng, hợp đồng, đợt thanh toán | Nhập tay |
| F · Số tiền | Tổng tiền thanh toán trên hoá đơn (đã gồm thuế GTGT) | Nhập tay |
| G · Ngày đến hạn | Ngày hoá đơn cộng số ngày được nợ | Công thức |
| H · Đã thu | Tổng tiền đã thu cho hoá đơn này | Công thức |
| I · Còn nợ | F trừ H | Công thức |
| J · Số ngày quá hạn | 0 nếu chưa đến hạn hoặc đã thu đủ | Công thức |
| K · Nhóm tuổi nợ | Chưa đến hạn, 1–30, 31–60, 61–90, trên 90 ngày | Công thức |
| L · Trạng thái | Chưa thu, thu một phần, đã thu đủ | Công thức |
Sheet THU: Thu tiền
| Cột | Nội dung | Ghi chú |
|---|---|---|
| A · Ngày thu | Ngày tiền về tài khoản hoặc quỹ | Lấy theo sao kê, không theo ngày khách báo |
| B · Mã KH | Khách đã trả | Danh sách thả xuống như PS_HD |
| C · Số hoá đơn được thu | Hoá đơn mà khoản tiền này thanh toán | Khách trả gộp nhiều hoá đơn thì tách nhiều dòng |
| D · Số tiền | Số tiền phân bổ cho hoá đơn ở cột C | Tổng các dòng tách phải bằng số tiền thực nhận |
| E · Hình thức | Chuyển khoản, tiền mặt, điều chỉnh giảm | "Điều chỉnh giảm" dùng cho hàng trả lại, chiết khấu sau bán |
| F · Mã giao dịch / nội dung chuyển khoản | Để đối chiếu ngược với sao kê | Với tiền mặt, ghi số phiếu thu |
Sheet TONG_HOP có các cột: Mã KH, Tên khách hàng, Tổng phát sinh, Đã thu, Còn nợ, năm cột nhóm tuổi nợ, Chênh lệch kiểm tra và Cảnh báo hạn mức. Tiêu đề của năm cột tuổi nợ phải gõ giống hệt nhãn trong cột K của PS_HD, vì công thức dùng chính tiêu đề làm điều kiện.
Mẹo: chuyển mỗi vùng dữ liệu thành Excel Table (chọn vùng, nhấn Ctrl + T). Bảng tự mở rộng khi thêm dòng, công thức tự chép xuống, nên ít bị sót dòng hơn so với vùng cố định kiểu A2:A500.
Công thức Excel cho bảng theo dõi công nợ
Các công thức dưới đây viết cho dòng 2 rồi chép xuống. Ví dụ dùng dấu phẩy để ngăn cách đối số. Nếu máy đặt định dạng vùng Việt Nam và Excel báo lỗi, hãy thay dấu phẩy bằng dấu chấm phẩy.
Tra tên khách hàng theo mã (cột D của PS_HD)
=XLOOKUP(C2, DM_KH!$A:$A, DM_KH!$B:$B, "Chưa có mã")XLOOKUP có trong Microsoft 365, Excel 2021 và Excel 2024. Với phiên bản cũ hơn, dùng VLOOKUP với tham số cuối là FALSE để tìm chính xác:
=IFERROR(VLOOKUP(C2, DM_KH!$A:$H, 2, FALSE), "Chưa có mã")Ngày đến hạn (cột G)
=B2 + XLOOKUP(C2, DM_KH!$A:$A, DM_KH!$F:$F, 0)Nếu một hoá đơn có hạn riêng khác điều khoản chung, gõ đè ngày đến hạn cho dòng đó và ghi lý do ở cột Diễn giải.
Đã thu, còn nợ và trạng thái (cột H, I, L)
H2 =SUMIFS(THU!$D:$D, THU!$C:$C, A2)
I2 =F2 - H2
L2 =IF(I2<=0, "Đã thu đủ", IF(H2>0, "Thu một phần", "Chưa thu"))SUMIFS cộng mọi dòng ở sheet THU có số hoá đơn trùng với A2, nên khách trả nhiều lần vẫn cộng đủ. Cú pháp gốc là SUMIFS(vùng cần cộng, vùng điều kiện 1, điều kiện 1, …), tối đa 127 cặp điều kiện.
Số ngày quá hạn (cột J)
=IF(I2<=0, 0, MAX(0, TODAY() - G2))Excel lưu ngày dưới dạng số, nên lấy ngày sau trừ ngày trước là ra số ngày. Nếu muốn dùng DATEDIF, phải chặn trường hợp chưa đến hạn, vì hàm này trả #NUM! khi ngày bắt đầu lớn hơn ngày kết thúc:
=IF(AND(I2>0, TODAY()>G2), DATEDIF(G2, TODAY(), "d"), 0)Ví dụ một dòng hoá đơn
| Cột | Giá trị | Cách ra số |
|---|---|---|
| Ngày hoá đơn | 05/08/2026 | Nhập tay |
| Số tiền | VND 120.000.000 | Nhập tay |
| Ngày đến hạn | 04/09/2026 | 05/08/2026 cộng 30 ngày được nợ |
| Đã thu | VND 50.000.000 | Một dòng ở sheet THU ngày 01/09/2026 |
| Còn nợ | VND 70.000.000 | 120.000.000 trừ 50.000.000 |
| Số ngày quá hạn tại 18/10/2026 | 44 | 18/10/2026 trừ 04/09/2026 |
| Nhóm tuổi nợ | 31–60 ngày | 44 nằm trong khoảng 31–60 |
Phân nhóm tuổi nợ và cảnh báo quá hạn
Tuổi nợ cho biết khoản nào cần xử lý trước: khoản càng quá hạn lâu càng khó thu. Có hai cách tính phổ biến: tính số ngày kể từ ngày đến hạn (số ngày quá hạn) hoặc kể từ ngày hoá đơn. Bài này dùng cách thứ nhất vì nó gắn trực tiếp với việc nhắc thanh toán; quan trọng là cả file chỉ dùng một cách.
Nhóm tuổi nợ (cột K của PS_HD)
=IF(I2<=0, "Đã thu đủ",
IF(J2=0, "Chưa đến hạn",
IF(J2<=30, "1–30 ngày",
IF(J2<=60, "31–60 ngày",
IF(J2<=90, "61–90 ngày", "Trên 90 ngày")))))Một số doanh nghiệp gộp "chưa đến hạn" và "quá hạn dưới 30 ngày" thành nhóm 0–30 ngày. Cách chia nào cũng được, miễn là nhãn ở cột K và tiêu đề cột ở sheet TONG_HOP giống nhau từng ký tự.
Sheet TONG_HOP: công nợ theo từng khách
Cột A là mã khách hàng, cột B tra tên như ở PS_HD. Từ cột C trở đi:
C2 Tổng phát sinh =SUMIFS(PS_HD!$F:$F, PS_HD!$C:$C, $A2)
D2 Đã thu =SUMIFS(THU!$D:$D, THU!$B:$B, $A2)
E2 Còn nợ =C2 - D2
F2 Chưa đến hạn =SUMIFS(PS_HD!$I:$I, PS_HD!$C:$C, $A2, PS_HD!$K:$K, F$1)
(chép F2 sang G2:J2 cho các cột 1–30, 31–60, 61–90, Trên 90 ngày)
K2 Chênh lệch =E2 - SUM(F2:J2)
L2 Cảnh báo hạn mức =IF(AND(XLOOKUP($A2, DM_KH!$A:$A, DM_KH!$G:$G, 0)>0,
E2>XLOOKUP($A2, DM_KH!$A:$A, DM_KH!$G:$G, 0)),
"Vượt hạn mức", "")Cột K là cột kiểm tra: bình thường phải bằng 0. Nếu khác 0, thường có khoản tiền thu ghi sai số hoá đơn, chưa gắn hoá đơn, hoặc khách trả thừa.
Cảnh báo bằng màu và danh sách sắp đến hạn
- Chọn vùng dữ liệu của PS_HD, ví dụ A2:L2000, vào Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
- Quá hạn trên 90 ngày (tô đỏ):
=AND($I2>0, $J2>90). - Đã quá hạn (tô cam):
=AND($I2>0, $J2>0). Đặt quy tắc tô đỏ nằm trên quy tắc tô cam trong danh sách quy tắc. - Đến hạn trong 7 ngày tới (tô vàng):
=AND($I2>0, $G2>=TODAY(), $G2-TODAY()<=7). - Tạo danh sách cần nhắc ở một sheet riêng (Microsoft 365, Excel 2021 trở lên) bằng hàm
FILTER, như khối dưới đây.
=FILTER(PS_HD!A2:L2000,
(PS_HD!I2:I2000>0) * (PS_HD!G2:G2000<=TODAY()+7),
"Không có khoản cần nhắc")Danh sách này gồm cả khoản đã quá hạn và khoản sắp đến hạn trong 7 ngày. Sắp xếp theo cột J giảm dần để khoản quá hạn lâu nhất nằm trên cùng.
Áp dụng cho công nợ nhà cung cấp
Công nợ phải trả dùng cùng cấu trúc, chỉ đổi chiều dòng tiền. Nên tách thành bộ sheet riêng hoặc file riêng, không gộp chung với phải thu, để tổng phải thu và tổng phải trả không bị cộng lẫn.
| Phải thu khách hàng | Phải trả nhà cung cấp | Điểm khác cần lưu ý |
|---|---|---|
| DM_KH | DM_NCC | Thêm cột số tài khoản ngân hàng và tên chủ tài khoản của nhà cung cấp |
| PS_HD (hoá đơn bán ra) | PS_MUA (hoá đơn mua vào) | Ghi số hoá đơn của nhà cung cấp; một số hoá đơn có thể trùng số giữa hai nhà cung cấp, nên ghép mã NCC với số hoá đơn làm khoá |
| THU | CHI (thanh toán) | Thêm cột người duyệt chi và số đề nghị thanh toán |
| Tuổi nợ để nhắc thu | Lịch đến hạn để xếp lịch chi | Dùng cột "đến hạn trong 7 ngày" để lập kế hoạch tiền ra |
Đặt lịch đến hạn phải trả cạnh lịch dự kiến thu sẽ cho thấy tuần nào tiền ra nhiều hơn tiền vào. Cách đọc hai lịch này cùng lúc được trình bày trong bài quản lý dòng tiền doanh nghiệp.
Lưu ý: file Excel là công cụ quản trị nội bộ, không thay sổ kế toán. Trên sổ sách, công nợ phải thu và phải trả thường được theo dõi trên tài khoản 131 và 331 theo chế độ kế toán doanh nghiệp. Từ năm tài chính bắt đầu từ 01/01/2026, chế độ này theo Thông tư 99/2025/TT-BTC (thay Thông tư 200/2014/TT-BTC); doanh nghiệp nhỏ và vừa có thể đang áp dụng Thông tư 133/2016/TT-BTC, doanh nghiệp siêu nhỏ áp dụng Thông tư 58/2026/TT-BTC từ 01/07/2026. Kế toán nên đối chiếu số dư của file với sổ chi tiết công nợ hằng tháng.
Quy trình cập nhật bảng theo dõi công nợ hằng ngày
Bảng theo dõi công nợ chỉ đúng khi được cập nhật đều. Quy trình dưới đây mất ít thao tác nếu làm mỗi ngày, và tránh được cảnh cuối tháng dò lại cả trăm giao dịch.
- Nhập hoá đơn mới: mỗi hoá đơn xuất trong ngày thêm một dòng vào PS_HD, chọn mã khách từ danh sách thả xuống.
- Tải sao kê ngân hàng trong ngày: với mỗi khoản tiền vào, xác định khách và hoá đơn dựa vào nội dung chuyển khoản, rồi ghi vào sheet THU.
- Tách khoản trả gộp: khách trả một lần cho nhiều hoá đơn thì ghi nhiều dòng, mỗi dòng một số hoá đơn; khoản chưa rõ ghi tạm số hoá đơn "CHUA_GAN" để không bị quên.
- Ghi tiền mặt và điều chỉnh: nhập các khoản thu tiền mặt kèm số phiếu thu, các khoản hàng trả lại hoặc chiết khấu với hình thức "Điều chỉnh giảm".
- Kiểm tra cột Chênh lệch ở TONG_HOP: mọi dòng phải bằng 0, và tổng tiền ghi ở THU trong ngày phải khớp với tổng tiền vào trên sao kê.
- Mở danh sách cần nhắc: lọc khoản quá hạn và sắp đến hạn, giao cho người phụ trách liên hệ khách; ghi ngày và nội dung đã nhắc vào cột ghi chú.
- Lưu bản sao: nếu file nằm trên máy cá nhân, lưu thêm một bản theo ngày lên ổ dùng chung hoặc dịch vụ lưu trữ đám mây.
Mỗi tháng, gửi cho từng khách một bảng đối chiếu gồm các hoá đơn còn mở, số đã thanh toán và số dư cuối kỳ, đề nghị khách xác nhận. Khi có chênh lệch, kiểm tra lại chứng từ gốc trước khi sửa file. Khi gửi nhắc thanh toán, kèm mã QR đúng số tiền giúp khách chuyển khoản không sai nội dung; xem thêm cách thu tiền qua QR.
5 lỗi thường gặp khi quản lý công nợ bằng Excel và cách tránh
| Lỗi | Hậu quả | Cách tránh |
|---|---|---|
| 1. Cập nhật trễ, dồn đến cuối tuần hoặc cuối tháng | Số dư trên file không còn đúng; bỏ lỡ thời điểm nhắc khi khoản nợ vừa đến hạn | Ghi hoá đơn và tiền thu trong ngày phát sinh; giao rõ một người chịu trách nhiệm cập nhật |
| 2. Gõ tay tên khách thay vì dùng mã | "Cty An Phát" và "Công ty An Phát" bị tính là hai khách; SUMIFS bỏ sót tiền | Dùng mã khách và Data Validation (Data → Data Validation → List) trỏ về cột mã của DM_KH |
| 3. Sửa đè cột "đã thu" thay vì ghi dòng thu mới | Mất dấu vết khách trả ngày nào, bao nhiêu; khó đối chiếu khi có tranh chấp | Mỗi lần nhận tiền là một dòng ở sheet THU; cột "đã thu" ở PS_HD luôn là công thức |
| 4. Công thức bị sửa hoặc vùng tham chiếu không bao hết dữ liệu | Tổng công nợ thấp hơn thực tế mà không ai nhận ra | Dùng Excel Table hoặc tham chiếu cả cột; khoá các cột công thức bằng Review → Protect Sheet; theo dõi cột Chênh lệch |
| 5. Chỉ lưu một bản trên máy cá nhân, nhiều người gửi file qua lại | Mất dữ liệu khi máy hỏng; nhiều phiên bản khác nhau, không biết bản nào đúng | Lưu một file gốc trên ổ dùng chung (OneDrive, SharePoint hoặc Google Drive), phân quyền chỉ xem cho người không cần sửa, đặt quy ước tên bản sao lưu theo ngày |
Thêm một lỗi kỹ thuật hay gặp: định dạng ngày không thống nhất. Ngày gõ dạng văn bản (căn trái trong ô) sẽ làm phép trừ ngày báo lỗi #VALUE!; hãy định dạng cột ngày là Date và kiểm tra bằng hàm ISNUMBER.
Khi nào Excel còn đủ, khi nào nên chuyển sang phần mềm
Excel là công cụ hợp lý cho nhiều doanh nghiệp: có sẵn, linh hoạt, kế toán đã quen. Câu hỏi không phải Excel tốt hay kém, mà là phần việc thủ công quanh file đã lớn đến đâu.
| Tình huống | Excel vẫn đủ | Nên cân nhắc phần mềm |
|---|---|---|
| Số hoá đơn và khoản tiền về | Ít, ghép tay với sao kê trong thời gian ngắn | Nhiều khoản chuyển khoản mỗi ngày, dò sao kê chiếm phần lớn thời gian của kế toán |
| Người cập nhật | Một người, hoặc vài người với file chung có phân quyền | Nhiều bộ phận cùng sửa, cần phân quyền theo vai trò và lịch sử thao tác rõ ràng |
| Nhắc thanh toán | Lọc danh sách, gọi điện hoặc nhắn tin thủ công | Muốn tự động gửi email nhắc theo lịch, kèm mã QR đúng số tiền, và có sẵn nội dung nhắc để gửi qua Zalo |
| Điều khoản bán hàng | Điều khoản đơn giản, ít trả góp nhiều đợt | Nhiều đợt thanh toán, nhiều chi nhánh, nhiều tài khoản ngân hàng |
| Nhu cầu báo cáo | Xem tổng hợp tuổi nợ khi cần | Chủ doanh nghiệp cần xem công nợ và dòng tiền mỗi ngày, không chờ kế toán tổng hợp |
| Chi phí | Dùng bản Office doanh nghiệp đã có | Có phí thuê bao; nên so với số giờ dò sao kê và nhắc nợ thủ công hằng tháng |
Google Sheets là bước trung gian: cùng cách làm như Excel, nhiều người sửa cùng lúc, có lịch sử phiên bản và phân quyền xem hoặc sửa theo email. Việc nhập liệu và đối chiếu với ngân hàng vẫn là thủ công. Bảng so sánh chi tiết theo từng tác vụ có tại FinanOne và Excel.
Câu hỏi thường gặp
Có mẫu file Excel quản lý công nợ đơn giản để tải về không?
Bài này không kèm file tải về. Bạn có thể tự dựng file theo bảng cấu trúc 4 sheet và các công thức ở trên; cách tự dựng giúp người phụ trách hiểu rõ từng cột, khi bàn giao không bị phụ thuộc vào người tạo file.
Bảng theo dõi công nợ khách hàng cần những cột nào?
Tối thiểu cần: số hoá đơn, ngày hoá đơn, mã và tên khách hàng, số tiền, ngày đến hạn, số đã thu, số còn nợ, số ngày quá hạn và trạng thái. Danh mục khách hàng nên có thêm số ngày được nợ, hạn mức và người liên hệ.
Dùng hàm gì để tính số ngày quá hạn công nợ trong Excel?
Dùng =MAX(0, TODAY() - ngày_đến_hạn). Hàm DATEDIF cũng tính được nhưng trả lỗi #NUM! khi hoá đơn chưa đến hạn, nên phải bọc thêm hàm IF.
Nên quản lý công nợ bằng Excel hay Google Sheets?
Excel phù hợp khi một người cập nhật và cần làm việc không có mạng. Google Sheets phù hợp khi nhiều người cùng xem, sửa và cần lịch sử phiên bản. Công thức trong bài dùng được trên cả hai, riêng XLOOKUP và FILTER cần Excel bản mới.
Bao lâu nên đối chiếu công nợ với khách hàng một lần?
Nhiều doanh nghiệp đối chiếu hằng tháng và vào cuối kỳ kế toán trước khi khoá sổ. Với khách mua thường xuyên hoặc có số dư lớn, đối chiếu dày hơn giúp phát hiện chênh lệch sớm.
File Excel công nợ có thay được sổ chi tiết công nợ không?
Không. File Excel là công cụ quản trị để biết ai còn nợ và cần nhắc ai; sổ kế toán theo chế độ kế toán doanh nghiệp hiện hành mới là căn cứ ghi sổ và lập báo cáo tài chính. Hai bên nên được đối chiếu số dư định kỳ.
Khi dò sao kê và nhắc thanh toán chiếm nhiều thời gian hơn bản thân file Excel
FinanOne liên kết trực tiếp tài khoản Shinhan, MB, VPBank, BIDV (hoặc nhập sao kê Excel từ ngân hàng khác) để giao dịch tự cập nhật, rồi khớp từng khoản tiền về với đơn hàng, hoá đơn theo mã tham chiếu, số tiền và người mua; khoản chưa chắc hiện để bạn xác nhận. Email nhắc thanh toán được gửi tự động theo lịch bạn đặt, kèm link thanh toán và mã VietQR đúng số tiền; nội dung nhắc cũng được soạn sẵn để gửi qua Zalo. Bạn bật hoặc tắt nhắc tự động cho từng đơn và xem báo cáo tuổi nợ phải thu.