Hướng dẫn sử dụng công thức IF AND trong Excel

Siêu Marketing xin chia sẻ cách kết hợp hàm IF với hàm AND trong Excel để bạn có thể đồng thời kiểm tra nhiều điều kiện trong một công thức duy nhất.

Blog này đã giới thiệu nhiều hướng dẫn sử dụng hàm IF, và hôm nay, chúng tôi sẽ giới thiệu cách bạn có thể đồng thời áp dụng hàm IF cùng với hàm AND để đánh giá đa điều kiện.

Cách dùng công thức IF AND trong Excel

Để tạo công thức IF AND, bạn chỉ việc ghép hàm IF và AND lại với nhau như sau:

IF(AND(điều kiện 1, điều kiện 2,…), giá trị nếu đúng, giá trị nếu sai)

Giản lược, phương pháp này có nghĩa là: nếu điều kiện 1 và điều kiện 2 cùng chính xác, thực hiện hành động này, nếu không, thì hành động khác sẽ được thực hiện.

  • Ví dụ: Bạn muốn kiểm tra ô B2 có ghi “delivered” và ô C2 có ngày không như sau:
  • Nếu cả hai điều kiện trên đều đúng, đơn hàng sẽ được ghi là “Closed”.
  • Nếu có ít nhất một điều kiện sai, hoặc cả hai sai, công thức sẽ trả về chuỗi trống (“”).

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

Sau đây là hình ảnh minh họa cho công thức IF AND trong Excel:

Cách dùng công thức IF AND trong Excel 1
Cách dùng công thức IF AND trong Excel 1

Bạn có thể cô đặc giá trị trả về cho đối số giá trị nếu sai nếu muốn. Ví dụ sau đây là một minh họa:

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

Trong trường hợp này, công thức sẽ cho kết quả “Closed” khi cột B có “delivered” và cột C không trống. Trong các trường hợp còn lại, kết quả trả về sẽ là “Open”:

Cách dùng công thức IF AND trong Excel 2
Cách dùng công thức IF AND trong Excel 2

Lưu ý khi xử lý các điều kiện trong công thức IF AND, chữ hoa và chữ thường được Excel xem như nhau. Nếu cần kiểm tra chữ hoa chữ thường một cách khác biệt thì hãy sử dụng hàm EXACT mà blog đã hướng dẫn trước đây.

Giờ đây, khi đã nắm rõ cách dùng công thức IF AND trong Excel, bạn có thể áp dụng cho những tình huống nào? Đây là một số gợi ý:

Excel IF: kiểm tra lớn hơn VÀ nhỏ hơn

Trên đây chúng tôi chỉ cho bạn cách thử nghiệm với hai ô riêng biệt, nhưng đôi khi, bạn cần kiểm tra cùng một ô xem giá trị có ở giữa hai số cụ thể hay không. Hãy dùng công thức IF AND để thực hiện việc này trong Excel một cách dễ dàng!

Chẳng hạn, bạn có danh sách doanh số bán hàng ở cột B và muốn đánh dấu những doanh số từ $50 đến dưới $100. Công thức sau sẽ là giải pháp cho bạn, chỉ việc nhập vào ô C2 và kéo xuống dọc cột:

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

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

Công thức IF AND trong Excel 3

Đối với những trường hợp cần tính đến các giá trị ngưỡng như 50 và 100, hãy dùng cách viết dùng dấu bằng kèm theo dấu nhỏ hơn hoặc bằng (<=) và dấu lớn hơn hoặc bằng (>=):

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

Công thức IF AND trong Excel 4
Công thức IF AND trong Excel 4

Với mục đích linh hoạt hóa các giá trị ngưỡng mà không thay đổi công thức, bạn có thể nhập chúng vào hai ô tách biệt và liên kết đến chúng trong công thức. Để đảm bảo cho công thức chạy đúng đắn ở mọi dòng, hãy nhớ dùng tham chiếu tuyệt đối cho các ô chứa giá trị ngưỡng ($F$1 và $F$2 trong ví dụ này):

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

Công thức IF AND trong Excel 5
Công thức IF AND trong Excel 5

Bạn cũng có thể kiểm tra liệu ngày có thuộc phạm vi cụ thể hay không bằng cách dùng công thức tương tự.

Xét một ví dụ, bạn muốn đánh dấu các ngày từ ngày 10 đến ngày 30 tháng 9 năm 2018. Một điểm đáng lưu ý là ngày tháng trong các phép so sánh logic không được nhận diện trực tiếp. Do đó cần phải sử dụng hàm DATEVALUE để chuyển đổi, như sau:

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

Hoặc với phương pháp đơn giản hơn là nhập ngày bắt đầu và kết thúc vào hai ô ($F$1 và $F$2 trong trường hợp này) và sử dụng công thức IF AND quen thuộc để “kéo” ngày từ hai ô này:

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

Công thức IF AND trong Excel 6
Công thức IF AND trong Excel 6

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

Hàm IF AND trong Excel không chỉ dùng để trả lại giá trị cố định nhưng còn có thể thực hiện tính toán phức tạp nếu điều kiện đặt ra là TRUE hoặc FALSE.

Chẳng hạn, ta có ví dụ thường gặp là tính thưởng 5% trên tổng doanh số đối với những đơn hàng có trạng thái “Closed” và số tiền từ 100 đô la trở lên.

Số tiền sẽ được đặt ở cột B và trạng thái đơn hàng được ghi ở cột C, công thức sẽ là:

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

“`html
Công thức IF AND trong Excel 7
Công thức IF AND trong Excel 7

Với công thức này, những đơn hàng không được đáp ứng điều kiện sẽ được gán giá trị là 0 (value_if_false=0). Để cung cấp một khoản thưởng nhỏ 3% dành cho các đơn hàng không thỏa mãn điều kiện, bạn cần chỉnh sửa phần value_if_false như sau:

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

Công thức IF AND trong Excel 8
Công thức IF AND trong Excel 8

Ứng dụng các câu lệnh IF AND liên tiếp trong Excel

Nếu bạn cảm thấy các ví dụ trước là chưa đủ và bạn muốn đánh giá nhiều điều kiện cùng một lúc trong công thức IF AND, hãy yên tâm là điều này hoàn toàn khả thi miễn là bạn tuân theo những hạn chế sau đây của Excel:

  • Đối với Excel 2019 – 2007, bạn có thể dùng tối đa 255 đối số trong một công thức và độ dài của công thức không được quá 8.192 ký tự.
  • Còn đối với Excel 2003 và các phiên bản trước, bạn chỉ có thể sử dụng tối đa 30 đối số trong một công thức với tổng độ dài tối đa là 1.024 ký tự.

Minh họa với một ví dụ điều kiện AND phức tạp: Xét các điều kiện sau đây:

Giá trị đơn hàng (B2) cần phải từ 100 đô la trở lên

Tình trạng đơn hàng (C2) phải là “Closed”(đã đóng)

Ngày giao (D2) phải nằm trong tháng hiện tại

Chúng ta hãy thiết lập công thức IF AND để kiểm tra xem liệu có đủ cả ba tiêu chí hay không. Công thức sẽ như thế này:

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

Tính theo thời gian viết bài này, nếu tình cờ là tháng 10, công thức sẽ cho ra kết quả như sau:

Công thức IF AND trong Excel 9
Công thức IF AND trong Excel 9

Kỹ thuật lồng ghép câu lệnh IF AND

Có những khi, khi làm việc với bảng tính có kích thước lớn, bạn cần xem xét các nhóm tiêu chí AND riêng biệt. Trong trường hợp này, bạn có thể tận dụng công thức IF lồng nhau trong Excel để tăng cường khả năng kiểm tra logic bằng cách sử dụng các câu lệnh AND như ví dụ sau:

IF(AND(…), kết_quả1, IF(AND(…), kết_quả2, IF(AND(…), kết_quả3, kết_quả4)))

Hãy cùng nhìn vào một trường hợp cụ thể: Giả sử bạn muốn phân loại chất lượng dịch vụ dựa trên độ cao của chi phí vận chuyển cũng như thời gian ước lượng giao hàng (ETD):

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

Poor: nếu chi phí vận chuyển trên $30 và ETD lên đến 5 ngày

Average: Nếu kết quả nằm giữa các điều kiện trên

Thay vì viết một câu lệnh lớn, bạn có thể chia nhỏ thành hai câu lệnh IF AND:

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

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

… Và sau đó lồng ghép chúng vào nhau:

=IF(AND(B2>30,
“`

Như trên đây, bài viết đã được đổi cú pháp một cách hợp lý mà vẫn giữ nguyên nội dung nguyên gốc bằng tiếng Việt, kèm theo các thẻ HTML đã có từ trước.

Nếu A2>=50 ĐẠT, nếu không thất bại. Nếu B2>5 “Kém”, và nếu cả hai B2<20 và C2<3 thì “Xuất sắc”, còn không thì “Trung bình”.

Kết quả thu được như dưới đây:

Công thức IF AND trong Excel 10
Công thức IF AND trong Excel 10

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

Phần trước chúng ta đã đề cập, hàm IF AND không làm phân biệt chữ hoa hay chữ thường bởi hàm AND tự nhiên không quan tâm đến việc đó.

Khi cần xét điều kiện AND với dữ liệu cảm nhận chữ hoa thường và mong muốn phản ánh việc này trong kiểm tra, bạn nên sử dụng hàm EXACT cho từng kiểm tra logic và sau đó nhúng vào bên trong AND như sau:

IF(AND(EXACT(ô,”điều kiện 1“), EXACT(ô,”điều kiện 2“)), giá_trị_đúng, giá_trị_sai)

Xét trường hợp đánh dấu đơn hàng từ khách hàng cố định (ví dụ: tên công ty là Cyberspace) có giá trị đơn hàng cao hơn một mức nhất định, giả dụ là $100.

Trong hình chụp màn hình dưới đây, một số tên công ty ở cột B có vẻ giống hệt nhau, nhưng trên thực tế là khác biệt, do đó cần kiểm tra tên một cách chính xác. 

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

Để công thức trở nên đa năng hơn, bạn có thể đặt 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 chúng. Đừng quên sử dụng dấu $ để cố định tham chiếu ô ($G$1 và $G$2) để tránh thay đổi khi bạn kéo công thức xuống các hàng khác:

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

Bằng cách này, bạn có thể nhập bất kỳ tên và số tiền nào muốn vào ô tham chiếu và công thức sẽ tự đánh dấu các đơn hàng tương ứng trong bảng:

Công thức IF AND trong Excel 11
Công thức IF AND trong Excel 11

Hàm IF OR kết hợp với AND trong Excel

Bạn không nhất thiết chỉ sử dụng mỗi một hàm logic có sẵn trong công thức IF của Excel. Bạn có thể kết hợp các hàm IF, AND, OR,… để thực hiện các bài kiểm tra logic một cách linh hoạt. Dưới đây là công thức IF kết hợp AND và OR để kiểm tra mốn điều kiện khác nhau:

Giả sử bạn cần đánh dấu đơn đặt hàng của hai khách hàng khác nhau có giá trị lớn hơn một số cụ thể, ví dụ là 100 đô la.

Trong lối biểu đạt của Excel, điều kiện này được thể hiện như sau:

OR(AND(Khách hàng 1, Số tiền>100), AND(Khách hàng 2, Số tiền>100))

Nếu tên của khách hàng được ghi ở cột B, số tiền ở cột C, còn hai tên khách hàng mục tiêu ở G1 và G2 cùng mức số tiền mục tiêu là G3, bạn có thể dùng công thức sau để đánh dấu đơn hàng tương ứng là “x”:

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

Cách làm tương tự cũng có thể đạt được với cách viết ngắn gọn hơn:

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

Công thức IF AND trong Excel 12
Công thức IF AND trong Excel 12

Trên là hướng dẫn về việc sử dụng các hàm IF và AND cùng lúc trong Excel. Hãy thử tập làm các bài tập bên trên để hiểu hơn về chúng. Cảm ơn bạn đã theo dõi và đừng quên cập nhật kiến thức mới từ các bài viết sắp tới!

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop