Shulou(Shulou.com)11/24 Report--

Original title: "only the IF function Out!" It is the best choice function in Excel, but 90% of people have never used it! "

When it comes to the IF function, it can be said that "there is no one in the world who does not appreciate you", and the first Excel function that many people come into contact with is it.

As for the enhanced version of IF-the CHOOSE function, it is the embarrassment of "who is willing to believe if you do not meet the line".

Today, Xiaohua will share with you this low-key high-energy function to correct its name.

To give a simple example, we need to divide all the students into three groups according to the Mantissa of the student number. how to set it up with the formula?

If you don't know the CHOOSE function yet, I guess you'll use IF to solve this problem.

The C2 formula is as follows:

= IF (MOD (B2P3) = 1, "one group", IF (MOD (B2P3) = 2, "two groups", "three groups"))

Description of the formula:

The MOD function returns the remainder of the division of two numbers, and MOD (B2p3) returns the remainder of the student number divided by 3, with possible values of 1, 2, and 0. Then use the nesting formula of IF function to make two-layer logical judgment and return the corresponding grouping.

Obviously, the setting of nesting formulas for IF functions is very cumbersome, and when conditional judgments (such as MOD (B2P3)) are complex, or when more situations need to be judged (for example, divided into 7 groups), it will be very difficult to still use the IF function.

But if you can use the CHOOSE function, the formula is much more concise.

The CHOOSE function returns the corresponding position in the list of values based on the index value.

We only need to use a simple formula to transform logical judgment into numbers, and CHOOSE can index it, thus realizing multi-conditional judgment.

The C2 formula is as follows:

= CHOOSE (MOD (B2Magol 3) + 1, "three groups", "one group", "two groups")

Description of the formula:

MOD (B2P3) + 1 returns the remainder + 1 of the student number divided by 3. The possible values are 2, 3 and 1 Magi chart returns one of the corresponding parameter lists {"three groups", "one group" and "two groups"} according to the value of MOD (B2p3) + 1.

C2 cell because MOD (B2Power3) + 1 equals 2, CHOOSE returns the second value of the parameter list, that is, "group".

02. Core competence the above examples do not fully show the role of the CHOOSE function, and VLOOKUP seems to be better suited to the problem of multi-conditional judgment in which the return value is only a specific cell reference or a specific constant.

When the return value applies different calculation rules under different conditions, the advantage of CHOOSE function is very obvious, and commission calculation is a typical case.

For example, a company uses the following rules to issue a commission and asks how to set a formula to quickly calculate the commission of each employee.

Query functions such as VLOOKUP are obviously unable to complete the calculation of this kind of complex rules, IF functions are multiple nesting and laborious, while CHOOSE functions are just properly handled.

The D2 formula is as follows:

= CHOOSE (C2G1MagneB2B2L1% B2B2B2B1.5% 1000 B2B2O2% 3000 B2B2B2B2O5, 000)

Description of the formula:

If the first argument of the CHOOSE function is a decimal, only the integer part is taken as the index value.

Using this feature of CHOOSE, we quickly transform the completion rate into an index value that points to the applicable calculation rules through C2x1, which greatly simplifies the operation.

Then, the calculation formulas of each case are listed one by one, and the formula can be calculated correctly.

03. The advanced usage of every function in brain combination is basically related to the array, and the CHOOSE function is no exception.

The most frequently mentioned usage is to combine CHOOSE and VLOOKUP to implement reverse queries.

As a high-frequency function, VLOOKUP is very convenient for querying data, but it does have a significant defect that it can only match the first column of the query area and return the result value of the specified column to the right of the first column.

When this occurs, VLOOKUP cannot return the last name value of column A by matching the student number of column B, because column An is to the left of column B.

At this point, we can use the CHOOSE function to build a virtual area, adjust column A to the right of column B, and the VLOOKUP function can successfully complete the query.

The G2 formula is as follows:

= VLOOKUP (F2MagneCHOOSE ({1JEO 2}, B2WR B7 Magi A2JZ A7), 2Jing 0)

Description of the formula:

If the first argument Index_num of the CHOOSE function is an array, each value is calculated when the function CHOOSE is calculated.

In this formula, the first parameter of the CHOOSE function is the array {1 B2:B7 2}, which returns the new region composed of VALUE1 "B2:B7" and VALUE2 "A2:A7" with B as the first column and An as the second column, which solves the problem that the result is listed on the left side of the matching column.

At this point, there is no obstacle to the VLOOKUP query.

In addition, we can also use CHOOSE to help LOOKUP implement cross-queries.

The C2 formula is as follows:

= LOOKUP ($A2 recordings Av5VOOSE (MATCH (Category 1 recordings Band4 Vuitton Dobby0), $Bauer 10 memorials Cobelo 10))

Description of the formula:

Using the array form of LOOKUP

LOOKUP (lookup_value,array), you only need to enter two parameters, and LOOKUP matches in the first column of the query area according to the query value A2, and returns the value of the corresponding position in the last column of the query area.

The CHOOSE function can exactly match the column order calculated by the MATCH matching cross condition and return the cell at the end of the query area, which is connected with the first cell A5 to form the query area location required by LOOKUP.

The above is the use of the CHOOSE function shared by Xiaohua, including:

❶ returns the base usage of the corresponding parameter based on the index value.

The core competence of ❷ to implement different computing rules according to different situations.

The brain-hole-level usage of ❸-assisted query functions to achieve complex functions.

Do you like this CHOOSE function?

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiaohua, Editor: Yali Zi, Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.