Để phân tích tài liệu một cách dũng mạnh mẽ, chúng ta cũng có thể phải nhận xét nhiều điều kiện cùng một lúc, tức là bạn đề xuất xây dựng những phép thử logic phức hợp hơn bằng phương pháp sử dụng các hàm IF trong một công thức. Các công thức ví dụ dưới đây sẽ cho mình thấy làm cố kỉnh nào để thực hiện đúng. Bạn cũng trở thành học cách áp dụng IF trong các công thức mảng và mày mò những điều cơ phiên bản của hàm IFEFFOR và IFNA. Vớ cả sẽ có được trong nội dung bài viết của Học Excel Online


Làm cầm cố nào để thực hiện hàm IF với khá nhiều điều kiện:

Nói cầm lại, rất có thể có 2 loại đk cơ bạn dạng – với hàm logic AND cùng OR. Do đó, hàm IF của người tiêu dùng nên nhúng một hàm và và OR để gia công phép thử lô ghích tương ứng.

Bạn đang xem: Hàm iferror để làm gì

Hàm AND . Nếu phép demo logic của chúng ta chứa hàm & và Microsoft Excel trả về TRUE nếu tất cả các điều kiện đều được đáp ứng; Nếu không nó sẽ trả về FALSE.Hàm OR. Trong trường thích hợp bạn thực hiện 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 không.

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

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

Giả sử các bạn có một bảng với hiệu quả của nhì điểm thi. Điểm số đầu tiên, được lưu trữ trong cột C, phải bởi hoặc lớn hơn 20. Điểm số thiết bị hai, được liệt kê trong 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 viên mới thừa qua kỳ thi.

Cách dễ dàng nhất để tạo thành một công thức phù hợp là lưu lại điều khiếu nại đầu tiên, và kế tiếp kết đúng theo nó vào đối số logic_test của hàm IF:

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


*

*

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

Dễ dàng bắt buộc không? Công thức cho biết thêm Excel trả về “Pass” trường hợp một cực hiếm cột C> = đôi mươi và quý hiếm trong cột D> = 30. Nếu không, phương pháp sẽ trả về “Fail”. Hình dưới đây minh chứng rằng hàm IF / và của bọn họ là bao gồm 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ả những điều kiện trong hàm and và ngay cả khi một trong những điều kiện đã được kiểm tra nhận xét là FALSE. Điều này là hơi phi lý vì trong hầu như các ngôn ngữ lập trình, những điều kiện tiếp theo sau không nên 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 công thức IF / và đúng có thể bị lỗi do công dụng này. Ví dụ, công thức =IF(AND(A20,(1/A2)>0.5),”Good”, “Bad”) đang trả lại “lỗi phân chia cho 0” (# DIV / 0!) nếu như ô A2 bởi 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. Thực hiện hàm IF với OR vào Excel

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

Vì vậy, nếu bọn họ 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” giả dụ điểm số trước tiên bằng hoặc lớn hơn 20 hoặc điểm số đồ vật hai bởi hoặc to hơn 30.

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

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

Đương nhiên, bạn không bị giới hạn chỉ sử dụng hai hàm & / OR trong cách làm IF của mình. Bạn rất có thể sử dụng rất nhiều hàm lô ghích theo yêu cầu của công việc, với điều kiện là:

Trong Excel 2013, 2010 với 2007, công thức của người tiêu dùng không nhiều hơn 255 đối số, và tổng chiều dài của phương pháp không vượt thừa 8192 ký kết tự.Trong Excel 2003 với các bạn dạng cũ hơn, bạn cũng có thể sử dụng buổi tối đa 30 đối số và tổng chiều nhiều năm của công thức của bạn sẽ không vượt quá 1.024 cam kết tự.

Ví dụ 3. Thực hiện IF với những hàm AND & OR

Trong ngôi trường hợp bạn phải nhận xét dữ liệu dựa vào một đôi điều kiện, các bạn sẽ phải sử dụng cả hàm and và OR tại 1 thời điểm.

Trong bảng trên, đưa sử chúng ta có các tiêu chuẩn sau nhằm đánh chi phí công của học tập sinh:

Điều kiện 1: cột C> = trăng tròn và cột D> = 25Điều khiếu nại 2: cột C> = 15 cùng cột D> = 20

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

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

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

Cuối cùng, sử dụng hàm OR sinh hoạt trên như thể phép thử ngắn gọn xúc tích trong hàm IF và cung cấp các tham số value_if_true và value_if_false. Kết quả 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 dưới đây chỉ ra rằng bọn họ có phương pháp đúng:

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

Sử dụng nhiều 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ử súc tích cho dữ liệu của mình, bạn có thể thêm các câu lệnh IF bổ sung trong các đối số value_if_true và value_if_false của bí quyết IF của bạn. Các hàm IF này được điện thoại tư vấn 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 tiêu dùng trả lại ba công dụng trở lên.

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

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

Để bắt đầu, chúng ta cũng có thể thêm một cột bổ sung cập nhật (E) bằng công thức tiếp sau đây để cộng các số trong cột C và D: =C2+D2

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

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

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

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

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

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

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

Giống như những hàm Excel khác, IF rất có thể được sử dụng trong các cách làm mảng . Bạn có thể cần một bí quyết như vậy nếu bạn muốn đánh giá các thành phần của mảng khi tiến hành lệnh IF.

Ví dụ, phương pháp SUM / IF mảng dưới đây thể hiện phương pháp mà chúng ta có thể tổng hợp những ô trong phạm vi được chỉ định dựa vào một đk nào kia thay bởi cộng giá trị thực tế:

=SUM(IF(B1:B5

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

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

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

Trước phía trên trong giải đáp này, bọn họ đã bàn thảo một vài ví dụ về công thức IF biểu đạt cách áp dụng hàm IF với những hàm xúc tích và ngắn gọn AND và OR. Bây giờ, bọn họ hãy xem phần đa gì những hàm Excel khác hoàn toàn có thể được áp dụng với IF và công dụng chúng mang lại cho bạn.

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

Khi thảo luận các hàm IF lồng nhau , bọn họ đã viết phương pháp trả về các 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 tập sinh. Như các bạn thấy, họ đã thêm 1 cột new với phương pháp tính tổng điểm từ những cột C và D.

Nhưng điều gì sẽ xẩy ra nếu bảng của khách hàng có một kết cấu được xác định trước mà lại không được cho phép sửa đổi? Trong trường đúng theo này, thay vì thêm cột phụ, chúng ta có thể thêm những giá trị thẳng vào phương pháp IF của bạn, như sau:

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

Được rồi, tuy vậy nếu bảng của bạn đựng nhiều cột điểm, ví dụ điển hình tới 5 cột không giống nhau hoặc các hơn? Tổng hợp không ít con số thẳng trong công thức IF sẽ làm cho nó hết sức dài. Cách sửa chữa thay thế là nhúng hàm SUM trong phép thử xúc tí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, chúng ta có thể sử dụng các hàm Excel khác để triển khai phép demo logic trong các công thức IF của bạn:

IF với AVERAGE:

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

Nó trả lại công dụng ” Good ” giả dụ điểm trung bình trong số cột C: F bằng hoặc to hơn 30, ” Satisfactory ” ví như điểm vừa phải từ 29 cho 25, với ” Poor ” nếu bên dưới 25.

Xem thêm: Các Vấn Đề Liên Quan Đến Mật Khẩu Cấp 2, Đổi Mật Khẩu Tài Khoản

IF và MAX / MIN:

Để search điểm tối đa và tốt nhất, chúng ta cũng có thể sử dụng những hàm MAX và MIN, tương ứng. Giả sử rằng cột F là cột tổng thể điểm, các công thức tiếp sau đâ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 muốn có cả kết quả Min với Max trong và một cột, bạn cũng 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 nhiều điều kiện

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

Ngoài ra, Excel cung ứng một số hàm IF quan trọng đặc biệt để đối chiếu và thống kê giám sát dữ liệu dựa trên các điều kiện khác nhau.

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

Bạn đang biết phương pháp để 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 cung ứng các hàm tựa như để khẳng định các cực hiếm văn phiên bản và số – ISTEXT với ISNUMBER.

Đây là ví dụ 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” nếu như B1 cất một quý giá số, cùng “blank” trường hợp 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 các điều kiện

 

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

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

Đôi khi, bạn cũng có thể đạt được tác dụng mong muốn bằng cách nhúng IF trong một số 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 demo logic.

Đây là một trong những cách không giống để bạn có thể sử dụng những hàm CONCATINATE với IF cùng nhau:

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

Tôi tin rằng bạn hầu như không cần ngẫu nhiên lời giải thích nào về bí quyết nữa, đặc biệt là nhìn vào hình dưới đây:

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

Hàm IFERROR và hàm IFNA

Cả nhị hàm – hàm IFEFFOR với IFNA – đa số được áp dụng trong Excel để bắt số đông lỗi trong số công thức. Với cả nhì hàm có thể trả về một giá chỉ trị quan trọng mà chúng ta chỉ định nếu một công thức tạo thành lỗi. Trường hợp không, công dụng của công thức sẽ tiến hành trả lại.

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

Cú pháp của các 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ố trang bị hai (value_if_error / value_if_na) là cực hiếm trả lại nếu như công thức reviews lỗi (bất kỳ lỗi như thế nào trong trường hợp hàm IFEFFOR, tốt lỗi # N / A vào trường vừa lòng của IFNA).

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

Ví dụ sau chứng tỏ việc sử dụng hàm IFERROR vô cùng đối chọi 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ư bạn thấy trong ảnh chụp màn hình ở trên, cột D hiển thị số phân chia của cột B mang lại cột C. Bạn có thể thấy hai thông tin lỗi trong những ô D2 và D5 bởi mọi người đều biết rằng chúng ta không thể chia một số cho 0.

Trong một số trong những trường hợp, tốt hơn chúng ta nên thực hiện hàm IF để ngăn ngừa một lỗi sau đó ISERROR hoặc ISNA nhằm bắt lỗi. Thứ nhất, đó là một cách cấp tốc hơn (về CPU) cùng thứ nhì nó là 1 cách thực hành lập trình tốt. Ví dụ, bí quyết IF sau đây tạo ra tác dụng tương tự 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, bao hàm trường hợp khi bạn không thể đánh giá trước toàn bộ các hàm tham số, nhất là các bí quyết rất phức tạp, và dự kiến được tất cả các lỗi hoàn toàn có thể xảy ra. Trong hầu như trường vừa lòng như vậy, các hàm ISERROR () với IFNA () thực sự bao gồm ích.

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