Người làm kho dùng Excel mỗi tuần để kiểm kê, theo dõi hạn dùng của từng lô và báo cáo hàng tồn cho giám sát kho. Bộ bài tập Excel quản lý kho có lời giải này gồm 5 bài bám sát các việc đó, phù hợp để bạn luyện trước khi đi phỏng vấn vị trí thủ kho, nhân viên kho hoặc giám sát kho.

Bộ đề có 5 bài với tổng 100 điểm, thời gian làm bài gợi ý là 120 phút. Các bài lần lượt là kiểm kê và xử lý chênh lệch, phân loại lô theo hạn dùng và xếp thứ tự xuất FEFO, kiểm tra vị trí cất hàng lên kệ kèm đề xuất vị trí thay thế, tính năng suất soạn hàng theo ca, và lọc hàng chậm luân chuyển kèm tuổi tồn kho. Mọi công ty, mã hàng, nhân viên và số liệu trong đề đều là giả định.

Tải bộ đề: Bộ đề Excel có lời giải - Kho vận (OneDrive)

Bạn làm bài mà bị vướng ở câu nào? Bạn có thể đăng câu hỏi trong nhóm Vườn ươm nhà Nghề để cùng trao đổi và được giải đáp. Vào nhóm hỏi đáp

Bộ bài tập Excel quản lý kho gồm những bài nào?

Bài

Điểm

Việc cần làm

Kiến thức chính

1

15

Bạn chốt số kiểm kê, tính chênh lệch và tiền bồi thường.

IF lồng, AND, ABS, VLOOKUP, ROUNDDOWN, COUNTIF, SUMIF

2

20

Bạn phân loại lô theo hạn dùng còn lại và xếp thứ tự xuất FEFO.

ROUND, VLOOKUP gần đúng, COUNTIFS, INDEX/MATCH 2 điều kiện, SUMIFS

3

20

Bạn kiểm tra vị trí kệ và tự tìm vị trí thay thế cho dòng không đạt.

ROUNDUP, RIGHT, VALUE, INDEX/MATCH nhiều điều kiện, COUNTIFS, SUMIF với dấu *

4

20

Bạn tính năng suất soạn hàng theo nhân viên và theo ca.

MOD, VLOOKUP gần đúng theo giờ, SUMIF, AVERAGEIF, RANK

5

25

Bạn lọc hàng chậm luân chuyển và lập bảng tuổi tồn.

MAXIFS, SUMIFS theo khoảng ngày, FILTER, HSTACK, SORT

Bài 1 đến Bài 4 làm được trên Excel 2010 trở lên. Bài 5 cần Excel 2019 trở lên vì có hàm MAXIFS, riêng yêu cầu 7 của Bài 5 cần Excel 365 hoặc Excel 2024 vì có hàm HSTACK. Hai hàm FILTER và SORT trong yêu cầu này đã có từ Excel 2021, nhưng HSTACK chỉ có từ Excel 2024 và Excel 365.

Bạn dùng file bộ đề như thế nào?

File có 3 sheet. Sheet Giới thiệu khóa học giới thiệu khóa học của Nghề Excel, sheet Đề bài là nơi bạn làm bài, còn sheet Kết quả chứa lời giải bằng công thức. Bài 5 dùng số kiểm kê của Bài 1 và dữ liệu lô của Bài 2, nên bạn cần làm 2 bài này trước.

Đầu sheet Đề bài gồm tiêu đề, bối cảnh chung, ngày báo cáo và danh mục 10 mã hàng
Danh mục hàng hóa có giá vốn, số thùng mỗi pallet và cột hàng nặng, dùng chung cho cả 5 bài

Bạn điền công thức vào các cột có tiêu đề màu vàng, không gõ số tay. Bạn nên làm xong rồi mới mở sheet Kết quả để đối chiếu.

Bảng kiểm kê trên sheet Đề bài, các cột tiêu đề vàng đang để trống
Các cột tiêu đề vàng đánh dấu phần bạn cần điền

Các phần dưới đây tóm tắt yêu cầu và cách giải chính. Yêu cầu đầy đủ của từng bài nằm ngay trong file.

Bài 1: Bạn xử lý chênh lệch kiểm kê như thế nào?

Mã nào lệch quá ngưỡng ở lần đếm 1 phải được đếm lại, và số đếm lần 2 là số chốt. Cột SL thực tế tại ô H49 lấy lần 2 nếu có, còn lại lấy lần 1:

=IF(E49="",D49,E49)

Mỗi nhóm hàng có một định mức hao hụt cho phép, và số thùng cho phép được làm tròn xuống bằng =ROUNDDOWN(C49*K49,0) tại ô L49. Cột Kết quả tại ô M49 xét lần lượt 4 trường hợp:

=IF(I49=0,"Khớp",IF(I49>0,"Thừa",IF(-I49<=L49,"Thiếu trong định mức","Thiếu vượt định mức")))

Ví dụ, mã MH03 có sổ sách 763 thùng. Lần đếm 1 chỉ ra 741 thùng nên kho đếm lại, lần 2 ra 760 thùng. Định mức 0,5% cho phép thiếu 3,815 thùng, làm tròn xuống còn 3 thùng. Mã này thiếu đúng 3 thùng, nên kết quả là "Thiếu trong định mức" và không phải bồi thường.

Cột Kiểm tra đếm lại tại ô O49 báo những mã lệch nhiều mà chưa có lần đếm 2:

=IF(AND(E49="",ABS(D49-C49)>C49*$E$45),"Cần đếm lại","OK")

Bảng kiểm kê 10 mã hàng với SL thực tế, chênh lệch, kết quả và tiền bồi thường
Mã MH01 thiếu 4 thùng trên 330 thùng sổ sách, tức lệch 1,2% so với ngưỡng 1%, nên ô Kiểm tra đếm lại báo "Cần đếm lại"

Mã MH07 cũng lệch 1 thùng trên 100 thùng, tức đúng bằng 1%. Ngưỡng chỉ tính khi lệch lớn hơn 1%, nên mã này không cần đếm lại.

Bảng tổng hợp kiểm kê theo 4 kết quả, độ chính xác tồn kho 40%
Cả kho lệch 1.510.000 đồng theo giá vốn, tiền bồi thường là 1.960.000 đồng và 4 trên 10 mã khớp sổ

Bài 2: Bạn xếp thứ tự xuất lô theo FEFO như thế nào?

FEFO (First Expired, First Out) nghĩa là lô hết hạn sớm hơn được xuất trước, kể cả khi lô đó nhập kho sau. Bài này dùng % hạn dùng còn lại để chia kênh bán: siêu thị nhận lô còn từ 70%, đại lý nhận lô còn từ 40%. Công thức tại ô K92 tính % hạn dùng còn lại:

=IF(J92<=0,0,ROUND(J92/I92,2))

Sau đó, cột Phân loại tại ô L92 dùng VLOOKUP tìm gần đúng trên bảng mốc 0%, 40% và 70%. Lô đã tới ngày hết hạn được tách riêng thành "Hết hạn - chờ hủy". Lô LO-07 còn 146 trên 365 ngày, tức đúng 40%, nên lô này vẫn được bán cho đại lý.

Điểm khó nhất của bài là cột Thứ tự xuất FEFO tại ô M92. Công thức đếm số lô cùng mã còn hạn có HSD sớm hơn, rồi cộng số lô cùng HSD tính từ dòng đầu bảng đến dòng hiện tại:

=IF(J92<=0,"-",COUNTIFS($C$92:$C$111,C92,$F$92:$F$111,"<"&F92,$J$92:$J$111,">0")+COUNTIFS($C$92:C92,C92,$F$92:F92,F92))

Phần COUNTIFS thứ hai dùng vùng mở rộng dần, nên 2 lô cùng HSD không bị trùng thứ tự. Ví dụ, mã MH05 có lô LO-15 và LO-16 cùng hết hạn ngày 16/02/2027. Lô LO-15 nằm trên nên được thứ tự 1, lô LO-16 được thứ tự 2, còn lô LO-20 hết hạn sau được thứ tự 3.

Bảng 20 lô hàng với % hạn dùng còn lại, phân loại, thứ tự xuất FEFO và giá trị tồn
Lô LO-01 hết hạn đúng ngày báo cáo 31/10/2026 nên đã thuộc nhóm chờ hủy và không có thứ tự xuất

Bảng lô xuất tiếp theo tìm lô có thứ tự 1 của từng mã bằng INDEX/MATCH 2 điều kiện. Công thức tại ô D116 như sau:

=IFERROR(INDEX($B$92:$B$111,MATCH(1,INDEX(($C$92:$C$111=B116)*($M$92:$M$111=1),0),0)),"Không còn lô bán được")

Bảng lô xuất tiếp theo của 10 mã hàng, mã MH10 không còn lô bán được
Mã MH10 có cả 2 lô đã hết hạn, nên kho còn 21 thùng chờ hủy và không còn lô nào để xuất bán

Bạn cũng cần chú ý mã MH02. Lô xuất trước của mã này là LO-12, chỉ còn 7 ngày hạn dùng, nên khi đi làm thật, bạn nên báo bộ phận bán hàng để xả lô này trước.

Bảng tổng hợp 4 nhóm phân loại hạn dùng với số lô, số thùng, giá trị tồn và tỷ trọng
Hàng cận date và hết hạn chiếm 8,5% giá trị tồn, tương ứng 47.540.000 đồng

Bài 3: Bạn kiểm tra vị trí kệ và tìm vị trí thay thế như thế nào?

Kho nhận một xe hàng, và nhân viên nhận hàng đã ghi vị trí kệ đề xuất cho từng dòng. Kho có 3 quy tắc: mỗi vị trí chỉ chứa một mã hàng, mỗi dòng phiếu cất trọn vào một vị trí, và hàng nặng chỉ được xếp từ tầng 2 trở xuống. Bạn quy đổi số thùng ra pallet tại ô E171, vì pallet chưa đầy vẫn chiếm 1 chỗ:

=ROUNDUP(C171/VLOOKUP(B171,$B$13:$H$22,6,0),0)

Ví dụ, 250 thùng mì tôm chia cho 100 thùng mỗi pallet ra 2,5, nên công thức làm tròn lên thành 3 pallet. Tầng của vị trí lấy từ ký tự cuối của mã vị trí bằng =VALUE(RIGHT(D171,1)) tại ô G171.

Sơ đồ vị trí kệ có 3 cột vàng bạn cần điền trước: Tầng, Còn trống và Số dòng cất hợp lệ. Cột Số dòng cất hợp lệ tại ô H150 đếm các dòng phiếu nhập đã được duyệt cất vào vị trí đó:

=COUNTIFS($D$171:$D$180,B150,$H$171:$H$180,"Hợp lệ")

Sơ đồ 15 vị trí kệ dãy A đến C với sức chứa, mã hàng đang chứa, tầng, chỗ còn trống và số dòng cất hợp lệ
Vị trí A-01-2 đang trống nhưng đã có 1 dòng hợp lệ cất vào, nên vị trí này không còn được dùng làm vị trí thay thế

Cột Kết quả kiểm tra tại ô H171 xét lần lượt mã vị trí, tầng của hàng nặng, chuyện lẫn mã hàng, rồi mới tới chỗ trống:

=IF(COUNTIF($B$150:$B$164,D171)=0,"Sai mã vị trí",IF(AND(F171="Có",G171>$E$167),"Sai tầng",IF(AND(VLOOKUP(D171,$B$150:$H$164,4,0)<>"Trống",VLOOKUP(D171,$B$150:$H$164,4,0)<>B171),"Lẫn mã hàng",IF(E171>VLOOKUP(D171,$B$150:$H$164,6,0),"Không đủ chỗ","Hợp lệ"))))

Ví dụ, dòng 9 cất nước mắm MH08 vào vị trí C-01-2, nhưng vị trí này đang chứa sữa chua uống MH02, nên kết quả là "Lẫn mã hàng". Dòng 6 cất bánh quy bơ MH06 vào A-01-3 đúng mã hàng đang chứa, nhưng vị trí này đã đầy 3 trên 3 pallet, nên kết quả là "Không đủ chỗ".

Điểm khó nhất của bài là cột Vị trí cất tại ô I171. Với dòng không hợp lệ, công thức tìm vị trí đầu tiên trong sơ đồ đang trống, chưa có dòng hợp lệ nào cất vào, đủ chỗ và đúng tầng:

=IF(H171="Hợp lệ",D171,IFERROR(INDEX($B$150:$B$164,MATCH(1,INDEX(($E$150:$E$164="Trống")*($H$150:$H$164=0)*($G$150:$G$164>=E171)*((($F$150:$F$164<=$E$167)+(F171="Không"))>0),0),0)),"Chờ giám sát xếp"))

Phép nhân giữa các điều kiện đóng vai trò hàm AND, còn phép cộng trong điều kiện tầng đóng vai trò hàm OR. Điều kiện tầng đúng khi vị trí nằm từ tầng 2 trở xuống hoặc khi hàng không nặng. Phần >0 giữ cho kết quả của điều kiện tầng chỉ là 0 hoặc 1, nên MATCH(1, ...) vẫn tìm đúng.

Ví dụ, dòng 3 là dầu ăn MH07, thuộc hàng nặng và cần 2 pallet. Vị trí A-02-3 và B-01-3 đang trống nhưng nằm ở tầng 3, nên công thức bỏ qua 2 vị trí này và chọn B-02-2 ở tầng 2. Dòng 10 cần 7 pallet nước suối, trong khi vị trí trống lớn nhất chỉ chứa 6 pallet, nên kết quả là "Chờ giám sát xếp".

Bảng kiểm tra 10 dòng phiếu nhập với số pallet, hàng nặng, tầng, kết quả kiểm tra và vị trí cất
Dòng 9 cần 3 pallet hàng nặng nên bỏ qua B-02-2 chỉ có 2 chỗ và được chuyển sang C-01-1

Bảng tổng hợp theo dãy dùng SUMIF với ký tự đại diện để cộng số pallet cất vào từng dãy. Công thức tại ô E185 như sau:

=SUMIF($I$171:$I$180,B185&"-*",$E$171:$E$180)

Bạn nên ghép thêm dấu gạch ngang vào điều kiện. Nếu chỉ viết B185&"*", điều kiện "C*" của dãy C cũng khớp với chữ "Chờ giám sát xếp", và 7 pallet đang chờ sẽ bị cộng nhầm vào dãy C. Khi đó, ô Kiểm tra tại E190 sẽ báo "Lệch".

Bảng tổng hợp 3 dãy kệ sau khi cất hàng và ô kiểm tra tổng số pallet
Kho cất 21 pallet vào 3 dãy và còn 7 pallet chờ giám sát xếp, khớp với 28 pallet của phiếu nhập

Bài 4: Bạn tính năng suất soạn hàng theo ca như thế nào?

Phiếu ca đêm có thể kết thúc sau 0 giờ, khi đó giờ kết thúc nhỏ hơn giờ bắt đầu. Hàm MOD giúp phép trừ không ra số âm, và công thức tại ô K218 như sau:

=ROUND(MOD(F218-E218,1)*1440,0)

Ví dụ, phiếu PS-15 bắt đầu lúc 21:00 và kết thúc lúc 00:00. Thời gian soạn là 180 phút, nên năng suất là 148 dòng chia 180 phút nhân 60, bằng 49,3 dòng/giờ.

Ca của phiếu lấy theo giờ bắt đầu bằng VLOOKUP tìm gần đúng tại ô J218, với =VLOOKUP(E218,$F$207:$G$210,2,1). Bảng ca có 2 dòng "Ca đêm" ở mốc 00:00 và 22:00, nên phiếu bắt đầu lúc 05:30 vẫn thuộc ca đêm.

Nhật ký 16 phiếu soạn hàng với ca, thời gian và năng suất, các phiếu dưới chỉ tiêu được tô cam
Phiếu PS-06 bắt đầu lúc 13:50 nên thuộc ca sáng, dù phần lớn thời gian soạn rơi vào ca chiều

Bảng xếp loại tính năng suất trên tổng dòng và tổng phút của mỗi nhân viên, rồi xét độ chính xác. Công thức tại ô J237 như sau:

=IF(G237<$E$213,"Chưa đạt năng suất",IF(I237<=$E$214,"Đạt","Cần cải thiện độ chính xác"))

Bảng xếp loại 4 nhân viên soạn hàng và bảng năng suất theo 3 ca
Nhân viên NV04 đạt đúng 50,0 dòng/giờ và sai đúng 0,50%, nên vẫn được xếp loại "Đạt"

Nhân viên NV02 có năng suất 56,9 dòng/giờ, cao thứ 2 trong nhóm. Tuy vậy, tỷ lệ soạn sai của NV02 là 1,60%, nên kết quả là "Cần cải thiện độ chính xác".

Bài 5: Bạn lọc hàng chậm luân chuyển và tính tuổi tồn như thế nào?

Bài cuối cần Excel 2019 trở lên. Ngày xuất gần nhất tại ô F308 dùng MAXIFS, và mã chưa từng xuất thì để trống:

=IF(COUNTIFS($E$265:$E$294,B308,$D$265:$D$294,"Xuất")=0,"",MAXIFS($B$265:$B$294,$E$265:$E$294,B308,$D$265:$D$294,"Xuất"))

SL xuất trong kỳ tại ô H308 chỉ cộng các phiếu xuất nằm trong kỳ xét từ 01/08/2026 đến 31/10/2026:

=SUMIFS($F$265:$F$294,$E$265:$E$294,B308,$D$265:$D$294,"Xuất",$B$265:$B$294,">="&$E$297,$B$265:$B$294,"<="&$E$298)

Số ngày đủ bán cho biết tồn hiện tại đủ xuất bao lâu nếu kho xuất đều như kỳ xét. Mã có số ngày đủ bán trên 90 ngày bị xếp vào "Chậm luân chuyển".

Bảng luân chuyển 10 mã hàng với ngày xuất gần nhất, SL xuất trong kỳ, số ngày đủ bán và phân loại
Mã MH07 chỉ xuất ngày 31/07/2026, trước kỳ xét 1 ngày, nên SL xuất trong kỳ bằng 0 và mã này "Không luân chuyển"

Mã MH08 có 134 thùng tồn và xuất 137 thùng trong 92 ngày. Số ngày đủ bán là 89,985, làm tròn thành 90, nên mã này vẫn "Bình thường" vì chưa vượt ngưỡng.

Bảng tuổi tồn cộng số thùng của các lô theo ngày nhập kho. Công thức tại ô C322 chép được sang phải và xuống dưới nhờ cố định đúng dòng, đúng cột:

=SUMIFS($G$92:$G$111,$C$92:$C$111,$B322,$D$92:$D$111,">="&($E$9-C$304),$D$92:$D$111,"<="&($E$9-C$303))

Yêu cầu cuối cần Excel 365 hoặc Excel 2024 vì có hàm HSTACK. Bạn chỉ viết một công thức tại ô B336, và kết quả tự tràn ra danh sách hàng cần xử lý:

=SORT(FILTER(HSTACK($B$308:$B$317,$C$308:$C$317,$J$308:$J$317,$E$308:$E$317),$J$308:$J$317<>"Bình thường"),4,-1)

Bảng tuổi tồn theo 4 nhóm và danh sách 4 mã hàng cần xử lý xếp theo giá trị tồn
Cột Kiểm tra báo "Khớp" cả 10 mã, nghĩa là tổng các lô ở Bài 2 bằng đúng số kiểm kê ở Bài 1

Mã MH03 đứng đầu danh sách với 68.400.000 đồng tồn kho. Bảng tuổi tồn cũng cho thấy 310 thùng của mã này đã nằm trong kho hơn 180 ngày, nên đây là mã nên ưu tiên xả trước.

Đề đã đơn giản hóa những gì so với thực tế?

Bộ đề giữ đúng bản chất nghiệp vụ, nhưng đơn giản hóa một số điểm để bài gọn hơn:

  • Định mức hao hụt: Đề tính hao hụt cho phép theo phần trăm số tồn sổ sách, trong khi nhiều công ty tính theo lượng hàng nhập xuất trong kỳ.

  • Đơn vị tính: Đề tính mọi số lượng theo thùng, trong khi kho thật thường kiểm kê cả hàng lẻ.

  • Hạn dùng còn lại: Đề dùng 2 mốc 70% và 40% cho mọi khách hàng, còn mỗi chuỗi siêu thị thường có mốc riêng.

  • Sức chứa kệ: Đề tính sức chứa theo số pallet, chưa xét tải trọng của từng tầng kệ. Đề cũng tìm vị trí thay thế cho từng dòng riêng lẻ, còn khi làm thật bạn cần duyệt lần lượt để 2 dòng không bị xếp chung một vị trí.

  • Năng suất soạn hàng: Đề đo năng suất theo số dòng hàng, chưa xét số thùng hay quãng đường đi lại trong kho.

Khi đi làm, bạn cần kiểm tra các điểm này theo quy trình của công ty mình.

Bạn nên luyện bộ đề này theo thứ tự nào?

Bạn nên làm lần lượt từ Bài 1 đến Bài 5, vì độ khó tăng dần và Bài 5 dùng kết quả của Bài 1, Bài 2. Nếu mới bắt đầu, bạn nên chắc Bài 1 trước, rồi mới sang các bài dùng COUNTIFS với vùng mở rộng dần và INDEX/MATCH nhiều điều kiện. Bạn có thể đọc lại bài hàm đếm số lượng trong Excel nếu vướng ở cột thứ tự FEFO.

Nếu cần một file dùng ngay cho kho của mình, bạn có thể xem file Excel quản lý xuất nhập tồn kho. Nghề Excel cũng chia sẻ bộ bài tập Excel Purchasing có lời giải với các bài về điểm đặt hàng và theo dõi giao hàng.

Tải bộ đề tại đây: Bộ đề Excel có lời giải - Kho vận (OneDrive)

Bạn muốn dùng Excel thành thạo cho công việc kho?

Bộ đề này giúp bạn tự kiểm tra kỹ năng. Điều khó hơn khi đi làm là tự đọc yêu cầu và biết nên kết hợp các hàm ra sao. Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.