Bài Test Excel C&b: Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao

1. Giới thiệu chung về Excel trong lĩnh vực C&B

Trong môi trường nhân sự hiện đại, việc quản lý Compensation & Benefits (C&B) – tức là tiền lương và các chế độ phúc lợi – đòi hỏi những công cụ tính toán chính xác, nhanh chóng và linh hoạt. Excel, với khả năng xử lý dữ liệu mạnh mẽ, đa dạng hàm số và tính năng biểu đồ, luôn là “cây bút” không thể thiếu của các chuyên gia C&B. Tuy nhiên, để tận dụng tối đa tiềm năng của Excel, người dùng cần nắm vững một loạt kỹ năng từ cơ bản đến nâng cao, đồng thời biết cách áp dụng chúng vào các bài test thực tế.

Bài viết này sẽ cung cấp bài test Excel C&B chi tiết, bao gồm:

• Các câu hỏi lý thuyết và thực hành.
• Hướng dẫn giải từng bài tập bằng từng bước thao tác.
• Các mẹo, thủ thuật giúp tăng tốc độ làm việc.
• Đánh giá mức độ khó và thời gian dự kiến hoàn thành.

Với nội dung trên 1500 chữ, bạn sẽ có một tài liệu tham khảo toàn diện để chuẩn bị cho các kỳ thi nội bộ, phỏng vấn vị trí C&B Analyst, hoặc nâng cao kỹ năng làm việc hằng ngày.

2. Cấu trúc của bài test Excel C&B

2.1. Phân loại câu hỏi

2.2. Định dạng file và môi trường làm việc

• File mẫu: Thường được cung cấp dưới dạng .xlsx hoặc .xlsm (khi có macro).
• Phiên bản Excel: Excel 2026 trở lên, hỗ trợ Power Query và XLOOKUP.
• Môi trường: Máy tính cá nhân, laptop hoặc máy ảo tại trung tâm đào tạo.

3. Bài test Excel C&B – Phần 1: Kiến thức cơ bản

3.1. Câu hỏi 1: Tính tổng lương cơ bản

Yêu cầu: Trong bảng SalaryData, cột B chứa mức lương cơ bản (Basic Salary) của 200 nhân viên. Hãy tính tổng lương cơ bản của toàn công ty.

Cách làm:

1. Chọn ô trống (ví dụ D2).
2. Nhập công thức =SUM(B2:B201).
3. Nhấn Enter.
4. Định dạng ô thành Currency để hiển thị tiền tệ.

Mẹo: Sử dụng AutoSum (Alt + =).

3.2. Câu hỏi 2: Tính trung bình mức lương

Yêu cầu: Tìm mức lương trung bình (Average) và làm tròn đến hàng chục.

Công thức: =ROUND(AVERAGE(B2:B201),-1)

3.3. Câu hỏi 3: Đếm số nhân viên có lương > 15,000,000

Công thức: =COUNTIF(B2:B201,">15000000")

3.4. Câu hỏi 4: Định dạng ngày tháng

Trong cột C là ngày vào làm (Hire Date) dạng dd/mm/yyyy. Yêu cầu hiển thị dưới dạng “Tháng Năm” (MM/YYYY).

Bước: Chọn cột C → Format Cells → Custom → nhập mm/yyyy.

3.5. Câu hỏi 5: Tính thuế thu nhập cá nhân (TNCN) theo công thức:

• Thu nhập chịu thuế = Lương cơ bản – 9,000,000 (miễn thuế)
• Thuế suất: 5% cho phần 0‑5,000,000; 10% cho phần 5,000,001‑10,000,000; 20% cho phần >10,000,000.

Công thức mẫu (ô D2):

=IF(B2<=9000000,0, IF(B2<=14000000,(B2-9000000)5%, IF(B2<=19000000,(50000005%)+(B2-14000000)10%, (50000005%)+(500000010%)+(B2-19000000)20%)))

Kéo công thức xuống toàn cột.

4. Bài test Excel C&B – Phần 2: Hàm logic và xử lý lỗi

4.1. Câu hỏi 6: Xác định nhân viên đủ điều kiện nhận thưởng

• Điều kiện: Thâm niên > 3 năm và Lương cơ bản > 12,000,000.

Công thức (ô E2):

=IF(AND(DATEDIF(C2,TODAY(),"y")>3,B2>12000000),"Đủ","Không")

4.2. Câu hỏi 7: Xử lý lỗi #N/A khi VLOOKUP

Khi tra cứu mức phụ cấp (Allowance) trong bảng AllowanceTable (cột A: Job Title, cột B: Allowance). Nếu không tìm thấy, trả về “0”.

Công thức:

=IFERROR(VLOOKUP(D2,AllowanceTable!$A$2:$B$50,2,FALSE),0)

4.3. Câu hỏi 8: Tính tổng tiền phụ cấp

Giả sử cột F chứa phụ cấp đã tra cứu (theo câu 7). Tổng phụ cấp = SUM(F2:F201).

4.4. Câu hỏi 9: Kiểm tra dữ liệu trùng lặp mã nhân viên

Công thức (ô G2, áp dụng Conditional Formatting):

=COUNTIF($A$2:$A$201,A2)>1

• Chọn cột A → Conditional Formatting → New Rule → Use a formula → nhập công thức trên → đặt màu nền.

5. Bài test Excel C&B – Phần 3: Hàm tra cứu nâng cao

5.1. Câu hỏi 10: Sử dụng INDEX‑MATCH để tra cứu mức lương theo mã nhân viên

Công thức (ô H2):

=INDEX(SalaryData!$B$2:$B$201,MATCH(A2,SalaryData!$A$2:$A$201,0))

5.2. Câu hỏi 11: Xây dựng bảng tính cho 3 năm lương dự kiến

• Các cột: Năm 2026, 2026, 2026.
• Tăng lương hàng năm: 7% + Bonus (nếu có).

Công thức (ô I2 – năm 2026):

=H21.07 + IF(E2="Đủ",5000000,0)

• Kéo sang các cột kế tiếp, thay đổi hệ số tăng lên 1.07 cho mỗi năm.

5.3. Câu hỏi 12: Sử dụng XLOOKUP (Excel 365/2026 trở lên)

=XLOOKUP(A2,SalaryData!$A$2:$A$201,SalaryData!$B$2:$B$201,"Không tìm thấy",0)

5.4. Câu hỏi 13: Tách họ và tên từ ô “Họ và Tên”

Giả sử ô J2 chứa “Nguyễn Văn A”.

• Họ: =LEFT(J2,FIND(" ",J2)-1)
• Tên: =RIGHT(J2,LEN(J2)-FIND("@",SUBSTITUTE(J2," ","@",LEN(J2)-LEN(SUBSTITUTE(J2," ",""))))) (công thức phức tạp, có thể dùng Text to Columns).

6. Bài test Excel C&B – Phần 4: Phân tích dữ liệu với PivotTable

6.1. Câu hỏi 14: Tổng lương theo phòng ban

1. Chọn toàn bộ dữ liệu (A1:H201).
2. Insert → PivotTable → Đặt vào sheet mới.
3. Kéo Department vào Rows, Basic Salary vào Values → Sum.

6.2. Câu hỏi 15: Trung bình phụ cấp theo cấp bậc

• Kéo Job Level vào Rows, Allowance vào Values → Average.

6.3. Câu hỏi 16: Đếm số nhân viên có thu nhập chịu thuế > 20,000,000

• Thêm trường Taxable Income (cột tính ở câu 5).
• Kéo Taxable Income vào Filters, chọn “>20000000”.
• Kéo Employee ID vào Values → Count.

6.4. Mẹo nhanh: Slicer để lọc nhanh

• Insert → Slicer → chọn Department hoặc Job Level.
• Giúp người dùng tương tác trực tiếp khi trình bày báo cáo.

7. Bài test Excel C&B – Phần 5: Power Query (Get & Transform)

7.1. Câu hỏi 17: Gộp dữ liệu lương từ 3 file Excel

1. Data → Get Data → From File → From Workbook → Chọn file 1.
2. Trong Power Query Editor, click Append Queries → Append Queries as New → chọn 3 file.
3. Xóa cột trùng lặp, đổi tên cột thống nhất.
4. Close & Load → Dữ liệu đã gộp vào sheet mới.

7.2. Câu hỏi 18: Loại bỏ các bản ghi có lương = 0

Trong Power Query, Filter cột Basic Salary → Number Filters → Does Not Equal → 0.

7.3. Câu hỏi 19: Thêm cột “Thâm niên” tính bằng năm

• Add Column → Custom Column → công thức:
  = Duration.TotalYears(DateTime.LocalNow() - Hire Date)

Sau khi hoàn thành, Close & Load.

8. Bài test Excel C&B – Phần 6: Tự động hoá với Macro và VBA

8.1. Câu hỏi 20: Tự động tạo báo cáo tổng lương hàng tháng

Bước 1: Ghi macro (Developer → Record Macro) với tên CreateMonthlyReport.

Bước 2: Thực hiện các thao tác:

• Lọc dữ liệu theo tháng (cột Pay Date).
• Tạo PivotTable tổng lương.
• Định dạng tiêu đề, chèn biểu đồ cột.

Bước 3: Dừng ghi macro. Mã VBA mẫu:

Sub CreateMonthlyReport() Dim ws As Worksheet, pt As PivotTable Set ws = Worksheets.Add ws.Name = "Report_" & Format(Date, "yyyymm") 'Copy data Sheets("SalaryData").Range("A1:H201").Copy Destination:=ws.Range("A1") 'Create Pivot Set pt = ws.PivotTables.Add(PivotCache:=ThisWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, SourceData:=ws.Range("A1").CurrentRegion), _ TableDestination:=ws.Range("J3")) With pt .PivotFields("Department").Orientation = xlRowField .PivotFields("Basic Salary").Orientation = xlDataField .PivotFields("Basic Salary").Function = xlSum End With 'Insert Chart Dim ch As ChartObject Set ch = ws.ChartObjects.Add(Left:=300, Top:=10, Width:=500, Height:=300) ch.Chart.SetSourceData Source:=pt.TableRange1 ch.Chart.ChartType = xlColumnClustered
End Sub

8.2. Câu hỏi 21: Kiểm tra và báo cáo lỗi dữ liệu

Macro kiểm tra:

• Lương < 0 → ghi vào file ErrorLog.txt.
• Ngày vào làm > hôm nay → báo lỗi.

Sub ValidateData() Dim ws As Worksheet, r As Long, msg As String Set ws = Worksheets("SalaryData") For r = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row If ws.Cells(r, "B").Value < 0 Then msg = msg & "Row " & r & ": Lương âm" & vbCrLf End If If ws.Cells(r, "C").Value > Date Then msg = msg & "Row " & r & ": Ngày vào làm sau ngày hiện tại" & vbCrLf End If Next r If msg <> "" Then Open ThisWorkbook.Path & "\ErrorLog.txt" For Output As #1 Print #1, msg Close #1 MsgBox "Có lỗi dữ liệu, xem file ErrorLog.txt", vbExclamation Else MsgBox "Dữ liệu hợp lệ", vbInformation End If
End Sub

9. Bài test Excel C&B – Phần 7: Trình bày báo cáo chuyên nghiệp

9.1. Định dạng có điều kiện (Conditional Formatting)

• Mục tiêu: Highlight những nhân viên có thu nhập chịu thuế > 30,000,000.

Bước: Chọn cột Taxable Income → Conditional Formatting → New Rule → Use a formula →
=K2>30000000 → Đặt màu nền Orange.

9.2. Biểu đồ phân bố lương

• Chọn cột Basic Salary → Insert → Histogram.
• Đặt tiêu đề: “Phân bố mức lương cơ bản 2026”.
• Thêm trục phụ Số nhân viên.

9.3. Header & Footer

• Insert → Header & Footer → Thêm logo công ty, ngày tạo báo cáo (=TODAY()), và tên file.

9.4. Bảo vệ sheet

• Review → Protect Sheet → Chỉ cho phép người dùng chỉnh sửa ô dữ liệu nhập (ví dụ cột Allowance), ngăn thay đổi công thức.

10. Đánh giá kết quả và gợi ý cải thiện

Khi đạt 80% trở lên: Bạn đã nắm vững kiến thức Excel C&B và sẵn sàng ứng tuyển vị trí Analyst hoặc Specialist.

Nếu dưới 60%: Hãy tập trung vào:

• Hàm VLOOKUP/XLOOKUP: Thực hành trên dữ liệu thực tế.
• PivotTable: Đọc tài liệu Microsoft hoặc video tutorial.
• Macro: Bắt đầu với ghi macro, sau đó tự viết VBA.

11. Một số mẹo “thủ thuật” giúp tăng năng suất trong công việc C&B

1. Sử dụng Power Pivot: Khi dữ liệu lên hàng nghìn dòng, Power Pivot cho phép tạo mô hình dữ liệu và tính toán nhanh hơn PivotTable truyền thống.
2. Dynamic Arrays (Excel 365): Hàm FILTER, UNIQUE, SORT giúp loại bỏ bước lọc thủ công.
3. Tên vùng (Named Ranges): Đặt tên cho bảng lương (tblSalary) để công thức dễ đọc, bảo trì.
4. Data Validation: Giới hạn nhập dữ liệu (ví dụ, chỉ cho phép nhập mức lương > 0).
5. Shortcut: Ctrl + Shift + L bật/tắt Filter; Ctrl + T tạo Table; Alt + F1 tạo biểu đồ nhanh.
6. Kiểm tra nhanh: Ctrl + (dấu huyền) hiển thị công thức trong ô, giúp debug nhanh.

12. Kết luận

Bài test Excel C&B không chỉ là một bộ câu hỏi kiểm tra kiến thức, mà còn là công cụ đánh giá khả năng áp dụng thực tiễn trong môi trường nhân sự hiện đại. Qua các phần từ kiến thức cơ bản, hàm logic, tra cứu nâng cao, phân tích dữ liệu, đến tự động hoá và trình bày báo cáo, bạn sẽ:

• Nắm vững các hàm Excel quan trọng cho C&B.
• Biết cách xây dựng báo cáo lương, phụ cấp, thuế, và dự báo tăng lương.
• Sử dụng PivotTable, Power Query để xử lý khối lượng dữ liệu lớn.
• Tự động hoá các công việc lặp lại bằng Macro/VBA, giảm thiểu sai sót.
• Trình bày kết quả một cách chuyên nghiệp, đáp ứng yêu cầu của ban lãnh đạo.

Hãy luyện tập thường xuyên, áp dụng những “tips” trên vào dự án thực tế, và bạn sẽ trở thành một chuyên gia Excel C&B thực thụ, sẵn sàng đối mặt với mọi thách thức trong lĩnh vực nhân sự. Chúc bạn thành công!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.