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.
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). Bạn đang xem: Tính hệ số lương trong excel | Đ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, | 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í 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, | 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ố 1Number2 là số 2 |
| 11. Hàm Sumif (Tính tổng có điều kiện) | SUMIF(range, criteria, | 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, | Đâ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.Download MIỄN PHÍ mẫu báo cáo quỹ lương ngay
"},"collapse_subitems":"yes","minimize_box":"yes","minimized_on":"tablet","hierarchical_view":"yes","min_height":{"unit":"px","size":"","sizes":<>},"min_height_tablet":{"unit":"px","size":"","sizes":<>},"min_height_mobile":{"unit":"px","size":"","sizes":<>}}" data-widget_type="table-of-contents.default">Đã từ lâu Excel trở thành công cụ hỗ trợ đắc lực cho công việc tính toán bảng lương, thưởng, bảo hiểm xã hội, thuế thu nhập cá nhân….. trong các công ty, doanh nghiệp. Để giúp bạn hiểu hơn về công việc này như thế nào, chúng tôi sẽ hướng dẫn chi tiết cho bạn cách tính các chỉ tiêu trên bảng tính lương Excel.
Bảng lương trên Excel là một trong những cách tính tiền lương phổ biến nhất trong doanh nghiệp. Căn cứ để làm bảng lương trên đây bao gồm bảng chấm công, hợp đồng lao động, mức lương tối thiểu vùng mới nhất, các khoản đóng & không phải đóng BHXH, các khoản thu nhập chịu thuế & không chịu thuế TNCN…..
Dưới đây là chi tiết cách tính chế độ lương thưởng và những hàm thường dùng trong Excel.
Cách tính các chỉ tiêu trong bảng tính lương Excel
Lương chính
Lương chính là mức lương thể hiện trên hợp đồng lao động, là căn cứ để xây dựng mức lương đóng BHXH (mức này không thấp hơn mức lương tối thiểu vùng).
Mức lương tối thiểu vùng năm 2022 như sau:– 4.420.000 đồng / tháng đối với doanh nghiệp hoạt động trên địa bàn thuộc vùng I– 3.920.000 đồng / tháng đối với doanh nghiệp hoạt động trên địa bàn thuộc vùng II– 3.430.000 đồng / tháng đối với doanh nghiệp hoạt động trên địa bàn thuộc vùng III– 3.070.000 đồng / tháng đối với doanh nghiệp hoạt động trên địa bàn thuộc vùng IV
Các khoản Phụ cấp
Các khoản phụ cấp không phải đó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ỏ– Các khoản 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.
Lưu ý: Trong các khoản hỗ trợ không phải đóng BHXH nêu trên thì 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 gồm:
– 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à)
Và phụ cấp trách nhiệm: Khoản này phải đóng BHXH và phải tính thuế TNCN
Tổng thu nhập
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 …
Ngày công
Bạn phải dựa vào Bảng chấm công để nhập vào phần này. Bảng chấm công sẽ tổng hợp số ngày công của người lao động trong tháng đó.
Tính tổng lương thực tế bằng công thức?
Dưới đây là công thức tính tổng tiền lương thực tế của người lao động.
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ế.
Lương đóng BHXH
Mức lương để đóng Bảo hiểm xã hội được tính theo công thức: Lương đóng BHXH = Mức lương chính ở phần 1 + Các khoản phụ cấp phải đóng BHXH.
Các khoản phụ cấp phải đóng BHXH theo quyết định 595/QĐ-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 và phụ cấp thu hút & các phụ cấp có tính chất tương tự.
Tỉ lệ trích các khoản bảo hiểm năm 2021
Thuế TNCN phải nộp
Đây là phần không thể thiếu của một bảng lương Excel.
Các bạn phải tự tính số tiền thuế TNCN của từng người bằng một file Excel khác sau đó lấy số tiền phải nộp nhập vào đây.
Lưu ý: Với những lao động ký hợp đồng trên 3 tháng thì tính theo biểu lũy tiến từng phần. Những lao động ký hợp đồng dưới 3 tháng thì khấu trừ 10% trên thu nhập trước khi trả lương.
Tạm ứng
Đây là khoản mà nhân viên đó đã ứng trong tháng đó.
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ờ, làm tăng ca, làm vào các ngày lễ thì tiền lương được tính như sau:
– Vào ngày thường, ít nhất bằng 150%– Vào ngày nghỉ hàng tuần, ít nhất bằng 200%– Vào ngày lễ, ngày nghỉ có hưởng lương, ít nhất bằng 300%
Khi thanh toán tiền lương cho nhân viên, kế toán phải yêu cầu họ ký vào bảng thanh toán tiền lương, như thế thì chi phí tiền lương mới được trừ khi tính thuế TNDN.
Các hàm và công thức dùng trong bảng tính lương Excel
Khi làm kế toán hay tính lương, bạn cần phải nằm lòng các hàm kèm công thức dùng trong bảng tính lương Excel sau:
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.
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.
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.
Hàm IFERROR
Công thức: =IFERROR(giá trị)
Hàm sử dụng để chuyển giá trị lỗi về 0
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)
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ố.
Hàm COUNTIF
Làm hàm dế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.
Hàm COUNTIFS
Hàm đế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í.
Hàm COUNTA
Hàm đế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ố.
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
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í).
Hàm Sumifs
Hàm 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.
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
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).
Xem thêm: Nấm Lùn Phiêu Lưu Ký Chi Tiết Nhất!, Maplestory M
Trên đây chúng tôi đã hướng dẫn cách tính các chỉ tiêu trên bảng tính lương Excel rất cụ thể, chi tiết và các hàm thường dùng trong Excel. Mong rằng bạn đọc đã hiểu thêm về nghiệp vụ này.