几乎所有编程语言中最常用的语句之一是 IF 语句。因此，在Google 表格(Google Sheets)中使用 IF 和嵌套 IF 语句非常流行且非常有用也就不足为奇了。
Google 表格(Google Sheets)中的 IF 语句允许您根据其他单元格的条件将不同的函数插入到单元格中。嵌套 IF 语句时，您可以基于数据创建更高级的分析。在本文中，您将学习如何执行此操作并查看一些示例。
在Google表格中使用IF 语句(IF Statement)
在开始花哨并开始嵌套 IF 语句之前，您需要先了解简单的 IF 语句在Google 表格(Google Sheets)中的工作原理。
理解这一点的最简单方法是举个例子。对于我们的第一个示例，假设您从互联网上提取了您正在考虑徒步旅行的山脉列表，以及它们的海拔高度。
您只对海拔 3000 英尺以上的山脉进行远足感兴趣。因此，您创建了另一个名为“Over 3000 Feet”的列。IF 语句是填写此字段的最简单方法。
以下是 IF 语句的工作原理：
IF（逻辑表达式，value_if_true，value_if_false）(IF(logical_expression, value_if_true, value_if_false))
IF 语句中的参数工作如下：
- Logical_expression：这是一个使用 <、> 或 = 等运算符的条件语句。
- Value_if_true ：如果逻辑表达式为(Value_if_true)TRUE，则在单元格中输入您想要的内容。
- Value_if_false ：如果逻辑表达式为(Value_if_false)FALSE，则在单元格中输入您想要的内容。
在此示例中，从第一行开始并将光标放在单元格 C2 中。然后，键入以下公式：
=IF(B2>3000,”YES”,”NO”)
这个 IF 语句表示如果单元格 B2 中的山的高度大于 3000，则在单元格 C2 中显示YES，否则显示 NO。
按Enter 键(Enter)，您将在单元格 C2 中看到正确的结果 ( YES )。
您可能还会看到Google 表格(Google Sheets)建议自动填充此单元格下方的其余单元格。如果您想继续使用相同的功能自动填充其余单元格，请选中复选标记。参考将自动更新为这些山高的正确单元格。
如果您没有看到此建议，您可以按住键盘上的Shift键并双击输入第一个公式的单元格右下角的小方块。
在Google表格中使用嵌套的 IF语句(Statements)
现在让我们看一个在Google 表格(Google Sheets)中创建嵌套 IF 语句的示例。
使用与上面相同的示例，假设您想在列表中找到海拔不超过 3,000 英尺的第一座山，因此您选择它作为您的下一次“轻松”徒步旅行。要在列中从上到下搜索与条件匹配的下一个项目，需要嵌套的 IF 语句。
将光标放在您希望结果所在的单元格中。然后，键入以下公式：
=IF(B2<3000,A2,IF(B3<3000,A3,IF(B4<3000,A4,IF(B5<3000,A5,IF(B6<3000,A6,IF(B7<3000,A7,IF(B8<3000,A8,IF(B9<3000,A9,IF(B10<3000,A10,IF(B11<3000,A11,IF(B12<3000,A12,IF(B13<3000,A13,IF(B14<3000,A14,IF(B15<3000,A15,”None”))))))))))))))
看起来有点疯狂，不是吗？是的，嵌套的 IF 语句会变得复杂。让我们把它分解一下，这样它就更有意义了。
语句的第一部分 ( =IF(B2<3000 ) 检查列中的第一个单元格是否小于 3000。如果是，则这是列表中低于 3000 的第一座山，因此它将返回 A2 ( ,A2 ) 因为那个 IF 语句是真的。
如果为假，则需要嵌套另一个 IF 语句来检查下一列 ( ,IF(B3<3000 )。如果为真，则返回 A3 ( ,A3 )
您重复 IF 语句的这种嵌套，直到一直到A15，然后用重复的“）”字符关闭所有 if 语句。
您会注意到嵌套 IF 语句中的最后一个“false”参数是“None”。这是因为如果A15也不低于 3000 英尺，那么没有一座山低于 3000 英尺。
在此示例中，单元格 D2 中的结果如下所示。
专业提示(Pro-tip)：更简单的方法是使用INDEX、MATCH和VLOOKUP 函数(VLOOKUP functions)。
嵌套IF 语句(IF Statement)查找最大数(Highest Number)
在前面的示例中，IF 语句作为FALSE参数嵌套在它之前的 IF 语句中。嵌套 IF 语句的相反示例是将它们嵌套为TRUE参数。
您可以使用这种方法来查找列表中的最高数字。例如，假设您有一个学生列表和他们的考试成绩。您想使用嵌套的 IF 语句来查找最高等级。
将光标放在要放置结果的单元格中，然后键入以下公式：
=IF(B2>B3,IF(B2>B4,B2,IF(B4>B3,B4,B3)),B3)
语句的第一部分 ( =IF(B2>B3 ) 检查列中的第一个单元格是否大于第二个单元格。如果是，则该单元格 (B2) 可能是最大的，但您仍然需要检查休息。因此，代替TRUE参数，您将嵌套另一个 IF 语句，检查 B2 和 B4。
- 如果 B2 仍然大于 B4，则它是最大的数，您可以将 B2 作为下一个TRUE参数返回。
- 如果不是，B4 可能是最大的数字。所以FALSE参数需要检查 B4 和 B3。如果它更大，那么它是最大的数字，这个最终的 IF 语句将在TRUE参数中返回 B4。
- 如果不是，则 B3 是最大的，应作为最终的FALSE参数返回。
- 最后，如果第二次检查（B2>B4）为假，那么B3最大，因为第一个IF语句（B2>B3）已经为假，所以B3可以作为这个FALSE参数返回。
结果如下：
迷茫了吗？
你不是一个人。对这样的事情使用嵌套的 IF 语句非常复杂。一旦你将更多的数字添加到列表中，它就会变得更加复杂。
这就是为什么Google 表格(Google Sheets)实际上有一个MAX函数，您只需将单元格范围（在本例中为列）传递给它，它就会返回最大数量。还有一个MIN函数将返回最小值。
一个现实的嵌套 IF Google 表格示例(Realistic Nested IF Google Sheets Example)
前面的两个示例旨在向您展示，如果您确实不应该使用嵌套的 IF 语句，那么很容易陷入混乱。这是一个容易进入的陷阱。始终寻找更简单、单一的Google 表格(Google Sheets)功能来完成您想做的事情。
例如，假设您拥有一家公司，并且您收到了有关四名员工的反馈。根据您收到反馈的四个特征，您需要确定每个员工是否是促销材料。
您可以编写一个嵌套的 IF 语句来检查每个特征的答案，然后在结果列中提供一个决定。
如果员工不是：
- 准时(Punctual)：你不太在意，但你可能不会宣传（也许不会）。
- 高效(Efficient)：你不是太在意，并且可能仍然会推广（也许）。
- 领导质量(Leadership Quality)：如果您同意反馈（可能不同意），您可能不会晋升。
- 值得信赖(Trustworthy)：你绝对不想宣传（绝对不是）。
您可以将这些决策编程到嵌套的 IF 语句中。将光标置于需要结果的单元格中，然后键入以下公式：
=IF(B2=”YES”,IF(C2=”YES”,IF(D2=”YES”,IF(E2=”YES”,”Definitely”,”Maybe Not”),”Maybe”),”Maybe Not”),”Definitely Not”)
这是一个简单的嵌套 IF 语句，如果所有响应均为“是”，则返回“肯定”，但随后根据是否有任何单个单元格为“否”返回不同的答案。
这是嵌套 IF 语句是不错选择的少数示例之一。但如上所述，如果您需要做任何更复杂的事情，您最好寻找一个现有的Google 表格(Google Sheets)功能，它可以更轻松地完成相同的目标。
一些出色的高级“IF”函数示例包括SUMIF、COUNTIFS 、 SUMIFS 和 AVERAGEIFS(COUNTIFS, SUMIFS, and AVERAGEIFS)。
How to Use If and Nested If in Google Sheets
One of the most commonly usеd statements in nearly every programming languаge is the IF statement. So it should come as no surprise that using IF and nested IF ѕtatеments in Google Sheets is vеry popular and very useful.
The IF statement in Google Sheets lets you insert different functions into a cell-based on conditions from other cells. When you nest IF statements, you can create more advanced analytics based on data. In this article, you’ll learn how to do this as well as see a few examples.
Using the IF Statement in Google Sheets
Before you can get fancy and start nesting IF statements, you need to understand how a simple IF statement works in Google Sheets first.
The easiest way to understand this is with an example. For our first example, imagine you pulled a list of mountains you’re thinking of hiking from the internet, along with their altitude.
You’re only interested in hiking mountains that are higher than 3000 feet in altitude. So you create another column called “Over 3000 Feet”. An IF statement is the easiest way to fill out this field.
Here’s how an IF statement works:
IF(logical_expression, value_if_true, value_if_false)
The parameters in the IF statement work as follows:
- Logical_expression: This is a conditional statement using operators like <, >, or =.
- Value_if_true: Enter what you want in the cell if the logical expression is TRUE.
- Value_if_false: Enter what you want in the cell if the logical expression is FALSE.
In this example, start with the first row and place the cursor in cell C2. Then, type the following formula:
=IF(B2>3000,”YES”,”NO”)
This IF statement means if the height of the mountain in cell B2 is greater than 3000, then display YES in cell C2, otherwise display NO.
Press Enter and you’ll see the correct result (YES) in cell C2.
You may also see a Google Sheets suggestion to autofill the rest of the cells below this one. Select the checkmark if you’d like to go ahead and autofill the rest of the cells with this same function. The references will automatically update to the correct cells for those mountain heights.
If you don’t see this suggestion, you can hold the Shift key on your keyboard and double-click the small square at the lower right corner of the cell where you entered the first formula.
Using Nested IF Statements in Google Sheets
Now let’s look at an example of creating a nested IF statement in Google Sheets.
Using the same example as above, let’s say you want to find the first mountain in the list that isn’t over 3,000 feet in altitude so you choose it as your next “easy” hike. To search from top to bottom in a column for the next item that matches a condition requires a nested IF statement.
Place the cursor in the cell where you’d like the result to go. Then, type the following formula:
=IF(B2<3000,A2,IF(B3<3000,A3,IF(B4<3000,A4,IF(B5<3000,A5,IF(B6<3000,A6,IF(B7<3000,A7,IF(B8<3000,A8,IF(B9<3000,A9,IF(B10<3000,A10,IF(B11<3000,A11,IF(B12<3000,A12,IF(B13<3000,A13,IF(B14<3000,A14,IF(B15<3000,A15,”None”))))))))))))))
Looks a bit crazy, doesn’t it? Yes, nested IF statements can get complicated. Let’s break this one down so it makes more sense.
The first part of the statement (=IF(B2<3000) checks if the first cell in the column is less than 3000. If it is, then that’s the first mountain on the list under 3000 and so it will return A2 (,A2) since that IF statement is true.
If it’s false, then you need to nest another IF statement to check the next column (,IF(B3<3000). If this is true, return A3 (,A3)
You repeat this nesting of IF statements until you get all the way down to A15, and then close out all of the if statements with the repeated “)” character.
You’ll notice the last “false” parameter in the nested IF statement is “None”. This is because if A15 isn’t under 3000 feet either, then none of the mountains are under 3000 feet.
In this example, here’s what the result will look like in cell D2.
Pro-tip: A simpler way to do this is to use the INDEX, MATCH, and VLOOKUP functions.
Nested IF Statement to Find the Highest Number
In the previous example, the IF statements were nested as FALSE parameters inside the IF statement before it. An opposite example of nesting IF statements is nesting them as TRUE parameters.
You may use this approach to find the highest number in a list. For example, let’s say you have a list of students and their test grades. You want to use nested IF statements to find the highest grade.
Place the cursor in the cell where you want to place the result and type the following formula:
=IF(B2>B3,IF(B2>B4,B2,IF(B4>B3,B4,B3)),B3)
The first part of the statement (=IF(B2>B3) checks if the first cell in the column is greater than the second. If it is, then that cell (B2) may be the largest, but you still need to check the rest. So in place of the TRUE parameter, you’ll nest another IF statement checking B2 against B4.
- If B2 is still larger than B4, it’s the largest number and you can return B2 as the next TRUE parameter.
- If it isn’t, B4 could be the largest number. So the FALSE parameter needs to check B4 against B3. If it is larger, then it’s the largest number and this final IF statement will return B4 in the TRUE parameter.
- If it isn’t, then B3 is the largest and should be returned as the final FALSE parameter.
- Finally, if the second check (B2>B4) is false, then B3 is the largest because the first IF statement (B2>B3) is already false, so B3 can be returned as this FALSE parameter.
Here’s what the result looks like:
Confused yet?
You’re not alone. Using nested IF statements for something like this is pretty complicated. And once you add even more numbers to the list, it gets even more complex.
That’s why Google Sheets actually has a MAX function where you simply pass it the range of cells (in this case the column), and it’ll return the maximum number. There’s also a MIN function that will return the minimum value.
A Realistic Nested IF Google Sheets Example
The previous two examples were intended to show you how easy it is to get into a mess if you use nested IF statements when you really shouldn’t. That’s an easy trap to get into. Always look for a simpler, single Google Sheets function to accomplish what you’re trying to do.
For example, let’s say you own a company and you’ve received feedback about four employees. Based on the four characteristics that you received feedback on, you need to determine if each employee is promotion material.
You can write a nested IF statement that’ll examine the answer for each characteristic and then provide a decision in the result column.
If the employee wasn’t:
- Punctual: You’re not too concerned, but you may not promote (maybe not).
- Efficient: You’re not too concerned, and may still promote (maybe).
- Leadership Quality: You may not promote if you agree with the feedback (maybe not).
- Trustworthy: You definitely don’t want to promote (definitely not).
You can program these decisions into the nested IF statement. Place the cursor into the cell where you want the results and type the following formula:
=IF(B2=”YES”,IF(C2=”YES”,IF(D2=”YES”,IF(E2=”YES”,”Definitely”,”Maybe Not”),”Maybe”),”Maybe Not”),”Definitely Not”)
This is a simple nested IF statement that returns “Definitely” if all responses are “YES”, but then returns different answers depending on whether any of the individual cells are “NO”.
This is one of the few examples where a nested IF statement would be a good choice. But as mentioned above, if you need to do anything much more complex, you’re much better off looking for an existing Google Sheets function that accomplishes the same goal much easier.
Some examples of great advanced “IF” functions include SUMIF, COUNTIFS, SUMIFS, and AVERAGEIFS.
