Bán chịu là chuyện thường ngày ở cửa hàng vật liệu xây dựng, tạp hóa, điện nước… nhưng ghi nợ vào sổ tay thì sớm muộn cũng có ngày quên ghi, ghi nhầm hoặc cãi nhau với khách vì lệch số. Bài viết này hướng dẫn bạn quản lý công nợ bằng Excel theo cách mình vẫn dạy học viên: chỉ 3 sheet, vài công thức SUMIFS – INDEX/MATCH, nhập 1 lần là biết ngay khách nào còn nợ bao nhiêu, ai đã vượt hạn mức.
1. Vì sao nên quản lý công nợ bằng Excel thay sổ tay?
Sổ tay thì quen tay, nhưng khi số khách nợ lên vài chục người, mỗi người mua chịu – trả dần nhiều lần trong tháng, bạn sẽ gặp đúng mấy chuyện này:
- Muốn biết một khách còn nợ bao nhiêu phải lật từng trang cộng trừ bằng máy tính.
- Cuối tháng không biết tổng tiền đang nằm ngoài khách là bao nhiêu để xoay vốn nhập hàng.
- Khách hỏi lại “tôi nợ những đơn nào?” – không có bảng kê rõ ràng để đối chiếu.
- Sổ ướt, rách, mất là mất luôn toàn bộ công nợ.
Quản lý công nợ bằng Excel giải quyết gọn cả 4 việc: tự cộng trừ, lọc theo khách trong 1 giây, in bảng kê gửi khách, và sao lưu được nhiều bản.
2. Cấu trúc file quản lý công nợ bằng Excel: 3 sheet là đủ
Nhiều người làm mỗi khách một sheet – vài tháng sau file có 50 sheet, rất khó tổng hợp. Cách đúng là tách dữ liệu theo vai trò:
3. Bước 1 – Lập danh mục khách hàng
Tạo sheet DanhMucKH, mỗi khách 1 dòng. Cột quan trọng nhất là Mã KH – mã phải không trùng, ngắn, dễ gõ (KH01, KH02…). Hai cột số gồm Nợ đầu kỳ (số nợ cũ còn lại trước khi bắt đầu dùng file) và Hạn mức nợ (mức tối đa bạn cho khách nợ).
Hình 1: Sheet DanhMucKH – mỗi khách một dòng, mã KH không trùng
=COUNTIF($A:$A,A1)=1 để Excel báo lỗi ngay khi bạn gõ trùng mã khách.4. Bước 2 – Ghi nhật ký công nợ hằng ngày
Sheet NhatKy là nơi bạn làm việc mỗi ngày. Mỗi lần bán chịu thì ghi số tiền vào cột Phát sinh nợ, mỗi lần khách trả tiền thì ghi vào cột Đã thu. Bạn chỉ nhập 5 cột: Ngày, Mã KH, Diễn giải, Phát sinh nợ, Đã thu – hai cột còn lại Excel tự tính.
Hình 2: Sheet NhatKy – dòng xanh là lần thu tiền, dư nợ KH01 giảm từ 23,5 triệu còn 13,5 triệu
4.1. Tạo danh sách xổ xuống cho cột Mã KH
Chọn vùng B5:B200 → Data → Data Validation → Allow: List → Source: =DanhMucKH!$A$5:$A$200. Từ giờ chỉ cần bấm chọn mã, không sợ gõ sai.
4.2. Tự hiện tên khách bằng INDEX + MATCH
Tại ô C5 nhập công thức rồi kéo xuống:
MATCH tìm vị trí mã KH trong danh mục, INDEX lấy tên ở đúng vị trí đó. Nếu mã không có trong danh mục, ô sẽ báo “Sai mã KH” để bạn kiểm tra lại.
4.3. Dư nợ sau mỗi giao dịch (số dư lũy kế)
Cột G cho biết ngay sau giao dịch đó khách còn nợ bao nhiêu – rất tiện khi khách gọi hỏi. Công thức tại G5:
$E$5:E5: điểm đầu cố định bằng dấu $, điểm cuối thay đổi khi kéo công thức xuống. Nhờ vậy SUMIFS chỉ cộng các giao dịch từ đầu đến dòng hiện tại của đúng khách đó → ra số dư lũy kế.5. Bước 3 – Bảng tổng hợp công nợ bằng SUMIFS
Sheet TongHop trả lời câu hỏi quan trọng nhất: mỗi khách đang nợ bao nhiêu? Mã KH, tên, nợ đầu kỳ, hạn mức lấy từ DanhMucKH; phát sinh và đã thu cộng từ NhatKy bằng SUMIFS:
Phát sinh nợ (E5):
Đã thu (F5):
Còn nợ (G5): =D5+E5-F5
Trạng thái (I5):
Hình 3: Sheet TongHop – nhìn là biết tổng 64,8 triệu đang nằm ngoài khách, chị Hạnh đã vượt hạn mức
📌 Muốn xem công nợ theo tháng? Thêm 2 ô Từ ngày (L1) và Đến ngày (L2), rồi bổ sung điều kiện ngày vào SUMIFS:
6. Bước 4 – Tô màu khách vượt hạn mức nợ
Để những khách cần nhắc nợ “tự nổi lên”, dùng Conditional Formatting:
1
2
3
4
=$I5="Vượt hạn mức" → bấm Format, chọn nền đỏ nhạt, chữ đỏ đậm → OK.5
=$I5="Hết nợ" và chọn nền xanh lá nhạt.Hình 4: Hộp thoại New Formatting Rule – tô đỏ khách vượt hạn mức
7. 5 lỗi thường gặp khi quản lý công nợ bằng Excel
“Cô Lan” và “Cô Lan ” (thừa dấu cách) là 2 người khác nhau với Excel → SUMIFS cộng thiếu. Luôn dùng Mã KH và danh sách xổ xuống.
Gõ “5tr” hay “5.000.000đ” khiến Excel hiểu là chữ, không cộng được. Chỉ gõ số, định dạng hiển thị bằng Format Cells.
Vùng SUMIFS đến dòng 200 mà bạn ghi tới dòng 250 thì phần dư bị bỏ qua. Nên chuyển NhatKy thành Table (Ctrl+T) để vùng tự giãn.
Gõ số vào cột Còn nợ “cho nhanh” sẽ phá công thức. Khóa các cột công thức bằng Review → Protect Sheet.
File trên 1 máy, hỏng ổ cứng là mất hết. Lưu thêm bản trên Google Drive/OneDrive hoặc copy ra USB mỗi tuần.
8. Khi nào nên chuyển sang phần mềm quản lý công nợ?
Cách quản lý công nợ bằng Excel ở trên rất hợp khi bạn mới bắt đầu hoặc có ít khách nợ. Nhưng khi cửa hàng bán chịu nhiều mỗi ngày, việc vừa lập hóa đơn vừa ghi công nợ ở hai nơi sẽ tốn thời gian và dễ sót. Lúc đó, phần mềm bán hàng có sẵn quản lý công nợ sẽ đỡ việc hơn nhiều:
| Tiêu chí | File Excel tự làm | Phần mềm TPSoft |
|---|---|---|
| Ghi công nợ | Nhập tay vào nhật ký | ✅ Tự ghi khi lập hóa đơn bán chịu |
| Tồn kho | Phải làm file riêng | ✅ Tự trừ kho khi bán |
| In bảng đối chiếu cho khách | Tự lọc và định dạng | ✅ In sẵn theo khách, theo kỳ |
| Nợ nhà cung cấp | Thêm file khác | ✅ Có sẵn trong cùng phần mềm |
| Báo cáo lãi lỗ | Phải tự làm | ✅ Có sẵn |
TPSoft là phần mềm quản lý bán hàng kết hợp quản lý kho và công nợ viết trên nền Excel VBA, hoạt động Offline, phù hợp nhiều ngành: tạp hóa, vật liệu xây dựng, điện nước, nhôm kính, tôn sắt thép,…. Cài đặt 1 lần sử dụng vĩnh viễn. Vì chạy trên Excel nên ai đã quen bảng tính như bài này sẽ làm quen rất nhanh. Xem thêm bảng giá hoặc video hướng dẫn sử dụng.
9. Tải file mẫu quản lý công nợ bằng Excel
⬇ Tải file mẫu miễn phí (.xlsx)
10. Câu hỏi thường gặp
📺 Học Excel và quản lý bán hàng cùng Tin học Đông Phương
Theo dõi kênh YouTube Tin học Đông Phương để xem thêm các bài Excel, VBA thực tế. Cần quản lý bán hàng – kho – công nợ trọn gói? Dùng thử phần mềm TPSoft, cài 1 lần dùng vĩnh viễn, hoặc nhắn Zalo 0906 864 860 để được tư vấn.
Trao giá trị, nhận niềm tin.
