Hàm IF – các công thức được kết hợp với nhau và tránh các rắc rối

Ghi chú

Dù Excel cho phép bạn lồng đến 64 hàm IF khác nhau thì bạn cũng không nên làm như vậy. Tại sao?

• Nhiều câu lệnh IF sẽ yêu cầu bạn suy nghĩ rất lâu để xây dựng chính xác và đảm bảo rằng lô-gic của câu lệnh có thể tính toán chính xác qua từng điều kiện cho đến khi kết thúc. Nếu bạn không lồng công thức chính xác 100 % thì khả năng 75 % là công thức có thể hoạt động được nhưng 25 % sẽ trả về kết quả không mong muốn. Rất tiếc, khả năng bạn tìm ra nguyên nhân của 25 % đó là rất nhỏ.

• Nhiều câu lệnh IF có thể rất khó để duy trì, đặc biệt là khi bạn quay lại một thời gian sau đó và cố gắng tìm hiểu điều mà bạn hoặc tệ hơn là một người khác, đang cố gắng thực hiện.

Nếu bạn gặp một câu lệnh IF mà có vẻ như kéo dài mãi thì bạn nên tạm dừng và xem lại chiến lược của mình.

Hãy xem cách phù hợp để tạo một câu lệnh IF được lồng phức tạp sử dụng nhiều hàm IF và thời điểm cần để nhận ra rằng đã đến lúc sử dụng một công cụ khác trong kho Excel của bạn.

Ví dụ

Dưới đây là một ví dụ về một câu lệnh IF được lồng tương đối chuẩn để chuyển đổi các điểm kiểm tra của học viên thành điểm theo chữ cái tương đương.

• =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

  Câu lệnh IF được lồng phức tạp này tuân theo lô-gic dễ hiểu:

1. Nếu Điểm Kiểm tra (trong ô D2) lớn hơn 89 thì học viên nhận điểm A

2. Nếu Điểm Kiểm tra lớn hơn 79 thì học viên nhận điểm B

3. Nếu Điểm Kiểm tra lớn hơn 69 thì học viên nhận điểm C

4. Nếu Điểm Kiểm tra lớn hơn 59 thì học viên nhận điểm D

5. Các trường hợp khác học viên nhận điểm F

Ví dụ cụ thể này tương đối an toàn vì không có khả năng mà sự tương quan giữa điểm kiểm tra và điểm theo chữ cái sẽ thay đổi nên sẽ không yêu cầu duy trì nhiều. Nhưng còn một vấn đề nữa – điều gì sẽ xảy ra nếu bạn cần phân đoạn các điểm giữa A+, A và A- (cùng các điểm khác)? Bây giờ, bạn cần viết lại câu lệnh IF bốn điều kiện của mình thành câu lệnh có 12 điều kiện! Lúc này, công thức của bạn sẽ có dạng như sau:

• =IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-", IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))

Câu lệnh này vẫn chính xác về mặt chức năng và sẽ hoạt động như dự kiến nhưng có thể mất nhiều thời gian để viết và nhiều thời gian hơn để kiểm tra nhằm đảm bảo rằng câu lệnh thực hiện được những gì bạn mong muốn. Một vấn đề hiển nhiên khác là bạn đã phải nhập điểm và điểm theo chữ cái tương đương bằng cách thủ công. Điều gì sẽ xảy ra khi bạn vô tình tạo lỗi đánh máy? Bây giờ, hãy tưởng tượng bạn đang cố gắng thực hiện điều này 64 lần với các điều kiện phức tạp hơn! Tất nhiên, điều đó là có thể nhưng bạn có thực sự muốn mất công sức như vậy và gặp những lỗi thực sự khó phát hiện có thể gặp không?

Ví dụ bổ sung

Dưới đây là ví dụ rất phổ biến khi tính toán Tiền hoa hồng Bán hàng dựa theo các mức Doanh thu đạt được.

• =IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))

Công thức này cho biết IF(C9 Lớn Hơn 15.000 thì trả về 20 %, IF(C9 Lớn Hơn 12.500 thì trả về 17,5 %, v.v...

Mặc dù ví dụ này khá giống với ví dụ về Điểm trước đây nhưng công thức này vẫn là một ví dụ tuyệt vời về việc duy trì các câu lệnh IF lớn khó khăn như thế nào – bạn cần làm gì nếu tổ chức của mình quyết định thêm các mức độ bồi thường mới và thậm chí có thể thay đổi các giá trị phần trăm hoặc đô la hiện có? Bạn sẽ phải chịu trách nhiệm xử lý nhiều công việc!

Dưới đây là ví dụ về kịch bản tiền hoa hồng với lô-gic không theo thứ tự:

Bạn có thể nhìn thấy lỗi không? So sánh thứ tự của các so sánh Doanh thu cho ví dụ trước đó. Đây là cách so sánh nào? Chính xác, thứ tự này từ thấp đến cao ($5.000 đến $15.000), không thể so sánh được. Nhưng tại sao đó lại là vấn đề quan trọng? Đây là vấn đề quan trọng vì công thức không thể chuyển đánh giá đầu tiên cho mọi giá trị trên $5.000. Hãy tưởng tượng bạn có doanh thu $12.500 – câu lệnh IF sẽ trả về 10 % vì câu lệnh lớn hơn $5.000 và sẽ dừng lại ở đó. Điều này có thể rất nghiêm trọng vì trong rất nhiều tình huống, những loại lỗi này không được thông báo cho đến khi chúng gây ảnh hưởng tiêu cực. Vì vậy, khi biết rằng có một số bẫy nghiêm trọng với các câu lệnh IF phức tạp lồng nhau, bạn có thể làm gì? Trong hầu hết các trường hợp, bạn có thể sử dụng hàm VLOOKUP thay vì tạo công thức phức tạp bằng hàm IF. Khi sử dụng hàm VLOOKUP, trước tiên, bạn cần tạo bảng tham chiếu sau:

• =VLOOKUP(C2,C5:D17,2,TRUE)

Công thức này cho biết có thể tìm giá trị ở ô C2 trong dải ô C5:C17. Nếu tìm thấy giá trị thì trả về giá trị tương ứng từ cùng một hàng trong cột D.

• =VLOOKUP(B9,B2:C6,2,TRUE)

Tương tự, công thức này tìm giá trị ở ô B9 trong dải ô B2:B22. Nếu tìm thấy giá trị thì trả về giá trị tương ứng từ cùng một hàng trong cột C

VLOOKUP được trình bày chi tiết hơn ở đây, nhưng điều này chắc chắn đơn giản hơn nhiều so với câu lệnh IF lồng 12 mức, phức tạp! Có những lợi ích khác ít rõ ràng hơn như sau:

• Bảng tham chiếu VLOOKUP rất rõ ràng và dễ xem.

• Có thể dễ dàng cập nhật các giá trị trong bảng và bạn không bao giờ phải chỉnh sửa công thức nếu các điều kiện thay đổi.

• Nếu bạn không muốn mọi người xem hoặc can thiệp vào bảng tham chiếu của mình, chỉ cần đặt bảng tham chiếu vào một trang tính khác.

Bạn có biết?

Hiện có một hàm IFS có thể thay thế nhiều câu lệnh IF lồng nhau bằng một hàm duy nhất. Vì vậy, thay vì ví dụ của chúng tôi về các điểm ban đầu chứa 4 hàm IF lồng nhau:

• =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

Bạn có thể tạo ra công thức đơn giản hơn nhiều chỉ với một hàm IFS duy nhất:

• =IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")

Hàm IFS rất tuyệt vì bạn không cần phải lo lắng về tất cả các câu lệnh IF và dấu ngoặc tròn đó.

Bạn cần thêm trợ giúp?

Bạn luôn có thể hỏi chuyên gia trong Cộng đồng kỹ thuật Excel hoặc nhận hỗ trợ trong Cộng đồng.

Chủ đề liên quan

Video: Hàm IF nâng cao
Hàm IFS (Microsoft 365, Excel 2016 trở lên)
Hàm COUNTIF
sẽ đếm các giá trị dựa trên một tiêu chí duy nhất HàmCOUNTIFS
sẽ đếm giá trị dựa trên nhiều tiêu chí HàmSUMIF
sẽ tính tổng các giá trị dựa trên một tiêu chí duy nhất HàmSUMIFS
sẽ tính tổng các giá trị dựa trên nhiều tiêu chí hàmAND hàm
OR
hàm VLOOKUP
Tổng quan về các côngthức trong Excel Cách tránh công thức bị lỗi Phát hiện lỗi trong công thức Hàm lô-gicCác hàm Excel ( theo bảng chữ cái )
Các hàm Excel



(theo thể loại)