Hàm IF là một hàm rất phổ biến trên Excel dùng để diễn đạt logic trên công thức. Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn cách tính hoa hồng bán hàng bằng hàm IF trên Excel để giúp các bạn làm quen với việc diễn đạt logic trên công thức.

1. Yêu cầu

Chúng ta có 1 bảng theo dõi doanh số bán hàng của nhân viên và 1 bảng quy định mức hoa hồng trên doanh số bán hàng như sau:

Yêu cầu: tính hoa hồng bán hàng cho từng nhân viên căn cứ vào bảng mức hoa hồng.

2. Phân tích yêu cầu

Trước khi bắt tay vào việc viết hàm, công thức, chúng ta nên làm tốt bước Phân tích yêu cầu. Bởi khi đã xác định đúng logic của bài toán thì việc thực hiện sẽ không có gì khó khăn.

Cụ thể ở đây chúng ta cần phải phân tích được logic của việc tính hoa hồng trong bảng 2.

• Nếu doanh số nhỏ hơn mức 1 thì không được thưởng
• Tại mức 1: doanh số đạt từ 400 triệu tới dưới 600 triệu, mức hoa hồng là 1% của doanh số
• Tại mức 2: doanh số đạt từ 600 triệu tới dưới 800 triệu, mức hoa hồng là 1,5% của doanh số
• Tại mức 3: doanh số đạt từ 800 triệu trở lên thì mức hoa hồng là 2% trên doanh số, ngoài ra sẽ thưởng thêm 3% trên số vượt mức 800 triệu

Theo logic trên, chúng ta cần chú ý làm rõ hơn ở mức 3, được hiểu là nếu doanh số đạt trên 800 triệu thì sẽ tính như sau: =(800 triệu * 2% ) + (số vượt 800 triệu * 3%)

3. Xây dựng công thức tính

Chúng ta sẽ xây dựng công thức tính cho bảng 1, công thức sẽ đặt tại cột D, bắt đầu từ dòng 3.

Tại ô D3 chúng ta xét như sau:

• Nếu doanh số (ô C3) < Mức 1 (ô F3) thì sẽ tính là 0 tương đương với IF(C3<F3,0,kết quả nếu C3>=F3)
• Nếu C3>=F3 thì chúng ta phải xét theo các mức, cụ thể:
• Nếu C3>=F3 và C3<G3 thì tính theo hoa hồng mức 1, tương đương với IF(AND(C3>=F3,C3<G3),C3*F4,…)
• Nếu C3>=G3 và C3<H3 thì tính theo hoa hồng mức 2, tương đương với IF(AND(C3>=G3,C3<H3),C3*G4,…)
• Nếu C3>=H3 thì tính theo hoa hồng mức 3, tương đương với IF(C3>=H3,(H3*H4)+(C3-H3)*H5,…)
• Do đã biện luận hết các trường hợp nên ở hàm IF cuối cùng chúng ta có thể chỉ cần viết công thức tính ở mệnh đề đúng, không cần viết cả hàm IF (được hiểu là trường hợp còn lại khi tất cả các hàm IF trước đó đều không đúng)

Lồng các hàm IF lại chúng ta được

D3=IF(C3<F3,0,IF(AND(C3>=F3,C3<G3),C3*F4,IF(AND(C3>=G3,C3<H3),C3*G4,(H3*H4)+(C3-H3)*H5)))

Vì sử dụng 3 hàm IF nên cần đóng 3 dấu ngoặc ở cuối

Do bảng mức hoa hồng là cố định, không thay đổi, trong khi công thức tính sẽ thay đổi cho từng nhân viên (C3 sẽ thay đổi, còn các vị trí ở cột F, G, H không đổi) do đó sẽ cố định các vị trí ở cột F, G, H như sau:

D3=IF(C3<$F$3,0,IF(AND(C3>=$F$3,C3<$G$3),C3*$F$4,IF(AND(C3>=$G$3,C3<$H$3),C3*$G$4,($H$3*$H$4)+(C3-$H$3)*$H$5)))

Kết quả như sau:

4. Chú ý

• Khi phân tích yêu cầu đề bài, chúng ta cần làm rõ được là trong logical_test của hàm IF có gồm nhiều điều kiện không để có thể sử dụng thêm các hàm AND / OR để biện luận
• Những đối tượng tham chiếu bên ngoài (bảng Mức hoa hồng) sẽ được cố định các vị trí để khi sao chép công thức cho các đối tượng khác trong cột kết quả thì không làm ảnh hưởng tới công thức.
• Hàm IF lồng thường là dài, khó nhớ nên cần đặc biệt chú ý việc phân tích yêu cầu để có thể xác định đúng logic và thứ tự các thành phần hàm IF để đặt vào đúng vị trí.

Tải về file mẫu: http://bit.ly/2wZTROd

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