Công thức IF AND trong Excel

Bài viết dưới đây Học Excel Online sẽ hướng dẫn bạn cách sử dụng hàm IF kết hợp với hàm AND trong Excel để kiểm tra nhiều điều kiện trong một công thức nhé!

Trên blog của mình đã có một số hướng dẫn về hàm IF và vẫn liên tục được cập nhật những cách dùng mới. Hôm nay, mình sẽ chỉ bạn cách làm thế nào có thể sử dụng hàm IF kết hợp với hàm AND để đánh giá hai hoặc nhiều điều kiện cùng một lúc.

Công thức IF AND trong Excel

Để xây dựng câu lệnh IF AND, đương nhiên là bạn cần kết hợp các hàm IF và AND trong một công thức. Bạn hãy thực hiện như sau

IF(AND(condition1, condition2,…), value_if_true, value_if_false)

Bạn hoàn toàn có thể hiểu đơn thuần như sau : NẾU điều kiện kèm theo 1 là đúng VÀ điều kiện kèm theo 2 là đúng, hãy làm một việc, nếu không hãy làm điều khác .

• Ví dụ : Hãy tạo một công thức kiểm tra xem B2 có được “ delivered ” ( phân phối ” hay không và C2 có chứa ngày tháng không :
• Nếu cả hai điều kiện kèm theo đều TRUE, hãy lưu lại đơn hàng là “ Closed ” .
• Nếu một trong hai điều kiện kèm theo là FALSE hoặc cả hai đều FALSE, thì trả về một chuỗi rỗng ( “ ” ) .

=IF(AND(B2=”delivered”, C2<>””), “Closed”, “”)

Ảnh chụp màn hình hiển thị bên dưới hiển thị hàm IF AND trong Excel :
Nếu bạn muốn trả về 1 số ít giá trị trong trường hợp FALSE, hãy phân phối giá trị đó trong đối số value_if_false. Ví dụ :

=IF(AND(B2=”delivered”, C2<>””), “Closed”, “Open”)

Công thức này xuất ra “ Closed ” nếu cột B là “ delivered ” và C có bất kể ngày nào trong đó ( không trống ). Trong tổng thể những trường hợp khác, nó trả về “ Open ” :

Khi sử dụng công thức IF AND trong Excel để đánh giá điều kiện, hãy lưu ý rằng chữ thường và chữ hoa được coi là cùng một ký tự nhé. Nếu bạn đang tìm c AND phân biệt chữ hoa chữ thường, hãy đưa một hoặc nhiều đối số của AND vào hàm EXACT đã được hướng dẫn ở blog trước đó!

Bây giờ bạn đã biết cú pháp của câu lệnh IF AND trong Excel, mình sẽ mách cho bạn những loại tác vụ nào mà nó hoàn toàn có thể xử lý .

Excel IF: lớn hơn VÀ nhỏ hơn

Trong ví dụ trước đó, tất cả chúng ta đang thử nghiệm hai điều kiện kèm theo trong hai ô khác nhau. Nhưng đôi lúc bạn hoàn toàn có thể cần chạy hai hoặc nhiều công thức ngay trên cùng một ô. Ví dụ nổi bật là kiểm tra xem giá trị ô có nằm giữa hai số hay không. Hàm IF AND trong Excel hoàn toàn có thể thuận tiện làm điều đó !
Giả sử bạn có một số ít doanh thu bán hàng trong cột B và bạn được nhu yếu gắn cờ số tiền lớn hơn USD 50 nhưng nhỏ hơn USD 100. Đơn giản thôi, bạn hãy chèn công thức này vào C2 và sau đó sao chép nó xuống cột :

=IF(AND(B2>50, B2<100), “x”, “”)

Nếu bạn cần gồm có những giá trị biên ( 50 và 100 ), hãy sử dụng toán tử nhỏ hơn hoặc bằng ( < = ) và lớn hơn hoặc bằng ( > = ) :

=IF(AND(B2>=50, B2<=100), “x”, “”)

Để giải quyết và xử lý một số ít giá trị ranh giới khác mà không biến hóa công thức, hãy nhập số tối thiểu và số tối đa vào hai ô riêng không liên quan gì đến nhau và tham chiếu đến những ô đó trong công thức của bạn. Để công thức hoạt động giải trí đúng mực trong toàn bộ những hàng, hãy bảo vệ sử dụng tham chiếu tuyệt đối cho những ô ranh giới ( USD F USD 1 và USD F USD 2 trong trường hợp này ) :

=IF(AND(B2>=$F$1, B2<=$F$2), “x”, “”)

Bằng cách sử dụng một công thức tương tự như, bạn cũng hoàn toàn có thể kiểm tra xem một ngày có nằm trong một khoanh vùng phạm vi được chỉ định hay không .
Ví dụ : hãy gắn cờ những ngày từ 10 tháng 9 năm 2018 đến 30 tháng 9 năm 2018. Một trở ngại nhỏ là ngày tháng không được phân phối trực tiếp cho những bài kiểm tra logic. Vì vậy để Excel hiểu ngày tháng, chúng phải được đặt trong hàm DATEVALUE, như sau :

=IF(AND(B2>=DATEVALUE(“9/10/2018”), B2<=DATEVALUE(“9/30/2018”)), “x”, “”)

Hoặc bạn chỉ cần nhập ngày Từ và Đến trong hai ô ( USD F USD 1 và USD F USD 2 trong ví dụ này ) và “ kéo ” chúng từ những ô đó bằng cách sử dụng công thức IF AND đã quen thuộc :

=IF(AND(B2>=$F$1, B2<=$F$2), “x”, “”)

NẾU cái này VÀ cái kia

Ngoài việc trả về các giá trị được xác định trước, hàm IF AND trong Excel cũng có thể thực hiện các phép tính khác nhau tùy thuộc vào việc các điều kiện được chỉ định là TRUE hay FALSE.

Một ví dụ đơn thuần, tất cả chúng ta sẽ giám sát khoản tiền thưởng 5 % cho doanh thu “ Closed ” với số tiền lớn hơn hoặc bằng 100 đô la .
Giả sử số tiền nằm trong cột B và trạng thái đơn hàng trong cột C, công thức sẽ như sau :

=IF(AND(B2>=100, C2=”closed”), B2*10%, 0)

Công thức trên chỉ định số 0 cho các đơn hàng còn lại (value_if_false=0). Nếu bạn sẵn sàng đưa ra một khoản tiền thưởng kích thích nhỏ, chẳng hạn như 3%, cho các đơn hàng không đáp ứng các điều kiện, hãy thêm vào trong đối số value_if_false:

=IF(AND(B2>=100, C2=”closed”), B2*10%, B2*3%)

Nhiều câu lệnh IF AND trong Excel

Như bạn hoàn toàn có thể nhận thấy những ví dụ trên đây tất cả chúng ta chỉ nhìn nhận hai điều kiện kèm theo. Vậy nếu nhiều hơn thì sao ? Chả sao cả, không có gì hoàn toàn có thể ngăn cản bạn xét một lúc nhiều điều kiện kèm theo trong công thức IF AND của mình miễn là chúng tuân thủ những số lượng giới hạn chung sau của Excel :

• Trong Excel 2019 – 2007, hoàn toàn có thể sử dụng tối đa 255 đối số trong một công thức, với tổng độ dài công thức không vượt quá 8.192 ký tự .
• Trong Excel 2003 trở xuống, không được phép có hơn 30 đối số, với tổng độ dài không vượt quá 1.024 ký tự .

Ví dụ về nhiều điều kiện AND, chúng ta hãy xem xét những điều kiện sau:

Số tiền ( B2 ) phải lớn hơn hoặc bằng 100 đô la
Trạng thái đơn hàng ( C2 ) là “ Closed ” ( đã đóng )
Ngày giao hàng ( D2 ) trong tháng hiện tại
Bây giờ, tất cả chúng ta cần câu lệnh IF AND để xác lập những lệnh mà cả 3 điều kiện kèm theo đều ĐÚNG. Công thức như sau :

=IF(AND(B2>=100, C2=”Closed”, MONTH(D2)=MONTH(TODAY())), “x”, “”)

Giả sử tháng hiện tại tại thời gian viết bài này là tháng 10, công thức sẽ cho tác dụng dưới đây :

Câu lệnh IF AND lồng nhau

Khi thao tác với những trang tính lớn, bạn hoàn toàn có thể cần kiểm tra một vài bộ tiêu chuẩn AND khác nhau cùng một lúc. Lúc này, bạn hoàn toàn có thể sử dụng công thức IF lồng nhau trong Excel cổ xưa và lan rộng ra những kiểm tra logic của nó bằng những câu lệnh AND, như sau :

IF(AND(…), output1, IF(AND(…), output2, IF(AND(…), output3, output4)))

Để tưởng tượng rõ hơn, tất cả chúng ta cùng xem ví dụ sau : Giả sử bạn muốn xếp hạng dịch vụ của mình dựa trên ngân sách luân chuyển và thời hạn giao hàng ước tính ( ETD ) :
Excellent : ngân sách luân chuyển dưới USD 20 và ETD dưới 3 ngày
Poor : ngân sách luân chuyển trên USD 30 và ETD trong 5 ngày
Average : bất kể thứ gì ở giữa
Bạn hãy viết hai câu lệnh IF AND riêng không liên quan gì đến nhau :

IF(AND(B2<20, C2<3), “Excellent”, …)

IF(AND(B2>30, C2>5), “Poor”, …)

… Và lồng cái này vào cái kia :

=IF(AND(B2>30, C2>5), “Poor”, IF(AND(B2<20, C2<3), “Excellent”, “Average”))

Kết quả sẽ giống như sau :

Hàm IF AND phân biệt chữ hoa chữ thường trong Excel

Như đã đề cập trong phần đầu, những công thức IF AND trong Excel không phân biệt giữa ký tự viết hoa và viết thường vì hàm AND về thực chất là không phân biệt chữ hoa chữ thường .
Nếu bạn đang thao tác với tài liệu phân biệt chữ hoa chữ thường và muốn nhìn nhận điều kiện kèm theo AND có tính đến trường hợp văn bản, hãy thực thi từng phép thử logic riêng không liên quan gì đến nhau bên trong hàm EXACT và lồng những hàm đó vào câu lệnh AND của bạn :

IF(AND(EXACT(cell,”condition1“), EXACT(cell,”condition2“)), value_if_true, value_if_false)

Đối với ví dụ này, chúng ta sẽ gắn cờ các đơn đặt hàng của một khách hàng cụ thể (ví dụ: công ty có tên là Cyberspace) với số tiền vượt quá một số nhất định, chẳng hạn như $100.

Như bạn hoàn toàn có thể thấy trong ảnh chụp màn hình hiển thị bên dưới, 1 số ít tên công ty trong cột B trông giống nhau trong phần trích dẫn những ký tự, tuy nhiên chúng là những công ty khác nhau, vì thế tất cả chúng ta phải kiểm tra tên đúng chuẩn .

=IF(AND(EXACT(B2, “Cyberspace”), C2>100), “x”, “”)

Để làm cho công thức linh động hơn, bạn hoàn toàn có thể nhập tên và số tiền của người mua tiềm năng vào hai ô riêng không liên quan gì đến nhau và tham chiếu đến những ô đó. Chỉ cần nhớ khóa những tham chiếu ô bằng dấu USD ( trong trường hợp này là USD G USD 1 và USD G USD 2 ) để chúng không biến hóa khi bạn sao chép công thức sang những hàng khác :

=IF(AND(EXACT(B2, $G$1), C2>$G$2), “x”, “”)

Bây giờ, bạn hoàn toàn có thể nhập bất kể tên và số tiền nào vào những ô được tham chiếu và công thức sẽ gắn cờ những đơn hàng tương ứng trong bảng của bạn :

IF OR AND công thức trong Excel

Trong công thức IF của Excel, bạn không bị số lượng giới hạn chỉ sử dụng một hàm logic. Để kiểm tra những tích hợp khác nhau của nhiều điều kiện kèm theo, bạn hoàn toàn có thể tự do tích hợp IF, AND, OR và những hàm khác để chạy những bài kiểm tra logic thiết yếu. Dưới đây là một ví dụ về công thức IF AND OR kiểm tra một vài điều kiện kèm theo OR trong AND. Và giờ đây, tôi sẽ chỉ cho bạn cách bạn hoàn toàn có thể thực thi hai hoặc nhiều bài kiểm tra AND trong hàm OR .
Giả sử, bạn muốn lưu lại đơn đặt hàng của hai người mua với số tiền lớn hơn 1 số ít nhất định, ví dụ điển hình như 100 đô la .
Trong ngôn từ Excel, những điều kiện kèm theo của chúng tôi được bộc lộ theo cách sau :

OR(AND(Customer1, Amount>100), AND(Customer2, Amount>100)

Giả sử tên người mua nằm trong cột B, số tiền trong cột C, 2 tên tiềm năng là G1 và G2 và số tiền tiềm năng ở G3, bạn sử dụng công thức này để ghi lại những đơn đặt hàng tương ứng bằng “ x ” :

=IF(OR(AND(B2=$G$1, C2>$G$3), AND(B2=$G$2, C2>$G$3)), “x”, “”)

Kết quả tựa như hoàn toàn có thể đạt được với cú pháp nhỏ gọn hơn :

=IF(AND(OR(B2=$G$1,B2= $G$2), C2>$G$3), “x”, “”)

Trên đây là cách bạn sử dụng hàm IF và AND cùng nhau trong Excel. Để hiểu thêm về những công thức, bạn hãy mở Excel ra và thực hành thực tế cùng mình nhé. Cảm ơn bạn đã đã sát cánh, liên tục theo dõi để update những kiến thức và kỹ năng mới nhất nhé !

Source: http://wp.ftn61.com
Category: Hỏi Đáp