Cách tính hoa hồng bán hàng bằng hàm IF lồng nhau trên Excel – Học Excel Online Miễn Phí

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 thu bán hàng của nhân viên cấp dưới và 1 bảng lao lý mức hoa hồng trên doanh thu bán hàng như sau :

Mọi Người Cũng Xem   Cách Tính Ma Trận Bằng Máy Tính Fx 570Es Plus Nhanh Nhất, Các Trò Vui Với Máy Tính Bỏ Túi Casio

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, tất cả chúng ta nên làm tốt bước Phân tích nhu yếu. Bởi khi đã xác lập đúng logic của bài toán thì việc thực thi sẽ không có gì khó khăn vất vả .
Cụ thể ở đây tất cả chúng ta cần phải nghiên cứu và 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, tất cả chúng ta cần chú ý quan tâm làm rõ hơn ở mức 3, được hiểu là nếu doanh thu đạ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ẽ kiến thiết 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, mở màn từ dòng 3 .
Tại ô D3 tất cả 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)
  • 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=F3,C3
  • Nếu C3>=G3 và C3

    =G3,C3
  • 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,C3=G3,C3 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 và thắt chặt, không biến hóa, trong khi công thức tính sẽ biến hóa cho từng nhân viên cấp dưới ( C3 sẽ đổi khác, còn các vị trí ở cột F, G, H không đổi ) do đó sẽ cố định và thắt chặt các vị trí ở cột F, G, H như sau :
D3 = IF ( C3 < $ F $ 3,0, IF ( AND ( C3 > = USD F USD 3, C3 < USD G USD 3 ), C3 * USD F USD 4, IF ( AND ( C3 > = USD G USD 3, C3 < $ H $ 3 ), C3 * USD G USD 4, ( $ H $ 3 * USD H $ 4 ) + ( C3 - USD H $ 3 ) * USD H USD 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

Mọi Người Cũng Xem   Cách tính thuế ô tô ở Việt Nam và giá thực trả của khách hàng khi mua xe

Related Posts

About The Author

Add Comment