Để phân tích dữ liệu một cách to gan lớn mật 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 đề xuất xây dựng các phép test 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. Những công thức ví dụ sau đây sẽ cho mình thấy làm nạm nào để triển khai đúng. Bạn cũng biến thành học cách thực hiện IF trong số công thức mảng và mày mò những điều cơ bạn dạng của hàm IFEFFOR và IFNA. Tất cả sẽ có được trong bài viết của Học Excel Online


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

Nói nắm lại, có thể có 2 loại đk cơ phiên bản – với hàm logic AND với OR. Do đó, hàm IF của chúng ta nên nhúng một hàm & và OR để triển khai phép thử xúc tích và ngắn gọn tương ứng.

Bạn đang xem: Hàm if nâng cao

Hàm AND . Nếu phép demo logic của khách hàng 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ợp bạn sử dụng hàm OR vào phép demo logic, Excel trả về TRUE nếu đáp ứng được bất kỳ điều kiện nào ; FALSE nếu như không.

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

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

Giả sử bạn có một bảng với công dụng của nhì điểm thi. Điểm số đầu tiên, được tàng trữ trong cột C, phải bằng hoặc to hơn 20. Điểm số vật dụng 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 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à lưu lại điều khiếu nại đầu tiên, và kế tiếp 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 thấy thêm Excel trả về “Pass” giả dụ một giá trị cột C> = 20 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 các đ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 các điều kiện đã có được kiểm tra đánh giá là FALSE. Điều này là hơi không bình thường vì trong hầu như các ngôn ngữ lập trình, những điều kiện tiếp sau không nên kiểm tra nếu bất kỳ kiểm tra trước đó đã trả về FALSE.

Trên thực tế, một bí quyết IF / & đúng hoàn toàn có thể bị lỗi do nhân kiệt này. Ví dụ, công thức =IF(AND(A20,(1/A2)>0.5),”Good”, “Bad”) sẽ trả lại “lỗi phân chia cho 0” (# DIV / 0!) trường hợp ô A2 bởi 0. Để tránh đ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 vào Excel

Bạn sử dụng phối kết hợp các hàm IF & OR theo một bí quyết tương tự. Sự biệt lập từ công thức IF / & được trao đổi ở trên là Excel trả về TRUE nếu đáp ứng được ít độc nhất vô nhị 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” ví như điểm số đầu tiên bằng hoặc to hơn 20 hoặc điểm số thiết bị hai bằng hoặc lớn hơn 30.

Như chúng ta thấy vào hình bên dưới, những học viên của họ có thời cơ tốt hơn nhằm vượt qua kỳ thi sau cuối với các điều kiện bởi vậy (bạn Scott thật không may mắn khi chỉ thiếu 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ỉ thực hiện hai hàm and / OR trong bí quyết IF của mình. Bạn có thể sử dụng không hề ít 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 người sử dụng không nhiều hơn thế nữa 255 đối số, với tổng chiều nhiều năm của phương pháp không vượt quá 8192 ký kết 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ố và tổng chiều nhiều năm của bí quyết của các bạn sẽ không vượt vượt 1.024 ký kết tự.

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

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

Trong bảng trên, đưa sử chúng ta có các tiêu chí sau nhằm đánh giá cả công của học tập 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à cột D> = 20

Nếu một trong những hai đk trên được đáp ứng, đánh giá cuối cùng vẫn là đạt, nếu không – ko đạti.

Công thức trông dường như khó khăn, nhưng duy nhất chốc, các bạn sẽ thấy rằng nó không phải khó! Bạn chỉ việc thể hiện tại hai điều kiện là câu lệnh and và đính thêm kèm bọn chúng trong hàm OR vì chúng ta không bắt buộc 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ư thể phép thử xúc tích trong hàm IF và hỗ trợ các tham số value_if_true cùng value_if_false. Kết trái là, các bạn sẽ nhận được phương pháp IF có tương đối nhiều điều kiện và / OR:

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

Hình tiếp sau đây chỉ ra rằng chúng ta có công thức đúng:

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

Sử dụng những 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 cho dữ liệu của mình, bạn có thể thêm những 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 call là các hàm IF lồng nhau và chúng quan trọng hữu ích nếu bạn muốn công thức của doanh nghiệp trả lại ba kết quả trở lên.

Đây là một trong ví dụ điển hình: mang sử bạn không chỉ cần đủ điều kiện hiệu quả của sinh viên như Pass / Fail, nhưng 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, 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 dưới đây để cộng các số trong cột C cùng D: =C2+D2

4 – hàm If nâng cao: hàm iferror, hàm if các đ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 như là một bài bác thực hành giỏi để ban đầu với điều kiện đặc biệt quan trọng nhất và làm 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 chúng ta 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à đầy đủ trong trường đúng theo này. Đương nhiên, chúng ta cũng có thể tổ chức thêm hàm IF 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 cập nhật thêm một điều kiện – tổng điểm từ bỏ 70 điểm trở lên được coi 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 giỏi 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 có thể được sử dụng trong các bí quyết mảng . Bạn hoàn toàn có thể cần một cách làm như vậy nếu bạn có nhu cầu đánh giá phần nhiều 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 tiếp sau đây thể hiện biện pháp mà bạn cũng có thể tổng hợp các ô trong phạm vi được chỉ định dựa trên một đk nào kia thay bởi vì cộng cực hiếm 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ì đó là một cách làm 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 chỉ dẫn này, bọn họ đã bàn bạc một vài ví dụ về cách làm IF miêu tả cách sử dụng hàm IF với những hàm súc tích AND với OR. Bây giờ, họ hãy xem các gì những 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 đến cho bạn.

Ví dụ 1. Thực hiện 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ách làm trả về những thứ hạng khác 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ư bạn thấy, bọn họ đã thêm một cột new với công thức tính tổng điểm từ các cột C cùng 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 mà lại không có thể chấp nhận được sửa đổi? Trong trường phù hợp này, thay vày thêm cột phụ, bạn cũng 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 nhiên 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 nhiều hơn? Tổng hợp không ít con số trực tiếp trong phương pháp IF sẽ tạo nên nó cực kỳ dài. Cách sửa chữa là nhúng hàm SUM trong phép thử sú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 các điều kiện

Tương trường đoản cú như vậy, bạn có thể sử dụng những hàm Excel khác để triển khai phép test logic trong các 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 “))

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

Xem thêm: (Mới Nhất 5/2022) Top 40 Phim Ma, Kinh Dị Hay Kinh Điển Mọi Thời Đại

IF với MAX / MIN:

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

Theo phương 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ụ, chúng ta có thể sử dụng nó với những hàm GetCellColor / GetCellFontColor nhằm trả lại tác dụng khác nhau dựa vào màu của một ô.

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

Ví dụ: nhằm đếm số lần mở ra của một giá trị văn bạn dạng hoặc số dựa trên một đk duy tốt nhất hoặc các điều kiện, bạn cũng có thể sử dụng COUNTIF cùng COUNTIFS tương ứng. Để tìm thấy một tổng các giá trị dựa trên (các) điều kiện đượ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 áp dụng AVERAGEIF hoặc AVERAGEIFS.

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

Bạn đã biết phương pháp để phát hiện các ô trống với không trống bằng cách áp dụng hàm ISBLANK. Microsoft Excel cung cấp các hàm giống như để xác định các cực hiếm văn bạn dạng và số – ISTEXT và ISNUMBER.

Đây là lấy ví dụ về hàm IF lồng nhau trả về “text” ví như ô B1 chứa ngẫu nhiên giá trị văn phiên bản nào, “number” giả dụ B1 đựng một cực hiếm số, cùng “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 các điều kiện

 

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

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

Đôi khi, chúng ta cũng có thể đạt được công dụng mong muốn bằng phương pháp nhúng IF trong một số hàm Excel khác, nắm vì thực hiện một hàm khác trong một phép demo logic.

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

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

Tôi tin tưởng rằng bạn phần nhiều không cần bất kỳ lời giải thích nào về phương pháp 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 các điều kiện

Hàm IFERROR với hàm IFNA

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

Sự khác hoàn toàn là hàm IFEFFOR xử lý toàn bộ các lỗi Excel, tất 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 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 bình chọn cho một lỗi.

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

Chú ý. Nếu bất kỳ đối số nào là 1 trong ô rỗng, cả nhị hàm Error sẽ giải pháp xử lý nó như một chuỗi trống rỗng (“”).

Ví dụ sau chứng minh 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 những điều kiện

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

Trong một số trong những trường hợp, giỏi hơn các bạn nên áp dụng hàm IF để ngăn ngừa 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) cùng thứ nhì nó là 1 trong những cách thực hành lập trình tốt. Ví dụ, công thức IF sau đây tạo ra kết quả tương tự như hàm IFERROR đã trình bày ở trên:

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

Nhưng tất nhiên, có những trường hợp khi bạn không thể kiểm tra trước toàn bộ các hàm tham số, đặc biệt là các công thức rất phức tạp, và dự kiến được tất cả các lỗi rất có thể xảy ra. Trong mọi trường hợp như vậy, những hàm ISERROR () cùng IFNA () thực sự bao gồm ích.

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