Cách tạo phiếu nhập xuất kho bằng Excel: tự tính tồn, in theo số phiếu

Q: Quản lý kho bằng Excel hay Google Sheets tốt hơn cho cửa hàng nhỏ?

Excel phù hợp khi một người nhập liệu trên một máy tính và cần in phiếu đẹp, ổn định. Google Sheets phù hợp khi nhiều nhân viên cần nhập kho từ nhiều nơi, kể cả trên điện thoại, vì dữ liệu nằm trên mạng và đồng bộ ngay. Cấu trúc danh mục, nhật ký, SUMIFS tính tồn kho ở cả hai công cụ gần như giống nhau.

Q: File quản lý kho Excel dùng được bao nhiêu dòng thì bắt đầu chậm?

Excel chứa được hơn một triệu dòng mỗi sheet, nhưng file kho dùng nhiều SUMIFS sẽ chậm dần khi nhật ký lên vài chục nghìn dòng, nhất là cột tồn sau giao dịch. Cách xử lý thường dùng là mỗi năm tạo file mới, lấy tồn cuối năm cũ làm tồn đầu kỳ của file mới.

Q: Có tính được giá vốn hàng xuất kho bằng Excel không?

Có, nhưng phức tạp hơn tính số lượng. Cách đơn giản nhất là bình quân gia quyền cuối kỳ: lấy tổng giá trị tồn đầu cộng giá trị nhập, chia cho tổng số lượng tồn đầu cộng số lượng nhập, rồi nhân với số lượng xuất. Nếu doanh nghiệp có kế toán, nên dùng đúng phương pháp tính giá xuất kho mà kế toán đã đăng ký áp dụng.

Q: Làm sao để nhân viên không sửa nhầm công thức trong file kho Excel?

Mở khóa các cột nhập tay (SoPhieu, Ngay, Loai, MaHang, SoLuong, DonGia), giữ khóa các cột công thức, rồi bật Review → Protect Sheet. Nhân viên vẫn ghi phiếu bình thường nhưng không xóa được công thức tồn kho; cách làm chi tiết có trong bài khóa ô, bảo vệ công thức trên blog Điền Nguyễn AZ.

Cách làm phiếu nhập xuất kho bằng Excel gọn nhất cho cửa hàng nhỏ là: ghi mọi lần nhập và xuất vào một bảng nhật ký duy nhất, để Excel tự cộng ra tồn kho, rồi làm một trang phiếu in chỉ cần chọn số phiếu là tự điền đủ ngày, đối tác, danh sách hàng và tổng tiền.

Bài này hướng dẫn dựng file gồm 3 sheet: DanhMuc (danh mục hàng + tồn kho), NhatKy (nhật ký nhập/xuất) và Phieu (mẫu phiếu để in). Công thức dùng XLOOKUP và FILTER nên cần Excel Microsoft 365 hoặc Excel 2021 trở lên; phần cuối có ghi chú cho bản cũ.

Vì sao không làm mỗi phiếu một sheet?

Nhiều cửa hàng làm theo kiểu: mỗi phiếu nhập là một sheet (PN01, PN02...), mỗi phiếu xuất một sheet khác. Cách này in đẹp nhưng sau vài tháng có hàng trăm sheet, và muốn biết "áo thun size M còn bao nhiêu" thì không có công thức nào cộng được.

Nguyên tắc của bài này: dữ liệu nằm một chỗ, phiếu chỉ là cách hiển thị. Mỗi dòng trong nhật ký là một mặt hàng của một phiếu. Phiếu có 5 mặt hàng thì ghi 5 dòng cùng số phiếu. Nhờ vậy tồn kho, báo cáo theo tháng hay theo nhà cung cấp đều tính được bằng công thức.

Bước 1: Tạo sheet DanhMuc và chuyển thành Table

Tạo sheet tên DanhMuc, gõ tiêu đề từ A1 đến I1:

A	B	C	D	E	F	G	H	I
MaHang	TenHang	DVT	TonDau	MucToiThieu	TongNhap	TongXuat	TonCuoi	CanhBao
G001	Gạo ST25 túi 5kg	Túi	20	10
N002	Nước mắm Nam Ngư 500ml	Chai	48	24
D003	Dầu ăn Neptune 1L	Chai	30	12

TonDau: số tồn thực tế lúc bắt đầu dùng file (đếm kho một lần).
MucToiThieu: tồn bằng hoặc dưới mức này thì cần nhập thêm.

Chọn một ô trong vùng dữ liệu, nhấn Ctrl + T → tích My table has headers → OK. Sang tab Table Design, ô Table Name ở góc trái, đổi tên thành tblHang.

Lý do dùng Table: thêm mã hàng mới ở dòng cuối, mọi công thức và danh sách chọn tự mở rộng theo, không phải sửa vùng A2:A100 bằng tay.

Bước 2: Tạo sheet NhatKy — nơi ghi mọi lần nhập, xuất

Tạo sheet NhatKy, gõ tiêu đề từ A1 đến L1 rồi cũng nhấn Ctrl + T, đặt tên Table là tblKho:

Cột	Tiêu đề	Cách nhập
A	SoPhieu	Gõ tay: PN0001, PX0001...
B	Ngay	Gõ ngày dạng 03/11/2026
C	Loai	Chọn Nhập hoặc Xuất
D	DoiTac	Nhà cung cấp hoặc khách
E	MaHang	Chọn từ danh sách
F	TenHang	Công thức
G	DVT	Công thức
H	SoLuong	Gõ tay
I	DonGia	Gõ tay (giá nhập hoặc giá bán)
J	ThanhTien	Công thức
K	TonSauGD	Công thức (nâng cao)
L	GhiChu	Gõ tay

Quan trọng: giữ đúng thứ tự cột E → J liền nhau (MaHang, TenHang, DVT, SoLuong, DonGia, ThanhTien), vì trang phiếu ở Bước 5 lấy nguyên cụm cột này.

Công thức tự điền

Ở dòng dữ liệu đầu tiên (dòng 2), gõ các công thức sau. Trong Table, gõ một ô là cả cột tự điền.

Cột F (TenHang):

=XLOOKUP([@MaHang],tblHang[MaHang],tblHang[TenHang],"Sai mã")

Tìm MaHang của dòng hiện tại ([@MaHang]) trong cột mã của danh mục, trả về tên hàng. Gõ mã không có trong danh mục thì hiện "Sai mã" để bạn biết mà sửa.

Cột G (DVT):

=XLOOKUP([@MaHang],tblHang[MaHang],tblHang[DVT],"")

Cột J (ThanhTien):

=[@SoLuong]*[@DonGia]

Danh sách chọn cho cột Loai và MaHang

Chọn cả cột C trong Table (bấm vào ô C2, nhấn Ctrl + Space) → tab Data → Data Validation → Allow: List → ô Source gõ:

Nhập,Xuất

Chọn cột E trong Table → Data Validation → List → Source:

=INDIRECT("tblHang[MaHang]")

Ô Source của Data Validation không nhận trực tiếp tên cột Table, nên phải bọc trong INDIRECT và đặt tên cột trong dấu ngoặc kép. Thêm mã hàng mới vào DanhMuc là danh sách tự có thêm. Cách tạo danh sách chọn chi tiết có trong bài tạo drop-down list trong Excel.

Ví dụ ghi một phiếu nhập 2 mặt hàng

SoPhieu	Ngay	Loai	DoiTac	MaHang	SoLuong	DonGia
PN0001	03/11/2026	Nhập	Đại lý Minh Phát	G001	30	185000
PN0001	03/11/2026	Nhập	Đại lý Minh Phát	N002	24	42000
PX0001	04/11/2026	Xuất	Quán cơm Bà Lý	G001	5	210000

Mẹo nhập nhanh: ở dòng thứ hai của cùng phiếu, nhấn Ctrl + ' (phím nháy đơn) để chép nội dung ô ngay phía trên — rất tiện cho SoPhieu, Ngay, Loai, DoiTac.

Bước 3: Tính tồn kho tự động bằng SUMIFS

Quay lại sheet DanhMuc, gõ công thức vào dòng đầu của các cột F → I:

Cột F (TongNhap):

=SUMIFS(tblKho[SoLuong],tblKho[MaHang],[@MaHang],tblKho[Loai],"Nhập")

Cộng cột SoLuong trong nhật ký, chỉ những dòng có mã trùng mã hàng này và loại là "Nhập".

Cột G (TongXuat):

=SUMIFS(tblKho[SoLuong],tblKho[MaHang],[@MaHang],tblKho[Loai],"Xuất")

Cột H (TonCuoi):

=[@TonDau]+[@TongNhap]-[@TongXuat]

Cột I (CanhBao):

=IF([@TonCuoi]<0,"Âm kho - kiểm tra",IF([@TonCuoi]<=[@MucToiThieu],"Cần nhập thêm",""))

Với dữ liệu ví dụ, gạo G001: 20 + 30 − 5 = 45 túi, không cảnh báo. Muốn tô màu đỏ các dòng cần nhập thêm, chọn cột CanhBao → Home → Conditional Formatting → Highlight Cells Rules → Text that Contains → gõ Cần nhập. Nếu chưa quen SUMIFS, đọc thêm bài hàm SUMIF và SUMIFS.

Lưu ý: chữ "Nhập", "Xuất" trong công thức phải gõ giống hệt chữ trong danh sách chọn ở cột Loai (cùng dấu, cùng bảng mã Unicode). Đó là lý do nên chọn từ danh sách thay vì gõ tay.

Bước 4 (nâng cao): Biết tồn ngay sau từng giao dịch để chặn xuất âm kho

Cột TonCuoi chỉ cho biết tồn hiện tại. Khi nhân viên ghi một phiếu xuất 50 túi gạo trong khi kho còn 45, bạn muốn thấy lỗi ngay tại dòng đó. Cột K (TonSauGD) trong NhatKy làm việc này:

=XLOOKUP([@MaHang],tblHang[MaHang],tblHang[TonDau],0)+SUMIFS(tblKho[[#Headers],[SoLuong]]:[@SoLuong],tblKho[[#Headers],[MaHang]]:[@MaHang],[@MaHang],tblKho[[#Headers],[Loai]]:[@Loai],"Nhập")-SUMIFS(tblKho[[#Headers],[SoLuong]]:[@SoLuong],tblKho[[#Headers],[MaHang]]:[@MaHang],[@MaHang],tblKho[[#Headers],[Loai]]:[@Loai],"Xuất")

Giải thích:

tblKho[[#Headers],[SoLuong]]:[@SoLuong] là vùng từ ô tiêu đề cột SoLuong đến dòng hiện tại. Dòng càng xuống dưới, vùng càng dài ra — giống kiểu $H$1:H2 nhưng an toàn khi thêm dòng trong Table.
Hai hàm SUMIFS cộng tổng nhập và tổng xuất của mã hàng này tính đến dòng hiện tại, rồi cộng với tồn đầu kỳ.
Công thức dài nhưng chỉ gồm 3 phần nối bằng dấu + và -: tồn đầu, cộng tổng nhập, trừ tổng xuất. Muốn dễ đọc trong thanh công thức, đặt con trỏ trước dấu + hoặc - rồi nhấn Alt + Enter để xuống dòng.

Sau đó chọn cột K → Conditional Formatting → Highlight Cells Rules → Less Than → gõ 0 → chọn màu đỏ. Dòng nào làm tồn âm sẽ đỏ ngay khi gõ xong số lượng.

Điều kiện để cột này đúng: nhật ký được ghi theo thứ tự thời gian (dòng sau xảy ra sau dòng trước). Nếu cần ghi bù phiếu cũ, hãy chèn dòng vào đúng vị trí ngày, hoặc sắp xếp lại bảng theo cột Ngay (Data → Sort) rồi mới đọc cột này.

Bước 5: Làm trang phiếu in — chọn số phiếu là tự điền

Tạo sheet Phieu và bố trí như sau:

Ô	Nội dung
A1	Tên cửa hàng, địa chỉ, số điện thoại (gõ tay)
A3	Tiêu đề phiếu (công thức)
B5	Chữ "Số phiếu:" — C5 là ô chọn số phiếu
B6 / C6	"Ngày:" / công thức
B7 / C7	"Nhà cung cấp / Khách hàng:" / công thức
A9:G9	Tiêu đề bảng: STT, Mã hàng, Tên hàng, ĐVT, Số lượng, Đơn giá, Thành tiền
A10	Danh sách hàng (công thức tràn xuống)
F26 / G26	"Tổng cộng:" / công thức
A28:G28	Người lập phiếu, Người giao, Người nhận, Thủ kho (ký tên)

Danh sách số phiếu để chọn

Ở một cột phụ ngoài vùng in, ví dụ K2, gõ:

=SORT(UNIQUE(tblKho[SoPhieu]))

Kết quả tràn xuống thành danh sách các số phiếu không trùng. Chọn ô C5 → Data Validation → List → Source: =$K$2#. Dấu # nghĩa là "toàn bộ vùng tràn bắt đầu từ K2", nên có phiếu mới là danh sách tự dài thêm. Có thể ẩn cột K sau khi làm xong.

Công thức phần đầu phiếu

Ô A3 (tiêu đề tự đổi theo loại phiếu):

=IF(XLOOKUP(C5,tblKho[SoPhieu],tblKho[Loai],"")="Nhập","PHIẾU NHẬP KHO","PHIẾU XUẤT KHO")

Ô C6 (ngày, định dạng ô kiểu dd/mm/yyyy):

=XLOOKUP(C5,tblKho[SoPhieu],tblKho[Ngay],"")

Ô C7:

=XLOOKUP(C5,tblKho[SoPhieu],tblKho[DoiTac],"")

XLOOKUP trả về dòng đầu tiên có số phiếu này — vì mọi dòng cùng phiếu có chung ngày và đối tác nên lấy dòng nào cũng đúng.

Công thức danh sách hàng

Ô B10:

=FILTER(tblKho[[MaHang]:[ThanhTien]],tblKho[SoPhieu]=C5,"")

tblKho[[MaHang]:[ThanhTien]] là cụm 6 cột liền nhau từ MaHang đến ThanhTien. FILTER giữ lại những dòng có SoPhieu bằng ô C5, kết quả tự tràn sang phải và xuống dưới. Xem thêm cách dùng ở bài hàm FILTER trong Excel.

Ô A10 (số thứ tự):

=IFERROR(SEQUENCE(ROWS(FILTER(tblKho[SoPhieu],tblKho[SoPhieu]=C5))),"")

Đếm số dòng của phiếu rồi tạo dãy 1, 2, 3... Nếu chưa chọn phiếu, FILTER báo lỗi và IFERROR trả về ô trống.

Ô G26 (tổng tiền):

=SUMIFS(tblKho[ThanhTien],tblKho[SoPhieu],C5)

Để chừa chỗ cho phiếu dài, bảng hàng từ dòng 10 đến dòng 25 (tối đa 16 mặt hàng). Phiếu nhiều hơn thì chèn thêm dòng trước dòng tổng cộng.

Thiết lập in

Chọn vùng A1:G30 → tab Page Layout → Print Area → Set Print Area.
Page Layout → Size chọn A5 (nếu máy in hỗ trợ) hoặc A4; Orientation chọn Landscape cho A5.
Nhấn Ctrl + P, ở mục Scaling chọn Fit Sheet on One Page.

Từ giờ, in phiếu nào chỉ cần chọn số phiếu ở C5 rồi Ctrl + P. Các mẹo căn lề, lặp tiêu đề có trong bài in Excel vừa khít trang giấy.

Phần hay bị bỏ sót: kiểm kê và tồn tại một ngày bất kỳ

Kiểm kê định kỳ: cuối tháng đếm kho thật, so với cột TonCuoi. Nếu lệch, không sửa thẳng vào TonDau, mà ghi một dòng điều chỉnh vào nhật ký: số phiếu KK1125, loại "Xuất" (nếu thiếu) hoặc "Nhập" (nếu thừa), ghi chú "Điều chỉnh kiểm kê". Như vậy lịch sử chênh lệch vẫn còn để truy lại.

Tồn tại một ngày bất kỳ (ví dụ cuối tháng trước): gõ ngày cần xem vào ô K1 của DanhMuc, thêm một cột tính:

=[@TonDau]+SUMIFS(tblKho[SoLuong],tblKho[MaHang],[@MaHang],tblKho[Loai],"Nhập",tblKho[Ngay],"<="&$K$1)-SUMIFS(tblKho[SoLuong],tblKho[MaHang],[@MaHang],tblKho[Loai],"Xuất",tblKho[Ngay],"<="&$K$1)

Điều kiện "<="&$K$1 ghép dấu so sánh với ngày trong K1, để SUMIFS chỉ cộng các dòng có ngày nhỏ hơn hoặc bằng ngày đó.

Mẫu phiếu theo chế độ kế toán: nếu cửa hàng là doanh nghiệp có kế toán, phiếu nhập/xuất kho thường cần theo mẫu kế toán đang áp dụng (mẫu 01-VT, 02-VT) với đủ chữ ký. Bố cục ở Bước 5 sửa lại tiêu đề và phần chữ ký là dùng được; hãy hỏi kế toán của bạn mẫu nào đang áp dụng.

Dùng Excel 2016, 2019 thì sao?

Các bản này không có XLOOKUP, FILTER, UNIQUE, SEQUENCE. Phần DanhMuc và NhatKy vẫn làm được bằng cách thay XLOOKUP bằng INDEX/MATCH, ví dụ cột TenHang:

=IFERROR(INDEX(tblHang[TenHang],MATCH([@MaHang],tblHang[MaHang],0)),"Sai mã")

SUMIFS và cột TonSauGD giữ nguyên. Riêng trang phiếu tự tràn danh sách thì không làm gọn được bằng công thức; cách đơn giản là bật bộ lọc ở NhatKy (Data → Filter), lọc theo SoPhieu rồi in vùng đã lọc. Xem thêm bài XLOOKUP thay thế VLOOKUP để biết bản nào có hàm mới.

Lỗi thường gặp

Cột TenHang hiện "Sai mã" dù mã đúng Nguyên nhân: mã có khoảng trắng thừa ("G001 ") hoặc gõ số 0 thành chữ O. Cách sửa: chọn mã từ danh sách thay vì gõ tay; làm sạch danh mục bằng =TRIM() nếu nhập từ file khác.

Tồn kho không đổi sau khi ghi phiếu Nguyên nhân: cột Loai gõ tay "nhập" khác dấu, hoặc dòng mới ghi ngoài Table (Table không tự mở rộng vì có dòng trống ở giữa). Cách sửa: chọn Loai từ danh sách; ghi tiếp ngay dưới dòng cuối của Table, kiểm tra viền Table đã bao dòng mới.

Ô B10 của phiếu báo #SPILL! Nguyên nhân: vùng bên dưới hoặc bên phải B10 có dữ liệu hay ô gộp (Merge) chắn đường tràn. Cách sửa: xóa nội dung, bỏ gộp ô trong vùng B10:G25.

Ngày trên phiếu hiện số như 46329 Nguyên nhân: ô C6 đang định dạng General. Cách sửa: nhấn Ctrl + 1 → Number → Date hoặc Custom gõ dd/mm/yyyy.

Cột TonSauGD đúng hôm trước, hôm sau sai Nguyên nhân: có người sắp xếp bảng theo mã hàng hoặc ghi bù phiếu cũ ở cuối bảng. Cách sửa: sắp xếp lại NhatKy theo cột Ngay tăng dần (nếu cùng ngày thì theo SoPhieu).

Bảng tóm tắt

Phần	Vị trí	Công thức / thao tác
Danh mục hàng	DanhMuc, Table `tblHang`	Ctrl + T, đặt tên ở Table Design
Nhật ký nhập xuất	NhatKy, Table `tblKho`	Mỗi dòng = 1 mặt hàng của 1 phiếu
Tên hàng	tblKho[TenHang]	`=XLOOKUP([@MaHang],tblHang[MaHang],tblHang[TenHang],"Sai mã")`
Chọn mã hàng	tblKho[MaHang]	Data Validation → List → `=INDIRECT("tblHang[MaHang]")`
Tổng nhập	tblHang[TongNhap]	`=SUMIFS(tblKho[SoLuong],tblKho[MaHang],[@MaHang],tblKho[Loai],"Nhập")`
Tồn cuối	tblHang[TonCuoi]	`=[@TonDau]+[@TongNhap]-[@TongXuat]`
Chặn âm kho	tblKho[TonSauGD]	SUMIFS vùng mở rộng + tô đỏ khi < 0
Chọn số phiếu	Phieu!C5	List nguồn `=$K$2#`, K2: `=SORT(UNIQUE(tblKho[SoPhieu]))`
Dòng hàng trên phiếu	Phieu!B10	`=FILTER(tblKho[[MaHang]:[ThanhTien]],tblKho[SoPhieu]=C5,"")`
In phiếu	Phieu!A1:G30	Set Print Area, A5 ngang, Fit Sheet on One Page

Khi nào nên nhờ viết công cụ riêng?

File trên đủ cho một cửa hàng, một kho, vài chục phiếu mỗi ngày. Khi bạn cần form nhập phiếu có nút "Lưu" tự chuyển vào nhật ký, đánh số phiếu tự động không trùng, in hàng loạt phiếu trong ngày, tính giá vốn bình quân, quản lý nhiều kho hoặc nhiều người nhập cùng lúc, Excel công thức bắt đầu chậm và dễ bị sửa nhầm. Lúc đó nên cân nhắc một công cụ VBA (Excel) hoặc Apps Script (Google Sheets) viết theo đúng quy trình cửa hàng, hoặc phần mềm kho có sẵn — xem phân tích ở bài phần mềm quản lý riêng hay phần mềm có sẵn. Điền Nguyễn AZ có nhận viết công cụ VBA, Apps Script theo yêu cầu.

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

Quản lý kho bằng Excel hay Google Sheets tốt hơn cho cửa hàng nhỏ?