Các công thức tính lương trong Excel – Hướng dẫn chi tiết và đầy đủ

Các công thức tính lương trong Excel là công cụ thiết yếu giúp doanh nghiệp tự động hóa quy trình tính toán tiền lương, tiết kiệm thời gian và giảm thiểu sai sót. Với khả năng xử lý dữ liệu lớn và linh hoạt, Excel trở thành lựa chọn hàng đầu cho các nhà quản lý nhân sự trong việc thiết lập hệ thống lương hiệu quả. Từ các hàm cơ bản đến nâng cao, Excel cung cấp đầy đủ công cụ để xây dựng bảng tính lương tự động, chính xác và phù hợp với đặc thù của từng doanh nghiệp. 1C Việt Nam sẽ hướng dẫn cách áp dụng các công thức tính lương trong Excel hiệu quả nhất cho doanh nghiệp.

>>> ĐỪNG BỎ LỠ: Các công thức tính lương chính xác xác nhất [Cập Nhật 2025]

1. Tổng quan về các công thức tính lương trong Excel

Excel là công cụ phổ biến và hiệu quả trong việc quản lý tiền lương. Trước khi đi vào chi tiết các công thức, hãy tìm hiểu tại sao Excel lại được ưa chuộng và những yếu tố cần chuẩn bị.

1.1. Tại sao nên sử dụng Excel để tính lương?

Việc sử dụng Excel để tính lương mang lại nhiều lợi ích vượt trội so với phương pháp tính toán thủ công truyền thống. Dưới đây là những lý do chính khiến Excel trở thành công cụ được ưa chuộng trong quản lý tiền lương:

• Tiết kiệm thời gian đáng kể: Với các công thức tính lương trong Excel được thiết lập sẵn, việc tính toán cho hàng trăm nhân viên có thể được thực hiện chỉ trong vài phút thay vì nhiều giờ như cách làm thủ công.
• Tự động hóa quy trình: Excel cho phép tự động hóa các bước tính toán lặp đi lặp lại, từ việc tính ngày công, phụ cấp đến khấu trừ thuế và bảo hiểm.
• Giảm thiểu sai sót: Các công thức được thiết lập đúng sẽ đảm bảo tính chính xác cao, hạn chế lỗi do con người gây ra trong quá trình tính toán thủ công.
• Dễ dàng tùy chỉnh: Excel cho phép điều chỉnh bảng tính linh hoạt theo đặc thù của từng doanh nghiệp, từ quy mô nhỏ đến lớn.
• Khả năng lưu trữ và truy xuất dữ liệu: Dễ dàng lưu trữ, tìm kiếm và so sánh dữ liệu lương qua các tháng, quý hoặc năm.

Theo khảo sát của Microsoft vào năm 2023, hơn 78% doanh nghiệp vừa và nhỏ tại Việt Nam sử dụng Excel làm công cụ chính để quản lý tiền lương trước khi chuyển sang các phần mềm chuyên dụng.

1.2. Những yếu tố cần chuẩn bị trước khi tính lương bằng Excel

Để xây dựng một hệ thống tính lương hiệu quả trong Excel, cần chuẩn bị kỹ lưỡng các yếu tố sau:

Bảng chấm công chi tiết:

• Thông tin về ngày công làm việc thực tế của từng nhân viên
• Số giờ làm thêm, làm đêm (nếu có)
• Ngày nghỉ phép, nghỉ ốm, nghỉ không lương
• Thời gian đi muộn, về sớm (nếu áp dụng)

Quy định pháp lý liên quan:

• Mức lương tối thiểu vùng (hiện nay từ 4.420.000đ đến 6.240.000đ tùy vùng)
• Quy định về thuế thu nhập cá nhân (TNCN)
• Tỷ lệ đóng bảo hiểm xã hội (BHXH), bảo hiểm y tế (BHYT), bảo hiểm thất nghiệp (BHTN)
• Quy định về phụ cấp, trợ cấp theo luật lao động

Danh sách nhân viên và thông tin cơ bản:

• Mã nhân viên, họ tên, vị trí công việc
• Mức lương cơ bản, hệ số lương
• Phụ cấp cố định (nếu có)
• Thông tin về người phụ thuộc (để tính giảm trừ gia cảnh)
• Thông tin tài khoản ngân hàng (nếu trả lương qua chuyển khoản)

Quy chế lương của doanh nghiệp:

• Cách tính lương (theo thời gian, sản phẩm, doanh thu...)
• Chính sách thưởng, phạt
• Quy định về tăng ca, làm thêm giờ
• Các khoản phúc lợi khác

Việc chuẩn bị đầy đủ các yếu tố trên sẽ giúp quá trình xây dựng các công thức tính lương trong Excel trở nên thuận lợi và chính xác hơn.

2. Các công thức tính lương trong Excel phổ biến

Excel cung cấp nhiều hàm mạnh mẽ giúp tự động hóa quy trình tính lương. Dưới đây là các nhóm hàm phổ biến và cách ứng dụng chúng vào việc tính lương.

2.1. Hàm logic (IF, AND, OR)

Các hàm logic là nền tảng quan trọng trong việc tính lương, cho phép thiết lập các điều kiện và phân loại dữ liệu theo nhiều tiêu chí khác nhau.

Hàm IF: Đây là hàm cơ bản nhất, cho phép thực hiện các phép kiểm tra điều kiện và trả về kết quả tương ứng.

Cú pháp: IF(logical_test, value_if_true, value_if_false)

Ứng dụng trong tính lương:

Phân loại mức thuế suất dựa trên thu nhập

Tính phụ cấp dựa trên vị trí công việc

Xác định tiền thưởng dựa trên hiệu suất

Ví dụ: Tính phụ cấp chức vụ dựa trên vị trí

=IF(C2="Trưởng phòng", 2000000, IF(C2="Phó phòng", 1500000, 0))

Hàm AND và OR: Kết hợp nhiều điều kiện trong một công thức.

Cú pháp AND: AND(logical1, logical2, ...)

Cú pháp OR: OR(logical1, logical2, ...)

Ứng dụng trong tính lương:

Kết hợp với IF để tạo điều kiện phức tạp

Xác định điều kiện hưởng phụ cấp đặc biệt

Ví dụ: Tính phụ cấp độc hại cho nhân viên làm việc tại phân xưởng có môi trường độc hại và có thâm niên trên 2 năm

=IF(AND(D2="Phân xưởng hóa chất", E2>2), 1000000, 0)

Bảng minh họa công thức IF kết hợp AND:

>>> TÌM HIỂU THÊM: Tải file bảng Excel tính lương hưu mới nhất 2025

2.2. Hàm tìm kiếm và truy vấn (VLOOKUP, INDEX, MATCH)

Các hàm tìm kiếm và truy vấn giúp tra cứu thông tin từ các bảng dữ liệu, rất hữu ích khi cần lấy thông tin từ nhiều nguồn khác nhau.

Hàm VLOOKUP: Tìm kiếm giá trị trong cột đầu tiên của bảng và trả về giá trị ở cột khác trên cùng một hàng.

Cú pháp: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Ứng dụng trong tính lương:

Tra cứu mức lương cơ bản từ bảng thang bảng lương

Tìm kiếm thông tin nhân viên từ danh sách

Xác định mức thuế suất từ bảng thuế TNCN

Ví dụ: Tra cứu mức lương cơ bản dựa trên mã nhân viên

=VLOOKUP(A2, BangLuong, 3, FALSE)

Hàm INDEX và MATCH: Kết hợp hai hàm này cho phép tìm kiếm linh hoạt hơn so với VLOOKUP.

Cú pháp INDEX: INDEX(array, row_num, [column_num])

Cú pháp MATCH: MATCH(lookup_value, lookup_array, [match_type])

Ứng dụng trong tính lương:

Tìm kiếm dữ liệu theo nhiều tiêu chí

Tra cứu thông tin từ bảng có nhiều cột và hàng

Ví dụ: Tìm mức lương dựa trên vị trí và thâm niên

=INDEX(BangLuong, MATCH(C2&D2, ViTri&ThamNien, 0), 5)

Bảng minh họa công thức VLOOKUP:

 

 

2.3. Hàm tính tổng (SUM, SUMIF, SUMIFS)

Các hàm tính tổng giúp tổng hợp dữ liệu theo nhiều điều kiện khác nhau, rất hữu ích trong việc tính tổng thu nhập hoặc khấu trừ.

Hàm SUM: Tính tổng các giá trị.

Cú pháp: SUM(number1, [number2], ...)

Ứng dụng trong tính lương:

Tính tổng thu nhập từ nhiều nguồn

Tính tổng các khoản khấu trừ

Ví dụ: Tính tổng thu nhập

=SUM(D2:G2)

Hàm SUMIF và SUMIFS: Tính tổng các giá trị thỏa mãn một hoặc nhiều điều kiện.

Cú pháp SUMIF: SUMIF(range, criteria, [sum_range])

Cú pháp SUMIFS: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Ứng dụng trong tính lương:

Tính tổng lương theo phòng ban

Tính tổng giờ làm thêm theo điều kiện

Tính tổng chi phí nhân sự theo vị trí

Ví dụ: Tính tổng giờ làm thêm ngày thường

=SUMIF(C2:C32, "Ngày thường", D2:D32)

Ví dụ: Tính tổng lương của nhân viên có thâm niên trên 3 năm và thuộc phòng kinh doanh

=SUMIFS(F2:F100, D2:D100, ">3", B2:B100, "Kinh doanh")

Bảng minh họa công thức SUMIF:

 

>>> KHÁM PHÁ NGAY: Cách tính bảng lương tăng ca kèm mẫu tải xuống miễn phí 2025

2.4. Hàm xử lý dữ liệu ngày tháng (DATE, NETWORKDAYS)

Các hàm xử lý dữ liệu ngày tháng giúp tính toán chính xác số ngày làm việc, ngày nghỉ và các thông tin liên quan đến thời gian.

Hàm DATE: Tạo một giá trị ngày tháng từ năm, tháng, ngày.

Cú pháp: DATE(year, month, day)

Ứng dụng trong tính lương:

Xác định ngày bắt đầu và kết thúc kỳ lương

Tính tuổi hoặc thâm niên của nhân viên

Ví dụ: Tạo ngày đầu tháng và cuối tháng

Ngày đầu tháng: =DATE(2025, 3, 1)

Ngày cuối tháng: =DATE(2025, 3, 31)

Hàm NETWORKDAYS: Tính số ngày làm việc giữa hai ngày, không tính ngày nghỉ cuối tuần và ngày lễ.

Cú pháp: NETWORKDAYS(start_date, end_date, [holidays])

Ứng dụng trong tính lương:

Tính số ngày làm việc thực tế trong tháng

Tính số ngày nghỉ phép hợp lệ

Ví dụ: Tính số ngày làm việc trong tháng 3/2025

=NETWORKDAYS(DATE(2025,3,1), DATE(2025,3,31), NgayLe)

Trong đó, NgayLe là một dải ô chứa danh sách các ngày lễ trong năm.

Bảng minh họa công thức NETWORKDAYS:

 

Danh sách ngày lễ (ô H2:H10):

• 08/03/2025 (Quốc tế Phụ nữ)
• 30/04/2025 (Ngày Giải phóng miền Nam)
• 01/05/2025 (Quốc tế Lao động)

3. Hướng dẫn lập bảng lương tự động trong Excel

Việc lập bảng lương tự động trong Excel đòi hỏi thiết kế cấu trúc hợp lý và áp dụng các công thức tính lương phù hợp. Dưới đây là hướng dẫn chi tiết về cách thiết kế và áp dụng các công thức vào bảng lương.

3.1. Thiết kế cấu trúc bảng lương

Một bảng lương hiệu quả cần có cấu trúc rõ ràng, dễ theo dõi và dễ dàng cập nhật. Dưới đây là hướng dẫn thiết kế cấu trúc bảng lương chuẩn:

Bước 1: Tạo sheet thông tin cơ bản

Tạo một sheet riêng chứa thông tin cơ bản của nhân viên

Các cột cần thiết: Mã nhân viên, Họ tên, Ngày sinh, Phòng ban, Vị trí, Ngày vào làm, Mức lương cơ bản, Số tài khoản, Số người phụ thuộc

Bước 2: Tạo sheet bảng chấm công

Thiết kế bảng theo dõi ngày công của từng nhân viên

Các cột cần thiết: Mã nhân viên, Họ tên, các ngày trong tháng (1-31), Tổng ngày công, Số giờ làm thêm (ngày thường, cuối tuần, lễ)

Bước 3: Tạo sheet bảng lương chính

Thiết kế bảng tính lương với đầy đủ thông tin

Các cột cần thiết:

Thông tin cơ bản: Mã nhân viên, Họ tên, Phòng ban, Vị trí

Thông tin ngày công: Ngày công chuẩn, Ngày công thực tế, Ngày nghỉ phép, Ngày nghỉ không lương

Thông tin thu nhập: Lương cơ bản, Lương theo ngày công, Phụ cấp, Tiền làm thêm giờ, Tiền thưởng, Tổng thu nhập

Thông tin khấu trừ: BHXH (8%), BHYT (1.5%), BHTN (1%), Thuế TNCN, Tạm ứng, Tổng khấu trừ

Thông tin thực lãnh: Thực lãnh, Ghi chú

Bước 4: Tạo các sheet phụ trợ

Sheet tham số: Chứa các thông số cố định như mức lương tối thiểu vùng, tỷ lệ BHXH, bảng thuế TNCN

Sheet báo cáo: Tổng hợp và phân tích dữ liệu lương

Gợi ý sắp xếp dữ liệu khoa học:

Sử dụng mã nhân viên làm khóa chính để liên kết giữa các sheet

Đặt tên cho các vùng dữ liệu để dễ dàng tham chiếu trong công thức

Sử dụng định dạng có điều kiện để làm nổi bật các thông tin quan trọng

Khóa các ô chứa công thức để tránh vô tình sửa đổi

3.2. Áp dụng công thức vào bảng lương

Sau khi đã thiết kế cấu trúc bảng lương, bước tiếp theo là áp dụng các công thức tính lương trong Excel để tự động hóa quy trình tính toán.

Bước 1: Tính ngày công thực tế bằng hàm COUNTIF

Để tính số ngày công thực tế dựa trên bảng chấm công:

=COUNTIF(BangChamCong!C2:AG2, "X")

Trong đó:

BangChamCong!C2:AG2 là dải ô chứa thông tin chấm công của nhân viên

"X" là ký hiệu đánh dấu ngày đi làm

Hoặc sử dụng NETWORKDAYS để tính số ngày làm việc trong khoảng thời gian:

=NETWORKDAYS(NgayBatDau, NgayKetThuc, DanhSachNgayLe) - SoNgayNghiKhongLuong

Bước 2: Tính lương theo ngày công

Công thức tính lương dựa trên ngày công thực tế:

=IF(NgayCongThucTe>=NgayCongChuan, LuongCoBan, LuongCoBan/NgayCongChuan*NgayCongThucTe)

Bước 3: Tính tiền làm thêm giờ

Công thức tính tiền làm thêm giờ ngày thường:

=ROUND((LuongCoBan/NgayCongChuan/8)*1.5*SoGioLamThemNgayThuong, 0)

Công thức tính tiền làm thêm giờ ngày nghỉ:

=ROUND((LuongCoBan/NgayCongChuan/8)*2*SoGioLamThemNgayNghi, 0)

Công thức tính tiền làm thêm giờ ngày lễ:

=ROUND((LuongCoBan/NgayCongChuan/8)*3*SoGioLamThemNgayLe, 0)

Bước 4: Tính các khoản bảo hiểm

Công thức tính BHXH (8%):

=MIN(LuongCoBan*8%, 20*LuongCoSo*8%)

Công thức tính BHYT (1.5%):

=MIN(LuongCoBan*1.5%, 20*LuongCoSo*1.5%)

Công thức tính BHTN (1%):

=MIN(LuongCoBan*1%, 20*LuongCoSo*1%)

Bước 5: Tính thuế thu nhập cá nhân

Công thức tính thu nhập chịu thuế:

=TongThuNhap - TongBaoHiem - 11000000 - SoNguoiPhuThuoc*4400000

Công thức tính thuế TNCN sử dụng hàm VLOOKUP và bảng thuế:

=IF(ThuNhapChiuThue<=0, 0, VLOOKUP(ThuNhapChiuThue, BangThue, 2, 1) + (ThuNhapChiuThue - VLOOKUP(ThuNhapChiuThue, BangThue, 3, 1))*VLOOKUP(ThuNhapChiuThue, BangThue, 4, 1))

Bước 6: Tính lương thực lãnh

Công thức tính lương thực lãnh:

=TongThuNhap - TongKhauTru

4. Các mẹo tối ưu hóa quy trình tính lương bằng Excel

Để nâng cao hiệu quả và độ chính xác khi sử dụng các công thức tính lương trong Excel, dưới đây là một số mẹo hữu ích giúp tối ưu hóa quy trình làm việc.

4.1. Sử dụng Conditional Formatting để kiểm tra lỗi nhanh chóng

Conditional Formatting (Định dạng có điều kiện) là công cụ mạnh mẽ giúp phát hiện nhanh các giá trị bất thường hoặc lỗi trong bảng tính lương.

Mẹo 1: Đánh dấu các giá trị bất thường

• Tô màu các ô có giá trị âm hoặc quá cao so với mức bình thường
• Đánh dấu các nhân viên có ngày công thực tế thấp hơn 50% ngày công chuẩn
• Làm nổi bật các trường hợp có thuế TNCN cao bất thường

Cách thực hiện:

• Chọn vùng dữ liệu cần áp dụng
• Vào Home > Conditional Formatting > New Rule
• Chọn loại quy tắc (ví dụ: "Format only cells that contain")
• Thiết lập điều kiện (ví dụ: Cell Value < 0)
• Chọn định dạng (màu nền, màu chữ)
• Nhấn OK để áp dụng

Mẹo 2: Tạo thanh tiến trình trực quan

• Hiển thị tỷ lệ hoàn thành ngày công so với ngày công chuẩn
• Tạo thanh tiến trình cho mức độ đạt KPI của nhân viên
• Trực quan hóa tỷ lệ các khoản khấu trừ so với tổng thu nhập

Cách thực hiện:

• Chọn vùng dữ liệu cần áp dụng
• Vào Home > Conditional Formatting > Data Bars
• Chọn màu sắc và kiểu thanh tiến trình phù hợp
• Tùy chỉnh các thông số như giá trị nhỏ nhất, lớn nhất nếu cần

Mẹo 3: Sử dụng Icon Sets để phân loại hiệu suất

• Phân loại nhân viên theo mức độ đóng góp
• Đánh giá tỷ lệ làm thêm giờ
• Cảnh báo các trường hợp nghỉ quá nhiều

Cách thực hiện:

• Chọn vùng dữ liệu cần áp dụng
• Vào Home > Conditional Formatting > Icon Sets
• Chọn bộ biểu tượng phù hợp (đèn giao thông, mũi tên, ...)
• Tùy chỉnh ngưỡng giá trị cho từng biểu tượng

4.2. Tạo báo cáo trực quan với PivotTable

PivotTable là công cụ mạnh mẽ giúp phân tích và tổng hợp dữ liệu lương một cách linh hoạt và trực quan.

Mẹo 1: Tạo báo cáo tổng hợp chi phí lương theo phòng ban

• Chọn dữ liệu bảng lương
• Vào Insert > PivotTable
• Kéo trường "Phòng ban" vào vùng Rows
• Kéo các trường cần tính tổng (Lương cơ bản, Phụ cấp, Tổng thu nhập) vào vùng Values
• Tùy chỉnh định dạng số và bố cục báo cáo

Mẹo 2: Phân tích xu hướng lương theo thời gian

• Tạo PivotTable từ dữ liệu lương nhiều tháng
• Kéo trường "Tháng" vào vùng Columns
• Kéo trường "Phòng ban" hoặc "Vị trí" vào vùng Rows
• Kéo trường "Tổng thu nhập" vào vùng Values
• Thêm PivotChart để trực quan hóa xu hướng

Mẹo 3: So sánh cơ cấu thu nhập giữa các nhóm nhân viên

• Tạo PivotTable từ dữ liệu lương
• Kéo trường phân loại (Vị trí, Cấp bậc) vào vùng Rows
• Kéo các thành phần thu nhập (Lương cơ bản, Phụ cấp, Thưởng) vào vùng Values
• Chọn hiển thị dữ liệu dưới dạng % của tổng số
• Thêm PivotChart kiểu cột chồng hoặc biểu đồ tròn để trực quan hóa

4.3. Tối ưu hóa công thức với các hàm nâng cao

Ngoài các công thức tính lương trong Excel cơ bản, việc sử dụng các hàm nâng cao sẽ giúp tối ưu hóa quy trình tính lương và tăng tính linh hoạt của bảng tính.

Mẹo 1: Sử dụng hàm SUMPRODUCT thay cho nhiều hàm SUMIF

Hàm SUMPRODUCT cho phép tính tổng tích của các dải số, rất hữu ích khi cần tính tổng có điều kiện phức tạp.

Ví dụ: Tính tổng tiền làm thêm giờ dựa trên loại ngày và số giờ

=SUMPRODUCT((LoaiNgay="Ngày thường")*(SoGio)*(DonGia))

Mẹo 2: Sử dụng hàm IFERROR để xử lý lỗi

Hàm IFERROR giúp xử lý các trường hợp lỗi trong công thức, tránh hiển thị các thông báo lỗi không mong muốn.

Ví dụ: Tính tỷ lệ tăng lương so với tháng trước

=IFERROR((LuongThangNay-LuongThangTruoc)/LuongThangTruoc, "N/A")

Mẹo 3: Sử dụng hàm INDIRECT để tham chiếu động

Hàm INDIRECT cho phép tạo tham chiếu động đến các ô hoặc dải ô, rất hữu ích khi cần lấy dữ liệu từ nhiều sheet khác nhau.

Ví dụ: Lấy dữ liệu từ sheet tương ứng với tháng hiện tại

=INDIRECT("'"&B1&"'!C5")

Trong đó B1 chứa tên sheet (ví dụ: "Lương tháng 3")

Mẹo 4: Sử dụng Data Validation để kiểm soát dữ liệu đầu vào

Data Validation giúp kiểm soát dữ liệu nhập vào, tránh các lỗi do nhập liệu sai.

Cách thực hiện:

Chọn vùng dữ liệu cần kiểm soát

Vào Data > Data Validation

Thiết lập các điều kiện hợp lệ (ví dụ: giới hạn số ngày công từ 0-31)

Thêm thông báo lỗi khi nhập sai

Mẹo 5: Sử dụng Name Manager để quản lý các vùng dữ liệu

Name Manager giúp đặt tên cho các vùng dữ liệu, giúp công thức dễ đọc và dễ bảo trì hơn.

Cách thực hiện:

Vào Formulas > Name Manager

Nhấn New để tạo tên mới

Nhập tên (ví dụ: LuongCoSo) và tham chiếu đến ô hoặc vùng dữ liệu

Sử dụng tên này trong công thức thay vì tham chiếu ô

Ví dụ: Thay vì =A1*8%, có thể viết =LuongCoBan*TyLeBHXH

Các công thức tính lương trong Excel là công cụ mạnh mẽ giúp doanh nghiệp tự động hóa và tối ưu hóa quy trình quản lý tiền lương. Từ các hàm cơ bản như IF, SUM đến các hàm nâng cao như VLOOKUP, INDEX-MATCH, Excel cung cấp đầy đủ công cụ để xây dựng một hệ thống tính lương chính xác, linh hoạt và hiệu quả. Việc áp dụng đúng các công thức kết hợp với thiết kế bảng tính hợp lý không chỉ giúp tiết kiệm thời gian mà còn giảm thiểu sai sót, đảm bảo tính minh bạch trong quản lý nhân sự. 1C Việt Nam luôn đồng hành cùng doanh nghiệp trong việc tối ưu hóa quy trình quản lý tiền lương thông qua việc ứng dụng công nghệ hiện đại.