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

The original title: "inefficiency is always scolded and cried by the boss, learned this Excel skill is no longer afraid!" "

Hello, everyone. I am a bitter worker in Pingyue, determined to use Excel to redeem myself from being squeezed.

IF this logic processing function, I believe you should be no stranger to it.

What? Others say they can't use it?

OK, no, no, no. Oh no, wrong, is to give you a detailed introduction to this IF if the function, read today's article, you can no longer say no oh.

Since some cute girls may have heard of the IF function for the first time, I think it's necessary to define it first. Let's take a look at the official description of IF:

A value is returned if the specified condition evaluates to the TRUE,IF function, or another value is returned if the condition evaluates to FALSE.

The function expression is IF (logical_test, [value_if_true], [value_if_false])

I heard some cute little girls say they don't know the technical terms. This is... Blame me for not learning English well. It's all right. The boss hasn't come back yet. I still have time to speak slowly.

"logical_test" indicates conditional judgment, returns the value "value_if_true" if the condition is true, and "value_if_false" if it is not true.

In vernacular, the first is a judgment condition, if the answer is "yes", the first value is output, and the answer "no" is the second value.

Oh, by the way, to emphasize that this formula must be used in English symbols, otherwise the wrong value will be returned.

What can the IF function do? don't worry. It just occurred to me that my boss left a lot of work before his business trip. I haven't done it yet. I just arranged an example to show you.

1. Single-condition judgment before the boss leaves, let me count the checklist. There is a bonus of 500 yuan for those with more than 70 points, and nothing for those with 70 points or less.

❶ select the cell that needs to get the result, enter the formula and drop down to fill it:

= IF (B20 > C20jue 500, "none")

This function expression is very simple and suitable for simple single condition judgment. We only need to set the condition and the corresponding values of "yes" and "no".

2. After judging by the nesting condition, the boss felt that there were too many blondes in this way, so he changed another way: those below 60 were given 200 for those below 80 and below, and those above for 300, so that everyone could get a bonus to reflect the welfare of the company.

❶ do not rush to enter the formula, this time is not a simple condition judgment, the need for nested conditions. Consider 80 points as the limit first, and the formula is:

= IF (B2080300200) for ease of understanding, let's assume that the result of this formula is X.

❷ then considers the 60-point limit, and the formula is:

= IFB2060,100,X ❸ uses the equivalent substitution of mathematics, and the final input formula is:

= IFB2060,100, "= IFB2080300200)"

Why does an error appear?

Hit the blackboard, first of all, a formula only needs to add "=" at the beginning of the input, and does not need to be added when the value is returned.

= IFB2060,100, "= IFB2080300200" secondly, in the IF function, double quotation marks are required if the result is literal; if it is a number, double quotation marks are not required. If the number is in double quotation marks, it doesn't matter.

❹, so the final formula we enter should be:

= IFB2060,100,IFB2080300200

3, reverse search only rely on the ability of the IF function, it is not very good, but it and other functions, can burst out a more powerful role. For example, with VLOOKUP, you can do a reverse search.

The form is ready, but the boss is the memory of the fish, only seven seconds, and then asked me who scored 60 points. To be honest, I was also very scared at that time. After all, I didn't have a good brain, and I couldn't remember so many people.

I pretended to reply calmly to the boss: "Boss, please wait for me for a minute, I will think about it." "then I opened the EXCEL table quickly.

❶ insert a row named "grades", enter "60", and then insert a row named "name" next to it.

❷ input function expression:

= VLOOKUP (F20MagneIf ({1Magne0}, B20VOB31MagneA20VA31), 2JING0)

Well, I have replied to the leader, and I have time to explain the principle of this expression to all of you.

IF ({1Jing 0} indicates a condition, returns B20:B31 if the condition is true for 1, and returns A20:A31 if the condition is not true for 0. In this way, the order of the two columns is swapped and the reverse search is realized.

By the way, this expression is once and for all. If the boss asks me who scored 91, I only need to change 60 to 91 to get the corresponding answer. I don't need to re-edit the function. The boss wants to find out who got how many points on the test.

I looked at the time, the boss is about to go back to the office, I will not say more, otherwise the boss will say that I touch the fish to deduct my salary.

For those of you who have read this article, you should have a preliminary understanding of the IF function, so in addition to VLOOKUP+IF can achieve reverse search, you also know which functions can achieve reverse search?

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

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.