ZoneTechQuản lý kho vật tư bằng Excel: bảng định mức, cấp phát và tồn
Quản lý kho vật tư bằng Excel cần thêm hai thứ mà kho hàng bán không có: bảng định mức và sổ cấp phát theo lệnh sản xuất. Từ hai bảng này, Excel tính được mỗi lệnh cần bao nhiêu vật tư và đã cấp thừa hay thiếu. Cách dựng sổ nhập xuất tồn cơ bản có ở bài quản lý kho bằng Excel.
Tóm tắt nhanh
- Quản lý kho vật tư bằng Excel xuất theo lệnh sản xuất, không theo đơn bán.
- Cần cấp = số lượng lệnh × định mức × (1 + hao hụt), làm tròn lên.
- Vật tư trả lại kho ghi một dòng riêng, không sửa dòng cấp cũ.
Mục lục
Kho vật tư khác kho hàng bán ở chỗ nào?
Hàng bán đi ra khỏi kho theo đơn của khách. Vật tư đi ra theo lệnh sản xuất, và lượng ra phải khớp định mức. Lệnh sản xuất là phiếu giao xưởng làm một số lượng thành phẩm cụ thể, có số lệnh riêng.
| Điểm | Kho hàng bán | Kho vật tư |
|---|---|---|
| Căn cứ xuất | Đơn hàng | Lệnh sản xuất |
| Câu hỏi chính | Còn bao nhiêu để bán? | Lệnh này đã cấp đúng định mức chưa? |
| Hao hụt | Hàng hỏng, ghi hủy | Có tỷ lệ cho phép trong định mức |
Mẫu quản lý kho vật tư bằng Excel gồm 7 sheet
Trong mẫu quản lý kho vật tư bằng Excel, năm sheet ghi tay và hai sheet chỉ có công thức.
| Sheet | Cột chính | Ai ghi |
|---|---|---|
| DanhMucVT | MaVT, TenVT, DonVi, TonDau, TonToiThieu | Thủ kho, một lần |
| DinhMuc | MaTP, MaVT, DinhMuc, HaoHut | Tổ trưởng sản xuất hoặc kỹ thuật |
| NhapVT | Ngay, MaVT, SoLuong, NhaCungCap | Thủ kho |
| LenhSX | SoLenh, Ngay, MaTP, SoLuong | Người lập kế hoạch |
| CapPhat | Ngay, SoLenh, MaVT, SoLuong, Loai (Cấp hoặc Trả) | Thủ kho |
| DoiChieu | Mỗi dòng một cặp lệnh và vật tư | Chỉ công thức |
| TonVT | Mỗi dòng một mã vật tư | Chỉ công thức |
Lập sheet Định mức
Định mức là lượng vật tư cần để làm ra một thành phẩm. Mỗi dòng của sheet là một cặp thành phẩm và vật tư. Cột HaoHut định dạng phần trăm, gõ 2% thì Excel lưu 0,02.
Vật tư dùng chung cho nhiều thành phẩm thì định mức là số lẻ. Một thùng carton đựng 20 hộp thì định mức thùng là 0,05 cho mỗi hộp. Hao hụt nên lấy từ số đã ghi của vài lệnh trước, không đoán.
Công thức sheet Đối chiếu theo lệnh
Sheet này là phần quan trọng nhất khi quản lý kho vật tư bằng Excel. Khi mở lệnh mới, chép các dòng định mức của thành phẩm đó sang sheet DoiChieu, mỗi vật tư một dòng. Cột A ghi số lệnh, cột B ghi mã vật tư, bắt đầu từ dòng 2.
| Cột | Nội dung | Công thức |
|---|---|---|
| C | Mã thành phẩm | =INDEX(LenhSX!C:C;MATCH(A2;LenhSX!A:A;0)) |
| D | Số lượng lệnh | =INDEX(LenhSX!D:D;MATCH(A2;LenhSX!A:A;0)) |
| E | Định mức | =SUMIFS(DinhMuc!C:C;DinhMuc!A:A;C2;DinhMuc!B:B;B2) |
| F | Hao hụt | =SUMIFS(DinhMuc!D:D;DinhMuc!A:A;C2;DinhMuc!B:B;B2) |
| G | Cần cấp | =ROUNDUP(ROUND(D2*E2*(1+F2);2);0) |
| H | Đã cấp ròng | =SUMIFS(CapPhat!D:D;CapPhat!B:B;A2;CapPhat!C:C;B2;CapPhat!E:E;"Cấp")-SUMIFS(CapPhat!D:D;CapPhat!B:B;A2;CapPhat!C:C;B2;CapPhat!E:E;"Trả") |
| I | Chênh lệch | =H2-G2 |
| J | Cảnh báo | =IF(I2>0;"Vượt định mức";IF(I2<0;"Còn thiếu";"Đủ")) |
INDEX và MATCH chạy được cả trên Excel 2016, 2019. XLOOKUP chỉ có từ Excel 2021, 2024 và Microsoft 365, không có trong Excel 2016, 2019 (Hỗ trợ Microsoft, đọc ngày 16/10/2026). Hàm ROUND bên trong bỏ phần lẻ do phép nhân số thập phân, để ROUNDUP không làm tròn lên thừa một đơn vị.
Cột E dùng SUMIFS vì hàm này lọc theo hai điều kiện cùng lúc (Hỗ trợ Microsoft). Cặp không có trong định mức cho 0, dễ nhận ra.
Ví dụ quản lý kho vật tư bằng Excel: lệnh 500 hộp trà
Ví dụ giả định, số minh họa: xưởng đóng gói mở lệnh LSX-01 cho 500 hộp trà 100 g. Thùng cấp 26, sau lệnh trả lại 1 nên đã cấp ròng là 25.
| Vật tư | Định mức | Hao hụt | Cần cấp | Đã cấp ròng | Chênh | Cảnh báo |
|---|---|---|---|---|---|---|
| Túi zip 100 g | 1 | 2% | 510 | 520 | 10 | Vượt định mức |
| Hộp giấy | 1 | 1% | 505 | 505 | 0 | Đủ |
| Tem nhãn | 1 | 3% | 515 | 500 | -15 | Còn thiếu |
| Thùng carton | 0,05 | 0% | 25 | 25 | 0 | Đủ |
Túi zip cấp thừa 10 cái, thủ kho hỏi tổ đóng gói trước khi cấp tiếp. Tem nhãn thiếu 15 nhưng lệnh đã xong thì đó là phần hao hụt chưa dùng tới. Đọc bảng này mỗi cuối lệnh là thói quen chính khi quản lý kho vật tư bằng Excel.
Tồn vật tư và chứng từ xuất kho
Sheet TonVT khép lại mẫu quản lý kho vật tư bằng Excel: tồn đầu cộng nhập, trừ đã cấp ròng. Cột A ghi mã vật tư, bắt đầu từ dòng 2.
- B, tồn đầu:
=INDEX(DanhMucVT!D:D;MATCH(A2;DanhMucVT!A:A;0)) - C, đã nhập:
=SUMIFS(NhapVT!C:C;NhapVT!B:B;A2) - D, đã cấp ròng:
=SUMIFS(CapPhat!D:D;CapPhat!C:C;A2;CapPhat!E:E;"Cấp")-SUMIFS(CapPhat!D:D;CapPhat!C:C;A2;CapPhat!E:E;"Trả") - E, tồn hiện tại:
=B2+C2-D2 - F, cảnh báo:
=IF(E2<INDEX(DanhMucVT!E:E;MATCH(A2;DanhMucVT!A:A;0));"Cần mua";"")
Sổ cấp phát là sổ theo dõi nội bộ, ghi vật tư đã cấp và đã trả theo từng lệnh. Chứng từ giao kế toán theo Thông tư 99/2025/TT-BTC, hiệu lực từ 1/1/2026, thay Thông tư 200/2014/TT-BTC. Phiếu xuất kho mẫu 02-VT nằm ở Phụ lục I. Theo LuatVietnam, Điều 9 cho doanh nghiệp tham khảo mẫu này và được sửa đổi, bổ sung. Hộ kinh doanh ghi sổ S2d-HKD theo Thông tư 152/2025/TT-BTC.
Ghi phiếu cấp phát bằng nút bấm, chặn cấp vượt tồn thì xem bài quản lý kho bằng Excel VBA. Nhiều tổ cùng ghi một lúc thì cân nhắc quản lý kho bằng Access.
Cần người kèm đội bán hàng?
Quản lý kho vật tư bằng Excel cần một người chốt sổ cấp phát mỗi cuối lệnh. ZoneTech cùng doanh nghiệp đặt nếp vận hành đó qua giải pháp vận hành bán hàng ZoneOps. Giá từng gói có ở bảng giá.
Câu hỏi thường gặp
Tỷ lệ hao hụt vật tư nên lấy bao nhiêu?
Lấy từ số thực tế của vài lệnh gần nhất trong file quản lý kho vật tư bằng Excel: lượng cấp ròng trừ lượng cần theo định mức gốc, chia cho lượng cần. Không có một tỷ lệ chung đúng cho mọi xưởng.
Vật tư thừa sau lệnh ghi thế nào?
Ghi một dòng Trả ở sheet CapPhat, cùng số lệnh và mã vật tư. Công thức tự trừ ra, tồn kho tự cộng lại.
Cập nhật lần cuối: 01/11/2026 · Biên soạn: Đỗ Hữu Thuận