vba Excel 中的嵌套 IF 语句 [超过 7 个允许的限制]
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2465318/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Nested IF statements in Excel [Over the 7 allowed limit]
提问by Alks
I am trying to create a spreadsheet which automagically gives a grade to a student based on their marks they got.
我正在尝试创建一个电子表格，它会根据学生获得的分数自动给他们打分。
I've apparently hit Excel's nested IF statement limit which is 7.
我显然达到了 Excel 的嵌套 IF 语句限制，即 7。
Here's my if statement:
这是我的 if 语句：
=IF(O5>0.895,"A+",IF(O5>0.845,"A",IF(O5>0.795,"A-",IF(O5>0.745,"B+",IF(O5>0.695,"B",IF(O5>0.645,"B-",IF(O5>0.595,"C+",IF(O5>0.545,"C","D"))))))))
I was reading online that I could create a VBA script and assign it that, but I dont know anything about VBA....so if someone could help me write a VBA for this, would be awesome.
我在网上阅读说我可以创建一个 VBA 脚本并分配给它，但我对 VBA 一无所知......所以如果有人能帮我为此编写一个 VBA，那就太棒了。
Its still missing the C- grade and anything lower should be awarded a D mark.
它仍然缺少 C 级，任何更低的都应该被授予 D 标记。
This is the grading scheme I am trying to create...:
这是我正在尝试创建的评分方案...：
A+ 89.500 - 100.000 Pass with Distinction
A 84.500 - 89.490 Pass with Distinction
A- 79.500 - 84.490 Pass with Distinction
B+ 74.500 - 79.490 Pass with Merit
B 69.500 - 74.490 Pass with Merit
B- 64.500 - 69.490 Pass with Merit
C+ 59.500 - 64.490 Pass
C 54.500 - 59.490 Pass
C- 49.500 - 54.490 Pass
D 0.000 - 49.490 Specified Fail
A+ 89.500 - 100.000 以优异成绩及格
84.500 - 89.490 的优异成绩
A- 79.500 - 84.490 优异成绩及格
B+ 74.500 - 79.490 通过并获得优异成绩
B 69.500 - 74.490 优异成绩及格
B- 64.500 - 69.490 通过并获得优异成绩
C+ 59.500 - 64.490 通过
C 54.500 - 59.490 通过
C- 49.500 - 54.490 通过
D 0.000 - 49.490 指定失败
I wouldn't mind going down the VBA route, however my understanding of VB language is absolutely minimal (don't like it)...if this gets too tedious, I was thinking to create a small php/mysql application instead.
我不介意走 VBA 路线，但是我对 VB 语言的理解绝对是最少的（不喜欢它）...如果这太乏味了，我正在考虑创建一个小的 php/mysql 应用程序。
回答by SLaks
You can do this much more elegantly with the VLOOKUPformula by making separate table mapping lower bounds to letters. The mapping table must be sorted by grade number ascending.
VLOOKUP通过将下界映射到字母的单独表格，您可以使用公式更优雅地完成此操作。映射表必须按年级编号升序排序。
For example:
例如：
Table
桌子
A B 0 D 49.5 C- 54 C 59.5 C+ ... ...
Formula:
公式：
=VLOOKUP(SomeCell, $A:$B, 2, TRUE)
Where $A$1:$B$9is the range with the grade table. (The $signs tell Excel not to move the reference if you copy the formula).
Passing TRUEas the last argument will cause Excel to do a binary search to find the value, which (as long as the data is sorted) is exactly what you want it to do.
$A$1:$B$9与等级表的范围在哪里。（$如果您复制公式，这些标志告诉 Excel 不要移动引用）。作为最后一个参数
传递TRUE将导致 Excel 进行二分搜索以查找值，这（只要数据已排序）正是您希望它执行的操作。
回答by Andrew Neely
Go to the Visual Basic Editor, and insert this code. I don't know what version of Excel you're using, but for versions before 2007, go to tools, Macros, Visual Basic Editor. For Version 2007 and newer , it is on the Development Tab which is not enabled by default.
转到 Visual Basic 编辑器，并插入此代码。我不知道您使用的是哪个版本的 Excel，但对于 2007 年之前的版本，请转到工具、宏、Visual Basic 编辑器。对于版本 2007 及更新版本，它位于默认情况下未启用的开发选项卡上。
Depending on how you want to link it, you could add a button to the page, or call it from the Worksheet_Calculate event.
根据您希望如何链接它，您可以向页面添加一个按钮，或从 Worksheet_Calculate 事件中调用它。
This assumes that you have the student's total grade in cell A2, and will put the results in A2 and B2.
这假设您在单元格 A2 中有学生的总成绩，并将结果放在 A2 和 B2 中。
Sub Calculate
dim LetterGrade as string
dim Superlative as string
Select Case Cells(1,2)
Case >= 89.500
LetterGrade="A+"
Superlative ="Pass with Distinction"
Case 84.500 to 89.490
LetterGrade="A"
Superlative ="Pass with Distinction"
Case 79.500 to 84.490
LetterGrade="A-"
Superlative ="Pass with Distinction"
Case 74.500 to 79.490
LetterGrade="B+"
Superlative ="Pass with Merit"
Case 69.500 to 74.490
LetterGrade="B"
Superlative ="Pass with Merit"
Case 64.500 to 69.490
LetterGrade="B-"
Superlative ="Pass with Merit"
case 59.500 to 64.490
LetterGrade="C+"
Superlative ="Pass"
Case 54.500 to 59.490
LetterGrade="C"
Superlative ="Pass"
Case 49.500 to 54.490
LetterGrade="C-"
Superlative ="Pass"
Case <= 49.490
LetterGrade="F"
Superlative ="Specified Fail"
End Select
Cells(2, 1) = LetterGrade
Cells(2, 2) = Superlative
End Sub
回答by Rahul Raj
While I prefer the Vlookup method (Slaks solution) for numeric parameters for the simplicity and elegance, an alternative which becomes valuable in case of non-numeric parameters is concatenated IFs.
虽然我更喜欢 Vlookup 方法（Slaks 解决方案）用于简单和优雅的数字参数，但在非数字参数的情况下变得有价值的替代方法是连接 IF。
=IF( Case1 , "Label 1", "" ) &
IF( Case2 , "Label 2", "" ) &
IF( Case3 , "Label 3", "" ) &
..... and so on
..... 等等
For example, in this case:
例如，在这种情况下：
= IF( O5 >= 89.5 , "A+" , "" ) &
If( AND ( O5 < 89.5 , O5 >= 84.5 ) , "A" , "" ) &
If( AND ( O5 < 84.5 , O5 >= 79.5 ) , "B+" , "" ) &
..... and so on for other levels.
..... 其他级别的依此类推。
回答by Codezy
An easy solution would be to simply split the formula into two cells
一个简单的解决方案是简单地将公式拆分为两个单元格
=IF(O5>0.895,"A+",IF(O5>0.845,"A",IF(O5>0.795,"A-",<Other cell ref here>)))
Other cell:
其他单元格：
=IF(O5>0.745,"B+",IF(O5>0.695,"B",IF(O5>0.645,"B-",IF(O5>0.595,"C+",IF(O5>0.545,"C","D")))))
回答by icelobber
The VLOOKUP solution seems the best. To add a VBA script, you could bring up the Visual Basic Toolbar, add a control like a Button and then double-click it. The code for that event opens in the Excel VBA environment. There you could add the code in VB, perhaps something like this.
VLOOKUP 解决方案似乎是最好的。要添加 VBA 脚本，您可以调出 Visual Basic 工具栏，添加像 Button 这样的控件，然后双击它。该事件的代码在 Excel VBA 环境中打开。在那里你可以在 VB 中添加代码，也许是这样的。
Private Sub CommandButton1_Click()
Cells(1, 2) = getValue(Cells(1, 1))
End Sub
Private Function getValue(ByVal argMarks As Double)
If argMarks > 89.5 And argMarks <= 100 Then
getValue = "A+"
If argMarks > 84.5 And argMarks <= 89.49 Then
getValue = "A"
.
.
and so on...
End Function
