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 tân tiến hơn của Excel năm nay, Excel 2013, Excel 2010 và Excel 2007, bạn hoàn toàn có thể lồng tối đa 64 hàm IF trong một công thức. Trong Excel 2003 quay trở lại trước, chỉ hoàn toàn 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.
Bạn đang đọc: Cách dùng hàm IF với nhiều điều kiện trong Excel
Tóm Tắt
Ví dụ 1: Công thức IF lồng nhau cổ điển/cơ bản
Giả sử bạn có list học viên 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 kèm theo sau :
- Excellent : trên 249
- Good : từ 200 đến 249
- Satisfactory : từ 150 đến 199
- Poor : dưới 150
Và giờ đây, tất cả 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 chuẩn trên. Đây là một thời cơ thực hành thực tế tốt, khởi đầu với những điều kiện kèm theo quan trọng nhất và viết hàm IF của bạn càng đơn thuần càng tốt. Công thức IF lồng nhau được bộc lộ như sau :
=IF(B2>249, “Excellent”, IF(B2>=200, “Good”, IF(B2>150, “Satisfactory”, “Poor “)))
Và tác dụng đúng chuẩn 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 trong thực tiễn, những gì công thức thực sự nhu yếu Excel làm là nhìn nhận kiểm tra tính logic của điều kiện kèm theo tiên phong của công thức hàm IF, nếu điều kiện kèm theo logic, Excel sẽ trả về giá trị được cung ứng khi điều kiện kèm theo được phân phối. Nếu điều kiện kèm theo của hàm If thứ nhất không logic thì liên tục kiểm tra điều kiện kèm theo thứ hai, điều kiện kèm theo 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 nhu yếu nổi bật khác : đơn giá biến hóa tùy thuộc vào số lượng được chỉ định đơn cử và trách nhiệm của bạn là viết công thức tính tổng giá cho số lượng mẫu sản phẩm 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 kèm theo và triển khai các phép tính khác nhau tùy thuộc vào khoanh vùng phạm vi số lượng hàng và giá thành được chỉ định trong bảng sau :
| Số lượng hàng | Giá cả mỗi đơn vị chức năng |
| 1 – 10 | 20 USD |
| 11 – 19 | 18 USD |
| 20 – 49 | 16 USD |
| 50 – 100 | 13 USD |
| Hơn 101 | 12 USD |
Công việc này cũng hoàn toàn có thể được triển khai bằng cách sử dụng hàm IF lồng nhau. Cách hiểu logic tựa như như trong ví dụ trên, sự độc lạ 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ị chức năng sản phẩm & 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à tác dụng sẽ được biểu lộ tương tự như 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 hoàn toàn có thể tham chiếu các ô chứa các giá trị đó ( ô B2 đến B6 ). Cách này sẽ được cho phép bạn chỉnh sửa tài liệu nguồn mà không cần phải update 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 hoàn toàn có thể thêm ( các ) điều kiện kèm theo IF bổ trợ để kiểm soát và điều chỉnh số lượng giới hạn trên, dưới hoặc cả hai đầu của khoanh vùng phạm vi số tiền. Khi số lượng nằm ngoài khoanh vùng phạm vi, công thức sẽ hiển thị thông tin “ 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 miêu tả ở trên đều hoạt động giải trí trong tổng thể các phiên bản Excel năm nay – 2000. Trong Excel năm nay – Office 365, bạn hoàn toà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 hoàn toàn có thể sử dụng công thức này triển khai điều tựa như như hàm IF lồng nhau mà tất cả chúng ta đã tranh 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 hề chối cãi – bạn hoàn toàn có thể chỉ định khoanh vùng phạm vi ô ( một dãy ô ) chứa các điều kiện kèm theo mình cần thay vì tham chiếu từng điều kiện kèm theo riêng không liên quan gì đến nhau. Điều này làm cho công thức linh động hơn và nếu người dùng biến hóa bất kể điều kiện kèm theo hiện có nào hoặc thêm điều kiện kèm theo mới, bạn sẽ chỉ phải update 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 kèm theo trong Excel không yên cầu các chiêu thức khoa học quá cao siêu. Các mẹo sau đây sẽ giúp bạn cải tổ 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 năm nay – 2007, bạn hoàn toàn có thể lồng tối đa 64 hàm IF hay tích hợp 64 điều kiện kèm theo. Trong các phiên bản cũ hơn của Excel 2003 trở lại trước, hoàn toàn có thể sử dụng tối đa 7 hàm IF lồng nhau. Tuy nhiên, bạn hoàn toàn có thể lồng nhiều điều kiện kèm theo trong một công thức không có nghĩa là bạn nên làm như vậy. Xin chú ý quan tâm rằng lượt bổ trợ điều kiện kèm theo 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 kèm theo được phối hợp, bạn hoàn toà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 sửa chữa 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ìn nhận tính logic theo thứ tự chúng Open trong công thức và ngay sau khi một trong các điều kiện kèm theo nhìn nhận là TRUE, các điều kiện kèm theo 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 hiệu quả là TRUE .
Hãy xem nó hoạt động giải trí như thế nào trong thực tiễn. Với B2 bằng 274, công thức IF lồng nhau bên dưới nhìn nhận tính logic của điều kiện kèm theo tiên phong ( B2 > 249 ) và trả về “ Xuất sắc ” vì tác dụng 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 kèm theo 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 kèm theo tiên phong, do tại 274 > 150, hiệu quả của kiểm tra tính logic này cũng sẽ là TRUE. Do đó, công thức trả về hiệu quả “ Satisfactory ” mà không cần kiểm tra các điều kiện kèm theo khác .
Bạn thấy đó, việc biến hóa thứ tự của các hàm IF sẽ đổi khác hiệu 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ìn nhận và việc nhấp vào nút Evaluate sẽ hiển thị cho bạn toàn bộ các bước trong quá trình nhìn nhận .
Ví dụ : việc nhìn nhận tính logic của điều kiện kèm theo tiên phong 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ử thách 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 giải trí. Nhưng đừng lo, Microsoft Excel cung ứng một số ít tính năng hoàn toàn có thể giúp bạn cân đối 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 khởi đầu khớp với dấu ngoặc đóng .
- Khi bạn đóng dấu ngoặc đơn, Excel sẽ lưu lại cặp tương thích. Sẽ có hiệu ứng in đậm hoặc “ nhấp nháy ” khi bạn vận động và 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 kiến thiết 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 giải quyết và 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à 1 số ít 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 kiến thiết xây dựng một công thức với nhiều điều kiện kèm theo lồng nhau, bạn hoàn toà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 chăm sóc đến khoảng cách thừa trong công thức, thế cho nên bạn không cần lo ngại về việc xô lệch của nó .
Để chuyển dời 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 đó, lan rộng ra thanh công thức đến mức thiết yếu 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 số lượng 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ế sửa chữa 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 tay nghề, giải pháp thay thế sửa chữa 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
Source: https://final-blade.com
Category : Kiến thức Internet
