Logical functions are some of the most popular and useful in Excel. They can test values in other cells and perform actions dependent upon the result of the test. This helps us to automate tasks in our spreadsheets.
逻辑功能是Excel中最流行和有用的功能。 他们可以测试其他单元格中的值，并根据测试结果执行操作。 这有助于我们自动执行电子表格中的任务。
如何使用中频功能 (How to Use the IF Function)
The IF function is the main logical function in Excel and is, therefore, the one to understand first. It will appear numerous times throughout this article.
IF函数是Excel中的主要逻辑函数，因此是首先要理解的函数。 在本文中，它将多次出现。
Let’s have a look at the structure of the IF function, and then see some examples of its use.
让我们看一下IF函数的结构，然后看一些使用它的例子。
The IF function accepts 3 bits of information:
IF函数接受3位信息：
=IF(logical_test, [value_if_true], [value_if_false])
logical_test: This is the condition for the function to check.
logical_test：这是函数要检查的条件。
value_if_true: The action to perform if the condition is met, or is true.
value_if_true：如果条件满足或为true，则执行的操作。
value_if_false: The action to perform if the condition is not met, or is false.
value_if_false：如果不满足条件或为false，则执行的操作。
比较运算符与逻辑函数一起使用 (Comparison Operators to Use with Logical Functions)
When performing the logical test with cell values, you need to be familiar with the comparison operators. You can see a breakdown of these in the table below.
使用单元格值执行逻辑测试时，您需要熟悉比较运算符。 您可以在下表中查看这些细分。
Now let’s look at some examples of it in action.
现在，让我们来看一些实际的例子。
IF函数示例1：文本值 (IF Function Example 1: Text Values)
In this example, we want to test if a cell is equal to a specific phrase. The IF function is not case-sensitive so does not take upper and lower case letters into account.
在此示例中，我们要测试一个单元格是否等于特定短语。 IF函数不区分大小写，因此不考虑大小写字母。
The following formula is used in column C to display “No” if column B contains the text “Completed” and “Yes” if it contains anything else.
如果列B包含文本“已完成”，则在列C中使用以下公式显示“否”，如果包含其他内容，则使用“是”。
=IF(B2="Completed","No","Yes")
Although the IF function is not case sensitive, the text must be an exact match.
尽管IF函数不区分大小写，但文本必须完全匹配。
IF函数示例2：数值 (IF Function Example 2: Numeric Values)
The IF function is also great for comparing numeric values.
IF函数对于比较数字值也非常有用。
In the formula below we test if cell B2 contains a number greater than or equal to 75. If it does, then we display the word “Pass,” and if not the word “Fail.”
在下面的公式中，我们测试单元格B2是否包含大于或等于75的数字。如果是，则显示单词“ Pass”，如果不是，则显示单词“ Fail”。
=IF(B2>=75,"Pass","Fail")
The IF function is a lot more than just displaying different text on the result of a test. We can also use it to run different calculations.
IF功能不仅仅是在测试结果上显示不同的文本。 我们还可以使用它来运行不同的计算。
In this example, we want to give a 10% discount if the customer spends a certain amount of money. We will use £3,000 as an example.
在此示例中，如果客户花费一定金额，我们希望给予10％的折扣。 我们将以3,000英镑为例。
=IF(B2>=3000,B2*90%,B2)
The B2*90% part of the formula is a way that you can subtract 10% from the value in cell B2. There are many ways of doing this.
公式的B2 * 90％部分是一种可以从单元格B2中的值减去10％的方法。 有很多方法可以做到这一点。
What’s important is that you can use any formula in the value_if_true or value_if_false sections. And running different formulas dependent upon the values of other cells is a very powerful skill to have.
重要的是您可以在value_if_true或value_if_false部分中使用任何公式。 根据其他单元格的值运行不同的公式是一项非常强大的技能。
IF函数示例3：日期值 (IF Function Example 3: Date Values)
In this third example, we use the IF function to track a list of due dates. We want to display the word “Overdue” if the date in column B is in the past. But if the date is in the future, calculate the number of days until the due date.
在第三个示例中，我们使用IF函数来跟踪到期日期列表。 如果B列中的日期是过去的日期，我们想显示单词“过期”。 但是，如果该日期是将来的日期，请计算直到到期日的天数。
The formula below is used in column C. We check if the due date in cell B2 is less than today’s date (The TODAY function returns today’s date from the computer’s clock).
C列中使用下面的公式。我们检查单元格B2中的到期日期是否小于今天的日期(TODAY函数从计算机时钟返回今天的日期)。
=IF(B2<TODAY(),"Overdue",B2-TODAY())
什么是嵌套IF公式？ (What are Nested IF Formulas?)
You may have heard of the term nested IFs before. This means that we can write an IF function within another IF function. We may want to do this if we have more than two actions to perform.
您可能以前曾经听说过嵌套IF。 这意味着我们可以在另一个IF函数中编写一个IF函数。 如果我们要执行两个以上的操作，则可能需要这样做。
One IF function is capable of performing two actions (the value_if_true and value_if_false ). But if we embed (or nest) another IF function in the value_if_false section, then we can perform another action.
一个IF函数能够执行两个操作( value_if_true和value_if_false )。 但是，如果我们在value_if_false部分中嵌入(或嵌套)另一个IF函数，则可以执行其他操作。
Take this example where we want to display the word “Excellent” if the value in cell B2 is greater than or equal to 90, display “Good” if the value is greater than or equal to 75, and display “Poor” if anything else.
以这个示例为例，如果单元格B2中的值大于或等于90，则要显示单词“ Excellent”；如果值大于或等于75，则要显示“ Good”；如果其他值不超过，则显示“ Poor” 。
=IF(B2>=90,"Excellent",IF(B2>=75,"Good","Poor"))
We have now extended our formula to beyond what just one IF function can do. And you can nest more IF functions if necessary.
现在，我们将公式扩展到了仅一个IF函数所不能提供的范围。 如果需要，您可以嵌套更多的IF函数。
Notice the two closing brackets on the end of the formula—one for each IF function.
请注意公式末尾的两个右括号-每个IF函数一个。
There are alternative formulas that can be cleaner than this nested IF approach. One very useful alternative is the SWITCH function in Excel.
有一些替代公式比此嵌套IF方法更干净。 一种非常有用的替代方法是Excel中的SWITCH函数 。
AND和OR逻辑功能 (The AND and OR Logical Functions)
The AND and OR functions are used when you want to perform more than one comparison in your formula. The IF function alone can only handle one condition, or comparison.
当您要在公式中执行多个比较时，可以使用AND和OR函数。 单独的IF函数只能处理一种情况或比较。
Take an example where we discount a value by 10% dependent upon the amount a customer spends and how many years they have been a customer.
举一个例子，我们根据客户花费的金额和他们成为客户的年限，将价值折让10％。
On their own, the AND and OR functions will return the value of TRUE or FALSE.
AND和OR函数本身将返回TRUE或FALSE的值。
The AND function returns TRUE only if every condition is met, and otherwise returns FALSE. The OR function returns TRUE if one or all of the conditions are met, and returns FALSE only if no conditions are met.
仅当满足每个条件时，AND函数才返回TRUE，否则返回FALSE。 如果满足一个或所有条件，则OR函数返回TRUE，仅当不满足条件时才返回FALSE。
These functions can test up to 255 conditions, so are certainly not limited to just two conditions like is demonstrated here.
这些功能最多可以测试255个条件，因此当然不仅仅限于此处演示的两个条件。
Below is the structure of the AND and OR functions. They are written the same. Just substitute the name AND for OR. It is just their logic which is different.
以下是AND和OR函数的结构。 它们的写法相同。 只需将名称AND替换为OR。 只是他们的逻辑不同。
=AND(logical1, [logical2] ...)
Let’s see an example of both of them evaluating two conditions.
让我们看一个他们两个都评估两个条件的例子。
AND功能示例 (AND Function example)
The AND function is used below to test if the customer spends at least £3,000 and has been a customer for at least three years.
下面使用AND函数测试客户是否花费了至少3,000英镑并且已经成为客户至少三年。
=AND(B2>=3000,C2>=3)
You can see that it returns FALSE for Matt and Terry because although they both meet one of the criteria, they need to meet both with the AND function.
您会看到它为Matt和Terry返回FALSE，因为尽管它们都符合其中一个条件，但它们都需要同时使用AND函数。
或函数示例 (OR Function Example)
The OR function is used below to test if the customer spends at least £3,000 or has been a customer for at least three years.
下面使用“或”功能测试客户是否花费了至少3,000英镑或已经成为客户至少三年。
=OR(B2>=3000,C2>=3)
In this example, the formula returns TRUE for Matt and Terry. Only Julie and Gillian fail both conditions and return the value of FALSE.
在本示例中，该公式对Matt和Terry返回TRUE。 只有Julie和Gillian都不满足这两个条件，并返回FALSE的值。
在IF函数中使用AND和OR (Using AND and OR with the IF Function)
Because the AND and OR functions return the value of TRUE or FALSE when used alone, it’s rare to use them by themselves.
由于AND和OR函数单独使用时会返回TRUE或FALSE的值，因此很少单独使用它们。
Instead, you’ll typically use them with the IF function, or within an Excel feature such as Conditional Formatting or Data Validation to perform some retrospective action if the formula evaluates to TRUE.
取而代之的是，通常将它们与IF函数一起使用，或者在Excel功能(如条件格式或数据验证)中使用它们，以在公式计算为TRUE时执行某些追溯操作。
In the formula below, the AND function is nested inside the IF function’s logical test. If the AND function returns TRUE then 10% is discounted from the amount in column B; otherwise, no discount is given and the value in column B is repeated in column D.
在下面的公式中，AND函数嵌套在IF函数的逻辑测试中。 如果AND函数返回TRUE，则从B列的金额中减去10％； 否则，不给出折扣，并且B列中的值在D列中重复。
=IF(AND(B2>=3000,C2>=3),B2*90%,B2)
XOR功能 (The XOR Function)
In addition to the OR function, there is also an exclusive OR function. This is called the XOR function. The XOR function was introduced with the Excel 2013 version.
除了“或”功能外，还具有排他性的“或”功能。 这称为XOR函数。 XOR函数是在Excel 2013版本中引入的。
This function can take some effort to understand, so a practical example is shown.
该功能可能需要花一些力气才能理解，因此显示了一个实际示例。
The structure of the XOR function is the same as the OR function.
XOR函数的结构与OR函数的结构相同。
=XOR(logical1, [logical2] ...)
When evaluating just two conditions the XOR function returns:
仅评估两个条件时，XOR函数将返回：
- TRUE if either condition evaluates to TRUE. 如果任一条件的评估结果为TRUE，则为TRUE。
- FALSE if both conditions are TRUE, or neither condition is TRUE. 如果两个条件都为真，或者两个条件都不为真，则为FALSE。
This differs from the OR function because that would return TRUE if both conditions were TRUE.
这与OR函数不同，因为如果两个条件都为TRUE，它将返回TRUE。
This function gets a little more confusing when more conditions are added. Then the XOR function returns:
当添加更多条件时，此函数会更加混乱。 然后，XOR函数返回：
TRUE if an odd number of conditions return TRUE.
如果奇数个条件返回TRUE，则为TRUE。
FALSE if an even number of conditions result in TRUE, or if all conditions are FALSE.
如果偶数个条件得出TRUE，或者所有条件均为FALSE，则为FALSE。
Let’s look at a simple example of the XOR function.
让我们看一个XOR函数的简单示例。
In this example, sales are split over two halves of the year. If a salesperson sells £3,000 or more in both halves then they are assigned Gold standard. This is achieved with an AND function with IF like earlier in the article.
在此示例中，销售分成一年中的两半。 如果推销员两半都卖出3,000英镑或以上，那么他们将被分配为黄金标准。 如本文前面所述，可以使用IF的AND函数来实现。
But if they sell £3,000 or more in either half then we want to assign them Silver status. If they don’t sell £3,000 or more in both then nothing.
但是，如果他们卖出了£3,000或更高的一半，那么我们想为他们分配白银身份。 如果他们两人都没有卖出3,000英镑或以上，那么什么都没有。
The XOR function is perfect for this logic. The formula below is entered into column E and shows the XOR function with IF to display “Yes” or “No” only if either condition is met.
XOR函数非常适合此逻辑。 下面的公式输入到E列，并且仅在满足任一条件的情况下，将IF的XOR函数显示为“是”或“否”。
=IF(XOR(B2>=3000,C2>=3000),"Yes","No")
NOT功能 (The NOT Function)
The final logical function to discuss in this article is the NOT function, and we have left the simplest for last. Although sometimes it can be hard to see the ‘real world’ uses of the function at first.
本文讨论的最后一个逻辑功能是NOT函数，最后我们保留了最简单的功能。 尽管有时一开始可能很难看到该功能的“真实世界”使用。
The NOT function reverses the value of its argument. So if the logical value is TRUE, then it returns FALSE. And if the logical value is FALSE, it will return TRUE.
NOT函数反转其参数的值。 因此，如果逻辑值为TRUE，则返回FALSE。 如果逻辑值为FALSE，它将返回TRUE。
This will be easier to explain with some examples.
通过一些示例将更易于解释。
The structure of the NOT function is;
NOT函数的结构是；
=NOT(logical)
NOT功能示例1 (NOT Function Example 1)
In this example, imagine we have a head office in London and then many other regional sites. We want to display the word “Yes” if the site is anything except London, and “No” if it is London.
在此示例中，假设我们在伦敦设有总部，然后在其他许多地区设有办事处。 如果网站是伦敦以外的地方，我们想显示“是”，如果是伦敦，则要显示“否”。
The NOT function has been nested in the logical test of the IF function below to reverse the TRUE result.
NOT函数已嵌套在下面的IF函数的逻辑测试中，以反转TRUE结果。
=IF(NOT(B2="London"),"Yes","No")
This can also be achieved by using the NOT logical operator of <>. Below is an example.
这也可以通过使用<>的NOT逻辑运算符来实现。 下面是一个例子。
=IF(B2<>"London","Yes","No")
NOT功能示例2 (NOT Function Example 2)
The NOT function is useful when working with information functions in Excel. These are a group of functions in Excel that check something, and return TRUE if the check is a success, and FALSE if it is not.
在Excel中使用信息功能时，NOT功能非常有用。 这些是Excel中用于检查某些内容的一组函数，如果检查成功，则返回TRUE，否则返回FALSE。
For example, the ISTEXT function will check if a cell contains text and return TRUE if it does and FALSE if it does not. The NOT function is helpful because it can reverse the result of these functions.
例如，ISTEXT函数将检查单元格是否包含文本，如果包含则返回TRUE，否则返回FALSE。 NOT函数很有用，因为它可以反转这些函数的结果。
In the example below, we want to pay a salesperson 5% of the amount they upsell. But if they did not upsell anything, the word “None” is in the cell and this will produce an error in the formula.
在下面的示例中，我们要向销售人员支付加售额的5％。 但是，如果他们没有加价，则单元格中会出现“无”字样，这将在公式中产生错误。
The ISTEXT function is used to check for the presence of text. This returns TRUE if there is text, so the NOT function reverses this to FALSE. And the IF performs its calculation.
ISTEXT函数用于检查文本是否存在。 如果有文本，则返回TRUE，因此NOT函数将其反转为FALSE。 IF执行其计算。
=IF(NOT(ISTEXT(B2)),B2*5%,0)
Mastering logical functions will give you a big advantage as an Excel user. To be able to test and compare values in cells and perform different actions based on those results is very useful.
精通逻辑功能将为您带来一个Excel用户的巨大优势。 能够测试和比较单元格中的值并根据这些结果执行不同的操作非常有用。
This article has covered the best logical functions used today. Recent versions of Excel have seen the introduction of more functions added to this library, such as the XOR function mentioned in this article. Keeping up to date with these new additions will keep you ahead of the crowd.
本文介绍了当今使用的最佳逻辑功能。 Excel的最新版本引入了添加到该库的更多函数，例如本文中提到的XOR函数。 随时了解这些新添加的内容将使您在人群中保持领先。
翻译自: https://www.howtogeek.com/401998/how-to-use-logical-functions-in-excel-if-and-or-xor-not/
扫一扫
902
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
