Cách sử dụng hàm IF lồng nhau trong excel

Một trong những hàm được nhiều người sử dụng nhất trong Excel chính là hàm If. Công thức của hàm If sẽ đơn giản nếu chỉ có một điều kiện. Nhưng với số liệu cần được kiểm tra bằng các thuật toán phức tạp với nhiều điều kiện. Trong những trường hợp này bạn cần sử dụng chức năng của hàm If, và công thức hàm If lúc này được gọi là hàm If lồng nhau. Bài viết dưới đây hướng dẫn bạn cách sử dụng hàm If lồng nhau trong Excel.

CÔNG THỨC CỦA HÀM IF LỒNG NHAU CƠ BẢN   

Dưới đây là một vài ví dụ điển hình về hàm IF với nhiều điều kiện. Giả sử bạn có một bảng tính gồm danh sách học sinh ở cột A và điểm số tương ứng ở cột B, bạn đang muốn phân loại ra theo những điều kiện sau:

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

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

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

Và hình ảnh dưới đây là kết quả:

Kết quả áp dụng hàm If lồng nhau

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ể khó rất hiểu. Nhưng sẽ dễ dàng hơn nếu bạn nhìn công thức hàm IF lồng nhau trên theo cách này:

=IF(B2>249, “Excellent”,

=IF(B2>=200, “Good”,

=IF(B2>150, “Satisfactory”, “Poor”)))

Thực tế, công thức yêu cầu của Excel đánh giá xem điều kiện đầu tiên của công thức hàm IF có logic hay không, nếu điều kiện đó logic, Excel sẽ trả về giá trị mà bạn cung cấp khi điều kiện này được đáp ứng. Nếu điều kiện đầu tiên không được đáp ứng, bạn thực hiện kiểm tra điều kiện 2 và tiếp tục.

IF(kiểm tra if B2>=249, if đúng – trả kết quả về “Excellent”, nếu không

IF(kiểm tra if B2>=200, if đúng – trả kết quả “Good”, nếu không

IF(kiểm tra if B2>150, if đúng – trả kết quả “Satisfactory”, if sai –

Trả ” Poor “)))

Thứ tự của điều kiện cũng rất quan trọng

Từ ví dụ trên, bạn hoàn toàn có thể thấy điều kiện quan trọng nhất sẽ được viết đầu tiên. Vì trong Excel sẽ kiểm tra điều kiện theo thứ tự xuất hiện trong công thức của bạn, và ngay khi một điều kiện được đáp ứng, điều kiện sau đó sẽ không được thực hiện đánh giá.

HÀM IF LỒNG NHAU BAO GỒM PHÉP TÍNH

Yêu cầu của đề bài như sau: giá cả của hàng hoá sẽ thay đổi phụ thuộc vào số lượng cụ thể. Và bạn muốn thực hiện viết một công thức tính toán tổng giá với số lượng hàng hoá bất kì và đưa vào một ô nhất định. Nói cách khác, công thức của bạn cần được kiểm tra các điều kiện và thực hiện nó theo phép tính tuỳ vào số lượng hàng và giá cả được tính như sau:

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

Giả sử người dùng đã nhập vào số lượng hàng vào ô B8, công thức áp dụng sẽ 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ẽ tương tự như dưới hình:

Hàm If lồng nhau bao gồm phép tính

Ví dụ này chỉ hướng dẫn bạn cách làm cơ bản, nếu bạn đã hiểu, bạn hoàn toàn có thể áp dụng hàm IF lồng nhau này vào những công việc của bạn.

Ví dụ, thay vì bạn đặt sẵn giá cả hàng hoá trong công thức, bạn hoàn toàn có thể dẫn tới các ô có chứa giá trị này (ô B2 đến B6). Phương pháp này sẽ giúp bạn thay đổi nguồn dữ liệu đầu vào mà không cần phải thay đổi 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, “”)))))

Kết quả sau khi áp dụng hàm If lồng nhau

Hoặc bạn hoàn toàn có thể thêm điều kiện IF nữa giúp chặn trên, chặn dưới hoặc cả 2 đầu của khoảng giá trị. Khi số lượng không nằm trong một khoảng giá trị nhất định, công thức sẽ xuất hiện cụm từ “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, “”))))))

Kết quả nhận được

Công thức của hàm IF lồng nhau được miêu tả bên trên có trong mọi phiên bản của Excel từ 2016-2000. Trong Excel 2016 thuộc Office 365, bạn cũng hoàn toàn có thể dùng hàm IF theo cách như trên.

Những người làm Excel chuyên nghiệp đã quen thuộc khi dùng công thức Mảng (array), thì hàm này hoàn toàn có thể làm tương tự như những hàm IF lồng nhau ở bên trên. Tuy hiểu công thức mảng array khó hơn rất nhiều, chưa nói đến việc viết ra, hàm Mảng có một ưu điểm – bạn hoàn toàn có thể dùng một dãy ô có chứa điều kiện bạn cần, hoặc không cần viết từng điều kiện. Điểm này giúp công thức tính toàn linh hoạt hơn, và nếu người dùng của bạn cần thay đổi một điều kiện bất kì hay thêm điều kiện mới, bạn chỉ cần thực hiện cập nhật lại dãy ô trong công thức của mình.

HÀM IF LỒNG NHAU, NHỮNG ĐIỀU CẦN NHỚ

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 phải sử dụng phương pháp, công thức cao siêu. Để cải thiện công thức hàm IF lồng nhau và tránh những lỗi thông thường, bạn hãy luôn nhớ 3 điều cơ bản sau:

1. Trong Excel 2016-2007, bạn hoàn toàn có thể kết hợp 64 điều kiện. Trong các phiên bản cũ hơn từ Excel 2003 về trước, bạn hoàn toàn có thể dùng tối đa 7 điều kiện.
2. Bạn luôn phải nghĩ tới thứ tự các điều kiện trong công thức hàm IF lồng nhau – nếu điều kiện đầu tiên đúng, những điều kiện sau sẽ không được thực hiện kiểm tra.
3. Nếu trong công thức của bạn có nhiều hơn 5 chức năng hàm IF, các công thức sau đây hoàn toàn có thể sẽ tối ưu hơn.

THAY THẾ CHO HÀM IF LỒNG NHAU TRONG EXCEL

Đế tránh khỏi mức giới hạn của 7 điều kiện trong hàm IF ở những phiên bản Excel cũ và giúp công thức hàm của bạn trở nên ngắn gọn và nhanh hơn, bạn hoàn toàn có thể chọn những phương pháp sau thay thế cho hàm IF với nhiều điều kiện.

1. Để kiểm tra nhiều điều kiện, bạn sử dụng hàm LOOKUP, VLOOKUP, INDEX/MATCH hoặc CHOOSE.
2. Dùng hàm IF kết hợp với hàm OR/AND
3. Dùng công thức Mảng array
4. Dùng hàm CONCATENATE và phương pháp nối chuỗi

Giống như các hàm Excel khác, hàm CONCATENATE hoàn toàn có thể bao gồm 30 điều kiện trong các phiên bản Excel cũ và 255 điều kiện trong bản Excel  2016 – 2007.

Ví dụ, để trả các kết quả khác nhau dựa vào giá trị trong ô B2, bạn hoàn toàn có thể dùng một trong số những công thức sau:

Hàm IF lồng nhau:

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

Hàm CONCATENATE:

=CONCATENATE(IF(C1=”a”, “Excellent”, “”), IF(C1=”b”, “Good”, “”), IF(C1=”c”, “Poor “, “”))

Hàm If lồng nhau kết hợp hàm CONCATENATE

Chức năng nối chuỗi:

=IF(B2=”a”, “Excellent”, “”) & IF(B2=”b”, “Good”, “”) & IF(B2=”c”, “Poor “, “”) & IF(B2=”d”, “Poor “, “”)