Để 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 reviews nhiều đk cùng một lúc, có nghĩa là bạn buộc phải xây dựng những phép test logic phức tạp hơn bằng phương pháp sử dụng những hàm IF trong một công thức. Các công thức ví dụ dưới đây sẽ cho chính mình thấy làm thay nào để tiến hành đúng. Bạn cũng trở thành học cách sử dụng IF trong những công thức mảng và tìm hiểu những điều cơ phiên bản của hàm IFEFFOR cùng IFNA. Tất cả sẽ có được trong nội dung bài viết của Học Excel Online


Làm nuốm nào để áp dụng hàm IF với khá nhiều điều kiện:

Nói bắt lại, có thể có 2 loại đk cơ bạn dạng – cùng với hàm xúc tích AND cùng 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: Hàm nếu hoặc trong excel

Hàm AND . Nếu phép demo logic của khách hàng 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 ko nó đã trả về FALSE.Hàm OR. Trong trường vừa lòng bạn thực hiện hàm OR vào phép thử 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, bọn họ hãy thuộc xem xét một vài ví dụ IF với tương đối nhiều điều kiện.

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

Giả sử bạn có một bảng với kết quả 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ố sản phẩm công nghệ hai, được liệt kê vào cột D, phải bởi hoặc hơn 30. Chỉ bao giờ 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 phù hợp là lưu lại điều khiếu nại đầu tiên, và tiếp đến kết hợp 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 Excel trả về “Pass” nếu như một quý hiếm cột C> = trăng tròn và giá trị trong cột D> = 30. Nếu không, bí quyết sẽ trả về “Fail”. Hình bên dưới đây minh chứng rằng hàm IF / và của họ là thiết 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ả những điều kiện trong hàm và và ngay cả khi một trong những điều kiện đã có được kiểm tra đánh giá là FALSE. Điều này là hơi phi lý vì trong hầu hết các ngữ điệu lập trình, các điều kiện tiếp theo không đề nghị kiểm tra nếu ngẫu nhiên kiểm tra trước đó đã trả về FALSE.

Trên thực tế, một cách làm IF / & đúng rất có thể bị lỗi do chức nă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 tách cho 0” (# DIV / 0!) nếu như ô 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. áp dụng hàm IF cùng với OR trong 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 hoàn toàn từ công thức IF / and được đàm luận ở 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 phong cách sau:

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

Cột E sẽ sở hữu được dấu “Pass” trường hợp điểm số đầu tiên bằng hoặc lớn hơn 20 hoặc điểm số đồ vật hai bởi hoặc lớn hơn 30.

Như các bạn thấy vào hình mặt dưới, những học viên của chúng ta có cơ hội tốt hơn để vượt qua kỳ thi cuối cùng với các điều kiện vì thế (bạn Scott thật rủi ro mắn khi chỉ thiếu hụt 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 trở nên giới hạn chỉ sử dụng hai hàm and / OR trong bí quyết IF của mình. Bạn rất có thể sử dụng tương đối 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 cùng 2007, công thức của bạn 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 quá 8192 ký tự.Trong Excel 2003 với các bản cũ hơn, chúng ta cũng có thể sử dụng buổi tối đa 30 đối số với tổng chiều lâu năm của cách làm của các bạn sẽ không vượt thừa 1.024 ký tự.

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

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

Trong bảng trên, trả sử chúng ta có các tiêu chuẩn sau nhằm đánh giá thành công của học sinh:

Điều kiện 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 1 trong những hai điều kiện trên được đáp ứng, tấn công giá cuối cùng vẫn là đạt, còn nếu 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ó chưa hẳn khó! Bạn chỉ cần thể hiện tại hai đk là câu lệnh and và đính kèm bọn chúng trong hàm OR vì các bạn không đề nghị cả hai đk được đáp ứng:

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

Cuối cùng, áp dụng hàm OR sống trên như là phép thử xúc tích trong hàm IF và cung cấp các thông số value_if_true cùng value_if_false. Kết trái là, các bạn sẽ nhận được công thức IF có tương đối 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 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 các phép thử xúc tích và ngắn gọn cho tài 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ới value_if_false của bí quyết 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 đặ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 những ví dụ điển hình: trả sử các bạn không chỉ việc đủ điều kiện hiệu quả của sinh viên như Pass / Fail, mà lại 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: từ 40 cho 60 (> 40 cùng Poor: 40 hoặc ít hơn (

Để bắt đầu, bạn cũng có thể thêm một cột bổ sung cập nhật (E) bởi công thức sau đây để cộng những 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 coi là một bài bác thực hành xuất sắc để bước đầu với điều kiện đặc trưng nhất và tạo cho cáchàm của công ty càng dễ dàng và đơn giản càng tốt. Công thức IF lồng nhau của chúng ta như sau:

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

Như chúng ta thấy, chỉ cần một hàm IF lồng nhau là đầy đủ trong trường hòa hợp này. Đương nhiên, chúng ta có thể tổ chức thêm hàm IF nếu 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 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 những điều kiện

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

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

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

Ví dụ, bí quyết SUM / IF mảng tiếp sau đây thể hiện phương pháp mà bạn cũng có thể tổng hợp các ô vào phạm vi được chỉ định dựa vào một điều kiện nào đó thay bởi cộng quý giá thực tế:

=SUM(IF(B1:B5

*
6 – hàm If nâng cao: hàm iferror, hàm if nhiều đ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 thiết yếu xác.

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

Trước đây trong hướng dẫn này, chúng ta đã luận bàn một vài ví dụ về công thức IF thể hiện cách áp dụng hàm IF với các hàm súc tích AND cùng OR. Bây giờ, bọn họ hãy xem gần như gì các hàm Excel khác hoàn toàn có thể được sử dụng với IF và ích lợi chúng đem về cho bạn.

Ví dụ 1. Thực hiện IF với hàm SUM, AVERAGE, MIN và 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ô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, họ đã thêm 1 cột new với bí quyết tính tổng điểm từ những cột C cùng D.

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

*
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 để gia công phép test 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 công dụng ” Good ” trường hợp điểm trung bình trong những cột C: F bằng hoặc lớn hơn 30, ” Satisfactory ” giả dụ điểm mức độ vừa phải từ 29 mang lại 25, cùng ” Poor ” nếu bên dưới 25.

Xem thêm: Thần Bài Macau 2 - Phim Thần Bài Macau Thuyết Minh

IF với MAX / MIN:

Để kiếm tìm điểm cao nhất và thấp nhất, bạn 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 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ả tác dụng Min cùng Max trong cùng một cột, chúng ta 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 phương pháp tương tự, bạn 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ụ, bạn có thể sử dụng nó với những hàm GetCellColor / GetCellFontColor để trả lại hiệu quả khác nhau dựa trên màu của một ô.

Ngoài ra, Excel hỗ trợ một số hàm IF đặc trưng để so với và 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 lộ diện của một quý hiếm văn phiên bản hoặc số dựa vào một đk duy duy nhất hoặc những điều kiện, chúng ta có thể sử dụng COUNTIF với COUNTIFS tương ứng. Để tìm thấy một tổng các giá trị dựa trên (các) đk được chỉ định, sử dụng những hàm SUMIF hoặc SUMIFS. Để tính vừa đủ theo các tiêu chuẩn nhất định, hãy áp dụng AVERAGEIF hoặc AVERAGEIFS.

Ví dụ 2. IF cùng với Hàm ISNUMBER với ISTEXT

Bạn đã biết phương pháp để phát hiện các ô trống cùng không trống bằng cách sử dụng 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à lấy 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 phiên bản nào, “number” ví như B1 cất một cực hiếm số, và “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 để ý rằng bí quyết trên hiển thị ” number ” cho những giá trị với ngày tháng. Điều này là vì Microsoft Excel giữ trữ các ngày theo phương thức số, bước đầ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ề vị IF trong một hàm Excel khác

Đôi khi, bạn có thể đạt được hiệu quả mong muốn bằng cách nhúng IF trong một số trong những hàm Excel khác, cầm cố vì áp dụng một hàm khác trong một phép thử logic.

Đây là 1 trong những cách khác để bạn cũng có thể sử dụng những hàm CONCATINATE cùng IF cùng nhau:

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

Tôi tin rằng bạn đa số không cần ngẫu nhiên lời lý giải 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 với hàm IFNA

Cả hai hàm – hàm IFEFFOR và IFNA – mọi được sử dụng trong Excel để bắt đều lỗi trong số công thức. Và cả nhị hàm rất có thể trả về một giá trị quan trọng đặc biệt mà chúng ta chỉ định nếu như một công thức tạo nên 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ự khác biệt 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 !, cùng #NULL !. Mặc dù hàmIFNA chỉ siêng về lỗi # N / A, đúng mực như tên của chính 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ố thứ nhất (giá trị) là đối số được bình chọn cho một lỗi.

Tham số sản phẩm hai (value_if_error / value_if_na) là quý hiếm trả lại nếu như công thức reviews lỗi (bất kỳ lỗi làm sao trong trường hòa hợp hàm IFEFFOR, tốt lỗi # N / A trong trường phù hợp của IFNA).

Chú ý. Nếu ngẫu nhiên đối số nào là một trong những ô rỗng, cả nhị hàm Error sẽ cách 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 solo giản:

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

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

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

Trong một trong những trường hợp, xuất sắc hơn bạn nên thực hiện hàm IF để ngăn ngừa một lỗi tiếp nối ISERROR hoặc ISNA nhằm bắt lỗi. Thứ nhất, đó là một cách nhanh hơn (về CPU) và thứ nhị nó là một cách thực hành thực tế lập trình tốt. Ví dụ, bí quyết IF tiếp sau đây tạo ra tác dụng tương từ bỏ như hàm IFERROR đã trình bày ở 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 bạn không thể bình chọn trước tất cả các hàm tham số, đặc biệt là các cách làm rất phức tạp, và dự đoán được tất cả các lỗi có thể xảy ra. Trong phần lớn trường phù hợp như vậy, các hàm ISERROR () và IFNA () thực sự tất cả ích.

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