Trong nội dung bài viết trước, họ đã biết cách sử dụng hàm IF với những con số, văn bản và tháng ngày 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 thực hiện hàm IF kết phù hợp với các hàm thông dụng không giống nhé!
Trong quy trình sử dụng Excel, song lúc các bạn sẽ đối khía cạnh với những bài toán phức tạp, buộc phải phải review nhiều điều kiện cùng một lúc, nghĩa là các bạn phải xây dựng các phép logic phức hợ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 vẫn chứa những ví dụ thực tế giúp cho bạn dễ hiểu nhất, dường như bạn còn mày mò kiến thức cơ phiên bản về hàm IFERROR cùng IFNA nữa đấy!
Xem nhanh
Cách áp dụng hàm IF vào Excel với tương đối nhiều điều kiện
Sử dụng hàm IF cùng với những hàm Excel khác
Cách áp dụng hàm IF vào Excel với tương đối nhiều điều kiện
Có 2 nhiều loại cơ bạn dạng của hàm IF nhiều đk – & và OR. Bởi vì đó, hàm IF của người sử dụng phải nhúng một hàm AND hoặc OR tương xứng trong phép demo logic.Bạn đang xem: Sử dụng hàm if và and
Hàm AND: Nếu soát sổ 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 đáp ứng; còn nếu không nó trả về FALSE.Hàm OR: vào trường thích hợp bạn áp dụng hàm OR trong bình chọn logic, Excel đang trả về TRUE nếu bất kỳ điều kiện nào được đáp ứng; trả về FALSE giả dụ không.
Để dễ nắm bắt hơn, họ hãy coi một vài lấy ví dụ IF với nhiều điều kiện.
Ví dụ 1. Sử dụng hàm IF và AND vào ExcelGiả sử, bạn có một bảng hiệu quả của nhì điểm thi. Điểm trước tiên ghi trong cột C phải bằng hoặc lớn hơn 20. Điểm sản phẩm công nghệ hai ghi trong cột D phải bằng hoặc thừa 30. Chỉ khi đáp ứng nhu cầu cả hai đk trên, học sinh mới được điểm cuối cùng. Thi.
Cách dễ nhất để sản xuất một công thức thích hợp là viết ra đk trước, sau đó kết hòa hợp nó vào đố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 thuận tiện phải không? bí quyết trên yêu ước Excel trả về “pass” nếu cực hiếm trong cột C> = 20 VÀ cực hiếm trong cột D> = 30. Trường hợp không, phương pháp trả về “Fail”. Ảnh chụp màn hình bên dưới chứng minh rằng hàm IF / và trong Excel của chúng ta là đúng:
Lưu ý rằng Microsoft Excel đang kiểm tra toàn bộ các điều kiện trong hàm AND, trong cả khi một trong những điều kiện đã làm được kiểm tra đánh giá là FALSE. Hành vi vì thế hơi không bình 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 sau sẽ không được bình chọn nếu bất kỳ kiểm tra làm sao 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ày tính rõ ràng này. Ví dụ: công thức sau đây sẽ trả về “Divide by Zero Error” (#DIV/0!) nếu ô A2 bởi 0:
=IF(AND(A20,(1/A2)>0.5),”Good”, “Bad”)
Để né điều này, bạn nên thực hiện hàm IF lồng nhau:
=IF(A20, IF((1/A2)>0.5, “Good”, “Bad”), “Bad”)
Ví dụ 2. áp dụng IF cùng với hàm OR trong ExcelBạn hoàn toà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 trao đổi ở trên là công thức này trả về TRUE nếu ít nhất một trong những điều khiếu nại được đáp ứng.
Vì vậy, nếu bọn họ sửa đổi phương pháp trên theo phong cách sau:
=IF((OR(C2>=20, D2>=30)), “Pass”, “Fail”)
Cột E sẽ sở hữu dấu “Pass” trường hợp điểm trước tiên bằng hoặc to hơn 20 HOẶC điểm máy hai bằng hoặc lớn hơn 30.
Như chúng ta thấy trong ảnh chụp màn hình bên dưới, những sinh viên có thời cơ tốt hơn nhằm vượt qua kỳ thi vào cuối kỳ với những điều kiện như vậy (Scott quan trọng đặc biệt không suôn sẻ khi chỉ trượt 1 điểm:)
F với nhiều điều kiện IF ANDVí dụ 3. áp dụng IF với những 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 vài tập hợp nhiều điều kiện, bạn sẽ phải áp dụng cả hai hàm AND và OR và một lúc.
Trong bảng trên, giả sử chúng ta có các tiêu chuẩn 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 cùng cột D>=20
Nếu một trong hai đk trên được đáp ứng, bài kiểm tra sau cùng được xem như là đạt(pass), còn nếu không thì ko đạt(fail).
Công thức nghe có vẻ như phức tạp, tuy thế trong giây lát, các bạn sẽ thấy rằng chưa hẳn vậy! các bạn chỉ cần biểu lộ hai điều kiện dưới dạng câu lệnh & và đặt nó trong hàm OR vì chúng ta không yêu cầu đáp ứng cả nhị đ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 áp dụng hàm OR sinh hoạt trên lấy lệ kiểm tra ngắn gọn xúc tích trong hàm IF và hỗ trợ các đối số value_if_true với value_if_false. Kết quả là bạn sẽ nhận được phương pháp IF sau với rất nhiều điều khiếu nại 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 họ đã gồm công thức đúng:
Đương nhiên, không hẳn bạn chỉ được sử dụng hai hàm AND/OR trong cách làm hàm IF nhiều đk của Excel mà chúng ta cũng có thể sử dụng các hàm ngắn gọn xúc tích khác miễn là:
Trong Excel 2016, 2013, 2010 cùng 2007, phương pháp của bạn bao hàm không vượt 255 đối số cùng tổng độ dài của cách làm không vượt vượt 8.192 ký tự.Trong Excel 2003 về bên trước, chúng ta có thể sử dụng tối đa 30 đối số với tổng độ nhiều năm công thức của khách hàng không được vượt thừa 1.024 ký kết tự.Sử dụng những 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 bí quyết logic phức hợp hơn cho tài liệu của mình, bạn cũng có thể bao gồm các câu lệnh IF bổ sung trong những đối số value_if_true với value_if_false của phương pháp IF vào Excel của bạn. Những hàm IF này được hotline là các hàm IF And với chúng rất có thể tỏ ra đặc biệt hữu ích nếu bạn muốn công thức của bản thân trả về 3 hoặc nhiều tác dụng khác nhau.
Đây là 1 ví dụ điển hình: mang sử bạn không những muốn tấn công giá hiệu quả của học viên là Pass/Fail, cơ mà còn khẳng định tổng điểm là “Good”, “Satisfactory” cùng “Poor”. Ví dụ:
Tốt(Good): 60 trở lên (>=60)
Đạt yêu thương cầu(Satisfactory): tự 40 mang đến 60 (>40 với
Kém(Bad): 40 trở xuống (
Để bắt đầu, chúng ta cũng có thể thêm một cột bổ sung (E) với công thức dưới đây để tính tổng các số vào cột C và D:
=C2+D2
Bây giờ, bọn họ 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 ban đầu với điều kiện quan trọng nhất và tạo nên các tác dụng của các bạn càng đơn giản và dễ dàng càng tốt. Bí quyết IF lồng nhau như sau:
=IF(E2>=60, “Good”, IF(E2>40, “Satisfactory”, “Poor “))
Như các bạn thấy đấy, có một hàm IF lồng nhau là đầy đủ trong trường hòa hợp này. Đương nhiên, bạn có thể lồng nhiều hàm IF hơn nếu như khách hàng 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 trường đoản cú 70 điểm trở lên là đã đạt được tiêu chuẩn chỉnh “Excellent“(xuất sắc).
Sử dụng Excel IF trong phương pháp mảng
Giống như các hàm Excel khác, IF cũng có thể được thực hiện trong bí quyết mảng.
Ví dụ: cách làm SUM/IF của mảng sau minh họa cách bạn cũng có thể tính tổng các ô trong phạm vi được chỉ định dựa vào một điều kiện nhất định thay vì cộng các giá trị thực:
=SUM(IF(B1:B5Công thức này đã gán một số trong những “điểm” tốt nhất định cho từng giá trị vào cột B – trường hợp một giá trị bởi hoặc nhỏ dại hơn 1, nó tương đương với cùng một điểm; với 2 điểm được gán cho mỗi giá trị lớn hơn 1. Và sau đó, hàm SUM cộng hiệu quả 1 cùng 2, như biểu đạt trong ảnh chụp screen 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ớ dấn Ctrl + Shift + Enter để nhập đúng mực nhé!
Sử dụng hàm IF thuộc với các hàm Excel khác
Vừa rồi chúng ta đã bàn luận một số ví dụ về kiểu cách sử dụng hàm IF vào Excel với các hàm lô ghích AND và OR. Bây giờ, hãy xem gần như hàm Excel khác rất có thể được thực hiện với IF nhé!
Ví dụ 1. Sử dụng IF với những hàm SUM, AVERAGE, MIN với 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 thứ hạng (Xuất sắc, Tốt, Đạt yêu cầu hoặc Kém) dựa vào tổng điểm của từng học tập sinh. Sau đó, chúng ta đã thêm một cột bắt đầu với cách làm 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 doanh nghiệp có cấu tạo được khẳng định trước và cấm đoán phép ngẫu nhiên sửa thay đổi nào? vào trường hợp này, thay bởi vì thêm cột trợ giúp, bạn có thể thêm cực hiếm trực tiếp vào phương pháp If của mình, như sau:
=IF((C2+D2)>=60, “Good”, IF((C2+D2)=>40, “Satisfactory”, “Poor “))
Được rồi, dẫu vậy nếu bảng của bạn đựng nhiều điểm riêng lẻ, ví dụ như 5 cột khác nhau trở lên thì sao? Tổng số không hề ít số liệu trực tiếp trong cách làm IF sẽ khiến cho nó cực kì lớn. Một phương án thay cố là nhúng hàm SUM vào kiểm tra ngắn gọn xúc tích của IF, như sau:
=IF(SUM(C2:F2)>=120, “Good”, IF(SUM(C2:F2)>=90, “Satisfactory”, “Poor “))
Tương tự, chúng ta có thể sử dụng các hàm Excel không giống trong phương pháp IF của bạn:
IF cùng AVERAGE:
=IF(AVERAGE(C2:F2)>=30,”Good”,IF(AVERAGE(C2:F2)>=25,”Satisfactory”,”Poor “))
Kết quả đang trả về “Good” nếu như điểm vừa đủ trong cột C:F bởi hoặc to hơn 30, “Satisfactory” nếu điểm vừa phải từ 29 cho 25, và “Poor” nếu bé dại hơn 25.
IF cùng MAX/MIN:
Để kiếm tìm điểm tối đa và tốt nhất, chúng ta cũng có thể sử dụng hàm MAX với MIN tương ứng. Giả sử rằng cột F là cột tổng điểm, những công thức sau đâ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 cùng Max trong cùng một cột, chúng ta cũng có thể lồng một trong những 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ự, chúng ta có thể sử dụng hàm IF với các hàm trang tính tùy chỉnh thiết lập của mình. Ví dụ: bạn có thể sử dụng nó với những hàm Get
Cell
Color / Get
Cell
Font
Color để trả về các tác dụng khác nhau dựa vào màu ô.
Ngoài ra, Excel còn hỗ trợ một số hàm IF quan trọng đặc biệt để phân tích và đo lường và thống kê dữ liệu dựa trên các điều kiện không giống nhau.
Ví dụ: để đếm số lần mở ra của cực hiếm văn bạn dạng hoặc số dựa vào một hoặc những điều kiện, bạn cũng có thể sử dụng COUNTIF với COUNTIFS tương ứng. Để tìm tổng giá trị dựa trên nhiều điều kiện, hãy áp dụng hàm SUMIF hoặc SUMIFS. Để tính quý hiếm 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 cùng với hàm ISNUMBER và ISTEXTBạn đã biết một phương pháp để xác định những ô trống cùng 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 giống như để xác minh giá trị văn bạn dạng và số – ISTEXT và ISNUMBER, thuộc xem chúng chuyển động thế nào:
Đây là lấy ví dụ về hàm IF lồng nhau vào Excel trả về “Text” trường hợp ô B1 chứa ngẫu nhiên giá trị văn bạn dạng nào, “Number” trường hợp ô B1 chứa giá trị số cùng “Blank” nếu ô B1 trống.
=IF(ISTEXT(B1), “Text”, IF(ISNUMBER(B1), “Number”, IF(ISBLANK(B1), “Blank”, “”)))
Lưu ý: phương pháp 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 tàng trữ ngày tháng bên dưới dạng số, bắt đầu từ ngày 1 tháng 1 năm 1900, tương tự với 1.
Ví dụ 3. Sử dụng tác dụng trả về do IF trong một hàm Excel khácĐôi khi, bạn cũng có thể đạt được công dụng mong muốn bằng cách nhúng câu lệnh IF vào một số hàm Excel khác, thế vì sử dụng một hàm khác trong một bài kiểm tra logic.
Đây là một trong cách khác để chúng ta cũng có thể sử dụng hàm CONCATENATE cùng hàm IF thuộc nhau:
=CONCATENATE(“You performed “, IF(C1>5,”fantastic!”, “well”))
Có lẽ các bạn không không cần bất kỳ lời phân tích và lý giải nào về buổi giao lưu của công thức này, đặc biệt là nhìn vào hình ảnh chụp màn hình bên dưới:
10 Câu lệnh IF với rất nhiều điều khiếu nại IF ANDHàm IF đối với IFERROR với IFNA
Cả hai hàm IFERROR cùng IFNA, số đông được sử dụng để bẫy lỗi trong phương pháp Excel và thay thế chúng bằng một phép tính khác, giá chỉ trị xác minh trước hoặc lời nhắn văn bản. Trong số phiên bạn dạng Excel trước, bạn cũng có thể sử dụng phối kết hợp IF ISERROR với IF ISNA để gắng thế
Sự biệt lập là IFERROR và ISERROR xử lý toàn bộ các lỗi Excel có thể xảy ra, bao hàm #VALUE!, #N/A, #NAME?, #REF!, #NUM!, #DIV/0!, and #NULL!. Trong những lúc IFNA và ISNA chỉ chăm về lỗi #N/A.
Xem thêm: Xem phim huyền của ôn noãn tập 2 ), tác giả an ninh, huyền của ôn noãn 2023
Đây là ví dụ đơn giản dễ dàng nhất về phương pháp IFERROR:
=IFERROR(B2/C2, “Sorry, an error has occurred”)
11 Câu lệnh IF với khá nhiều điều kiện IF ANDNhư chúng ta thấy trong hình ảnh chụp screen ở trên, cột D hiển thị mến số của phép chia một quý giá trong cột B cho một giá trị trong cột C. Bạn cũng có thể thấy hai thông tin lỗi trong ô D2 cùng D5 vì mọi người đều biết rằng các bạn không thể chia một số bằng không.
Tuy nhiên, trong một số trong những trường hợp, bạn cũng có thể không mong bẫy tất cả các lỗi mà cố vào chính là kiểm tra đk gây ra một lỗi nắm thể. Ví dụ: để sửa chữa lỗi phân tách cho 0 bằng thông báo của riêng rẽ bạn, hãy áp 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 vào Excel. Cảm ơn các bạn đã đồng hành cùng Học Excel Online, liên tiếp theo dõi để cập nhật những loài kiến thức tiên tiến nhất nhé!