Trong nội dung trước, tất 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 nội dung 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 phối 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ận xét 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 nội dung này sẽ chứa các ví dụ thực tiễn giúp bạn dễ hiểu nhất, ngoài ra bạn còn tìm hiểu tri thức cơ bản về hàm IFERROR và IFNA nữa đấy!
Table of Contents
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 xác minh logic của bạn chứa hàm AND, Microsoft Excel trả về TRUE nếu toàn bộ các điều kiện được thỏa mãn; 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 xác minh logic, Excel sẽ trả về TRUE nếu bất kỳ điều kiện nào được thỏa mãn; trả về FALSE nếu không.
Để dễ hiểu hơn, tất cả 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 trước nhất ghi trong cột C phải bằng hoặc to hơn 20. Điểm thứ hai ghi trong cột D phải bằng hoặc vượt 30. Chỉ khi thỏa mãn 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 đó phối hợp nó trong đối số logic_test của hàm IF của bạn:
Điều kiện: AND (B2vàgt; = 20, C2vàgt; = 30)
Công thức IF/AND:
=IF((AND(C2vàgt;=20, D2vàgt;=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 Cvàgt; = 20 VÀ giá trị trong cột Dvàgt; = 30. Nếu không, công thức trả về “Fail”. Ảnh chụp màn hình bên dưới minh chứng rằng hàm IF / AND trong Excel của tất cả chúng ta là đúng:
Lưu ý rằng Microsoft Excel sẽ xác minh toàn bộ các điều kiện trong hàm AND, ngay cả khi một trong các điều kiện đã được xác minh nhận xét là FALSE. Hành vi như vậy hơi dị 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 xác minh nếu bất kỳ xác minh nào trước đó trả về FALSE.
Trong thực tiễn, một công thức IF/AND có vẻ đúng có thể dẫn theo 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(A2vàlt;>0,(1/A2)>0.5),”Good”, “Bad”)
Để tránh điều này, chúng ta nên sử dụng hàm IF lồng nhau:
=IF(A2vàlt;>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ể phối 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 thỏa mãn.
Vì vậy, nếu tất cả chúng ta sửa đổi công thức trên theo cách sau:
=IF((OR(C2vàgt;=20, D2vàgt;=30)), “Pass”, “Fail”)
Cột E sẽ có dấu “Pass” nếu điểm trước nhất bằng hoặc to hơn 20 HOẶC điểm thứ hai bằng hoặc to 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ó thời dịp 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 một đ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ận xét 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 chuẩn sau để nhận xét mức độ thành công của học sinh:
Điều kiện 1: cột Cvàgt;=20 và cột Dvàgt;=25
Điều kiện 2: cột Cvàgt;=15 và cột Dvàgt;=20
Nếu một trong hai điều kiện trên được thỏa mãn, bài xác minh 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 thỏa mãn cả hai điều kiện, chỉ cần thỏa mãn:
OR(AND(C2vàgt;=20, D2vàgt;=25), AND(C2vàgt;=15, D2vàgt;=20)
Cuối cùng, việc sử dụng hàm OR ở trên làm phép xác minh logic trong hàm IF và phân phối các đối số value_if_true và value_if_false. Kết quả là bạn sẽ thu được công thức IF sau với nhiều điều kiện AND/OR:
=IF(OR(AND(C2vàgt;=20, D2vàgt;=25), AND(C2vàgt;=15, D2vàgt;=20)), “Pass”, “Fail”)
Ảnh chụp màn hình bên dưới cho thấy rằng tất cả 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ận xét kết quả của học sinh là Pass/Fail, mà còn xác nhận 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)
Để khởi đầ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
Hiện tại, tất cả 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. Chúng ta nên khởi đầu với điều kiện trọng yếu nhất và làm cho các tính 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(E2vàgt;=60, “Good”, IF(E2vàgt;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(E2vàgt;=70, “Excellent”, IF(E2vàgt;=60, “Good”, IF(E2vàgt;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:B5vàlt;=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 một điểm; và 2 điểm được gán cho mỗi giá trị to hơn 1. Và sau đó, hàm SUM cộng kết quả 1 và 2, như trổ tài 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 chuẩn xác nhé!
Sử dụng hàm IF cùng với các hàm Excel khác
Vừa rồi tất cả 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. Hiện tại, 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
So với ví dụ về các hàm IF lồng nhau, tất cả 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 đó, tất cả 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ận 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 rẽ, ví dụ 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 phương án thay thế là nhúng hàm SUM vào xác minh 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 to 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 phân phối 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 chuẩn 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 phương pháp để xác nhận 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 phân phối các hàm tương tự để xác nhận 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ố, xuất 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 có thể đạt được kết quả 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 xác minh 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ó thể 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, nhấ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ận 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 phối hợp IF ISERROR và IF ISNA để thay thế
Sự khác biệt là IFERROR và ISERROR xử lý toàn bộ 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 toàn bộ các lỗi mà thay vào đó là xác minh đ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à toàn bộ về cách sử dụng hàm IF trong Excel. Cảm ơn bạn đã gắn bó cùng Học Excel Online, tiếp tục theo dõi để update những tri thức tiên tiến nhất nhé!
