Cách kết hợp hàm IF trong excel với nhiều điều kiện trong một công thức

Nếu được hỏi chức năng cụ thể nào trong Excel mà bạn dùng nhiều nhất, bạn sẽ trả lời như thế nào? Với rất nhiều người, đó chính là việc sử dụng hàm IF trong Excel. Cùng 123job tìm hiểu chi tiết về hàm này trong bài viết sau nhé!

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

I. Làm thế nào để sử dụng cách dùng hàm IF trong Excel với nhiều điều kiện

1. Ví dụ 1. Cách dùng hàm IF nhiều điều kiện và hàm AND trong Excel

Giả sử bạn có một bảng dữ liệu với kết quả của hai điểm thi trong trang tính Excel. Điểm số đầu tiên, được tương ứng lưu trữ trong cột C, phải được biểu diễn bằng hoặc lớn hơn 20. Điểm số thứ hai, được liệt kê trong cột D của trang tính Excel, phải bằng hoặc hơn 30. Chỉ khi nào cả hai điều kiện được nói trên được đáp ứng, thì học sinh đó mới vượt qua được kỳ thi.

Cách dễ nhất để tạo ra một công thức thích hợp là lần lượt ghi lại điều kiện đầu tiên, và sau đó kết hợp nó trong đối số logic_test củahàm IF trong Excel hay hàm IF nhiều điều kiện:

• Điều kiện cần có: AND (B2> = 20, C2> = 30)
• Công thức IF / AND: =IF((AND(C2>=20, D2>=30)), “Pass”, “Fail”)

Công thức cho biết trang tính Excel khi sử dụng hàm IF nhiều điều kiện sẽ trả về “Pass” nếu một giá trị được cho trong cột C> = 20 và giá trị trong cột D> = 30. Nếu không, công thức hàm IF trong Excel sẽ trả về “Fail”. Hình dưới đây chứng minh rằng hàm IF trong Excel và hàm AND của chúng ta là chính xác:

Ví dụ 1. Cách dùng hàm IF nhiều điều kiện và hàm AND trong Excel

Chú ý. Microsoft Excel sẽ thực hiện kiểm tra tất cả các điều kiện trong hàm AND và ngay cả khi một trong các điều kiện đã được quy định kiểm tra đánh giá là FALSE. Điều này là hơi bất thường vì trong hầu hết các ngôn ngữ lập trình cơ bản hiện nay, các điều kiện tiếp theo không cần thực hiện kiểm tra nếu bất kỳ kiểm tra trước đó đã trả về kết quả FALSE.

Trên thực tế, một công thức hàm IF trong Excel và hàm AND trong Excel đúng có thể bị lỗi do tính năng này. 

Ví dụ, công thức =IF(AND(A20,(1/A2)>0.5),”Good”, “Bad”) sẽ lập tức trả lại “lỗi chia cho 0” (# DIV / 0!) Nếu ô A2 trong Microsoft Excel bằng 0. Để tránh điều này, bạn nên sử dụng hàm IF lồng ghép hay hàm IF trong Excel nhiều điều kiện:
=IF(A20, IF((1/A2)>0.5, “Good”, “Bad”), “Bad”)

2. Ví dụ 2. Sử dụng hàm IF trong Excel với hàm OR trong Excel

Bạn có thể sử dụng kết hợp các hàm OR và hàm IF trong Excel theo một cách tương tự. Sự khác biệt từ cách dùng hàm IF hay AND được thảo luận ở trên là Microsoft Excel trả về giá trị TRUE nếu đáp ứng được ít nhất một trong các điều kiện đã được chỉ định.

Vì vậy, nếu chúng ta sửa đổi cách dùng hàm IF trong Exceltrên theo cách sau:

=IF((OR(C2>=20, D2>=30)), “Pass”, “Fail”)

Cột E sẽ có dấu hiệu là “Pass” nếu điểm số đầu tiên bằng hoặc lớn hơn 20 hoặc điểm số thứ hai của học sinh đó bằng hoặc lớn hơn 30.

Như bạn thấy trong hình bên dưới, những học sinh trong phần dữ liệu của chúng ta có cơ hội tốt hơn để vượt qua kỳ thi cuối cùng với các điều kiện được đặt ra như vậy.

Ví dụ 2. Sử dụng hàm IF trong Excel với hàm OR trong Excel

Đương nhiên, bạn không bị giới hạn trong việc chỉ sử dụng hai hàm AND/OR trong cách dùng hàm IF trong Excel của mình. Bạn có thể sử dụng rất nhiều hàm logic khác nhau tùy theo yêu cầu của công việc, với điều kiện là:

Trong Microsoft Excel 2013, 2010 và 2007, công thức hàm IF trong Excel của bạn không nhiều hơn 255 đối số, và tổng chiều dài của công thức tương ứng không vượt quá 8192 ký tự.

Trong Microsoft Excel 2003 và các bản cũ hơn của nó, bạn có thể sử dụng tối đa là 30 đối số và tổng chiều dài tương ứng của công thức của bạn sẽ không vượt quá 1.024 ký tự.

3. Ví dụ 3. Sử dụng IF trong Excel với các hàm AND và OR

Trong trường hợp bạn phải thực hiện đánh giá dữ liệu dựa trên một vài điều kiện, bạn sẽ phải sử dụng cả hàm AND và hàm OR tại một thời điểm.

Trong bảng trên, giả sử bạn cần sử dụng các tiêu chí sau để đánh giá thành công của học sinh:

• Điều kiện 1: giá trị cột C> = 20 và cột D> = 25
• Điều kiện 2: giá trị cột C> = 15 và cột D> = 20

Nếu một trong hai điều kiện trên được dữ liệu trong Microsoft Excel đáp ứng, đánh giá cuối cùng vẫn là đạt, nếu không – kết quả trả về là không đạt.

Công thức của hàm này trông có vẻ khó khăn, nhưng chỉ một chốc, bạn sẽ thấy rằng nó không phải là bài toán khó! Bạn chỉ cần thể hiện hai điều kiện là câu lệnh hàm AND và đính kèm chúng một cách khéo léo trong hàm OR vì bạn không cần cả hai điều kiện được nói trên được đáp ứng:

OR(AND(C2>=20, D2>=25), AND(C2>=15, D2>=20)

Cuối cùng, sử dụng hàm OR ở trên như là phép thử logic tương ứng  với cách hàm IF trong Excel và cung cấp các tham số tương ứng là value_if_true và value_if_false. Kết quả là, bạn sẽ nhận được công thức hàm IF trong Excel có nhiều điều kiện AND / OR:

=IF(OR(AND(C2>=20, D2>=25), AND(C2>=15, D2>=20)), “Pass”, “Fail”)

Hình dưới đây chỉ ra rằng chúng ta đã thực hiện đúng cách dùng hàm IF trong Excel:

Ví dụ 3. Sử dụng IF trong Excel với các hàm AND và OR

II. Công thức hàm IF lồng nhau cơ bản

Dưới đây là một ví dụ điển hình về hàm IF trong Excel với nhiều điều kiện. Giả sử bạn có một danh sách dữ liệu thông tin các học sinh ở cột A và điểm số tương ứng ở cột B, bạn muốn thực hiện việc 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

Chúng ta hãy cùng viết hàm IF lồng nhau trong Microsoft Excel dựa trên những tiêu chí trên. Hãy coi đây là cơ hội thực hành cho bạn, bằng việc 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 trong Excel của chúng ta sẽ như sau:

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

Và đây là kết quả:

Công thức hàm IF lồng nhau cơ bản

III. Hàm IF lồng nhau bao gồm các phép tính

Yêu cầu như sau: giá cả hàng hoá được cho sẵn sẽ thay đổi phụ thuộc vào số lượng cụ thể. Và bạn muốn viết một công thức tính toán tổng giá với từng dữ liệu 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 hàm IF trong Excel của bạn cần kiểm tra các điều kiện và thực hiện phép tính tùy vào số lượng hàng và giá cả được tính như sau:

Hàm IF lồng nhau bao gồm các phép tính

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

Giả sử người dùng đã nhập số lượng hàng hóa được cho vào ô B8, công thức của hàm IF trong Excel 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ẽ được trả về như thế này:

Kết quả sẽ được trả về

Ví dụ này chỉ hướng dẫn cách làm cơ bản của Microsoft Excel, nếu bạn đã hiểu, bạn có thể áp dụng hàm IF trong Excel theo cách lồng nhau này vào công việc kinh doanh thường ngày.

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

Thay đổi nguồn dữ liệu đầu vào mà không cần thay đổi công thức

Hoặc bạn có thể thêm điều kiện cách dùng hàm IF trong Excelnữa giúp chặn trên, chặn dưới hoặc cả 2 đầu giá trị của khoảng giá trị. Khi số lượng của hàng hóa không nằm trong khoảng giá trị, công thức hàm IF trong Excel sẽ hiển thị cụm từ “out of the range”. Ví dụ như sau:

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

Ví dụ

IV. Thay thế cho hàm IF lồng nhau trong Excel

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

Để kiểm tra sự thỏa mãn các nhiều điều kiện, sử dụng hàm LOOKUP, hàm VLOOKUP, hàm INDEX/MATCH hoặc hàm CHOOSE.

• Dùng hàm IF trong Excel kết hợp với OR/AND
• Dùng công thức array trong Microsoft Excel
• Dùng hàm CONCATENATE trong Excel và phương pháp nối chuỗi

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

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

Hàm IF trong Excel lồng nhau:

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

Hàm CONCATENATE trong Excel:

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

Thay thế cho hàm IF lồng nhau trong Excel

Chức năng nối chuỗi trong Microsoft Excel:

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

V. Hàm IF lồng nhau và các điều cần nhớ

Như bạn vừa thấy, dùng hàm IF nhiều điều kiện trong Microsoft Excel không đòi hỏi phương pháp phong phú và công thức cao siêu. Để cải thiện công thức hàm IF trong Excel kiểu lồng nhau và có thể tránh những lỗi thông thường, hãy luôn nhớ 3 điều cơ bản sau:

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

VI. Kết luận 

Trên đây là những hướng dẫn cụ thể về cách dùng hàm IF trong Excel khi bạn có nhu cầu kết hợp nhiều điều kiện. Mong rằng điều này sẽ có ý nghĩa và giúp bạn thuận lợi hơn trong công việc tin học văn phòng của mình. Chúc bạn thành công!