công thức lồng nhau, nhiều câu lệnh và nhiều hơn nữa

Hướng dẫn cho thấy cách sử dụng IF cùng với hàm AND trong Excel để kiểm tra nhiều điều kiện trong một công thức.

Một số điều trên thế giới là hữu hạn. Những người khác là vô hạn, và hàm IF dường như là một trong những điều như vậy. Trên blog của chúng tôi, chúng tôi đã có một số Hướng dẫn Excel IF và vẫn khám phá ra cách sử dụng mới mỗi ngày. Hôm nay, chúng ta sẽ xem xét cách bạn có thể sử dụng IF cùng với hàm AND để đánh giá hai hoặc nhiều điều kiện cùng một lúc.

Câu lệnh IF VÀ trong Excel

Để xây dựng câu lệnh IF AND, rõ ràng bạn cần kết hợp NẾU chức năng trong một công thức. Đây là cách thực hiện:

NẾU (VÀ (điều kiện1, điều kiện2, Hoài), value_if_true, value_if_false)

Được dịch sang tiếng Anh đơn giản, công thức đọc như sau: NẾU điều kiện 1 là đúng VÀ điều kiện 2 là đúng, làm một việc, nếu không thì làm việc khác.

Ví dụ: hãy tạo một công thức kiểm tra xem B2 có được “phân phối” không và C2 không trống và tùy thuộc vào kết quả, thực hiện một trong các cách sau:

  • Nếu cả hai điều kiện là TRUE, đánh dấu thứ tự là “Đã đóng”.
  • Nếu một trong hai điều kiện là FALSE hoặc cả hai đều là 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:
Câu lệnh IF VÀ trong Excel

Nếu bạn muốn trả về một số giá trị trong trường hợp kiểm tra logic ước tính thành FALSE, hãy cung cấp giá trị đó trong value_if_false tranh luận. Ví dụ:

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

Công thức đã sửa đổi xuất ra “Đã đóng” nếu cột B được “phân phối” 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ề “Mở”:
NẾU VÀ công thức trong Excel

Ghi chú. Khi sử dụng công thức IF AND trong Excel để đánh giá các điều kiện văn bản, xin lưu ý rằng chữ thường và chữ hoa được coi là cùng một ký tự. Nếu bạn đang tìm kiếm một phân biệt chữ hoa chữ thường, bọc một hoặc nhiều đối số của AND vào hàm EXACT như được thực hiện trong ví dụ được liên kết.

Bây giờ bạn đã biết cú pháp của câu lệnh IF IF VÀ Excel, hãy để tôi chỉ cho bạn loại nhiệm vụ nào nó có thể giải quyết.

Excel IF: lớn hơn AND nhỏ hơn

Trong ví dụ trước, chúng tôi đã 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 bài kiểm tra trên cùng một ô. Một ví dụ điển hình là kiểm tra xem giá trị ô có phải là giữa hai số. Hàm IF IF VÀ cũng có thể dễ dàng làm điều đó!

Giả sử bạn có một số 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 dưới $ 100. Để hoàn thành nó, 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", "")

Công thức IF để kiểm tra điều kiện 'lớn hơn AND nhỏ hơn'

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

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

Tìm các giá trị giữa hai số, bao gồm các giá trị biên.

Để xử lý một số giá trị biên khác mà không thay đổi công thức, hãy nhập số lượng tối thiểu và tối đa vào hai ô riêng biệt và tham khảo 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 chắc chắn sử dụng tài liệu tham khảo tuyệt đối đối với các ô biên ($ F $ 1 và $ F $ 2 trong trường hợp của chúng tôi):

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

IF và công thức để gắn cờ các giá trị giữa các số được chỉ định

Bằng cách sử dụng một công thức tương tự, bạn có thể kiểm tra nếu ngày ngã trong một phạm vi được chỉ định.

Ví dụ: bao gồm các ngày gắn cờ trong khoảng thời gian 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 không thể được cung cấp trực tiếp cho các bài kiểm tra logic. Để Excel hiểu ngày, chúng phải được đặt trong NGÀY chức năng, như thế này:

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

Hoặc đơn giản là nhập TừĐến ngày trong hai ô (trong ví dụ này là $ F $ 1 và $ F $ 2) và “kéo” chúng khỏi 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 VÀ công thức để tìm ngày nằm trong một phạm vi được chỉ định

NẾU cái này VÀ cái kia, sau đó tính toán một cái gì đó

Ngoài việc trả về các giá trị được xác định trước, hàm IF IF của 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.

Để thể hiện cách tiếp cận, chúng tôi sẽ tính toán tiền thưởng 5% cho doanh số “Đã đóng” 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)

Nếu các điều kiện được chỉ định là TRUE, thì hãy tính toán một cái gì đó

Công thức trên chỉ định 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 phần thưởng kích thích nhỏ, hãy nói 3%, cho các đơn hàng không đáp ứng các điều kiện, bao gồm phương trình tương ứng trong value_if_false tranh luận:

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

IF và công thức để 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 là TRUE hay FALSE

Nhiều câu lệnh IF VÀ trong Excel

Như bạn có thể nhận thấy, chúng tôi chỉ đánh giá hai tiêu chí trong tất cả các ví dụ trên. Nhưng không có gì có thể ngăn bạn bao gồm ba bài kiểm tra trở lên trong các công thức IF AND của bạn miễn là chúng tuân thủ các giới hạn chung này 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 quá 8.192 ký tự.
  • Trong Excel 2003 trở xuống, không cho phép quá 30 đối số, với tổng độ dài không quá 1.024 ký tự.

Để làm ví dụ cho nhiều điều kiện AND, vui lòng xem xét các điều kiện sau:

  • Số tiền (B2) phải lớn hơn hoặc bằng $ 100
  • Trạng thái đơn hàng (C2) là “Đã đóng”
  • Ngày giao hàng (D2) là trong tháng hiện tại

Bây giờ, chúng ta cần một câu lệnh IF AND để xác định các đơn hàng có cả 3 điều kiện là TRUE. Và đây là:

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

Cho rằng ‘tháng hiện tại’ tại thời điểm viết là tháng 10, công thức mang lại kết quả như sau:
Nhiều câu lệnh IF VÀ trong Excel

Báo cáo IF và lồng nhau

Khi làm việc với các bảng tính lớn, có thể bạn sẽ phải kiểm tra một vài bộ tiêu chí AND khác nhau tại một thời điểm. Đối với điều này, bạn có một cổ điển Excel lồng nhau công thức IF và mở rộng các bài kiểm tra logic của nó bằng các câu lệnh AND, như thế này:

NẾU (VÀ (rất), đầu ra1, NẾU (VÀ (góc), đầu ra2, NẾU (VÀ (góc), đầu ra3, đầu ra4)))

Để có được ý tưởng chung, xin vui lòng xem ví dụ sau đây.

Giả sử bạn muốn xếp hạng dịch vụ của mình dựa trên chi phí giao hàng và thời gian giao hàng ước tính (ETD):

  • Thông minh: chi phí giao hàng dưới $ 20 và ETD dưới 3 ngày
  • Nghèo: chi phí giao hàng trên $ 30 và ETD trong 5 ngày
  • Trung bình cộng: bất cứ điều gì ở giữa

Để hoàn thành nó, bạn 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", …)

Cẩu 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ẽ trông giống như thế này:
Báo cáo IF và lồng nhau

Nhiều ví dụ công thức có thể được tìm thấy trong Excel lồng nhau IF và câu lệnh.

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

Như đã đề cập ở phần đầu của hướng dẫn này, các công thức IF IF và Excel không phân biệt giữa các ký tự chữ hoa và chữ thường vì hàm AND không phân biệt chữ hoa chữ thường.

Nếu bạn đang làm việc với trường hợp nhạy cảm dữ liệu và muốn đánh giá các điều kiện AND có tính đến trường hợp văn bản, thực hiện từng kiểm tra 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:

NẾU (VÀ (CHÍNH XÁC (tế bào, “điều kiện1“), CHÍNH XÁC(tế bào, “điều kiện2“)), value_if_true, value_if_false)

Trong ví dụ này, chúng tôi 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 Không gian mạng) với số tiền vượt quá một số nhất định, giả sử $ 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 như trường hợp các ký tự và tuy nhiên chúng là các công ty khác nhau, vì vậy chúng tôi phải kiểm tra tên chính xác. Số tiền trong cột C là số và chúng tôi chạy thử nghiệm “lớn hơn” thường xuyên cho chúng:

=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ố lượng khách hàng mục tiêu vào hai ô riêng biệt và tham khảo các ô đó. Chỉ cần nhớ khóa các tham chiếu ô bằng dấu $ ($ G $ 1 và $ G $ 2 trong trường hợp của chúng tôi) vì vậy chúng sẽ 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 trong 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:
Hàm IF AND phân biệt chữ hoa chữ thường trong Excel

NẾU HOẶC VÀ công thức trong Excel

Trong các công thức IF IF, bạn không bị giới hạn chỉ sử dụng một chức năng 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 chức năng khác để chạy các bài kiểm tra logic cần thiết. Đây là một ví dụ về NẾU VÀ HOẶC công thức 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 các đơn đặt hàng của hai khách hàng với số tiền lớn hơn một con số nhất định, giả sử là 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 này:

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 nằm trong G1 và G2 và số lượng mục tiêu là trong G3, bạn sử dụng công thức này để đánh dấu các đơn 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", "")

Các kết quả tương tự có thể đạt được với một cú pháp nhỏ gọn hơn:

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

Công thức NẾU VÀ HOẶC trong Excel

Không chắc chắn bạn hoàn toàn hiểu logic của công thức? Thêm thông tin có thể được tìm thấy trong Excel IF với nhiều điều kiện AND / OR.

Đó là cách bạn sử dụng các hàm IF và AND cùng nhau trong Excel. Để hiểu thêm về các công thức, tôi mời bạn tải xuống mẫu của chúng tôi Sổ làm việc NẾU VÀ Excel. Cảm ơn bạn đã đọc và hẹn gặp lại vào tuần tới!

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

Source link

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *