Blog Tin học văn phòng

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:

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 tính toán từ cơ bản đến 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.
Tích hợp công thức và dữ liệu: Dễ dàng 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.
Theo dõi lịch trình và thay đổi: Excel cho phép tạo các biểu đồ, đồ thị để theo dõi thay đổi lương theo thời gian hoặc các 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

Tạo báo cáo và tài liệu: Dễ dàng và 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.
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.
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.
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.

Việc sử dụng Excel giúp tiết kiệm thời gian

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

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.

>>> 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"

ĐĂNG KÝ MUA NGAY

Các hàm tính lương thưởng trong excel

Cách dùng hàm IF tính lương thưởng trong Excel

Một trong những hàm cơ bản và rất hay sử dụng trong mọi trường hợp, chúng ta phải kể đến hàm IF. Trước khi bắt đầu sử dụng hàm IF, chúng ta cần diễn giải đúng logic bằng lời trước khi viết ra công thức.

Cách dùng hàm IF tính lương thưởng trong Excel

Cách dùng hàm IF tính lương thưởng trong Excel

Ví dụ cho bảng số liệu như sau, ta có:

Nếu doanh số dưới 100 triệu thì không được thưởng nên % doanh số là 0%.
Nếu doanh số đạt từ 100 triệu đến dưới 150 triệu thì được thưởng 1% doanh số nên % doanh số là 1%.
Nếu doanh số đạt từ 150 triệu đến dưới 200 triệu thì được thưởng 1,5% doanh số nên % doanh số là 1,5%.
Nếu doanh số đạt từ 200 triệu trở lên thì được thưởng 2% doanh số nên % doanh số là 2%.

Như vậy, để làm biểu thức cần 3 hàm IF lồng nhau, công thức như sau:

IF(I7<$B$17,$B$16,IF(17<$B$B18,$C$17,IF(I7<$B$19,$C$18,$C$19)))*I7

Kiểm tra kỹ lại công thức rồi áp dụng tương tự cho các dòng tiếp theo.

Lưu ý: Để sao chép công thức xuống các dòng bên dưới thì trong công thức cần cố định tọa độ các tham chiếu. Bạn có thể tạo bảng phụ để trực tiếp tham chiếu mà không cần viết giá trị cụ thể trong hàm IF, giúp tránh làm rối công thức.

Cách dùng hàm VLOOKUP tính lương thưởng trong Excel

Bên cạnh hàm IF thì hàm VLOOKUP cũng là một hàm rất hay sử dụng để tính lương thưởng trong Excel. Trước khi sử dụng hàm Vlookup, chúng ta cần phải nắm được công thức và xác định các đối tượng trong hàm như sau:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Cách dùng hàm VLOOKUP tính lương thưởng trong Excel

Cách dùng hàm VLOOKUP tính lương thưởng trong Excel

Vẫn dựa vào ví trị trên, các giá trị tương ứng như sau:

Lookup_value: Giá trị tìm kiếm. Để tính lương thưởng, chúng ta cần tham chiếu Doanh số với bảng Thưởng doanh số, vì vậy mà giá trị tìm kiếm sẽ là doanh số tương ứng trên cùng dòng đặt ra.
Table_array: Bảng chứa giá trị tìm kiếm và kết quả cần tìm. Bảng này sẽ là bảng Thưởng doanh số từ B16:C19.
Col_index_num: Cột chứa kết quả là cột bao nhiêu trong able_array. Ví dụ ở đây có thể xác định cột doanh số là cột chứa các giá trị cần tìm.
[range_lookup]: Phương thức tìm kiếm. Ở đây bảng table_array được xác định theo khoảng và tăng dần, nên phương thức tìm kiếm là tương đối. Có thể không nhập giá trị hoặc nhập giá trị bằng 1.

Như vậy, công thức VLOOKUP sẽ được biểu hiện như sau:

=VLOOKUP(I7,$B$16:$C$19,2,1)*I7

Cách dùng hàm INDEX kết hợp hàm MATCH tính lương thưởng trong Excel

Nếu như hàm Vlookup chỉ giới hạn tìm kiếm từ trái qua phải, thì hàm INDEX và MATCH là hai hàm tìm kiếm và tham chiếu rất mạnh trong Excel. Công thức kết hợp hàm INDEX và MATCH như sau:

=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), [column_num])

Trong đó:

Array: Phạm vi ô mà bạn muốn trả về giá trị từ trước.
Lookup_value: Giá trị tìm kiếm là giá trị doanh số.
Lookup_array: Vùng tham chiếu, ở đây sẽ tham chiếu từ B16:B19.
[match_type]: Phương thức tìm kiếm tương đối và tăng dần, nhập số 1 tại đây.

Cách dùng hàm INDEX kết hợp hàm MATCH

Cách dùng hàm INDEX kết hợp hàm MATCH

Từ đó chúng ta có công thức hàm như sau:

J7=INDEX($C$16:$C$19,MATCH(I7,$B$16:$B$19,1))*I7

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à:

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à:

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à:

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:

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

Hàm COUNTIF (Đếm các ô dựa trên nhiều tiêu chí/đ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

Hàm COUNTIFS (Đếm các ô dựa trên nhiều tiêu chí/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

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

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Đ

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Đ

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

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à:

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.

Những lỗi thường gặp khi tính lương thưởng bằng hàm IF trong Excel

Khi sử dụng hàm IF trong Excel, chúng ta thường gặp phải một số lỗi phổ biến sau:

Sai hoặc thiếu cấu trúc hàm IF: Một trong những nguyên nhân khiến hàm IF không hoạt động là do người dùng chưa hiểu kỹ hoặc gõ đúng hàm IF.
Có một và chỉ một điều kiện về hàm IF: Nếu chỉ có một điều kiện trong hàm IF thì kết quả sẽ trở về giá trị đơn giản thay vì được tính toán như ban đầu.
Không sử dụng hàm ROUND: Trong quá trình tính lương thưởng, để hạn chế sai lệch, bạn nên sử dụng thêm hàm ROUND để làm tròn dữ liệu.
Dùng quá nhiều điều kiện lồng nhau trong hàm IF: Nếu hàm IF có quá nhiều điều kiện sẽ gây ra hiệu suất chậm, gây ra việc khó quản lý và dễ gặp phải lỗi

[Tổng số: 45 Trung bình: 2]

Tags: Excel

Tác giả

Hoàng Giang Nam CEO - Công ty TNHH Vạn Phát Group

Giảng Viên - Công ty Cổ Phần Tập đoàn Kế toán Hà Nội CEO - Công ty TNHH Vạn Phát Group Cố vấn tài chính cho nhiều công ty tại thành phố HCM Trên 10 năm kinh nghiệm kế toán và...