Excel 中的嵌套 IF 语句 [超过 7 个允许限制]

发布于 2024-08-26 03:02:03 字数 857 浏览 10 评论 0原文

我正在尝试创建一个电子表格，该电子表格会根据学生获得的分数自动给学生评分。

我显然已经达到了 Excel 的嵌套 IF 语句限制，即 7。

这是我的 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"))))))))

我在网上读到我可以创建一个 VBA 脚本并对其进行分配，但我对 VBA 一无所知......所以如果有人可以帮我为此写一个VBA，那就太棒了。

它仍然缺少 C 级，任何低于 C 级的都应该被授予 D 级。

这是我正在尝试创建的评分方案......：

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 指定失败

我不介意走 VBA 路线，但是我对 VB 语言的理解绝对是最少的（不喜欢它）...如果这太严重了乏味，我想创建一个小的 php/mysql 应用程序。

原文

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.

Here's my if statement:

=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.

Its still missing the C- grade and anything lower should be awarded a D mark.

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

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

终止放荡 2024-09-02 03:02:03

您可以使用 VLOOKUP 公式通过创建单独的表将下限映射到字母来更优雅地完成此操作。映射表必须按等级编号升序排序。

例如：

表格

A     B
0     D
49.5  C-
54    C
59.5  C+
...   ...

公式：

=VLOOKUP(SomeCell, $A$1:$B$9, 2, TRUE)

其中$A$1:$B$9 是成绩表的范围。（$ 符号告诉 Excel 在复制公式时不要移动引用）。
传递 TRUE 作为最后一个参数将导致 Excel 执行二分搜索来查找值，这（只要数据已排序）正是您想要的。

You can do this much more elegantly with the VLOOKUP formula by making separate table mapping lower bounds to letters. The mapping table must be sorted by grade number ascending.

For example:

Table

A     B
0     D
49.5  C-
54    C
59.5  C+
...   ...

Formula:

=VLOOKUP(SomeCell, $A$1:$B$9, 2, TRUE)

Where $A$1:$B$9 is the range with the grade table. (The $ signs tell Excel not to move the reference if you copy the formula).
Passing TRUE as 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.

回复收藏 0 原文

永言不败 2024-09-02 03:02:03

转到 Visual Basic 编辑器，然后插入此代码。我不知道您使用的 Excel 版本是什么，但对于 2007 年之前的版本，请转到“工具”、“宏”、“Visual Basic 编辑器”。对于 2007 及更高版本，它位于“开发”选项卡上，默认情况下未启用。

根据您想要链接它的方式，您可以向页面添加一个按钮，或者从 Worksheet_Calculate 事件中调用它。

假设您在单元格 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

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.

Depending on how you want to link it, you could add a button to the page, or call it from the Worksheet_Calculate event.

This assumes that you have the student's total grade in cell A2, and will put the results in A2 and 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

回复收藏 0 原文

倒数 2024-09-02 03:02:03

一个简单的解决方案是将公式简单地拆分为两个单元格

=IF(O5>0.895,"A+",IF(O5>0.845,"A",IF(O5>0.795,"A-",<Other cell ref here>)))

其他单元格：

=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")))))

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")))))

回复收藏 0 原文

じее 2024-09-02 03:02:03

虽然为了简单和优雅，我更喜欢使用 Vlookup 方法（Slaks 解决方案）来处理数字参数，但在非数字参数的情况下，另一种方法变得有价值，那就是串联 IF。

=IF( Case1 , "Label 1", "" ) & 
 IF( Case2 , "Label 2", "" ) & 
 IF( Case3 , "Label 3", "" ) &

..... 等等

例如，在本例中：

= IF( O5 >= 89.5 , "A+" , "" ) & 
  If( AND ( O5 < 89.5 , O5 >= 84.5 ) , "A" , "" ) & 
  If( AND ( O5 < 84.5 , O5 >= 79.5 ) , "B+" , "" ) &

..... 等等其他级别。

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.

=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.

回复收藏 0 原文

乄_柒ぐ汐 2024-09-02 03:02:03

VLOOKUP 解决方案似乎是最好的。要添加 VBA 脚本，您可以打开 Visual Basic 工具栏，添加一个按钮（如按钮），然后双击它。该事件的代码在 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

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.

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

回复收藏 0 原文

~没有更多了~