Có thể bạn quan tâm: Bảng Tra Thép Hình Excel: Hướng Dẫn Tạo, Sử Dụng Và Tối Ưu Hoá Để Quản Lý Dữ Liệu Kỹ Thuật Hiệu Quả
1. Giới thiệu chung
Trong môi trường doanh nghiệp hiện đại, việc tính toán và quản lý bảng lương là một trong những công việc quan trọng, ảnh hưởng trực tiếp đến tinh thần làm việc và sự hài lòng của nhân viên. Excel, với tính năng linh hoạt, khả năng xử lý dữ liệu lớn và các công cụ phân tích mạnh mẽ, đã trở thành lựa chọn hàng đầu cho nhiều doanh nghiệp, đặc biệt là các doanh nghiệp vừa và nhỏ, trong việc xây dựng và tự động hoá bảng lương.
Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từng bước một, từ việc chuẩn bị dữ liệu, xây dựng công thức tính lương cơ bản, xử lý các khoản phụ cấp, khấu trừ, tới việc tạo báo cáo tổng hợp và in ấn. Ngoài ra, chúng tôi cũng sẽ giới thiệu một số mẹo nâng cao giúp bạn tối ưu hoá quy trình tính lương, giảm thiểu lỗi và tiết kiệm thời gian.
Lưu ý: Bài viết này hướng tới người mới bắt đầu nhưng cũng chứa những kỹ thuật nâng cao dành cho người đã có kinh nghiệm. Bạn có thể đọc từ đầu tới cuối hoặc chỉ tập trung vào các phần bạn cần.
Có thể bạn quan tâm: Mẫu Bảng Thanh Toán Tiền Lương Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
2. Chuẩn bị dữ liệu đầu vào
2.1. Xác định các thành phần lương cơ bản
Trước khi tạo bảng tính, bạn cần liệt kê rõ ràng các thành phần sẽ có trong bảng lương, bao gồm:
| Thành phần | Mô tả | Loại dữ liệu |
|---|---|---|
| Mã nhân viên | Mã định danh duy nhất cho mỗi nhân viên | Văn bản (text) |
| Họ và tên | Thông tin cá nhân | Văn bản |
| Chức vụ | Vị trí công việc | Văn bản |
| Phòng ban | Đơn vị làm việc | Văn bản |
| Lương cơ bản | Mức lương cố định hàng tháng | Số tiền |
| Hệ số lương | Tỷ lệ tính lương (nếu có) | Số thập phân |
| Số ngày làm việc | Số ngày thực tế làm trong tháng | Số nguyên |
| Số ngày nghỉ có phép | Số ngày nghỉ được trả lương | Số nguyên |
| Phụ cấp ăn trưa | Khoản trợ cấp ăn uống | Số tiền |
| Phụ cấp đi lại | Khoản trợ cấp đi lại | Số tiền |
| Thưởng | Thưởng tháng, thưởng dự án … | Số tiền |
| Các khoản khấu trừ | Bảo hiểm, thuế thu nhập cá nhân, tạm ứng … | Số tiền |
| Tổng lương | Tổng thu nhập trước khấu trừ | Tự tính |
| Lương thực nhận | Thu nhập sau khấu trừ | Tự tính |
2.2. Thu thập dữ liệu nhân sự
Bạn có thể lấy dữ liệu này từ:
- Hệ thống HRM: Xuất file CSV hoặc Excel.
- Sổ sách giấy: Nhập thủ công (cần kiểm tra kỹ lưỡng để tránh lỗi).
- Form Google: Thu thập nhanh từ nhân viên và tự động đồng bộ sang Google Sheets → Excel.
2.3. Định dạng bảng trong Excel
- Tạo một sheet mới và đặt tên “Bảng Lương”.
- Dòng tiêu đề (Row 1) nhập các cột như trong bảng ở mục 2.1.
- Định dạng cột:
- Cột
Mã nhân viên,Họ và tên,Chức vụ,Phòng ban→ Text. - Cột
Lương cơ bản,Hệ số lương,Phụ cấp,Thưởng,Khấu trừ→ Currency (định dạng tiền tệ). - Cột
Số ngày làm việc,Số ngày nghỉ có phép→ Number (không thập phân). - Bảo vệ sheet: Đánh dấu “Protect Sheet” để tránh người dùng vô tình thay đổi công thức.
Có thể bạn quan tâm: Cách Chuyển Bảng Mã Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
3. Xây dựng công thức tính lương cơ bản
3.1. Công thức tính Lương Cơ Bản (LB)
Giả sử:
- Lương cơ bản (cột
E) là mức lương cố định. - Hệ số lương (cột
F) được áp dụng nếu công ty có thang lương dựa trên hệ số.
Công thức (ô G2 – cột Lương thực tế):
=E2F2
Nếu không dùng hệ số, chỉ cần
=E2.
3.2. Tính Lương theo số ngày làm việc
Nếu lương được tính theo ngày công (đối với nhân viên thời vụ), công thức:
=E2/30 D2
Trong đó:
– E2 = Lương cơ bản tháng.
– 30 = Số ngày trong tháng chuẩn (có thể dùng hàm DAY(EOMONTH(TODAY(),0)) để tự động lấy số ngày thực tế của tháng hiện tại).
– D2 = Số ngày làm việc thực tế.
3.3. Tính phụ cấp và thưởng
Giả sử:
- Phụ cấp ăn trưa (cột
H) và phụ cấp đi lại (cộtI) là cố định. - Thưởng (cột
J) có thể là phần trăm của lương cơ bản hoặc số tiền cố định.
Công thức tổng phụ cấp (ô K2 – cột Tổng phụ cấp):
=H2 + I2 + J2
Nếu thưởng là % lương cơ bản (ví dụ 10%):
=H2 + I2 + (E210%)
3.4. Tính tổng thu nhập trước khấu trừ
Ô L2 (cột Tổng thu nhập):
=G2 + K2
Trong đó:
– G2 = Lương thực tế (cơ bản hệ số hoặc ngày công).
– K2 = Tổng phụ cấp và thưởng.
4. Xử lý các khoản khấu trừ
4.1. Bảo hiểm xã hội, y tế, thất nghiệp
Các mức bảo hiểm thường tính theo % lương cơ bản, ví dụ:
- BHXH: 8%
- BHYT: 1.5%
- BHTN: 1%
Công thức (ô M2 – cột Bảo hiểm):
=E2 (8% + 1.5% + 1%)
Hoặc tách từng mục vào cột riêng để có chi tiết.
4.2. Thuế thu nhập cá nhân (TNCN)
Thuế TNCN ở Việt Nam tính theo bậc lũy tiến. Để đơn giản, ta có thể dùng công thức giả lập:
=IF(L2<=5000000, L25%, IF(L2<=10000000, L210% - 250000, IF(L2<=18000000, L215% - 750000, IF(L2<=32000000, L220% - 1650000, IF(L2<=52000000, L225% - 3250000, IF(L2<=80000000, L230% - 5850000, L235% - 9850000))))))
Lưu ý: Công thức trên chưa tính các giảm trừ gia cảnh, bảo hiểm xã hội đã trừ. Bạn có thể mở rộng bằng cách tạo bảng bậc thuế và dùng hàm
VLOOKUP.
4.3. Các khoản khấu trừ khác
- Tạm ứng (cột
N): nhập số tiền đã tạm ứng. - Nợ cá nhân (cột
O): nếu có.
4.4. Tổng khấu trừ
Ô P2 (cột Tổng khấu trừ):
= M2 + N2 + O2
(Nếu bao gồm cả thuế TNCN, cần cộng thêm công thức thuế.)
4.5. Lương thực nhận
Ô Q2 (cột Lương thực nhận):
= L2 - P2
Sau khi nhập công thức ở hàng 2, kéo công thức xuống hết danh sách nhân viên (double-click vào góc dưới bên phải của ô).
Có thể bạn quan tâm: Bảng Tính Giá Tour Excel: Cách Tạo, Sử Dụng Và Tối Ưu Hóa Để Quản Lý Kinh Doanh Du Lịch Hiệu Quả
5. Tự động hoá với bảng tham chiếu (Lookup Tables)
5.1. Tạo bảng hệ số lương
| Chức vụ | Hệ số |
|---|---|
| Nhân viên | 1 |
| Trưởng phòng | 1.5 |
| Giám đốc | 2.5 |
- Đặt tên cho bảng này là
tbl_Heso. - Sử dụng công thức
VLOOKUPđể lấy hệ số dựa trên chức vụ:
=VLOOKUP(C2, tbl_Heso, 2, FALSE)
Công thức này thay thế nhập trực tiếp hệ số trong cột
F.
5.2. Bảng mức phụ cấp
Nếu phụ cấp ăn trưa và đi lại thay đổi theo phòng ban, tạo bảng:
| Phòng ban | Phụ cấp ăn trưa | Phụ cấp đi lại |
|---|---|---|
Đặt tên tbl_PhuCap. Công thức:
=VLOOKUP(D2, tbl_PhuCap, 2, FALSE) // cho ăn trưa
=VLOOKUP(D2, tbl_PhuCap, 3, FALSE) // cho đi lại
5.3. Bảng mức thuế
Sử dụng bảng bậc thuế (cột Mức lương, Thuế %, Giảm trừ), rồi dùng hàm LOOKUP hoặc INDEX/MATCH để lấy thuế tương ứng.
6. Tạo báo cáo tổng hợp
6.1. Tổng lương theo phòng ban
Sử dụng PivotTable:
- Chọn toàn bộ dữ liệu.
- Vào
Insert > PivotTable. - Kéo
Phòng banvàoRows. - Kéo
Lương thực nhậnvàoValues→Sum. - Định dạng tiền tệ.
6.2. Báo cáo chi phí lương theo tháng
Nếu bạn có nhiều tháng trong một file, tạo cột Tháng (ví dụ “2026-07”). Sau đó:
- Dùng PivotChart để hiển thị xu hướng chi phí lương.
- Hoặc dùng hàm
SUMIFS:
=SUMIFS(Q:Q, R:R, "2026-07")
(Trong đó R là cột Tháng.)
6.3. In phiếu lương cho từng nhân viên
Tạo Mail Merge với Word:
- Lưu bảng lương dưới dạng
CSV. - Mở Word →
Mailings > Start Mail Merge > Labels. - Chọn mẫu phiếu lương, chèn trường dữ liệu (Tên, Lương cơ bản, Phụ cấp, Khấu trừ, Lương thực nhận).
- Thực hiện
Finish & Merge.
7. Bảo mật và kiểm soát truy cập
- Mật khẩu bảo vệ sheet:
Review > Protect Sheet. - Mật khẩu mở workbook:
File > Info > Protect Workbook. - Ẩn cột/row chứa công thức nhạy cảm.
- Sử dụng Data Validation để ngăn nhập dữ liệu sai (ví dụ: chỉ cho phép nhập số dương trong cột ngày làm việc).
8. Mẹo nâng cao và tối ưu hoá
8.1. Sử dụng Power Query để nhập dữ liệu tự động
Nếu dữ liệu nhân viên được lưu trên hệ thống ERP, bạn có thể:
- Vào
Data > Get Data > From Database/From Web. - Thiết lập truy vấn để lấy bảng nhân viên.
- Tự động cập nhật khi dữ liệu nguồn thay đổi (Refresh).
8.2. Sử dụng Macro (VBA) để tự động tính lương
Sub TinhBangLuong() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Bảng Lương") Dim lastRow As Long lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row Dim i As Long For i = 2 To lastRow With ws .Cells(i, "G").Value = .Cells(i, "E").Value .Cells(i, "F").Value 'Lương cơ bản .Cells(i, "K").Value = .Cells(i, "H").Value + .Cells(i, "I").Value + .Cells(i, "J").Value .Cells(i, "L").Value = .Cells(i, "G").Value + .Cells(i, "K").Value .Cells(i, "M").Value = .Cells(i, "E").Value 0.105 'Bảo hiểm tổng 10.5% .Cells(i, "P").Value = .Cells(i, "M").Value + .Cells(i, "N").Value + .Cells(i, "O").Value .Cells(i, "Q").Value = .Cells(i, "L").Value - .Cells(i, "P").Value End With Next i MsgBox "Tính lương hoàn tất!", vbInformation
End Sub
- Gán macro này cho nút “Tính Lương” trên sheet.
- Lưu file dưới dạng
.xlsmđể giữ macro.
8.3. Kiểm tra lỗi tự động (Conditional Formatting)
- Đánh dấu số ngày làm việc > 31 hoặc < 0:
Home > Conditional Formatting > New Rule > Use a formula.- Công thức:
=OR(D2>31, D2<0). Đặt màu nền đỏ.
Đánh dấu lương thực nhận < 0 (có lỗi tính):
- Công thức:
=Q2<0.
8.4. Sử dụng Data Tables để mô phỏng kịch bản
Nếu muốn thử nghiệm ảnh hưởng của thay đổi mức phụ cấp hoặc thuế, tạo Data Table:
- Đặt ô
X1là mức phụ cấp (ví dụ 500,000). - Đặt ô
Y1là công thức=SUMIF(...)tính tổng lương thực nhận. - Chọn vùng
X1:Y10→Data > What-If Analysis > Data Table. - Xác định
Column Input Celllà ô phụ cấp trong bảng gốc.
Kết quả sẽ cho bạn bảng mô phỏng nhanh.
9. Kiểm tra, xác nhận và xuất báo cáo
9.1. Kiểm tra tổng số tiền chi trả
=SUM(Q:Q) // Tổng lương thực nhận
So sánh với ngân sách lương đã duyệt.
9.2. Kiểm tra tính đồng nhất
- Kiểm tra trùng lặp mã nhân viên:
Conditional Formatting > Duplicate Values. - Kiểm tra ngày làm việc:
=COUNTIF(D:D,">31")→ phải bằng 0.
9.3. Xuất file
- PDF:
File > Export > Create PDF/XPS. - CSV:
File > Save As > CSV (Comma delimited). Dùng CSV để import vào phần mềm kế toán.
10. Tổng kết
Việc tính bảng lương bằng Excel không chỉ giúp doanh nghiệp giảm chi phí phần mềm mà còn cho phép tùy biến linh hoạt theo quy trình và chính sách riêng. Bằng cách:
- Chuẩn bị dữ liệu đầy đủ và chuẩn hoá.
- Xây dựng công thức chính xác cho lương cơ bản, phụ cấp, khấu trừ và thuế.
- Sử dụng bảng tham chiếu (lookup) để giảm nhập liệu thủ công.
- Tự động hoá bằng PivotTable, Power Query, Macro.
- Bảo mật và kiểm tra lỗi trước khi xuất báo cáo.
Bạn sẽ có một hệ thống tính lương vừa chính xác, vừa nhanh chóng, đồng thời dễ dàng mở rộng khi doanh nghiệp phát triển.
Nếu bạn mới bắt đầu, hãy bắt đầu với các công thức cơ bản, sau đó dần dần tích hợp các tính năng nâng cao như Power Query và VBA. Đừng quên thường xuyên sao lưu file và kiểm tra tính toàn vẹn của dữ liệu.
Chúc bạn thành công trong việc xây dựng và quản lý bảng lươ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ẻ.