Excel function learning: dichotomy principle of LOOKUP function

In the previous article "Excel function learning: 5 ways to use the LOOKUP function", we learned about the 5 ways to use the LOOKUP function. It is estimated that many friends don't understand it. Today I will give you details Let’s explain the dichotomy principle of LOOKUP. After understanding the principle, go back and read yesterday’s tutorial. I believe you will have a different understanding of LOOKUP.

In the previous article, we learned various routines of the LOOKUP function, and also mentioned many times that the search of the LOOKUP function is based on the dichotomy method, so in the end What is dichotomy? Let’s talk about this issue today.

Let’s still use yesterday’s example: search for results by serial number. The serial numbers are arranged in ascending order. The result of the formula =LOOKUP(J2,A2:D19) is correct.

1. Binary search principle

Binary search is to search the data in the search range according to Divide the number into two to find a piece of data in the middle, the middle value, and then compare it with our search value and the middle value. When the middle value is equal to the search value, the result is obtained directly; when the middle value is less than the search value, the binary search comparison is continued downward (that is, the binary search is continued in the lower half of the data excluding the middle value). method search); when the intermediate value is greater than the search value, the binary search comparison continues upward (that is, the binary search continues in the upper half of the data excluding the intermediate value). If no data equal to the search value is found after dichotomizing until the last data: if the last data is less than the search value, then use the position of the last data to obtain the result value; if the last data is greater than the search value, then search upwards The data whose position is closest to the last data is less than or equal to the search value, and then the result is obtained based on the position of this data.

I guess many friends will be confused by this explanation. Let’s take a look at the above example to see how to find the Chinese score of 79 through serial number 5.

First comparison: There are 18 data in the search range A2~A19, and the middle position is 18÷2=9, that is, the middle value is 9 in cell A10. Obviously the search value 5 is less than 9, so continue to search upwards in A2~A9;

Tips: If the number of data in the search range is an odd number, the middle position is (number 1) ÷ 2, for example, if it is row 11, The middle position is (11 1) ÷ 2 = 6; if the number of data is an even number, the middle position is (number) ÷ 2.

Second comparison: There are only 8 data, the middle position is 8÷2=4, the middle value is 4 in cell A5, the search value 5 is greater than 4, so Continue looking down in A6~A9. Note that there are only four numbers below at this time, and the data below 9 are directly excluded during the first search.

The third comparison: 4 data, the middle value is 6 of A7, the search value 5 is less than 6, so search upward. At this time, there is only one data left, 5 in cell A6, which is consistent with the search value, so the data 79 in column D corresponding to 5 is obtained.

It is very difficult to understand the dichotomy through just such an example. Let’s look at another example. Arrange the data in the table above in descending order of scores, or search for Chinese scores by serial number 5. The formula does not need to be modified. Because the order of the serial number column is out of order and is not arranged in ascending order, an error occurs. The actual number is 79, and the formula yields 94. What's going on? Let’s look at it through dichotomy.

The first search: the middle value (9th data) is 18, the search value 5 is less than 18, so search upward in A2~A9;

Second search: The above 8 data, the middle value (the 4th data) is 8, the search value 5 is less than 8, continue to search upward in A2~A4;

The third search: the above 3 data, the middle value is 1, the search value 5 is greater than 1, search downward:

The fourth search: Now there is only one data 7 in cell A4. The search value 5 is less than 7. Therefore, using 7 as a reference, find the data with a position closest to 7 and a value less than 5 or equal to 5, that is, 1 in cell A3, and thus obtain the corresponding The Chinese value is 94.

Through these two examples, I think everyone should have a certain understanding of the dichotomy. Regarding this principle, there is only one sentence in the function description:

In practical applications, we don’t need to worry about what the dichotomy is, what is the middle position, whether to look down or up, this is all the work of the function, we only need to remember one thing : The data must be arranged in ascending order. If it cannot be arranged in ascending order, then design the formula according to the precise search routine of LOOKUP.

2. LOOKUP implements data rounding

That’s all the introduction to the principle of dichotomy. Next, we need to solve the two problems left before.

In the article on May 12, we used LOOKUP to solve a rounding problem. As a result, everyone left messages asking for an explanation:

So what caused this? What is this formula that everyone is talking about? Look at the picture below:

#It turns out that this formula uses the LOOKUP function to remove all numbers below the hundredth place, thereby achieving percentile rounding.

After understanding the principle of dichotomy, it is time for LOOKUP to explain it to everyone. First, explain the ROW (A:A)*100 part. It actually just gets a set of numbers. In order to make it clear to everyone, let’s make the range A:A smaller. We use =ROW(A1:A31)*100 as an explanation:

##Although the cell can only Seeing a 100 is actually 31 numbers. We can use the f9 function key to see the specific content:

ROW function is used to get the row number of the cell. ROW(A1:A31)*100 is to multiply the row numbers of cells A1 to A31 by 100 to obtain a set of data rounded to hundreds digits {100;200;300;...3000;3100}.

Then LOOKUP came on stage. It searches for A1 in the array obtained above that has been rounded to hundreds digits. Because the array is arranged in ascending order, the essence of finding A1 is to find the maximum value less than or equal to A1 in the array.

Taking 2517.32 as an example, only 2500 is the maximum value smaller than it, so the result is 2500. Friends who are interested can try it themselves using the dichotomy principle to see if it is right.

The same is true for other numbers. The reason why this formula is clever is that it turns a rounding problem into a problem of finding references. It's really wonderful!

3. LOOKUP for data extraction

We also use LOOKUP for data extraction, so we have a 5000-word appointment:

What is the problem this time? The original question extracts the student number as shown in the picture:

Let’s use the first data in the picture above to explain. Enter "10 Zhang Yong a" in cell N1, and then enter the formula =-LOOKUP(1,-LEFT(N1,ROW(1:9))) in cell O1 to extract the student number.

# Isn’t LOOKUP a reference function? How can I extract numbers again?

The second parameter of the LEFT function uses an array, ROW(1:9) is equivalent to {1;2;3;4;5;6;7;8;9}. LEFT extracts the data specified by the first parameter from the left, and the length of the extraction is determined by the second parameter. LEFT extracts according to the array {1;2;3;4;5;6;7;8;9} and gets 9 results:

, that is, extracting from the left 1st digit, 2nd digit...all the way to 9th digit. Because the results extracted by LEFT are all text types, adding a negative sign in front of LEFT can convert the text numbers into numerical values, and the text becomes an error value:

The error value is ignored by LOOKUP, and now it becomes 1 in {-1; -10}. 1 is larger than this set of data. According to the principle of binary search, after binary search, you can only search downward until the last digit. data less than 1. Therefore, we can simply understand that when the search value is greater than all the data in the search range (regardless of whether it is in ascending order), the essence of LOOKUP is to find the last data.

In fact, it is also possible to change 1 in the formula to 0, because 0 is also larger than all negative numbers:

The current last number is -10 , so we add a negative sign in front of LOOKUP, and it becomes 10, which is the number we need to extract.

For beginners, the usage of LOOKUP in the above two cases is too advanced. Even through these introductions, it is estimated that they have only a partial understanding. In fact, learning functions is a process. From understanding to understanding, from understanding to mastering , which requires a lot of practice and thinking. As long as everyone maintains a positive and optimistic attitude and can experience the fun of learning functions, success is not far away.

Related learning recommendations: excel tutorial

The above is the detailed content of Excel function learning: dichotomy principle of LOOKUP function. For more information, please follow other related articles on the PHP Chinese website!