Microsoft Excel (Bài 77): Hàm IF, lồng ghép hàm IF với nhiều điều kiện IFERROR, IFNA và nhiều hơn nữa

Để phân tích dữ liệu một cách mạnh 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 phải xây dựng các phép thử logic phức tạp hơn bằng cách sử dụng nhiều hàm IF trong một công thức. Các công thức ví dụ dưới đây sẽ cho bạn thấy làm thế nào để thực hiện đúng. Bạn cũng sẽ học cách sử dụng IF trong các công thức mảng và tìm hiểu những điều cơ bản của hàm IFEFFOR và IFNA.

LÀM THẾ NÀO ĐỂ SỬ DỤNG HÀM IF VỚI NHIỀU ĐIỀU KIỆN:

Nói tóm lại, có thể có 2 loại điều kiện cơ bản – với hàm logic AND và OR. Do đó, hàm IF của bạn nên nhúng một hàm AND và OR để làm phép thử logic tương ứng.

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

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

VÍ DỤ 1. SỬ DỤNG IF & AND TRONG EXCEL

Giả sử bạn có một bảng với kết quả của hai đ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ố thứ hai, được liệt kê trong cột D, phải bằng hoặc hơn 30. Chỉ khi 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 dễ nhất để tạo ra một công thức thích hợp là ghi lại điều kiện đầu tiên, và sau đó kết 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 phải không? Công thức cho biết Excel trả về “Pass” nếu một giá trị cột C> = 20 và giá trị trong cột D> = 30. Nếu không, công thức sẽ trả về “Fail”. Hình dưới đây chứng minh rằng hàm IF / AND của chúng ta là chính xác:

Chú ý. Microsoft Excel kiểm tra tất cả các điều kiện trong hàm AND và ngay 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 hết các ngôn ngữ lập trình, các điều kiện tiếp theo không cần kiểm tra nếu bất kỳ kiểm tra trước đó đã trả về FALSE.

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

=IF(A2<>0, 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 kết hợp các hàm IF & OR theo một cách tương tự. Sự khác biệt từ công thức IF / AND được thảo luận ở trên là Excel trả về TRUE nếu đáp ứng được ít nhất một trong các điều kiện được chỉ định.

Vì vậy, nếu chúng ta 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” nếu điểm số đầu tiên bằng hoặc lớn hơn 20 hoặc điểm số thứ hai bằng hoặc lớn hơn 30.

Như bạn thấy trong hình bên dưới, những học sinh 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 như vậy (bạn Scott thật không may mắn khi chỉ thiếu 1 điểm.)

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

• Trong Excel 2013, 2010 và 2007, công thức của bạn không nhiều hơn 255 đối số, và tổng chiều dài của công thức không vượt quá 8192 ký tự.
• Trong Excel 2003 và các bản cũ hơn, bạn có thể sử dụng tối đa 30 đối số và tổng chiều dài của công thức của bạn sẽ không vượt quá 1.024 ký tự.

VÍ DỤ 3. SỬ DỤNG IF VỚI CÁC HÀM AND & OR

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

Trong bảng trên, giả sử bạn có các tiêu chí sau để đánh giá 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 và cột D> = 20

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

Công thức trông có vẻ khó khăn, nhưng chỉ một chốc, bạn sẽ thấy rằng nó không phải khó! Bạn chỉ cần thể hiện hai điều kiện là câu lệnh AND và đính kèm 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, sử dụng hàm OR ở trên như là phép thử logic trong hàm IF và cung cấp các tham số value_if_true và value_if_false. Kết quả là, bạn sẽ nhận được công thức IF có nhiều điều kiện AND / OR:

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

Hình dưới đây chỉ ra rằng chúng ta có công thức đúng:

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 các phép thử logic 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 công thức 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 bạn trả lại ba kết quả trở lên.

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

• Good: 60 trở lên (> = 60)
• Satisfactory: từ 40 đến 60 (> 40 và <60)
• Poor: 40 hoặc ít hơn (<= 40)

Để bắt đầu, bạn có thể thêm một cột bổ sung (E) bằng công thức sau đây để cộng các số trong cột C và D: =C2+D2

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

SỬ DỤNG EXCEL IF TRONG CÁC CÔNG THỨC MẢNG

Giống như các hàm Excel khác, IF có thể được sử dụng trong các công thức mảng . Bạn có thể cần một công thức như vậy nếu bạn muốn đánh giá mọi thành phần của mảng khi thực hiện lệnh IF.

Ví dụ, công thức SUM / IF mảng dưới đây thể hiện cách mà bạn có thể tổng hợp các ô trong phạm vi được chỉ định dựa trên một điều kiện nào đó thay vì cộng giá trị thực tế:

=SUM(IF(B1:B5<=1,1,2))

Công thức gán một số “điểm” nhất định cho mỗi giá trị trong cột B – nếu một giá trị bằng hoặc nhỏ hơn 1, nó tương đương với 1 điểm; Và 2 điểm được gán cho mỗi giá trị lớn hơn 1. Và sau đó,hàm SUM thêm các kết quả của 1 và 2, như thể hiện trong hình dưới đây.

Chú ý. Vì đây là một công thức mảng, hãy nhớ nhấn Ctrl + Shift + Enter để nhập chính 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 đã thảo luận một vài ví dụ về công thức IF thể hiện cách sử dụng hàm IF với các hàm logic AND và OR. Bây giờ, chúng ta hãy xem những gì các hàm Excel khác có thể được sử dụng với IF và lợi ích chúng mang lại cho bạn.

VÍ DỤ 1. SỬ DỤNG IF VỚI HÀM SUM, AVERAGE, MIN VÀ MAX

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

Nhưng điều gì sẽ xảy ra nếu bảng của bạn có một cấu trúc được xác định trước mà không cho phép sửa đổi? Trong trường 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 công thức IF của bạn, như sau:

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

Được rồi, nhưng nếu bảng của bạn chứa nhiều cột điểm, chẳng hạn tới 5 cột khác nhau hoặc nhiều hơn? Tổng hợp rất nhiều con số trực tiếp trong công thức IF sẽ làm cho nó rất dài. Cách thay thế là nhúng hàm SUM trong phép thử logic của IF, như sau:

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

Tương tự như vậy, bạn có thể sử dụng các hàm Excel khác để làm phép thử 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 kết quả ” Good ” nếu điểm trung bình trong các cột C: F bằng hoặc lớn hơn 30, ” Satisfactory ” nếu điểm trung bình từ 29 đến 25, và ” Poor ” nếu dưới 25.

IF và MAX / MIN:

Để tìm điểm cao nhất và thấp 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 tổng số đ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 muốn có cả kết quả Min và Max trong cùng 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”, “”))

Theo cách tương tự, bạn 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 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à tính toán dữ liệu dựa trên các điều kiện khác nhau.

Ví dụ: để đếm số lần xuất hiện của một giá trị văn bản hoặc số dựa trên một điều kiện duy nhất hoặc nhiều điều kiện, bạn có thể sử dụng COUNTIF và COUNTIFS tương ứng. Để tìm ra một tổng các giá trị dựa trên (các) điều kiện được chỉ định, sử dụng các 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 VỚI HÀM ISNUMBER VÀ ISTEXT

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

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

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

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

VÍ DỤ 3. SỬ DỤNG KẾT QUẢ TRẢ VỀ BỞI 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 số hàm Excel khác, thay vì sử dụng một hàm khác trong một phép thử logic.

Đây là một cách khác để bạn có thể sử dụng các hàm CONCATINATE và 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 bất kỳ lời giải thích nào về công thức nữa, đặc biệt là nhìn vào hình dưới đây:

HÀM IFERROR VÀ HÀM IFNA

Cả hai hàm – IFERROR và IFNA – đều được sử dụng trong Excel để bắt những lỗi trong các công thức. Và cả hai hàm có thể trả về một giá trị đặc biệt mà bạn chỉ định nếu một công thức tạo ra lỗi. Nếu không, kết quả của công thức sẽ được trả lại.

Sự khác biệt là IFERROR xử lý tất cả các lỗi Excel, kể cả #VALUE !, # N / A, #NAME ?, #REF !, #NUM !, # DIV / 0 !, và #NULL !. Mặc dù hàml IFNA chỉ chuyên về lỗi # N / A, chính xác 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 tra cho một lỗi.

Tham số thứ hai (value_if_error / value_if_na) là giá trị trả lại nếu công thức đánh giá lỗi (bất kỳ lỗi nào trong trường hợp IFERROR, hay lỗi # N / A trong trường hợp của IFNA).

Chú ý. Nếu bất kỳ đối số nào là một ô rỗng, cả hai hàm Error sẽ xử lý 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 đơn giản:

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

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

Trong một số trường hợp, tốt hơn bạn nên sử dụng hàm IF để ngăn chặn một lỗi sau đó ISERROR hoặc ISNA để bắt lỗi. Thứ nhất, đó là một cách nhanh hơn (về CPU) và thứ hai nó là một 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 tất cả các hàm tham số, đặc biệt là các công thức rất phức tạp, và dự đoán được tất cả các lỗi có thể xảy ra. Trong những trường hợp như vậy, các hàm ISERROR () và IFNA () thực sự có ích.