Công việc & bán hàng11 phút đọc
Quản lý công việc nhóm bằng Google Sheets: bảng việc, Kanban, nhắc hạn
Cách quản lý công việc nhóm bằng Google Sheets: cột cần có, công thức báo trễ hạn, Kanban bằng FILTER, tổng quan theo người và nhắc hạn qua email.
Công việc & bán hàng · 13 phút đọc
Quản lý công nợ khách hàng bằng Excel, Google Sheets: sổ chi tiết, tuổi nợ, nhắc nợ
Quản lý công nợ khách hàng bằng Excel hay Google Sheets cần hai bảng: sổ chi tiết ghi mọi lần phát sinh nợ và thu tiền (số dư = tổng nợ − tổng đã thu), và bảng hoá đơn còn nợ để chia tuổi nợ 0–30, 31–60, trên 60 ngày. Bài này dựng cả hai với công thức SUMIFS có số ví dụ, thêm cột hạn mức tín dụng, mẫu tin nhắc nợ lịch sự, cách đối chiếu công nợ cuối tháng và những lỗi làm số dư lệch.
Đăng HiTaVa
File quản lý công nợ khách hàng gồm những bảng nào
Công nợ phải thu là tiền khách đã nhận hàng mà chưa trả. Muốn biết ai nợ bao nhiêu, nợ từ bao giờ và có nên bán tiếp cho họ không, bạn cần ba tab, mỗi tab một việc:
| Tab | Mỗi dòng là | Trả lời câu hỏi |
|---|---|---|
| Sổ công nợ (sổ chi tiết) | Một lần phát sinh: bán chịu, thu tiền, trả hàng | Khách X đang nợ bao nhiêu, số dư thay đổi thế nào |
| Hoá đơn | Một hoá đơn bán chịu | Hoá đơn nào còn nợ, nợ bao nhiêu ngày, quá hạn chưa |
| Khách hàng | Một khách | Hạn mức bao nhiêu, còn được bán nợ không |
- Số dư công nợ
- Tổng phát sinh nợ − tổng đã thu
- Tuổi nợ
- Ngày chốt − ngày hoá đơn
- Nhóm tuổi nợ hay dùng
- 0–30 · 31–60 · trên 60 ngày
- Ví dụ trong bài
- 2 khách, 25.700.000đ phải thu
Nếu bạn chưa có sổ đơn bán hàng, nên đọc trước quản lý bán hàng bằng Google Sheets — đơn bán chịu ở đó chính là dòng phát sinh nợ ở đây.
Cách lập sổ chi tiết công nợ khách hàng
Sổ chi tiết công nợ ghi theo thời gian, mọi khách chung một bảng. Cột: A Ngày, B Khách, C Chứng từ (số hoá đơn HĐ-…, phiếu thu PT-…), D Diễn giải, E Phát sinh nợ, F Đã thu, G Số dư khách. Mỗi lần bán chịu ghi số tiền vào E, mỗi lần khách trả ghi vào F — không bao giờ sửa đè số dư bằng tay.
=SUMIFS($E$2:E2; $B$2:B2; B2) - SUMIFS($F$2:F2; $B$2:B2; B2)Thử lại ở dòng 8 (Tạp hoá Minh Anh, phiếu thu PT-052 ngày 18/10): phát sinh nợ của Minh Anh từ dòng 2 tới 8 là 12.000.000 + 15.000.000 + 9.500.000 = 36.500.000; đã thu 8.000.000 + 10.000.000 = 18.000.000. Số dư = 36.500.000 − 18.000.000 = 18.500.000đ.
=SUMIFS(SoCongNo!$E:$E; SoCongNo!$B:$B; A2) - SUMIFS(SoCongNo!$F:$F; SoCongNo!$B:$B; A2)Dùng mã khách, không dùng tên gõ tay
"Tạp hoá Minh Anh" và "Tap hoa Minh Anh" là hai khách với SUMIFS. Lập danh sách khách có mã (KH001…) và cho cột B chọn từ danh sách — xem cách tạo danh sách thả xuống trong Google Sheets. Cách SUMIFS cộng theo điều kiện giải thích kỹ ở bài hàm SUMIFS, COUNTIFS.
Cách tính tuổi nợ 0–30, 31–60, trên 60 ngày bằng công thức
Số dư chỉ cho biết khách nợ bao nhiêu. Tuổi nợ cho biết khoản nợ đó cũ tới đâu: 9 triệu nợ từ tuần trước khác hẳn 9 triệu nợ từ ba tháng trước. Tuổi nợ tính theo từng hoá đơn, nên cần tab Hoá đơn: A Mã HĐ, B Khách, C Ngày HĐ, D Hạn thanh toán, E Số tiền, F Đã thu, G Còn nợ, H Số ngày, I Nhóm. Ô K1 là ngày chốt, ví dụ 31/10/2026.
Khó nhất là cột F: khách trả 10 triệu thì trừ vào hoá đơn nào? Quy ước phổ biến là trừ hoá đơn cũ nhất trước. Công thức dưới lấy tổng tiền khách đã trả, trừ đi các hoá đơn cũ hơn, phần còn lại mới tính cho hoá đơn đang xét:
=MIN(E2; MAX(0; SUMIFS(SoCongNo!$F:$F; SoCongNo!$B:$B; B2) - SUMIFS($E$2:$E$500; $B$2:$B$500; B2; $C$2:$C$500; "<"&C2)))=E2 - F2
=IF(G2=0; ""; $K$1 - C2)
=IF(G2=0; ""; IF(H2<=30; "0-30"; IF(H2<=60; "31-60"; "Trên 60")))
Thử lại với HĐ-0745 của Minh Anh: tổng đã trả 18.000.000; hoá đơn cũ hơn chỉ có HĐ-0712 = 12.000.000; 18.000.000 − 12.000.000 = 6.000.000, nhỏ hơn 15.000.000 nên F = 6.000.000, còn nợ 9.000.000. Số ngày = 31/10 − 10/09 = 20 + 31 = 51 → nhóm 31–60. Với HĐ-0790: 18.000.000 − (12.000.000 + 15.000.000) âm, MAX về 0, nên chưa thu đồng nào.
0-30 ngày: =SUMIFS($G$2:$G$500; $C$2:$C$500; ">="&($K$1-30))
31-60 ngày: =SUMIFS($G$2:$G$500; $C$2:$C$500; "<"&($K$1-30); $C$2:$C$500; ">="&($K$1-60))
Trên 60 ngày: =SUMIFS($G$2:$G$500; $C$2:$C$500; "<"&($K$1-60))Thử lại: K1 − 30 = 01/10/2026. Hoá đơn từ 01/10 trở đi còn nợ: HĐ-0790 9.500.000 + HĐ-0801 3.200.000 = 12.700.000. K1 − 60 = 01/09: hoá đơn từ 01/09 tới 30/09 chỉ có HĐ-0745 = 9.000.000. Trước 01/09 còn HĐ-0688 = 4.000.000 (HĐ-0712 đã trả đủ nên cộng 0).
| Tuổi nợ (từ ngày hoá đơn) | Quá hạn (từ hạn thanh toán) | |
|---|---|---|
| Tính từ | Ngày xuất hoá đơn | Ngày hết hạn trả, ví dụ ngày HĐ + 30 |
| Dùng để | Thấy nợ cũ dần đều, so sánh giữa các khách | Biết ai đang trễ hẹn, cần nhắc ngay |
| Ví dụ HĐ-0745 (10/09, hạn 30 ngày) | 51 ngày, nhóm 31–60 | Hạn 10/10, quá hạn 21 ngày |
| Công thức | =$K$1 - C2 | =MAX(0; $K$1 - D2) |
Ngày phải là ngày thật
Nếu ô ngày là chữ (căn trái, gõ 10/09 bị hiểu thành 9 tháng 10) thì K1 − C2 báo lỗi #VALUE! hoặc ra số vô lý. Sửa theo bài lỗi ngày tháng trong Google Sheets trước khi tin vào báo cáo tuổi nợ.
Hạn mức tín dụng (hạn mức công nợ) cho từng khách
Hạn mức công nợ là số tiền tối đa một khách được nợ cùng lúc. Đặt hạn mức giúp bạn trả lời nhanh "có giao đơn này không" thay vì nhớ trong đầu. Tab Khách hàng: A Khách, B Hạn mức, C Đang nợ, D Còn được nợ, E Cảnh báo.
=SUMIFS(HoaDon!$G:$G; HoaDon!$B:$B; A2)
=B2 - C2
=IF(C2 > B2; "Vượt hạn mức"; IF(SUMIFS(HoaDon!$G:$G; HoaDon!$B:$B; A2; HoaDon!$H:$H; ">60") > 0; "Có nợ trên 60 ngày"; "Được bán nợ"))| Khách | Hạn mức | Đang nợ | Còn được nợ | Cảnh báo |
|---|---|---|---|---|
| Tạp hoá Minh Anh | 25.000.000 | 18.500.000 | 6.500.000 | Được bán nợ |
| Spa Ngọc Lan | 10.000.000 | 7.200.000 | 2.800.000 | Có nợ trên 60 ngày |
Cách chọn hạn mức: bắt đầu bằng khoảng doanh số bán chịu trung bình 1 tháng của khách đó, nâng dần khi khách trả đúng hạn vài tháng liền, hạ hoặc chuyển sang thu tiền ngay khi khách có nợ trên 60 ngày. Khách mới chưa có lịch sử thì cho hạn mức thấp hoặc thu trước một phần. Ghi hạn mức và số ngày được nợ vào hợp đồng hoặc tin nhắn xác nhận đơn đầu tiên để sau này không cãi nhau.
Lô hàng có lãi mỏng thì nợ lâu càng đắt: tiền nằm ở khách là tiền bạn phải đi vay hoặc không nhập được hàng mới. Bài cách tính giá vốn và lợi nhuận và cách tính điểm hoà vốn giúp bạn biết mỗi đơn bán chịu thực ra lãi bao nhiêu.
Mẫu tin nhắn nhắc nợ khách hàng lịch sự
Nhắc nợ hiệu quả nhất khi đúng lúc, đúng số, dễ trả: gửi theo mốc cố định, ghi rõ số hoá đơn và số tiền, kèm cách chuyển khoản. Ba mẫu theo ba mốc, thay phần trong ngoặc vuông:
| Mốc | Mẫu tin |
|---|---|
| Trước hạn 3 ngày | Chào anh/chị [tên], [cửa hàng] xin nhắc hoá đơn [HĐ-0790] số tiền [9.500.000đ] đến hạn thanh toán ngày [14/11]. Anh/chị chuyển khoản theo mã QR đính kèm, nội dung [HD0790] giúp em nhé. Cảm ơn anh/chị. |
| Quá hạn 7 ngày | Chào anh/chị [tên], hoá đơn [HĐ-0745] ngày [10/09] còn [9.000.000đ] đã quá hạn [7 ngày]. Anh/chị kiểm tra giúp em, nếu đã chuyển thì gửi em ảnh giao dịch để em đối chiếu. Cảm ơn anh/chị. |
| Quá hạn 30 ngày trở lên | Chào anh/chị [tên], hiện công nợ của [khách] là [7.200.000đ], trong đó [4.000.000đ] từ hoá đơn [HĐ-0688] ngày [20/07]. Để tiếp tục giao hàng, [cửa hàng] mong anh/chị thanh toán phần quá hạn trước ngày [dd/mm], hoặc báo em lịch trả cụ thể. Em gửi kèm bảng đối chiếu công nợ. |
- Kèm mã VietQR đúng số tiền — khách quét là xong, không gõ nhầm số tài khoản. Cách tạo mã ngay trong bảng tính ở bài tạo mã QR chuyển khoản trong Google Sheets.
- Gửi một người, một kênh: tin nhắn tới người quyết định trả tiền (chủ tiệm, kế toán), không gửi vào nhóm chung có nhân viên của khách.
- Không dọa, không bêu tên công khai trên mạng xã hội — vừa mất khách vừa có thể bị khiếu nại vì xúc phạm danh dự.
- Ghi lại lần nhắc (ngày, kênh, khách hẹn gì) vào cột Ghi chú của hoá đơn để lần sau không nhắc trùng.
Đối chiếu công nợ cuối tháng với khách
Đối chiếu công nợ là gửi cho khách bảng số dư của họ trong tháng để hai bên cùng xác nhận. Làm đều mỗi tháng (hoặc mỗi quý với khách nhỏ) giúp phát hiện sớm phiếu thu ghi thiếu, hàng trả lại chưa trừ, chuyển khoản ghi nhầm khách — trước khi số lệch thành chuyện lớn.
| Khoản | Tạp hoá Minh Anh, tháng 10/2026 | Lấy từ |
|---|---|---|
| Số dư đầu kỳ (đến hết 30/09) | 19.000.000 | Nợ trước 01/10 − thu trước 01/10 |
| Phát sinh tăng (bán chịu trong tháng) | 9.500.000 | HĐ-0790 ngày 15/10 |
| Phát sinh giảm (đã thu, trả hàng) | 10.000.000 | PT-052 ngày 18/10 |
| Số dư cuối kỳ | 18.500.000 | 19.000.000 + 9.500.000 − 10.000.000 |
=SUMIFS(SoCongNo!$E:$E; SoCongNo!$B:$B; "Tạp hoá Minh Anh"; SoCongNo!$A:$A; "<"&DATE(2026;10;1)) - SUMIFS(SoCongNo!$F:$F; SoCongNo!$B:$B; "Tạp hoá Minh Anh"; SoCongNo!$A:$A; "<"&DATE(2026;10;1))Thử lại: trước 01/10 Minh Anh phát sinh 12.000.000 + 15.000.000 = 27.000.000, đã trả 8.000.000 → đầu kỳ 19.000.000. Khớp với số dư ở dòng 10/09 của sổ chi tiết.
- Ngày 1–3 tháng sau, lọc sổ chi tiết theo khách và tháng, kèm bảng 4 dòng như trên.
- Gửi khách bằng kênh có lưu vết (email, Zalo), đề nghị xác nhận trong 5–7 ngày.
- Khách báo lệch: dò theo số hoá đơn và phiếu thu, sửa bằng dòng điều chỉnh có diễn giải, không xoá dòng cũ.
- Với khách lớn, lập biên bản đối chiếu công nợ hai bên ký, lưu cùng hồ sơ khách.
Lỗi hay gặp khi quản lý công nợ bằng Excel
| Lỗi | Hậu quả | Cách tránh |
|---|---|---|
| Gõ số tiền kèm chữ "9tr5", "9.500.000đ" | SUMIFS bỏ qua ô chữ, tổng bằng 0 hoặc thiếu | Ô số chỉ gõ số, định dạng hiển thị dấu chấm; xem lỗi SUM bằng 0 |
| Sửa đè số dư bằng tay | Không ai biết số dư từ đâu ra, đối chiếu không được | Chỉ ghi phát sinh, số dư luôn là công thức |
| Xoá dòng khi khách trả hàng | Mất lịch sử, lệch với khách | Thêm dòng trả hàng ghi ở cột Đã thu (giảm nợ) kèm diễn giải |
| Thu tiền không ghi số hoá đơn | Không biết trừ hoá đơn nào, tuổi nợ sai | Quy ước trừ hoá đơn cũ nhất trước, nội dung chuyển khoản ghi số HĐ |
| Một khách hai tên | Số dư bị chia đôi, hạn mức vô dụng | Mã khách + danh sách thả xuống |
| Ai cũng sửa được công thức | Kéo nhầm, ghi đè công thức | Khoá ô công thức, chỉ mở vùng nhập |
| Hai hoá đơn cùng khách cùng ngày | Công thức trừ hoá đơn cũ trước coi chúng ngang nhau, có thể trừ trùng | Gộp thành một hoá đơn, hoặc nhập cả giờ vào ô ngày (15/10/2026 09:00) |
Dấu hiệu file tự làm đã tới giới hạn: nhiều người cùng ghi thu tiền, phải dò sao kê ngân hàng từng dòng mỗi ngày, hay cần chặn bán nợ ngay tại quầy chứ không phải xem báo cáo sau. Khi đó công nợ nên được ghi ngay từ lúc bán thay vì nhập lại.
Đọc tiếp
Công nợ là một nhánh của file bán hàng. Nếu khách của bạn đến từ đội sales gọi điện, chăm sóc trước khi mua, xem thêm quản lý khách hàng tiềm năng bằng Google Sheets.
Câu hỏi thường gặp về quản lý công nợ
Công nợ phải thu khác công nợ phải trả thế nào?
Phải thu là tiền khách nợ bạn (bán chịu). Phải trả là tiền bạn nợ nhà cung cấp (mua chịu). Cách lập sổ giống hệt nhau: phát sinh nợ, đã trả, số dư, tuổi nợ — chỉ đổi khách thành nhà cung cấp và nên để ở hai tab riêng.
Nên chia tuổi nợ thành mấy nhóm?
Cửa hàng nhỏ dùng 3 nhóm 0–30, 31–60, trên 60 ngày là đủ. Bán sỉ cho đại lý với hạn 30–45 ngày thì nên tách thêm 61–90 và trên 90 ngày, vì nợ trên 90 ngày thường khó đòi hơn hẳn. Thêm nhóm chỉ là thêm một dòng SUMIFS với mốc K1 − 90.
Khách trả một phần thì ghi thế nào?
Ghi một dòng phiếu thu với đúng số tiền khách trả ở cột Đã thu. Công thức trừ hoá đơn cũ trước sẽ tự chia: hoá đơn cũ nhất được trừ đủ trước, phần còn lại sang hoá đơn kế tiếp. Không cần sửa dòng hoá đơn.
Hàng trả lại có ghi vào cột Đã thu không?
Có thể ghi vào cột Đã thu với diễn giải "Trả hàng HĐ-…" vì nó cũng làm giảm nợ. Nếu muốn báo cáo tách riêng tiền thật và hàng trả, thêm cột Giảm nợ khác và trừ cả hai cột trong công thức số dư.
Có cần làm biên bản đối chiếu công nợ không?
Với khách lẻ, một tin nhắn xác nhận số dư là đủ. Với khách doanh nghiệp, đại lý hay khoản nợ lớn, nên có biên bản đối chiếu hai bên ký mỗi tháng hoặc mỗi quý — đó là bằng chứng rõ nhất nếu sau này có tranh chấp, và kế toán hai bên thường cần nó khi chốt sổ.
Công thức có dùng được trên Excel không?
Được. SUMIFS, MIN, MAX, IF, DATE đều có trong Excel 2010 trở lên. Nếu Excel để vùng Hoa Kỳ thì đổi dấu chấm phẩy thành dấu phẩy, và đổi tên tab trong công thức nếu bạn đặt tên khác SoCongNo, HoaDon.
Mẫu làm sẵn cho việc này
Đọc tiếp
- Công việc & bán hàng16 phút đọc
Chăm sóc khách hàng sau bán: lịch gọi lại ngày 1, 7, 30, 90, xin đánh giá, xử lý khiếu nại và giữ khách quay lại
Chăm sóc khách hàng sau bán: lịch chạm khách ngày 1, 7, 30, 90, mẫu tin nhắn, xin đánh giá, xử lý khiếu nại, tỷ lệ mua lại và bảng Google Sheets nhắc gọi.
- Công việc & bán hàng14 phút đọc
Cách tính điểm hoà vốn cho cửa hàng, quán nhỏ: công thức, ví dụ và bảng tính Google Sheets
Cách tính điểm hoà vốn theo số lượng và doanh thu, ví dụ quán cà phê và shop mỹ phẩm, biên lợi nhuận góp, nhiều mặt hàng, bảng tính hoà vốn trên Google Sheets.
- Công việc & bán hàng15 phút đọc
Cách tính hoa hồng cho nhân viên bán hàng: % cố định, bậc thang, theo lãi gộp (có công thức Google Sheets)
Cách tính hoa hồng nhân viên bán hàng: % doanh số, bậc thang luỹ tiến hay toàn phần, theo lãi gộp, chỉ tiêu, trừ đơn hoàn, chia nhóm, công thức Google Sheets.
Xem thêm mẹo và video hướng dẫn trên kênh YouTube HiTaVa, hoặc đọc các bài khác trong chủ đề Công việc & bán hàng.
Cần hỏi thêm?Zalo 0935077673 (Hải Vân)