Hướng dẫn if nested loop in excel - nếu vòng lặp lồng nhau trong excel

Hướng dẫn giải thích cách sử dụng chức năng lồng nhau nếu chức năng Excel để kiểm tra nhiều điều kiện. Bạn cũng sẽ tìm hiểu một vài chức năng khác có thể là lựa chọn thay thế tốt để sử dụng công thức lồng nhau trong Excel.

Làm thế nào để bạn thường thực hiện logic ra quyết định trong bảng tính Excel của bạn? Trong hầu hết các trường hợp, bạn sẽ sử dụng công thức IF để kiểm tra tình trạng của mình và trả về một giá trị nếu điều kiện được đáp ứng, một giá trị khác nếu điều kiện không được đáp ứng. Để đánh giá nhiều hơn một điều kiện và trả về các giá trị khác nhau tùy thuộc vào kết quả, bạn làm tổ nhiều IF bên trong nhau.

Mặc dù rất phổ biến, câu lệnh Nested IF không phải là cách duy nhất để kiểm tra nhiều điều kiện trong Excel. Trong hướng dẫn này, bạn sẽ tìm thấy một số ít các lựa chọn thay thế chắc chắn đáng để khám phá.

Excel lồng nhau nếu tuyên bố

Đây là công thức kinh điển excel nếu công thức ở dạng chung:

If (điều kiện1, result1, if (điều kiện2, result2, if (điều kiện3, result3, result4)))

Bạn có thể thấy rằng mỗi hàm tiếp theo nếu hàm được nhúng vào đối số value_if_false của hàm trước. Mỗi chức năng nếu được đặt trong tập hợp các dấu ngoặc đơn của riêng mình, nhưng tất cả các dấu ngoặc đơn đóng ở cuối công thức.

Công thức chung chung của chúng tôi đánh giá 3 điều kiện và trả về 4 kết quả khác nhau (kết quả 4 được trả lại nếu không có điều kiện nào là đúng). Được dịch sang ngôn ngữ của con người, câu nói nếu câu nói này bảo Excel làm như sau:

Điều kiện kiểm tra1, nếu đúng - kết quả trả về1, nếu false -test điều kiện2, nếu đúng - kết quả trả về2, nếu sai - điều kiện kiểm tra3, nếu đúng - kết quả trả về
test condition2, if TRUE - return result2, if FALSE -
test condition3, if TRUE - return result3, if FALSE -
return result4

Ví dụ, hãy tìm ra hoa hồng cho một số người bán dựa trên số tiền bán hàng họ đã thực hiện:

Trong toán học, việc thay đổi thứ tự của bổ sung không thay đổi tổng. Trong Excel, thay đổi thứ tự nếu các hàm thay đổi kết quả. Tại sao? Bởi vì một công thức lồng nhau trả về một giá trị tương ứng với điều kiện thực sự đầu tiên. Do đó, trong các câu lệnh nếu của bạn, điều rất quan trọng là sắp xếp các điều kiện theo đúng hướng - cao đến thấp hoặc thấp đến cao, tùy thuộc vào logic công thức của bạn. Trong trường hợp của chúng tôi, chúng tôi kiểm tra điều kiện "cao nhất" trước tiên, sau đó là "cao thứ hai", v.v.first TRUE condition. Therefore, in your nested IF statements, it's very important to arrange the conditions in the right direction - high to low or low to high, depending on your formula's logic. In our case, we check the "highest" condition first, then the "second highest", and so on:

=IF(B2>150, 10%, IF(B2>=101, 7%, IF(B2>=51, 5%, IF(B2>=1, 3%, ""))))

Nếu chúng tôi đặt các điều kiện theo thứ tự ngược lại, từ dưới lên, kết quả sẽ sai vì công thức của chúng tôi sẽ dừng sau khi kiểm tra logic đầu tiên (b2> = 1) cho bất kỳ giá trị nào lớn hơn 1. Hãy nói, chúng tôi có 100 đô la Trong bán hàng - nó lớn hơn 1, vì vậy công thức sẽ không kiểm tra các điều kiện khác và trả lại 3% như kết quả.

Nếu bạn muốn sắp xếp các điều kiện từ thấp đến cao, thì hãy sử dụng "ít hơn" toán tử và đánh giá điều kiện "thấp nhất" trước tiên, thì "thấp thứ hai", v.v.

=IF($B2<1, 0%, IF($B2<51, 3%, IF($B2<101, 5%, IF($B2<=150, 7%, 10%))))

Như bạn thấy, phải mất khá nhiều suy nghĩ để xây dựng logic của một câu lệnh nếu được lồng vào cuối cùng cho đến cuối cùng. Và mặc dù Microsoft Excel cho phép làm tổ lên tới 64 nếu các chức năng trong một công thức, nhưng đó không phải là điều bạn thực sự muốn làm trong bảng tính của mình. Vì vậy, nếu bạn (hoặc người khác) đang nhìn chằm chằm vào excel của bạn nếu công thức cố gắng tìm ra những gì nó thực sự làm, đã đến lúc xem xét lại chiến lược của bạn và có thể chọn một công cụ khác trong kho vũ khí của bạn.

Để biết thêm thông tin, vui lòng xem Excel lồng nhau nếu tuyên bố.

Lồng nhau nếu có hoặc/điều kiện

Trong trường hợp bạn cần đánh giá một vài bộ điều kiện khác nhau, bạn có thể thể hiện các điều kiện đó bằng cách sử dụng hoặc cũng như và hoạt động, làm tổ các chức năng bên trong các câu lệnh IF, sau đó làm tổ các câu lệnh IF vào nhau.

Lồng nhau nếu trong Excel với hoặc các tuyên bố

Bằng cách sử dụng chức năng OR, bạn có thể kiểm tra hai hoặc nhiều điều kiện khác nhau trong thử nghiệm logic của từng hàm IF và trả về đúng nếu có (ít nhất một) các đối số hoặc các đối số đánh giá là true. Để xem nó thực sự hoạt động như thế nào, xin vui lòng xem xét ví dụ sau.

Giả sử, bạn có hai cột bán hàng, nói rằng doanh số bán hàng trong cột B và tháng hai trong cột C. Bạn muốn kiểm tra các số trong cả hai cột và tính toán hoa hồng dựa trên số lượng cao hơn. Nói cách khác, bạn xây dựng một công thức với logic sau: nếu doanh số bán hàng tháng 2 hoặc tháng 2 lớn hơn $ 150, người bán nhận được 10% hoa hồng, nếu doanh số bán hàng tháng 2 hoặc tháng 2 lớn hơn hoặc bằng $ 101, người bán nhận được 7% hoa hồng , và như thế.

Để hoàn thành, hãy viết một vài câu như hoặc (B2> 150, C2> 150) và tổ chúng vào các thử nghiệm logic của các hàm IF được thảo luận ở trên. Kết quả là, bạn nhận được công thức này:

=IF(OR(B2>150, C2>150), 10%, IF(OR(B2>=101, C2>=101),7%, IF(OR(B2>=51, C2>=51), 5%, IF(OR(B2>=1, C2>=1), 3%, ""))))

Và có ủy ban được chỉ định dựa trên số tiền bán hàng cao hơn:

Để biết thêm các ví dụ công thức, vui lòng xem Excel nếu hoặc tuyên bố.

Lồng nhau nếu trong Excel với và các tuyên bố

Nếu các bài kiểm tra logic của bạn bao gồm nhiều điều kiện và tất cả các điều kiện đó sẽ đánh giá thành true, thể hiện chúng bằng cách sử dụng chức năng và chức năng.

Ví dụ, để gán hoa hồng dựa trên số lượng doanh số thấp hơn, lấy công thức trên và thay thế hoặc với và báo cáo. Nói cách khác, bạn nói với Excel chỉ trả lại 10% nếu doanh số bán hàng tháng 2 và tháng 2 lớn hơn 150 đô la, 7% nếu doanh số bán hàng tháng 2 và tháng 2 lớn hơn hoặc bằng 101 đô la, v.v.

=IF(AND(B2>150, C2>150), 10%, IF(AND(B2>=101, C2>=101), 7%, IF(AND(B2>=51, C2>=51), 5%, IF(AND(B2>=1, C2>=1), 3%, ""))))

Kết quả là, công thức lồng nhau của chúng tôi tính toán hoa hồng dựa trên số lượng thấp hơn trong các cột B và C. Nếu một trong hai cột trống, không có hoa hồng nào vì không có điều kiện nào và điều kiện được đáp ứng:

Nếu bạn muốn trả về 0% thay vì các ô trống, hãy thay thế một chuỗi trống ('' '") trong đối số cuối cùng bằng 0%:

=IF(AND(B2>150, C2>150), 10%, IF(AND(B2>=101, C2>=101), 7%, IF(AND(B2>=51, C2>=51), 5%, IF(AND(B2>=1, C2>=1), 3%, 0%))))

Thêm thông tin có thể được tìm thấy ở đây: Excel nếu có nhiều và/hoặc điều kiện.

Vlookup thay vì lồng nhau nếu trong excel

Khi bạn đang xử lý "thang đo", tức là phạm vi liên tục của các giá trị số cùng nhau bao gồm toàn bộ phạm vi, trong hầu hết các trường hợp, bạn có thể sử dụng hàm VLookup thay vì các if lồng nhau.

Để bắt đầu, hãy tạo một bảng tham chiếu như được hiển thị trong ảnh chụp màn hình bên dưới. Và sau đó, xây dựng một công thức Vlookup với sự phù hợp gần đúng, tức là với đối số Range_Lookup được đặt thành True.approximate match, i.e. with the range_lookup argument set to TRUE.

Giả sử giá trị tra cứu nằm trong B2 và bảng tham chiếu là F2: G5, công thức diễn ra như sau:

=VLOOKUP(B2,$F$2:$G$5,2,TRUE)

Xin lưu ý rằng chúng tôi sửa lỗi TAGE_Array với các tài liệu tham khảo tuyệt đối ($ F $ 2: $ g $ 5) để công thức sao chép chính xác vào các ô khác:

Bằng cách đặt đối số cuối cùng của công thức VLOOKUP của bạn thành True, bạn bảo Excel tìm kiếm trận đấu gần nhất - nếu không tìm thấy khớp chính xác, hãy trả về giá trị lớn nhất tiếp theo nhỏ hơn giá trị tra cứu. Kết quả là, công thức của bạn sẽ không chỉ khớp với các giá trị chính xác trong bảng tra cứu mà còn bất kỳ giá trị nào nằm ở giữa.closest match - if an exact match is not found, return the next largest value that is smaller than the lookup value. As the result, your formula will match not only the exact values in the lookup table, but also any values that fall in between.

Ví dụ, giá trị tra cứu trong B3 là $ 95. Số này không tồn tại trong bảng tra cứu và vlookup với khớp chính xác sẽ trả về lỗi #N/A trong trường hợp này. Nhưng Vlookup với trận đấu gần đúng tiếp tục tìm kiếm cho đến khi tìm thấy giá trị gần nhất nhỏ hơn giá trị tra cứu (là 50 đô la trong ví dụ của chúng tôi) và trả về giá trị từ cột thứ hai trong cùng một hàng (là 5%).

Nhưng điều gì sẽ xảy ra nếu giá trị tra cứu nhỏ hơn số nhỏ nhất trong bảng tra cứu hoặc ô tra cứu trống? Trong trường hợp này, một công thức VLOOKUP sẽ trả về lỗi #N/A. Nếu đó không phải là những gì bạn thực sự muốn, Nest Vlookup bên trong iferror và cung cấp giá trị cho đầu ra khi không tìm thấy giá trị tra cứu. Ví dụ:

=IFERROR(VLOOKUP(B2, $F$2:$G$5, 2, TRUE), "Outside range")

Lưu ý quan trọng! Đối với công thức VLookup với khớp gần đúng để hoạt động chính xác, cột đầu tiên trong bảng tra cứu phải được sắp xếp theo thứ tự tăng dần, từ nhỏ nhất đến lớn nhất. For a Vlookup formula with approximate match to work correctly, the first column in the lookup table must be sorted in ascending order, from smallest to largest.

Để biết thêm thông tin, vui lòng xem VLookUP phù hợp chính xác so với Vlookup gần đúng.

Tuyên bố IFS thay thế cho chức năng nếu chức năng

Trong Excel 2016 và các phiên bản sau này, Microsoft đã giới thiệu một chức năng đặc biệt để đánh giá nhiều điều kiện - chức năng IFS.

Một công thức IFS có thể xử lý tối đa 127 cặp logic_test/value_if_true và thử nghiệm logic đầu tiên đánh giá thành "chiến thắng" thực sự:

Ifs (logical_test1, value_if_true1, [logical_test2, value_if_true2] ...)

Theo cú pháp trên, công thức Nested của chúng tôi có thể được xây dựng lại theo cách này:

=IFS(B2>150, 10%, B2>=101, 7%, B2>=51, 5%, B2>0, 3%)

Vui lòng chú ý rằng chức năng IFS trả về lỗi #N/A nếu không có điều kiện được chỉ định nào được đáp ứng. Để tránh điều này, bạn có thể thêm một logic_test/value_if_true vào cuối công thức của bạn sẽ trả về 0 hoặc chuỗi trống ("") hoặc bất kỳ giá trị nào bạn muốn nếu không có thử nghiệm logic nào trước đó là đúng:

=IFS(B2>150, 10%, B2>=101, 7%, B2>=51, 5%, B2>0, 3%, TRUE, "")

Kết quả là, công thức của chúng tôi sẽ trả về một chuỗi trống (ô trống) thay vì lỗi #N/A nếu một ô tương ứng trong cột B trống hoặc chứa văn bản hoặc số âm.

Ghi chú. Giống như Nested If, chức năng IFS của Excel trả về giá trị tương ứng với điều kiện đầu tiên đánh giá là TRUE, đó là lý do tại sao thứ tự các thử nghiệm logic trong công thức IFS có vấn đề. Like nested IF, Excel's IFS function returns a value corresponding to the first condition that evaluates to TRUE, which is why the order of logical tests in an IFS formula matters.

Để biết thêm thông tin, vui lòng xem chức năng IFS Excel thay vì lồng nhau nếu.

Chọn thay vì lồng nhau nếu công thức trong excel

Một cách khác để kiểm tra nhiều điều kiện trong một công thức duy nhất trong Excel là sử dụng hàm chọn, được thiết kế để trả về giá trị từ danh sách dựa trên vị trí của giá trị đó.

Áp dụng cho bộ dữ liệu mẫu của chúng tôi, công thức có hình dạng sau:

=CHOOSE((B2>=1) + (B2>=51) + (B2>=101) + (B2>150), 3%, 5%, 7%, 10%)

Trong đối số đầu tiên (index_num), bạn đánh giá tất cả các điều kiện và thêm kết quả. Cho rằng đúng tương đương với 1 và sai với 0, theo cách này bạn tính toán vị trí của giá trị để trả về.

Ví dụ, giá trị trong B2 là $ 150. Đối với giá trị này, 3 điều kiện đầu tiên là đúng và cái cuối cùng (b2> 150) là sai. Vì vậy, index_num bằng 3, có nghĩa là giá trị thứ 3 được trả về, là 7%.

Mẹo. Nếu không có bài kiểm tra logic nào là đúng, index_num bằng 0 và công thức trả về #Value! lỗi. Một bản sửa lỗi dễ dàng là gói chọn trong hàm iferror như thế này: If none of the logical tests is TRUE, index_num is equal to 0, and the formula returns the #VALUE! error. An easy fix is wrapping CHOOSE in the IFERROR function like this:

=IF($B2<1, 0%, IF($B2<51, 3%, IF($B2<101, 5%, IF($B2<=150, 7%, 10%))))0

Để biết thêm thông tin, vui lòng xem Excel chọn chức năng với các ví dụ công thức.

Chuyển đổi chức năng như một dạng lồng nhau ngắn gọn nếu trong excel

Trong các tình huống khi bạn đang xử lý một tập hợp các giá trị được xác định trước, không phải là thang đo, hàm chuyển đổi có thể là một sự thay thế nhỏ gọn cho các câu lệnh nếu được lồng vào phức tạp:

Chuyển đổi (biểu thức, value1, result1, value2, result2, veS, [mặc định])

Hàm chuyển đổi đánh giá biểu thức dựa trên danh sách các giá trị và trả về kết quả tương ứng với trận đấu được tìm thấy đầu tiên.

Trong trường hợp, bạn muốn tính toán hoa hồng dựa trên các lớp sau, thay vì số tiền bán hàng, bạn có thể sử dụng phiên bản nhỏ gọn này của Nested nếu công thức trong Excel:

=IF($B2<1, 0%, IF($B2<51, 3%, IF($B2<101, 5%, IF($B2<=150, 7%, 10%))))1

Hoặc, bạn có thể tạo một bảng tham chiếu như được hiển thị trong ảnh chụp màn hình bên dưới và sử dụng tham chiếu ô thay vì các giá trị được mã hóa cứng:

=IF($B2<1, 0%, IF($B2<51, 3%, IF($B2<101, 5%, IF($B2<=150, 7%, 10%))))2

Xin lưu ý rằng chúng tôi khóa tất cả các tài liệu tham khảo ngoại trừ cái đầu tiên có dấu hiệu $ để ngăn chúng thay đổi khi sao chép công thức sang các ô khác:

Ghi chú. Chức năng chuyển đổi chỉ có sẵn trong Excel 2016 và cao hơn. The SWITCH function is only available in Excel 2016 and higher.

Để biết thêm thông tin, vui lòng xem chức năng Switch - hình thức nhỏ gọn của câu lệnh Nested if.

Kết hợp nhiều chức năng nếu excel

Như đã đề cập trong ví dụ trước, hàm Switch chỉ được giới thiệu trong Excel 2016. Để xử lý các tác vụ tương tự trong các phiên bản Excel cũ hơn, bạn có thể kết hợp hai hoặc nhiều câu lệnh bằng cách sử dụng toán tử Concatenate (&) hoặc hàm Concatenate.

Ví dụ:

=IF($B2<1, 0%, IF($B2<51, 3%, IF($B2<101, 5%, IF($B2<=150, 7%, 10%))))3

Hoặc

=IF($B2<1, 0%, IF($B2<51, 3%, IF($B2<101, 5%, IF($B2<=150, 7%, 10%))))4

Như bạn có thể nhận thấy, chúng tôi nhân kết quả với 1 trong cả hai công thức. Nó được thực hiện để chuyển đổi một chuỗi được trả về bởi công thức Concatenate thành một số. Nếu đầu ra dự kiến ​​của bạn là văn bản, thì hoạt động nhân không cần thiết.

Để biết thêm thông tin, vui lòng xem chức năng Concatenate trong Excel.

Bạn có thể thấy rằng Microsoft Excel cung cấp một số ít các lựa chọn thay thế tốt cho các công thức nếu công thức và hy vọng hướng dẫn này đã cung cấp cho bạn một số manh mối về cách tận dụng chúng trong bảng tính của bạn. Để xem xét kỹ hơn các ví dụ được thảo luận trong hướng dẫn này, bạn có thể tải xuống sổ làm việc mẫu của chúng tôi dưới đây. 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 lồng nhau if statement - ví dụ (tệp .xlsx)

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

Làm thế nào để bạn sử dụng một vòng lặp nếu vòng lặp trong excel?

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

Nested có nghĩa là gì trong excel?