Hướng dẫn 14 hàm tính lương thưởng trong excel chuẩn nhất hiện nay
Excel là một phần mềm rất hữu ích và phổ biến trong công việc kế toán, nhất là khi lập bảng lương và tính lương cho nhân viên. Excel cung cấp cho người dùng nhiều hàm tính toán, thống kê, lọc dữ liệu, tra cứu và xử lý các điều kiện phức tạp. Trong bài viết này, tôi sẽ hướng dẫn bạn cách sử dụng 14 hàm tính lương thưởng trong excel chuẩn nhất hiện nay.
Tại sao cần sử dụng hàm tính lương thưởng trong excel?
Sử dụng hàm Excel khi lập bảng lương và tính lương mang lại nhiều lợi ích quan trọng. Dưới đây là một số lý do tại sao nên sử dụng Excel cho công việc này:
1. Tính toán tự động
Excel cung cấp một loạt các hàm tích hợp cho các phép toán toán học cơ bản, cũng như tính lương dựa trên các quy tắc phức tạp. Điều này giúp loại bỏ các sai sót tính toán thủ công và tiết kiệm thời gian.
2. Tích hợp công thức và dữ liệu
Bạn có thể kết hợp dữ liệu từ nhiều nguồn và sử dụng các công thức để tính toán lương dựa trên các tham số khác nhau. Điều này giúp tạo ra các bảng lương linh hoạt và dễ dàng cập nhật.
>>> Xem thêm: Hàm ifs trong excel: Khái niệm, công thức, cách dùng và lưu ý
3. Theo dõi lịch trình và thay đổi
Excel cho phép bạn tạo các biểu đồ và đồ thị để theo dõi thay đổi lương theo thời gian hoặc theo tiêu chí khác nhau. Điều này giúp quản lý lương hiệu quả và đưa ra quyết định dựa trên dữ liệu.
Công thức Excel cho phép theo dõi lịch trình và thay đổi
4. Tạo báo cáo và tài liệu
Excel cho phép bạn tạo các báo cáo và tài liệu dễ dàng từ dữ liệu lương. Bạn có thể tự động tạo ra các báo cáo thống kê hoặc tổng hợp, giúp bạn hiểu rõ hơn về tình hình lương của công ty.
5. Kiểm soát và đảm bảo chính xác
Sử dụng Excel giúp kiểm soát và đảm bảo tính chính xác của quy trình tính lương. Bạn có thể kiểm tra lại các bước tính toán và dễ dàng tìm ra và sửa các lỗi.
6. Dễ dàng sử dụng và tùy chỉnh
Excel là một công cụ dễ sử dụng, đặc biệt đối với những người quen thuộc với giao diện Microsoft Office. Nó cũng cho phép người dùng tùy chỉnh bảng lương theo nhu cầu cụ thể của công ty.
>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"
7. Tiết kiệm thời gian và công sức
Việc sử dụng Excel giúp tiết kiệm thời gian và công sức so với việc tính toán lương thủ công. Các công thức và tính năng tự động hoá giúp quá trình này nhanh chóng và dễ dàng hơn.
Tóm lại, sử dụng hàm Excel khi lập bảng lương và tính lương mang lại nhiều lợi ích về tính toán tự động, theo dõi, kiểm soát chính xác và tiết kiệm thời gian. Đây là một công cụ mạnh mẽ để quản lý và xử lý thông tin lương trong môi trường công ty.
Việc sử dụng Excel giúp tiết kiệm thời gian và công sức so với việc tính toán lương thủ công
Cách tính các chỉ tiêu trên bảng tính lương excel
Trước khi sử dụng các hàm Excel để tính lương thưởng, bạn cần biết cách tính các chỉ tiêu cơ bản trên bảng tính lương excel. Chi tiết sẽ được chúng tôi đề cập ở dưới đây:
1. Lương chính
Lương chính là mức lương cơ bản của nhân viên, được thỏa thuận giữa nhân viên và công ty khi ký hợp đồng lao động. Lương chính được tính theo tháng hoặc theo giờ, tùy theo loại hợp đồng. Lương chính được nhập vào bảng tính lương excel theo từng nhân viên, không cần sử dụng hàm Excel.
Lương chính là mức lương cơ bản của nhân viên, được thỏa thuận giữa nhân viên và công ty khi ký hợp đồng lao động
2. Các khoản Phụ cấp
Phụ cấp là các khoản tiền được công ty trả thêm cho nhân viên ngoài lương chính, để bù đắp cho các chi phí phát sinh trong quá trình làm việc như phụ cấp đi lại, phụ cấp ăn trưa, phụ cấp chuyên cần, phụ cấp trách nhiệm,…
Phụ cấp được tính theo mức định mức hoặc theo tỷ lệ phần trăm của lương chính, tùy theo quy định của công ty. Phụ cấp được nhập vào bảng tính lương excel theo từng nhân viên, không cần sử dụng hàm Excel.
3. Tổng thu nhập
Tổng thu nhập là tổng số tiền mà nhân viên nhận được từ công ty trong một tháng, bao gồm lương chính và các khoản phụ cấp.
Tổng thu nhập được tính bằng cách cộng lương chính và các khoản phụ cấp của mỗi nhân viên. Tổng thu nhập có thể sử dụng hàm Excel SUM để tính toán nhanh chóng và chính xác.
4. Ngày công
Ngày công là số ngày mà nhân viên thực hiện công việc trong một tháng, không tính các ngày nghỉ lễ, nghỉ phép, nghỉ bệnh,…
Ngày công được tính theo số giờ làm việc của nhân viên trong một ngày, chia cho số giờ quy định của công ty. Ngày công được nhập vào bảng tính lương excel theo từng nhân viên, không cần sử dụng hàm Excel.
Ngày công là số ngày mà nhân viên thực hiện công việc trong một tháng, không tính các ngày nghỉ lễ, nghỉ phép, nghỉ bệnh,…
5. Cách tính Tổng Lương thực tế
Tổng Lương thực tế là số tiền mà nhân viên thực sự nhận được sau khi đã trừ đi các khoản khấu trừ như bảo hiểm xã hội, bảo hiểm y tế, bảo hiểm thất nghiệp, thuế thu nhập cá nhân…
Tổng Lương thực tế được tính bằng cách nhân Tổng thu nhập với tỷ lệ Ngày công, rồi trừ đi các khoản khấu trừ.
Tổng Lương thực tế có thể sử dụng hàm Excel IF để xử lý các trường hợp đặc biệt, như khi Ngày công bằng không hoặc âm.
6. Tỷ lệ trích các khoản bảo hiểm năm 2021
Tỷ lệ trích các khoản bảo hiểm là tỷ lệ phần trăm mà công ty và nhân viên phải đóng vào quỹ bảo hiểm xã hội, bảo hiểm y tế và bảo hiểm thất nghiệp.
Tỷ lệ trích các khoản bảo hiểm được quy định theo Luật Bảo hiểm xã hội và Thông tư số 59/2015/TT-BLĐTBXH.
Tỷ lệ trích các khoản bảo hiểm năm 2021 là:
- Bảo hiểm xã hội: Công ty đóng 17,5%, nhân viên đóng 8%.
- Bảo hiểm y tế: Công ty đóng 3%, nhân viên đóng 1,5%.
- Bảo hiểm thất nghiệp: Công ty đóng 1%, nhân viên đóng 1%.
Tỷ lệ trích các khoản bảo hiểm được nhập vào bảng tính lương excel theo từng loại bảo hiểm, không cần sử dụng hàm Excel.
Tỷ lệ trích các khoản bảo hiểm là tỷ lệ phần trăm mà công ty và nhân viên phải đóng vào quỹ bảo hiểm xã hội, bảo hiểm y tế và bảo hiểm thất nghiệp
7. Lương đóng BHXH
BHXH là số tiền mà công ty và nhân viên đóng góp vào quỹ bảo hiểm xã hội, để hưởng các chế độ bảo hiểm khi có sự cố xảy ra, như tai nạn lao động, bệnh nghề nghiệp, nghỉ thai sản, nghỉ hưu…
Lương đóng BHXH được tính theo mức lương chính và các khoản phụ cấp có tính chất thường xuyên của nhân viên, không được thấp hơn mức lương tối thiểu vùng. Lương đóng BHXH được nhập vào bảng tính lương excel theo từng nhân viên, không cần sử dụng hàm Excel.
8. Thuế TNCN phải nộp
Thuế TNCN phải nộp là số tiền mà nhân viên phải nộp cho Nhà nước theo quy định của Luật Thuế thu nhập cá nhân và Thông tư số 111/2013/TT-BTC.
Thuế TNCN phải nộp được tính theo công thức sau:
Thuế TNCN phải nộp = (Tổng thu nhập - Giảm trừ gia cảnh - Giảm trừ cá nhân) x Thuế suất
Trong đó:
- Tổng thu nhập là tổng số tiền mà nhân viên nhận được từ công ty trong một tháng, bao gồm lương chính và các khoản phụ cấp.
- Giảm trừ gia cảnh là số tiền được giảm trừ cho người phụ thuộc của nhân viên, được quy định là 4.400.000 đồng/tháng/ người.
- Giảm trừ cá nhân là số tiền được giảm trừ cho chính nhân viên, được quy định là 11.000.000 đồng/tháng.
- Thuế suất là tỷ lệ phần trăm mà nhân viên phải nộp thuế theo các khoảng thu nhập chịu thuế. Các khoảng thu nhập chịu thuế và thuế suất tương ứng là:
Thuế TNCN phải nộp có thể sử dụng hàm Excel IF hoặc VLOOKUP để tính toán nhanh chóng và chính xác.
Thuế TNCN phải nộp là số tiền mà nhân viên phải nộp cho Nhà nước theo quy định của Luật Thuế thu nhập cá nhân và Thông tư số 111/2013/TT-BTC
9. Tạm ứng
Tạm ứng là số tiền mà nhân viên đã nhận trước từ công ty trong một tháng, để chi tiêu cho các nhu cầu cá nhân hoặc gia đình.
Tạm ứng được trừ vào Tổng Lương thực tế của nhân viên khi thanh toán lương. Tạm ứng được nhập vào bảng tính lương excel theo từng nhân viên, không cần sử dụng hàm Excel.
10. Thực lĩnh
Thực lĩnh là số tiền mà nhân viên thực sự nhận được sau khi đã trừ đi các khoản khấu trừ và tạm ứng.
Thực lĩnh được tính bằng cách trừ Tổng Lương thực tế cho Tạm ứng của mỗi nhân viên. Thực lĩnh có thể sử dụng hàm Excel IF để xử lý các trường hợp đặc biệt, như khi Tạm ứng lớn hơn Tổng Lương thực tế.
14 hàm tính lương thưởng trong excel
Trong phần này, Unica sẽ giới thiệu cho bạn 14 hàm tính lương thưởng trong excel, cùng với cách sử dụng và ví dụ minh họa. Các hàm này sẽ giúp bạn xử lý các trường hợp phức tạp, như tính lương theo điều kiện, tính lương theo bậc thang, tính lương theo công thức, tính lương theo bảng tra cứu,…
1. Cách tính lương trong excel bằng hàm IF
Hàm IF là một hàm logic cơ bản trong excel, cho phép bạn kiểm tra một điều kiện nào đó và trả về kết quả khác nhau tùy thuộc vào điều kiện đó có đúng hay sai.
Công thức của hàm tính lương thưởng trong excel IF là:
=IF(logical_test,value_if_true,value_if_false)
Trong đó:
- logical_test là điều kiện cần kiểm tra, có thể là một biểu thức so sánh, một giá trị logic hoặc một công thức khác.
- value_if_true là giá trị hoặc công thức sẽ được trả về nếu điều kiện đúng.
- value_if_false là giá trị hoặc công thức sẽ được trả về nếu điều kiện sai.
Ví dụ: Cho bảng dữ liệu 1:
Yêu cầu: Tính lương của các nhân viên có trong bảng 1 với điều kiện:
- Nếu doanh số đạt từ 100 triệu trở lên, thì lương = lương cơ bản + 10% doanh số.
- Nếu doanh số dưới 100 triệu, thì lương = lương cơ bản + 5% doanh số.
Cách sử dụng hàm if để tính tiền thưởng:
- Bước 1: Ở ô D3, bạn nhập công thức:
=IF(C3>100000000;B3+10%*C3;B3+5%*C3).
Nhập công thức vào ô D3
- Bước 2: Nhấn enter, Excel sẽ trả về kết quả là 17000000 VNĐ.
Kết quả sau khi nhập công thức
- Bước 3: Kéo công thức xuống bên dưới, bảng kết quả bạn nhận được là:
2. Hàm IF kết hợp với OR
Hàm OR là một hàm logic trong excel, cho phép bạn kiểm tra nhiều điều kiện cùng một lúc và trả về TRUE nếu ít nhất một trong các điều kiện đó đúng, và FALSE nếu tất cả các điều kiện đó sai.
Công thức của hàm tính lương thưởng trong excel OR là:
=OR(logical1,logical2,...)
Trong đó:
- logical1, logical2,… là các điều kiện cần kiểm tra, có thể là một biểu thức so sánh, một giá trị logic hoặc một công thức khác.
- Hàm OR có thể chứa tối đa 255 điều kiện.
Ví dụ: Cho bảng dữ liệu 2:
Yêu cầu: Tính lương của các nhân viên trong bảng 2 với điều kiện:
- Nếu doanh số đạt từ 100 triệu trở lên hoặc ngày công từ 26 ngày trở lên, thì lương = lương cơ bản + 10% doanh số.
- Nếu không, thì lương = lương cơ bản + 5% doanh số.
Cách tính lương thưởng theo doanh thu:
- Bước 1: Nhập công thức sau vào ô E15:
=IF(OR(C15>100000000;D15>=26);B15+10%*C15;B15+5%*C15).
Nhập công thức IF và OR vào E15
- Bước 2: Nhấn enter, kết quả thu được là 9500000 VNĐ.
Kết quả thu được là 9500000 VNĐ
- Bước 3: Copy công thức xuống dưới, bảng kết quả bạn thu được là:
3. Hàm IF kết hợp với AND
Trong số các hàm tính lương trong excel, AND là một hàm logic, cho phép bạn kiểm tra nhiều điều kiện cùng một lúc và trả về TRUE nếu tất cả các điều kiện đó đúng, và FALSE nếu ít nhất một trong các điều kiện đó sai.
Công thức của hàm tính lương thưởng trong excel AND là:
=AND(logical1,logical2,...)
Trong đó:
- logical1, logical2,… là các điều kiện cần kiểm tra, có thể là một biểu thức so sánh, một giá trị logic hoặc một công thức khác.
- Hàm AND có thể chứa tối đa 255 điều kiện.
Ví dụ: Cho bảng dữ liệu 3:
Yêu cầu: Tính lương của các nhân viên trong bảng 3 với điều kiện:
- Nếu doanh số đạt từ 100 triệu trở lên và ngày công từ 26 ngày trở lên, thì lương = lương cơ bản + 15% doanh số.
- Nếu không, thì lương = lương cơ bản + 5% doanh số.
Cách tính tiền thưởng theo doanh số:
- Bước 1: Ở ô E33, bạn nhập công thức tính lương trong excel như sau:
=IF(AND(C33>100000000;D33>=26);B33+10%*C33;B33+5%*C33).
Nhập công thức IF và AND
- Bước 2: Nhấn enter, Excel trả về kết quả là 23000000 VNĐ.
Kết quả là 23000000 VNĐ
- Bước 3: Kéo thả chuột xuống bên dưới, bảng kết quả thu được là:
4. Hàm IFERROR
Hàm IFERROR là một hàm thông dụng trong excel, cho phép bạn xử lý các trường hợp có lỗi xảy ra trong công thức, như #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!.
Công thức của hàm tính lương thưởng trong excel IFERROR là:
=IFERROR(value,value_if_error)
Trong đó:
- value là giá trị hoặc công thức cần kiểm tra lỗi.
- value_if_error là giá trị hoặc công thức sẽ được trả về nếu value có lỗi.
Ví dụ: Cho bảng dữ liệu 4:
Yêu cầu: Tính tỷ lệ dựa trên doanh số và mục tiêu của từng tháng theo:
- Tỷ lệ = doanh số / mục tiêu
- Nếu mục tiêu bằng 0, thì trả về “Không xác định”
Cách tính tiền thưởng trong excel:
- Bước 1: Nhập công thức tính thưởng trong excel sau vào ô D46:
=IFERROR(B46/C46;"Không xác định").
Nhập công thức sau vào ô D46
- Bước 2: Nhấn enter, kết quả Excel trả về là 1,348314607.
Kết quả sau khi nhập công thức
- Bước 3: Kéo thả chuột xuống bên dưới, bảng kết quả bạn thu được là:
5. Công thức tính lương trong excel bằng hàm IF lồng nhau
Hàm IF lồng nhau là cách sử dụng nhiều hàm IF trong một công thức, để xử lý các trường hợp có nhiều điều kiện khác nhau.
Công thức của hàm tính lương thưởng trong excel IF lồng nhau là:
=IF(logical_test1,value_if_true1,IF(logical_test2,value_if_true2,...,value_if_false))
Trong đó:
- logical_test1, logical_test2,… là các điều kiện cần kiểm tra, có thể là một biểu thức so sánh, một giá trị logic hoặc một công thức khác.
- value_if_true1, value_if_true2,… là các giá trị hoặc công thức sẽ được trả về nếu điều kiện tương ứng đúng.
- value_if_false là giá trị hoặc công thức sẽ được trả về nếu tất cả các điều kiện đều sai.
- Hàm IF lồng nhau có thể chứa tối đa 64 hàm IF.
Ví dụ: Cho bảng dữ liệu 5:
Yêu cầu: Tính lương của các nhân viên trong bảng 5 với điều kiện:
- Nếu doanh số đạt từ 100 triệu trở lên và ngày công từ 26 ngày trở lên, thì lương = lương cơ bản + 15% doanh số + 1.000.000 đồng thưởng chuyên cần.
- Nếu doanh số dưới 100 triệu và ngày công từ 26 ngày trở lên, thì lương = lương cơ bản + 5% doanh số.
Cách làm:
- Bước 1: Ở ô E57, bạn nhập công thức tính tiền thưởng trong excel như sau:
=IF(AND(C57>100000000;D57>=26);B57+15%*C57+1000000;IF(AND(C57<100000000;D57>=26);B57+5%*C57)).
Công thức tính lương trong excel bằng hàm IF lồng nhau
- Bước 2: Nhấn enter, kết quả thu được là 34000000 VNĐ.
Kết quả thu được là 34000000 VNĐ
- Bước 3: Copy công thức xuống bên dưới, bảng kết quả thu được như sau:
6. Hàm COUNT (Đếm số ô chứa số)
Hàm COUNT là một hàm thống kê trong excel, cho phép bạn đếm số ô chứa các giá trị số trong một vùng dữ liệu.
Công thức của hàm tính lương thưởng trong excel COUNT là:
=COUNT(value1,value2,...)
Trong đó:
- value1, value2,… là các giá trị hoặc vùng dữ liệu cần đếm, có thể là một số, một ô, một dãy ô hoặc một công thức khác.
- Hàm COUNT có thể chứa tối đa 255 giá trị.
Ví dụ: Cho bảng ví dụ 6:
Yêu cầu: Đếm số nhân viên có doanh số trong bảng 6.
Cách tính lương trong excel:
- Bước 1: Ở ô C77, bạn nhập công thức:
=COUNT(C72:C76;">150000000").
Nhập công thức tính
- Bước 2: Nhấn enter, Excel sẽ trả về kết quả là 4 tức là có 4 nhân viên có doanh thu.
Kết quả tính toán
7. Hàm COUNTIF (Đếm các ô dựa trên nhiều tiêu chí/điều kiện)
Hàm COUNTIF là một hàm thống kê trong excel, cho phép bạn đếm số ô thỏa mãn một hoặc nhiều tiêu chí hoặc điều kiện trong một vùng dữ liệu.
Công thức của hàm tính lương thưởng trong excel COUNTIF là:
=COUNTIF(range,criteria)
Trong đó:
- range là vùng dữ liệu cần đếm, có thể là một ô, một dãy ô hoặc một tên miền.
- criteria là tiêu chí hoặc điều kiện cần kiểm tra, có thể là một số, một chuỗi, một biểu thức so sánh, một giá trị logic hoặc một công thức khác.
Ví dụ: Cho bảng dữ liệu 7:
Yêu cầu: Đếm số nhân viên có doanh số từ 100 triệu trở lên trong bảng tính lương excel.
Cách làm:
- Bước 1: Trong ô C77, nhập công thức sau:
=COUNTIF(C72:C76;">150000000").
Nhập công thức COUNTIF
Trong đó: C72:C76 là vùng dữ liệu chứa doanh số của các nhân viên.
- Bước 2: Kết quả sẽ là 3, tức là có 3 nhân viên có doanh số từ 150 triệu trở lên.
Kết quả tính toán
>>> Xem thêm: Hướng dẫn cách viết dấu lớn hơn hoặc bằng dấu khác trong excel
8. Hàm COUNTIFS (Đếm các ô dựa trên nhiều tiêu chí/nhiều điều kiện)
Hàm COUNTIFS là một hàm thống kê trong excel, cho phép bạn đếm số ô thỏa mãn nhiều tiêu chí hoặc điều kiện trong nhiều vùng dữ liệu khác nhau.
Công thức của hàm tính lương thưởng trong excel COUNTIFS là:
=COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,...)
Trong đó:
- criteria_range1, criteria_range2,… là các vùng dữ liệu cần đếm, có thể là một ô, một dãy ô hoặc một tên miền.
- criteria1, criteria2,… là các tiêu chí hoặc điều kiện cần kiểm tra, có thể là một số, một chuỗi, một biểu thức so sánh, một giá trị logic hoặc một công thức khác.
- Hàm COUNTIFS có thể chứa tối đa 127 cặp vùng dữ liệu và tiêu chí.
Ví dụ: Cho bảng dữ liệu 8:
Yêu cầu: Đếm số nhân viên có doanh thu trên 80 triệu đồng và có số ngày công từ 23 ngày trở lên.
Cách làm:
- Bước 1: Nhập công thức sau vào ô C107:
=COUNTIFS(C102:C106;">80000000";D102:D106;">23").
Nhập công thức tính
Trong đó:
+ C102:C106: Là vùng dữ liệu chứa doanh số.
+ D102:D106: Là vùng dữ liệu chứa ngày công của các nhân viên.
- Bước 2: Nhấn enter, kết quả trả về là 2, tức là có 2 nhân viên có doanh thu trên 80 triệu đồng và có số ngày công từ 23 ngày trở lên.
Kết quả là có 2 nhân viên có doanh thu trên 80 triệu đồng và có số ngày công từ 23 ngày trở lên
9. Hàm COUNTA (đếm số ô không trống trong một phạm vi)
Hàm COUNTA là một hàm thống kê trong excel, cho phép bạn đếm số ô không trống trong một vùng dữ liệu, bao gồm cả các ô chứa số, chuỗi, logic, lỗi hoặc công thức.
Công thức của hàm tính lương thưởng trong excel COUNTA là:
=COUNTA(value1,value2,...)
Trong đó:
- value1, value2,… là các giá trị hoặc vùng dữ liệu cần đếm, có thể là một số, một chuỗi, một giá trị logic, một lỗi, một công thức, một ô, một dãy ô hoặc một tên miền.
- Hàm COUNTA có thể chứa tối đa 255 giá trị.
Ví dụ: Cho bảng dữ liệu 9:
Yêu cầu: Đếm số nhân viên có nhập thông tin doanh số trong bảng dữ liệu 9.
Cách làm:
- Bước 1: Ở ô B132, bạn nhập công thức =COUNTA(B124:B131).
Nhập công thức COUNTA
- Bước 2: Nhấn enter, kết quả thu được là 6 tức là chỉ có 6 nhân viên trên tổng số 8 người nhập dữ liệu về doanh số trong bảng.
Kết quả thu được là 6 tức là chỉ có 6 nhân viên trên tổng số 8 người nhập dữ liệu về doanh số trong bảng
10. Hàm tính công lương trong excel: Sum
Hàm SUM là một hàm toán học cơ bản trong excel, cho phép bạn tính tổng các giá trị số trong một vùng dữ liệu.
Công thức của hàm tính lương thưởng trong excel SUM là:
=SUM(number1,number2,...)
Trong đó:
- number1, number2,… là các giá trị số cần tính tổng, có thể là một số, một ô, một dãy ô hoặc một công thức khác.
- Hàm SUM có thể chứa tối đa 255 giá trị.
Ví dụ: Cho bảng dữ liệu 10:
Yêu cầu: Tính tổng lương thực tế mà các nhân viên được nhân.
Cách làm:
- Bước 1: Nhập công thức =SUM(E144:E148) ở ô E149.
Nhập công thức SUM
- Bước 2: Nhấn enter, kết quả Excel trả về là 138950000 VNĐ.
Kết quả Excel trả về là 138950000 VNĐ
11. Hàm Sumif (Tính tổng có điều kiện)
Hàm SUMIF là một hàm toán học trong excel, cho phép bạn tính tổng các giá trị số thỏa mãn một điều kiện trong một vùng dữ liệu.
Công thức của hàm tính lương thưởng trong excel SUMIF là:
=SUMIF(range,criteria,[sum_range])
Trong đó:
- range là vùng dữ liệu cần kiểm tra điều kiện, có thể là một ô, một dãy ô hoặc một tên miền.
- criteria là điều kiện cần kiểm tra, có thể là một số, một chuỗi, một biểu thức so sánh, một giá trị logic hoặc một công thức khác.
- [sum_range] là vùng dữ liệu cần tính tổng, có thể là một ô, một dãy ô hoặc một tên miền. Nếu không nhập [sum_range], hàm SUMIF sẽ tính tổng trên cùng vùng dữ liệu với range.
Ví dụ: Cho bảng dữ liệu 11:
Yêu cầu: Tính tổng lương của các nhân viên có số ngày công trên 25 ngày.
Cách làm:
- Bước 1: Ở ô E161, bạn nhập công thức =SUMIF(D156:D160;">25";E156:E160).
Nhập công thức hàm tính SUMIF
- Bước 2: Nhấn enter, kết quả thu được là 138950000 VNĐ. Tức là tổng lương của các nhân viên có số ngày công trên 25 ngày là 138950000 VNĐ.
Tổng lương của các nhân viên có số ngày công trên 25 ngày là 138950000 VNĐ
12. Hàm Sumifs (Tính tổng có nhiều điều kiện)
Hàm SUMIFS là một hàm toán học trong excel, cho phép bạn tính tổng các giá trị số thỏa mãn nhiều điều kiện trong nhiều vùng dữ liệu khác nhau.
Công thức của hàm SUMIFS là:
=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...)
Trong đó:
- sum_range là vùng dữ liệu cần tính tổng, có thể là một ô, một dãy ô hoặc một tên miền.
- criteria_range1, criteria_range2,… là các vùng dữ liệu cần kiểm tra điều kiện, có thể là một ô, một dãy ô hoặc một tên miền.
- criteria1, criteria2,… là các điều kiện cần kiểm tra, có thể là một số, một chuỗi, một biểu thức so sánh, một giá trị logic hoặc một công thức khác.
- Hàm SUMIFS có thể chứa tối đa 127 cặp vùng dữ liệu và tiêu chí.
Ví dụ: Cho bảng dữ liệu 12:
Yêu cầu: Tính tổng lương của các nhân viên có doanh số trên 80000000 VNĐ và có ngày công lớn hơn hoặc bằng 25 ngày.
Cách làm:
- Bước 1: Nhập hàm tính tiền lương trong excel dưới đây vào ô E176:
=SUMIFS(E171:E175;C171:C175;">80000000";D171:D175;">=25").
Nhập công thức SUMIFS vào ô E176
- Bước 2: Nhấn enter, kết quả bạn nhận về sẽ là 123000000 VNĐ. Tức là tổng lương thực tế của các nhân viên có doanh số trên 80000000 VNĐ và có ngày công lớn hơn hoặc bằng 25 ngày và 123000000 VNĐ.
Kết quả tính toán
13. Sử dụng hàm VLOOKUP
Hàm VLOOKUP là một hàm tham chiếu trong excel, cho phép bạn tìm kiếm một giá trị trong một cột bên trái của một bảng dữ liệu, và trả về giá trị tương ứng trong một cột khác của bảng đó.
Công thức của hàm tính lương thưởng trong excel VLOOKUP là:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Trong đó:
- lookup_value là giá trị cần tìm kiếm, có thể là một số, một chuỗi, một ô, một giá trị logic hoặc một công thức khác.
- table_array là bảng dữ liệu chứa giá trị tìm kiếm và kết quả cần tìm, có thể là một dãy ô hoặc một tên miền. Giá trị tìm kiếm phải nằm trong cột bên trái nhất của bảng dữ liệu.
- col_index_num là cột chứa kết quả cần tìm, là số nguyên dương chỉ vị trí của cột trong bảng dữ liệu.
- [range_lookup] là phương thức tìm kiếm, có thể là TRUE, FALSE hoặc không nhập. Nếu là TRUE hoặc không nhập, hàm VLOOKUP sẽ tìm kiếm theo phương pháp tương đối, tức là sẽ trả về giá trị gần nhất với giá trị tìm kiếm nếu không có giá trị chính xác. Điều này yêu cầu bảng dữ liệu phải được sắp xếp theo thứ tự tăng dần của cột chứa giá trị tìm kiếm. Nếu là FALSE, hàm VLOOKUP sẽ tìm kiếm theo phương pháp chính xác, tức là chỉ trả về giá trị khi có giá trị chính xác khớp với giá trị tìm kiếm. Điều này không yêu cầu bảng dữ liệu phải được sắp xếp theo thứ tự nào.
Ví dụ: Cho bảng số liệu 13 và 14:
Yêu cầu: Tính phần thành tiền của từng loại quả theo công thức:
- Thành tiền = Số lượng x Giá bán
Dùng hàm Vlookup để tìm giá bán theo xếp hạng ở bảng số 14.
Cách làm:
- Bước 1: Nhập công thức sau vào ô D186:
=C186*VLOOKUP(B186;$A$194:$B$196;2;1).
Nhập công thức tính vào ô D186
- Bước 2: Nhấn enter, kết quả thu được là 360000 VNĐ.
Kết quả sau khi nhập công thức
- Bước 3: Kéo thả chuột xuống bên dưới, bảng kết quả bạn nhận được là:
14. Sử dụng hàm INDEX kết hợp hàm MATCH
Hàm INDEX và hàm MATCH là hai hàm tham chiếu trong excel, cho phép bạn tìm kiếm một giá trị trong một bảng dữ liệu, và trả về giá trị tương ứng trong một cột khác của bảng đó.
Công thức của hàm INDEX là:
=INDEX(array,row_num,[column_num])
Trong đó:
- array là bảng dữ liệu chứa kết quả cần tìm, có thể là một dãy ô hoặc một tên miền.
- row_num là số dòng chứa kết quả cần tìm, là số nguyên dương chỉ vị trí của dòng trong bảng dữ liệu.
- [column_num] là số cột chứa kết quả cần tìm, là số nguyên dương chỉ vị trí của cột trong bảng dữ liệu. Nếu không nhập [column_num], hàm INDEX sẽ trả về giá trị trong cột đầu tiên của bảng dữ liệu.
Công thức của hàm INDEX
Công thức của hàm MATCH là:
=MATCH(lookup_value,lookup_array,[match_type])
Trong đó:
- lookup_value là giá trị cần tìm kiếm, có thể là một số, một chuỗi, một ô, một giá trị logic hoặc một công thức khác.
- lookup_array là vùng dữ liệu chứa giá trị tìm kiếm, có thể là một ô, một dãy ô hoặc một tên miền. Giá trị tìm kiếm phải nằm trong cột bên trái nhất hoặc hàng trên cùng của vùng dữ liệu.
- [match_type] là phương thức tìm kiếm, có thể là 1, 0 hoặc -1. Nếu là 1 hoặc không nhập, hàm MATCH sẽ tìm kiếm theo phương pháp tương đối lớn nhất, tức là sẽ trả về giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tìm kiếm. Điều này yêu cầu vùng dữ liệu phải được sắp xếp theo thứ tự tăng dần. Nếu là 0, hàm MATCH sẽ tìm kiếm theo phương pháp chính xác, tức là chỉ trả về giá trị khi có giá trị chính xác khớp với giá trị tìm kiếm. Điều này không yêu cầu vùng dữ liệu phải được sắp xếp theo thứ tự nào. Nếu là -1, hàm MATCH sẽ tìm kiếm theo phương pháp tương đối nhỏ nhất, tức là sẽ trả về giá trị nhỏ nhất lớn hơn hoặc bằng giá trị tìm kiếm. Điều này yêu cầu vùng dữ liệu phải được sắp xếp theo thứ tự giảm dần.
Công thức của hàm MATCH
Cách kết hợp hàm INDEX và hàm MATCH trong excel là gì?
Khi kết hợp hàm INDEX và hàm MATCH trong excel, bạn có thể tạo ra một công thức tra cứu linh hoạt và mạnh mẽ, có thể thay thế cho hàm VLOOKUP hay HLOOKUP trong nhiều trường hợp.
Công thức kết hợp hàm INDEX và hàm MATCH trong excel có dạng như sau:
=INDEX(array,MATCH(lookup_value,row_lookup_array,[match_type]),MATCH(lookup_value,column_lookup_array,[match_type]))
Trong đó:
- array là bảng dữ liệu chứa kết quả cần tìm, có thể là một dãy ô hoặc một tên miền.
- lookup_value là giá trị cần tìm kiếm, có thể là một số, một chuỗi, một ô, một giá trị logic hoặc một công thức khác.
- row_lookup_array là vùng dữ liệu chứa giá trị tìm kiếm theo hàng, có thể là một ô, một dãy ô hoặc một tên miền. Giá trị tìm kiếm phải nằm trong hàng trên cùng của vùng dữ liệu.
- column_lookup_array là vùng dữ liệu chứa giá trị tìm kiếm theo cột, có thể là một ô, một dãy ô hoặc một tên miền. Giá trị tìm kiếm phải nằm trong cột bên trái nhất của vùng dữ liệu.
- [match_type] là phương thức tìm kiếm, có thể là 1, 0 hoặc -1. Nếu là 1 hoặc không nhập, hàm MATCH sẽ tìm kiếm theo phương pháp tương đối lớn nhất, tức là sẽ trả về giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tìm kiếm. Điều này yêu cầu vùng dữ liệu phải được sắp xếp theo thứ tự tăng dần. Nếu là 0, hàm MATCH sẽ tìm kiếm theo phương pháp chính xác, tức là chỉ trả về giá trị khi có giá trị chính xác khớp với giá trị tìm kiếm. Điều này không yêu cầu vùng dữ liệu phải được sắp xếp theo thứ tự nào. Nếu là -1, hàm MATCH sẽ tìm kiếm theo phương pháp tương đối nhỏ nhất, tức là sẽ trả về giá trị nhỏ nhất lớn hơn hoặc bằng giá trị tìm kiếm. Điều này yêu cầu vùng dữ liệu phải được sắp xếp theo thứ tự giảm dần.
Ví dụ: Tính thưởng doanh số của từng nhân viên trong bảng ví dụ.
Cách làm:
- Bước 1: Nhập công thức sau vào ô tính thưởng doanh số của nhân viên Nguyễn Văn A:
J7=INDEX($C$16:$C$19,MATCH(I7,$B$16:$B$19,1))*I7.
- Bước 2: Kết quả tính toán như hình dưới đây:
Tags: Bán hàng Online
