Practical Excel skills sharing: Let's talk about the three major methods of interval query!

The problem of interval value selection is often encountered in our daily work, such as: sales commission, grade assessment, production standard assessment, performance appraisal, etc., all belong to this type of problem. Today I will introduce to you some commonly used ones. The methods and methods are designed to enrich everyone's knowledge and at the same time broaden their horizons on the application of functions.

[Data source]

First give the source data for today’s teaching. It is recommended that before looking at the solution below, you first think about what method you will use to deal with it, and how many methods you will use to deal with it, and then confirm it.

The data itself has nothing to say. The requirement is: based on the value in column B, find the interval coefficient corresponding to the range in column H in the range condition of column E, and extract it to Column C accrual coefficient.

In itself, this type of problem is more about calculating the accrual amount. In order to highlight the function part of extracting the coefficient, we have omitted this link. Please remember to multiply each function below by column B. The accrued amount can be obtained from the numerical value.

【Additional knowledge】

Before giving you a way to solve the problem, let me talk about some additional knowledge. Take a look at the expression in column F in the picture above. In daily work, we see that most of the condition descriptions for such problems are written in the format of column E. However, the standard way of expressing the interval should be like the format of column F, which consists of two values ​​separated by commas. The value on the left is the minimum value and the value on the right is the maximum value. "[ ]" means including equals, "( )" means not including equals. The infinity symbol is entered in the caret, as follows.

[Solution solution】

[Method 1: IF function]

Legend:

## C2 cell function:

=IF(B2>=500,0.1,IF(B2>=300,5%,IF(B2>=150,3%,IF(B2>=50 ,1%,0))))

Function analysis:

For the problem of interval values, the IF function may be the first way we think of , is also the most used method by many students, and it is indeed the best way to understand the principle of functions. But small functions have great wisdom. For the IF function, we need to know that the order of operations of multi-level IF nesting is from left to It is performed on the right. If the first-level condition B2>=500 is true (TRUE), 0.1 is returned. If it is false (FALSE), the second-level IF judgment B2>=300 is performed. If it is true (TRUE), 5% is returned. If false (FALSE), the third level judgment condition is performed, and so on. When a certain level condition is true (TRUE) and a certain value is returned, the function will no longer run backwards.

So many students Wrong IF nesting means writing the wrong logical relationship, resulting in incorrect return values. Remember that this logical relationship must either use all > signs, from uppercase to lowercase; or use all

The above picture is an IF function written entirely with numbers. Have you noticed that the = sign in the condition is included in the next-level jump point interval, so we only use the sign in the function. , the = sign is not used.

C2 cell function:

=IF(B2IF(B2IF(B2IF(B2IF(B210%)))))

[Method 2: VLOOKUP function]

Legend:

C2 cell function:

=VLOOKUP(B2,$G$2:$H$6,2,1)

Function analysis:

VLOOKUP function is used For vertical query, there are four parameters in total. We will not introduce the first three in detail. The fourth parameter is fuzzy query (TRUE)/precise query (FALSE).

The VLOOKUP function is a usage rate Very high function, we can use precise queries in most working environments, but when it comes to interval values, fuzzy queries must be used.

When using the VLOOKUP function to obtain interval values, our The data source must arrange the data in "ascending order" like columns G and H. Of course, we can also use a sequence, and the input of the same sequence must also be written in ascending order {0,0;50,1%;150, 3%;300,5%;500,10%}. The issue of arrays is not the focus today. We will talk about it later when we write the content of array functions.

[Method 3: LOOKUP function]

Legend:

C2 Unit Lattice function:

=LOOKUP(B2,$G$2:$G$6,$H$2:$H$6)

Function analysis:

Here we use the "vector" usage of the LOOKUP function. That is, from the query value in the first area (the second parameter), the corresponding value in the second area (the third parameter) is returned. Is this better to understand than the VLOOKUP function? In the same way, like the VLOOKUP function, its data source also needs to be arranged in ascending order.

Of course we can also use the array usage of the LOOKUP function, as follows:

The array usage of the LOOKUP function is to find the value at the beginning of the area, and then Returns the value corresponding to the end of the range.

Compared with the VLOOKUP function (vertical query) and the HLOOKUP function (horizontal query), the LOOKUP function seems to be more "all-powerful". It can do today's interval value problem based on rows or columns, as follows Picture:

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: Let's talk about the three major methods of interval query!. For more information, please follow other related articles on the PHP Chinese website!