Làm sao để tính lương nhân viên trên excel, bài viết này sẽ hướng dẫn bạn cách thực hiện nhé. Excel cung cấp một loạt các hàm nâng cao cho việc tính lương, dựa trên nhiều quy tắc chặt chẽ. Các hàm có thể được áp dụng hàng loạt cho ô nhất định, theo tùy chỉnh của người dùng. Adtimin sẽ giúp người dùng tiết kiệm thêm nhiều thời gian và loại bỏ các sai sót so với tính toán bằng các phép tính thủ công hoặc bấm máy tính.
YÊU CẦU CỦA BÀI TẬP TÍNH LƯƠNG NHÂN VIÊN TRÊN EXCEL
- Nhập và định dạng dữ liệu như bảng tính
- Đánh số thứ tự cho cột STT theo mốc điền sẵn.
- Phụ cấp chức vụ được tính dựa vào chức vụ: (Sử dụng hàm IF)
+ GĐ: 1,000,000
+ TGĐ: 2,000,000
+ TP: 800,000
+ KT: 600,000
+ Các trường hợp khác: 300,000. - Tính cột Lương = Lương cơ bản * Ngày công.
- Tạm ứng được tính như sau:
+ Nếu (Phụ cấp chức vụ + Lương)*2/3 < 4,500,000 thì Tạm ứng = (Phụ cấp chức vụ + Lương)*2/3
+ Ngược lại: Tạm ứng = 5,000,000 (Làm tròn đến hàng ngàn, sử dụng hàm ROUND) - Tính cột Còn lại = Phụ cấp chức vụ + Lương – Tạm ứng.
- Tháng: Dùng hàm lấy ra tháng hiện tại
- Lưu bài tập với tên BaiTapExcel002.xlsx”
dow
Tính lương nhân viên trên excel
Download file bài tập tính lương nhân viên trên exel
Nếu bạn là nhân sự, hãy tải các file tính lương sau:
Để tạo được bảng tính lương bằng Excel, một số yếu tố cần có bao gồm thông tin của nhân viên như số thứ tự, mã nhân viên, họ tên, vị trí, và các khoản để tính lương như số ngày công thực tế, số ngày đi làm trên lý thuyết, mức lương theo hợp đồng, các khoản phụ cấp, tiền thưởng, tiền phạt…
- File Excel thanh toán lương theo thông tư 200: Tải xuống
- File mẫu tính lương 3P excel: Tải xuống
- File Mẫu bảng lương công nhân: Tải xuống
- File Mẫu thanh toán lương theo Thông tư 133: Tải xuống
1. Căn cứ để tính lương của người lao động
Các cơ sở để tính lương của người lao động bao gồm 4 phần:
- Thông tin trong hợp đồng lao động
- Loại hợp đồng (hợp đồng toàn thời gian, bán thời gian, thuê khoán …)
- Mức lương (lương cơ bản, lương hiệu suất, lương theo vị trí…)
- Thời gian & hình thức làm việc (VD: làm từ thứ 2 – sáng thứ 7, từ 8h30-18h00)
- Các khoản phụ cấp hàng tháng (điện thoại, phụ cấp đi lại, laptop,…)
- Các tính các khoản thưởng KPI (% hoa hồng …)
- Thông tin cá nhân (số người phụ thuộc, …)
- Tỉ lệ các khoản trích theo lương quy định trong luật (Nghị định 146/2018)
- Tỉ lệ BHYT, BHXH, BHTN công ty trả
- Tỉ lệ BHYT, BHXH, BHTN người lao động trả
- Mức tính thuế TNCN
- Tỉ lệ hoàn thành công việc & thời gian làm việc thực tế
- Phiếu chấm công/Bảng chấm công (Mẫu bảng chấm công Excel)
- Số giờ làm thêm, bảng xác nhận thời gian làm thêm giờ
- Biên bản nghiệm thu, tính toán tỉ lệ công việc hoàn thành (với lương khoán)
- Bảng tính KPI (tính lương P3)
2. Hướng dẫn cách làm bảng tính lương trên Excel
Sau khi thống nhất hình thức tính lương sẽ đi vào làm bảng lương. Bài viết này sẽ hướng dẫn các bạn cách làm bảng tính lương trên file Excel.
Trong bảng lương tiêu chuẩn thường bao gồm các tiêu chí:
- Số thứ tự nhân viên
- Mã nhân viên
- Họ tên nhân viên
- Vị trí: trưởng phòng, phó phòng
- Phòng ban
- Lương theo hợp đồng
- Số ngày công đi làm thực tế: Số ngày người lao động đi làm thực tế được ghi lại trong bảng công
- Số ngày đi làm lý thuyết
- Lương P1, Lương P2, Lương P3 theo hợp đồng
- Lương P1 P2 P3 theo thực tế
- Các khoản phụ cấp: Các mức tiền phụ cấp của công ty dành cho nhân viên và được quy định trong hợp đồng lao động như phụ cấp ăn trưa, xăng xe, điện thoại…
- Thưởng: các khoản thưởng kinh doanh, hoa hồng, thưởng lễ tết …
- Phạt: các khoản phạt được tính trong tháng
- Tổng thu nhập: Cột này bao gồm lương chính + phụ cấp
- Cột bảo hiểm bắt buộc (BHBB) bao gồm các loại bảo hiểm dành cho người lao động như bảo hiểm xã hội, bảo hiểm y tế, bảo hiểm nhân thọ. Gồm 2 cột, bảo hiểm nhân viên trả (10.5%) bảo hiểm công ty trả (21.5%)
- Thu nhập tính thuế TNCN: Tổng thu nhập thực tế – BHBB nhân viên
- Giảm trừ cá nhân: mặc định theo luật là 11.000.000 VNĐ
- Giảm trừ phụ thuộc: tính theo số người phụ thuộc người lao động đăng ký (4.400.000 * số người phụ thuộc)
- Tổng giảm trừ: Thu nhập tính thuế TNCN – Giảm trừ cá nhân – Giảm trừ phụ thuộc
- Mức tính thuế TNCN: Thu nhập tính thuế TNCN – Tổng giảm trừ (=0 nếu Tổng giảm trừ > Thu nhập tính thuế TNCN)
- Thuế thu nhập cá nhân: Tính theo thang thuế TNCN 2023 dựa trên cột mức tính thuế TNCN
- Cột tạm ứng: Là số tiền mà nhân viên đã tạm ứng trước khi được trả lương
- Tổng lương thực lĩnh: Là lương cuối cùng được nhận sau khi đã cộng hay trừ các khoản trên và được áp dụng theo công thức:
Lương thực lĩnh = tổng lương thực tế + các khoản thưởng – các khoản bảo hiểm – tạm ứng – phạt (nếu có)
3. Cách tính các khoản chính trong bảng lương trên Excel
Hiện nay các doanh nghiệp đang áp dụng 6 hình thức tính lương chính
Tính lương theo thời gian:
Công thức tính:
Cách 1: Tổng lương tháng = [( Lương + các khoản phụ cấp) / số ngày đi làm đã quy định trong hợp đồng]*số ngày thực tế theo bảng chấm công.
Cách 2: Tổng lương = [(Lương + các khoản phụ cấp) / 26]*số ngày đi làm thực tế
Cách tính lương dựa trên thời gian làm việc của người lao động
Áp dụng: Lao động làm việc theo ca (cố định, ca xoay, ca gãy)
Tính lương theo sản phẩm
Công thức tính:
Trả lương căn cứ vào số lượng sản phẩm nhân viên đạt được trong tháng đó
Tổng lương = đơn giá của sản phẩm * số lượng sản phẩm đạt được trong tháng
Cách tính lương dựa theo năng suất/số lượng sản phẩm thực tế của người lao động
Áp dụng:
Áp dụng cho nhân viên làm sản phẩm (công nhân may, công nhân nhà máy …)
Tính lương khoán:
Công thức tính:
Tổng lương = mức lương khoán đã đặt ra*tỷ lệ hoàn thành các công việc được giao
Là cách tính lương dựa trên khối lượng & số lượng cũng như chất lượng của công việc được giao.
Áp dụng:
Cho lao động làm việc theo dự án (xây dựng, …), các công việc cụ thể có thể đo lường chính xác tỉ lệ hoàn thành công việc
Dựa vào những căn cứ trên để kế toán có thể tính lương đi làm cho nhân viên một cách chính xác tuyệt đối.
Lương hoa hồng
Công thức:
Lương (hoa hồng) = % hoa hồng cá nhân * doanh thu cá nhân + % hoa hồng nhóm * doanh thu nhóm
Tính tiền lương dựa trên doanh số mà cá nhân hoặc nhóm đã đạt được trong quá trình làm việc.
Áp dụng:
Cho nhân viên bán hàng (sales): VD nhân viên sales bất động sản, điện thoại, phần mềm
Lương ngạch bậc
Công thức:
(Lương cơ sở x Hệ số lương (theo ngạch/bậc)) + Phụ cấp
Lương tính theo ngạch và bậc của nhân viên trong công ty
Áp dụng:
Cách tính lương này hiện tại không còn quá phổ biến, thường chỉ áp dụng trong doanh nghiệp nhà nước
Lương 3P
Công thức:
Lương = Lương P1 + Lương P2 + Lương P3
Lương tính theo 3P của người lao động (Position: vị trí, Person: năng lực, Perfomance: hiệu suất).
Đọc thêm: Lương 3P & Cách tính lương 3P
Áp dụng:
Đây là hình thức tính lương hiện đại đang được áp dụng hầu hết tại các doanh nghiệp & phù hợp với hầu hết người lao động.
4. Các hàm thường được sử dụng trong bảng tính lương trên Excel
| Loại hàm | Tên hàm | Công dụng |
| Hàm tra cứu nhân viên | Hàm VLOOKUP | Là hàm truy vấn và tìm kiếm thông dụng nhất hiện nay. Đối tượng để tìm kiếm cần phải nằm trong cột đầu tiên từ bên trái của vùng bảng tìm kiếm. |
| Hàm INDEX + MATCH | Là sự kết hợp của hai hàm để có thể tìm kiếm, truy vấn thông tin nhân viên một cách nhanh chóng. INDEX + MATCH mang lại hiệu quả tốt hơn so với hàm VLOOKUP vì nó không bị giới hạn các cấu trúc tìm kiếm. | |
| Hàm xử lý thời gian | DATE | Tạo ra 1 giá trị ngày tháng xác định rõ ràng, chính xác theo năm, tháng, ngày cụ thể |
| YEAR | Lấy ra năm của ô đã chọn (VD: year(03/04/2023) = 2023) | |
| MONTH | lấy tháng của 1 giá trị ngày tháng nào đó (VD: month(03/04/2023) = 4) | |
| DAY | lấy số ngày của 1 giá trị ngày tháng nào đó (VD: day(03/04/2023) = 03) | |
| HOUR | lấy số giờ của 1 giá trị có chứa giờ, phút (VD: hour(15:30)=15) | |
| MIN | lấy số phút của 1 giá trị có chứa giờ, phút (VD: min(15:30) = 30) | |
| Hàm chấm công | COUNTIF | Đếm số ký hiệu trong các bảng chấm công dựa theo một điều kiện nhất định |
| COUNTIFS | Đếm số ký hiệu công trong bảng chấm công theo nhiều điều kiện cùng một lúc | |
| SUMIF | Tính tổng số giờ công theo bảng chấm công và căn cứ vào điều kiện duy nhất | |
| SUMIFS | Tính bảng chấm công theo điều kiện cùng một lúc | |
| Hàm tính lương | IF | Hàm này dùng để phản biện logic trong khi tính toán. Ví dụ: Nếu theo yêu cầu thứ 1 thì sẽ ra kết quả như thế nào? Nếu không thỏa mãn được yêu cầu đó thì kết quả sẽ ra sao? |
| Hàm AND/ OR | Sử dụng hàm này khi cần phải biện luận nhiều điều kiện khác nhau, phức tạp thì AND/ OR có thể ghép nối các điều kiện đó với nhau. | |
| Hàm LOOKUP/ VLOOKUP | Dùng hàm này để truy vấn đến các thông tin về nhân viên, kết quả chấm công, thông tin lương,… | |
| Hàm SUM/ SUMIF/ SUMIFS | Dùng để tính tổng số tiền lương thực lĩnh cho nhân viên |
Tuy nhiên, làm bảng tính lương trên Excel vẫn có một số hạn chế nhất định, có thể thể kể đến như bị lỗi hàm, mất nhiều thời gian và công sức, khả năng bảo mật kém, không phù hợp với các doanh nghiệp quy mô lớn có nhiều nhân viên. Để giải quyết các vấn đề này, bạn có thể sử dụng phần mềm tính lương chuyên dụng, chẳng hạn như Adtimin để giải quyết các sai sót, đảm bảo tính chính xác và công bằng.
