Cách tính lương trên Excel chính xác nhất năm 2022

Cách tính lương trên Excel chính xác nhất 2022 là gì? Tính lương hàng tháng là một trong những hoạt động quen thuộc của người làm kế toán, nhân sự. mặc dù vậy trong thực tế thì còn nhiều bạn kế toán vẫn gặp nhiều vấn đề khi thực hiện. đặc biệt là với những người mới bắt tay vào làm vào nghề chưa có nhiều kinh nghiệm. Học cách tính lương trên excel chuẩn giúp công việc trở nên dễ dàng và thuận lợi hơn.

Bạn đang xem bài viết: Cách tính lương trên Excel chính xác nhất năm 2022

Căn cứ để làm bảng tính lương excel

từ rất sớm, Excel đã trở thành một trong những kỹ năng tin học quan trọng của mọi ngành nghề. Với những người làm nhân sự, đặc biệt so với phòng ban C&B (phụ trách chế độ tiền lương và phúc lợi) công cụ này như một thứ vũ khí sắc bén giúp đỡ đắc lực cho công tác tính toán phúc lợi, các chế độ BHXH, thuế TNCN.

Căn cứ để làm Bảng tính lương Excel gồm:

– Bảng chấm công, phiếu công nhận hàng hóa hoàn thành công việc.

– Hợp đồng lao động.

– Mức lương tối thiểu vùng mới nhất.

– Các khoản thu nhập chịu thuế và không chịu thuế TNCN.

– Tính được thuế TNCN phải nộp.

– Các khoản đóng và không phải đóng BHXH.

– tỷ lệ trích các khoản Bảo hiểm vào chi phí công ty và trích vào Lương người lao động…

Cách tính các chỉ tiêu trên bảng tính lương excel

1. Lương chính

Lương Chính là lương được thể hiện trên hợp đồng lao động, mức lương này cũng đều được thể hiện trên thang bảng lương mà các bạn xây dựng để nộp cho đơn vị bảo hiểm -> Là căn cứ để tạo ra mức lương đóng BHXH (tức là không nên thấp hơn mức lương ít ra vùng)

Ví dụ: doanh nghiệp Kế toán Thiên Ưng ở Vùng 1 thì mức lương tối thiểu năm 2021 là: 4.420.000. Và mức lương ít ra vùng sử dụng cho người lao động làm công việc đòi hỏi đã qua huấn luyện nghề, học nghề là 4.729.400đ/ tháng.

Như vậy: Các bạn phải thể hiện trên hợp đồng lao động và thang bảng lương: Mức lương tối thiểu phải là 4.729.400 (đối với những người lao động làm công việc đòi hỏi đã qua học nghề, đào tạo nghề).

Ở trong mẫu Bảng thanh toán tiền lương của Tanca để là: 5.000.000 đ/tháng (mức thấp nhất), thuyết phục yêu cầu. Tiếp đấy tùy từng vị trí các bạn xây dựng sao cho phù hợp.

2. Các khoản Phụ cấp

Các khoản phụ cấp không đóng BHXH gồm:

– Tiền thưởng theo quy định tại Điều 104 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 chông gai khi bị tai nạn lao động, bệnh nghề nghiệp và các khoản giúp đỡ, trợ cấp khác ghi thành mục riêng trong hợp đồng lao động quy định tại tiết c2 điểm c khoản 5 Điều 3 của Thông tư số 10/2020/TT-BLĐTBXH.

Chú ý: Trong các khoản giúp đỡ không phải đóng BHXH nêu trên thì có:

* Các khoản sau được miễn thuế TNCN:

– Tiền ăn ca, ăn giữa trưa.

– Tiền điện thoại.

– Tiền công tác phí.

– Tiền bộ quần áo.

– Tiền làm thêm giờ vào ngày nghỉ, lễ, thực hiện công việc vào ban đêm được trả cao hơn đối với ngày bình thường.

– Tiền đám hiếu, đám hỷ.

* Các khoản giúp đỡ, phụ cấp không được miễn thuế TNCN:

– Tiền thưởng.

– Tiền xăng xe, đi lại

– Tiền nuôi con nhỏ…

– Tiền phụ cấp …

Khoản tiền thuê nhà mà DN trả thay cho người làm công thì tính vào Thu nhập chịu thuế không vượt quá 15% tổng thu nhập chịu thuế (chưa bao gồm tiền thuê nhà)

* Phụ cấp trách nhiệm

– Khoản này phải đóng BHXH và phải tính thuế TNCN.

– Chi tiết về các khoản chịu thuế TNCN, không chịu thuế TNCN … Các bạn coi tại “Cách tính thuế TNCN” phía dưới nhé

3. Tổng thu nhập

Tổng thu nhập = Lương Chính + Phụ cấp …

4. Ngày công

Các bạn phải phụ thuộc vào Bảng chấm công để nhập vào công đoạn này.

5. Cách tính Tổng Lương thực tế

Tổng lương thực tế = Tổng thu nhập x (Số ngày công đi làm thực tế / 26)

(Hoặc = (Tổng lương / ngày công hành chính của tháng ) x số ngày thực hiện công việc thực tế.

Ví dụ: Tháng 9/2021 có 30 ngày: theo quy định của tổ chức nhân viên được nghỉ 4 ngày chủ nhật => có 26 ngày công thực tế. doanh nghiệp trả lương 5.000.000/tháng.

Cách 1: Tổng thu nhập x (Số ngày công đi làm thực tế / 26)

– nếu như bạn đi làm đủ 26 ngày: = 5.000.000 x (26/26) = 5.000.000

– nếu như bạn đi làm 25 ngày: = 5.000.000 x (25/26) = 4.807.692

Cách 2: Tổng thu nhập / ngày công hành chính của tháng x số ngày làm việc thực tế.

– nếu như bạn đi làm đủ 26 ngày = (5.000.000 / 26 ) x 26 = 5.000.000

– nếu bạn đi làm 25 ngày = (5.000.000 / 26 ) x 25 = 4.807.692

Chú ý: Tiền lương ngày được trả cho một ngày thực hiện công việc nắm rõ ràng trên cơ sở tiền lương tháng chia cho số ngày làm việc bình thường trong tháng theo quy định của pháp luật mà công ty Lựa chọn, tuy nhiên cao nhất không quá 26 ngày”.

(Theo Khoản 4 điều 14 Thông tư 47/2015/TT-BLĐTBXH)

6. Lương đóng BHXH

Lương đóng BHXH = Là mức lương chính ở phần 1 + Các khoản phụ cấp phải đóng BHXH.

Theo Quyết định 595/QĐ-BHXH quy định các khoản phụ cấp phải đóng BHXH:

– Phụ cấp chức vụ, chức danh;

– Phụ cấp trách nhiệm;

– Phụ cấp nặng nhọc, độc hại, nguy hiểm;

– Phụ cấp thâm niên;

­- Phụ cấp khu vực;

– Phụ cấp lưu động;

­- Phụ cấp nổi bật và các phụ cấp có tính chất tương tự

7. tỷ lệ trích các khoản bảo hiểm năm 2021

8. Thuế TNCN phải nộp

– Cột này thì các bạn phải tự tính số tiền thuế TNCN của từng nhân viên bằng 1 file Excel khác sau đấy thu thập số tiền phải nộp đấy nhập vào đây (Nếu làm Excel thì cần dùng hàm là đường link sang được ngay).

– Những lao động có ký hợp đồng trên 3 tháng thì các bạn tính theo biểu lũy tiến từng phần.

– Những lao động thời vụ, thử việc, ký hợp đồng dưới 3 tháng thì các bạn khấu trừ 10% trên thu nhập trước khi trả lương cho.

9. Tạm ứng

đây chính là số tiền mà nhân sự đã ứng trong tháng (chú ý Nó là tiền lương mà nhân viên đó ứng nhé, không phải tiền ứng để đi mua hàng)

10. Thực lĩnh

Thực lĩnh = Tổng thu nhập – Khoản tiền BHXH trính vào lương của NV – Thuế TNCN phải nộp (nếu có) – Tạm ứng (nếu có).

Lưu ý: nếu công ty có tính lương làm thêm giờ, tăng ca, ngày lễ..

Theo quy định: Người lao động làm thêm giờ được trả lương theo đơn giá thành lương hoặc tiền lương của công việc đang làm như sau:

a. Vào ngày thường, ít nhất bằng 150%;

b. Vào ngày nghỉ hàng tuần, ít nhất bằng 200%;

c. Vào ngày lễ, ngày nghỉ có hưởng lương, ít nhất bằng 300%.

lưu ý Quan trọng: Khi thanh toán tiền lương cho nhân viên các bạn phải đòi hỏi họ ký vào bảng thanh toán tiền lương, như vậy thì chi phí tiền lương này mới được trừ khi tính thuế TNDN nhé.

Các hàm, công thức thông dụng trong cách tính lương trên Excel

Trong Tin học văn phòng, 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 lúc sẽ có các chuyên môn phát sinh như lọc dữ liệu, cùng định dạng hàng, cột,… Các cán bộ tiền lương có thể tham khảo thêm top 10+ bí quyết 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ộ bí quyết 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ì thành quả sẽ là A, nếu không điều kiện nào thỏa mãn thì thành quả sẽ là B.
3. Hàm IF(AND)	IF(điều kiện(ĐK 1, ĐK 2, ĐK 3,…), thành quả A, thành quả B)	nếu như 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 gặp hư hạ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, thành quả C…..giá trị H)))	nếu như đ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 thành quả B,… nếu như không giá trị nào được thỏa mãn sẽ cho ra giá trị H.bí quyết thường sử 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: không thể không. Một hoặc nhiều ô, gồm có các số hoặc tên, mảng hay tham chiếu chứa số. thành quả trống và thành quả văn bản sẽ bị bỏ qua.– tiêu chí: không thể không. 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 trước tiên, cần đánh giá các tiêu chí liên kết.– tiêu chí 1: không thể không. Tiêu chí này có khả năng dưới dạng một vài, 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. cao nhất 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 thành quả muốn đếm.– value2, … Tùy chọn. Các đối số bổ sung đại diện cho thành quả mong muốn đếm, cao nhất 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ận xét 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 nắm rõ ràng 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 ô đã nắm rõ ràng trong đối số range. nếu như đối số sum_range bị bỏ qua, Excel cộng các ô được nắm rõ ràng trong đối số range (chính các ô đã được 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, gồm có 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ó khả năng khai báo cao nhất 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 thành quả thời gian
14. Hàm VLOOKUP	VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])	Đây là hàm Lựa chọn và truy vấn thông dụng nhất.mặc dù vậy đối tượng mục tiêu chọn lựa (lookup_value = Mã nhân sự / 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 chọn lựa (table_array = Bảng Danh sách nhân viên).

Cách tính lương trên Excel với hàm LEFT, RIGHT, MID

Mã nhân sự đượ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ã bộ phận.

đầu tiên, con người sẽ sử dụng hàm Right và Hlookup để điền đủ bộ phận.

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: một khi nhập bí quyết xong thì ấn Enter để hoàn thành. Sau đấy bạn giữ và kéo ôD6 xuống dưới để bộ máy tự điền các ô còn lại.

tiếp theo, chúng ta sẽ dùng hàm Left để điền mã Xếp loại của cấp dưới đấy

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 để thu thập 2 ký tự ở giữa của Mã nhân sự.

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 đấy 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 đấy 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 thu thập Lương cơ bản * Ngày công * Hệ số lương.

Bước 9: Các bạn nhập vào ô J6 công thức: =E6*F6*I6 sau đấy ấn Enter để hoàn thành và kéo xuống để điền nốt các ô còn lại