Công Thức IF AND Trong Excel - Học Excel Online Miễn Phí

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 có thể hiểu đơn giản như sau: NẾU điều kiện 1 là đúng VÀ điều kiện 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 đều TRUE, hãy đánh dấu đơn hàng là “Closed”.
• Nếu một trong hai điều kiện 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 bên dưới hiển thị hàm IF AND trong Excel:

Nếu bạn muốn trả về một số giá trị trong trường hợp FALSE, hãy cung cấp 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ất cả các 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ó có thể giải quyết.

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

Trong ví dụ trước đó, chúng ta đang thử nghiệm hai điều kiện trong hai ô khác nhau. Nhưng đôi khi bạ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ụ điển hình là kiểm tra xem giá trị ô có nằm giữa hai số hay không. Hàm IF AND trong Excel  có thể dễ dàng làm điều đó!

Giả sử bạn có một số doanh số bán hàng trong cột B và bạn được yêu cầu gắn cờ số tiền lớn hơn $50 nhưng nhỏ hơn $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 bao gồm các 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”, “”)

Để xử lý một số giá trị ranh giới khác mà không thay đổi công thức, hãy nhập số tối thiểu và số tối đa vào hai ô riêng biệt và tham chiếu đến các ô đó trong công thức của bạn. Để công thức hoạt động chính xác trong tất cả các hàng, hãy đảm bảo sử dụng tham chiếu tuyệt đối cho các ô ranh giới ($F$1 và $F$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ự, bạn cũng có thể kiểm tra xem một ngày có nằm trong một phạm vi được chỉ định hay không.

Ví dụ: hãy gắn cờ các 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 cung cấp trực tiếp cho các 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 ô ($F$1 và $F$2 trong ví dụ này) và “kéo” chúng từ các ô đó 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 giản, chúng ta sẽ tính toán khoản tiền thưởng 5% cho doanh số “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 có thể nhận thấy những ví dụ trên đây chúng ta chỉ đánh giá hai điều kiện. Vậy nếu nhiều hơn thì sao? Chả sao cả, không có gì có thể ngăn cản bạn xét một lúc nhiều điều kiện trong công thức IF AND của mình miễn là chúng tuân thủ các giới hạn chung sau của Excel:

• Trong Excel 2019 – 2007, 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ờ, chúng ta cần câu lệnh IF AND để xác định các lệnh mà cả 3 điều kiện đề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 điểm viết bài này là tháng 10, công thức sẽ cho kết quả dưới đây:

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

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

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

Để hình dung rõ hơn, 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 chi phí vận chuyển và thời gian giao hàng ước tính (ETD):

Excellent: chi phí vận chuyển dưới $20 và ETD dưới 3 ngày

Poor: chi phí vận chuyển trên $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 lẻ:

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, các 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ề bản chất là không phân biệt chữ hoa chữ thường.

Nếu bạn đang làm việc với dữ liệu phân biệt chữ hoa chữ thường và muốn đánh giá điều kiện AND có tính đến trường hợp văn bản, hãy thực hiện từng phép thử logic riêng lẻ bên trong hàm EXACT và lồng các 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 có thể thấy trong ảnh chụp màn hình bên dưới, một số tên công ty trong cột B trông giống nhau trong phần trích dẫn các ký tự, tuy nhiên chúng là các công ty khác nhau, vì vậy chúng ta phải kiểm tra tên chính xác. 

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

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

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

Bây giờ, bạn có thể nhập bất kỳ tên và số tiền nào vào các ô được tham chiếu và công thức sẽ gắn cờ các đơ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ị 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, AND, OR và các hàm khác để chạy các bài kiểm tra logic cần thiết. 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 OR trong AND. Và bây giờ, tôi sẽ chỉ cho bạn cách bạn có thể thực hiện hai hoặc nhiều bài kiểm tra AND trong hàm OR.

Giả sử, bạn muốn đánh dấu đơn đặt hàng của hai khách hàng với số tiền lớn hơn một số nhất định, chẳng hạn như 100 đô la.

Trong ngôn ngữ Excel, các điều kiện của chúng tôi được thể hiện theo cách sau:

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

Giả sử tên khách hàng nằm trong cột B, số tiền trong cột C, 2 tên mục tiêu là G1 và G2 và số tiền mục tiêu ở G3, bạn sử dụng công thức này để đánh dấu các đơ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ương tự 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ề các công thức, bạn hãy mở Excel ra và thực hành cùng mình nhé. Cảm ơn bạn đã đã đồng hành, tiếp tục theo dõi để cập nhật những kiến thức mới nhất nhé!