我在公式中经常使用的一个Excel函数是(Excel)IF函数。IF函数用于测试逻辑条件并根据逻辑条件返回 TRUE 或 FALSE 产生两种不同(TRUE)的结果(FALSE)。
下面以手机销量表为例。您可以在此处下载示例文件。
具有单一条件的 IF 函数(IF Function
with Single Condition)
考虑一个场景，您需要计算每个销售行的佣金费用(Commission Fee)，具体取决于销售地点（D 列(Column D)）。如果在美国(USA)进行销售，佣金(Commission Fee)为 10%，否则其余地点将收取 5% 的佣金(Commission Fee)。
您需要在单元格 F2(Cell F2)上输入的第一个公式如下所示：
=IF(D2="USA", E2*10%, E2*5%)
公式分解：
- =IF( - “=”表示单元格中公式的开头，IF是我们正在使用的excel函数。
- D2=”USA” –我们执行的逻辑(Logical)测试（即如果D2列中的数据是USA）。
- E2*10% –如果初始逻辑测试结果为(Result)TRUE，则公式将返回的结果（即D2列中的值为USA）。
- E2*5% –如果初始逻辑测试结果为(Result)FALSE（即D2列中的值为NOT USA），公式将返回的结果。
- ) –右括号表示公式的结束(Closing)。
然后，您可以将单元格 F2(Cell
F2)中的公式复制到F(Column
F)列中的其余行，它将计算每行的佣金费用，根据(Commission
Fee)IF逻辑测试是在每行上返回TRUE还是FALSE，按 10% 或 5%计算排。
具有多个条件的 IF 函数(IF Function with Multiple Conditions)
如果规则稍微复杂一点，您需要测试多个逻辑条件并为每个条件返回不同的结果，该怎么办？
Excel对此有答案！我们可以在同一个单元格中组合多个IF函数，这有时被称为嵌套 IF(Nested IF)。
考虑一个类似的场景，其中每个销售地点的(Sales Location)佣金(Commissions)不同，如下所示：
- 美国(USA)10%
- 澳大利亚(Australia)5%
- 新加坡(Singapore)2%
在单元格 F2(Cell F2)（稍后将复制到同一列 F 中的其余行）中，输入公式如下：
=IF(D2="USA",E2*10%,IF(D2="Australia",E2*5%,E2*2%))
公式分解：
- =IF( –使用 IF 语句的公式开头(Beginning)
- D2=”USA” –我们执行的第一个(First)逻辑测试（即，如果D2列中的数据是USA）。
- E2*10% –如果初始逻辑测试结果为(Result)TRUE，则公式将返回的结果（即D2列中的值为USA）。
- IF(D2=”Australia”,E2*5%,E2*2%) – 第二个Excel IF语句，如果初始逻辑测试结果为(Excel IF)FALSE（即 D2 列中的值不是(NOT) USA ） ，则将对其进行评估。这与本文前面讨论的“具有单一条件的 IF 函数(IF Function with Single Condition”)”的语法类似，如果单元格(Cell) D2上的值为澳大利亚(Australia)，则将返回E2*5%否则，如果该值不是Australia，则该函数将返回E2*2%.
- ) –右括号指示第一个IF函数的公式结束。(Closing)
由于Excel将从左到右评估公式，当满足逻辑测试时（例如D2=“USA”,函数将停止并返回结果，忽略之后的任何进一步逻辑测试（例如D2=“Australia”。 )
因此，如果第一个逻辑测试返回FALSE（即 location 不是USA），它将继续评估第二个逻辑测试。如果第二个逻辑测试也返回FALSE（即 location 不是Australia），我们不需要进一步测试，因为我们知道Cell D2上唯一可能的值是Singapore因此它应该返回E2*2%的结果。
如果您希望清楚起见，可以添加第三个逻辑测试IF(D2=”Singapore”, “value if TRUE” , “value if FALSE”)。因此，完整的扩展公式如下所示：
=IF(D2="USA",E2*10%,IF(D2="Australia",E2*5%,IF(D2="Singapore",E2*2%)))
如前所述，上面将返回与我们最初的公式相同的结果。
=IF(D2="USA",E2*10%,IF(D2="Australia",E2*5%,E2*2%))
快速提示(Quick Tips)
- 对于每个单独的IF(函数，都需要有一个左圆括号和右圆括号。当按照上述示例之一存在三个IF函数时，公式将需要三个右括号(IF)“)))”，每个都标记结束相应的开场IF(语句。
- 如果我们不指定逻辑测试的第二个结果（当逻辑测试结果为FALSE时）， (FALSE)Excel分配的默认值将是文本“FALSE”。因此，如果D2不是“USA ”，公式=IF(D2=”USA”,E2*10%)将返回文本“FALSE ” 。
- 如果你有几个不同的逻辑测试，每个测试都有不同的结果，你可以多次组合/嵌套IF函数，一个接一个，类似于上面的例子。(IF)
How to Use If and Nested If Statements in Excel
One Excel function that I use quite a bit in my formulas is the IF function. The IF function is used to test a logical condition and produce two different results depending on whether the logical condition returns TRUE or FALSE.
Let’s use the mobile phone sales table below as an example. You can download the example file here.
IF Function
with Single Condition
Consider a scenario where you need to calculate the Commission Fee for each sales row, depending on where the sales was made (Column D). If the sales was made in the USA, the Commission Fee is 10%, otherwise the remaining locations will have Commission Fee of 5%.
The first formula that you need to enter on Cell F2 is as shown below:
=IF(D2="USA", E2*10%, E2*5%)
Formula
breakdown:
- =IF( – The “=” indicates the beginning of a formula in the cell and IF is the excel function that we are using.
- D2=”USA” – Logical test that we perform (i.e. if data in column D2 is USA).
- E2*10% – Result that will be returned by the formula if the initial logical test results in TRUE (i.e. value in column D2 is USA).
- E2*5% – Result that will be returned by the formula if the initial logical test results in FALSE (i.e. value in column D2 is NOT USA).
- ) – Closing bracket indicating the end of the formula.
Then
you can copy down the formula from Cell
F2 to the rest of the rows in Column
F and it will calculate the Commission
Fee for each line, either by 10% or 5% dependent on whether the IF logical test returns TRUE or FALSE on each row.
IF Function with Multiple Conditions
What if the rules were a bit more complicated where you need to test for more than one logical condition with different results being returned for each condition?
Excel has an answer to this! We can combine multiple IF functions within the same cell, which is sometimes known as a Nested IF.
Consider a similar
scenario where the Commissions are
different for each Sales Location as
below:
- USA 10%
- Australia 5%
- Singapore 2%
In Cell F2 (which later will be copied to the rest of the rows in the same column F), enter the formula as follow:
=IF(D2="USA",E2*10%,IF(D2="Australia",E2*5%,E2*2%))
Formula
breakdown:
- =IF( – Beginning of the formula using an IF statement
- D2=”USA” – First logical test that we perform (i.e. if data in column D2 is USA).
- E2*10% – Result that will be returned by the formula if the initial logical test results in TRUE (i.e. value in column D2 is USA).
- IF(D2=”Australia”,E2*5%,E2*2%) – second Excel IF statement that will be assessed if the initial logical test resulted in FALSE (i.e. value in column D2 is NOT USA). This is a similar syntax of “IF Function with Single Condition” discussed earlier in this article where if value on Cell D2 is Australia, the result of E2*5% will be returned. Otherwise, if the value is not Australia, the function will return result of E2*2%.
- ) – Closing bracket indicating the end of the formula for the first IF function.
As Excel will assess the formula from the left to the right, when a logical test is met (e.g. D2=“USA”, the function will stop and return the result, ignoring any further logical test after (e.g. D2=“Australia”.)
So if the first logical test returns FALSE (i.e. location is not USA), it will continue to assess the second logical test. If the second logical test returns FALSE as well (i.e. location is not Australia), we do not need to test further as we know the only possible value on Cell D2 is Singapore hence it should return a result of E2*2%.
If you prefer for clarity, you can add the third logical test IF(D2=”Singapore”, “value if TRUE” , “value if FALSE”). Therefore, the full extended formula is as shown below:
=IF(D2="USA",E2*10%,IF(D2="Australia",E2*5%,IF(D2="Singapore",E2*2%)))
As
mentioned earlier, the above will return the same result as the initial formula
that we had.
=IF(D2="USA",E2*10%,IF(D2="Australia",E2*5%,E2*2%))
Quick Tips
- For every single IF( function, there needs to be an opening and closing round bracket. When there are three IF functions as per one of the examples above, the formula will need three closing brackets “)))”, each marking the ending of a corresponding opening IF( statement.
- If we do not specify the second outcome of the logical test (when the logical test resulted in FALSE), the default value assigned by Excel will be the text “FALSE”. So formula =IF(D2=”USA”,E2*10%) will return the text “FALSE” if D2 is not “USA”.
- If you have several different logical tests each with its own different outcome, you can combine/nest the IF function multiple times, one after another, similar to the example above.
