Tiền thưởng là một yếu tố quan trọng trong tiền lương của nhân sự. Vậy trong Excel thường dùng công thức nào để tính tiền thưởng? Sau đây Học Excel Online sẽ hướng dẫn các bạn 3 cách tính tiền thưởng doanh thu trên bảng lương Excel.

Chúng ta sẽ tìm hiểu nội dung này thông qua một ví dụ như sau:

Trong bảng lương dưới đây có bao gồm tiền Doanh số cho bộ phận kinh doanh. Hãy xác định thưởng doanh số biết tiêu chí xác định mức thưởng được quy định ở bảng Thưởng doanh số (B14:C19):

Cách thứ 1: Dùng hàm IF

Đây là cách cơ bản và thường được sử dụng trong trường hợp chúng ta chỉ biết về hàm IF. Cách này làm dựa vào việc diễn giải lại logic bằng hàm IF. Do đó điều quan trọng là phải diễn giải đúng logic bằng lời trước khi viết công thức. Trong trường hợp này logic được diễn đạt như sau:

• Nếu doanh số dưới 100 triệu thì % doanh số = 0% (không được thưởng)
• Nếu doanh số từ 100 triệu đến dưới 150 triệu thì % doanh số = 1% (lấy 1% nhân với doanh số để ra số thưởng)
• Nếu doanh số từ 150 triệu đến dưới 200 triệu thì % doanh số = 1,5% (lấy 1,5% nhân với doanh số để ra số thưởng)
• Còn lại nếu doanh số từ 200 triệu trở lên thì % doanh số = 2% (lấy 2% nhân với doanh số để ra số thưởng)

Như vậy cần tới 3 hàm IF lồng nhau. Tại ô J7 chúng ta có thể viết công thức như sau:

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

Áp dụng công thức từ J7 tới các dòng bên dưới tới J12. Lưu ý: để có thể copy công thức xuống các dòng bên dưới thì trong công thức cần lưu ý cố định tọa độ các tham chiếu. Trong bài này có sẵn bảng phụ nên chúng ta có thể tham chiếu trực tiếp tới bảng mà không cần viết cụ thể giá trị trong hàm IF.

Xem thêm: Hàm IF lồng ghép, kết hợp nhiều điều kiện trong một công thức duy nhất

Cách thứ 2: Sử dụng hàm VLOOKUP

Hàm VLOOKUP là hàm phổ biến dùng để tham chiếu giá trị từ 1 bảng dữ liệu. Để sử dụng hàm Vlookup trong trường hợp này thì chúng ta xác định các đối tượng trong hàm như sau:

• Lookup_Value: là giá trị tìm kiếm. Trong yêu cầu này chúng ta cần tham chiếu Doanh số tới bảng Thưởng doanh số, do đó giá trị tìm kiếm sẽ là giá trị doanh số tương ứng trên cùng dòng đặt kết quả.
• Table_array: là bảng chứa giá trị tìm kiếm và kết quả cần tìm. Bảng này chính là bảng Thưởng doanh số, vùng B16:C19
• Col_index_num: Cột chứa kết quả là cột thứ mấy trong table_array. Tại đây có thể xác định cột thứ 2 (% doanh số) là 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 dạng khoảng và tăng dần, nên phương thức tìm kiếm là tương đối. Có thể nhập giá trị là 1 hoặc không nhập giá trị.

Công thức VLOOKUP tại J7 có thể viết như sau:

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

Với cách sử dụng hàm Vlookup, chúng ta thấy cách viết dễ hơn nhiều so với dùng hàm IF.

Cách thứ 3: Sử dụng hàm INDEX kết hợp hàm MATCH

INDEX và MATCH là hai hàm tìm kiếm, tham chiếu rất mạnh trong Excel. Dùng INDEX và MATCH có thể thay thế hoàn toàn cho hàm VLOOKUP, thậm chí còn ưu việt hơn. Trong trường hợp này chúng ta cùng tìm hiểu cách làm bằng INDEX và MATCH nhé:

Để tìm hiểu kỹ hơn về 2 hàm này, mời các bạn tham khảo bài viết:

Vì sao dùng INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel

Kết quả cần tìm ở cột % doanh số. Do đó đối tượng đầu tiên trong hàm INDEX là C16:C19

Để xác định mức doanh số cần tìm nằm ở vị trí dòng nào trong vùng B16:B19 thì chúng ta dùng hàm MATCH. Lưu ý trong trường hợp này là tham chiếu tương đối theo thứ tự tăng dần:

• Lookup_value: giá trị tìm kiếm là giá trị doanh số I7 (khi đặt công thức ở dòng 7)
• Lookup_array: vùng tham chiếu là vùng B16:B19
• [match_type]: phương thức tìm kiếm tương đối và tăng dần nên nhập là số 1

Cách viết hàm này như sau:

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

Cả 3 cách đều cho chúng ta kết quả giống nhau. Khi sử dụng tham chiếu tới các bảng tính phụ (như bảng B16:C19) thì các tham chiếu đó đều phải được cố định bằng dấu $ trong công thức. Bởi khi áp dụng công thức xuống các dòng tiếp theo sẽ có thể sai kết quả do tham chiếu bị thay đổi khi không cố định vùng tham chiếu.

Ngoài 3 cách này, các bạn có thể tìm thêm một số cách khác không? Ví dụ như sử dụng hàm LOOKUP thay cho VLOOKUP có được không? Hãy chia sẻ những cách khác mà bạn khám phá được nhé.

Xem thêm:

Hướng dẫn cách xác định cơ chế tính lương theo phương pháp 3P

Ngoài ra Học Excel Online xin giới thiệu với các bạn Khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để giúp bạn làm việc trên Excel được tốt hơn, hiệu quả hơn. Chi tiết xem tại: