Go to an article on Excel Logical Functions

Today, I used the excel if function to use nesting. I found this article on the Internet, which is comprehensive.

Editor's note: Excel
It is a very important software in office automation. Many giant international companies rely on Excel for data management. It not only facilitates table processing and graphic analysis, but also provides more powerful functions.
In the automatic processing and computing of data, however, it is difficult for many people who lack the background of science and engineering or do not know the powerful data processing functions of Excel. The editor thinks that excel functions cannot be used.
The solution is to block ordinary users from completely mastering excel. However, this teaching article is rare, so I specially organized this "Excel function application" series, hoping
It is helpful for advanced Excel learners. The EXCEL function application series will be updated every week. We will gradually introduce various Excel functions and Their Applications. Stay tuned!

A logical function is an Excel function used to determine the true or false values or perform a composite test. Six logical functions are provided in Excel. That is, the and, or, not, false, if, and true functions.

I. And, or, not Functions

All three functions are used to return the logical value of the parameter. For details, see:

(1) and Function

True is returned when the logical values of all parameters are true. If the logical values of a parameter are false, false is returned. In short, when all and parameters meet a certain condition, the returned result is true; otherwise, the returned result is false.

The syntax is and (logical1, logical ,...), logical1, logical ,... 1 to 30 condition values to be detected. The value of each condition may be true or false. The parameter must be a logical value or an array or reference containing the logical value. Example:

1. Enter the number 50 in cell B2, and write the formula = and (B2> 30, b2 <60) in cell C2 ). Because B2 is equal to 50, it is indeed greater than 30, less than 60. Therefore, if both condition values (logical) are true, true is returned.

　
Figure 1 and function Example 1

2. If the values in the B1-B3 cell are true, false, true, the three parameters are obviously not true, so the formula = and (B1: B3) in cell B4 is equal to false

　
Figure 2 and function Example 2

(2) or functions

Or
A function indicates that in a parameter group, if the logical value of any parameter is true
True. The difference between it and the and function is that the and function requires that the logical values of all functions are true and the result is true. The or function can be true only if any one of them is true. For example, Example 2 above,
If the formula in cell B4 is set to = or (B1: B3), the result is true.

　
Figure 3 or function example

Excel Function Series

● Mathematical and trigonometric functions of Excel Functions ● Introduction to excel Functions

(3) Not Function

The not function is used to reverse the parameter value. When you want to ensure that a value is not equal to a specific value, you can use the not function. In short, when the parameter value is true, the result returned by the not function is the opposite, and the result is false.
For example, not (2 + 2 = 4), because the result of 2 + 2 is indeed 4, the result of this parameter is true. Because it is a not function, the returned function result is the opposite, is false.

2. Functions true and false

The true and false functions are used to return the logical values of parameters. Because you can directly Type a value of true or false in a cell or formula. Therefore, these two functions are usually not used.

Iii. If Functions

(1) If Function Description

The IF function returns different results based on the true and false values of the logical test. Therefore, the IF function is also called a conditional function. It is widely used and can use the function if to perform conditional detection on values and formulas.

Its syntax is if (logical_test, value_if_true, value_if_false ). Logical_test indicates any value or expression in which the calculation result is true or false. This parameter can use any comparison operator.

Value_if_true
The value returned when logical_test is true. value_if_true can also be another formula. Value_if_false
The value returned when logical_test is false. Value_if_false can also be another formula.

In short, if the first parameter
If the result returned by logical_test is true, the result of the second parameter value_if_true is executed. Otherwise, the result of the third parameter value_if_false is executed.
Result. The IF function can be nested with seven layers. The value_if_false and value_if_true parameters can be used to construct complex detection conditions.

Excel also provides other functions that can analyze data based on certain conditions. For example, to calculate the number of times a text string or number appears in a cell area, you can use the countif worksheet function. To sum a text string or number in the cell area, you can use the sumif worksheet function.

(2) If Function Application

1. Output a blank form with a formula

　
Figure 5 personnel analysis table 1

The personnel analysis table shown in the figure is used as an example. The formula in the total column (cell G5 is used as an example) is as follows:

= Sum (C5: F5)

Me
The result is 0. If such a table is printed, the display is not satisfactory. Is there a way to remove 0 from the total column? You may say that you don't need to write a formula. Of course
This is a method, but if we use the IF function, we can also write the formula without displaying these 0 values. How to implement it? You only need to set the formula in the total column (cell G5 is used as an example)
Rewrite:

= If (sum (C5: F5), sum (C5: F5 ),"")

The general explanation is: If sum (C5: F5) is not equal to zero, the result of sum (C5: F5) is displayed in the cell; otherwise, the string is displayed.

Notes:

(1) sum (C5: F5) is not equal to zero. The regular syntax is sum (C5: F5) <> 0, which can be omitted in Excel <> 0;

(2) "" indicates that the content of the string is null. Therefore, the execution result is that no characters are displayed in the cell.

　
Figure 4

2. Different Conditions Return different results

If we have a good understanding of the above examples, we can easily apply the IF function to a wider range of fields. For example, in the score table, pass and fail are distinguished based on different scores. Now we will take the English score of a class as an example to describe the usage.

　
Figure 6

As shown in score 6 of a class, in order to make a final comprehensive evaluation, we set a rule to judge whether the score of the student is equal to the average score. If the average score of each subject exceeds 60, the score is deemed to be qualified. Otherwise, the score is recorded as unqualified.

According to this rule, we write a formula in the Comprehensive Evaluation (taking cell B12 as an example ):

= If (B11> 60, "qualified", "unqualified ")

Syntax: if the value of cell B11 is greater than 60, run the second parameter to display the qualified words in cell B12, otherwise, run the third parameter to display the unqualified words in cell B12.

In the comprehensive evaluation column, we can see that the average score of all subjects in Column C is 54, and the overall evaluation is unqualified. All others are qualified.

3. Application of Multi-layer nested Functions

In
In the above example, we simply divide the score into "pass" and "fail". In practice, the score usually has multiple levels, for example, excellent, good, medium, pass, or fail. Is there a way to differentiate them all at once? Yes
It is implemented by using multi-layer nesting. For example, when the average score of each subject is over 90, the overall evaluation rule is set to "excellent. 7.

　
Figure 7

Note: For ease of interpretation, we only provide two-fold nested examples here. You can perform more nesting based on the actual situation, however, note that the IF function of Excel allows up to seven duplicates of nesting.

According to this rule, we write a formula in the Comprehensive Evaluation (taking cell F12 as an example ):

= If (F11> 60, if (and (F11> 90), "excellent", "qualified"), "unqualified ")

Language
If the cell F11 value is greater than 60, run the second parameter. Here it is a nested function, continue to judge whether the value of cell F11 is greater than 90 (to let everyone know about the and Function
Application, written as and (F11> 90), can actually only write F11> 90). If the cell F12 contains excellent words, the qualified words are not displayed. If the cell F11 contains
If none of the above conditions are met, execute the third parameter to display the unqualified words in cell F12.

In the comprehensive evaluation column, we can see that the average score of all subjects in column F is 92, and the overall evaluation is excellent.

(3) Calculate values based on conditions

In
After learning how to use the IF function, let's take a look at other functions provided by similar Excel that can analyze data according to certain conditions. For example, if you want to calculate a text string or number in the cell area
You can use the countif worksheet function. To sum a text string or number in the cell area, use the sumif
Worksheet function. Sumif functions are described in detail in mathematics and trigonometric functions. Here we will focus on the countif application.

Countif can be used to calculate the number of cells that meet specific conditions in a given area. For example, in the score table, calculate the number of courses that each student has excellent scores. Find the number of employees whose basic salary is above 2000 yuan in the payroll.

Language
The format is countif (range, criteria ). Range is the area where the number of cells that meet the conditions needs to be calculated. Criteria determines which tickets
A condition in the form of a number, expression, or text. For example, a condition can be expressed as 32, "32", "> 32", or "Apples ".

1. Orders table

Here, the examples of the tables above illustrate some application methods. We need to calculate the number of courses that each student has excellent scores. The rule is that the score is better than 90. 8

　
Figure 8

According to this rule, we write a formula in the number of excellent doors (cell B13 is used as an example ):

= Countif (B4: B10, "> 90 ")

The syntax is to calculate the range B4 to B10, that is, the number of cells in each score of Jarry with a value greater than 90.

In the "number of excellent doors" column, we can see that Jarry's number of excellent doors is two. You can also see it in turn.

2. sales performance table

The sales performance table may be a typical example of comprehensive use of IF, sumif, and countif. For example, you may want to calculate the order number of sales personnel, then summarize the sales volume of each sales person, and decide the bonus for each sales according to the total shipment volume.

Raw data table 9 (raw data is listed in the form of a sequential list, that is, by order number)

　
Figure 9 Original data table

According to Sales Personnel Summary Table 10

　
Figure 10 sales personnel summary table

The table shown in 10 uses function compute to automatically aggregate data. First, create a form style summarized by sales personnel ,. Then calculate the order number, total order amount, and sales bonus respectively.

(1) number of orders-countif is used to calculate the number of orders of sales personnel.

Take the order number formula of salesperson Annie as an example. Formula:

= Countif ($ C $2: $ C $13, A17)

The syntax is interpreted as the calculated cell A17 (sales staff Annie) is within the range of "sales staff" list $ C $2: $ C $13 (that is, the original data table shown in Figure 9) the number of occurrences.

The number of times this appears can be considered as the order number of the salesperson Annie.

(2) Total orders-use sumif to summarize the sales volume of each salesperson.

Take the order amount formula of salesperson Annie as an example. Formula:

= Sumif ($ C $2: $ C $13, A17, $ B $2: $ B $13)

In the "salesperson" list $ C $2: $ C $13, this formula checks the text (sales staff Annie) in cell A17 ), then calculate the sum of the corresponding amount in the "Order amount" Column ($ B $2: $ B $13.

The sum of the corresponding quantity is the total order amount of sales personnel Annie.

(3) sales bonus: use if to decide the bonus to be obtained for each sale based on the total order amount.

Assume that the company's sales bonus rule is that when the total order amount exceeds 50 thousand yuan, the reward range is 15%, otherwise it is 10%. According to this rule, sales staff Annie is still used as an example. Formula:

= If (C17 <50000,10%, 15%) * C17

If the total order amount is less than 50000, the bonus is 10%; if the total order amount is greater than or equal to 50000, the bonus is 15%.

So far, we have fully understood the logic functions of Excel functions. I believe that you will come up with more useful functions in your practical work.