Câu lệnh if trong excel với nhiều điều kiện if/and

Bạn đang xem: Câu lệnh if trong excel với nhiều điều kiện if/and Tại Website saigonmetromall.com.vn

Bạn đang xem: Câu lệnh if trong excel với nhiều điều kiện if/and Tại Website saigonmetromall.com.vn

Trong bài viết trước, chúng ta đã biết cách sử dụng hàm IF với các con số, văn bản và ngày tháng trong Excel. Tiếp tục bài viết này Học Excel Online sẽ giới thiệu với các bạn cách sử dụng hàm IF kết hợp với các hàm thông dụng khác nhé!

Trong quá trình sử dụng Excel, đôi lúc bạn sẽ đối mặt với những bài toán phức tạp, cần phải đánh giá nhiều điều kiện cùng một lúc, nghĩa là bạn phải xây dựng các phép logic phức tạp hơn bằng cách sử dụng nhiều hàm IF trong một công thức. Trong bài viết này sẽ chứa các ví dụ thực tế giúp bạn dễ hiểu nhất, ngoài ra bạn còn tìm hiểu kiến thức cơ bản về hàm IFERROR và IFNA nữa đấy!

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

Có 2 loại cơ bản của hàm IF nhiều điều kiện – AND và OR. Do đó, hàm IF của bạn phải nhúng một hàm AND hoặc OR tương ứng trong phép thử logic.

• Hàm AND: Nếu kiểm tra logic của bạn chứa hàm AND, Microsoft Excel trả về TRUE nếu tất cả các điều kiện được đáp ứng; nếu không nó trả về FALSE.

• Hàm OR: Trong trường hợp bạn sử dụng hàm OR trong kiểm tra logic, Excel sẽ trả về TRUE nếu bất kỳ điều kiện nào được đáp ứng; trả về FALSE nếu không.

Để dễ hiểu hơn, chúng ta hãy xem một vài ví dụ IF với nhiều điều kiện.

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

Giả sử, bạn có một bảng kết quả của hai điểm thi. Điểm đầu tiên ghi trong cột C phải bằng hoặc lớn hơn 20. Điểm thứ hai ghi trong cột D phải bằng hoặc vượt 30. Chỉ khi đáp ứng cả hai điều kiện trên, học sinh mới đạt điểm cuối cùng. thi.

Cách dễ nhất để tạo một công thức thích hợp là viết ra điều kiện trước, sau đó kết hợp nó trong đối số logic_test của hàm IF của bạn:

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

Công thức IF/AND:

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

Thật dễ dàng phải không? Công thức trên yêu cầu Excel trả về “pass” nếu giá trị trong cột C> = 20 VÀ giá trị trong cột D> = 30. Nếu không, công thức trả về “Fail”. Ảnh chụp màn hình bên dưới chứng minh rằng hàm IF / AND trong Excel của chúng ta là đúng:

Lưu ý rằng Microsoft Excel sẽ kiểm tra tất cả các điều kiện trong hàm AND, ngay cả khi một trong các điều kiện đã được kiểm tra đánh giá là FALSE. Hành vi như vậy hơi bất thường vì trong hầu hết các ngôn ngữ lập trình, các điều kiện tiếp theo sẽ không được kiểm tra nếu bất kỳ kiểm tra nào trước đó trả về FALSE.

Trong thực tế, một công thức IF/AND có vẻ đúng có thể dẫn đến lỗi vì tính cụ thể này. Ví dụ: công thức dưới đây sẽ trả về “Divide by Zero Error” (#DIV/0!) Nếu ô A2 bằng 0:

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

Để tránh điều này, bạn nên sử dụng hàm IF lồng nhau:

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

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

Bạn có thể kết hợp các hàm IF & OR theo cách tương tự. Tuy nhiên, khác với công thức IF/AND được thảo luận ở trên là công thức này trả về TRUE nếu ít nhất một trong các điều kiện được đáp ứng.

Vì vậy, nếu chúng ta sửa đổi công thức trên theo cách sau:

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

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

Như bạn thấy trong ảnh chụp màn hình bên dưới, các sinh viên có cơ hội tốt hơn để vượt qua kỳ thi cuối kỳ với những điều kiện như vậy (Scott đặc biệt không may mắn khi chỉ trượt 1 điểm:)

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

Trong trường hợp bạn phải đánh giá dữ liệu của mình dựa trên một số tập hợp nhiều điều kiện, bạn sẽ phải sử dụng cả hai hàm AND & OR cùng một lúc.

Trong bảng trên, giả sử bạn có các tiêu chí sau để đánh giá mức độ 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 một trong hai điều kiện trên được đáp ứng, bài kiểm tra cuối cùng được coi là đạt(pass), nếu không thì không đạt(fail).

Công thức nghe có vẻ phức tạp, nhưng trong giây lát, bạn sẽ thấy rằng không phải vậy! Bạn chỉ cần biểu thị hai điều kiện dưới dạng câu lệnh AND và đặt chúng trong hàm OR vì bạn không yêu cầu đáp ứng cả hai điều kiện, chỉ cần đáp ứng:

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

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

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

Ảnh chụp màn hình bên dưới cho thấy rằng chúng ta đã có công thức đúng:

Đương nhiên, không phải bạn chỉ được sử dụng hai hàm AND/OR trong công thức  hàm IF nhiều điều kiện của Excel mà bạn có thể sử dụng nhiều hàm logic khác miễn là:

• Trong Excel 2016, 2013, 2010 và 2007, công thức của bạn bao gồm không quá 255 đối số và tổng độ dài của công thức không vượt quá 8.192 ký tự.

• Trong Excel 2003 trở về trước, bạn có thể sử dụng tối đa 30 đối số và tổng độ dài công thức của bạn không được vượt quá 1.024 ký tự.

Sử dụng nhiều câu lệnh IF trong Excel (các hàm IF lồng nhau)

Nếu bạn cần tạo ra các công thức logic phức tạp hơn cho dữ liệu của mình, bạn có thể bao gồm các câu lệnh IF bổ sung trong các đối số value_if_true và value_if_false của công thức IF trong Excel của bạn. Nhiều hàm IF này được gọi là các hàm IF And và chúng có thể tỏ ra đặc biệt hữu ích nếu bạn muốn công thức của mình trả về 3 hoặc nhiều kết quả khác nhau.

Đây là một ví dụ điển hình: giả sử bạn không chỉ muốn đánh giá kết quả của học sinh là Pass/Fail, mà còn xác định tổng điểm là “Good”, “” và “Poor”. Ví dụ:

Tốt(Good): 60 trở lên (>=60)

Đạt yêu cầu(: từ 40 đến 60 (>40 và <60)

Kém(Bad): 40 trở xuống (<=40)

Để bắt đầu, bạn có thể thêm một cột bổ sung (E) với công thức sau đây để tính tổng các số trong cột C và D:

=C2+D2

Bây giờ, chúng ta hãy viết một hàm IF lồng nhau dựa trên các điều kiện trên. Bạn nên bắt đầu với điều kiện quan trọng nhất và làm cho các chức năng 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 thấy đấy, chỉ một hàm IF lồng nhau là đủ trong trường hợp này. Đương nhiên, bạn có thể lồng nhiều hàm IF hơn nếu bạn muốn. Ví dụ:

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

Công thức trên bổ sung thêm một điều kiện: tổng điểm từ 70 điểm trở lên là đạt tiêu chuẩn “Excellent“(xuất sắc).

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

Giống như các hàm Excel khác, IF cũng có thể được sử dụng trong công thức mảng. 

Ví dụ: công thức SUM/IF của mảng sau minh họa cách bạn có thể tính tổng các ô trong phạm vi được chỉ định dựa trên một điều kiện nhất định thay vì cộng các giá trị thực:

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

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

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

Lưu ý: Vì đây là công thức mảng, hãy nhớ nhấn Ctrl + Shift + Enter để nhập chính xác nhé!

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

Vừa rồi chúng ta đã thảo luận một số ví dụ về cách sử dụng hàm IF trong Excel với các hàm logic AND và OR. Bây giờ, hãy xem những hàm Excel khác có thể được sử dụng với IF nhé!

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

Đối với ví dụ về các hàm IF lồng nhau, chúng ta đã viết công thức trả về xếp hạng (Xuất sắc, Tốt, Đạt yêu cầu hoặc Kém) dựa trên tổng điểm của từng học sinh. Sau đó, chúng ta đã thêm một cột mới với công thức tính tổng điểm trong cột C và D.

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

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

Được rồi, nhưng nếu bảng của bạn chứa nhiều điểm riêng lẻ, chẳng hạn như 5 cột khác nhau trở lên thì sao? Tổng số rất nhiều số liệu trực tiếp trong công thức IF sẽ làm cho nó cực kỳ lớn. Một giải pháp thay thế là nhúng hàm SUM vào kiểm tra logic của IF, như sau:

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

Tương tự, bạn có thể sử dụng các hàm Excel khác trong 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 “))

Kết quả sẽ trả về “Good” nếu điểm trung bình trong cột C:F bằng hoặc lớn hơn 30, “Satisfactory” nếu điểm trung bình từ 29 đến 25, và “Poor” nếu nhỏ hơn 25.

IF và MAX/MIN:

Để tìm điểm cao nhất và thấp nhất, bạn có thể sử dụng hàm MAX và MIN tương ứng. Giả sử rằng cột F là cột tổng điểm, các công thức dưới đây có tác dụng:

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

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

Nếu bạn muốn có cả kết quả Min và Max trong cùng một cột, bạn có thể lồng một trong các hàm trên vào hàm kia, ví dụ:

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

Tương tự, bạn có thể sử dụng hàm IF với các hàm trang tính tùy chỉnh của mình. Ví dụ: bạn có thể sử dụng nó với các hàm GetCellColor / GetCellFontColor để trả về các kết quả khác nhau dựa trên màu ô.

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

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

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

Bạn đã biết một cách để xác định các ô trống và không trống bằng cách sử dụng hàm ISBLANK chưa? Ngoài ra Microsoft Excel cung cấp các hàm tương tự để xác định giá trị văn bản và số – ISTEXT và ISNUMBER, cùng xem chúng hoạt động thế nào: 

Đây là ví dụ về hàm IF lồng nhau trong Excel trả về “Text” nếu ô B1 chứa bất kỳ giá trị văn bản nào, “Number” nếu ô B1 chứa giá trị số và “Blank” nếu ô B1 trống.

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

Lưu ý: công thức trên hiển thị “Number” cho các giá trị số và ngày tháng. Điều này là do Microsoft Excel lưu trữ ngày tháng dưới dạng số, bắt đầu 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 khi, bạn có thể đạt được kết quả mong muốn bằng cách nhúng câu lệnh IF vào một số hàm Excel khác, thay vì sử dụng một hàm khác trong một bài kiểm tra logic.

Đây là một cách khác để bạn có thể sử dụng hàm CONCATENATE và hàm IF cùng nhau:

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

Có lẽ bạn không không cần bất kỳ lời giải thích nào về hoạt động của công thức này, đặc biệt là nhìn vào ảnh chụp màn hình bên dưới:

Hàm IF so với IFERROR và IFNA

Cả hai hàm IFERROR và IFNA, đều được sử dụng để bẫy lỗi trong công thức Excel và thay thế chúng bằng một phép tính khác, giá trị xác định trước hoặc tin nhắn văn bản. Trong các phiên bản Excel trước, bạn có thể sử dụng kết hợp IF ISERROR và IF ISNA để thay thế

Sự khác biệt là IFERROR và ISERROR xử lý tất cả các lỗi Excel có thể xảy ra, bao gồm #VALUE!, #N/A, #NAME?, #REF!, #NUM!, #DIV/0!, and #NULL!. Trong khi IFNA và ISNA chỉ chuyên về lỗi #N/A.

Đây là ví dụ đơn giản nhất về công thức IFERROR:

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

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

Tuy nhiên, trong một số trường hợp, bạn có thể không muốn bẫy tất cả các lỗi mà thay vào đó là kiểm tra điều kiện gây ra một lỗi cụ thể. Ví dụ: để thay thế lỗi chia cho 0 bằng thông báo của riêng bạn, hãy sử dụng công thức IF sau:

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

Trên đây là tất cả về cách sử dụng hàm IF trong Excel. Cảm ơn bạn đã đồng hành cùng Học Excel Online, tiếp tục theo dõi để cập nhật những kiến thức mới nhất nhé!

Xem thêm bài viết thuộc chuyên mục: Thủ Thuật

Xem thêm bài viết thuộc chuyên mục: Thủ Thuật