Hướng dẫn how do you use if and function in excel with multiple conditions? - làm thế nào để bạn sử dụng if và chức năng trong excel với nhiều điều kiện?

Hướng dẫn chỉ ra cách tạo nhiều câu lệnh IF trong Excel với và cũng như hoặc logic. Ngoài ra, bạn sẽ học cách sử dụng nếu cùng với các hàm Excel khác.

Trong phần đầu tiên của Excel nếu hướng dẫn của chúng tôi, chúng tôi đã xem xét cách xây dựng một câu lệnh IF đơn giản với một điều kiện cho văn bản, số, ngày, khoảng trống và không có chỗ trống. Tuy nhiên, để phân tích dữ liệu mạnh mẽ, bạn có thể thường cần đánh giá nhiều điều kiện cùng một lúc. Các ví dụ công thức dưới đây sẽ chỉ cho bạn những cách hiệu quả nhất để làm điều này.

Cách sử dụng nếu chức năng có nhiều điều kiện

Về bản chất, có hai loại công thức IF có nhiều tiêu chí dựa trên và / hoặc logic. Do đó, trong bài kiểm tra logic của công thức IF của bạn, bạn nên sử dụng một trong những chức năng này:IF formula with multiple criteria based on the AND / OR logic. Consequently, in the logical test of your IF formula, you should use one of these functions:

  • Và chức năng - trả về đúng nếu tất cả các điều kiện được đáp ứng; Sai nếu không.all the conditions are met; FALSE otherwise.
  • Hoặc chức năng - trả về true nếu có bất kỳ điều kiện nào được đáp ứng; Sai nếu không.any single condition is met; FALSE otherwise.

Để minh họa tốt hơn điểm, hãy điều tra một số ví dụ công thức thực tế.

Excel if statement có nhiều điều kiện (và logic)

Công thức chung của Excel nếu có hai điều kiện trở lên là:

If (và (điều kiện1, điều kiện2, xông), value_if_true, value_if_false)

Được dịch sang ngôn ngữ của con người, công thức nói: Nếu điều kiện 1 là đúng và điều kiện 2 là đúng, hãy trả về value_if_true; khác trả về value_if_false.

Giả sử bạn có một bảng liệt kê điểm số của hai bài kiểm tra trong các cột B và C. Để vượt qua bài kiểm tra cuối cùng, một học sinh phải có cả hai điểm lớn hơn 50.

Đối với bài kiểm tra logic, bạn sử dụng các thông tin sau và câu lệnh: và (b2> 50, c2> 50)

Nếu cả hai điều kiện là đúng, công thức sẽ trả về "vượt qua"; Nếu bất kỳ điều kiện là sai - "thất bại".

=IF(AND(B2>50, B2>50), "Pass", "Fail")

Dễ dàng, phải không? Ảnh chụp màn hình bên dưới chứng minh rằng excel if /và công thức của chúng tôi hoạt động đúng:

Hướng dẫn how do you use if and function in excel with multiple conditions? - làm thế nào để bạn sử dụng if và chức năng trong excel với nhiều điều kiện?

Theo cách tương tự, bạn có thể sử dụng Excel nếu chức năng có nhiều điều kiện văn bản.text conditions.

Chẳng hạn, để xuất ra "tốt" nếu cả B2 và C2 lớn hơn 50, "xấu" nếu không, công thức là:

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad")

Hướng dẫn how do you use if and function in excel with multiple conditions? - làm thế nào để bạn sử dụng if và chức năng trong excel với nhiều điều kiện?

Lưu ý quan trọng! Chức năng và chức năng kiểm tra tất cả các điều kiện, ngay cả khi (các) (các) đã được đánh giá là sai. Hành vi như vậy là một chút 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 kiểm tra nếu bất kỳ bài kiểm tra nào trước đó đã trả về sai. The AND function checks all the conditions, even if the already tested one(s) evaluated to FALSE. Such behavior is a bit unusual since in most of programming languages, subsequent conditions are not tested if any of the previous tests has returned FALSE.

Trong thực tế, một câu lệnh dường như chính xác có thể dẫn đến lỗi vì tính đặc hiệu này. Ví dụ: công thức dưới đây sẽ trả về #Div/0! ("chia cho lỗi 0") nếu ô A2 bằng 0:

=IF(AND(A2<>0, (1/A2)>0.5),"Good", "Bad")

Việc tránh điều này, bạn nên sử dụng chức năng nếu chức năng:

=IF(A2<>0, IF((1/A2)>0.5, "Good", "Bad"), "Bad")

Để biết thêm thông tin, xin vui lòng xem và công thức trong Excel.

Excel nếu hàm có nhiều điều kiện (hoặc logic)

Để làm một việc nếu có bất kỳ điều kiện nào được đáp ứng, nếu không hãy làm điều gì khác, sử dụng sự kết hợp của IF và hoặc các chức năng này:any condition is met, otherwise do something else, use this combination of the IF and OR functions:

If (hoặc (điều kiện1, điều kiện2, xông), value_if_true, value_if_false)

Sự khác biệt so với IF / và công thức được thảo luận ở trên là Excel trả về đúng nếu bất kỳ điều kiện được chỉ định nào là đúng.

Vì vậy, nếu trong công thức trước đó, chúng tôi sử dụng hoặc thay vì và:

=IF(OR(B2>50, B2>50), "Pass", "Fail")

Sau đó, bất cứ ai có hơn 50 điểm trong cả hai bài kiểm tra sẽ nhận được "vượt qua" trong cột D. Với các điều kiện như vậy, học sinh của chúng tôi có cơ hội tốt hơn để vượt qua kỳ thi cuối cùng (Yvette đặc biệt không may mắn thất bại chỉ 1 điểm :)

Hướng dẫn how do you use if and function in excel with multiple conditions? - làm thế nào để bạn sử dụng if và chức năng trong excel với nhiều điều kiện?

Mẹo. Trong trường hợp bạn đang tạo một câu lệnh nhiều IF với văn bản và kiểm tra giá trị trong một ô có hoặc logic (nghĩa là một ô có thể là "cái này" hoặc "đó"), thì bạn có thể xây dựng một công thức nhỏ gọn hơn bằng cách sử dụng hằng số mảng. In case you are creating a multiple IF statement with text and testing a value in one cell with the OR logic (i.e. a cell can be "this" or "that"), then you can build a more compact formula using an array constant.

Ví dụ: để đánh dấu việc bán là "đóng" nếu ô B2 là "được giao" hoặc "trả tiền", công thức là:

=IF(OR(B2={"delivered", "paid"}), "Closed", "")

Nhiều ví dụ công thức có thể được tìm thấy trong excel nếu hoặc chức năng.

Nếu có nhiều và & hoặc câu lệnh

Nếu nhiệm vụ của bạn yêu cầu đánh giá một số bộ nhiều điều kiện, bạn sẽ phải sử dụng cả hai và hoặc hoặc các chức năng tại một thời điểm.

Trong bảng mẫu của chúng tôi, giả sử bạn có các tiêu chí sau để kiểm tra kết quả thi:

  • Điều kiện 1: Bài kiểm tra1> 50 và Bài kiểm tra2> 50
  • Điều kiện 2: Bài kiểm tra1> 40 và Bài kiểm tra2> 60

Nếu một trong hai điều kiện được đáp ứng, kỳ thi cuối cùng được coi là thông qua.

Thoạt nhìn, công thức có vẻ hơi khó khăn, nhưng thực tế nó không phải là! Bạn chỉ thể hiện từng điều kiện trên dưới dạng và tuyên bố và làm tổ trong chức năng OR (vì không cần thiết phải đáp ứng cả hai điều kiện, sẽ đủ):

OR(AND(B2>50, C2>50), AND(B2>40, C2>60)

Sau đó, sử dụng chức năng OR cho thử nghiệm logic của IF và cung cấp giá trị mong muốn_IF_TRUE và value_if_false. Kết quả là, bạn sẽ nhận được những điều sau đây nếu công thức có nhiều và / hoặc điều kiện:

=IF(OR(AND(B2>50, C2>50), AND(B2>40, C2>60), "Pass", "Fail")

Ảnh chụp màn hình bên dưới cho thấy rằng chúng tôi đã thực hiện đúng công thức:

Hướng dẫn how do you use if and function in excel with multiple conditions? - làm thế nào để bạn sử dụng if và chức năng trong excel với nhiều điều kiện?

Đương nhiên, bạn không giới hạn chỉ sử dụng hai và/hoặc các chức năng trong các công thức IF của bạn. Bạn có thể sử dụng nhiều trong số chúng như logic kinh doanh của bạn yêu cầu, với điều kiện là:

  • Trong Excel 2007 trở lên, bạn không có quá 255 đối số và tổng độ dài của công thức IF không vượt quá 8.192 ký tự.
  • Trong Excel 2003 trở xuống, không có quá 30 đối số và tổng độ dài của công thức IF của bạn không vượt quá 1.024 ký tự.

Nested If Statement để kiểm tra nhiều bài kiểm tra logic

Nếu bạn muốn đánh giá nhiều bài kiểm tra logic trong một công thức duy nhất, thì bạn có thể làm tổ một số chức năng sang một công thức khác. Các chức năng như vậy được gọi là lồng nhau nếu các chức năng. Chúng chứng minh đặc biệt hữu ích khi bạn muốn trả về các giá trị khác nhau tùy thuộc vào kết quả của các thử nghiệm logic.nested IF functions. They prove particularly useful when you wish to return different values depending on the logical tests' results.

Đây là một ví dụ điển hình: Giả sử bạn muốn đủ điều kiện thành tích của học sinh là "tốt", "thỏa đáng" và "nghèo" dựa trên các điểm số sau:

  • Tốt: 60 trở lên (> = 60)
  • Hợp nhất: từ 40 đến 60 (> 40 đến
  • Nghèo: 40 hoặc ít hơn (

Trước khi viết một công thức, hãy xem xét thứ tự các chức năng bạn sẽ làm tổ. Excel sẽ đánh giá các thử nghiệm logic theo thứ tự chúng xuất hiện trong công thức. Khi một điều kiện đánh giá là TRUE, các điều kiện tiếp theo không được kiểm tra, có nghĩa là công thức dừng sau kết quả thực sự đầu tiên.

Trong trường hợp của chúng tôi, các chức năng được sắp xếp từ lớn nhất đến nhỏ nhất:

=IF(B2>=60, "Good", IF(B2>40, "Satisfactory", "Poor"))

Đương nhiên, bạn có thể làm tổ nhiều chức năng hơn nếu cần (lên đến 64 trong các phiên bản hiện đại).

Hướng dẫn how do you use if and function in excel with multiple conditions? - làm thế nào để bạn sử dụng if và chức năng trong excel với nhiều điều kiện?

Để biết thêm thông tin, vui lòng xem cách sử dụng nhiều câu lệnh nếu có trong Excel.

Excel nếu công thức mảng có nhiều điều kiện

Một cách khác để có được Excel nếu kiểm tra nhiều điều kiện là sử dụng công thức mảng.

Để đánh giá các điều kiện với và logic, hãy sử dụng dấu hoa thị:

If (điều kiện1) * (điều kiện2) *,, value_if_true, value_if_false)

Để kiểm tra các điều kiện với hoặc logic, hãy sử dụng dấu cộng:

If (điều kiện1) + (điều kiện2) +, value_if_true, value_if_false)

Để hoàn thành công thức mảng một cách chính xác, nhấn Ctrl + Shift + Enter Keys với nhau. Trong Excel 365 và Excel 2021, điều này cũng hoạt động như một công thức thông thường do hỗ trợ cho các mảng động.Ctrl + Shift + Enter keys together. In Excel 365 and Excel 2021, this also works as a regular formula due to support for dynamic arrays.

Ví dụ: để nhận "Pass" nếu cả B2 và C2 lớn hơn 50, công thức là:

=IF((B2>50) * (C2>50), "Pass", "Fail")

Hướng dẫn how do you use if and function in excel with multiple conditions? - làm thế nào để bạn sử dụng if và chức năng trong excel với nhiều điều kiện?

Trong Excel 365 của tôi, một công thức bình thường hoạt động tốt (như bạn có thể thấy trong ảnh chụp màn hình ở trên). Trong Excel 2019 trở xuống, hãy nhớ làm cho nó thành công thức mảng bằng cách sử dụng phím tắt Ctrl + Shift + Enter.Ctrl + Shift + Enter shortcut.

Để đánh giá nhiều điều kiện với hoặc logic, công thức là:

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad")0

Hướng dẫn how do you use if and function in excel with multiple conditions? - làm thế nào để bạn sử dụng if và chức năng trong excel với nhiều điều kiện?

Sử dụng nếu cùng với các chức năng khác

Phần này giải thích cách sử dụng nếu kết hợp với các chức năng Excel khác và những lợi ích này mang lại cho bạn.

Ví dụ 1. Nếu lỗi #N/A trong VLookup

Khi Vlookup hoặc chức năng tra cứu khác không thể tìm thấy thứ gì đó, nó sẽ trả về lỗi #N/A. Để làm cho các bảng của bạn trông đẹp hơn, bạn có thể trả về 0, trống hoặc văn bản cụ thể nếu #n/a. Đối với điều này, hãy sử dụng công thức chung này:

If (isna (vlookup (xông)), value_if_na, vlookup (Mạnh)))

Ví dụ:

Nếu #N/A trở lại 0:

Nếu giá trị tra cứu trong E1 không được tìm thấy, công thức sẽ trả về 0.

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad")1

Nếu #N/A trở lại trống:

Nếu giá trị tra cứu không được tìm thấy, công thức không trả về không có gì (một chuỗi trống).

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad")2

Nếu #n/a trả về văn bản nhất định:

Nếu không tìm thấy giá trị tra cứu, công thức trả về văn bản cụ thể.

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad")3

Hướng dẫn how do you use if and function in excel with multiple conditions? - làm thế nào để bạn sử dụng if và chức năng trong excel với nhiều điều kiện?

Để biết thêm các ví dụ về công thức, vui lòng xem Vlookup với câu lệnh IF trong Excel.

Ví dụ 2. Nếu với các hàm tổng, trung bình, tối thiểu và tối đa

Để tổng hợp các giá trị ô dựa trên các tiêu chí nhất định, Excel cung cấp các hàm SUMIF và SUMIFS.

Trong một số tình huống, logic kinh doanh của bạn có thể yêu cầu bao gồm hàm tổng trong bài kiểm tra logic của IF. Ví dụ: để trả về các nhãn văn bản khác nhau tùy thuộc vào tổng các giá trị trong B2 và C2, công thức là:

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad")4

Nếu tổng lớn hơn 130, kết quả là "tốt"; Nếu lớn hơn 110 - "thỏa đáng ', nếu 110 hoặc thấp hơn -" nghèo ".

Hướng dẫn how do you use if and function in excel with multiple conditions? - làm thế nào để bạn sử dụng if và chức năng trong excel với nhiều điều kiện?

Theo cách tương tự, bạn có thể nhúng hàm trung bình trong bài kiểm tra logic IF và trả về các nhãn khác nhau dựa trên điểm trung bình:

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad")5

Giả sử tổng số điểm nằm trong cột D, bạn có thể xác định các giá trị cao nhất và thấp nhất với sự trợ giúp của các hàm tối đa và tối thiểu:

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad")6

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad")6

Để có cả hai nhãn trong một cột, hãy làm tổ các hàm trên sang các chức năng khác:

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad")8

Hướng dẫn how do you use if and function in excel with multiple conditions? - làm thế nào để bạn sử dụng if và chức năng trong excel với nhiều điều kiện?

Tương tự như vậy, bạn có thể sử dụng nếu cùng với các chức năng tùy chỉnh của bạn. Ví dụ: bạn có thể kết hợp nó với GetCellColor hoặc GetCellFontColor để trả về các kết quả khác nhau dựa trên màu tế bào.

Ngoài ra, Excel cung cấp một số chức năng để tính toán dữ liệu dựa trên các điều kiện. Đối với các ví dụ công thức chi tiết, vui lòng kiểm tra các hướng dẫn sau:

  • Countif - đếm các ô đáp ứng một điều kiện
  • Countifs - Đếm các ô có nhiều tiêu chí
  • SUMIF - Các ô tổng hợp có điều kiện
  • SUMIFS - Các ô tổng với nhiều tiêu chí

Ví dụ 3. Nếu với isnumber, istext và isblank

Để xác định văn bản, số và ô trống, Microsoft Excel cung cấp các chức năng đặc biệt như Istext, ISNumber và ISBlank. Bằng cách đặt chúng trong các bài kiểm tra logic của ba câu lệnh nếu được lồng, bạn có thể xác định tất cả các loại dữ liệu khác nhau trong một lần:

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad")9

Hướng dẫn how do you use if and function in excel with multiple conditions? - làm thế nào để bạn sử dụng if và chức năng trong excel với nhiều điều kiện?

Ví dụ 4. Nếu và Concatenate

Để xuất kết quả của IF và một số văn bản vào một ô, hãy sử dụng Concatenate hoặc Concat (trong Excel 2016 - 365) và nếu các chức năng cùng nhau. Ví dụ:

=IF(AND(A2<>0, (1/A2)>0.5),"Good", "Bad")0

=IF(AND(A2<>0, (1/A2)>0.5),"Good", "Bad")1

Nhìn vào ảnh chụp màn hình bên dưới, bạn sẽ không cần bất kỳ lời giải thích nào về công thức làm gì:

Hướng dẫn how do you use if and function in excel with multiple conditions? - làm thế nào để bạn sử dụng if và chức năng trong excel với nhiều điều kiện?

Nếu công thức iserror / isna trong excel

Các phiên bản hiện đại của Excel có các chức năng đặc biệt để bẫy các lỗi và thay thế chúng bằng một tính toán hoặc giá trị được xác định trước khác - iferror (trong Excel 2007 trở lên) và IFNA (trong Excel 2013 trở lên). Trong các phiên bản Excel trước đó, bạn có thể sử dụng IFERROR và nếu kết hợp ISNA thay thế.

Sự khác biệt là iferror và Iserror xử lý tất cả các lỗi Excel có thể, bao gồm #Value !, #N/A, #Name ?, #Ref !, #Num !, #Div/0 !, Và #Null !. Trong khi IFNA và ISNA chỉ chuyên về lỗi #N/A.

Ví dụ: để thay thế lỗi "chia cho 0" (#Div/0!) Bằng văn bản tùy chỉnh của bạn, bạn có thể sử dụng công thức sau:

=IF(AND(A2<>0, (1/A2)>0.5),"Good", "Bad")2

Hướng dẫn how do you use if and function in excel with multiple conditions? - làm thế nào để bạn sử dụng if và chức năng trong excel với nhiều điều kiện?

Và đó là tất cả những gì tôi phải nói về việc sử dụng chức năng IF trong Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

Thực hành sổ làm việc để tải xuống

Excel nếu nhiều tiêu chí - ví dụ (tệp .xlsx)

Bạn cũng có thể quan tâm

Chúng ta có thể nhập nhiều nếu điều kiện trong và nếu công thức không?

Một công thức thường xuyên kiểm tra một điều kiện duy nhất rất đơn giản và dễ viết. Nhưng nếu dữ liệu của bạn yêu cầu các bài kiểm tra logic phức tạp hơn với nhiều điều kiện? Trong trường hợp này, bạn có thể bao gồm một số chức năng IF trong một công thức và nhiều câu lệnh này được gọi là excel lồng nhau nếu.you can include several IF functions in one formula, and these multiple If statements are called Excel Nested IF.

Bạn có thể kết hợp IF và và chức năng trong Excel không?

Trong excel nếu các công thức, bạn không giới hạn chỉ sử dụng một hàm logic. Để kiểm tra các kết hợp khác nhau của nhiều điều kiện, bạn có thể tự do kết hợp IF và, hoặc và các chức năng khác để chạy các thử nghiệm logic cần thiết. Dưới đây là một ví dụ về IF và hoặc công thức kiểm tra một vài hoặc điều kiện trong và.you are free to combine the IF, AND, OR and other functions to run the required logical tests. Here is an example of IF AND OR formula that tests a couple of OR conditions within AND.

Nếu tuyên bố có 2 điều kiện trong Excel?

Hàm IF cho phép bạn thực hiện so sánh hợp lý giữa một giá trị và những gì bạn mong đợi bằng cách kiểm tra điều kiện và trả về kết quả nếu đúng hoặc sai.Vì vậy, một tuyên bố nếu có thể có hai kết quả.Kết quả đầu tiên là nếu so sánh của bạn là đúng, thứ hai nếu so sánh của bạn là sai.an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

Bạn có thể làm tổ và hoặc hoặc các chức năng trong chức năng IFS không?

Bạn có thể tự động hóa việc đánh giá các thử nghiệm logic bằng cách làm tổ và, hoặc, không phải là các chức năng bên trong một hàm nếu một chức năng.Điều này có nghĩa là nếu chúng ta có nhiều điều kiện nhưng chúng ta muốn trả về một đầu ra duy nhất, chúng ta có thể tổ chức bất kỳ chức năng kết hợp nào bên trong IF và chỉ định đầu ra phù hợp.. This means that if we have multiple conditions but we want to return a single output, we can nest any of the conjunction functions inside an IF and specify outputs accordingly.