Người làm mua hàng dùng Excel mỗi ngày để so báo giá, theo dõi đơn và quyết định khi nào phải đặt thêm hàng. Bộ bài tập Excel Purchasing có lời giải này gồm 6 bài bám sát các việc đó, với độ khó tăng dần từ bài lập đơn hàng tới bài lập kế hoạch vật tư nhiều tuần.
Mọi công ty, nhà cung cấp và số liệu trong đề đều là giả định. Tổng điểm là 100 và thời gian làm bài gợi ý là 180 phút.
Tải bộ đề: Bộ đề Excel có lời giải - Nghiệp vụ Purchasing (OneDrive)
Bộ đề Excel Purchasing 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 | 10 | Bạn lập đơn đặt hàng. | VLOOKUP, ROUND, IF, COUNTIF, MAX, AVERAGE |
2 | 15 | Bạn theo dõi giao hàng và tính tiền phạt. | IF lồng, MAX, MIN, COUNTIF, SUMIF |
3 | 15 | Bạn so sánh báo giá có ngoại tệ và chiết khấu. | COUNTIFS, MINIFS, MAXIFS, INDEX/MATCH |
4 | 20 | Bạn lập kế hoạch đặt hàng theo ROP và EOQ. | SQRT, CEILING, ROUNDUP, WORKDAY |
5 | 20 | Bạn đối chiếu 3 chiều và phân tích ABC. | SUMIFS, IFERROR, SUMPRODUCT, LARGE |
6 | 20 | Bạn xử lý giá lũy tiến, phân bổ nhà cung cấp, MRP và báo cáo bằng một công thức. | SUMPRODUCT, CEILING, LET, MAP, FILTER, HSTACK |
Bài 3 cần Excel 2019 trở lên vì có MINIFS và MAXIFS. Bài 6 cần Excel 365 vì có các hàm mảng động.
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ạn điền công thức vào các cột hoặc dòng 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.

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 lập đơn đặt hàng như thế nào?
Bạn có danh sách mã vật tư và số lượng, rồi cần tra tên, đơn giá, % VAT và tính tiền. Công thức tra tên hàng tại ô C30 như sau:
=VLOOKUP(B30,$B$10:$F$17,2,FALSE)
Tiền VAT dùng hàm ROUND để làm tròn đến đồng. Cột Phê duyệt dùng hàm IF so sánh Thành tiền với ngưỡng ở ô K13, và công thức tại ô K30 như sau:
=IF(G30>=$K$13,"Cần duyệt","Không cần duyệt")

Bài 2: Bạn theo dõi giao hàng và tiền phạt như thế nào?
Điểm khó của bài này là dòng đã quá hạn mà chưa nhận đủ hàng thì vẫn đang trễ, nên số ngày trễ phải tính tới Ngày báo cáo. Công thức tính Số ngày trễ tại ô L57 như sau:
=MAX(0,IF(K57>0,$K$10,G57)-F57)
Tiền phạt có mức trần, nên hàm MIN giữ tỷ lệ phạt không vượt mức tối đa ở ô K12. Công thức tại ô N57 như sau:
=ROUND(MIN(L57*$K$11,$K$12)*J57*IF(K57>0,K57,H57),0)
Ví dụ, đơn P203 trễ 18 ngày nên tỷ lệ tính ra là 9%, nhưng chỉ được tính ở mức trần 5%. Đơn này còn thiếu 10 tấm với đơn giá 210.000 đồng, nên tiền phạt là 105.000 đồng.

Sau đó, bạn dùng COUNTIF và SUMIF tổng hợp theo Tình trạng để biết mỗi tình trạng có bao nhiêu dòng và bao nhiêu tiền phạt.

Bài 3: Bạn so sánh báo giá có ngoại tệ như thế nào?
Đơn giá thấp chưa chắc là rẻ nhất. Bạn phải quy đổi ngoại tệ, xét chiết khấu theo số lượng, cộng phí vận chuyển rồi mới so sánh. Công thức tính Tổng chi phí tại ô M97 như sau:
=ROUND(J97*K97*(1-L97),0)+H97
Hạng trong mã chỉ xếp các báo giá đạt điều kiện về thời gian giao, và công thức tại ô O97 như sau:
=IF(N97="Đạt",1+COUNTIFS($C$97:$C$105,C97,$N$97:$N$105,"Đạt",$M$97:$M$105,"<"&M97),"-")

Bảng tổng hợp dùng MINIFS để tìm chi phí thấp nhất trong các báo giá đạt, rồi dùng INDEX/MATCH nhiều điều kiện để lấy tên nhà cung cấp. Công thức tại ô E109 như sau:
=MINIFS($M$97:$M$105,$C$97:$C$105,B109,$N$97:$N$105,"Đạt")

Bài 4: Bạn lập kế hoạch đặt hàng theo ROP và EOQ như thế nào?
Bài này trả lời 3 câu hỏi: mã nào cần đặt, đặt bao nhiêu và ngày nào là muộn nhất phải phát đơn.
Chỉ số | Chỉ số này là gì | Công thức của mã đầu tiên |
|---|---|---|
ROP (ô | Đây là mức tồn mà tại đó phải đặt hàng. |
|
EOQ (ô | Đây là lượng đặt giúp tổng chi phí đặt hàng và lưu kho thấp nhất. |
|
SL đề xuất (ô | Đây là số lớn hơn giữa EOQ và MOQ, làm tròn lên theo bội số đóng gói. |
|
Ngày hết hàng (ô | Đây là ngày tồn kho hiện có dùng hết, chỉ tính ngày làm việc. |
|
Ngày đặt muộn nhất tại ô N132 lấy Ngày hết hàng lùi lại đúng Lead time bằng =WORKDAY(M132,-C132,$E$126:$E$128). Hàm ROUND nằm bên trong công thức ROP và Ngày hết hàng giúp loại sai số rất nhỏ của phép chia trước khi làm tròn.

Ví dụ, mã VT01 dùng 24 tấm mỗi ngày làm việc. Tồn kho 120 tấm chỉ đủ 5 ngày, nên hàng hết vào 06/11/2026. Lead time là 7 ngày, nên ngày đặt muộn nhất là 28/10/2026. Ngày này đứng trước Ngày báo cáo 30/10/2026, nên trạng thái là "Trễ - đặt gấp".
Bạn cũng cần chú ý mã VT06 có trạng thái "Chưa cần đặt" dù ngày đặt muộn nhất đã qua. Tồn khả dụng của mã này vẫn trên ROP nhờ 900 cái đang về, nhưng đề không có ngày về của lô hàng đó, nên khi đi làm thật, bạn nên hỏi lại ngày về.

Bài 5: Bạn đối chiếu 3 chiều và phân tích ABC như thế nào?
Đối chiếu 3 chiều nghĩa là so hóa đơn với đơn hàng (PO) và phiếu nhập kho (GRN) trước khi thanh toán. Một dòng PO có thể nhận hàng nhiều đợt, nên bạn dùng SUMIFS với 2 điều kiện Số PO và Mã VT.
Đơn giá PO cần tra theo 2 điều kiện mà không dùng cột phụ. Công thức tại ô H181 như sau:
=IFERROR(INDEX($E$167:$E$175,MATCH(1,INDEX(($A$167:$A$175=B181)*($B$167:$B$175=C181),0),0)),0)
Kết quả đối chiếu xét lệch số lượng và lệch giá. Số lượng hóa đơn không được vượt số nhỏ hơn giữa SL PO và SL đã nhận. Công thức tại ô K181 như sau:
=IF(H181=0,"Không có PO",IF(AND(D181>MIN(F181,G181),ABS(I181)>$K$14),"Lệch cả hai",IF(D181>MIN(F181,G181),"Lệch SL",IF(ABS(I181)>$K$14,"Lệch giá","Khớp"))))

Phần phân tích ABC xếp các mã theo chi tiêu giảm dần chỉ bằng công thức. Hàm LARGE lấy chi tiêu theo hạng, INDEX/MATCH lấy lại mã vật tư, rồi bạn tính lũy kế để chia nhóm. Nhóm của mỗi mã xét theo lũy kế của hạng ngay trước, nên mã làm lũy kế vượt mốc 70% vẫn thuộc nhóm A.

Bài 6: Bạn xử lý các bài toán mua hàng phức tạp như thế nào?
Bài cuối có 3 phần lớn và 2 báo cáo mảng động. Mỗi ô chỉ dùng một công thức.
Giá lũy tiến: Giá của bậc nào chỉ áp dụng cho phần số lượng nằm trong bậc đó. Bạn tính tổng tiền bằng một công thức SUMPRODUCT cộng dồn phần chênh lệch giá của từng bậc. Công thức tại ô H217 như sau, trong đó vùng $B$216:$B$224 và $D$216:$D$224 lùi lên một dòng để lấy giá của bậc trước:
=SUMPRODUCT(($B$217:$B$225=H$216)*($G217>$C$217:$C$225)*($G217-$C$217:$C$225)*($D$217:$D$225-IF($B$216:$B$224=H$216,$D$216:$D$224,0)))
Với đơn 2.500 cái, NCC01 có 1.000 cái đầu giá 50.000 đồng và 1.500 cái sau giá 46.000 đồng, tổng là 119.000.000 đồng.

Phân bổ theo công suất: NCC giá thấp nhận trước tới hết công suất hiệu lực, phần còn lại chuyển cho NCC kế tiếp. Công thức tại ô I229 không cần sắp xếp bảng:
=MAX(0,MIN(H229,$C$228-SUMIFS($H$229:$H$232,$F$229:$F$232,"<"&F229)))

MRP 8 tuần: Bạn tính tồn đầu, lượng cần nhận và tồn cuối theo từng tuần. Lệnh đặt phải phát sớm hơn lượng nhận đúng bằng Lead time. Tuần 2 có cảnh báo "Không kịp", vì Lead time là 2 tuần nên lô này phải đặt từ trước tuần 1.

Báo cáo bằng một công thức: Bạn chỉ viết một công thức tại ô đầu và kết quả tự tràn ra các ô còn lại. Công thức tại ô B253 lấy 3 NCC có chi tiêu lớn nhất như sau:
=LET(n,UNIQUE($C$167:$C$175),t,MAP(n,LAMBDA(x,SUMPRODUCT(($C$167:$C$175=x)*$D$167:$D$175*$E$167:$E$175))),TAKE(SORTBY(HSTACK(n,t),t,-1),3))

Đề đã đơ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:
Phê duyệt theo dòng: Đề xét ngưỡng duyệt theo từng dòng, trong khi nhiều công ty xét theo cả đơn hàng.
Tiền phạt: Đề chỉ phạt phần còn thiếu với dòng giao thiếu, và không phạt phần đã giao trễ của lô đó.
Giá so sánh: Bài 3 so sánh trên giá chưa VAT và coi giá USD đã gồm thuế nhập khẩu.
Tồn khả dụng: Đề tính bằng Tồn kho cộng Hàng đang về, chưa trừ hàng đã cam kết cho đơn khác.
Thanh toán: Bài 5 đối chiếu trên tiền hóa đơn chưa VAT.
Khi đi làm, bạn cần kiểm tra các điểm này theo hợp đồng và 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 6, vì độ khó tăng dần. Nếu mới bắt đầu, bạn nên chắc Bài 1 và Bài 2 trước, rồi mới sang các bài dùng nhiều điều kiện. Bạn có thể đọc lại bài hàm VLOOKUP trong Excel và hàm IF nhiều điều kiện trong Excel nếu vướng ở các bài đầu.
Nghề Excel cũng chia sẻ bộ 4 bài tập Excel Sales Admin có lời giải và bài tập Excel kế toán nếu bạn muốn luyện thêm.
Tải bộ đề tại đây: Bộ đề Excel có lời giải - Nghiệp vụ Purchasing (OneDrive)
Bạn muốn dùng Excel thành thạo cho công việc mua hàng?
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.