当心Excel中的嵌套IF公式

Do you use the IF function in your workbooks?  It's helpful if you want to check something, and then show a result based on the result of your test.

您是否在工作簿中使用IF函数？ 如果要检查某些内容，然后根据测试结果显示结果，这将很有帮助。

For example, in the screen shot below, the total won't show if a quantity hasn't been entered in cell D7.

例如，在下面的屏幕快照中，如果未在单元格D7中输入数量，则总计不会显示。

嵌套IF公式出错 (Nested IF Formulas Gone Wrong)

While I was updating the IF Function page on my website this week, I remembered some of the overly complex IF formulas that I've seen.

本周，当我在网站上更新IF Function页面时 ，我想起了一些过于复杂的IF公式。

For example, do you ever see nested IF formulas that look like this? Not in your workbooks, of course, but your co-workers might create them!

例如，您看到过嵌套的IF公式吗？ 当然，不在您的工作簿中，但是您的同事可以创建它们！

=IF(B4>=85,"A",IF(B4>=70,"B", IF(B4>=60,"C",IF(B4>=50,"D","F"))))

= IF(B4> = 85，“ A”，IF(B4> = 70，“ B”，IF(B4> = 60，“ C”，IF(B4> = 50，“ D”，“ F”))) ))

That formula was designed to convert students' numeric scores into letter grades.

该公式旨在将学生的数字分数转换为字母分数。

创建一个查询表 (Create a Lookup Table)

Sure, that long, nested IF formula works, but it's difficult to read, and a pain to maintain. If the scoring system changes, you'll have to find all those IF formulas and change them.

当然，嵌套的IF公式很长，但是很难读，而且很难维护。 如果评分系统发生变化，则必须找到所有这些IF公式并进行更改。

Instead of building nested IF formulas to do this type of "lookup", use the INDEX and MATCH functions, or the VLOOKUP function instead.

不用建立嵌套的IF公式来进行这种“查找”，而是使用INDEX和MATCH函数 ，或者使用VLOOKUP函数 。

Create a list that shows the values, like the numeric and letter grades in the screen shot below. The range A2:B6 is named "GradeList".

创建一个显示值的列表，例如以下屏幕截图中的数字和字母等级。 范围A2：B6被命名为“ GradeList”。

使用VLOOKUP公式 (Use a VLOOKUP Formula)

Then, you could get the letter grades with a simple VLOOKUP formula:

然后，您可以使用简单的VLOOKUP公式获得字母等级：

=VLOOKUP(B4,GradeList,2,TRUE)

= VLOOKUP(B4，GradeList，2，TRUE)

With VLOOKUP, the lookup values have to be in the leftmost column of the lookup range.

使用VLOOKUP，查找值必须在查找范围的最左列中。

使用INDEX和MATCH公式 (Use an INDEX and MATCH Formula)

Or, if you prefer, you could use an INDEX and MATCH formula:

或者，如果您愿意，可以使用INDEX和MATCH公式：

=INDEX(Grades!$B$2:$B$6, MATCH(B4,Grades!$A$2:$A$6,1))

= INDEX(等级！$ B $ 2：$ B $ 6，MATCH(B4，等级！$ A $ 2：$ A $ 6,1))

With INDEX and MATCH, the lookup values can be in any column of the lookup table.

使用INDEX和MATCH，查找值可以位于查找表的任何列中。

易于维护 (Easy to Maintain)

With a lookup table, it's easy to change the scoring system, and maintain the Excel file.

使用查找表，可以轻松更改计分系统并维护Excel文件。

Just make the updates in the table, and all the VLOOKUP or INDEX and MATCH formulas will automatically show the correct data.

只需在表中进行更新，所有的VLOOKUP或INDEX和MATCH公式都会自动显示正确的数据。

You can also use a Named Excel Table, to make the maintenance even easier. It will automatically adjust if you add or remove rows or columns.

您还可以使用命名Excel表来使维护更加容易。 如果您添加或删除行或列，它将自动进行调整。

And it's much easier than trying to add more nested IFs, to include all the new plus (+) grades!

而且比尝试添加更多嵌套的IF(包括所有新的加号(+))要容易得多！

下载中频功能 (Download the IF Function)

You can download the sample file for the IF function, from my Contextures website. It was updated this week, to include all the new samples from the IF Function page. The zipped file is in xlsx format, and does not contain macros.

您可以从我的Contextures网站下载IF函数的示例文件 。 它在本周进行了更新，以包括IF Function页面中的所有新样本。 压缩文件为xlsx格式，不包含宏。

翻译自: https://contexturesblog.com/archives/2015/09/17/beware-the-nested-if-formula-in-excel/