Hướng dẫn cách làm bảng tính lương trong Excel theo mẫu Bảng thanh toán tiền lương ban hành kèm theo Thông tư 133 và Thông tư 200 mới nhất hiện nay.
Bước 1:
– Theo quy định tại Thông tư 200 và Thông tư 133 thì DN được tự thiết kế Mẫu bảng thanh toán tiền lương phù hợp với yêu cầu của DN.
-> Kế toán Thiên Ưng đã thiết kế 1 Mẫu bảng thanh toán tiền lương trên Excel chi tiết các khoản như: Lương cơ bản, lương đóng BHXH, phụ cấp, ăn trưa, điện thoại, các khoản BH, cách tính thuế TNCN.
-> Các bạn có thể tải miễn phí về tại đây:
Mẫu bảng thanh toán tiền lương trên Excel
Bước 2: – Cách tính các chỉ tiêu trên bảng thanh toán tiền lương:
1. Lương cơ bản:
– Lương cơ bản là lương được thể hiện trên hợp đồng lao động, mức lương này cũng được thể hiện trên thang bảng lương mà các bạn xây dựng để nộp cho cơ quan bảo hiểm -> Là căn cứ để xây dựng mức lương đóng BHXH.
VD: Công ty bạn ở Vùng 1 thì mức lương tối thiểu áp dụng cho nhân đã qua đào tạo nghề là 4.012.000 -> Như vậy các bạn phải thể hiện trên hợp đồng và thang bảng lương, mức lương tối thiểu [Cơ bản] phải là 4.012.000.
Chú ý: Khi xây dựng thanh bảng lương thì lương cơ bản phải căn cứ vào mức lương tối thiểu vùng, chi tiết xem tại đây: Mức lương tối thiểu vùng mới nhất
2. Các khoản hỗ trợ không đóng BHXH.
Hiện tại các khoản hỗ trợ không đóng BHXH gồm:
– Tiền thưởng theo quy định tại Điều 103 của Bộ luật lao động, tiền thưởng sáng kiến; – Tiền ăn giữa ca; – Các khoản hỗ trợ xăng xe, điện thoại, đi lại, tiền nhà ở, tiền giữ trẻ, nuôi con nhỏ; – Hỗ trợ khi người lao động có thân nhân bị chết, người lao động có người thân kết hôn, sinh nhật của người lao động, trợ cấp cho người lao động gặp hoàn cảnh khó khăn khi bị tai nạn lao động, bệnh nghề nghiệp và các khoản hỗ trợ, trợ cấp khác thì ghi thành mục riêng trong hợp đồng lao động theo Khoản 11 Điều 4 Nghị định số 05/2015/NĐ-CP.
Xem thêm: Các khoản phụ cấp phải đóng BHXH
Chú ý: Trong các khoản hộ trợ không phải đóng BHXH bên trên thì có:
Hôm nay, Taimienphi.vn sẽ hướng dẫn các bạn cách tính lương trong Excel sử dụng hàm Vlookup, Hlookup, Left, Right, Mid và If. Mời các bạn tham khảo.
CÁCH TÍNH LƯƠNG TRONG EXCEL SỬ DỤNG HÀM VLOOKUP, HLOOKUP, LEFT, RIGHT, MID VÀ IF
Bài tập tính lương trong Excel sẽ giúp các bạn làm quen và sử dụng các hàm Vlookup, Hlookup, Left, Right, Mid và If một cách thuần thục qua đó các bạn có thể áp dụng các hàm này vào các tình huống một cách hợp lý.
Đối với bài tập này, chúng ta sẽ hoàn thành các cột trống trong bảng dữ liệu dưới đây dựa vào hai bảng dữ liệu phụ và sử dụng các hàm Vlookup, Hlookup, Left, Right, Mid và If để hoàn thành nó.
Mã nhân viên được kết hợp bởi 3 yếu tố sau: Xếp loại [A, B, C, D], Số năm công tác và Mã phòng ban.
Đầu tiên, chúng ta sẽ sử dụng hàm Right và Hlookup để điền đủ Phòng ban.
Bước 1: Nhập vào ô D6 công thức như sau: =HLOOKUP[RIGHT[B6,2],$A$16:$D$17,2,0].
Bước 2: Sau khi nhập công thức xong thì ấn Enter để hoàn thành. Sau đó bạn giữ và kéo ôD6 xuống dưới để hệ thống tự điền các ô còn lại.
Tiếp theo, chúng ta sẽ sử dụng hàm Left để điền mã Xếp loại của nhân viên đó
Bước 3: Nhập vào ô G6 công thức: =LEFT[B6,1].
Bước 4: Các bạn ấn Enter để hoàn thành sau đó giữ và kéo ô G6 xuống để điền các ô còn lại
Để điền Số năm công tác, chúng ta sẽ sử dụng hàm Mid để lấy 2 ký tự ở giữa của Mã nhân viên.
Bước 5: Nhập vào H6 công thức: =MID[B6,2,2]
Bước 6: Các bạn ấn Enter để hoàn thành sau đó kéo xuống để điền các ô còn lại
Hệ số lương sẽ phải phụ thuộc vào mã Xếp loại và Số năm công tác đã có trong Bảng hệ số. Ở đây chúng ta sẽ kết hợp hàng If với hàm Vlookup để tính toán.
Bước 7: Nhập vào ô I6 công thức: =VLOOKUP[G6,$F$18:$J$21,IF[H6>=16,5,IF[H6>=9,4,IF[H6>=4,3,2]]]].
Bước 8: Các bạn ấn Enter để hoàn thành sau đó kéo xuống để điền các ô còn lại
Cuối cùng, chúng ta sẽ tính Tổng lương bằng cách lấy Lương cơ bản * Ngày công * Hệ số lương.
Bước 9: Các bạn điền vào ô J6 công thức: =E6*F6*I6 sau đó ấn Enter để hoàn thành và kéo xuống để điền nốt các ô còn lại
Trên đây là hướng dẫn cách tính lương trong Excel sử dụng hàm Vlookup, Hlookup, Left, hàm Right, Mid và If. Hi vọng rằng với bài viết trên đây, các bạn có thể dễ dàng thực hiện bài tập tính lương trong Excel này. Nếu trong quá trình thực hiện bạn gặp khó khăn thì đừng ngại hãy comment phía dưới, đội ngũ kỹ thuật của Taimienphi.vn sẽ hỗ trợ bạn.
Có bao giờ bạn thắc mắc các cơ quan, doanh nghiệp, công ty,… sử dụng bảng tính lương như thế nào hay chưa, vậy cùng tham khảo mẫu bảng tính tiền lương để giải đáp những điều còn vướng mắc của bản thân nhé.
Từ trước cho tới nay, cách tính phần trăm % rất phổ biến, xuất hiện trong nhiều lĩnh vực của cuộc sống, sử dụng thường xuyên trong Excel. Các bạn đọc bài viết các cách tính phần trăm nhanh và chính xác để có thể làm quen với dạng toán phổ biến này.
Tiếp tục các bài tập trong Excel, hôm nay chúng ta sẽ tiếp tục làm bài tập tính lương trong Excel sử dụng hàm Vlookup, Hlookup, Left, Right, Mid và If. Mời các bạn tham khảo.
Cách sử dụng kết hợp hàm Vlookup với hàm Left Hướng dẫn hàm vlookup nâng cao Sử dụng 2 hàm VLOOKUP để tính toán nhanh hơn Sử dụng hàm VLOOKUP để tra cứu 2 chiều Cách tính thang điểm chữ bằng hàm VLOOKUP Sử dụng hàm VLOOKUP để lấy dữ liệu từ bảng tính này sang bảng tính khác
Rất nhiều doanh nghiệp hiện nay áp dụng cách tính lương trên excel bởi tính quen thuộc, truyền thống. Tuy nhiên, đa phần thao tác trên các bảng lương khi đó đều là thủ công, yêu cầu doanh nghiệp phải tự thiết kế các thành phần tính lương cũng như lập hàm riêng biệt. Để tránh hoang mang trong quá trình làm bảng lương, MISA AMIS sẽ giới thiệu bộ chỉ tiêu cùng các hàm phổ dụng nhất, kèm theo đó là mẫu bảng lương có sẵn công thức để doanh nghiệp có thể tham khảo.
Xem thêm:
>> Tải miễn phí mẫu bảng lương nhân viên cập nhật 2022
>> Tổng hợp các mẫu xác nhận lương cho nhân viên miễn phí
1. Một số quy định và căn cứ để áp dụng cách tính lương trên Excel
Theo Thông tư 133 và Thông tư 200, doanh nghiệp được phép tự thiết kế bảng thanh toán tiền lương phù hợp với đặc thù và thuận tiện cho doanh nghiệp. Tuy nhiên, bất kể được xây dựng và tính toán ra sao, mẫu bảng lương này cũng cần căn cứ theo một số yếu tố sau đây:
- Bảng chấm công [hoặc phiếu xác nhận lượng sản phẩm đối với hình thức chấm công theo sản phẩm];
- Hợp đồng lao động;
- Mức lương tối thiểu vùng [cập nhật theo quy định mới nhất];
- Các khoản thu nhập cá nhân chịu thuế và không chịu thuế;
- Các khoản tính vào phí đóng và không phải đóng bảo hiểm xã hội;
- Tỷ lệ trích bảo hiểm vào chi phí doanh nghiệp và vào lương người lao động.
2. Các khoản mục cơ bản khi áp dụng cách tính lương trên Excel
2.1. Lương chính
Lương chính là lương được ghi trong hợp đồng lao động. Mức lương này cũng được thể hiện trong thang lương/bảng lương của doanh nghiệp, đồng thời là căn cứ xây dựng mức lương đóng BHXH.
>> Xem thêm: Các bước thiết lập và triển khai hệ thống lương 3P
2.2. Các khoản Phụ cấp
Một số loại phụ cấp trong doanh nghiệp2.2.1 Phụ cấp không đóng BHXH
Các khoản tiền này bao gồm:
– Các loại tiền thưởng, thưởng sáng kiến,… [căn cứ theo điều 104 Luật lao động hiện hành];
– Tiền ăn trưa, ăn giữa ca;
– Các khoản hỗ trợ đi lại, xăng xe, điện thoại, nhà ở,…;
– Hỗ trợ khi người lao động có thân nhân kết hôn, sinh nhật, chết, tai nạn,… [chi tiết tại mục c2 điểm c khoản 5 Điều 3 của Thông tư số 10/2020/TT-BLĐTBXH].
Trong đó, một số khoản sẽ được miễn thuế thu nhập cá nhân, bao gồm: tiền ăn theo ca, ăn giữa trưa; tiền điện thoại; công tác phí; tiền trang phục; tiền làm thêm giờ vào ngày nghỉ, lễ, hoặc làm việc ban đêm; tiền hỗ trợ hiếu hỉ.
Các khoản còn lại, bao gồm tiền thưởng, tiền xăng xe, tiền nuôi con nhỏ và phụ cấp sẽ không được miễn thuế. Riêng với các doanh nghiệp hỗ trợ tiền thuê nhà cho nhân viên thì khoản hỗ trợ này sẽ được tính vào thu nhập chịu thuế và không được vượt quá 15% tổng thu nhập chịu thuế.
2.2.2 Phụ cấp đóng BHXH
Căn cứ theo Quyết định 595/QĐ-BHXH, các khoản phụ cấp cần đóng BHXH bao gồm:
– Phụ cấp trách nhiệm;
– Phụ cấp chức vụ, chức danh;
– Phụ cấp độc hại, nặng nhọc, nguy hiểm;
– Phụ cấp khu vực;
– Phụ cấp thâm niên;
– Phụ cấp lưu động;
- Phụ cấp thu hút và các phụ cấp tương tự.
Cần lưu ý rằng mức phụ cấp giữa các doanh nghiệp là hoàn toàn khác nhau và mỗi người lao động cũng sẽ có những chế độ phụ cấp khác nhau. Mức phụ cấp có thể cao hay thấp tùy thuộc vào ngân sách doanh nghiệp, tính chất công việc hoặc điều kiện làm việc,…
>> Xem thêm: Phụ cấp là gì? 6 chế độ phụ cấp quan trọng nhất trong doanh nghiệp
2.3. Tổng thu nhập
Được tính bằng công thức: Tổng thu nhập = Lương Chính + Phụ cấp
2.4. Ngày công
Dựa vào bảng chấm công file excel hoặc dữ liệu trích xuất từ các phần mềm chấm công.
2.5. Lương thực tế
Tổng tiền lương thực tế sẽ được tính theo công thức:
Lương thực tế = Tổng thu nhập x [Số ngày đi làm thực tế / 26]
[Hoặc = [Tổng thu nhập/ngày công hành chính trong tháng ] x số ngày đi làm thực tế]
Công thức tính lương thực tế2.6. Lương đóng BHXH
Lương đóng BHXH = mức lương chính + Các khoản phụ cấp phải đóng BHXH
Căn cứ theo Quyết định 595/QĐ-BHXH, các khoản phụ cấp cần đóng BHXH bao gồm:
- Phụ cấp trách nhiệm;
- Phụ cấp chức vụ, chức danh;
- Phụ cấp độc hại, nặng nhọc, nguy hiểm;
- Phụ cấp khu vực;
- Phụ cấp thâm niên;
- Phụ cấp lưu động;
- Phụ cấp thu hút và các phụ cấp tương tự.
2.7. Tỷ lệ trích các khoản bảo hiểm năm 2022
Mục BHXH trích vào chi phí doanh nghiệp Mục BHXH trích vào lương nhân viên| Các khoản trích theo lương | Tính vào chi phí DN | Tính vào lương nhân viên |
| BHXH [bảo hiểm xã hội] | 17% | 8% |
| BHYT [bảo hiểm y tế] | 3% | 1,5% |
| BHTN [bảo hiểm thất nghiệp] | 1% | 1% |
| KPCĐ [kinh phí công đoàn] | 2% | |
| Tổng | 23% | 10,5% |
2.8. Thuế TNCN phải nộp
Theo phương pháp quản trị truyền thống, cán bộ nhân sự tiền lương thường phải tự tính toán tiền thuế này vào một file excel riêng, sau đó nhập lại dữ liệu hoặc tận dụng hàm để đồng bộ dữ liệu.
Với lao động có hợp đồng trên 3 tháng, phần thuế này sẽ tính theo lũy tiến từng phần. Với lao động thời vụ, đang trong thời gian thử việc hoặc ký hợp đồng lao động dưới 3 tháng thì sẽ khấu trừ 10% trên thu nhập trước khi trả lương.
>> Xem thêm: Quy trình và thủ tục hoàn thuế TNCN
2.9. Tạm ứng
Được tính bằng tổng số tiền lương mà nhân viên đã ứng trong tháng. Tiền tạm ứng để đi mua hàng sẽ không được tính trong mục này.
2.10. Thực lĩnh
Thực lĩnh = Tổng thu nhập – Khoản tiền BHXH trích vào lương của nhân viên – Thuế TNCN phải nộp [nếu có] – Tạm ứng [nếu có].
3. Các hàm, công thức thông dụng nhất khi áp dụng cách tính lương trên excel
Với 10 hạng mục kể trên, các công thức tưởng như chỉ dừng lại ở các phép tính cơ bản như cộng, trừ, nhân, chia. Tuy nhiên, đôi khi sẽ có các nghiệp vụ phát sinh như lọc dữ liệu, đồng bộ hàng, cột,… Các cán bộ tiền lương có thể tham khảo thêm top 10+ công thức excel phổ biến nhất hoặc tham khảo mẫu bảng tính lương trên excel với bộ công thức mẫu trong bài viết này.
| Hàm | Cú pháp | Ý nghĩa |
| 1. Hàm IF | IF [điều kiện, giá trị A, giá trị B]. | Điều kiện được thỏa mãn cho ra giá trị A, điều kiện không thỏa mãn cho ra giá trị B. |
| 2. Hàm IF[OR] | IF[điều kiện[hoặc là ĐK 1, hoặc là ĐK 2, hoặc là ĐK 3,…], giá trị A, giá trị B] | Nếu thỏa mãn 1 trong n điều kiện thì giá trị sẽ là A, nếu không điều kiện nào thỏa mãn thì giá trị sẽ là B. |
| 3. Hàm IF[AND] | IF[điều kiện[ĐK 1, ĐK 2, ĐK 3,…], giá trị A, giá trị B] | Nếu thỏa mãn đồng thời n điều kiện thì giá trị sẽ là A, nếu không điều kiện nào thỏa mãn thì giá trị sẽ là B. |
| 4. Hàm IFERROR | IFERROR[giá trị] | Nếu ô tính bị lỗi sẽ hiện giá trị 0 |
| 5. Hàm IF lồng nhau | IF[điều kiện 1,giá trị A,if[ĐK 2, giá trị B, ĐK 3, giá trị C…..giá trị H]]] | Nếu điều kiện 1 thỏa mãn cho ra giá trị A, điều kiện 2 thỏa mãn cho ra giá trị B,… Nếu không giá trị nào được thỏa mãn sẽ cho ra giá trị H.
Công thức thường áp dụng tính thuế TNCN |
| 6. Hàm COUNT [Đếm số ô chứa số] | COUNT[value1, [value2], …] | – value1: Bắt buộc. Mục đầu tiên, tham chiếu ô hoặc phạm vi bất kỳ bạn muốn đếm số.
– value2: Tùy chọn. Tối đa 255 mục, tham chiếu ô hoặc phạm vi bổ sung bạn muốn đếm số. |
| 7. Hàm COUNTIF [Đếm các ô dựa trên nhiều tiêu chí/điều kiện] | COUNTIF[phạm vi, tiêu chí] | – phạm vi: Bắt buộc. Một hoặc nhiều ô, bao gồm các số hoặc tên, mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản sẽ bị bỏ qua.
– tiêu chí: Bắt buộc. Gồm số, biểu thức, tham chiếu ô hay chuỗi văn bản xác định. |
| 8. Hàm COUNTIFS [Đếm các ô dựa trên nhiều tiêu chí/nhiều điều kiện] | COUNTIFS [phạm vi tiêu chí 1, tiêu chí 1, [phạm vi tiêu chí 2, tiêu chí 2],…] | – phạm vi tiêu chí 1: Bắt buộc. Phạm vi đầu tiên, cần đánh giá các tiêu chí liên kết.
– tiêu chí 1: Bắt buộc. Tiêu chí này có thể dưới dạng một số, biểu thức, tham chiếu ô hoặc văn bản xác định các ô cần đếm. – phạm vi tiêu chí 2, tiêu chí 2, … Tùy chọn. Tối đa 127 cặp phạm vi/tiêu chí. |
| 9. Hàm COUNTA [đếm số ô không trống trong một phạm vi.] | COUNTA[value1, [value2], …] | – value1 Bắt buộc. Đối số đầu tiên đại diện cho giá trị muốn đếm.
– value2, … Tùy chọn. Các đối số bổ sung đại diện cho giá trị muốn đếm, tối đa 255 đối số. |
| 10. Hàm Sum [Tính tổng các số] | SUM[[number1,number2, …] hoặc Sum[A1:An] | – Number1 là số 1
– Number2 là số 2 |
| 11. Hàm Sumif [Tính tổng có điều kiện] | SUMIF[range, criteria, [sum_range]] | – range: Bắt buộc. Phạm vi muốn đánh giá theo tiêu chí. Các ô trong mỗi phạm vi phải là số/tên/mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản sẽ bị bỏ qua.
– criteria: Bắt buộc. Tiêu chí ở dạng số/biểu thức/tham chiếu ô/văn bản hoặc hàm xác định sẽ cộng các ô nào. – sum_range: Tùy chọn. Các ô thực tế để cộng nếu muốn cộng các ô không phải là các ô đã xác định trong đối số range. Nếu đối số sum_range bị bỏ qua, Excel cộng các ô được xác định trong đối số range [chính các ô đã được áp dụng tiêu chí]. |
| 12. Hàm Sumifs [Tính tổng có nhiều điều kiện] | SUMIFS[sum_range,criteria_range1,criteria1,criteri a_range2,criteria2…] | – sum_range là các ô cần tính tổng, bao gồm các số, tên vùng, mãng hay các tham chiếu đến các giá trị. Các ô trống hay chứa văn bản sẽ bị bỏ qua.
– criteria_range1, criteria_range2, … có thể khai báo tối đa 127 vùng dùng để liên kết với các điều kiện bổ sung cho vùng. – criteria1, criteria2, … có thể khai báo tối đa 127 điều kiện dạng số, biểu thức, tham chiếu hoặc chuỗi. |
| 13. Hàm xử lý thời gian | – Hàm DATE[year,month,day]
– Hàm YEAR[serial_number] – Hàm MONTH[serial_number] – Hàm DAY[serial_number] – Hàm HOUR[serial_number] – Hàm MIN[serial_number] |
– Tạo 1 giá trị ngày tháng cụ thể;
– Theo dõi số năm tương ứng với 1 giá trị ngày tháng – Theo dõi số tháng ứng với 1 giá trị ngày tháng – Theo dõi số ngày ứng với 1 giá trị ngày tháng – Theo dõi số giờ ứng với 1 giá trị thời gian – Theo dõi số phút ứng với 1 giá trị thời gian |
| 14. Hàm VLOOKUP | VLOOKUP[lookup_value,table_array,col_index_num,[range_lookup]] | Đây là hàm tìm kiếm và truy vấn thông dụng nhất.
Tuy nhiên đối tượng tìm kiếm [lookup_value = Mã nhân viên / Tên nhân viên] cần nằm trong phạm vi cột đầu tiên phía bên trái của bảng tìm kiếm [table_array = Bảng Danh sách nhân viên]. |
4. Hạn chế sai sót, tính lương nhanh gọn với phần mềm tính lương AMIS Tiền lương
Cách tính lương trên Excel là phương pháp truyền thống và được rất nhiều doanh nghiệp áp dụng. Tuy nhiên, cách tính này còn khá thủ công, thường tốn thời gian tổng hợp dữ liệu và tính toán, đôi khi có thể xảy ra sai sót. Để khắc phục những nhược điểm này, AMIS Tiền lương ra đời với chức năng:
- Tự động thiết lập đầy đủ các thành phần bảng tính lương tương theo các khoản mục lương mà HR đang sử dụng trong cách tính lương trên excel, có hỗ trợ thiết lập công thức và hàm đầy đủ như excel;
- Nhập dữ liệu tính lương đầu vào bằng file excel để phần mềm tự động tính toán theo các phương thức: Lương theo thời gian, theo sản phẩm, theo doanh số, theo KPIs hoặc các bảng thu nhập khác nếu phát sinh thêm;
- Phần mềm tự động tính toán bảng lương nhân viên cùng các khoản Thuế TNCN, Bảo hiểm theo quy định của Nhà nước để ra bảng lương cuối cùng.
Đăng ký trải nghiệm ứng dụng AMIS Tiền lương hoàn toàn miễn phí
HRM.A0302 - TKLy [A-0302]