Khi ứng tuyển vào vị trí nhân sự, đặc biệt là các vị trí C&B (lương, thưởng và phúc lợi) hay hành chính nhân sự, nhiều công ty sẽ yêu cầu bạn làm một bài test Excel ngay trên máy tính. Yêu cầu này rất dễ hiểu, vì công việc hằng tháng của người làm nhân sự gắn liền với bảng tính: tổng hợp chấm công, tính lương, theo dõi hợp đồng lao động và lập báo cáo nhân sự gửi Ban Giám đốc.

Nếu bạn chưa từng làm một bài test như vậy, cảm giác lo lắng là điều rất bình thường. Cách chuẩn bị hiệu quả nhất là bạn tự làm thử một bộ đề sát với thực tế, có bấm giờ, sau đó đối chiếu với lời giải để biết mình còn hổng ở đâu.

Nghề Excel đã soạn sẵn một bài test Excel nhân sự như thế: 4 bài, 90 phút, 100 điểm, xoay quanh 4 đầu việc người làm nhân sự gặp hằng tháng. File có sẵn sheet lời giải bằng công thức để bạn tự chấm. Bài viết này sẽ đi cùng bạn qua từng bài và giải thích cả phần nghiệp vụ nhân sự lẫn từng hàm Excel. Vì vậy, kể cả khi bạn mới bắt đầu học Excel, bạn vẫn có thể đọc hiểu và tự làm được.

Tải bộ đề: Bộ đề Excel nhân sự HR có lời giải (file Excel trê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ài test Excel nhân sự này gồm những gì?

Bài

Nghiệp vụ

Hàm Excel chính

Điểm

1

Tổng hợp chấm công tháng 9/2026

NETWORKDAYS.INTL, COUNTIFS, SUMIFS, IF, AND, Conditional Formatting

25

2

Bảng lương, bảo hiểm và thuế TNCN

ROUND, VLOOKUP, MIN, MAX, IF

25

3

Hồ sơ nhân sự và cảnh báo hết hạn hợp đồng

DATEDIF, EDATE, VLOOKUP, IF lồng nhau, Conditional Formatting

25

4

Báo cáo biến động nhân sự theo phòng ban

COUNTIFS, SUMIFS, AVERAGEIFS

25

Cả 4 bài dùng chung một bối cảnh: bạn là chuyên viên nhân sự tại Công ty Cổ phần XYZ. Công ty có 15 nhân viên làm việc trong tháng 9/2026 và làm việc từ thứ 2 đến thứ 7. Vì 4 bài lấy chung một danh sách nhân viên, các con số trong đề nối với nhau giống như khi bạn làm việc thật: kết quả chấm công ở Bài 1 được dùng để tính lương ở Bài 2 và lập báo cáo ở Bài 4.

Bạn mở file và bắt đầu làm bài như thế nào?

Bước 1: Bạn tải file về máy tính

Link tài liệu mở file trên OneDrive bằng Excel bản web. Bạn có thể đọc đề ngay trên trình duyệt, nhưng để làm bài thoải mái, bạn nên tải file về máy. Bạn vào File, chọn Save As, rồi chọn Download a Copy để tải một bản sao về máy, sau đó mở file bằng Excel trên máy tính. Bộ đề dùng hàm NETWORKDAYS.INTL, nên máy của bạn cần có Excel 2010 trở lên.

Bước 2: Bạn làm quen với 3 sheet trong file

Một file Excel có thể chứa nhiều trang tính, gọi là sheet. Tên các sheet nằm ở thanh dưới cùng của cửa sổ Excel, bạn bấm vào tên sheet nào thì Excel mở sheet đó. Bộ đề có 3 sheet:

  • Giới thiệu khóa học giới thiệu về Nghề Excel. Sheet này không ảnh hưởng tới bài làm.

  • Đề bài là nơi bạn đọc đề và làm bài. Các cột có tiêu đề màu vàng đang để trống, đó là phần bạn cần điền.

  • Kết quả chứa lời giải bằng công thức cho mọi ô màu vàng. Bạn chỉ nên mở sheet này sau khi đã làm xong bài.

Bước 3: Bạn đọc bố cục của từng bài

Mỗi bài trong sheet Đề bài được trình bày theo cùng một thứ tự, nên bạn chỉ cần làm quen một lần là đọc được cả 4 bài:

  • Thanh màu xanh đậm ghi số thứ tự, tên bài và số điểm.

  • Bối cảnh nghiệp vụ mô tả tình huống công việc mà bạn đang xử lý.

  • Yêu cầu liệt kê từng việc bạn cần làm, được đánh số theo thứ tự.

  • Bảng tra cứu chứa các tham số dùng chung, ví dụ ngày đầu tháng, danh sách ngày lễ, biểu thuế.

  • Dữ liệu là bảng số liệu đầu vào, ví dụ sổ theo dõi nghỉ của nhân viên.

  • Phần làm bài là bảng có các cột tiêu đề màu vàng để bạn điền công thức.

Đề có 3 quy định chung bạn cần nhớ. Bạn phải dùng công thức thay vì tự tính rồi gõ số vào ô. Công thức phải kéo xuống được cho cả cột. Bạn cũng không được sửa dữ liệu đầu vào.

Bạn cần nắm 5 kiến thức Excel nào trước khi làm bài test Excel nhân sự?

Phần này dành cho bạn nào mới làm quen với Excel. Nếu bạn đã quen viết công thức, bạn có thể chuyển thẳng sang phần tiếp theo.

1. Mỗi ô trong Excel có một địa chỉ riêng

Trang tính Excel là một lưới gồm các cột được đặt tên bằng chữ cái (A, B, C...) và các dòng được đánh số (1, 2, 3...). Giao của một cột và một dòng là một ô. Ô được gọi theo tên cột trước, số dòng sau, ví dụ ô E69 là ô nằm ở cột E, dòng 69. Công thức trong Excel làm việc với địa chỉ ô thay vì con số cụ thể, nhờ vậy khi dữ liệu thay đổi, kết quả cũng tự thay đổi theo.

2. Công thức luôn bắt đầu bằng dấu bằng

Để viết công thức, bạn bấm vào một ô, gõ dấu =, gõ phép tính rồi nhấn Enter. Ví dụ, công thức =G69+H69 cộng giá trị của 2 ô G69 và H69. Hàm là những phép tính có sẵn của Excel. Một hàm được viết theo dạng tên hàm, theo sau là các thông tin hàm cần (gọi là đối số) nằm trong cặp ngoặc tròn. Ví dụ, công thức =SUM(I69:I83) cộng tất cả các ô từ I69 đến I83.

Các đối số được ngăn cách bằng dấu phẩy hoặc dấu chấm phẩy, tùy theo cài đặt vùng (Region) trên máy tính của bạn. Bài viết này dùng dấu phẩy. Nếu Excel báo lỗi khi bạn gõ dấu phẩy, bạn chỉ cần đổi sang dấu chấm phẩy.

3. Dấu $ giữ cố định ô khi bạn kéo công thức

Sau khi viết công thức ở dòng đầu tiên, bạn đưa chuột vào góc dưới bên phải của ô cho tới khi con trỏ biến thành dấu cộng màu đen. Lúc này, bạn kéo xuống hoặc bấm đúp chuột để sao chép công thức cho cả cột. Khi sao chép, Excel tự dịch các địa chỉ ô theo dòng: ô E69 trong công thức ở dòng đầu sẽ trở thành E70 ở dòng tiếp theo.

Việc tự dịch này rất tiện cho dữ liệu riêng của từng nhân viên. Tuy nhiên, những ô dùng chung như Ngày đầu tháng hay bảng tra cứu thì phải đứng yên. Để giữ cố định một ô, bạn thêm dấu $ trước tên cột và trước số dòng, ví dụ $E$40. Cách nhanh nhất là bạn bấm vào địa chỉ ô trong công thức rồi nhấn phím F4. Quên dấu $ là lỗi phổ biến nhất trong các bài test Excel: dòng đầu ra kết quả đúng, nhưng các dòng sau lại sai hoặc báo lỗi.

4. Ngày tháng trong Excel thực chất là một con số

Excel lưu mỗi ngày dưới dạng một số thứ tự, rồi hiển thị con số đó ra dạng ngày tháng cho bạn dễ đọc. Ví dụ, ngày 30/09/2026 được Excel lưu là số 46295. Nhờ vậy, bạn có thể cộng trừ ngày như cộng trừ số: ngày 30/09/2026 trừ ngày 31/08/2026 cho kết quả là 30 ngày. Nếu một ô kết quả hiện ra con số lạ như 46295, ô đó chỉ đang sai định dạng. Bạn chọn ô, vào tab Home và chọn định dạng Short Date là ô hiện lại thành ngày.

5. Chữ trong công thức phải đặt trong ngoặc kép

Khi công thức cần trả về một đoạn chữ như "Đạt", "Không đạt", hoặc cần so sánh với một đoạn chữ, bạn phải đặt đoạn chữ đó trong dấu ngoặc kép. Hai dấu ngoặc kép liền nhau "" có nghĩa là ô trống. Với con số và địa chỉ ô, bạn không dùng ngoặc kép.

Nếu muốn ôn lại kỹ hơn các thao tác cơ bản này, bạn có thể đọc bài Hướng dẫn sử dụng Excel cho người mới bắt đầu trước khi làm đề.

Danh sách nhân viên dùng chung cho cả 4 bài có gì?

Phần đầu sheet Đề bài là danh sách 15 nhân viên thuộc 5 phòng ban. Mỗi nhân viên có mã nhân viên, họ tên, phòng ban, chức vụ, ngày sinh, ngày vào làm, ngày làm việc cuối cùng, lương cơ bản, số người phụ thuộc, loại hợp đồng và ngày ký hợp đồng hiện tại. Ô Ngày làm việc cuối cùng để trống nghĩa là nhân viên đó vẫn đang làm việc.

Danh sách 15 nhân viên tháng 9/2026 dùng chung cho cả 4 bài trong bài test Excel nhân sự
Danh sách nhân viên dùng chung, có 1 người nghỉ việc và 1 người mới vào làm trong tháng

Bạn nên dành 2 đến 3 phút đọc kỹ danh sách này trước khi làm bài. Đề cố ý cài sẵn một vài trường hợp đặc biệt để kiểm tra xem công thức của bạn có xử lý được hay không:

  • Nhân viên NV08 (Bùi Ngọc Lan) nghỉ việc sau ngày 18/09/2026, nên chỉ được tính công đến ngày này.

  • Nhân viên NV13 (Trịnh Minh Quân) mới vào làm ngày 28/09/2026, nên chỉ có 3 ngày làm việc trong tháng.

  • Hai nhân viên NV09 và NV13 đang thử việc, nên bảo hiểm và thuế của họ được tính theo cách riêng.

  • Lương của Giám đốc NV01 và Trưởng phòng Kỹ thuật NV10 vượt mức lương tối đa đóng bảo hiểm, nên bảo hiểm của họ chỉ tính đến mức trần.

Một công thức tốt phải cho kết quả đúng ở cả những dòng bình thường lẫn những dòng đặc biệt này. Khi làm xong mỗi cột, bạn nên dò lại kết quả ở các dòng NV08, NV09, NV13 trước tiên.

Bài 1: Bạn tổng hợp chấm công tháng 9/2026 như thế nào?

Bài 1 yêu cầu bạn làm gì?

Đề cho Sổ theo dõi nghỉ gồm 15 lượt nghỉ trong tháng. Mỗi lượt ghi ngày nghỉ, mã nhân viên, loại nghỉ và số ngày nghỉ, trong đó có lượt nghỉ nửa ngày được ghi là 0,5. Từ sổ này và danh sách nhân viên, bạn lập bảng tổng hợp chấm công gồm 7 cột: Ngày làm việc, Ngày lễ hưởng lương, Nghỉ phép, Nghỉ ốm, Nghỉ không phép, Ngày công tính lương và Chuyên cần.

Bài 1 bài test Excel nhân sự: bảng tổng hợp chấm công tháng 9/2026 với 7 cột vàng cần điền
Bài 1: bảng tổng hợp chấm công, các cột vàng là phần bạn cần điền bằng công thức

Bạn cần hiểu những khái niệm chấm công nào?

  • Ngày làm việc là số ngày nhân viên phải đi làm trong tháng. Công ty làm việc từ thứ 2 đến thứ 7, nên bạn không tính Chủ nhật và ngày lễ.

  • Ngày lễ hưởng lương là ngày lễ nằm trong thời gian nhân viên còn làm việc. Người lao động được nghỉ ngày lễ mà vẫn hưởng nguyên lương, nên ngày lễ không phải đi làm nhưng vẫn được cộng vào công tính lương. Đề giả định tháng 9/2026 có 2 ngày lễ là 1/9 và 2/9.

  • Nghỉ phép năm (P) là ngày nghỉ vẫn được công ty trả lương.

  • Nghỉ ốm (Ô) là ngày nghỉ được hưởng chế độ ốm đau từ quỹ BHXH, nên công ty không trả lương cho những ngày này.

  • Nghỉ không phép (KP) là ngày nghỉ không được trả lương.

Từ các khái niệm trên, đề đưa ra cách tính: Ngày công tính lương = Ngày làm việc + Ngày lễ hưởng lương − Nghỉ ốm − Nghỉ không phép. Nghỉ phép năm không bị trừ, vì ngày nghỉ phép vẫn được trả lương.

Bạn đếm ngày làm việc bằng hàm NETWORKDAYS.INTL như thế nào?

Hàm NETWORKDAYS.INTL đếm số ngày làm việc giữa 2 ngày. Hàm tự bỏ qua những ngày cuối tuần và những ngày lễ bạn liệt kê. Cú pháp của hàm gồm 4 đối số:

=NETWORKDAYS.INTL(ngày bắt đầu, ngày kết thúc, mã cuối tuần, danh sách ngày lễ)

  • Ngày bắt đầu và ngày kết thúc được tính cả 2 đầu.

  • Mã cuối tuần cho Excel biết những ngày nào trong tuần là ngày nghỉ. Mã 1 là nghỉ thứ 7 và Chủ nhật, mã 11 là chỉ nghỉ Chủ nhật. Công ty trong đề làm cả thứ 7, nên bạn dùng mã 11.

  • Danh sách ngày lễ là vùng ô chứa các ngày lễ ở Bảng tra cứu, cụ thể là vùng $B$45:$B$46.

Điểm khó của yêu cầu này nằm ở chỗ mỗi nhân viên có một khoảng thời gian làm việc khác nhau. Ngày bắt đầu là ngày muộn hơn giữa Ngày vào làm và Ngày đầu tháng, nên bạn dùng hàm MAX để lấy ngày lớn hơn. Ngày kết thúc là Ngày làm việc cuối cùng, nhưng nếu ô này trống (nhân viên vẫn đang làm việc) thì bạn lấy Ngày cuối tháng, nên bạn dùng hàm IF. Ghép lại, công thức ở dòng đầu tiên là:

=NETWORKDAYS.INTL(MAX(E69,$E$40),IF(F69="",$E$41,F69),11,$B$45:$B$46)

Trong công thức này, ô $E$40 là Ngày đầu tháng (01/09/2026), ô $E$41 là Ngày cuối tháng (30/09/2026), còn E69 và F69 là Ngày vào làm và Ngày làm việc cuối cùng của nhân viên ở dòng đó. Với một nhân viên đã làm từ trước tháng 9 và vẫn đang làm, công thức đếm từ 01/09 đến 30/09 và cho kết quả 24 ngày.

Lịch tháng 9/2026: cách đếm ngày làm việc và ngày lễ hưởng lương cho nhân viên nghỉ việc ngày 18/9
NV08 làm đến 18/9: 14 ngày làm việc và 2 ngày lễ vẫn được hưởng lương

Hình trên minh họa trường hợp của nhân viên NV08. Từ ngày 1/9 đến ngày 18/9 có 18 ngày. Sau khi bỏ 2 ngày Chủ nhật (6/9 và 13/9) và 2 ngày lễ (1/9 và 2/9), nhân viên này còn 14 ngày làm việc. Hai ngày lễ đều nằm trong thời gian NV08 còn làm việc, nên NV08 vẫn được tính 2 ngày lễ hưởng lương.

Bạn đếm ngày lễ hưởng lương bằng hàm COUNTIFS như thế nào?

Hàm COUNTIFS đếm số ô thỏa mãn đồng thời nhiều điều kiện. Cú pháp của hàm gồm từng cặp vùng điều kiện và điều kiện. Ở yêu cầu này, bạn đếm trong danh sách ngày lễ những ngày nằm trong khoảng thời gian làm việc của nhân viên, tức là những ngày lớn hơn hoặc bằng ngày bắt đầu và nhỏ hơn hoặc bằng ngày kết thúc:

=COUNTIFS($B$45:$B$46,">="&MAX(E69,$E$40),$B$45:$B$46,"<="&IF(F69="",$E$41,F69))

Khi điều kiện là một phép so sánh với giá trị nằm trong ô, bạn viết dấu so sánh trong ngoặc kép rồi nối với ô bằng dấu &, ví dụ ">="&MAX(E69,$E$40). Bạn sẽ dùng lại cách viết này nhiều lần ở Bài 4. Với nhân viên NV13 vào làm ngày 28/09, cả 2 ngày lễ đều nằm trước ngày vào làm, nên kết quả bằng 0.

Bạn cộng số ngày nghỉ bằng hàm SUMIFS như thế nào?

Hàm SUMIFS cộng các ô thỏa mãn nhiều điều kiện. Đối số đầu tiên là vùng cần cộng, các đối số tiếp theo là từng cặp vùng điều kiện và điều kiện. Để tính số ngày nghỉ phép của một nhân viên, bạn cộng cột Số ngày trong Sổ theo dõi nghỉ, với điều kiện cột Mã NV bằng mã của nhân viên đó và cột Loại nghỉ bằng "P":

=SUMIFS($F$51:$F$65,$C$51:$C$65,$B69,$E$51:$E$65,"P")

Bạn dùng SUMIFS thay vì COUNTIFS vì sổ có những lượt nghỉ nửa ngày. Nếu bạn đếm số lượt nghỉ, một lượt nghỉ 0,5 ngày sẽ bị tính thành 1 ngày. Hai cột Nghỉ ốm và Nghỉ không phép dùng công thức tương tự, bạn chỉ cần thay "P" thành "Ô" và "KP".

Ô $B69 chỉ có dấu $ trước chữ B. Cách viết này giữ cố định cột Mã NV nhưng vẫn cho số dòng thay đổi khi bạn kéo công thức xuống. Nhờ vậy, bạn có thể viết công thức một lần ở cột Nghỉ phép rồi kéo sang 2 cột bên cạnh, chỉ cần sửa lại loại nghỉ.

Bạn xếp loại chuyên cần bằng hàm IF và AND như thế nào?

Theo đề, một nhân viên đạt chuyên cần khi thỏa cả 2 điều kiện: nhân viên không có ngày nghỉ không phép, và tổng số ngày nghỉ không vượt quá 2 ngày (ô $E$42). Khi cần nhiều điều kiện cùng đúng, bạn đặt chúng trong hàm AND rồi đưa hàm AND vào hàm IF:

=IF(AND(K69=0,I69+J69+K69<=$E$42),"Đạt","Không đạt")

Hàm IF hoạt động như một câu "nếu... thì... ngược lại...": nếu điều kiện đúng thì hàm trả về "Đạt", ngược lại hàm trả về "Không đạt". Trong dữ liệu có 3 nhân viên không đạt với 2 lý do khác nhau. Nhân viên NV07 và NV11 có ngày nghỉ không phép. Nhân viên NV06 không nghỉ không phép, nhưng nghỉ tổng cộng 3 ngày. Bạn có thể đọc thêm về cách kết hợp nhiều điều kiện trong bài Hàm IF nhiều điều kiện trong Excel.

Bạn kiểm tra và tô màu kết quả Bài 1 như thế nào?

Ô Kiểm tra so sánh tổng 3 cột nghỉ với tổng cột Số ngày trong Sổ theo dõi nghỉ. Hai con số này phải bằng nhau và cùng bằng 13,5 ngày, vì mỗi lượt nghỉ phải được cộng vào đúng một nhân viên. Nếu ô Kiểm tra báo "Lệch", có thể bạn đã gõ sai ký hiệu loại nghỉ trong công thức hoặc quên dấu $.

Yêu cầu cuối của Bài 1 là tô nền đỏ, chữ trắng cho các ô "Không đạt" bằng Conditional Formatting (định dạng có điều kiện). Bạn làm theo 3 bước:

  1. Bạn chọn vùng cột Chuyên cần, từ ô M69 đến ô M83.

  2. Bạn vào tab Home, bấm Conditional Formatting, chọn Highlight Cells Rules rồi chọn Equal To.

  3. Bạn gõ Không đạt vào ô bên trái. Ở ô bên phải, bạn chọn Custom Format, đặt màu nền đỏ ở thẻ Fill và màu chữ trắng ở thẻ Font, rồi bấm OK.

Từ giờ, mỗi khi kết quả trong cột đổi thành "Không đạt", ô đó tự chuyển sang màu đỏ mà bạn không phải tô tay.

Bài 2: Bạn lập bảng lương, tính bảo hiểm và thuế TNCN như thế nào?

Bài 2 yêu cầu bạn làm gì?

Bài 2 cho sẵn Ngày công tính lương của từng nhân viên, đây chính là kết quả của Bài 1. Nhờ vậy, bạn vẫn làm được Bài 2 kể cả khi chưa làm xong Bài 1. Bạn cần tính 6 cột: Lương theo ngày công, Phụ cấp chức vụ, BH người lao động, Thu nhập tính thuế, Thuế TNCN và Thực lĩnh.

Bài 2 bài test Excel nhân sự: bảng lương tháng 9/2026, bảng phụ cấp chức vụ và biểu thuế TNCN
Bài 2: bảng lương cùng bảng phụ cấp chức vụ, biểu thuế và các tham số giả định

Đây là bài nặng nghiệp vụ nhất trong bộ đề. Trước khi viết công thức, bạn nên nắm thứ tự tính của một bảng lương:

  1. Bạn tính lương theo số ngày công thực tế.

  2. Bạn cộng thêm các khoản phụ cấp để có tổng thu nhập.

  3. Bạn trừ tiền bảo hiểm bắt buộc mà người lao động phải đóng.

  4. Bạn trừ tiếp các khoản giảm trừ gia cảnh để có thu nhập tính thuế.

  5. Bạn tính thuế thu nhập cá nhân (TNCN) trên thu nhập tính thuế.

  6. Bạn lấy tổng thu nhập trừ bảo hiểm và thuế để ra số tiền thực lĩnh.

Mỗi cột vàng trong bảng lương ứng với một bước ở trên, nên bạn cứ đi lần lượt từ trái sang phải là không bị rối.

Bạn tính lương theo ngày công như thế nào?

Lương theo ngày công bằng Lương cơ bản chia cho Ngày công chuẩn, rồi nhân với Ngày công tính lương. Ngày công chuẩn tháng 9/2026 của công ty là 26 ngày, đã bao gồm 2 ngày lễ. Một nhân viên đi làm đủ tháng có 26 công, nên nhận đủ lương cơ bản. Nhân viên nghỉ không lương hoặc làm không trọn tháng sẽ nhận lương theo tỷ lệ số công thực tế. Hàm ROUND với đối số thứ hai là 0 làm tròn kết quả đến hàng đồng:

=ROUND(E111/$E$101*F111,0)

Ô $E$101 là ô Ngày công chuẩn trong Bảng tra cứu. Ô này được cố định bằng dấu $, vì mọi nhân viên đều chia cho cùng một ngày công chuẩn.

Bạn lấy phụ cấp chức vụ bằng hàm VLOOKUP như thế nào?

Hàm VLOOKUP tìm một giá trị ở cột đầu tiên của bảng tra cứu, rồi trả về giá trị nằm cùng dòng ở cột bạn chỉ định. Cú pháp của hàm gồm 4 đối số: giá trị cần tìm, bảng tra cứu, số thứ tự của cột cần lấy và kiểu tìm. Đối số kiểu tìm bằng 0 nghĩa là Excel chỉ chấp nhận kết quả trùng khớp chính xác:

=VLOOKUP(D111,$G$101:$H$104,2,0)

Công thức tìm chức vụ ở ô D111 trong bảng phụ cấp $G$101:$H$104 và lấy giá trị ở cột thứ 2 của bảng. Nếu bạn dùng Excel 365 hoặc Excel 2021, bạn có thể dùng hàm XLOOKUP thay cho VLOOKUP. Bạn xem cách dùng trong bài Hàm XLOOKUP trong Excel.

Bạn tính bảo hiểm có mức trần như thế nào?

Người lao động đóng 10,5% tiền lương tháng cho 3 loại bảo hiểm bắt buộc, gồm 8% bảo hiểm xã hội (BHXH), 1,5% bảo hiểm y tế (BHYT) và 1% bảo hiểm thất nghiệp (BHTN). Tiền lương đóng bảo hiểm gồm lương cơ bản cộng phụ cấp chức vụ, nhưng chỉ được tính tối đa đến một mức trần. Đề giả định mức trần là 46.800.000. Hàm MIN chọn số nhỏ hơn giữa tiền lương đóng bảo hiểm thực tế và mức trần:

=IF(G111="Thử việc",0,ROUND($E$102*MIN(E111+J111,$E$103),0))

Nhân viên thử việc trong đề ký hợp đồng thử việc riêng nên không đóng bảo hiểm bắt buộc. Vì vậy, công thức có thêm hàm IF bọc bên ngoài để trả về 0 khi Loại hợp đồng là "Thử việc". Với Giám đốc NV01, lương cơ bản cộng phụ cấp là 95.000.000, vượt mức trần, nên bảo hiểm chỉ tính trên 46.800.000 và bằng 4.914.000.

Bạn tính thu nhập tính thuế như thế nào?

Thu nhập tính thuế là phần thu nhập còn lại sau khi trừ bảo hiểm và các khoản giảm trừ gia cảnh. Đề giả định mức giảm trừ cho bản thân người nộp thuế là 15.500.000 mỗi tháng, và mức giảm trừ cho mỗi người phụ thuộc là 6.200.000 mỗi tháng. Nếu kết quả âm, nhân viên không phải nộp thuế, nên bạn dùng hàm MAX với số 0 để đưa các số âm về 0:

=IF(G111="Thử việc",0,MAX(I111+J111-K111-$E$104-H111*$E$105,0))

Hàm MAX trả về số lớn hơn trong các số bạn đưa vào. Khi phép tính bên trong ra số âm, số 0 lớn hơn nên hàm trả về 0. Khi phép tính ra số dương, hàm giữ nguyên kết quả. Nhân viên thử việc được ghi 0 ở cột này, vì thuế của họ tính theo cách khác ở bước tiếp theo.

Bạn tính thuế TNCN theo biểu lũy tiến như thế nào?

Biểu thuế lũy tiến từng phần chia thu nhập tính thuế thành nhiều bậc, mỗi bậc chịu một thuế suất riêng. Theo biểu thuế giả định trong đề, phần thu nhập đến 10 triệu chịu thuế 5%, phần trên 10 triệu đến 30 triệu chịu thuế 10%, và các bậc sau tăng dần. Việc tính thuế từng bậc rồi cộng lại khá dài, nên người làm lương thường dùng cách tính rút gọn với số trừ nhanh:

Thuế TNCN = Thu nhập tính thuế × Thuế suất của bậc cao nhất − Số trừ nhanh của bậc đó

Biểu thuế trong Bảng tra cứu chỉ ghi mốc bắt đầu của mỗi bậc: 0, 10 triệu, 30 triệu, 60 triệu và 100 triệu. Để biết một mức thu nhập rơi vào bậc nào, bạn dùng VLOOKUP với đối số cuối là TRUE, tức là tìm gần đúng. Khi tìm gần đúng, VLOOKUP lấy dòng có mốc lớn nhất nhưng không vượt quá giá trị cần tìm, đúng bằng cách xác định bậc thuế. Cột mốc phải được sắp xếp tăng dần thì cách tìm này mới cho kết quả đúng. Phần tính thuế cho nhân viên chính thức có dạng:

=ROUND(L111*VLOOKUP(L111,$J$101:$L$105,2,TRUE)-VLOOKUP(L111,$J$101:$L$105,3,TRUE),0)

Với nhân viên thử việc có hợp đồng dưới 3 tháng, công ty khấu trừ thuế 10% trên tổng thu nhập nếu khoản chi trả từ 2.000.000 trở lên. Dưới mức này, công ty không khấu trừ. Vì vậy, công thức hoàn chỉnh ở ô M111 có thêm một hàm IF bọc ngoài để tách 2 trường hợp:

=IF(G111="Thử việc",IF(I111+J111>=$E$107,ROUND((I111+J111)*$E$106,0),0),ROUND(L111*VLOOKUP(L111,$J$101:$L$105,2,TRUE)-VLOOKUP(L111,$J$101:$L$105,3,TRUE),0))

Trong dữ liệu, nhân viên NV09 nhận 8.826.923 nên bị khấu trừ 10%. Nhân viên NV13 mới làm 3 ngày, nhận 1.730.769, thấp hơn 2.000.000 nên không bị khấu trừ.

Cuối cùng, Thực lĩnh bằng Lương theo ngày công cộng Phụ cấp chức vụ, trừ BH người lao động và trừ Thuế TNCN: =I111+J111-K111-M111.

Bảng lương của nhân viên NV05 được tính ra sao?

Để bạn hình dung rõ cả quy trình, bảng dưới đây tính lần lượt từng cột cho Trưởng phòng Kinh doanh NV05. Nhân viên này có lương cơ bản 35.000.000, đi làm đủ 26 công và không có người phụ thuộc:

Bước

Cách tính

Kết quả

Lương theo ngày công

35.000.000 / 26 × 26

35.000.000

Phụ cấp chức vụ

Chức vụ Trưởng phòng

3.000.000

Lương đóng bảo hiểm

35.000.000 + 3.000.000, chưa vượt 46.800.000

38.000.000

BH người lao động

38.000.000 × 10,5%

3.990.000

Thu nhập tính thuế

35.000.000 + 3.000.000 − 3.990.000 − 15.500.000

18.510.000

Thuế TNCN

18.510.000 × 10% − 500.000

1.351.000

Thực lĩnh

35.000.000 + 3.000.000 − 3.990.000 − 1.351.000

32.659.000

Bạn có thể tự kiểm tra lại số thuế bằng cách tính từng bậc. Phần 10.000.000 đầu tiên chịu thuế 5%, tức là 500.000. Phần 8.510.000 còn lại chịu thuế 10%, tức là 851.000. Cộng 2 phần lại, bạn được đúng 1.351.000, bằng kết quả của công thức số trừ nhanh. Cách đối chiếu này giúp bạn yên tâm rằng công thức thuế của mình không bị sai.

Bạn thường mắc lỗi gì ở Bài 2?

  • Bạn dùng VLOOKUP tìm chính xác cho biểu thuế. Hầu hết các mức thu nhập không trùng với mốc của biểu thuế, nên VLOOKUP với đối số 0 sẽ báo lỗi #N/A.

  • Bạn quên đưa số âm về 0 ở cột Thu nhập tính thuế. Khi đó, thuế bị tính ra số âm và làm số tiền thực lĩnh tăng sai.

  • Bạn tính bảo hiểm trên lương theo ngày công. Theo cách tính trong đề, bảo hiểm tính trên lương cơ bản cộng phụ cấp chức vụ, không phụ thuộc số công trong tháng.

  • Bạn bỏ sót trường hợp thử việc. Công thức không có hàm IF tách riêng thử việc sẽ tính bảo hiểm và thuế lũy tiến cho cả NV09 và NV13.

Bài 3: Bạn theo dõi hồ sơ và hạn hợp đồng lao động như thế nào?

Bài 3 yêu cầu bạn làm gì?

Trưởng phòng Nhân sự cần danh sách 14 nhân viên đang làm việc tại ngày 30/09/2026, kèm tuổi, thâm niên và tình trạng hợp đồng lao động. Danh sách này giúp phòng nhân sự kịp gia hạn hoặc ký hợp đồng mới trước khi hợp đồng cũ hết hạn. Nhân viên NV08 đã nghỉ việc nên không có trong danh sách. Bạn cần tính 6 cột: Tuổi, Thâm niên, Thời hạn HĐ, Ngày hết hạn HĐ, Số ngày còn lại và Tình trạng HĐ.

Bài 3 bài test Excel nhân sự: tính tuổi, thâm niên và cảnh báo hợp đồng lao động sắp hết hạn
Bài 3: hồ sơ 14 nhân viên đang làm việc và 4 tình trạng hợp đồng cần xác định

Bạn tính tuổi và thâm niên bằng hàm DATEDIF như thế nào?

Hàm DATEDIF tính khoảng cách giữa 2 ngày theo đơn vị bạn chọn, với cú pháp =DATEDIF(ngày đầu, ngày cuối, "đơn vị"). Đơn vị "y" cho số năm tròn, đơn vị "m" cho số tháng tròn, còn đơn vị "ym" cho số tháng lẻ sau khi đã bỏ đi các năm tròn. DATEDIF là một hàm ẩn: Excel không gợi ý tên hàm và cú pháp khi bạn gõ, nên bạn cần gõ đúng chính tả và đủ 3 đối số. Tuổi của nhân viên là số năm tròn tính từ ngày sinh đến Ngày báo cáo ở ô $E$141:

=DATEDIF(D150,$E$141,"y")

Thâm niên cần hiển thị theo dạng "x năm y tháng". Bạn tính số năm bằng một hàm DATEDIF với đơn vị "y", tính số tháng lẻ bằng một hàm DATEDIF khác với đơn vị "ym", rồi dùng dấu & để nối các kết quả với chữ " năm " và " tháng":

=DATEDIF(E150,$E$141,"y")&" năm "&DATEDIF(E150,$E$141,"ym")&" tháng"

Ví dụ, nhân viên NV01 vào làm ngày 01/03/2015, nên đến ngày 30/09/2026 có thâm niên là "11 năm 6 tháng". Bạn có thể tìm hiểu thêm các hàm xử lý ngày tháng khác trong bài các hàm ngày tháng trong Excel.

Bạn tính ngày hết hạn hợp đồng bằng hàm EDATE như thế nào?

Bảng tra cứu loại hợp đồng có 2 đơn vị thời hạn. Hợp đồng chính thức tính theo tháng (12, 24 hoặc 36 tháng). Hợp đồng thử việc tính theo ngày, vì Bộ luật Lao động giới hạn thời gian thử việc theo số ngày, tối đa 60 ngày với công việc yêu cầu trình độ cao đẳng trở lên. Hợp đồng không thời hạn có thời hạn bằng 0. Bước đầu tiên, bạn dùng VLOOKUP lấy thời hạn theo loại hợp đồng: =VLOOKUP(F150,$G$142:$I$146,2,0).

Với thời hạn tính theo tháng, bạn dùng hàm EDATE(ngày, số tháng). Hàm này trả về ngày nằm sau ngày ban đầu đúng số tháng bạn chỉ định. Kết quả cần trừ thêm 1 ngày, vì hợp đồng 12 tháng ký ngày 01/09/2025 sẽ kết thúc vào ngày 31/08/2026 chứ không phải ngày 01/09/2026. Với thời hạn tính theo ngày, bạn lấy ngày ký cộng số ngày rồi cũng trừ 1 theo cùng lý do: ngày ký được tính là ngày đầu tiên của hợp đồng.

=IF(J150=0,"",IF(VLOOKUP(F150,$G$142:$I$146,3,0)="Ngày",G150+J150-1,EDATE(G150,J150)-1))

Công thức này lồng 2 hàm IF. Hàm IF thứ nhất trả về ô trống nếu hợp đồng không thời hạn. Hàm IF thứ hai dùng VLOOKUP lấy cột Đơn vị trong bảng tra cứu để quyết định cộng theo ngày hay cộng theo tháng. Kết quả của EDATE là một con số ngày tháng, nên nếu ô hiện ra một con số như 46265, bạn chỉ cần định dạng lại ô thành kiểu ngày như đã hướng dẫn ở phần kiến thức nền.

Số ngày còn lại bằng Ngày hết hạn trừ Ngày báo cáo. Hợp đồng không thời hạn không có ngày hết hạn, nên ô Số ngày còn lại cũng để trống: =IF(K150="","",K150-$E$141).

Bạn xác định tình trạng hợp đồng bằng IF lồng nhau như thế nào?

Từ Số ngày còn lại, bạn xếp mỗi hợp đồng vào 1 trong 4 tình trạng. Bạn cần kiểm tra các điều kiện theo đúng thứ tự sau:

  1. Nếu hợp đồng không có ngày hết hạn, tình trạng là "Không thời hạn".

  2. Nếu Số ngày còn lại nhỏ hơn 0, tình trạng là "Đã hết hạn".

  3. Nếu Số ngày còn lại không vượt quá ngưỡng cảnh báo 30 ngày, tình trạng là "Sắp hết hạn".

  4. Các trường hợp còn lại có tình trạng là "Còn hạn".

=IF(K150="","Không thời hạn",IF(L150<0,"Đã hết hạn",IF(L150<=$E$142,"Sắp hết hạn","Còn hạn")))

Thứ tự các điều kiện rất quan trọng. Một hợp đồng còn −30 ngày cũng thỏa điều kiện "nhỏ hơn hoặc bằng 30". Nếu bạn kiểm tra điều kiện "Sắp hết hạn" trước, hợp đồng đã hết hạn sẽ bị xếp nhầm thành sắp hết hạn. Trong dữ liệu, hợp đồng 12 tháng của nhân viên NV07 đã hết hạn từ ngày 31/08/2026 mà chưa được ký lại. Đây chính là tình huống mà một báo cáo theo dõi hợp đồng giúp phòng nhân sự phát hiện sớm.

Bạn tô màu cả dòng theo tình trạng hợp đồng như thế nào?

Khác với Bài 1, Bài 3 yêu cầu tô màu cả dòng thay vì chỉ một ô, nên bạn cần dùng Conditional Formatting với công thức. Bạn làm theo 4 bước:

  1. Bạn chọn toàn bộ bảng làm bài, từ ô A150 đến ô M163.

  2. Bạn vào tab Home, bấm Conditional Formatting, chọn New Rule rồi chọn dòng Use a formula to determine which cells to format.

  3. Bạn gõ công thức =$M150="Đã hết hạn", bấm Format để đặt nền đỏ và chữ trắng, rồi bấm OK.

  4. Bạn lặp lại các bước trên với công thức =$M150="Sắp hết hạn" và chọn nền vàng.

Dấu $ đứng trước chữ M giữ cố định cột Tình trạng HĐ, còn số dòng 150 không có dấu $ để Excel tự đổi theo từng dòng. Nhờ vậy, mọi ô trong cùng một dòng đều nhìn vào ô Tình trạng HĐ của chính dòng đó, và cả dòng được tô cùng một màu.

Bài 4: Bạn lập báo cáo biến động nhân sự theo phòng ban như thế nào?

Bài 4 yêu cầu bạn làm gì?

Ban Giám đốc cần một báo cáo nhanh cho 5 phòng ban về số lượng nhân sự, tỷ lệ nghỉ việc, mặt bằng lương và tình hình chuyên cần trong tháng 9/2026. Bạn lấy dữ liệu từ Danh sách nhân viên ở đầu đề và từ Bảng chấm công ở Bài 1. Ngày đầu tháng và Ngày cuối tháng lấy ở Bảng tra cứu của Bài 1.

Bài 4 bài test Excel nhân sự: báo cáo biến động nhân sự theo 5 phòng ban bằng COUNTIFS và AVERAGEIFS
Bài 4: báo cáo nhân sự theo phòng ban, có dòng Tổng cộng và ô Kiểm tra

Bạn đếm biến động nhân sự bằng hàm COUNTIFS như thế nào?

Với mỗi phòng ban, bạn cần đếm 3 nhóm nhân viên. Cả 3 nhóm đều được xác định dựa trên ngày tháng:

  • NV đầu tháng là những người vào làm trước ngày 01/09/2026: =COUNTIFS($D$10:$D$24,$B179,$G$10:$G$24,"<"&$E$40)

  • Tuyển mới là những người có ngày vào làm nằm trong tháng 9. Công thức đặt 2 điều kiện lên cùng cột Ngày vào làm: =COUNTIFS($D$10:$D$24,$B179,$G$10:$G$24,">="&$E$40,$G$10:$G$24,"<="&$E$41)

  • Nghỉ việc là những người có Ngày làm việc cuối cùng nằm trong tháng 9. Công thức giống cột Tuyển mới, bạn chỉ đổi vùng điều kiện sang cột H.

Số nhân viên cuối tháng bằng số đầu tháng cộng số tuyển mới, rồi trừ số nghỉ việc. Tỷ lệ nghỉ việc bằng số người nghỉ việc chia cho số nhân viên đầu tháng, và bạn định dạng ô theo kiểu phần trăm. Phòng Kinh doanh có 5 nhân viên đầu tháng và 1 người nghỉ việc, nên tỷ lệ nghỉ việc của phòng là 20%.

Trong thực tế, tỷ lệ nghỉ việc có nhiều cách tính, ví dụ có công ty chia cho số nhân viên bình quân trong kỳ. Khi làm bài test, bạn chỉ cần tính đúng theo định nghĩa mà đề đưa ra.

Bạn tính tổng lương và lương bình quân bằng SUMIFS, AVERAGEIFS như thế nào?

Đề chỉ tính lương của những người còn làm việc vào cuối tháng, tức là những người có ô Ngày làm việc cuối cùng để trống. Trong hàm SUMIFS và AVERAGEIFS, điều kiện "" có nghĩa là ô trống:

=SUMIFS($I$10:$I$24,$D$10:$D$24,$B179,$H$10:$H$24,"")

=AVERAGEIFS($I$10:$I$24,$D$10:$D$24,$B179,$H$10:$H$24,"")

Hàm AVERAGEIFS có cú pháp giống hệt SUMIFS, chỉ khác là hàm tính trung bình thay vì tính tổng. Phòng Kinh doanh có 4 người còn làm việc với tổng lương cơ bản 76.000.000, nên lương cơ bản bình quân của phòng là 19.000.000.

Vì sao dòng Tổng cộng không được lấy trung bình của cột?

Đây là cái bẫy dễ mắc nhất của Bài 4. Nếu bạn lấy trung bình của 5 con số lương bình quân của 5 phòng ban, bạn sẽ được khoảng 35,4 triệu. Con số này sai, vì Ban Giám đốc chỉ có 1 người với mức lương 90 triệu, nhưng lại được tính nặng ngang phòng Kinh doanh có 4 người. Lương bình quân đúng của cả công ty phải lấy tổng lương cơ bản (377.000.000) chia cho tổng số nhân viên cuối tháng (14 người), và bằng 26.928.571.

Tỷ lệ nghỉ việc ở dòng Tổng cộng cũng tính theo cách đó. Bạn lấy tổng số người nghỉ việc chia cho tổng số nhân viên đầu tháng, tức là 1 chia 14, xấp xỉ 7,1%. Bạn không được cộng hay lấy trung bình các tỷ lệ của từng phòng ban.

Bạn đếm số nhân viên đạt chuyên cần và kiểm tra báo cáo như thế nào?

Cột Số NV đạt chuyên cần dùng COUNTIFS trên Bảng chấm công ở Bài 1, với 2 điều kiện là phòng ban và kết quả "Đạt": =COUNTIFS($D$69:$D$83,$B179,$M$69:$M$83,"Đạt"). Cột này lấy kết quả của Bài 1, nên cột chỉ ra đúng số khi bạn đã làm xong Bài 1.

Ô Kiểm tra cuối bài so sánh tổng số nhân viên cuối tháng với số người có ô Ngày làm việc cuối cùng để trống trong danh sách nhân viên: =IF(F184=COUNTIFS($H$10:$H$24,""),"Khớp","Lệch"). Nếu các công thức đếm của bạn đúng, 2 con số này cùng bằng 14 và ô Kiểm tra báo "Khớp".

Bạn tự chấm bài test Excel nhân sự và rút kinh nghiệm như thế nào?

  • Bạn nên bấm giờ 90 phút như khi thi thật, làm bài trên sheet Đề bài và chưa mở sheet Kết quả.

  • Bạn nên làm phần chắc tay trước và để lại những yêu cầu còn vướng. Cách làm này giúp bạn không bị hết giờ khi mới làm xong một nửa đề.

  • Bạn so từng ô với sheet Kết quả. Khi thấy ô nào lệch, bạn bấm vào ô đáp án tương ứng và đọc công thức trên thanh công thức để tìm ra bước mình làm sai. Phím tắt Ctrl + ` (phím nằm bên trái số 1) hiện toàn bộ công thức trên sheet, nhấn lại lần nữa để trở về như cũ.

  • Bạn tận dụng các ô Kiểm tra. Bài 1 và Bài 4 đều có ô Kiểm tra. Ô báo "Khớp" là dấu hiệu tốt, còn ô báo "Lệch" cho biết bạn còn sót hoặc tính trùng ở đâu đó.

  • Bạn kéo công thức xuống cả cột và dò lại các dòng đặc biệt như NV08, NV09, NV13. Đề yêu cầu công thức sao chép được, nên một công thức chỉ đúng ở dòng đầu là chưa đạt.

  • Vài ngày sau, bạn làm lại từ đầu mà không xem đáp án. Chỗ nào bạn vẫn còn bí là chỗ bạn cần ôn thêm.

Nếu bạn muốn luyện thêm, Nghề Excel cũng có bộ đề phỏng vấn Excel cho vị trí kế toán với cùng cách trình bày. Nhiều hàm như SUMIFS, COUNTIFS, VLOOKUP xuất hiện ở cả 2 bộ đề, nên bạn có thể dùng bộ đề kế toán để luyện thêm các hàm này.

Bạn cần lưu ý gì về số liệu trong đề?

Toàn bộ số liệu trong đề là giả định để luyện tập. Mức lương tối đa đóng bảo hiểm, mức giảm trừ gia cảnh và biểu thuế 5 bậc trong Bảng tra cứu chỉ dùng để làm bài. Khi tính lương thật, bạn cần cập nhật các con số này theo quy định hiện hành tại thời điểm tính. Một số quy định nghiệp vụ trong đề cũng đã được đơn giản hóa để tập trung đánh giá kỹ năng Excel, ví dụ đề chưa tính khoản tiền chế độ ốm đau do BHXH chi trả.

Bộ đề đặt mọi tham số trong Bảng tra cứu thay vì viết thẳng con số vào công thức. Khi quy định thay đổi, bạn chỉ cần sửa con số trong ô tham số, các công thức sẽ tự tính lại. Đây cũng là thói quen tốt bạn nên áp dụng khi làm bảng lương thật ở công ty.

Tải bộ đề tại đây: Bộ đề Excel nhân sự HR có lời giải (file Excel trên OneDrive)

Bạn muốn luyện Excel bài bản hơn?

Nếu bạn làm xong bộ đề mà vẫn còn vướng nhiều chỗ, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel. Khóa học dành cho cả người chưa biết gì về Excel lẫn người đang loay hoay với Excel ở công ty. Sau khóa học, bạn sẽ tự tin dùng Excel thành thạo như người đã đi làm 3-4 năm.

Bạn đạt được gì sau khóa học?

  • Bạn tự phân tích được đề bài. Khi gặp một công việc Excel, bạn chọn đúng hàm phù hợp và kết hợp được nhiều hàm trong cùng một công thức.

  • Bạn phân tích và xử lý được dữ liệu lớn, từ hàng chục nghìn dòng trở lên.

  • Bạn xây dựng được bảng biểu và Dashboard tự động. Nhờ vậy, bạn hạn chế được việc cập nhật thủ công mất thời gian.

  • Bạn dùng AI đúng cách để nhân năng suất của chính mình lên x2, x3.

Khóa học đồng hành cùng bạn như thế nào?

  • Khóa học đầu tư hẳn 30 buổi luyện tập. Bạn luyện tập tới khi dùng được Excel cho công việc.

  • Coach đồng hành cùng bạn. Coach làm bài tập cùng bạn trong từng buổi.

  • Khóa học có hơn 500 bài tập thực tế. Các bài tập này được lấy từ doanh nghiệp.

Trước khi quyết định, bạn có thể đọc cảm nhận của các học viên đã học khóa này.

Xem chi tiết khóa học: 30 ngày học và thực hành Excel

Nghề Excel chúc bạn luyện tốt và tự tin trong buổi phỏng vấn nhân sự sắp tới.