Cách quản lý xuất nhập tồn kho bằng Excel và Google Sheets: công thức, cảnh báo, kiểm kê

Đừng gõ tay cột "tồn" rồi tự cộng trừ mỗi lần nhập, xuất — chỉ cần quên một lần là số sai mãi mà không biết sai từ đâu. Cách bền là ghi nghiệp vụ, để công thức tính tồn:

Công thức gốc

Tồn cuối = tồn đầu + nhập − xuất

Hàm chính

SUMIFS theo mã hàng

Cảnh báo

Tồn cuối ≤ mức tối thiểu

Ví dụ

SUA1L: 36 + 96 − 118 = 14 hộp

Có người thích gộp nhập và xuất vào một sổ chung với cột Loại (Nhập / Xuất). Cũng được — công thức đổi thành hai SUMIFS cùng cột SL, khác điều kiện Loại. Tách hai sổ thì dễ đọc hơn và cột Đơn giá chỉ cần ở sổ nhập.

• Mỗi dòng một mã hàng. Phiếu nhập 3 mặt hàng là 3 dòng cùng Số phiếu.
• Một mã, một đơn vị tính. Sữa tính theo hộp thì nhập 8 thùng phải ghi 96 hộp. Muốn gõ theo thùng thì thêm cột Số thùng và cột SL = Số thùng × 12, nhưng SUMIFS luôn cộng cột SL theo hộp.
• Chọn mã từ danh sách, không gõ tay: đặt xác thực dữ liệu cho cột Mã hàng, lấy danh sách từ DanhMuc!A2:A. Một mã gõ thừa dấu cách là SUMIFS không đếm.
• Xuất nào cũng ghi, không chỉ bán: hàng hỏng, hết hạn, lấy dùng, trả nhà cung cấp — ghi rõ ở cột Lý do để cuối tháng biết hao hụt bao nhiêu.
• Không sửa, không xoá dòng cũ để chữa số. Ghi sai thì thêm dòng điều chỉnh có ghi chú; lịch sử còn nguyên để tra.

Trong sheet DanhMuc, cột A là mã hàng, D là tồn đầu (số đếm được ngày bắt đầu dùng sổ). Thêm ba cột công thức F, G, H cho dòng 2 rồi kéo xuống:

F2 (Nhập):    =SUMIFS(Nhap!D:D;Nhap!C:C;A2)
G2 (Xuất):    =SUMIFS(Xuat!D:D;Xuat!C:C;A2)
H2 (Tồn cuối): =D2+F2-G2

Thử lại với SUA1L: tồn đầu 36 hộp; hai phiếu nhập 48 + 48 = 96; sổ xuất trong tháng cộng được 118 (115 bán, 3 hộp móp hỏng). Tồn cuối = 36 + 96 − 118 = 14 hộp.

Cách trên cộng toàn bộ sổ từ ngày bắt đầu, nên cột H là tồn hiện tại. Muốn xem riêng từng tháng mà không phải xoá sổ cũ hay gõ lại tồn đầu tháng, làm một sheet BaoCaoKy: B1 là từ ngày, B2 là đến ngày, từ dòng 5 cột A là mã hàng.

C5 Tồn đầu kỳ: =XLOOKUP(A5;DanhMuc!A:A;DanhMuc!D:D;0)+SUMIFS(Nhap!D:D;Nhap!C:C;A5;Nhap!A:A;"<"&$B$1)-SUMIFS(Xuat!D:D;Xuat!C:C;A5;Xuat!A:A;"<"&$B$1)
D5 Nhập kỳ:    =SUMIFS(Nhap!D:D;Nhap!C:C;A5;Nhap!A:A;">="&$B$1;Nhap!A:A;"<="&$B$2)
E5 Xuất kỳ:    =SUMIFS(Xuat!D:D;Xuat!C:C;A5;Xuat!A:A;">="&$B$1;Xuat!A:A;"<="&$B$2)
F5 Tồn cuối kỳ: =C5+D5-E5

Thử lại: sổ bắt đầu 01/09 với SUA1L tồn gốc 30; tháng 9 nhập 108, xuất 102. Đặt B1 = 01/10/2026, B2 = 31/10/2026: tồn đầu kỳ = 30 + 108 − 102 = 36, khớp với số đếm đầu tháng 10.

Cột E của DanhMuc là tồn tối thiểu: xuống tới mức này là phải đặt hàng, để hàng mới về kịp trước khi kệ trống. Cột I hiện trạng thái:

=IF(A2="";"";IF(H2<=0;"Hết hàng";IF(H2<=E2;"Sắp hết";"Đủ hàng")))

Đặt mức tối thiểu bao nhiêu? Một cách đơn giản: bán trung bình mỗi ngày × (số ngày chờ hàng về + vài ngày dự phòng), làm tròn lên theo thùng. Sữa bán 118 hộp trong 31 ngày ≈ 3,8 hộp/ngày; nhà phân phối giao sau 3 ngày, dự phòng 2 ngày: 3,8 × 5 ≈ 19 hộp, làm tròn lên 2 thùng = 24 hộp.

=ROUNDUP(G2/31*(3+2)/12;0)*12

Cho cả dòng đổi màu: chọn vùng A2:K, vào định dạng có điều kiện, chọn "Công thức tuỳ chỉnh" và nhập =AND($A2<>"";$H2<=$E2), tô nền cam. Danh sách hàng cần đặt ở một chỗ riêng:

=FILTER(A2:B;H2:H<=E2:E;A2:A<>"")

Sổ sách đúng công thức vẫn lệch với kệ hàng: quên ghi một lần xuất, hàng vỡ, hàng thất lạc. Kiểm kê định kỳ (hàng tháng, hoặc hàng tuần với hàng giá trị cao) là cách duy nhất biết số thật. Sheet KiemKe:

B2 Tồn sổ:     =XLOOKUP(A2;DanhMuc!A:A;DanhMuc!H:H;0)
C2 Đếm thực tế: (gõ tay)
D2 Chênh lệch:  =C2-B2
E2 Giá trị:     =D2*XLOOKUP(A2;DanhMuc!A:A;DanhMuc!J:J;0)

1. Kiểm lúc không bán, sau giờ đóng cửa; ghi hết phiếu nhập, xuất trong ngày trước khi đếm.
2. Tìm nguyên nhân trước khi chữa số: chênh dương thường là quên ghi phiếu nhập hoặc ghi xuất thừa — tra sổ trước.
3. Điều chỉnh bằng phiếu, không sửa tồn đầu hay sửa công thức: thiếu thì thêm dòng vào sổ xuất, lý do "Hao hụt kiểm kê"; thừa thì thêm dòng vào sổ nhập, lý do "Thừa kiểm kê", đơn giá bằng giá vốn. Sau đó tồn sổ khớp số đếm.
4. Theo dõi tổng hao hụt mỗi tháng bằng SUMIFS cột SL của sổ xuất có lý do "Hao hụt kiểm kê".

Giá trị tồn của một mã = tồn cuối × giá vốn. Cột K của DanhMuc =H2*J2, tổng cả kho:

=SUMPRODUCT(H2:H5;J2:J5)

Khó là giá vốn lấy số nào, vì mỗi đợt nhập một giá. Nước suối đợt 1 nhập 3.100/chai, đợt 2 nhập 3.300/chai. Cách đơn giản cho bảng tính là giá bình quân các lần nhập:

=IFERROR(SUMIFS(Nhap!F:F;Nhap!C:C;A2)/SUMIFS(Nhap!D:D;Nhap!C:C;A2);0)

Cách này chưa tính giá trị của tồn đầu và không đổi theo thời điểm xuất. Giá vốn bình quân di động (tính lại sau mỗi lần nhập) chính xác hơn nhưng làm bằng công thức bảng tính khá rối. Các cách tính giá vốn và ảnh hưởng tới lãi có trong bài cách tính giá vốn và lợi nhuận.

Nếu bạn đã ghi đơn bán mỗi dòng một món như trong bài quản lý bán hàng bằng Google Sheets, không cần chép lại số bán sang sổ xuất: cột Xuất lấy thẳng từ sổ đơn cộng với các lần xuất khác.

=SUMIFS(DonBan!F:F;DonBan!D:D;A2)+SUMIFS(Xuat!D:D;Xuat!C:C;A2)

Nhiều người cùng ghi sổ kho thì nên khoá các cột công thức (cách khoá ô công thức). Còn phân vân giữa file Excel trên máy và Google Sheets chia sẻ, xem Google Sheets hay Excel — thủ kho ghi trên điện thoại thì Google Sheets tiện hơn.

• Nhiều kho, nhiều cửa hàng: mỗi SUMIFS thêm điều kiện kho, cộng thêm phiếu chuyển kho đang trên đường.
• Hàng có lô và hạn dùng: phải biết lô nào còn bao nhiêu, xuất lô hạn sớm trước — công thức theo lô rất nhanh rối.
• Đơn vị quy đổi (thùng, lốc, chai) với giá riêng từng đơn vị.
• Kiểm kê cả trăm mã bằng giấy rồi về gõ lại.
• Giá vốn bình quân di động mà sửa một phiếu ngày cũ là phải tính lại các phiếu sau.

Bán hàng 360 PRO làm phần kho trên chính file Google Sheets của bạn, dùng qua web app: tới 5 kho / cửa hàng / xưởng, chuyển kho có trạng thái đang chuyển, kiểm kê bằng điện thoại nhiều người theo khu, lô và hạn dùng xuất hạn sớm trước, cảnh báo cận date, giá vốn bình quân di động theo từng kho (tính lại khi sửa phiếu ngày cũ), gợi ý nhập theo tốc độ bán, đơn mua nhận từng phần với phí vận chuyển chia vào giá vốn. Đơn bán tại quầy tự trừ kho, không phải ghi phiếu xuất.

Mở file bằng Excel thì tab Tồn kho và Thẻ kho vẫn tính bằng công thức, nhưng bạn tự gõ chứng từ và giá vốn từng dòng, Excel không tự tính giá vốn bình quân di động; web app, kiểm kê trên điện thoại, mã vạch chỉ có trên Google Sheets. Một file chứa tới 5.000 mã hàng và 80.000 dòng hàng.

• Cách quản lý bán hàng bằng Google SheetsSổ đơn bán lẻ, thu tiền, báo cáo doanh thu ngày, tháng.
• Cách tính giá vốn và lợi nhuậnGiá vốn lấy số nào, lãi gộp, lãi ròng.
• Google Sheets hay ExcelChọn công cụ cho sổ kho nhiều người cùng ghi.
• Hướng dẫn cài Bán hàng 360 PRONhập tồn đầu, cài kho, lô và hạn dùng.