函数if实例（Function if instance）.docVIP

函数if实例（Function if instance） Using array formula to solve the 7 layer nesting problem of IF () function The function nesting in Excel can have 7 layers at most. For IF (), there are times when you can work with AND () and OR () to solve multilevel problems. But sometimes they cant be solved by using them, which requires us to consider other methods. At first, I thought that only the IF () function had 7 nested problems, and later that I wanted to replace IF () with CHOOSE (), and I found that CHOOSE () could not do more than 7 levels of nesting. Finally, it is concluded that all functions in Excel are not more than 7 nested. ------------------------ Nested solution for 7 layers of IF (): ------------------------ Look at the syntax of the IF () function first: IF (logical_test, value_if_true, value_if_false), Here is the logical_test, which can only return TRUE or FALSE, In Excel, the TRUE value is 1, and the FALSE value is 0, but to reflect 1 and 0, You must add 0 or multiply 1 to TRUE and FALSE (that is, to convert the type). A little more, and its crucial: For the IF () structure, the judgment is ended whenever the logical_test is encountered in TRUE (in the order of the order). After the principle is clear, the actual operation begins: First, put the conditions in order. For example: A1-50, -50=A10, A1=0,0A150,... Equivalent to: IF (A1-50,..., IF (), (A1=-50) * (A10),..., IF (A1=0,..., IF ((A10) * (A150),...))) Explanation: the * here means AND and is used because the array formula will be used. Write here almost, and finally to do is: make the conditions and results corresponding. According to what was said, the judgment is over as long as the condition is TRUE (in the order of the condition). So we have to find the first place to meet the requirements. Give an example first: {=MIN (IF ({TRUE, FALSE, TRUE, TRUE, FALSE, FALSE, {1,2,3,4,5,6,7,8,9}, TRUE, FALSE, TRUE}+0={1,1,1,1,1,1,1,1,1},))} The {TRUE, FALSE, TRUE, TRUE,, FALSE, FALSE, TRUE, FALSE, and TRUE} are