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

Original title: "learned a new Excel function, too easy to use!" But I don't recommend that you learn... "

I am Osama bin Laden, a sand sculpture high-energy teacher who can design forms.

I'll tell you a terrible thing.

Yesterday I learned a new EXCEL function: LET function!

Yes, although I have been very strong, but I still keep on learning!

Isn't it scary!

1. What is the LET function for? The official explanation is like this.

Translated into Chinese that everyone can understand, that is:

If your formula is long and difficult to remember, you can give the formula a name and replace the long formula with a name.

In fact, this concept is not new, this is the concept of "variable" in programming!

Give me a simple example.

For example, now we are going to calculate 5 times 1.

The common formula is like this, which is not professional at all.

= A2+B2 We can save the number 5 in X, then calculate SUM (XMagol 1), then put 5 into the SUM function, and calculate the result "= 6".

So the above formula can be understood in two steps.

The first step is to save 5 in X.

The second step is to replace the write with 5 to calculate the SUM.

Isn't it amazing!

But I guess there must be some people who are jealous of my talent, and although they have already admired it, they still show an expression of disdain on the surface.

2. Let's take a look at another case. Now there are two forms, one is the list, the other is the transcript.

▲ list

The ▲ report card now needs to add the scores from the report card to the list, and then:

Pass remarks with ❶ greater than or equal to 60 points

If the ❷ is less than 60 points, the original number is displayed.

How do you write the formula?

You have to use VLOOKUP to query the results, and then write an IF function to judge the conditions.

And then the formula is so long.

= IF (VLOOKUP) > 60, passing, VLOOKUP) the formula is long, mainly because the VLOOKUP formula is written twice.

At this point, we can give the VLOOKUP formula a name "score", and then use "grade" directly in the IF function.

This named operation is what the LET function does!

The modified formula is as follows:

= LET score VLOOKUP (A2 recordings Fellow1 IF 9), IF 60, "pass" score) is it particularly easy to use?

3. You can also use a lot of areas that are often referenced in the formula, such as the following VLOOKUP function.

If you want to find out if you have a boyfriend, the formula is not easy to understand when there are more constituencies.

So we can use the LET function to give the selection a name, which is easy to remember.

Modified formula:

= LET (name, $B$2:$B$9, boyfriend, $A$2:$A$9, change order, IF ({1Magin0}, name, boyfriend), VLOOKUP (E2, change order, 2jin0)) so that you can easily understand the calculation principle of the formula.

❶ name = $B$2:$B$9

❷ boyfriend = $A$2:$A$9

❸ change order = IF ({1J 0}, name, boyfriend).

It turns out that the function of IF {1J 0} is to change the order of the columns "name" and "boyfriend".

How's it going? Are you jealous of my talent?!

4. Summarize that the LET function is easy to use, but still pay attention to some rules of use.

❶ name cannot be a number

There cannot be operators in the ❷ name

The name defined by ❸, which can only be used in the current formula.

So do you want to learn?

It is not recommended to learn, because.

OFFICE 365 is required to support it!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: bin Laden Dony

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.