Trong môi trường doanh nghiệp hiện đại, việc quản lý tiền lương và thưởng cho nhân viên không chỉ là một công việc hành chính mà còn là yếu tố then chốt giúp duy trì động lực làm việc, tăng năng suất và giữ chân nhân tài. Excel, với tính linh hoạt và khả năng tùy biến mạnh mẽ, đã trở thành công cụ “đắc lực” cho các bộ phận nhân sự và kế toán trong việc xây dựng các bảng tính lương thưởng. Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, toàn diện về cách sử dụng các hàm trong Excel để tính lương thưởng, bao gồm:
- Giới thiệu tổng quan về quy trình tính lương thưởng
- Các hàm cơ bản trong Excel hỗ trợ tính lương
- Xây dựng bảng lương cơ bản với các công thức
- Tính thưởng dựa trên hiệu suất, doanh số, và KPI
- Sử dụng hàm IF, VLOOKUP, INDEX-MATCH, SUMIFS, COUNTIFS
- Áp dụng hàm XLOOKUP và FILTER (Excel 365)
- Tự động cập nhật dữ liệu từ nguồn ngoài (Power Query)
- Bảo mật và chia sẻ bảng lương
- Mẹo tối ưu hoá hiệu suất và giảm lỗi
- Ví dụ thực tế: Mẫu bảng lương tháng và năm
Có thể bạn quan tâm: Hàm Giai Thừa Trong Excel: Cách Sử Dụng, Ứng Dụng Thực Tế Và Một Số Mẹo Tiên Tiến
1. Giới thiệu tổng quan về quy trình tính lương thưởng
Trước khi đi vào chi tiết công thức, bạn cần hiểu rõ quy trình tính lương thường bao gồm các bước sau:
| Bước | Nội dung | Mô tả |
|---|---|---|
| 1 | Thu thập dữ liệu nhân sự | Thông tin cá nhân, vị trí, mức lương cơ bản, ngày công, ngày nghỉ, phụ cấp |
| 2 | Xác định tiêu chí thưởng | Doanh số, KPI, dự án hoàn thành, thâm niên, chất lượng công việc |
| 3 | Thu thập dữ liệu thực tế | Doanh số bán hàng, thời gian làm việc, số ngày nghỉ, … |
| 4 | Áp dụng công thức tính | Sử dụng các hàm Excel để tính lương cơ bản + thưởng – các khoản khấu trừ |
| 5 | Kiểm tra, xác nhận | So sánh với bảng lương mẫu, kiểm tra lỗi công thức |
| 6 | Xuất báo cáo | PDF, in ấn, hoặc gửi qua email cho nhân viên |
Việc chuẩn hoá quy trình sẽ giúp bạn thiết kế bảng tính một cách logic, giảm thiểu lỗi và tăng tính tự động hoá.
Có thể bạn quan tâm: Hàm Bao Gồm Trong Excel: Tất Cả Những Bạn Cần Biết Để Tối Ưu Hoá Công Việc Xử Lý Dữ Liệu
2. Các hàm cơ bản trong Excel hỗ trợ tính lương
| Hàm | Mô tả | Ứng dụng trong tính lương |
|---|---|---|
| SUM | Cộng các giá trị | Tính tổng tiền lương cơ bản, thưởng |
| IF | Điều kiện logic | Xác định mức thưởng dựa trên tiêu chí |
| VLOOKUP | Tìm kiếm dọc | Lấy mức lương cơ bản từ bảng lương chuẩn |
| HLOOKUP | Tìm kiếm ngang | Tìm các hệ số phụ cấp theo cấp bậc |
| INDEX-MATCH | Tìm kiếm linh hoạt | Thay thế VLOOKUP khi cần tìm cả bên trái |
| SUMIF / SUMIFS | Cộng có điều kiện | Tính tổng thưởng theo phòng ban, dự án |
| COUNTIF / COUNTIFS | Đếm có điều kiện | Đếm số ngày làm việc, số lần đạt KPI |
| ROUND, ROUNDUP, ROUNDDOWN | Làm tròn số | Đảm bảo tiền lương làm tròn tới đồng hoặc nghìn đồng |
| TEXT | Định dạng văn bản | Chuyển số thành chuỗi “đồng” để in báo cáo |
| DATE, YEAR, MONTH, DAY | Xử lý ngày tháng | Tính số ngày làm việc trong tháng |
Có thể bạn quan tâm: Hoán Đổi Cột Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
3. Xây dựng bảng lương cơ bản với các công thức
3.1. Cấu trúc bảng lương mẫu
| A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|
| Mã NV | Tên NV | Chức vụ | Lương cơ bản | Hệ số phụ cấp | Ngày công | Ngày nghỉ | Lương thực nhận | Thưởng | Tổng cộng |
| 001 | Nguyễn Văn A | Kế toán | 8,000,000 | 1.2 | 22 | 2 | =D2E2(F2/(F2+G2)) | =IF(H2>20,500000,0) | =H2+I2 |
| … | … | … | … | … | … | … | … | … | … |
3.2. Công thức chi tiết
- Lương thực nhận (cột H)
excel =D2E2(F2/(F2+G2)) D2= Lương cơ bảnE2= Hệ số phụ cấp (ví dụ 1.2)F2= Ngày công (số ngày làm việc trong tháng)G2= Ngày nghỉ (nghỉ có phép, không tính lương)Thưởng (cột I) – Ví dụ thưởng 500,000 nếu ngày công > 20:
excel =IF(F2>20,500000,0)Tổng cộng (cột J)
excel =H2+I2
Khi sao chép công thức xuống các dòng, Excel sẽ tự động cập nhật tham chiếu ô.
4. Tính thưởng dựa trên hiệu suất, doanh số và KPI
Thưởng thường phụ thuộc vào nhiều tiêu chí, ví dụ:
- Thưởng doanh số: % trên doanh số đạt được.
- Thưởng KPI: dựa trên điểm KPI (0‑100).
- Thưởng thâm niên: theo số năm công tác.
4.1. Thưởng doanh số
Giả sử doanh số tháng của mỗi nhân viên nằm ở cột K và mức thưởng là 2% doanh số nếu > 100 triệu:
=IF(K2>100000000, K20.02, 0)
4.2. Thưởng KPI
Nếu KPI (cột L) được chấm 0‑100, và công ty quy định:
| KPI | Phần thưởng |
|---|---|
| 90‑100 | 10% lương cơ bản |
| 80‑89 | 5% lương cơ bản |
| <80 | 0 |
Công thức sử dụng IFS (Excel 2026 trở lên) hoặc lồng IF:
=IFS( L2>=90, D20.10, L2>=80, D20.05, TRUE, 0
)
4.3. Thưởng thâm niên
Giả sử số năm công tác nằm ở cột M, thưởng 0.5% lương cơ bản cho mỗi năm, tối đa 10%:
=MIN(M20.005,0.10)D2
4.4. Tổng thưởng (cột N)
Kết hợp các thành phần:
=IF(K2>100000000, K20.02, 0) // doanh số
+ IFS(L2>=90, D20.10, L2>=80, D20.05, TRUE, 0) // KPI
+ MIN(M20.005,0.10)D2 // thâm niên
5. Sử dụng hàm IF, VLOOKUP, INDEX-MATCH, SUMIFS, COUNTIFS
5.1. IF lồng nhau
Khi có nhiều tiêu chí phức tạp, dùng IF lồng:
=IF(F2>22, "Làm quá giờ", IF(F2<20, "Thiếu ngày công", "Bình thường"))
5.2. VLOOKUP để lấy mức lương chuẩn
Giả sử bảng “Bảng chuẩn lương” nằm trong sheet SalaryTable:
| A | B |
|---|---|
| Chức vụ | Mức lương |
| Kế toán | 8,000,000 |
| Kế toán trưởng | 12,000,000 |
| … | … |
Công thức lấy mức lương cho nhân viên ở cột C (chức vụ):
=VLOOKUP(C2, SalaryTable!$A$2:$B$50, 2, FALSE)
5.3. INDEX-MATCH – thay thế VLOOKUP
Có thể bạn quan tâm: Tổng Hợp Các Hàm Trong Excel: Hướng Dẫn Toàn Diện Cho Người Dùng Mới Và Nâng Cao
Nếu bảng chuẩn có nhiều cột và bạn muốn trả về cột không nằm ở bên phải:
=INDEX(SalaryTable!$B$2:$B$50, MATCH(C2, SalaryTable!$A$2:$A$50, 0))
5.4. SUMIFS – tính tổng thưởng theo phòng ban
Giả sử cột D là “Phòng ban”, cột N là “Tổng thưởng”. Để tính tổng thưởng cho phòng “Kế toán”:
=SUMIFS(N:N, D:D, "Kế toán")
5.5. COUNTIFS – đếm nhân viên đạt KPI > 90
=COUNTIFS(L:L, ">90")
6. Áp dụng hàm XLOOKUP và FILTER (Excel 365)
Excel 365 cung cấp các hàm mới mạnh mẽ, giúp giảm độ phức tạp.
6.1. XLOOKUP – tìm kiếm đa chiều
=XLOOKUP(C2, SalaryTable!$A$2:$A$50, SalaryTable!$B$2:$B$50, "Không tìm thấy", 0, 1)
- Tham số cuối cùng
1cho phép tìm kiếm gần nhất (khi không có giá trị chính xác).
6.2. FILTER – trích xuất danh sách nhân viên có lương > 10 triệu
=FILTER(A2:J100, H2:H100>10000000, "Không có dữ liệu")
Kết quả là một mảng động tự động mở rộng, rất hữu ích khi tạo báo cáo “Top 10 lương cao”.
7. Tự động cập nhật dữ liệu từ nguồn ngoài (Power Query)
Khi dữ liệu nhân sự, doanh số được lưu trong các file CSV, hệ thống ERP hoặc cơ sở dữ liệu SQL, Power Query (Get & Transform) cho phép:
- Kết nối: Data → Get Data → From File / From Database.
- Biến đổi: Loại bỏ cột không cần, đổi kiểu dữ liệu, tính toán cột mới (thêm “Thưởng”).
- Load: Đưa dữ liệu đã xử lý vào một sheet “Data”.
Sau khi thiết lập, mỗi khi file nguồn thay đổi, chỉ cần nhấn Refresh là bảng lương sẽ tự động cập nhật.
8. Bảo mật và chia sẻ bảng lương
Bảng lương là thông tin nhạy cảm, cần:
- Mật khẩu bảo vệ sheet: Review → Protect Sheet → Đặt mật khẩu.
- Ẩn cột/ô chứa công thức: Format → Hide & Unhide → Hide Columns.
- Sử dụng Excel Rights Management (nếu có) để giới hạn quyền đọc/viết.
- Xuất PDF có mật khẩu khi gửi cho nhân viên.
9. Mẹo tối ưu hoá hiệu suất và giảm lỗi
| Mẹo | Lý do |
|---|---|
| Sử dụng Table (Ctrl+T) | Khi dữ liệu thay đổi, công thức tự mở rộng, tránh lỗi #REF!. |
| Tránh volatile functions (NOW(), TODAY(), OFFSET) | Chúng làm bảng tính chậm khi dữ liệu lớn. |
| Kiểm tra lỗi bằng Conditional Formatting | Đánh dấu ô #DIV/0! hoặc ô có giá trị bất thường. |
| Sử dụng Data Validation | Giới hạn nhập dữ liệu (ví dụ danh sách chức vụ). |
| Tách các công thức tính lương và thưởng ra các sheet riêng | Giúp quản lý, bảo trì dễ dàng. |
| Sử dụng named ranges | Dễ đọc hơn (ví dụ BaseSalary, BonusRate). |
10. Ví dụ thực tế: Mẫu bảng lương tháng và năm
10.1. Bảng lương tháng (Sheet “Salary_Month”)
| A | B | C | D | E | F | G | H | I | J | K | L | M | N |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Mã NV | Tên NV | Chức vụ | Lương cơ bản | Hệ số phụ cấp | Ngày công | Ngày nghỉ | Lương thực nhận | Doanh số | KPI | Thâm niên | Thưởng | Khấu trừ | Thực nhận |
| 001 | Nguyễn Văn A | Kế toán | =VLOOKUP(C2,SalaryTable!$A$2:$B$50,2,FALSE) | 1.2 | 22 | 2 | =D2E2(F2/(F2+G2)) | 120,000,000 | 95 | 5 | =IF(K2>100000000,K20.02,0)+IFS(L2>=90,D20.10,L2>=80,D20.05,TRUE,0)+MIN(M20.005,0.10)D2 | 200,000 | =H2+I2-J2 |
| … | … | … | … | … | … | … | … | … | … | … | … | … | … |
- Cột “Khấu trừ” có thể bao gồm bảo hiểm, thuế, vay tạm ứng… tính bằng công thức
=ROUND(H20.105,0)(ví dụ 10.5% tổng lương). - Cột “Thực nhận” là tổng lương + thưởng – khấu trừ.
10.2. Bảng lương năm (Sheet “Salary_Year”)
Đối với báo cáo tổng hợp năm, dùng PivotTable:
- Chọn dữ liệu tháng → Insert → PivotTable → New Worksheet.
- Kéo “Tên NV” vào Rows, “Tháng” vào Columns, “Thực nhận” vào Values (Sum).
- Định dạng số tiền, thêm slicer cho “Phòng ban” để lọc nhanh.
PivotTable cho phép bạn:
- Xem tổng lương từng nhân viên trong năm.
- So sánh tăng giảm giữa các tháng.
- Phân tích chi phí lương theo phòng ban.
Kết luận
Việc xây dựng hàm tính lương thưởng trong Excel không chỉ giúp giảm tải công việc thủ công mà còn nâng cao độ chính xác, tính minh bạch và khả năng phân tích dữ liệu nhân sự. Bằng cách:
- Xác định rõ tiêu chí thưởng (doanh số, KPI, thâm niên).
- Áp dụng các hàm cơ bản và nâng cao (IF, VLOOKUP, INDEX-MATCH, SUMIFS, XLOOKUP, FILTER).
- Sử dụng Table, Power Query và PivotTable để tự động hoá và báo cáo.
- Bảo mật, chia sẻ và tối ưu hoá bảng tính.
Bạn sẽ có một công cụ quản lý lương thưởng mạnh mẽ, linh hoạt, đáp ứng nhu cầu của doanh nghiệp dù là quy mô nhỏ hay lớn. Hãy bắt đầu tạo bảng tính ngay hôm nay, áp dụng những công thức trên và tinh chỉnh cho phù hợp với quy trình nội bộ của công ty bạn. Khi đã quen tay, bạn sẽ thấy Excel không chỉ là một “bảng tính” mà còn là một hệ thống quản trị nhân sự toàn diện.
Chúc bạn thành công trong việc triển khai hệ thống tính lương thưởng bằng Excel!
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ẻ.