Cách làm bảng theo dõi công nợ khách hàng bằng Excel

Cửa hàng bán chịu cho khách quen, ghi vào sổ tay rồi cuối tháng cộng lại thường dễ nhầm và khó đòi. Một bảng theo dõi công nợ trên Excel, thiết kế đúng cách, cho bạn biết ngay khách nào còn nợ bao nhiêu và khoản nào đã quá hạn.

Cách làm dưới đây dùng hai trang tính (sheet): một sheet ghi từng lần phát sinh, một sheet tổng hợp tự tính. Bạn chỉ nhập ở một nơi, mọi con số khác tự cập nhật.

Bước 1: Tạo sheet "GiaoDich" để ghi từng lần phát sinh

Đặt tên sheet đầu là GiaoDich và tạo bảng với các cột sau, dòng 1 là tiêu đề:

• A: Ngày (ngày phát sinh)
• B: Mã KH (viết ngắn gọn, ví dụ KH01)
• C: Tên khách
• D: Loại (chỉ gồm hai giá trị: "Bán chịu" hoặc "Thu tiền")
• E: Số tiền
• F: Hạn thanh toán (chỉ điền ở dòng bán chịu)
• G: Ghi chú

Nguyên tắc: mỗi lần khách mua chịu hoặc trả tiền là một dòng mới. Không sửa đè số cũ, để còn lịch sử đối chiếu.

Bước 2: Dùng danh sách thả xuống để nhập khỏi sai

1. Bôi đen cột D (từ D2 trở xuống).
2. Vào thẻ Data (Dữ liệu), chọn Data Validation (Xác thực dữ liệu).
3. Ở mục Allow, chọn List (Danh sách), ô Source gõ: Bán chịu,Thu tiền
4. Bấm OK.

Từ giờ cột D chỉ nhập được hai giá trị đúng chính tả, nên công thức không bị lệch. Chi tiết xem cách tạo danh sách thả xuống trong Excel. Nếu máy dùng dấu chấm phẩy thì gõ Bán chịu;Thu tiền.

Bước 3: Tạo sheet "CongNo" tổng hợp theo khách

Tạo sheet thứ hai tên CongNo với các cột:

• A: Mã KH, B: Tên khách (nhập danh sách khách, mỗi khách một dòng)
• C: Tổng bán chịu
• D: Tổng đã thu
• E: Còn nợ
• F: Hạn gần nhất, G: Tình trạng

Công thức ở dòng 2 (kéo xuống cho các dòng khác):

• C2: `=SUMIFS(GiaoDich!E:E,GiaoDich!B:B,A2,GiaoDich!D:D,"Bán chịu")`
• D2: `=SUMIFS(GiaoDich!E:E,GiaoDich!B:B,A2,GiaoDich!D:D,"Thu tiền")`
• E2: `=C2-D2`

Hàm SUMIFS cộng các khoản thỏa nhiều điều kiện cùng lúc: đúng khách và đúng loại giao dịch. Cách dùng đầy đủ xem hàm IF, COUNTIF, SUMIF trong Excel. Nếu Excel của bạn dùng dấu chấm phẩy, thay các dấu phẩy trong công thức bằng chấm phẩy.

Bước 4: Tính hạn thanh toán và tình trạng

Cách đơn giản và dễ hiểu cho cửa hàng nhỏ là lấy hạn của khoản bán chịu gần nhất làm mốc nhắc:

• F2: `=MAXIFS(GiaoDich!F:F,GiaoDich!B:B,A2,GiaoDich!D:D,"Bán chịu")`
• G2: `=IF(E2<=0,"Đã thanh toán",IF(TODAY()>F2,"Quá hạn","Trong hạn"))`

Cách tính này đơn giản hóa, vì nó coi toàn bộ số nợ có chung một hạn. Nếu bạn cần theo dõi từng hóa đơn riêng, mỗi hóa đơn nên có mã riêng và dùng bảng chi tiết, hoặc dùng phần mềm chuyên dụng.

Bước 5: Tô màu tự động cho khoản quá hạn

1. Ở sheet CongNo, bôi đen cột G.
2. Vào Home, chọn Conditional Formatting (Định dạng có điều kiện) rồi Highlight Cells Rules > Equal To.
3. Gõ Quá hạn và chọn màu đỏ.

Làm tương tự với "Đã thanh toán" màu xanh. Xem thêm định dạng có điều kiện trong Excel.

Bước 6: Thêm tổng toàn bộ nợ phải thu

Ở cuối cột E, nhấn Alt + = để cộng tổng nợ, xem AutoSum cộng tổng nhanh một cột. Con số này cho bạn biết cửa hàng đang bị khách chiếm dụng bao nhiêu vốn.

Mẹo vận hành để bảng hữu ích thật

• Nhập cùng ngày: cập nhật cuối ngày để không bỏ sót.
• Gửi đối chiếu định kỳ: lọc theo khách, in hoặc gửi ảnh qua Zalo cho khách xác nhận số dư.
• Sao lưu file hằng tuần lên đám mây hoặc USB, tránh mất cả sổ nợ khi hỏng máy.
• Bảo vệ file bằng mật khẩu nếu nhiều người dùng chung máy: đặt mật khẩu cho file Word, Excel.

Khi nào nên chuyển sang phần mềm?

Excel ổn khi bạn có dưới vài chục khách nợ. Nhiều khách, nhiều nhân viên cùng nhập, hoặc cần gắn công nợ với tồn kho và hóa đơn, thì dùng phần mềm bán hàng sẽ ít sai hơn. Quầy Việt PRO có quản lý bán hàng, kho, khách hàng và sổ nợ, chạy offline trên Windows. Xem thêm chọn phần mềm quản lý bán hàng cho cửa hàng.

Cần hỗ trợ thêm?

Đơn hàng từ 5 triệu tại cửa hàng được tặng phần mềm bán hàng Quầy Việt PRO, xem trang tặng phần mềm. Cần làm phần mềm riêng theo cách bán hàng của bạn, xem phần mềm theo yêu cầu hoặc gọi/Zalo 0943.847.333.