Bài hướng dẫn này, Học Excel Online sẽ giải thích cho các bạn cách dùng hàm IF với nhiều điều kiện trong Excel và cung cấp một số ví dụ về những hàm IF lồng nhau phổ biến nhất hay được dùng trong công việc.

Nếu được một người nào đó hỏi rằng chức năng nào trong Excel mà bạn dùng nhiều nhất, bạn sẽ trả lời như thế nào? Trong hầu hết các trường hợp, đó chính là hàm IF trong Excel. Hàm IF nếu chỉ có một điều kiện sẽ rất đơn giản và dễ viết. Nhưng điều gì sẽ xảy ra nếu dữ liệu của bạn cần phải được kiểm tra bằng thuật toán phức tạp với rất nhiều điều kiện? Trong trường hợp này, bạn có thể sử dụng nhiều hàm IF trong một công thức, và hàm IF lúc này được gọi là hàm IF lồng nhau. Ưu điểm lớn nhất của hàm IF lồng nhau là nó sẽ cho phép bạn kiểm tra nhiều hơn một điều kiện và được trả về các giá trị khác nhau tùy thuộc vào kết quả của các lần kiểm tra đó với một công thức duy nhất.

Trong các phiên bản hiện đại hơn của Excel 2016, Excel 2013, Excel 2010 và Excel 2007, bạn có thể lồng tối đa 64 hàm IF trong một công thức. Trong Excel 2003 trở về trước, chỉ có thể sử dụng tối đa 7 hàm IF lồng nhau.

Trong bài viết này, bạn sẽ tìm thấy những ví dụ về hàm IF lồng nhau cùng với phần giải thích chi tiết về cấu trúc cú pháp và logic của chúng.

Ví dụ 1: Công thức IF lồng nhau cổ điển/cơ bản

Giả sử bạn có danh sách học sinh nằm ở cột A và điểm thi của họ nằm ở cột B và bạn muốn phân loại điểm với các điều kiện sau:

• Excellent: trên 249
• Good: từ 200 đến 249
• Satisfactory: từ 150 đến 199
• Poor: dưới 150

Và bây giờ, chúng ta hãy cùng viết một hàm IF lồng nhau dựa trên các tiêu chí trên. Đây là một cơ hội thực hành tốt, bắt đầu với những điều kiện quan trọng nhất và viết hàm IF của bạn càng đơn giản càng tốt. Công thức IF lồng nhau được thể hiện như sau:

=IF(B2>249, “Excellent”, IF(B2>=200, “Good”, IF(B2>150, “Satisfactory”, “Poor “)))

Và kết quả chính xác sẽ là:

Cách hiểu logic của hàm IF lồng nhau:

Với nhiều người, hàm IF lồng nhau có thể rất khó hiểu. Nhưng sẽ đơn giản hơn nhiều nếu bạn nhìn nhận chúng theo cách này:

Trong thực tế, những gì công thức thực sự yêu cầu Excel làm là đánh giá kiểm tra tính logic của điều kiện đầu tiên của công thức hàm IF, nếu điều kiện logic, Excel sẽ trả về giá trị được cung cấp khi điều kiện được đáp ứng. Nếu điều kiện của hàm If thứ nhất không logic thì tiếp tục kiểm tra điều kiện thứ hai, điều kiện thứ thứ ba,…

Kiểm tra nếu B2 >= 249 thì trả về kết  quả “Excellent”, nếu không thì kiểm tra tiếp B2 >=200, nếu đúng thì trả về kết quả “Good”, nếu không thì kiểm tra tiếp B2 >=150, nếu đúng thì “Satisfactory”, nếu sai thì trả về “Poor”

Ví dụ 2. Hàm IF lồng nhau với các phép tính số học

Đây là một yêu cầu điển hình khác: đơn giá thay đổi tùy thuộc vào số lượng được chỉ định cụ thể và nhiệm vụ của bạn là viết công thức tính tổng giá cho số lượng mặt hàng bất kỳ và được nhập vào một ô nhất định. Nói cách khác, công thức của bạn cần phải kiểm tra nhiều điều kiện và thực hiện các phép tính khác nhau tùy thuộc vào phạm vi số lượng hàng và giá cả được chỉ định trong bảng sau:

Công việc này cũng có thể được thực hiện bằng cách sử dụng hàm IF lồng nhau. Cách hiểu logic tương tự như trong ví dụ trên, sự khác biệt duy nhất là bạn nhân số lượng được chỉ định với giá trị được trả về bởi các IF lồng nhau (tức là giá tương ứng trên mỗi đơn vị hàng hóa).

Giả sử người dùng nhập số lượng vào ô B8, công thức sẽ được thế hiện như sau:

=B8*IF(B8>=101, 12, IF(B8>=50, 13, IF(B8>=20, 16, IF( B8>=11, 18, IF(B8>=1, 20, “”)))))

Và kết quả sẽ được thể hiện tương tự như sau:

Ví dụ này chỉ trình bày cách làm chung và cơ bản, nếu bạn đã hiểu, bạn có thể dễ dàng tùy chỉnh hàm If lồng nhau này tùy thuộc vào công việc cụ thể của bạn.

Trong trường hợp: thay vì ấn định sẵn giá trong công thức, bạn có thể tham chiếu các ô chứa các giá trị đó (ô B2 đến B6). Cách này sẽ cho phép bạn chỉnh sửa dữ liệu nguồn mà không cần phải cập nhật công thức:

=B8*IF(B8>=101,B6, IF(B8>=50, B5, IF(B8>=20, B4, IF( B8>=11, B3, IF(B8>=1, B2, “”)))))

Hoặc bạn có thể thêm (các) điều kiện IF bổ sung để điều chỉnh giới hạn trên, dưới hoặc cả hai đầu của phạm vi số tiền. Khi số lượng nằm ngoài phạm vi, công thức sẽ hiển thị thông báo “out of the range”. Ví dụ:

=IF(OR(B8>200,B8<1), “Qty. out of range”, B8*IF(B8>=101,12, IF(B8>=50, 13, IF(B8>=20, 16, IF( B8>=11, 18, IF(B8>=1, 20, “”))))))

Các công thức hàm IF lồng nhau được mô tả ở trên đều hoạt động trong tất cả các phiên bản Excel 2016 – 2000. Trong Excel 2016 – Office 365, bạn có thể sử dụng hàm IFS theo các cách nêu trên.

Những người dùng Excel chuyên nghiệp đã quen thuộc với công thức mảng, về cơ bản có thể sử dụng công thức này thực hiện điều tương tự như hàm IF lồng nhau mà chúng ta đã thảo luận ở trên. Mặc dù công thức mảng khó hiểu và khó viết hơn nhiều nhưng nó có một lợi thế không thể chối cãi – bạn có thể chỉ định phạm vi ô (một dãy ô) chứa các điều kiện mình cần thay vì tham chiếu từng điều kiện riêng lẻ. Điều này làm cho công thức linh hoạt hơn và nếu người dùng thay đổi bất kỳ điều kiện hiện có nào hoặc thêm điều kiện mới, bạn sẽ chỉ phải cập nhật lại dãy ô trong công thức của mình.

Hàm IF lồng nhau trong Excel – mẹo và các thủ thuật

Như bạn vừa thấy, việc sử dụng hàm IF nhiều điều kiện trong Excel không đòi hỏi các phương pháp khoa học quá cao siêu. Các mẹo sau đây sẽ giúp bạn cải thiện các công thức IF lồng nhau và ngăn ngừa các lỗi thường gặp:

Giới hạn IF lồng nhau

Trong Excel 2016 – 2007, bạn có thể lồng tối đa 64 hàm IF hay kết hợp 64 điều kiện. Trong các phiên bản cũ hơn của Excel 2003 trở về trước, có thể sử dụng tối đa 7 hàm IF lồng nhau. Tuy nhiên, bạn có thể lồng nhiều điều kiện trong một công thức không có nghĩa là bạn nên làm như vậy. Xin lưu ý rằng lượt bổ sung điều kiện sẽ làm cho công thức của bạn khó hiểu và khó khắc phục lỗi hơn. Nếu công thức của bạn có quá nhiều điều kiện được kết hợp, bạn có thể tối ưu hóa nó bằng cách sử dụng một trong những lựa chọn thay thế sau đây.

Thứ tự của các điều kiện trong hàm IF lồng nhau rất quan trọng

Hàm IF lồng nhau trong Excel đánh giá tính logic theo thứ tự chúng xuất hiện trong công thức và ngay sau khi một trong các điều kiện đánh giá là TRUE, các điều kiện tiếp theo sau đó sẽ không được kiểm tra. Nói cách khác, công thức sẽ dừng sau khi kết quả là TRUE.

Hãy xem nó hoạt động như thế nào trong thực tế. Với B2 bằng 274, công thức IF lồng nhau bên dưới đánh giá tính logic của điều kiện đầu tiên (B2> 249) và trả về “Xuất sắc” vì kết quả kiểm tra tính logic này là TRUE:

=IF(B2>249, “Excellent”, IF(B2>=200, “Good”, IF(B2>150, “Satisfactory”, “Poor”)))

Bây giờ, hãy đảo ngược thứ tự của các điều kiện như sau:

=IF(B2>150, “Satisfactory”, IF(B2>200, “Good”, IF(B2>249, “Excellent”, “Poor”)))

Công thức sẽ kiểm tra điều kiện đầu tiên, bởi vì 274 > 150, kết quả của kiểm tra tính logic này cũng sẽ là TRUE. Do đó, công thức trả về kết quả “Satisfactory” mà không cần kiểm tra các điều kiện khác.

Bạn thấy đó, việc thay đổi thứ tự của các hàm IF sẽ thay đổi kết quả:

Đánh giá tính logic của công thức

Để theo dõi dòng logic của công thức hàm IF lồng nhau theo từng bước một, hãy sử dụng tính năng Evaluate Formula nằm trên tab Formula, trong nhóm Formula Auditing. Biểu thức được gạch chân là phần đang được đánh giá và việc nhấp vào nút Evaluate sẽ hiển thị cho bạn tất cả các bước trong quy trình đánh giá.

Ví dụ: việc đánh giá tính logic của điều kiện đầu tiên trong công thức IF lồng nhau được hiển thị trong ảnh bên dưới sẽ như sau: B2 > 249; 274 > 249; TRUE; Excellent.

Cân bằng dấu ngoặc của các hàm IF lồng nhau

Một trong những thách thức chính với hàm IF lồng nhau trong Excel là khớp các cặp dấu ngoặc đơn. Nếu dấu ngoặc đơn không khớp, công thức của bạn sẽ không hoạt động. Nhưng đừng lo, Microsoft Excel cung cấp một số tính năng có thể giúp bạn cân bằng các dấu ngoặc đơn khi chỉnh sửa công thức:

• Nếu bạn có nhiều hơn một tập hợp các dấu ngoặc đơn, thì các cặp dấu ngoặc đơn sẽ được tô bằng các màu khác nhau để dấu ngoặc mở đầu khớp với dấu ngoặc đóng.
• Khi bạn đóng dấu ngoặc đơn, Excel sẽ đánh dấu cặp phù hợp. Sẽ có hiệu ứng in đậm hoặc “nhấp nháy” khi bạn di chuyển qua công thức bằng cách sử dụng các phím mũi tên.

Cách xử lý các văn bản và các số là khác nhau

Khi xây dựng các bài kiểm tra tính logic cho các hàm IF lồng nhau của bạn, hãy nhớ rằng văn bản và số phải được xử lý theo cách khác nhau – luôn đặt các giá trị văn bản trong dấu ngoặc kép nhưng với số thì không:

Đúng: =IF(B2>249, “Excellent”,…)

Sai: =IF(B2>”249″, “Excellent”,…)

Kiểm tra tính logic của công thức thứ hai sẽ trả về FALSE ngay cả khi giá trị trong B2 > 249 Vì 249 là một số và “249” là một chuỗi số, chúng là hai thứ khác nhau.

Thêm dấu cách hoặc ngắt dòng để làm cho các IF lồng nhau dễ đọc hơn

Khi xây dựng một công thức với nhiều điều kiện lồng nhau, bạn có thể làm cho tính logic của công thức được rõ ràng hơn bằng cách tách các hàm IF khác nhau bằng dấu cách hoặc dấu ngắt dòng. Excel không quan tâm đến khoảng cách thừa trong công thức, vì vậy bạn không cần lo lắng về việc sai lệch của nó.

Để di chuyển một phần nhất định của công thức sang dòng tiếp theo, chỉ cần nhấp vào nơi bạn muốn chèn ngắt dòng và nhấn Alt + Enter. Sau đó, mở rộng thanh công thức đến mức cần thiết và công thức của bạn sẽ trở nên dễ hiểu hơn nhiều.

Các lựa chọn thay thế cho IF lồng nhau trong Excel

Để vượt qua giới hạn của 7 hàm IF lồng nhau trong Excel 2003 và các phiên bản cũ hơn và để làm cho công thức của bạn nhỏ gọn và nhanh hơn, hãy xem xét sử dụng các lựa chọn thay thế sau nhé:

• Để kiểm tra nhiều điều kiện và trả về các giá trị khác nhau dựa trên kết quả của những kiểm tra đó, bạn có thể sử dụng hàm CHOOSE.
• Xây dựng một bảng tham chiếu và sử dụng hàm VLOOKUP với đối sánh gần đúng.
• Sử dụng IF với các hàm logic OR/AND.
• Sử dụng công thức mảng (array).
• Kết hợp nhiều câu lệnh IF bằng cách sử dụng hàm CONCATENATE hoặc phương pháp nối chuỗi.
• Đối với người dùng Excel có kinh nghiệm, giải pháp thay thế tốt nhất để sử dụng nhiều hàm IF lồng nhau là tạo một hàm trang tính tùy chỉnh bằng VBA.

Xem thêm: Hàm IF nhiều điều kiện trong một công thức duy nhất