Hello friends, today let's talk about a function that looks very simple, but is very useful - IF.
If a sister asks you, how are you going to spend this weekend?
You will say: It depends on the weather, different weather, my choice is different.
Each contains an "what if...... So...... Otherwise, the sentence ......" can be translated into Excel's "language" by using the IF function in Excel's formula, and rewritten into Excel's "formula", that is:
IF (Is it sunny on weekends?) Go on an outing with friends, go to a bookstore to read)
Of course, this is not a correct Excel formula, but it is the logic of the IF function to think and deal with the problem. In this "formula", the IF function plays the role of "if...... So...... Otherwise...... the function of the group of related words, and Excel handles the process of the IF function as if you were choosing a road at a fork in the road.
Each IF function is like a fork in the road. Every time the formula reaches the fork in the IF, it will judge the set conditions, and then decide the direction of the next step according to whether the judgment result is TRUE or FALSE.
When you need to choose one of the many results, you can let the IF function do it for you.
For example, there are only two grades for a student in B2: "Pass" and "Fail". The criteria for evaluation are: if the score reaches 60 points, then it is assessed as passing, otherwise it is assessed as failing.
If you want the IF function to solve this problem for you, the formula can be written as:
Once Excel receives the formula you entered, it will grade the score according to the criteria you specified.
Whether in language or text, when describing a problem, attention should be paid to word order. What should be after "if" and "then" is not arbitrary, otherwise it will affect the expression effect.
"If" is followed by the condition to be judged, "then" is followed by the result returned when the condition is true, and "otherwise" is followed by the result returned when the condition is not true. Only by setting the parameters of the IF according to this rule will Excel understand your intentions.
There are 3 parameters in IF, each parameter plays a different role, and Excel will understand your intention only if the parameters are set correctly.
The role and function of a single function are relatively simple, and when solving practical problems, multiple functions are often required to be nested. Familiar with the use of these nested functions, it is still possible to improve efficiency in a single way.
As shown in the figure below, the score in cell B2 should be judged, and greater than 60 is "qualified", otherwise it is "unqualified".
Let's use the following formula:
=IF(B2>60,"合格","不合格")
Note that when returning characters directly in a formula, you need to precede and end the characters with a pair of half-width double quotation marks.
IF B2 IS GREATER THAN 60, B2>60 RETURNS THE LOGICAL VALUE TRUE, AND THE IF FUNCTION RETURNS THE CHARACTER SPECIFIED BY THE SECOND PARAMETER "QUALIFIED", OTHERWISE IT RETURNS THE CHARACTER SPECIFIED IN THE THIRD PARAMETER "UNQUALIFIED".
This is the judgment of a single condition, and the IF function can also be used to complete the judgment of multiple conditions.
As shown in the figure below, if the position in column B is judged, if it is equal to "secretary", then the subsidy is 1000. If it is not equal to "secretary", then continue to judge, if it is equal to "assistant", then the subsidy is 800, otherwise it is 0.
As we mentioned earlier, the second and third parameters of the IF function can be numeric values, words, or other formulas.
Here we will write the third parameter of the IF function as a new IF function, so that the position of column B can be judged twice.
=IF(B2="秘书",1000,IF(B2="助理",800,0))
The outermost IF function first determines whether the return result of B2="Secretary" is TRUE, and if it is TRUE, it returns the value specified by the second parameter 1000.
If B2 is not equal to the secretary, "B2="secretary"" gets FALSE, and the IF function returns the contents of the third argument.
What are the third arguments of the outermost IF function?
That's right, it's the part of the marker color IF(B2="assistant",800,0).
When the judgment result of "B2="Secretary" is FALSE, this part of the marker color starts to work, first to determine whether the condition of "B2="Assistant"" is valid, if this part of the condition is true, then return the second parameter of the IF function 800, otherwise return the third parameter of the IF function 0.
As shown in the figure below, if the position in column B is a secretary, and the employment period in column C is greater than 1, the subsidy is 1,000 yuan, otherwise the subsidy is 0.
Here we need to judge whether the two columns of conditions are met at the same time, the IF function is limited, and we need to ask the Tianshan Eryan - AND and OR functions to help.
Both the AND and OR functions perform judgments on the logical values returned by multiple parameters.
The differences are:
The AND function returns TRUE only when all parameters return a logical value of TRUE.
The OR function returns the logical value TRUE when the logical value returned by any parameter is TRUE.
Just like in the figure below, are the 4 nurses (parameters) judging that the blood routine is normal? Is the ECG normal? Is liver function normal? Is ultrasound normal?
When the judgment of each nurse (parameter) is TRUE, the AND doctor will finally conclude that the test is OK (TRUE), normal.
In the figure below, the 4 nurses (parameters) each judged that there was a problem with the blood routine? Is there a problem with the ECG? Is there a problem with liver function? Is there a problem with B-ultrasound?
When any nurse (parameter) judges that it is TRUE, the OR doctor will conclude that it is OK (TRUE) and go through the hospitalization procedures.
Back to the question at the beginning, we have to judge the two conditions of the position and the number of years of entry, only when the judgment result of the position = "secretary" is TRUE, and the judgment result of the entry period >1 is also TURE, the subsidy of 1000 yuan will be returned, which doctor should be used here? That's right, it's AND.
Enter the following formula into D2 cell, copy down to D8.
=IF(AND(B2="秘书",C2>1),1000,0)
TAKE THE FORMULA IN CELL D2 AS AN EXAMPLE, B2="SECRETARY" RETURNS FALSE, C2>1 RETURNS TRUE, AND THE AND FUNCTION FINALLY RETURNS FALSE. The IF function is further judged based on the result of the AND function, and finally returns the third parameter 0.
If we change the judging criteria, if the position = "secretary", or the number of years of employment > 1, a subsidy of 1,000 yuan will be given, and only if both items are not met, the subsidy is 0.
The judgment requirement here is that one of several conditions is met, as long as the AND in the above formula is replaced with OR.
=IF(OR(B2="秘书",C2>1),1000,0)
TAKE THE FORMULA IN CELL D2 AS AN EXAMPLE, B2="SECRETARY" RETURNS A RESULT THAT IS FALSE, C2>1 RETURNS A RESULT THAT IS TRUE, AND THE OR FUNCTION RETURNS TRUE AT THE END. The IF function is further judged based on the result of the OR function, and finally returns the second parameter 1000.
Let's talk about the problem of judging by multiple ranges.
As shown in the figure below, it is judged based on the monthly income in column B.
The criteria are:
A monthly income of less than 10,000 is considered poor;
A monthly income of 10,000 to 50,000 is subsistence type;
A monthly income greater than 50,000 is considered to be well-off.
In order to make multiple rounds of judgment on the value of column B, it is necessary to use the nesting of multiple IF functions.
Let's sort out the previous judgment criteria first, first judge whether B2 is less than 10,000, and return to "poverty" if the condition is established; If B2 is not less than 10,000, then continue to judge whether B2 is less than 50,000, and return "subsistence type" if the condition is true; If the conditions are still not true, it is those with a monthly income greater than 50,000, and they will return to the "well-off type".
Replace the above sentences with a combination of IF functions, which looks like this:
=IF(B2<10000,"贫困型",IF(B2<50000,"温饱型","小康型"))
The IF function in red is the third parameter of the previous IF function. The conditions of the blue part are judged first, and then the red part is judged if the conditions of the blue part are not true.
When performing multi-interval judgments, it is important to note that the preceding judgment conditions cannot include the later judgment conditions.
If you write the formula like this, and then look at the results, then you will all get rid of poverty.
=IF(B2<50000,"温饱型",IF(B2<10000,"贫困型","小康型"))
Why is this happening?
Take cell B3 as an example, if you judge 2500<50000 first, the condition must be true, then the IF function returns the second parameter "food and clothing type", and the later ones are no longer judged.
Just now we started looking for the back from the first car, in fact, we can also look forward from the last car.
The following formula is to start with the highest standard and then work your way down.
=IF(B2>=50000,"小康型",IF(B2>=10000,"温饱型","贫困型"))
Finally, I would like to add a place where friends are often prone to problems, such as judging whether the value of B2 is between 10,000 and 50,000, and return "food and clothing" if the conditions are met, and "to be investigated" if the conditions are not met.
The following formula is obviously incorrect:
=IF(10000<B2<50000,"温饱型","待考察")
Why the miscalculation?
Actually, it's not IF's fault, it's our writing that has a problem.
In the above formula, the first parameter of the IF function is 10000<B2<50000, and the operation process is as follows:
FIRST JUDGE 10,000 < B2, AND THEN USE TRUE TO COMPARE IT WITH 50,000. According to the unspoken rule in Excel, the logical value is greater than any value, so this step will return the logical value FALSE, and the IF function will eventually return the content of the third parameter.
The correct formula should look like this:
=IF(AND(10000<B2,B2<50000),"温饱型","待考察")
Or:
=IF(AND(B2>10000,B2<50000),"温饱型","待考察")
In addition to the above basic content, the IF function function is also widely used in array formulas, which we will save for later.
Well, that's all for today, I wish you all a good day!
Graphic production: Luo Guofa Zhu Hongzhong