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:
| Nhiệm vụ | Việc bán hàng |
| 3% | $ 1 - $ 50 |
| 5% | $ 51 - $ 100 |
| 7% | $ 101 - $ 150 |
| 10% | Hơn $ 150 |
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[$B2150], 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