Cách sử dụng hàm IF nhiều điều kiện trong Excel

Hãy cùng tìm hiểu về cách dùng hàm IF nhiều điều kiện trong Excel ở bài viết dưới đây nhé!

Làm thế nào để dùng hàm IF nhiều điều kiện trong Excel:

Thông thường, có 2 loại điều kiện cơ bản – với hàm logic AND và OR. Vì vậy hàm IF của bạn nên được nhúng một hàm AND và OR để thực hiện phép thử logic tương ứng.

  • Hàm AND: Nếu phép thử logic có chứa hàm AND và Microsoft Excel sẽ trả về TRUE nếu toàn bộ các điều kiện đều được đáp ứng; Nếu không sẽ trả về FALSE.
  • Hàm OR. Trường hợp bạn dùng hàm OR trong phép thử logic, Excel lúc này sẽ trả về TRUE nếu đáp ứng được bất cứ điều kiện nào ; nếu không sẽ trả về FALSE.

Để minh họa một cách dễ hiểu hơn, bạn hãy cùng xem xét một số ví dụ về hàm IF với nhiều điều kiện.

Ví dụ 1. Sử dụng IF & AND trong Excel

Ví dụ, bạn đang có một bảng chứa kết quả của hai điểm thi. Điểm số đầu tiên được lưu trữ trong cột C, với điều kiện phải bằng hoặc là lớn hơn 20. Điểm số thứ hai được liệt kê trong cột D, với điều kiện phải bằng hoặc là hơn 30. Khi nào cả hai điều kiện cho trước được đáp ứng, thì học sinh mới vượt qua được kỳ thi.

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

Điều kiện: AND (B2> = 20, C2> = 30)

Công thức IF / AND sẽ là: =IF((AND(C2>=20, D2>=30)), “Pass”, “Fail”)

Lúc này, công thức cho biết Excel trả về “Pass” nếu một giá trị cột C> = 20, giá trị trong cột D> = 30. Nếu không thỏa mãn điều kiện, công thức sẽ trả về “Fail”. Ảnh chụp bên dưới chứng minh được rằng hàm IF / AND là chính xác:

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

Thực tế thì, một công thức IF / AND đúng có thể sẽ bị lỗi do tính năng này. Ví dụ như công thức =IF(AND(A2<>0,(1/A2)>0.5),”Good”, “Bad”) sẽ trả lại “lỗi chia cho 0” (# DIV / 0!) Nếu như ô A2 bằng 0. Để có thể tránh được vấn đề này, bạn nên dùng IF lồng ghép :

=IF(A2<>0, IF((1/A2)>0.5, “Good”, “Bad”), “Bad”)

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

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

Vì vậy, nếu bạn chỉnh sửa công thức trên theo cách bên dưới:

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

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

Theo hình minh họa bên dưới, các học sinh có cơ hội tốt hơn để vượt qua kỳ thi cuối cùng với những điều kiện như vậy ( trong bạn Scott thật không may mắn vì chỉ thiếu 1 điểm.)

Tất nhiên, bạn không bị giới hạn chỉ dùng hai hàm AND / OR trong công thức IF. Bạn hoàn toàn có thể dùng rất nhiều hàm logic theo yêu cầu của công việc, với các điều kiện là:
  • Trong Excel 2013, 2010 và 2007, công thức không nhiều hơn 255 đối số, và tổng chiều dài của công thức không được vượt quá 8192 ký tự.
  • Trong Excel 2003 và các bản cũ hơn, có thể dùng tối đa 30 đối số và tổng chiều dài của công thức sẽ không vượt quá 1.024 ký tự.

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

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

Ở bảng trên, ví dụ bạn có những tiêu chí sau để đánh giá thành công của học sinh:

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

Nếu như một trong hai điều kiện trên được đáp ứng thì đánh giá cuối cùng vẫn được xếp là đạt, nếu không thì sẽ không đạti.

Công thức này thoạt đầu trông có vẻ hơi khó khăn, tuy nhiên chỉ một chốc, bạn sẽ thấy rằng nó không khó như bạn nghĩ! Bạn chỉ cần thể hiện được hai điều kiện là câu lệnh AND rồi đính kèm chúng trong hàm OR. Bởi vì kết quả bạn cần không cần thỏa cả hai điều kiện được đáp ứng:

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

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

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

Hãy xem hình mình họa bên dưới:

Dùng nhiều hàm IF trong Excel (các hàm IF lồng nhau):

Nếu như bạn đang cần tạo thêm những phép thử logic cho dữ liệu của mình, bạn có thể thêm những câu lệnh IF bổ sung trong những đối số value_if_true, value_if_false của công thức IF của bạn. Những hàm IF này được gọi chung là các hàm IF lồng nhau và đặc biệt hữu ích nếu như bạn muốn công thức trả lại ba kết quả trở lên.

Dưới dây là một ví dụ điển hình: bạn không chỉ cần đủ điều kiện kết quả của sinh viên như Pass / Fail, mà bạn còn cần xác định tổng số điểm là “Good“, “Satisfactory” và “Poor“.

  • Good: 60 trở lên (> = 60)
  • Satisfactory: từ 40 đến 60 (> 40 và <60)
  • Poor: 40 hoặc ít hơn (<= 40)

Bạn bắt đầu bằng việc thêm một cột bổ sung (E) bởi công thức sau đây để cộng những số trong cột C và D: =C2+D2

Bây giờ, bạn tiến hành viết một hàm IF lồng nhau dựa trên những điều kiện trên. Đó sẽ được xem là một bài thực hành tốt để giúp bạn bắt đầu với điều kiện quan trọng nhất và làm cho những hàm của bạn càng đơn giản càng tốt. Công thức IF lồng nhau như sau:

=IF(E2>=60, “Good”, IF(E2>40, “Satisfactory”, “Poor “))

Như bạn có thể thấy, chỉ cần một hàm IF lồng nhau là đủ đối với trường hợp này. Tất nhiên, bạn hoàn toàn có thể  tổ chức thêm hàm IF  nếu như bạn muốn. Ví dụ như sau:

=IF(E2>=70, “Excellent”, IF(E2>=60, “Good”, IF(E2>40, “Satisfactory”, “Poor “)))

Công thức trên được bổ sung thêm một điều kiện – tổng điểm từ 70 điểm trở lên được xếp hạng là “ Excellent “.

Sử dụng Excel IF trong các công thức mảng

Tương tự như các hàm Excel khác, hàm IF có thể được dùng trong những công thức mảng . Bạn có thể sẽ cần một công thức như vậy nếu như bạn muốn đánh giá mọi thành phần của mảng khi tiến hành lệnh IF.

Ví dụ như công thức SUM / IF mảng dưới đây thể hiện được cách mà bạn tổng hợp những ô trong phạm vi được chỉ định dựa trên một điều kiện nào đó thay cho việc cộng giá trị thực tế:

=SUM(IF(B1:B5<=1,1,2))

Với công thức gán một số “điểm” nhất định cho từng giá trị trong cột B – nếu như một giá trị bằng hoặc là nhỏ hơn 1, nó sẽ tương đương với 1 điểm; Và 2 điểm được gán cho từng giá trị lớn hơn 1. Sau đó,hàm SUM sẽ thêm những kết quả của 1 và 2, như thể hiện ở hình dưới đây.

Chú ý. Vì đây là một công thức mảng, nên bạn hãy nhớ nhấn Ctrl + Shift + Enter để có thể nhập chính xác.

Sử dụng hàm IF cùng với các hàm Excel khác

Ví dụ 1. Sử dụng IF với hàm SUM, AVERAGE, MIN và MAX

Khi thảo luận về những hàm IF lồng nhau , bạn đã viết công thức trả về những thứ hạng khác nhau (Excellent, Good, Satisfactory hoặc Poor) dựa vào tổng số điểm của mỗi học sinh. Như bạn có thể thấy, bạn đã thêm một cột mới với công thức tính tổng điểm từ những cột C và D.

Nhưng nếu bảng của bạn có một cấu trúc được xác định trước nhưng không cho phép sửa đổi thì điều gì sẽ xảy ra? Trong trường hợp này, thay cho việc thêm cột phụ, bạn cũng có thể tiến hành thêm những giá trị trực tiếp vào công thức IF của bạn, như bên dưới:

=IF((C2+D2)>=60, “Good”, IF((C2+D2)=>40, “Satisfactory”, “Poor “))

Trong trường hợp nếu bảng của bạn có chứa nhiều cột điểm, chẳng hạn như tới 5 cột khác nhau hoặc là nhiều hơn? Tổng hợp rất nhiều con số trực tiếp trong công thức IF có thể sẽ làm cho nó rất dài. Phương pháp thay thế đó là nhúng hàm SUM trong phép thử logic của IF:

=IF(SUM(C2:F2)>=120, “Good”, IF(SUM(C2:F2)>=90, “Satisfactory”, “Poor “))

Tương tự, bạn có thể dùng các hàm Excel khác để làm phép thử logic trong những công thức IF của bạn:

IF và AVERAGE:

=IF(AVERAGE(C2:F2)>=30,”Good”,IF(AVERAGE(C2:F2)>=25,”Satisfactory”,”Poor “))

Nó trả lại kết quả ” Good ” nếu như điểm trung bình trong các cột C: F bằng hoặc là lớn hơn 30, ” Satisfactory ” nếu như điểm trung bình từ 29 đến 25, ” Poor ” nếu dưới 25.

IF và MAX / MIN:

Để có thể tìm điểm cao nhất và thấp nhất, bạn có thể dùng những hàm MAX và MIN, tương ứng. Ví dụ rằng cột F là cột tổng số điểm, những công thức dưới đây để tìm ra kết quả:

MAX: =IF(F2=MAX($F$2:$F$10), “Best result”, “”)

MIN: =IF(F2=MIN($F$2:$F$10), “Worst result”, “”)

Nếu như bạn đang muốn có cả kết quả Min và Max trong cùng một cột, thì bạn có thể tiến hành xếp một hàm lồng trong hàm kia, ví dụ:

=IF(F2=MAX($F$2:$F$10) ,”Best result”, IF(F2=MIN($F$2:$F$10), “Worst result”, “”))

Cũng dựa vào cách tương tự, bạn có thể dùng hàm IF với những hàm bảng tính tuỳ chỉnh của bạn. Ví dụ, bạn có thể dùng nó với những hàm  GetCellColor / GetCellFontColor để có thể trả lại kết quả khác nhau dựa trên màu của một ô.

Bên cạnh đó, Excel cũng cung cấp một số hàm IF đặc biệt để phân tích cũng như tính toán dữ liệu dựa trên các điều kiện khác nhau.

Ví dụ như để đếm được số lần xuất hiện của một giá trị văn bản hoặc là số dựa trên một điều kiện duy nhất hoặc nhiều điều kiện, bạn có thể dùng hàm COUNTIF và COUNTIFS tương ứng. Để tiến hành tìm ra một tổng các giá trị dựa trên (các) điều kiện được chỉ định, hãy dùng hàm SUMIF hoặc là SUMIFS. Để thực hiện tính trung bình theo những tiêu chí nhất định, hãy dùng hàm  AVERAGEIF hoặc AVERAGEIFS.

Xem thêm: Cách dùng hàm LARGE và hàm SMALL trong Excel

Ví dụ 2. IF với Hàm ISNUMBER và ISTEXT

Bạn đã biết cách để có thể phát hiện những ô trống và không trống bằng việc dùng hàm ISBLANK. Microsoft Excel cung cấp các hàm tương tự để có thể xác định những giá trị văn bản và số – ISTEXT và ISNUMBER.

Đây là ví dụ về hàm IF lồng nhau trả về “text” nếu ô B1 có chứa bất cứ giá trị văn bản nào, “number” nếu như B1 có chứa một giá trị số, và “blank” nếu B1 trống.

=IF(ISTEXT(B1), “Text”, IF(ISNUMBER(B1), “Number”, IF(ISBLANK(B1), “Blank”, “”)))

 

Chú ý. Bạn hãy lưu ý rằng công thức trên hiển thị ” number ” cho những giá trị và ngày tháng. Điều này được lí giải là do Microsoft Excel lưu trữ các ngày theo dạng số, khởi phát từ ngày 1 tháng 1 năm 1900, tương đương với 1.

Ví dụ 3. Sử dụng kết quả trả về bởi IF trong một hàm Excel khác

Đôi lúc, bạn đạt được kết quả mong muốn bằng việc nhúng IF trong một số hàm Excel khác, thay vì dùng một hàm khác trong một phép thử logic.

Đây chính là một cách khác để bạn có thể dùng những hàm CONCATINATE và IF cùng nhau:

=CONCATENATE(“You performed “, IF(C1>5,”fantastic!”, “good”))

Hàm IFERROR và hàm IFNA

Cả hàm IFEFFOR và IFNA – đều được dùng trong Excel để bắt các lỗi trong công thức. Và cả hai hàm này đều có thể trả về một giá trị đặc biệt mà bạn đã chỉ định trước đó nếu một công thức tạo ra lỗi. Nếu như không, kết quả của công thức sẽ được trả lại.

Sự khác biệt ở đây là hàm IFEFFOR xử lý toàn bộ các lỗi Excel, kể cả #VALUE !, # N / A, #NAME ?, #REF !, #NUM !, # DIV / 0 !, và #NULL !. Mặc dù hàmIFNA chỉ có thể chuyên về lỗi # N / A, khả năng chính xác như là tên của nó vậy.

Cú pháp của các hàm Error sẽ là:

Hàm IFERROR

IFERROR (value value_if_error)

Hàm IFNA

IFNA (value, value_if_na)

Tham số đầu tiên (giá trị) được biết đến là đối số được kiểm tra cho một lỗi.

Tham số thứ hai (value_if_error / value_if_na): giá trị trả lại nếu công thức đánh giá bị lỗi (bất cứ lỗi nào trong trường hợp hàm IFEFFOR,  hoặc là lỗi # N / A trong trường hợp của IFNA).

Chú ý. Nếu bất cứ đối số nào là một ô rỗng, thì cả hai hàm Error sẽ xử lý nó như một chuỗi rỗng (“”).

Ví dụ bên dưới chứng minh cho việc dùng hàm IFERROR vô cùng đơn giản:

=IFERROR(B2/C2, “Sorry, an error has occurred”)

Như bạn cũng đã thấy trong ảnh trên thì cột D hiển thị số chia của cột B cho cột C. Bạn thấy rằng hai thông báo lỗi trong các ô D2 và D5 bởi vì mọi người đều biết rằng bạn sẽ không thể chia một số cho 0.

Ở một vài trường hợp, bạn tốt hơn nên dùng hàm IF để thực hiện ngăn chặn một lỗi sau đó dùng hàm ISERROR hoặ làc ISNA để bắt lỗi. Đầu tiên, đó chính là một cách nhanh hơn (về CPU), tiếp đến nó là một cách thực hành lập trình rất tốt. Ví dụ, công thức IF bên dưới tạo ra kết quả tương tự như hàm IFERROR đã được trình bày ở trên:

=IF(C2=0, “Sorry, an error has occurred”, B2/C2)

Nhưng đương nhiên là có những trường hợp khi bạn không thể kiểm tra trước toàn các hàm tham số, đặc biệt là những công thức rất phức tạp, cũng như dự đoán được tất cả các lỗi có thể xảy ra. Với các trường hợp này thì những hàm ISERROR () và IFNA () thực sự có ích.

Xem thêm: Hướng dẫn cách dùng và ứng dụng hàm LEFT trong Excel

Tin tức liên quan

Gửi bình luận

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *