Bộ bài tập Excel nhân sự nâng cao này dành cho người làm nhân sự đã quen các hàm cơ bản và muốn luyện các việc khó hơn: báo cáo tuyển dụng hằng tuần, tính phép năm hằng tháng, xếp loại KPI và tính thưởng cuối năm. Độ khó tăng dần từ bài chuẩn hóa danh sách ứng viên tới bài lập báo cáo bằng hàm mảng động.
Bộ đề có 5 bài, tổng 100 điểm, thời gian làm bài gợi ý là 120 phút. Các bài xoay quanh 3 nghiệp vụ: báo cáo tuyển dụng (phễu ứng viên theo kênh, chi phí mỗi người tuyển được, tiến độ theo vị trí), phép năm còn lại (phép theo thâm niên, phép chuyển năm) và xếp loại KPI theo phân bố để tính thưởng cuối năm. Mọi công ty, tên người và số liệu trong đề đều là giả định.
Tải bộ đề: Bộ đề Excel có lời giải - Tuyển dụng, phép năm và KPI (OneDrive)
Bộ bài tập Excel nhân sự nâng cao này 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 | 20 | Bạn chuẩn hóa danh sách ứng viên và xác định tình trạng hồ sơ. | TRIM, PROPER, LEFT, FIND, VLOOKUP, IF lồng, AND |
2 | 20 | Bạn lập phễu tuyển dụng theo kênh và theo dõi tiến độ từng vị trí. | COUNTIFS, AVERAGEIFS, ROUND, MAX, IF, VLOOKUP, COUNTA |
3 | 20 | Bạn tính phép năm còn lại theo thâm niên và phép chuyển năm. | NETWORKDAYS, DATEDIF, DAY, MONTH, MIN, SUMIFS |
4 | 25 | Bạn chấm điểm KPI, xếp loại theo phân bố và tính thưởng cuối năm. | MIN, INDEX/MATCH, ROUND, COUNTIFS, IF lồng, VLOOKUP, SUMIFS |
5 | 15 | Bạn lập 3 báo cáo nhanh, mỗi báo cáo chỉ có 1 công thức. | FILTER, SORT, CHOOSECOLS, TAKE, UNIQUE, MAXIFS, XLOOKUP |
Bài 1 đến Bài 4 làm được trên Excel 2010 trở lên. Bài 5 cần Excel 365 hoặc Excel 2024, vì bài này dùng hàm mảng động như FILTER, CHOOSECOLS và TAKE.
Bộ đề này khác bộ đề nhân sự đã có ở đâu?
Nghề Excel đã có bộ đề chấm công, bảng lương và hạn hợp đồng với 4 bài về chấm công, bảng lương có bảo hiểm và thuế TNCN, hạn hợp đồng lao động và biến động nhân sự trong tháng. Bộ đề đó hợp với vị trí C&B hoặc hành chính nhân sự làm lương hằng tháng.
Bộ đề mới không lặp lại các nghiệp vụ trên. Bộ này tập trung vào 3 việc khác: báo cáo tuyển dụng cho team tuyển dụng, theo dõi phép năm còn lại cho người phụ trách quan hệ lao động, và xếp loại KPI để tính thưởng cuối năm. Bộ mới cũng thêm phần xử lý chữ (họ tên ứng viên gõ lộn xộn) và một bài dùng hàm mảng động của 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ạ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. Các tham số như ngày báo cáo, hạn sàng lọc, ngưỡng chi phí hay mức phép đều nằm trong bảng tra cứu, nên công thức của bạn cần tham chiếu các ô đó. 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 điểm khó và cách giải chính của từng bài. Yêu cầu đầy đủ nằm ngay trong file.
Bài 1: Bạn chuẩn hóa danh sách ứng viên và xác định tình trạng hồ sơ như thế nào?
Ứng viên tự gõ họ tên vào form, nên dữ liệu có cả chữ hoa, chữ thường và khoảng trắng thừa. Công thức chuẩn hóa họ tên tại ô K36 như sau:
=PROPER(TRIM(C36))
Mã hồ sơ có dạng WEB-001, FB-002 hay HCVL-005, nên mã kênh dài từ 2 đến 4 ký tự. Hàm FIND tìm vị trí dấu gạch ngang, rồi hàm LEFT lấy phần đứng trước. Công thức tại ô L36 như sau:
=LEFT(B36,FIND("-",B36)-1)
Điểm khó nhất của bài là cột Tình trạng. Một ứng viên có thể đã qua nhiều bước, nên bạn phải xét từ bước cuối của quy trình ngược về bước đầu. Công thức tại ô O36 như sau:
=IF(AND(J36<>"",J36<=$E$9),"Đã nhận việc",IF(I36="Từ chối","Từ chối offer",IF(H36<>"","Chờ nhận việc",IF(G36="Đạt","Chờ gửi offer",IF(G36="Không đạt","Loại sau PV",IF(F36="Đạt","Chờ phỏng vấn",IF(F36="Loại","Loại hồ sơ",IF($E$9-E36>$E$32,"Quá hạn sàng lọc","Chờ sàng lọc"))))))))
Ví dụ, ứng viên Hà Văn Thắng ở dòng 46 đã đồng ý offer nhưng ngày nhận việc là 01/12/2026, tức là sau Ngày báo cáo 30/11/2026. Điều kiện J46<=$E$9 giữ ứng viên này ở trạng thái "Chờ nhận việc". Hồ sơ WEB-020 nộp ngày 20/11/2026 đã chờ 10 ngày, vượt hạn sàng lọc 7 ngày, nên có trạng thái "Quá hạn sàng lọc". Hồ sơ FB-021 nộp ngày 23/11/2026 chờ đúng 7 ngày, nên vẫn là "Chờ sàng lọc".

Bài 2: Bạn lập báo cáo phễu tuyển dụng theo kênh như thế nào?
Phễu tuyển dụng đếm số ứng viên còn lại sau mỗi bước, từ lúc nộp hồ sơ tới lúc nhận việc. Bạn dùng COUNTIFS đếm theo Mã kênh ở Bài 1. Cột Gửi offer đếm các dòng đã có Ngày gửi offer, nên điều kiện là "<>" (khác rỗng). Công thức tại ô H77 như sau:
=COUNTIFS($L$36:$L$57,$B77,$H$36:$H$57,"<>")
Chi phí mỗi người tuyển được bằng Chi phí kỳ chia số người nhận việc. Kênh Hội chợ việc làm chưa có ai nhận việc, nên cột này ghi "-" thay vì báo lỗi chia cho 0. Kênh Mạng xã hội tốn 10.000.000 đồng cho 2 người, tức là 5.000.000 đồng mỗi người, đúng bằng ngưỡng ở ô E73. Vì điều kiện là "không vượt ngưỡng", kênh này được đánh giá "Hiệu quả".
Dòng Tổng cộng có một lỗi hay gặp. Số ngày tuyển bình quân của cả công ty phải tính lại trên toàn bộ 4 người nhận việc, không lấy trung bình của 3 kênh. Công thức tại ô L81 như sau:
=IF(I81=0,"-",ROUND(AVERAGEIFS($P$36:$P$57,$O$36:$O$57,"Đã nhận việc"),1))
Kết quả đúng là 41,3 ngày. Nếu lấy trung bình 3 kênh (31, 45 và 44 ngày), bạn sẽ ra 40,0 ngày, vì cách này coi kênh có 1 người nặng ngang kênh có 2 người.

Bảng tiến độ đếm số người đã nhận việc và số người đang chờ nhận việc của từng vị trí. Còn thiếu dùng =MAX(0,D86-F86-G86) để không ra số âm. Vị trí VT01 cần 3 người, mới có 2 người nhận việc và đã qua hạn 31/10/2026, nên có tình trạng "Trễ hạn". Vị trí VT04 có hạn tuyển đúng bằng Ngày báo cáo, nên vẫn là "Đang tuyển".

Bài 3: Bạn tính phép năm còn lại của từng nhân viên như thế nào?
Mức 12 ngày phép (14 ngày với nghề nặng nhọc, độc hại, nguy hiểm), cách tính phép theo tỷ lệ số tháng cho người làm chưa đủ năm và quy tắc cứ đủ 5 năm cộng 1 ngày lấy theo Điều 113, 114 Bộ luật Lao động 2019. Ngày chốt 15, phép chuyển tối đa 5 ngày, cách tính thâm niên tới ngày cuối năm và việc xếp cả phòng Kho vận vào nhóm Nặng nhọc là quy chế giả định của đề.
Bài này có 2 bảng. Bảng đầu là sổ đơn nghỉ từ tháng 9, bạn cần đếm số ngày của từng đơn. Đơn nửa ngày tính 0,5 ngày, còn đơn cả ngày dùng NETWORKDAYS để bỏ thứ 7, Chủ nhật và ngày lễ. Bảng ngày lễ của đề có cả ngày 24/11/2026, là Ngày Văn hóa Việt Nam mà người lao động được nghỉ hưởng nguyên lương từ năm 2026. Công thức tại ô G121 như sau:
=IF(F121="Nửa ngày",0.5,NETWORKDAYS(D121,E121,$C$106:$C$117))
Ví dụ, đơn của NV07 ghi từ 01/09/2026 tới 07/09/2026. Ngày 01/09 và 02/09 là ngày lễ, ngày 05/09 và 06/09 là cuối tuần, nên đơn này chỉ trừ 3 ngày phép.

Bảng thứ hai tính phép năm 2026 của 17 nhân viên. Người vào làm trong năm chỉ được phép theo số tháng làm việc. Theo quy chế của đề, tháng vào làm được tính đủ nếu ngày vào làm không sau ngày 15. Công thức tại ô J143 như sau:
=IF(F143<$E$10,12,IF(DAY(F143)<=$K$112,13,12)-MONTH(F143))
Phép năm lấy mức phép theo nhóm nhân với số tháng chia 12, làm tròn, rồi cộng 1 ngày cho mỗi 5 năm làm việc. Công thức tại ô K143 như sau:
=ROUND(VLOOKUP(E143,$H$106:$I$107,2,FALSE)*J143/12,0)+INT(I143/$K$110)
Nhân viên NV09 thuộc nhóm Nặng nhọc (14 ngày) và vào làm ngày 15/04/2026, đúng ngày chốt, nên tháng 4 được tính đủ và NV09 có 9 tháng làm việc. Phép năm của NV09 là 14 × 9 / 12 = 10,5, làm tròn thành 11 ngày. Theo cách tính thâm niên tới ngày cuối năm của đề, NV03 vào làm ngày 31/12/2021 đủ 5 năm vào đúng ngày 31/12/2026, nên được cộng thêm 1 ngày. Nhân viên NV05 vào làm ngày 01/01/2022 thì mới có 4 năm, nên không được cộng.
Đã nghỉ cộng số đã tổng hợp tới 31/08 với các đơn phép năm có Từ ngày không sau Ngày báo cáo. Các đơn có Từ ngày sau Ngày báo cáo được cộng vào cột Đã đăng ký. Công thức tại ô M143 như sau:
=H143+SUMIFS($G$121:$G$139,$B$121:$B$139,B143,$C$121:$C$139,"Phép năm",$D$121:$D$139,"<="&$E$9)

Nhân viên NV01 còn đúng 6 ngày, bằng Ngưỡng nhắc nghỉ phép, nên vẫn nhận cảnh báo "Nhắc nghỉ phép". Nhân viên NV02 còn tồn 8 ngày từ năm 2025, nhưng chỉ được chuyển sang tối đa 5 ngày.
Bài 4: Bạn xếp loại KPI theo phân bố và tính thưởng cuối năm như thế nào?
Mỗi phòng có trọng số riêng cho 3 nhóm chỉ tiêu, và mỗi chỉ tiêu chỉ được tính tối đa 120%. Bảng tra cứu của bài này nằm ngay trên bảng làm bài.

Điểm KPI lấy trọng số bằng INDEX kết hợp MATCH, trong đó MATCH tìm dòng của phòng ban. Công thức tại ô J189 như sau:
=IF(I189<$F$182,"",ROUND(MIN(D189,$F$180)*INDEX($D$175:$F$177,MATCH(H189,$C$175:$C$177,0),1)+MIN(E189,$F$180)*INDEX($D$175:$F$177,MATCH(H189,$C$175:$C$177,0),2)+MIN(F189,$F$180)*INDEX($D$175:$F$177,MATCH(H189,$C$175:$C$177,0),3),3))
Ví dụ, NV02 đạt 135% chỉ tiêu chính, nhưng chỉ được tính 120%. Điểm KPI của NV02 là 120% × 60% + 100% × 20% + 100% × 20% = 112,0%. Nếu không áp trần, NV02 sẽ có 121,0%.
Hạng trong phòng bằng 1 cộng số người cùng phòng có điểm cao hơn. Cách này giúp người bằng điểm có cùng hạng. Công thức tại ô K189 như sau:
=IF(J189="","-",COUNTIFS($H$189:$H$205,H189,$J$189:$J$205,">"&J189)+1)
Hàm ROUND ở Điểm KPI giữ cho NV03 và NV05 cùng có 98,0%, nên 2 người cùng hạng 2 trong phòng Kinh doanh. Nếu bỏ ROUND, sai số rất nhỏ của phép nhân số thập phân có thể làm 2 điểm này lệch nhau.

Bài này có nhiều trường hợp ở đúng mốc. NV01 đứng hạng 1 trong 5 người được xét của phòng Hành chính - Kế toán, tức là đúng 20%, nên xếp loại A. Phòng Kho vận chỉ có 3 người được xét, nên người đứng đầu là NV04 vẫn ở mức 33,3% và chỉ xếp loại B. NV09 có điểm 70,0%, đúng bằng điểm tối thiểu, nên không bị xếp D mà xếp C theo hạng. NV12 vào làm ngày 12/10/2026 nên tháng 10 được tính đủ, Số tháng làm việc là 3 và người này vẫn được xét, nhưng điểm 66,0% thấp hơn mức tối thiểu nên xếp D.
Thưởng cuối năm tại ô N189 dùng =ROUND(G189*M189*I189/12,-3) để làm tròn tới hàng nghìn. NV10 có lương 10.000.000 đồng, xếp loại B và làm 6 tháng, nên nhận 10.000.000 × 1,2 × 6 / 12 = 6.000.000 đồng. Dòng Tổng cộng của bảng KPI cộng cột Thưởng cuối năm tại ô N206 và ra 200.975.000 đồng.

Bài 5: Bạn lập báo cáo nhanh bằng hàm mảng động như thế nào?
Bài cuối cần Excel 365 hoặc Excel 2024. Bạn chỉ viết 1 công thức ở ô đầu của mỗi bảng, và kết quả tự tràn sang các ô còn lại. Hai bảng có số dòng thay đổi được đặt ở cuối sheet, nên kết quả tràn dài thêm cũng không chạm bảng khác.
Bảng top 3 Điểm KPI chỉ lọc các nhân viên đã có điểm, sắp xếp giảm dần rồi lấy 3 dòng đầu. Công thức tại ô B226 như sau:
=TAKE(SORT(FILTER(CHOOSECOLS($B$189:$N$205,1,2,7,9,11),$J$189:$J$205<>""),4,-1),3)
Bảng nhắc nghỉ phép lọc các dòng có cảnh báo "Nhắc nghỉ phép", chọn 4 cột cần xem rồi sắp xếp theo số ngày còn lại. Công thức tại ô B232 như sau:
=SORT(FILTER(CHOOSECOLS($B$143:$P$159,1,2,3,14),$P$143:$P$159="Nhắc nghỉ phép"),4,-1)

Bảng theo phòng ban bắt đầu bằng =UNIQUE($D$143:$D$159) ở ô K232. Các cột bên phải tham chiếu cả vùng tràn bằng dấu #. Cột Người có điểm cao nhất tại ô N232 nối Phòng ban với Điểm KPI thành một khóa tra:
=XLOOKUP(K232#&"|"&M232#,$H$189:$H$205&"|"&$J$189:$J$205,$C$189:$C$205)

Đề đã đơ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ễu tuyển dụng: Đề coi mỗi ứng viên chỉ ứng tuyển 1 vị trí qua 1 kênh, trong khi thực tế một người có thể nộp nhiều lần qua nhiều kênh.
Chi phí kênh: Đề cho sẵn tổng chi phí kỳ của từng kênh, còn khi đi làm bạn phải tự cộng từ hóa đơn đăng tin, quảng cáo và thưởng giới thiệu.
Phép chuyển năm: Đề chỉ giới hạn số ngày được chuyển, chưa xét hạn phải dùng hết phép chuyển (nhiều công ty chỉ cho dùng tới hết quý 1).
Phép theo tháng: Nghị định 145/2020 tính một tháng là đủ khi số ngày làm việc và ngày nghỉ có hưởng lương từ 50% số ngày làm việc bình thường của tháng, còn đề dùng ngày chốt 15 cho gọn.
Xếp loại theo phân bố: Đề xếp theo tỷ lệ hạng trong phòng, nên phòng ít người có thể không có ai xếp A như phòng Kho vận.
Khi đi làm, bạn cần kiểm tra các điểm này theo quy định hiện hành và quy chế 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ì Bài 2 dùng kết quả của Bài 1 và Bài 4 dùng kết quả của Bài 3. Nếu mới bắt đầu, bạn nên chắc Bài 1 và Bài 2 trước, vì 2 bài này luyện IF lồng và COUNTIFS. Bạn có thể đọc lại bài hàm đếm số lượng trong Excel nếu vướng ở COUNTIFS, và bài hàm RANK trong Excel nếu muốn hiểu thêm cách xếp hạng theo từng nhóm ở Bài 4.
Tải bộ đề tại đây: Bộ đề Excel có lời giải - Tuyển dụng, phép năm và KPI (OneDrive)
Bạn muốn dùng Excel thành thạo cho công việc nhân sự?
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.