HƯỚNG DẪN CÁCH LẬP CÔNG THỨC TÍNH LƯƠNG TRONG EXCEL MỚI NHẤT 2023

Các yếu tố trong bảng lương

Trong bảng lương tiêu chuẩn thường bao gồm các tiêu chí:

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ó)

Các hàm Excel thường sử dụng trong bảng lương

Cách tra cứu nhân viên

Để có thể tra cứu thông tin nhanh nhất, bạn có thể áp dụng các hàm sau:

– 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.

Các hàm xử lý thời gian

Trên Excel có một số hàm có tính năng xử lý ngày tháng, giờ làm việc rất chính xác mà bạn có thể áp dụng:

– 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ể

– HOUR: lấy số giờ của 1 giá trị có chứa giờ, phút (VD: hour(15:30)=15)

– MIN: lấyi số phút của 1 giá trị có chứa giờ, phút (VD: min(15:30) = 30)

Các công thức 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: Tnh 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

Các công thức tính lương trên Excel 

Để tính lương cho nhân viên, người ta thường sử dụng các công thức sau:

– 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ự lĩnh cho nhân viên

Trên đây chúng tôi đã hướng dẫn đầy đủ cho các bạn cách làm bảng lương trên Excel mới nhất 2023. Chúc bạn thành công và chuyên nghiệp như một kế toán thực thụ.

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

Từ lâu, 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 đối với bộ phận 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 hỗ trợ đắc lực cho công tác tính toán lương thưởng, 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 xác nhận sản phẩm 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í Doanh nghiệp và trích vào Lương người lao động…

Hãy cùng shthcm.edu.vn tìm hiểu cách tính chế độ lương thưởng và các hàm để áp dụng trong excel nhé.

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 đượ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 (tức là không được thấp hơn mức lương tối thiểu vùng)

Ví dụ: Công ty 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ối thiểu vùng áp dụng cho người lao động làm công việc đòi hỏi đã qua đào tạo 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 shthcm.edu.vn để là: 5.000.000 đ/tháng (mức thấp nhất), đáp ứng yêu cầu. Tiếp đó 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 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 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 hỗ trợ 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 trang phục.

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

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

* Các khoản hỗ trợ, 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 nhân viên 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 xem tại "Cách tính thuế TNCN" bên 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 dựa vào Bảng chấm công để nhập vào phầ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 làm việc thực tế.

Ví dụ: Tháng 9/2021 có 30 ngày: theo quy định của Công ty nhân viên được nghỉ 4 ngày chủ nhật => có 26 ngày công thực tế. Công ty 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 bạn đi làm đủ 26 ngày: = 5.000.000 x (26/26) = 5.000.000

- Nếu 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 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 làm việc xác định 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à doanh nghiệp lựa chọn, nhưng tối đa 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 thu hú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 đó lấy số tiền phải nộp đó nhập vào đây (Nếu làm Excel thì cần dùng hàm là 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 là số tiền mà nhân viên đã ứng trong tháng (chú ý đây 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 doanh nghiệp 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á tiền 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%.

Chú ý Quan trọng: Khi thanh toán tiền lương cho nhân viên các bạn phải yêu cầu 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 excel chính trong chấm công tính lương

Khi làm kế toán tiền lương, bạn phải nắm được danh sách nhân viên của công ty, thông tin về hợp đồng lao động của mỗi người. Rồi thực hiện chấm công, tính lương thực hiện theo tháng. 

Vì vậy, shthcm.edu.vn sẽ liệt kê cho bạn các hàm, công thức excel liên quan đến việc xử lý công lương, truy vấn, tìm kiếm, tra cứu thông tin nhân viên có trong bảng danh sách nhân viên.

Cần nắm vững các hàm excel này vì tính ứng dụng và tần suất sử dụng trong nghiệp vụ chuyên môn của các vị trí hành chính nhân sự rất lớn.

1. Hàm IF

Cú pháp: IF (điều kiện, giá trị A, giá trị B).

Nếu thỏa mãn điều kiện đạt được thì giá trị sẽ là A, còn nếu không thỏa mãn điều kiện thì giá trị là B.

2. Hàm IF(OR)

Công thức: =IF(điều kiện(hoặc là điều kiện 1, hoặc là điều kiện 2, hoặc là điều kiện 3,…), giá trị A, giá trị B)

Nếu thỏa mãn các điều 1, hoặc 2, hoặc 3…thì giá trị sẽ là A, không thỏa mãn điều kiện là B.

3. Hàm IF(AND)

Công thức: =IF(điều kiện(điều kiện 1, điều kiện 2, điều kiện 3,…), giá trị A, giá trị B)

Nếu thỏa mãn đồng thời các điều kiện 1, điều kiện 2, điều kiện 3…thì giá trị là A, không thỏa mãn điều kiện là B.

4. Hàm IFERROR

Công thức: =IFERROR(giá trị)

Hàm sử dụng để chuyển giá trị lỗi về 0

5. Hàm IF lồng nhau

Công thức: =IF(điều kiện 1,giá trị A,if(điều kiện 2, giá trị B, điều kiện 3, giá trị C…..giá trị H)))

Nếu thỏa mãn điều kiện 1, thì đó là giá trị A, nếu thỏa mãn điều kiện 2, thì đó là giá trị B…Không là giá trị H (Lưu ý hàm sử dụng với nhiều điều kiện. Ví dụ như công thức tính thuế Thu nhập cá nhân)

6. Hàm COUNT (Đếm số ô chứa số)

Công thức: =COUNT(value1, , …)

- value1 Bắt buộc. Mục đầu tiên, tham chiếu ô hoặc phạm vi trong đó 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 trong đó 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)

Công thức: =COUNTIF(phạm vi, tiêu chí)

- phạm vi Bắt buộc. Một hoặc nhiều ô để đếm, 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 bị bỏ qua.

- tiêu chí Bắt buộc. Số, biểu thức, tham chiếu ô hay chuỗi văn bản xác định ô sẽ được đếm.

8. Hàm COUNTIFS (Đếm các ô dựa trên nhiều tiêu chí/nhiều điều kiện)

Công thức: =COUNTIFS(phạm vi tiêu chí 1, tiêu chí 1, ,…)

- phạm vi tiêu chí 1 Bắt buộc. Phạm vi thứ nhất trong đó cần đánh giá các tiêu chí liên kết.

- tiêu chí 1 Bắt buộc. Tiêu chí dưới dạng một số, biểu thức, tham chiếu ô hoặc văn bản để xác định những ô nào cần đếm.

- phạm vi tiêu chí 2, tiêu chí 2, … Tùy chọn. Những phạm vi bổ sung và tiêu chí liên kết của chúng. Cho phép 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.)

Công thức: =COUNTA(value1, , …)

- value1 Bắt buộc. Đối số đầu tiên đại diện cho giá trị mà bạn muốn đếm.

- value2, … Tùy chọn. Các đối số bổ sung đại diện cho giá trị mà bạn muốn đếm, tối đa 255 đối số.

10. Hàm Sum (Tính tổng các số)

Công thức: =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)

Công thức: =SUMIF(range, criteria, )

- range Bắt buộc. Phạm vi ô bạn muốn đánh giá theo tiêu chí. Các ô trong mỗi phạm vi phải là 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 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 bạn 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)

Công thức: =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 chuỗi (Text) sẽ bị bỏ qua.

- criteria_range1, criteria_range2, … có thể khai báo từ 1 đến 127 vùng dùng để liên kết với các điều kiện cho vùng.

- criteria1, criteria2, … có thể có từ 1 đến 127 điều kiện ở dạng cọn số, biểu thức, tham chiếu hoặc chuỗi.

Xem thêm: Cày Trên Đồng Ruộng Trắng Phau Khát Xuống Uống Nước Giếng Sâu Đen Ngòm

13. Hàm xử lý thời gian

- Hàm DATE(year,month,day): Tạo ra 1 giá trị ngày tháng xác định rõ bởi năm, tháng, ngày

- Hàm YEAR(serial_number) : Theo dõi số năm của 1 giá trị ngày tháng

- Hàm MONTH(serial_number): Theo dõi số tháng của 1 giá trị ngày tháng

- Hàm DAY(serial_number): Theo dõi số ngày của 1 giá trị ngày tháng

- Hàm HOUR(serial_number): Theo dõi số giờ của 1 giá trị thời gian

- Hàm MIN(serial_number): Theo dõi số phút của 1 giá trị thời gian

14. Hàm VLOOKUP

VLOOKUP(lookup_value,table_array,col_index_num,)

Đây là hàm truy vấn, tìm kiếm 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) phải nằm trong cột đầu tiên bên trái vùng bảng tìm kiếm (table_array = Bảng Danh sách nhân viên).