Phần mềm quản lý kho bằng Excel: cấu trúc file nhập–xuất–tồn và công thức
Phần mềm quản lý kho bằng Excel là một file bảng tính gồm sheet danh mục hàng, sheet nhập, sheet xuất và sheet tồn kho, trong đó tồn cuối được tính bằng công thức: Tồn cuối = Tồn đầu + Tổng nhập − Tổng xuất, dùng hàm SUMIFS theo mã hàng. Cách này miễn phí và đủ cho kho ít mã hàng, một người nhập liệu.
Rất nhiều kho nhỏ bắt đầu bằng Excel, và điều đó hợp lý: không tốn tiền, sửa được theo ý mình, ai cũng biết mở. Vấn đề chỉ xuất hiện khi file được dựng sai cấu trúc ngay từ đầu, hoặc khi kho đã lớn hơn sức chịu của bảng tính. Bài này hướng dẫn cấu trúc file nhập–xuất–tồn chuẩn, công thức tính tồn, giới hạn của Excel, sáu dấu hiệu nên chuyển sang phần mềm và cách mang dữ liệu đi.
File Excel quản lý kho nhập–xuất–tồn gồm những sheet nào?
Nguyên tắc gốc là tách dữ liệu gốc khỏi báo cáo. Mỗi lần hàng vào hoặc ra được ghi thành một dòng trong sheet giao dịch, không sửa trực tiếp con số tồn. Số tồn chỉ là kết quả của công thức cộng trừ từ các dòng đó. Làm vậy thì khi tồn sai, thủ kho lần ngược được dòng nào gây sai. File mà người dùng gõ đè số tồn mỗi ngày không lần lại được, và sai một lần là sai mãi.
Một file đủ dùng có năm sheet. Sheet danh mục là nơi mỗi mặt hàng xuất hiện đúng một lần với mã hàng riêng. Sheet nhập và sheet xuất ghi từng dòng giao dịch theo ngày. Sheet tồn kho lấy mã từ danh mục và tính số liệu bằng công thức. Sheet tồn đầu kỳ giữ số đếm thực tế tại ngày bắt đầu. Các cột chọn mã hàng nên dùng danh sách thả xuống (data validation) để không ai gõ sai mã.
| Sheet | Các cột chính | Ai nhập | Lưu ý |
|---|---|---|---|
| DanhMuc | Mã hàng, tên hàng, đơn vị tính, nhóm hàng, tồn tối thiểu | Người phụ trách kho | Mỗi mã một dòng, không trùng, không đổi mã giữa kỳ |
| TonDau | Mã hàng, số lượng tồn đầu, ngày chốt | Người phụ trách kho | Lấy từ số đếm thực tế, không lấy từ trí nhớ |
| Nhap | Ngày, số phiếu, mã hàng, tên hàng (tự dò), số lượng, nhà cung cấp | Thủ kho | Mỗi dòng một mặt hàng của một phiếu |
| Xuat | Ngày, số phiếu, mã hàng, tên hàng (tự dò), số lượng, bộ phận hoặc khách nhận | Thủ kho | Không gõ số âm; trả hàng ghi thành dòng nhập |
| TonKho | Mã hàng, tên hàng, tồn đầu, tổng nhập, tổng xuất, tồn cuối, tồn tối thiểu | Không ai nhập | Toàn bộ là công thức, nên khoá sheet |
Công thức tính tồn kho trong Excel bằng SUMIFS
Giả sử trong sheet Nhap và Xuat, cột C là mã hàng và cột E là số lượng; trong sheet TonKho, cột A là mã hàng. Tổng nhập của mã ở ô A2 được tính bằng: =sumifs(Nhap!E:E; Nhap!C:C; A2). Tổng xuất tương tự: =sumifs(Xuat!E:E; Xuat!C:C; A2). Tồn cuối bằng tồn đầu cộng tổng nhập trừ tổng xuất. Tuỳ thiết lập máy, dấu ngăn tham số là chấm phẩy hoặc phẩy. Công thức không cần ký tự nháy vì điều kiện là một ô tham chiếu.
Muốn xem tồn tại một ngày bất kỳ, thêm điều kiện ngày vào hàm. Giả sử cột A của sheet Nhap là ngày. Gõ điều kiện vào ô H1 của sheet TonKho dưới dạng dấu nhỏ hơn hoặc bằng đứng trước ngày cần xem, ví dụ <=31/10/2026, rồi thêm một cặp vùng và điều kiện: =sumifs(Nhap!E:E; Nhap!C:C; A2; Nhap!A:A; H1). Đổi ngày ở ô H1 là toàn bộ bảng tồn tính lại. Tên hàng trong sheet giao dịch nên tự dò từ danh mục bằng hàm xlookup hoặc vlookup theo mã, thay vì gõ tay.
Ví dụ kiểm tra công thức với ba mã hàng tự đặt trong bảng dưới. Mã VT002 có tồn đầu 40, nhập 100, xuất 115, nên tồn cuối là 40 + 100 − 115 = 25, thấp hơn tồn tối thiểu 30 và cần đặt hàng. Để cảnh báo tự hiện, dùng định dạng có điều kiện (conditional formatting) tô màu dòng có tồn cuối nhỏ hơn hoặc bằng tồn tối thiểu. Mức tồn tối thiểu có thể tính bằng công cụ điểm đặt hàng lại ở trang chính của cụm.
| Mã hàng | Tồn đầu | Tổng nhập | Tổng xuất | Tồn cuối | Tồn tối thiểu | Trạng thái |
|---|---|---|---|---|---|---|
| VT001 | 120 | 300 | 260 | 160 | 100 | Đủ hàng |
| VT002 | 40 | 100 | 115 | 25 | 30 | Cần đặt hàng |
| VT003 | 0 | 500 | 180 | 320 | 150 | Đủ hàng |
Quản lý kho bằng Excel miễn phí có những giới hạn nào?
Giới hạn đầu tiên là nhiều người dùng. File lưu trên một máy chỉ cho một người sửa; gửi qua Zalo thì sinh ra nhiều bản và không ai chắc bản nào mới. Google Sheets hoặc Excel trên nền web cho nhiều người sửa cùng lúc, nhưng vẫn không phân quyền theo chứng từ: người nhập phiếu xuất cũng sửa được danh mục và công thức. Bảng tính cũng không có nhật ký nghiệp vụ cho biết ai đã đổi số lượng của phiếu nào.
Giới hạn thứ hai là kiểm soát. Excel không chặn xuất quá số tồn, không bắt buộc số phiếu liên tục, không khoá kỳ đã chốt. Một công thức bị kéo lệch dòng cho kết quả sai mà không báo lỗi. Giới hạn thứ ba là nghiệp vụ nâng cao: nhiều kho và chuyển kho, lô và hạn dùng, vị trí kệ, quét mã vạch, tính giá xuất kho theo bình quân hoặc nhập trước xuất trước. Làm được bằng công thức, nhưng file trở nên nặng và chỉ người dựng mới hiểu.
- Không phân quyền theo vai trò và không có nhật ký sửa theo từng phiếu.
- Không chặn tồn âm, không khoá số liệu của kỳ đã chốt.
- Sai công thức không hiện cảnh báo; phụ thuộc một người hiểu file.
- File chậm dần khi số dòng giao dịch lên hàng chục nghìn.
- Không kết nối trực tiếp bán hàng, mua hàng, kế toán, thiết bị quét.
6 dấu hiệu nên chuyển từ Excel sang phần mềm quản lý kho
Excel còn đủ khi kho có một địa điểm, vài trăm mã hàng, vài chục dòng giao dịch mỗi ngày và một người chịu trách nhiệm file. Ở mức đó, chuyển sang phần mềm chưa chắc đem lại lợi ích tương xứng với công chuyển đổi. Doanh nghiệp nên ở lại với Excel, giữ cấu trúc năm sheet và kiểm kê đối chiếu đều đặn. Quyết định chuyển nên dựa trên dấu hiệu vận hành cụ thể, không dựa trên cảm giác file đã cũ.
Sáu dấu hiệu dưới đây là những tình huống bảng tính không xử lý gọn được. Xuất hiện một dấu hiệu thì nên theo dõi. Xuất hiện từ hai đến ba dấu hiệu cùng lúc thì chi phí của sai sót và công đối soát thường đã vượt chi phí phần mềm. Khi đó bước tiếp theo là liệt kê nghiệp vụ bắt buộc và dùng thử một vài loại giải pháp bằng dữ liệu thật, bắt đầu từ loại có chi phí thấp.
- Tồn sổ lệch tồn thực sau hầu hết các lần kiểm kê mà không lần ra nguyên nhân.
- Từ ba người trở lên cần nhập liệu hoặc xem tồn cùng lúc.
- Có từ hai kho hoặc hai địa điểm, phát sinh chuyển kho thường xuyên.
- Hàng có lô, hạn dùng hoặc số serial cần truy xuất khi khách khiếu nại.
- Bán hàng hoặc mua hàng cần số tồn tức thời, không chờ thủ kho cập nhật file.
- File chỉ một người hiểu; người đó nghỉ thì không ai dám sửa công thức.
Cách chuyển dữ liệu từ Excel sang phần mềm: tồn đầu kỳ
Không cần mang toàn bộ lịch sử giao dịch sang hệ thống mới. Cách làm phổ biến là chọn một ngày chốt, thường là đầu tháng, mang sang ba thứ: danh mục hàng đã làm sạch, đơn vị tính và số tồn đầu kỳ theo từng kho. File Excel cũ giữ lại để tra cứu. Trước ngày chốt cần rà mã trùng, gộp các tên gọi khác nhau của cùng một mặt hàng và quyết định quy tắc đặt mã dùng lâu dài.
Số tồn đầu kỳ nên là số đếm thực tế, không phải số trên file, vì mang số sai sang hệ thống mới là mang theo cả sai lệch cũ. Tổ chức một đợt kiểm kê toàn bộ sát ngày chốt, ngừng nhập xuất trong lúc đếm, rồi nhập kết quả. Trong ERPNext, chứng từ Stock Reconciliation dùng để nhập tồn đầu kỳ và điều chỉnh tồn sau kiểm kê. Nếu hệ thống tính giá trị tồn kho, cần thêm đơn giá của từng mặt hàng tại ngày chốt, do kế toán cung cấp.
Hai đến bốn tuần đầu nên chạy song song: thủ kho ghi trên phần mềm, một người đối chiếu tổng nhập, tổng xuất, tồn cuối với file Excel theo tuần. Khi hai bên khớp qua vài kỳ thì dừng file cũ. Uptech thực hiện bước chuyển dữ liệu này trong các dự án triển khai phần mềm kho theo phạm vi hợp đồng. Việc chọn phương pháp tính giá hàng tồn kho để ghi sổ thuộc chuyên môn kế toán, doanh nghiệp cần hỏi kế toán của mình.
- Bước 1: làm sạch danh mục, chốt quy tắc mã hàng và đơn vị tính.
- Bước 2: chọn ngày chốt, kiểm kê thực tế toàn bộ kho.
- Bước 3: nhập danh mục, sau đó nhập tồn đầu kỳ theo từng kho.
- Bước 4: chạy song song với file Excel, đối chiếu theo tuần.
- Bước 5: dừng file cũ, lưu trữ bản chỉ đọc để tra cứu.
Excel, Access hay phần mềm quản lý kho: chọn theo quy mô
Access là bước giữa mà một số kho chọn: dữ liệu nằm trong bảng có quan hệ, có biểu mẫu nhập và báo cáo, chặn được mã trùng. Đổi lại cần người biết thiết kế cơ sở dữ liệu, vẫn chạy trên máy tính nội bộ và khó dùng trên điện thoại. Doanh nghiệp đã có người rành Access thì đây là phương án chi phí thấp. Chưa có thì công học và công dựng thường ngang với việc cấu hình một phần mềm kho có sẵn.
Phần mềm quản lý kho thêm những thứ bảng tính không có: phân quyền, nhật ký thao tác, chặn tồn âm, nhiều kho, lô và hạn dùng, quét mã, kết nối bán hàng và kế toán. Mã nguồn mở như ERPNext không thu phí bản quyền theo người dùng nhưng cần triển khai. Bảng chọn nhanh: một kho, một người nhập thì Excel; vài người, cần biểu mẫu chặt hơn thì Google Sheets hoặc Access; nhiều kho, nhiều người, có lô hạn dùng thì phần mềm.
Câu hỏi thường gặp
Giải đáp nhanh những điểm doanh nghiệp hay hỏi về chủ đề này.
Phần mềm quản lý kho bằng Excel là gì?
Phần mềm quản lý kho bằng Excel là file bảng tính tự dựng để ghi nhập, xuất và tính tồn kho, không phải một phần mềm cài đặt riêng. File chuẩn gồm sheet danh mục hàng, tồn đầu kỳ, nhập, xuất và tồn kho. Tồn cuối được tính bằng hàm SUMIFS theo mã hàng. Cách này miễn phí nếu doanh nghiệp đã có Excel hoặc dùng Google Sheets.
Công thức tính tồn kho trong Excel là gì?
Tồn cuối = Tồn đầu + Tổng nhập − Tổng xuất. Tổng nhập của một mã hàng tính bằng hàm sumifs: vùng cộng là cột số lượng của sheet nhập, vùng điều kiện là cột mã hàng, điều kiện là ô chứa mã cần tính. Tổng xuất làm tương tự với sheet xuất. Ví dụ tồn đầu 40, nhập 100, xuất 115 thì tồn cuối là 25.
Quản lý kho bằng Excel có quản lý được nhiều kho không?
Được, nhưng file phức tạp hơn nhiều. Cần thêm cột mã kho vào sheet nhập, xuất và thêm điều kiện mã kho trong hàm sumifs. Chuyển kho phải ghi thành hai dòng: một dòng xuất ở kho đi, một dòng nhập ở kho đến. Thiếu một dòng là tổng tồn sai. Từ hai kho có chuyển hàng thường xuyên, phần mềm xử lý việc này an toàn hơn.
Nên dùng Excel hay Google Sheets để quản lý kho?
Chọn theo số người dùng. Excel trên máy tính xử lý file lớn nhanh hơn và hợp khi chỉ một người nhập liệu. Google Sheets hợp khi hai đến ba người cần nhập hoặc xem cùng lúc từ nhiều nơi, có lịch sử phiên bản để khôi phục. Cả hai đều không phân quyền theo chứng từ và không chặn tồn âm, nên vẫn cần kiểm kê đối chiếu định kỳ.
Chuyển từ Excel sang phần mềm quản lý kho mất bao lâu?
Phụ thuộc số mã hàng, số kho và mức sạch của dữ liệu, nên không có con số chung. Phần tốn thời gian là làm sạch danh mục và kiểm kê thực tế để có tồn đầu kỳ, không phải thao tác nhập file. Nên dự trù thêm hai đến bốn tuần chạy song song để đối chiếu trước khi dừng file Excel cũ.
Tài liệu chính thức được dẫn trong bài
- ERPNext – Stock Reconciliation (nhập tồn đầu kỳ, điều chỉnh sau kiểm kê)docs.frappe.io/erpnext/user/manual/en/stock-reconciliation
- ERPNext – trang sản phẩm, giấy phép, phân hệfrappe.io/erpnext
Nhờ Uptech khảo sát kho và đề xuất phần mềm
Gửi số mã hàng, số kho, loại hàng và phần mềm đang dùng. Uptech đề xuất loại phần mềm phù hợp, phạm vi triển khai và báo giá theo giai đoạn.
- Phản hồi trong ngày làm việc gần nhất
- Tư vấn trung lập: ERPNext, Odoo hoặc viết riêng
- Hợp đồng, nghiệm thu, hoá đơn VAT điện tử