ZoneTechQuản lý bán hàng bằng Excel: cách dựng file tự trừ kho, tính lãi gộp
Quản lý bán hàng bằng Excel chạy ổn khi file có 5 phần nối với nhau bằng mã hàng: danh mục hàng, nhập kho, bán hàng, tồn kho và báo cáo. Bạn chỉ gõ ở 3 sheet nhập liệu, còn tồn kho, giá vốn và lãi gộp do công thức tính. Bài dành cho cửa hàng, đại lý nhỏ chưa muốn dùng phần mềm, và là phần mở rộng của bài quản lý kho bằng Excel sang phía bán hàng.
Tóm tắt nhanh
- Mọi sheet nối với nhau bằng mã hàng, không dùng tên hàng làm khóa.
- Sheet bán hàng ghi mỗi dòng một mặt hàng; đơn nhiều mặt hàng đánh dấu “cùng đơn”.
- Tồn kho = tồn đầu + nhập − bán − xuất hủy, tính bằng SUMIFS.
- Giá vốn bình quân gia quyền cuối tháng cho lãi gộp sát thực tế hơn lấy giá nhập lần cuối.
- Trả hàng ghi số lượng âm để doanh thu, giá vốn và tồn cùng điều chỉnh.
File quản lý bán hàng cần những sheet nào?
Một file đủ dùng cho cửa hàng nhỏ cần 3 sheet nhập liệu và 2 sheet báo cáo.
| Sheet | Bạn nhập gì | Công thức tính gì |
|---|---|---|
| DM_HANG | Mã, tên, đơn vị tính, giá bán, tồn tối thiểu, tồn đầu | Tồn hiện tại, cảnh báo hết hàng |
| NHAP_KHO | Ngày, nhà cung cấp, mã hàng, số lượng, đơn giá nhập; hoặc xuất hủy | Thành tiền, tên hàng |
| BAN_HANG | Ngày, mã khách, mã hàng, số lượng, hình thức thanh toán | Số đơn, đơn giá, thành tiền, giá vốn |
| TON_KHO | Chọn tháng | Nhập, xuất, tồn theo số lượng và giá trị |
| BAO_CAO | Không nhập | Doanh thu, giá vốn, lãi gộp từng tháng |
Cách dựng từng phần trên Excel
Bước 1. Danh mục hàng. Mỗi mặt hàng một dòng, mã hàng không trùng và không đổi sau khi đã bán. Kiểm tra trùng mã: =IF(COUNTIF($A$5:$A$504,A5)>1,"Trùng mã","").
Bước 2. Sheet bán hàng, mỗi dòng một mặt hàng. Đơn có 3 món thì ghi 3 dòng. Dòng đầu ghi ngày, khách, hình thức thanh toán; 2 dòng sau chỉ đánh “x” ở cột “Cùng đơn trên” và ghi mã hàng, số lượng. Cách này giúp SUMIFS cộng đúng theo mặt hàng mà vẫn gom được theo đơn.
Cột F là đơn giá gõ tay (nếu bán giá khác). Cột V tính đơn giá thực dùng: F để trống thì lấy giá bán ở cột E của danh mục (công thức chạy trên Excel 2016):
=IF(F5="",INDEX(DM_HANG!$E:$E,MATCH(D5,DM_HANG!$A:$A,0)),F5)
Cột W là thành tiền sau chiết khấu: =E5*V5*(1-G5/100), trong đó E là số lượng, G là chiết khấu phần trăm (gõ 5 nghĩa là 5%). Cột M (Kỳ) ghi năm và tháng của dòng bán dạng 202610: =IF(J5="","",YEAR(J5)*100+MONTH(J5)), với J là ngày đơn (dòng “cùng đơn” lấy ngày của dòng trên).
Bước 3. Tồn kho hiện tại. Có thể tách 3 cột nhập trong năm (K), bán trong năm (L), hủy/hao hụt (M) rồi tính tồn hiện tại ở cột N: =IF($A5="","",N(G5)+K5-L5-M5). Nếu muốn gọn một cột thì dùng:
=G5+SUMIFS(NHAP_KHO!$F:$F,NHAP_KHO!$E:$E,A5,NHAP_KHO!$B:$B,"Nhập mua")-SUMIFS(NHAP_KHO!$F:$F,NHAP_KHO!$E:$E,A5,NHAP_KHO!$B:$B,"Xuất hủy")-SUMIFS(BAN_HANG!$E:$E,BAN_HANG!$D:$D,A5)
Cảnh báo ở cột S: =IF(N5<0,"Âm kho",IF(N5=0,"Hết hàng",IF(N5<F5,"Dưới tồn tối thiểu","OK"))). “Âm kho” gần như luôn là dấu hiệu quên ghi phiếu nhập.
Bước 4. Giá vốn bình quân gia quyền cuối tháng. Đơn giá vốn của một mặt hàng trong tháng bằng (giá trị tồn đầu tháng + giá trị nhập trong tháng) chia (số lượng tồn đầu + số lượng nhập). Hàng bán và hàng hủy trong tháng đều tính theo đơn giá này.
Ví dụ giả định tháng 10: nước suối tồn đầu tháng 120 chai trị giá 360.000 đồng, nhập thêm 240 chai giá 2.900 đồng (696.000 đồng). Đơn giá vốn = (360.000 + 696.000) ÷ (120 + 240) ≈ 2.933 đồng/chai. Tháng đó xuất 52 chai (50 chai bán, 2 chai hủy) thì giá vốn khoảng 152.533 đồng.
Bước 5. Báo cáo tháng. Doanh thu tháng: =SUMIFS(BAN_HANG!$W:$W,BAN_HANG!$M:$M,THAM_SO!$B$9*100+A5), với cột A là số tháng và ô B9 sheet THAM_SO là năm. Lãi gộp = doanh thu − giá vốn; tỷ suất lãi gộp = lãi gộp ÷ doanh thu. Tách thêm 3 cột tiền mặt, chuyển khoản, ghi nợ để đối chiếu với két và sao kê.
Để có sẵn phần kho, bạn có thể tải mẫu quản lý kho bằng Excel bản Free: sheet DM_HANG (danh mục hàng, tồn đầu, tồn tối thiểu) và THE_KHO (thẻ kho nhập, xuất, tồn từng mặt hàng). Bạn ghi tay và tự tính tồn theo từng dòng; sheet bán hàng, giá vốn và báo cáo thì dựng thêm theo 5 bước trên, rồi thay phần cộng tay bằng SUMIFS. Không dùng macro, tải miễn phí, không cần để lại thông tin.
Xử lý trả hàng, hàng hỏng và phiếu bán hàng
| Tình huống | Ghi ở đâu | Cách ghi |
|---|---|---|
| Khách trả hàng | BAN_HANG | Số lượng âm; doanh thu, giá vốn giảm, hàng cộng lại kho |
| Hàng hỏng, hết hạn, hao hụt kiểm kê | NHAP_KHO | Loại “Xuất hủy”, chỉ ghi số lượng; giá trị tính theo giá vốn bình quân |
| Bán giá sỉ cho khách quen | BAN_HANG | Gõ đơn giá vào cột đơn giá, để trống thì lấy giá bán lẻ |
| Khách mua ghi nợ | BAN_HANG | Hình thức “Ghi nợ”, bắt buộc có mã khách |
Nếu ghi bằng thẻ kho của bản Free, hàng hỏng là một dòng loại “Xuất hủy” như ảnh trên, có ghi chú biên bản hủy.
Phiếu bán hàng in từ Excel là chứng từ giao cho khách và lưu nội bộ. Hộ, cá nhân kinh doanh có doanh thu năm trên 1 tỷ đồng phải dùng hóa đơn điện tử có mã của cơ quan thuế hoặc hóa đơn khởi tạo từ máy tính tiền theo Nghị định 141/2026/NĐ-CP, nên phiếu bán hàng không thay được hóa đơn trong trường hợp đó.
Checklist vận hành file bán hàng
- [ ] Mã hàng không trùng, không đổi sau khi đã bán.
- [ ] Hàng nhập ghi ngay trong ngày nhận hàng.
- [ ] Cuối ngày đối chiếu tiền mặt trong két với cột tiền mặt.
- [ ] Không xóa dòng, chỉ xóa nội dung ô nhập.
- [ ] Kiểm kê thực tế mỗi tháng, chênh lệch ghi “Xuất hủy”.
- [ ] Sao lưu file mỗi tuần, đặt tên theo ngày.
Lỗi hay gặp khi quản lý bán hàng bằng Excel
Lỗi đầu tiên là gõ tên hàng thay vì mã. “Nước suối 500ml” và “nuoc suoi 500ml” là 2 mặt hàng với SUMIFS, tồn kho sẽ sai ngay.
Lỗi thứ hai là tính lãi bằng giá nhập lần cuối. Khi giá nhập lên xuống, lãi gộp tháng sẽ dao động theo lần nhập chứ không theo thực tế bán.
Lỗi thứ ba là nhiều người cùng sửa một file Excel gửi qua Zalo, mỗi người giữ một bản. Hãy để một file chính duy nhất, hoặc đưa lên Google Sheets để cùng nhập. Khi số mặt hàng, số người bán tăng lên, xem bài so sánh phần mềm quản lý kho miễn phí để cân nhắc chuyển sang phần mềm.
Zonetech hỗ trợ gì?
ZoneWeb làm website bán hàng trên Haravan, Sapo hoặc WordPress. Gói 10 triệu đồng (4 đến 6 tuần, đã gồm thuế, chưa gồm tên miền, hosting hay thuê bao nền tảng và quảng cáo) có kết nối sàn, fanpage, Zalo OA, quy trình đơn, kho, giao, đối soát và báo cáo ngày. Nhận tư vấn miễn phí qua hotline 0828.229.666.
Câu hỏi thường gặp
Excel quản lý bán hàng được bao nhiêu mặt hàng?
Tùy cách bạn dựng. File mẫu bản Free có sẵn 50 dòng mặt hàng và 50 dòng trên mỗi thẻ kho, cần thêm thì chèn dòng. Khi file mở chậm hoặc nhiều người cần nhập cùng lúc, nên cân nhắc chuyển sang phần mềm.
Có nên dùng VLOOKUP thay INDEX MATCH không?
VLOOKUP cũng chạy, nhưng chỉ tìm được cột bên phải cột mã. INDEX MATCH không phụ thuộc vị trí cột nên ít lỗi hơn khi bạn chèn thêm cột. Excel 365 có thêm XLOOKUP gọn hơn nhưng không chạy trên Excel 2016.
Bán online và bán tại quầy có ghi chung một sheet không?
Ghi chung được, thêm cột “Kênh” để lọc. Tồn kho khi đó luôn khớp cho mọi kênh vì chỉ có một nơi trừ hàng.
Làm sao in nhiều phiếu bán hàng một lúc?
Dựng một sheet in theo số thứ tự đơn trong tháng, mỗi lần lấy 10 đơn, 2 phiếu một trang A4; đổi ô “từ đơn thứ” thành 11, 21 để in tiếp.
Tải file: tải bản Free file Excel danh mục hàng và thẻ kho, miễn phí, không cần để lại thông tin.
Cập nhật lần cuối: 14/10/2026.