First, truth.
Function: Returns the logical value TRUE.
Objective: To judge whether two values are equal.
Method:
Enter the formula in the target cell: =B3=C3. If TRUE is returned, it means that the values of cells B3 and C3 are the same.
Second, falsehood.
Function: Returns the logical value FALSE.
Objective: To judge whether two values are unequal.
Method:
Enter the formula in the target cell: =B3=C3. If FALSE is returned, B3 and C3 have different values.
Third, the application skills of truth (falsehood).
Objective: To calculate the number of the same data in two columns.
Method:
Enter the formula =SUM((B3:B9=C3:C9)* 1) in the target cell and fill it with Ctrl+Shift+Enter.
Explanation:
1. In logical values, TRUE has the same effect as 1, and FALSE has the same effect as 0. In the formula, it is first judged whether the values of column B and column C are equal. If they are equal, return TRUE, that is, 1, otherwise return FALSE, that is, 0, and then sum.
2. If you want to calculate the number of different data in two columns, you only need to modify the formula to = sum ((B3: B9
Fourth, no.
Function: negate the logical value.
Grammatical structure: =Not (condition or logical value).
Precautions:
1. If the logical value is false, the Not function will return true; If the logical value is TRUE, the Not function will return FALSE.
2. If the parameter of the Not function is text number or text, the Not function will return the error value "#VALUE!" .
Objective: To calculate the number of values in a region.
Method:
Enter the formula = sum (not (iserror (not (E3: e12)) *1) in the target cell and fill it with Ctrl+Shift+Enter.
Explanation:
1 and Iserror are used to detect whether a value is wrong, and return TRUE if it is, and FALSE otherwise.
2. When the parameter of the Not function contains a non-logical value, it will return an error value, so the formula ISERROR(NOT(E3:E 12)) returns TRUE for cells containing non-numbers, and FALSE for cells containing numbers. Then the Not function is used to invert the array formula containing logical values returned by the Iserror function, and the obtained array is × 1, that is, the logical values are converted into numbers.
5. and.
Function: judge whether multiple conditions are established at the same time.
Grammatical structure: =And (condition 1, [condition 2], [condition 3] ...).
Precautions:
1. All parameters can be true or false logical values, or can be converted into logical expressions.
2. If the parameter is text number or text, the And function will return the error value "#Value".
Objective: To judge whether employees are retired (male, 55 years old, female, 50 years old).
Method:
Input formula = if (and (C3 > 55, D3= "male"), "retired", if (and (C3 >; 50, D3= "female"), "retired", ").
Explanation:
First of all, Judge C3 & gt55, D3= whether "male" is established at the same time, if so, directly return to "retirement", if not, continue to judge C3 >；; 50, D3= whether "female" is established, if it is established, it will return "retired", otherwise it will return null.
6. or.
Function: judge whether at least one of multiple conditions holds.
Grammatical structure: =Or (condition 1, [condition 2], [condition 3] ...)
Precautions:
1. All parameters can be true or false logical values, or can be converted into logical expressions.
2. If the parameter is text number Or text, the OR function will return the error value "#Value".
Objective: If the "age" is less than 35 years old or the "education" is "big", it is "qualified".
Method:
Enter the formula in the target cell: = if (or (C3
Explanation:
As long as one of the conditions of the Or function holds, it returns True.
Seven, XOR.
Function: Judge whether one of multiple conditions holds.
Grammatical structure: =Xor (condition 1, [condition 2], [condition 3] ...).
Precautions:
1. If all the tested conditions are true or false, the Xor function returns false, otherwise it returns true.
2. If the parameter is text number or text, the Xor function will return the error value "#Value".
Objectivity: "Age" is less than 35, "education" is "big capital", as long as one condition is met, it is "qualified", and both conditions are met, it is "unqualified".
Method:
Enter: = if (xor (C3
Explanation:
Formula = If (or (C3
Eight, if.
Function: Return different results according to the condition judgment.
Syntax structure: =If (condition, return value when condition is true, return time when condition is false).
Remarks:
It was covered in the last article, so I won't repeat it here.
Ifna。
Function: judge whether there is #N/A error in the formula.
Syntax structure: =Ifna (value or expression, information to be output when value or expression returns #N/A error).
Objective: To inquire the "monthly salary" of employees. If no information is found, "Any information about this employee" will be returned.
Method:
Enter the formula = ifna (vlookup (B3 J3: g12,6,0), "No employee information") in the target cell.
Explanation:
Because there is no permission in the data source, the Vlookup function returns #N/A, and the Ifna function returns "No employee information" after judgment.
Ten, Iferror.
Function: Return different contents according to formula results.
Syntax structure: =Iferror (information to be output when there is an error in a value or expression).
Precautions:
Errors returned by values or expressions can be: #N/A, #VALUE, #REF! 、DIV/0！ 、NUM！ , #NAME or #NULL! .
Objective: To inquire the "monthly salary" of employees. If no information is found, "Any information about this employee" will be returned.
Method:
Enter the formula = if error (vlookup (B3 J3: g12,6,0), "No employee information") in the target cell.
@Excel function formula
#Excel function formula #
# Talent in the workplace is tempered #
# Professionals say #