Để phân tích dữ liệu một cách mạnh bạo mẽ, bạn có thể phải đánh giá nhiều điều kiện cùng một lúc, có nghĩa là bạn nên xây dựng các phép demo logic phức hợp hơn bằng cách sử dụng các hàm IF vào một công thức. Những công thức ví dụ dưới đây sẽ cho chính mình thấy làm vắt nào để triển khai đúng. Bạn cũng biến thành học cách thực hiện IF trong những công thức mảng và tìm hiểu những điều cơ bạn dạng của hàm IFEFFOR cùng IFNA. Tất cả sẽ có trong bài viết của Học Excel Online


Làm nạm nào để thực hiện hàm IF với rất nhiều điều kiện:

Nói bắt lại, rất có thể có 2 loại điều kiện cơ bản – cùng với hàm ngắn gọn xúc tích AND và OR. Do đó, hàm IF của bạn nên nhúng một hàm and và OR để gia công phép thử xúc tích tương ứng.

Bạn đang xem: Iferror là hàm gì

Hàm AND . Nếu phép test logic của chúng ta chứa hàm và và Microsoft Excel trả về TRUE nếu tất cả các điều kiện đều được đáp ứng; Nếu ko nó đang trả về FALSE.Hàm OR. Trong trường hòa hợp bạn áp dụng hàm OR vào phép test logic, Excel trả về TRUE nếu đáp ứng được bất kỳ đk nào ; FALSE nếu như không.

Để minh họa xuất sắc hơn về sự việc này, họ hãy thuộc xem xét một trong những ví dụ IF với tương đối nhiều điều kiện.

Ví dụ 1. Thực hiện IF và AND vào Excel

Giả sử chúng ta có một bảng với tác dụng của nhị điểm thi. Điểm số đầu tiên, được lưu trữ trong cột C, phải bằng hoặc lớn hơn 20. Điểm số lắp thêm hai, được liệt kê vào cột D, phải bằng hoặc rộng 30. Chỉ lúc nào cả hai điều kiện trên được đáp ứng, thì học sinh mới vượt qua kỳ thi.

Cách đơn giản nhất để tạo thành một công thức thích hợp là ghi lại điều kiện đầu tiên, và kế tiếp kết hòa hợp nó trong đối số logic_test của hàm IF:

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


*

*

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

Dễ dàng đề nghị không? Công thức cho biết thêm Excel trả về “Pass” nếu một giá trị cột C> = trăng tròn và quý giá trong cột D> = 30. Nếu không, cách làm sẽ trả về “Fail”. Hình dưới đây minh chứng rằng hàm IF / and của bọn họ là chủ yếu xác:

*
1 – hàm If nâng cao: hàm iferror, hàm if những điều kiện

Chú ý. Microsoft Excel kiểm tra tất cả các điều kiện trong hàm và và trong cả khi một trong các điều kiện đã được kiểm tra đánh giá là FALSE. Điều này là hơi bất thường vì trong hầu như các ngôn từ lập trình, những điều kiện tiếp sau không bắt buộc kiểm tra nếu ngẫu nhiên kiểm tra trước này đã trả về FALSE.

Trên thực tế, một bí quyết IF / & đúng rất có thể bị lỗi do kĩ năng này. Ví dụ, công thức =IF(AND(A20,(1/A2)>0.5),”Good”, “Bad”) sẽ trả lại “lỗi chia cho 0” (# DIV / 0!) trường hợp ô A2 bằng 0. Để né điều này, bạn nên sử dụng IF lồng ghép :

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

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

Bạn sử dụng phối hợp các hàm IF & OR theo một biện pháp tương tự. Sự khác biệt từ cách làm IF / and được bàn luận ở bên trên là Excel trả về TRUE nếu đáp ứng được ít độc nhất một trong các đk được chỉ định.

Vì vậy, nếu chúng ta sửa đổi phương pháp trên theo 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 số thứ nhất bằng hoặc lớn hơn 20 hoặc điểm số sản phẩm công nghệ hai bằng hoặc to hơn 30.

Như chúng ta thấy trong hình bên dưới, những học sinh của họ có thời cơ tốt hơn nhằm vượt qua kỳ thi ở đầu cuối với các điều kiện do vậy (bạn Scott thật rủi ro mắn khi chỉ thiếu thốn 1 điểm.)

*
2 – hàm If nâng cao: hàm iferror, hàm if nhiều điều kiện

Đương nhiên, bạn không bị giới hạn chỉ thực hiện hai hàm and / OR trong phương pháp IF của mình. Bạn hoàn toàn có thể sử dụng tương đối nhiều hàm ngắn gọn xúc tích theo yêu mong của công việc, với đk là:

Trong Excel 2013, 2010 với 2007, công thức của công ty không nhiều hơn thế nữa 255 đối số, cùng tổng chiều dài của bí quyết không vượt vượt 8192 ký tự.Trong Excel 2003 với các phiên bản cũ hơn, chúng ta có thể sử dụng buổi tối đa 30 đối số cùng tổng chiều lâu năm của công thức của bạn sẽ không vượt quá 1.024 ký tự.

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

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

Trong bảng trên, mang sử bạn có các tiêu chí sau để đánh túi tiền công của học sinh:

Điều khiếu nại 1: cột C> = đôi mươi và cột D> = 25Điều kiện 2: cột C> = 15 với cột D> = 20

Nếu một trong hai điều kiện trên được đáp ứng, tấn công giá ở đầu cuối vẫn là đạt, nếu như không – không đạti.

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

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

Cuối cùng, thực hiện hàm OR sinh sống trên như là phép thử logic trong hàm IF và cung cấp các thông số value_if_true và value_if_false. Kết trái là, các bạn sẽ nhận được phương pháp IF có nhiều điều kiện & / OR:

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

Hình sau đây chỉ ra rằng họ có phương pháp đúng:

3 – hàm If nâng cao: hàm iferror, hàm if các điều kiện

Sử dụng các hàm IF trong Excel (các hàm IF lồng nhau):

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

Đây là 1 trong những ví dụ điển hình: đưa sử chúng ta không chỉ việc đủ điều kiện hiệu quả của sv như Pass / Fail, cơ mà còn xác minh tổng số điểm là “Good“, “Satisfactory” với “Poor“. . Ví dụ:

Good: 60 trở lên (> = 60)Satisfactory: trường đoản cú 40 mang đến 60 (> 40 cùng Poor: 40 hoặc thấp hơn (

Để bắt đầu, bạn có thể thêm một cột bổ sung cập nhật (E) bằng công thức dưới đây để cộng những số vào cột C và D: =C2+D2

4 – hàm If nâng cao: hàm iferror, hàm if những điều kiện

Và bây giờ, hãy viết một hàm IF lồng nhau dựa trên các điều kiện trên. Đó được xem là một bài xích thực hành xuất sắc để ban đầu với điều kiện đặc biệt nhất và tạo nên cáchàm của khách hàng càng đơn giản càng tốt. Công thức IF lồng nhau của bọn họ như sau:

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

Như các bạn thấy, chỉ việc một hàm IF lồng nhau là đủ trong trường hòa hợp này. Đương nhiên, bạn cũng có thể tổ chức thêm hàm IF nếu như 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ừ bỏ 70 điểm trở lên được nhìn nhận xếp hạng là “ Excellent “.

*
5 – hàm If nâng cao: hàm iferror, hàm if những điều kiện

Tổng hợp tài liệu học Excel cơ bản tốt nhất 2018

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

Giống như các hàm Excel khác, IF có thể được áp dụng trong các bí quyết mảng . Bạn hoàn toàn có thể cần một bí quyết như vậy nếu bạn muốn đánh giá đều thành phần của mảng khi thực hiện lệnh IF.

Ví dụ, bí quyết SUM / IF mảng dưới đây thể hiện giải pháp mà bạn có thể 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 kia thay vày cộng quý hiếm thực tế:

=SUM(IF(B1:B5

*
6 – hàm If nâng cao: hàm iferror, hàm if các điều kiện

Chú ý. Vì đó là một bí quyết mảng, hãy nhớ nhấn Ctrl + Shift + Enter để nhập thiết yếu xác.

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

Trước đây trong gợi ý này, bọn họ đã đàm luận một vài ví dụ về bí quyết IF biểu lộ cách thực hiện hàm IF với những hàm ngắn gọn xúc tích AND với OR. Bây giờ, họ hãy xem rất nhiều gì các hàm Excel khác rất có thể được thực hiện với IF và ích lợi chúng đem về cho bạn.

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

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

Nhưng điều gì sẽ xẩy ra nếu bảng của bạn có một kết cấu được xác định trước nhưng mà không được cho phép sửa đổi? Trong trường hòa hợp này, thay vì thêm cột phụ, bạn có thể thêm các giá trị trực tiếp vào bí quyết IF của bạn, 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 chứa được nhiều cột điểm, chẳng hạn tới 5 cột không giống nhau hoặc các hơn? Tổng hợp không hề ít con số trực tiếp trong phương pháp IF sẽ khiến cho nó rất dài. Cách sửa chữa là nhúng hàm SUM trong phép thử lô ghích của IF, như sau:

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

*
7 – hàm If nâng cao: hàm iferror, hàm if những điều kiện

Tương từ như vậy, bạn cũng có thể sử dụng những hàm Excel khác để triển khai phép demo logic trong số công thức IF của bạn:

IF cùng AVERAGE:

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

Nó trả lại hiệu quả ” Good ” nếu điểm trung bình trong các cột C: F bởi hoặc to hơn 30, ” Satisfactory ” nếu điểm vừa đủ từ 29 đến 25, với ” Poor ” nếu bên dưới 25.

Xem thêm: Cách Làm Thạch Hoa Quả Nhiều Tầng Đẹp Mắt Tráng Miệng Cực Hấp Dẫn

IF với MAX / MIN:

Để tìm kiếm điểm cao nhất và phải chăng nhất, bạn có thể sử dụng các hàm MAX và MIN, tương ứng. Giả sử rằng cột F là cột toàn bô điểm, các 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 bạn có nhu cầu có cả hiệu quả Min cùng Max trong và một cột, bạn có thể 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”, “”))

*
8 – hàm If nâng cao: hàm iferror, hàm if các điều kiện

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

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

Ví dụ: để đếm số lần xuất hiện thêm của một quý hiếm văn bản hoặc số dựa trên một điều kiện duy tốt nhất hoặc nhiều điều kiện, chúng ta cũng có thể sử dụng COUNTIF với COUNTIFS tương ứng. Để đưa ra một tổng những giá trị dựa vào (các) điều kiện được chỉ định, sử dụng những hàm SUMIF hoặc SUMIFS. Để tính 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ới ISTEXT

Bạn đã biết cách để phát hiện tại các ô trống và không trống bằng cách thực hiện hàm ISBLANK. Microsoft Excel hỗ trợ các hàm tương tự để xác định các cực hiếm văn bản và số – ISTEXT và ISNUMBER.

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

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

*
9 – hàm If nâng cao: hàm iferror, hàm if những điều kiện

 

Chú ý. Hãy chú ý rằng cách làm trên hiển thị ” number ” cho các giá trị với ngày tháng. Điều này là do Microsoft Excel giữ trữ những ngày theo mô hình số, bắt đầu từ ngày một tháng 1 năm 1900, tương đương với 1.

Ví dụ 3. Sử dụng công dụng trả về do 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 IF trong một trong những hàm Excel khác, nuốm vì thực hiện một hàm không giống trong một phép test logic.

Đây là 1 trong những cách khác để chúng ta có thể sử dụng những hàm CONCATINATE với IF thuộc nhau:

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

Tôi tin rằng bạn đa số không cần bất kỳ lời giải thích nào về công thức nữa, nhất là nhìn vào hình dưới đây:

*
10 – hàm If nâng cao: hàm iferror, hàm if những điều kiện

Hàm IFERROR cùng hàm IFNA

Cả hai hàm – hàm IFEFFOR với IFNA – phần nhiều được sử dụng trong Excel nhằm bắt gần như lỗi trong các công thức. Và cả hai hàm hoàn toàn có thể trả về một giá chỉ trị đặc biệt quan trọng mà các bạn chỉ định ví như một công thức tạo thành lỗi. Ví như không, kết quả của công thức sẽ tiến hành trả lại.

Sự khác hoàn toàn là hàm IFEFFOR xử lý toàn bộ các lỗi Excel, bao gồm cả #VALUE !, # N / A, #NAME ?, #REF !, #NUM !, # DIV / 0 !, với #NULL !. Mặc dù hàmIFNA chỉ chuyên về lỗi # N / A, đúng chuẩn như tên của nó vậy.

Cú pháp của những hàm Error như sau.

Hàm IFERROR

IFERROR (value value_if_error)

Hàm IFNA

IFNA (value, value_if_na)

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

Tham số đồ vật hai (value_if_error / value_if_na) là quý hiếm trả lại trường hợp công thức đánh giá lỗi (bất kỳ lỗi như thế nào trong trường hợp hàm IFEFFOR, xuất xắc lỗi # N / A vào trường hòa hợp của IFNA).

Chú ý. Nếu ngẫu nhiên đối số nào là 1 trong ô rỗng, cả nhì hàm Error sẽ xử trí nó như 1 chuỗi trống rỗng (“”).

Ví dụ sau chứng tỏ việc áp dụng hàm IFERROR vô cùng đơn giản:

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

*
11 – hàm If nâng cao: hàm iferror, hàm if các điều kiện

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

Trong một trong những trường hợp, xuất sắc hơn bạn nên sử dụng hàm IF để ngăn chặn một lỗi tiếp đến ISERROR hoặc ISNA để bắt lỗi. Thứ nhất, đó là 1 trong những cách cấp tốc hơn (về CPU) và thứ nhì nó là một trong những cách thực hành lập trình tốt. Ví dụ, phương pháp IF sau đây tạo ra công dụng tương từ bỏ như hàm IFERROR đã trình diễn ở trên:

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

Nhưng vớ nhiên, có những trường hợp khi chúng ta không thể soát sổ trước tất cả các hàm tham số, đặc biệt là các phương pháp rất phức tạp, và dự kiến được toàn bộ các lỗi hoàn toàn có thể xảy ra. Trong phần nhiều trường vừa lòng như vậy, các hàm ISERROR () và IFNA () thực sự có ích.

Những kiến thức bạn sẽ xem nằm trong khóa học EX101 – Excel từ bỏ cơ phiên bản tới cải thiện dành cho những người đi làm của hệ thống Học Excel Online. Trong khóa huấn luyện này các bạn sẽ được khám phá những kiến thức và kỹ năng đầy đủ, có hệ thống về những hàm, các công vắt của Excel, bài toán ứng dụng các hàm, pháp luật đó vào vào công việc. Hiện giờ hệ thống có rất nhiều ưu đãi cho mình khi đăng ký khóa huấn luyện này.