The 5 usage methods of if function, many people are looking for it, let you get from entry to proficiency

Today is Excel is the second part of the common function collection. Let's learn the necessary function of another workplace -IF. Its importance is not enhanced to VLOOKUP. Next function

Learn Excel from zero, improve efficiency and do not work overtime

Here ↓↓

1. The role and parameters of if function

IF: It is a logical judgment function, which can return the corresponding results according to the right or wrong of the given conditions

Grammar: = if (logical_test, value_if_true, [value_if_false])

The first parameter: the condition of the judgment, the result is a logical value, only the True or False second parameter: the result returned when the condition is correct: the result when the condition is wrong is the role and parameter of the IF function. The function is still relatively simple.

Conventional usage

As shown in the figure below, we want to judge whether to pass according to the achievements. For such problems, we can use the IF function to solve

Formula: = if (b2> = 60, "pass", "failure")

The first parameter: B2> = 60, conditional expression, is also the condition of determining whether the score is passing: the grid, the value returned when the condition is correct: the value of the time is not the values ​​when the condition is wrong is if the value returned is if it is if it is if it is if it is if it is if it is if it is if the value of the condition is if the value returns is if it is if it is if it is if it is if it is if the value of the condition is if the value returns is if it is if it is if it is. The function of the function is conventional, but in actual work, we encounter judgment that it will not only have one, that is, there are multiple judgments, let's take a look at the solution method below

Third, multiple conditions are met at the same time

As shown in the figure below, we need to calculate the bonus based on the [assessment scoring] and [late times]. The current requirements are: the assessment score is greater than 85, and the number of late times is less than equal to one time. You can get bonuses at the same time to get the same conditions at the same time

Formula: = if ((b3> 85)*(c3 <= 1), 200,0)

First parameter: (B3> 85)*(C3 <= 1), Judgment Condition 2 parameter: 200 third parameter: 0

The key to this function is the first parameter (B3> 85)*(C3 <= 1) how to understand this formula. If you want to understand this form, we also need to understand 1 point: In Excel, we can treat logic value true as a Yes 1, FALSE is regarded as 0

(B3> 85) The meaning is that the assessment score is greater than 85 points (C3 <= 1) It means that the number of late times is less than equal to 1 time. A logical value, we can think of logic values ​​as 0 or 1, 0 and 1 multiplier only 1, and the result will return 1. As long as one result is 0 When you are correct, you will get the bonus

Only two conditions are listed here. If there are more conditions, you only need to multiply them.

Fourth, meet any 1 condition

The conditions for obtaining bonuses are now changed: the assessment score is greater than 85, or if the number of late times is less than equal to one time, you can receive a 200 yuan bonus, that is, these two conditions can get the bonus as long as one meets one.

Formula: = if ((B3> 85)+(C3 <= 1), 200,0)

这个公式跟上一个唯一的区别就是在【第一参数中】将【乘号】换成了【加号】，一串0跟1相加，只要有1个数值1，那么这个结果就是1， It means the condition is correct

Some people may be puzzled. If I have 3 conditions and two corrects and 1 error, then the result is 2. This is not a violation of the first parameter of the IF, isn't it that it can only be true or false?

This function will automatically identify. If the value is greater than 1, the value will be automatically set to 1, which means that the conditions are correct.

Five, interval judgment

The so -called interval judgment is the result of 1 value corresponding to 1 range. We need to judge in multiple interval to find the correct result that belongs to it

As shown in the figure below, we need to determine the corresponding level according to the assessment score, the corresponding interval of each level on the right

The formula is: = if (b6> = 85, "excellent", if (b6> = 75, "good", if (b6> = 60, "and" "," fail ")))

This formula can be regarded as nested in the IF function layer by layer. The next IF function is placed in the third parameter of the previous IF function. Nest

We will look at the results of the 55th generation formula

In the first layer IF, 55> = 85, the condition is wrong, and the third parameter of the first layer IF function will be returned. The third parameter is exactly the second layer IF function, so it will continue to take stock

In the second layer IF, 55> = 75, the conditions are still wrong, and the third parameter of the second layer IF will be returned. She happens to be the third layer IF function, and she will continue to judge down on the third layer ifIn, 55> = 60, the condition is still wrong, and the third parameter of the third layer IF will be returned.

The above is all the contents of the IF function. It is relatively simple. The relatively difficult thing is [interval judgment] If you are not understanding, just ignore it. In the futureIn order to solve the [interval judgment]

I am Excel from zero to one, follow me, and continue to share more Excel skills

- END -