Cách làm báo cáo kho trên Excel: nhập xuất tồn bằng SUMIFS

Để làm báo cáo kho trên Excel, bạn cần bảng tồn gốc, nhật ký nhập xuất và bảng tổng hợp theo mã hàng, kho, khoảng ngày. Dùng SUMIFS tính tồn đầu kỳ, nhập trong kỳ, xuất trong kỳ; sau đó tính tồn cuối = tồn đầu + nhập − xuất. Điểm cần kiểm tra kỹ là ngày chốt tồn gốc và phạm vi giao dịch, vì công thức đúng vẫn có thể cộng sai kỳ.

Hướng dẫn này dựng một báo cáo số lượng cho cửa hàng có nhiều kho. Dữ liệu là ví dụ giả lập để bạn nhập thử, không phải số liệu khách hàng; phần định giá hàng tồn và giá vốn cần một quy trình riêng.

1. Chốt quy tắc trước khi dựng file

Mỗi dòng báo cáo nên đại diện cho một cặp mã hàng và kho. Không chỉ dùng tên hàng: cùng một sản phẩm có thể được gõ thành nhiều tên, còn hai quy cách khác nhau có thể có tên gần giống nhau. Hãy đặt mã riêng cho từng quy cách và thống nhất đơn vị tính.

Ví dụ, cà phê gói 500 g có mã CF500, đơn vị là gói. Nếu nhập theo thùng nhưng xuất theo gói, bạn phải quy đổi về cùng đơn vị trước khi tổng hợp. Đừng cộng 2 thùng với 10 gói rồi gọi kết quả là 12 sản phẩm.

• Tồn gốc là số lượng ngay trước ngày bắt đầu lưu giao dịch.
• Nhật ký chỉ chứa giao dịch từ ngày gốc trở đi; không nhập lại lịch sử đã nằm trong tồn gốc.
• Số lượng nhập và xuất đều là số dương; cột loại quyết định chiều biến động.
• Chỉ đưa giao dịch đã xác nhận vào nguồn báo cáo.

Ở ví dụ này, tồn gốc là số dư cuối ngày 30/09/2026. Mốc bắt đầu lưu giao dịch là 01/10/2026. Nếu chọn kỳ sớm hơn mốc đó, file chưa đủ dữ liệu để tính và cần báo lỗi thay vì hiển thị số tồn có vẻ hợp lý.

2. Chuẩn bị ba bảng nguồn trong Excel

Tạo ba sheet DanhMuc, TonGoc và GiaoDich. Chọn từng vùng có tiêu đề, nhấn Ctrl+T, xác nhận bảng có hàng tiêu đề rồi đặt tên Table lần lượt là DM, TG và GD. Tham chiếu có cấu trúc giúp công thức đọc rõ tên cột và theo vùng dữ liệu của Table khi bạn bổ sung dòng.

Bảng danh mục và tồn gốc

Table DM gồm MaHang, TenHang, DVT. Table TG gồm MaHang, Kho, SoLuong. Với mỗi cặp mã hàng và kho, giữ đúng một dòng tồn gốc để tránh nhập trùng mà SUMIFS vẫn cộng hết.

MaHang	Kho	SoLuong trong TG
CF500	HN	100
CF500	HCM	40
TRA200	HN	60

Bảng nhật ký nhập xuất

Table GD gồm Ngay, SoPhieu, MaHang, Kho, Loai, SoLuong. Mỗi dòng là một mặt hàng trên một chứng từ tại một kho. Một phiếu có ba mặt hàng cần ba dòng, không gộp cả phiếu vào một ô.

Ngay	SoPhieu	MaHang	Kho	Loai	SoLuong
02/10/2026	N001	CF500	HN	Nhap	30
05/10/2026	X001	CF500	HN	Xuat	20
10/10/2026	N002	CF500	HN	Nhap	50
15/10/2026	X002	CF500	HN	Xuat	35
20/10/2026	X003	CF500	HCM	Xuat	10

Nhập ngày dưới dạng ngày thực của Excel, không phải chuỗi văn bản. Có thể dùng DATE(2026,10,2) để tạo ngày kiểm tra rồi định dạng dd/mm/yyyy. Cột SoLuong phải là số; cột Loai chỉ nhận Nhap hoặc Xuat qua Data Validation để hạn chế lỗi gõ.

3. Tạo bảng báo cáo theo khoảng ngày

Trong sheet BaoCao, đặt B1 là ngày đầu kỳ, B2 là ngày cuối kỳ và B3 là ngày bắt đầu lưu giao dịch. Lần lượt nhập 10/10/2026, 15/10/2026 và 01/10/2026 dưới dạng ngày thực. B1 phải lớn hơn hoặc bằng B3, còn B2 phải lớn hơn hoặc bằng B1.

Tạo tiêu đề tại hàng 5: A là MaHang, B là Kho, C là TonDau, D là NhapKy, E là XuatKy, F là TonCuoi. Hàng 6 nhập CF500 tại A6 và HN tại B6. Các công thức dưới dùng dấu phẩy; nếu Excel của bạn dùng dấu chấm phẩy, đổi dấu phân cách đối số cho phù hợp.

Tính tồn đầu kỳ tại C6

=SUMIFS(TG[SoLuong],TG[MaHang],$A6,TG[Kho],$B6)
+SUMIFS(GD[SoLuong],GD[MaHang],$A6,GD[Kho],$B6,GD[Loai],"Nhap",GD[Ngay],">="&$B$3,GD[Ngay],"<"&$B$1)
-SUMIFS(GD[SoLuong],GD[MaHang],$A6,GD[Kho],$B6,GD[Loai],"Xuat",GD[Ngay],">="&$B$3,GD[Ngay],"<"&$B$1)

Tồn đầu không cố định ở 100. Trước ngày 10/10, kho HN đã nhập 30 và xuất 20 gói CF500, nên tồn đầu kỳ là 110. Công thức lấy tồn gốc rồi cộng trừ biến động trước ngày đầu kỳ, giúp đổi kỳ mà không phải chép số tháng trước bằng tay.

Tính nhập, xuất và tồn cuối

Tại D6, tính lượng nhập từ đầu ngày B1 đến trước đầu ngày sau B2:

=SUMIFS(GD[SoLuong],GD[MaHang],$A6,GD[Kho],$B6,GD[Loai],"Nhap",GD[Ngay],">="&$B$1,GD[Ngay],"<"&($B$2+1))

Tại E6, dùng cùng khoảng ngày nhưng đổi loại giao dịch:

=SUMIFS(GD[SoLuong],GD[MaHang],$A6,GD[Kho],$B6,GD[Loai],"Xuat",GD[Ngay],">="&$B$1,GD[Ngay],"<"&($B$2+1))

Tại F6 nhập công thức sau rồi sao chép các công thức xuống những cặp mã hàng, kho còn lại:

=C6+D6-E6

Với dòng CF500–HN, kết quả phải là tồn đầu 110, nhập 50, xuất 35, tồn cuối 125 gói. Điều kiện nhỏ hơn B2+1 lấy được cả giao dịch có giờ trong ngày cuối kỳ. Nếu muốn hiểu kỹ cách lọc ngày và giờ, bạn có thể xem hướng dẫn làm báo cáo Excel theo ngày.

4. Đối chiếu bằng các tình huống dễ sai

Đừng dừng ở việc thấy bảng có số. Trước khi dùng thực tế, hãy đổi kỳ và đối chiếu kết quả bằng tay cho một mã hàng. Các số dưới đây đều lấy từ bộ dữ liệu giả lập ở trên.

Kỳ và kho của CF500	Tồn đầu	Nhập	Xuất	Tồn cuối
01–31/10, HN	100	80	55	125
10–15/10, HN	110	50	35	125
16–31/10, HN	125	0	0	125
01–31/10, HCM	40	0	10	30

Thử thêm một giao dịch lúc 18:00 ngày 15/10 và một giao dịch lúc 00:00 ngày 16/10. Báo cáo kết thúc ngày 15 phải lấy giao dịch đầu, bỏ giao dịch sau. Sau khi kiểm tra, xóa các dòng thử để không lẫn vào số liệu chính thức.

Một tổng số khớp chưa chứng minh từng mã đúng: thiếu một phiếu nhập và thiếu một phiếu xuất cùng lượng có thể triệt tiêu nhau. Hãy đối chiếu riêng tổng nhập với phiếu nhập, tổng xuất với phiếu xuất, rồi so tồn cuối từng mã với biên bản kiểm kê cùng thời điểm.

5. Những lỗi làm báo cáo kho lệch số

• Thiếu cặp mã hàng–kho: báo cáo phải liệt kê cả hàng có tồn gốc nhưng không phát sinh trong kỳ; không chỉ lấy danh sách từ các phiếu tháng này.
• Nhập trùng dòng phiếu: giữ mã dòng chứng từ nếu nguồn có. SoPhieu có thể lặp hợp lệ khi một phiếu chứa nhiều hàng, nên không xóa trùng chỉ theo số phiếu.
• Chuyển kho thiếu một chiều: cần dòng xuất tại kho đi và dòng nhập tại kho đến. Tổng toàn hệ thống không tăng chỉ vì chuyển kho nội bộ.
• Hàng trả lại, điều chỉnh: quy định rõ nghiệp vụ tăng hay giảm tồn, giữ chứng từ gốc và lý do; không sửa số dư để ép khớp.
• Mã có khoảng trắng: chuẩn hóa tại nguồn, đối chiếu danh mục và kiểm tra những mã không tìm thấy trước khi tổng hợp.

Tồn âm là tín hiệu cần kiểm tra ngày chứng từ, thiếu phiếu hoặc xuất nhầm kho, không phải lý do để tự động thay bằng 0. Đồng thời, không cộng số lượng của những đơn vị khác nhau thành một KPI “tổng tồn”: 125 gói cà phê và 60 hộp trà cần hiển thị theo mặt hàng hoặc đơn vị phù hợp.

6. Khi nào cần Power Query hoặc Power BI?

Nếu mỗi kho gửi một file cùng cấu trúc, bạn có thể gộp nhiều file Excel bằng Power Query trước khi chạy báo cáo. Vẫn cần kiểm tra trùng phiếu, kiểu ngày và đơn vị; thao tác gộp không tự giải quyết lỗi nghiệp vụ.

Khi cần phân tích nhiều kho, nhiều kỳ và chia sẻ báo cáo định kỳ, hãy tham khảo cách dựng dashboard quản lý kho bằng Power BI. Với giá trị tồn, bạn còn phải thống nhất cách tính giá và đối chiếu kế toán. Không lấy số lượng cuối kỳ nhân tùy ý với giá mua gần nhất rồi coi đó là giá trị tồn chính thức.

Câu hỏi thường gặp

Có dùng SUMIF thay SUMIFS được không?

SUMIF phù hợp khi chỉ có một điều kiện. Báo cáo này lọc đồng thời mã hàng, kho, loại và ngày nên dùng SUMIFS sẽ rõ hơn. Các vùng điều kiện phải có cùng kích thước với vùng cộng.

Tại sao thêm giao dịch mà báo cáo không đổi?

Kiểm tra dòng mới có nằm trong Table GD không, ngày có đúng kiểu dữ liệu và nằm trong kỳ không. Sau đó kiểm tra mã hàng, kho, loại giao dịch và chế độ tính toán của workbook.

Có cần tạo một sheet cho mỗi tháng không?

Không cần cho phần dữ liệu nguồn. Bạn có thể lưu nhật ký liên tục rồi đổi B1, B2 để xem từng kỳ. Khi chốt tháng, lưu bản báo cáo đã đối chiếu và kiểm soát việc nhập bổ sung chứng từ vào kỳ cũ.

File này có thay được phần mềm quản lý kho không?

Đây là cách tổng hợp số lượng từ dữ liệu đã xác nhận. Những nhu cầu như nhiều người nhập đồng thời, phân quyền, lô hàng hoặc hạn sử dụng cần thiết kế thêm và có thể phù hợp hơn với phần mềm chuyên dụng.

Nếu bạn đã có dữ liệu nhập xuất nhưng cần người dựng báo cáo Power BI và kiểm tra cách tính tồn theo kỳ, hãy xem báo giá làm Power BI trên SenData và gửi cấu trúc dữ liệu cùng yêu cầu đối chiếu để trao đổi phạm vi.