如何利用Excel的强大功能来自动执行分配字母等级

如何利用Excel的强大功能来自动执行分配字母等级

作者

如何利用Excel的强大功能来自动执行分配字母等级

您是否曾面临过为大量分数分配字母等级的艰巨任务？无论您是敬业的教育工作者、一丝不苟的讲师，还是仅仅需要高效评分解决方案的人，您都找到了正确的解决方案。

在数据分析领域，Microsoft Excel 是一款多功能工具，可以简化复杂的任务。其中一个挑战是分配字母等级，无论是针对学生的学业成绩还是评估销售团队的表现。虽然手动创建表格可能是一种选择，但我们将向您展示一种更智能的方法。

在本教程中，我们将探讨如何利用Excel的强大功能来自动执行分配字母等级的过程。

无需手动、耗时的评分——相反，您会发现易于使用的公式、VBA 脚本和 Office 脚本可以简化任务。所以，拿起您的 Excel 工作簿，里面写满了等待评分的分数，让我们深入了解高效评分解决方案的世界。

推荐：WordPress多用途主题ShadePro

在 Excel 中分配字母等级的原因

字母评分是一种常见的做法，将数字分数转换为人们容易理解和联系的字母等级。

例如，如果你说你在比赛测试中得了 80 分，这可能不会给观众留下好印象。但是，如果你说你在数学测试中获得了 A 级，你的观众很容易就能明白你是班上数学成绩最好的学生之一。

此外，字母评分系统允许教育机构或任何其他使用它的人将数百个分数分成几个等级。例如，您的企业有 300 名销售人员，您需要将他们分为四个绩效级别，如 A、B、C 和 F。

以下是在 Excel 中使用字母等级的一些原因：

教育中的字母评分

  1. Excel 允许教育工作者快速计算并为大量学生的分数分配字母等级，与手动计算相比节省时间。
  2. 它确保了一致的评分系统，最大限度地减少错误并促进评分的公平性。
  3. Excel 可以灵活地定制评分标准以适应特定的课程、作业或评分标准。

商业中的字母等级

  1. Excel 使雇主和经理能够根据预定义的评分标准（使用 A、B、C、D 等英文字母）评估员工绩效。
  2. 它通过为各种与工作相关的指标分配字母等级来促进客观和数据驱动的绩效评估。
  3. Excel 提供了员工评估的数字记录，这对于人力资源文档和将来的参考非常有价值。

小规模使用的字母分级

  1. 当您在线学习技能或在家进行模拟测试以准备参加即将到来的竞争性考试时，您可以使用 Excel 中的字母等级来评估您的表现。
  2. 你可以使用这项技能来追踪个人目标和成就。这样，你就能保持动力并专注于进步。
  3. 您也可以在个人理财中使用字母等级。在 Excel 中为预算类别分配字母等级可以让您快速了解财务状况以及可以改进的领域。

使用 IF 公式分配字母等级

IF函数是所有方法中最简单的一种，它使您能够使用字母（如A、B、C等）对分数进行评分。它也适用于大多数 Excel 桌面版和在线版。以下是您需要遵循的步骤：

如何利用Excel的强大功能来自动执行分配字母等级
首先了解数据集的结构
  1. 您可以按照以下方式组织包含要评分的分数的工作表数据集：
    • A 列中名为“候选人”的列标题
    • B 列中的列标题GPA
    • C 列中的另一个列标题成绩
  2. 用相关数据填充A 列和B列的行。
在 Excel 中使用 IF 函数分配字母等级
在 Excel 中使用 IF 函数分配字母等级
  1. 在列标题“成绩”下方的第一行（单元格B2 ）中，复制并粘贴以下IF函数：
=IF(ISNUMBER(B2), IF(B2>=4, "A", IF(B2>=3.7, "A-", IF(B2>=3.3, "B+", IF(B2>=3, "B", IF(B2>=2.7, "B-", IF(B2>=2.3, "C+", IF(B2>=2, "C", IF(B2>=1.7, "C-", IF(B2>=1.3, "D+", IF(B2>=1, "D", IF(B2>=0.7, "D-", "F"))))))))))), "")
  1. 点击Enter即可获得第一位候选人的字母等级。
使用 Excel 中的自动填充功能
使用 Excel 中的自动填充功能
  1. 现在，拖动所选单元格的自动填充手柄并将其向下拉，直到相邻列 B中有可用数据的单元格。
  2. Excel 将立即填充其余 GPA 分数的字母等级。

如果您使用的是相同的 GPA 量表和字母评分系统（这是美国流行的系统），只需在上述函数中进行以下更改，以便它在您自己的工作表上运行：

  • 将公式中所有出现的单元格引用替换B2为另一个可获得分数的单元格引用。

使用 IFS 函数

如果您使用的是 Excel 桌面应用程序 2019 或更新版本以及 Excel 网页版工具，则可以使用IFS函数，而不是使用长嵌套的 IF 函数来用字母评分。

您可以在下方找到可用于本教程前面基于 IF 公式的方法中使用的相同数据集的IFS函数：

使用 IFS 函数代替 IF 公式
使用 IFS 函数代替 IF 公式
=IFS(B2>=4, "A", B2>=3.7, "A-", B2>=3.3, "B+", B2>=3, "B", B2>=2.7, "B-", B2>=2.3, "C+", B2>=2, "C", B2>=1.7, "C-", B2>=1.3, "D+", B2>=1, "D", B2>=0.7, "D-", B2>=0, "F")

再次，您需要更改上述公式中的单元格引用，以便B2在工作表中正确使用该公式。

例如，如果分数在D 列，则您应该在上面的函数中D2输入。B2

推荐：Google Sheets ADDRESS函数基础知识

使用 VLOOKUP 函数分配字母等级

在 Excel 中为分数分配字母等级时，VLOOKUP函数是不可或缺的。

此功能使您能够在表或范围内搜索特定值（例如分数），并从另一列中检索相应的值（字母等级）。

因此，您可以创建一个单独的表格，其中包含分数到等级的映射，从而轻松自动分配字母等级。以下是您可以遵循的说明：

组织数据集
组织数据集
  1. 如果您希望使用VLOOKUP，则应按以下方式组织工作表：
    • 在工作表的一个表格中创建列标题，如候选人、GPA和成绩
    • 根据需要在上述数据列中填充数据
    • 在列标题 GPA 和字母等级下创建另一个表格，用于GPA和字母等级的参考数据
    • 再次填充上述参考表的数据
    • 使用主页>编辑>排序和筛选中的从最小到最大排序选项对参考数据表进行排序
在 Excel 中输入 VLOOKUP 函数
在 Excel 中输入 VLOOKUP 函数
  1. 现在，突出显示C 列（成绩列）第二行的单元格并输入以下公式：
=VLOOKUP(B2,$E$2:$F$13,2,TRUE)
  1. 点击Enter获取单元格中相应分数的字母等级B2。
使用“自动填充”功能填充该列下的所有单元格中的 VLOOKUP
使用“自动填充”功能填充该列下的所有单元格中的 VLOOKUP
  1. 现在，使用“自动填充”拖动框将相同的公式复制并粘贴到“成绩”列的所有行中。

修改上述公式的方法如下：

  • 更改查找值的单元格引用，该引用B2根据您自己的数据集。
  • 另外，根据您的工作表修改查找表地址引用$E$2:$F$13和列索引。2

使用 SWITCH 函数分配字母等级

SWITCH函数是 Excel 中用于评分的动态工具。它在需要评估多个条件和结果的场景中表现出色。

SWITCH简化了该过程，无需嵌套多个 IF 函数或创建复杂的VLOOKUP表。

您提供一个值或表达式，SWITCH会将其与多个值及其各自的结果进行比较。

以下说明了如何在 Excel 中使用SWITCH函数对字母分数进行评分：

在 Excel 中输入 SWITCH 公式
在 Excel 中输入 SWITCH 公式
  1. 选择您想要填充相应分数的字母等级的单元格。
  2. 输入以下公式并点击Enter：
=SWITCH(TRUE,B2=4,"A",B2=3.7,"A-",B2=3.3,"B+",B2=3,"B",B2=2.7,"B-",B2=2.3,"C+",B2=2,"C",B2=1.7,"C-",B2=1.3,"D+",B2=1,"D",B2=0.7,"D-",B2=0,"F")
使用 SWITCH 函数在 Excel 中分配字母等级
在 Excel 中使用 SWITCH 函数分配字母等级
  1. Excel 将立即生成字母等级。
  2. 现在，使用自动填充手柄并将其拖动直到列中的最后一个值。
  3. 您应该看到其余分数的等级。

构建SWITCH公式非常简单。您需要在开头添加一个表达式，TRUE在本例中就是如此。然后，只需添加逻辑，如B2=4, "A"、B2=3.7, "A-"等等。

B2你可以根据自己的工作表更改的目标值。例如， B2=90%，对应分数的等级将是A。

使用 Power Query 在 Excel 中分配字母等级

下面介绍如何使用 Power Query 和Power Query中的自定义公式在 Excel 中为分数分配字母等级：

来自 SQL 数据库
来自 SQL 数据库

首先，您需要将工作表数据导出或将外部数据库导入 Power Query 编辑器。为此，只需转到数据>获取数据，然后选择数据连接器，例如从数据库>从 SQL Server 数据库。

导入数据
导入数据

对于 Excel 工作表数据，只需突出显示要导出的数据，然后单击“数据” > “从表/范围”。在显示的对话框中单击“确定”即可将数据导出到 Power Query。

进入具有分数数据集的 Power Query 编辑器后，请按照以下步骤操作：

使用公式在 Power Query 中创建新列
使用公式在 Power Query 中创建新列
  1. 通过单击添加列>自定义列来调出自定义列向导。
  2. 在新列名字段中，输入您选择的列名，例如Grades。
  3. 在自定义列公式中，将以下公式复制并粘贴到公式字段中：
 if [Score] >= 4.0 then "A"
    else if [Score] >= 3.7 then "A-"
    else if [Score] >= 3.3 then "B+"
    else if [Score] >= 3.0 then "B"
    else if [Score] >= 2.7 then "B-"
    else if [Score] >= 2.3 then "C+"
    else if [Score] >= 2.0 then "C"
    else if [Score] >= 1.7 then "C-"
    else if [Score] >= 1.3 then "D+"
    else if [Score] >= 1.0 then "D"
    else if [Score] >= 0.7 then "D-"
    else "F"
  1. 单击“确定”保存新创建的列。
使用 Power Query 分配字母等级
使用 Power Query 分配字母等级
  1. 您应该会看到一个与导入的数据集相关的包含字母等级的新列。

现在，您可以按照以下步骤将新列与导入的分数数据集一起导出：

关闭并加载至
关闭并加载至
  1. 单击“文件”选项卡。
  2. 在显示的上下文菜单中选择“关闭并加载到”选项。
从 Power Query 将数据导入工作表
从 Power Query 将数据导入工作表
  1. 在导入数据对话框中，选择现有工作表选项。
  2. 另外，选择工作表上的某个单元格范围作为目标。
  3. 单击“确定”完成导出过程。
  4. 删除现有的GPA列。
使用 Power Query 的字母等级
使用 Power Query 的字母等级

就这样！您已成功使用 Power Query 在 Excel 中分配字母等级。

推荐：最佳WordPress SEO插件Rank Math Pro

使用 Excel VBA 自动为分数分配字母等级

Excel VBA可让您自动执行 Excel 中的字母分级的整个过程。您只需提供输入数据。请参阅以下步骤：

创建 VBA 脚本来分配字母等级
创建 VBA 脚本来分配字母等级
  1. 按Alt+F11调出Excel VBA 编辑器。
  2. 单击“插入”并选择“模块”。
  3. 将以下脚本复制并粘贴到空白模块中：
Sub AssignLetterGrades()
    Dim ws As Worksheet
    Dim cell As Range
    Dim grade As String
    
    ' Define the worksheet where the GPA scores are located
    Set ws = ThisWorkbook.Sheets("Sheet5") ' Change "Sheet5" to your sheet name
    
    ' Loop through each cell in column B (B2 to B7)
    For Each cell In ws.Range("B2:B7")
        ' Check if the cell is not empty
        If Not IsEmpty(cell) Then
            ' Get the GPA score from the cell
            Dim gpa As Double
            gpa = cell.Value
            
            ' Assign the letter grade based on the GPA score
            Select Case gpa
                Case Is >= 4#
                    grade = "A"
                Case Is >= 3.7
                    grade = "A-"
                Case Is >= 3.3
                    grade = "B+"
                Case Is >= 3#
                    grade = "B"
                Case Is >= 2.7
                    grade = "B-"
                Case Is >= 2.3
                    grade = "C+"
                Case Is >= 2#
                    grade = "C"
                Case Is >= 1.7
                    grade = "C-"
                Case Is >= 1.3
                    grade = "D+"
                Case Is >= 1#
                    grade = "D"
                Case Else
                    grade = "F"
            End Select
            
            ' Write the letter grade back to the adjacent cell in column C
            cell.Offset(0, 1).Value = grade
        End If
    Next cell
End Sub
  1. 单击保存按钮并关闭VBA 编辑器。

要使用宏，请按照下列步骤操作：

运行 Excel 宏
运行 Excel 宏
  1. 按Alt+F8调出宏对话框。
  2. 选择AssignLetterGrades宏。
  3. 单击“运行”以执行宏。

您可以按照脚本中添加的注释轻松自定义 VBA 脚本。

使用 Office 脚本在 Excel 中分配字母等级

如果您需要在 Excel 网页版应用中自动执行字母评分任务，则可以使用Office 脚本。以下是脚本及其使用步骤：

在 Excel 中运行 Office 脚本代码
在 Excel 中运行 Office 脚本代码
  1. 单击“自动化”并选择“新脚本”选项。
  2. 在代码编辑器面板中，复制并粘贴以下脚本：
function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range C2 on selectedSheet
	selectedSheet.getRange("C2").setFormulaLocal("=IF(ISNUMBER(B2), IF(B2>=4, \"A\", IF(B2>=$F$2, \$E$2, IF(B2>=$F$3, \$E$3, IF(B2>=$F$4, \$E$4, IF(B2>=$F$5, \$E$5, IF(B2>=$F$6, \$E$6, IF(B2>=$F$7, \$E$7, IF(B2>=$F$8, \$E$8, IF(B2>=$F$9, \$E$9, IF(B2>=$F$10, \$E$10, IF(B2>=$F$11, \$E$11, \"F\"))))))))))), \"\")");
	// Auto fill range
	selectedSheet.getRange("C2").autoFill("C2:C7", ExcelScript.AutoFillType.fillDefault);
}
  1. 单击保存脚本按钮。
  2. 单击运行按钮来执行脚本。

脚本的使用方法如下：

  • 在单元格范围内输入GPAE2:E13值，并在单元格范围内输入字母等级F2:F13。
  • B2根据您的工作表将上述脚本中的单元格地址更改为另一个单元格地址。
  • 要更改脚本中的GPA值（E 列）和字母等级（F 列）参考，您需要修改所有嵌套IF$F$2函数中的和等条目。$E$2
  • selectedSheet.getRange("C2").autoFill("C2:C7",要在相关列或单元格范围内填充字母等级，请修改上述脚本的代码元素中的单元格地址。

结论

现在，您可以在 Excel 中高效地以编程方式对学生或工作表现进行评分。对于偶尔需要和小规模使用的情况，您应该依赖IF、IFS、VLOOKUP和SWITCH等 Excel 函数。

当您从第三方数据库站点或其他 Excel 文件导入包含分数的大型数据集时，可以使用Power Query。它允许您将分数转换为成绩，并将转换后的数据集导入所需的工作表。

最后，对于 Excel 中大规模、频繁的字母评分，您可以使用Excel VBA和Office Scripts等脚本工具将流程自动化高达 90％ 。

推荐：如何在Windows 11上安装Google Play商店

Previous

如何在Microsoft Excel中使用条件格式

Next

如何修复Forza Horizo​​n 5在线模式在Windows 11上不起作用

相关文章

在Microsoft Excel中进行四舍五入的13种方法

在Microsoft Excel中进行四舍五入的13种方法

Microsoft Excel中取消隐藏第一行或第一列的5种方法

Microsoft Excel中取消隐藏第一行或第一列的5种方法

在Microsoft Excel中选择随机样本的6种方法

关闭Microsoft Excel中的滚动锁定的5种方法

Microsoft Excel中冻结行和列的6种方法

Microsoft Excel中冻结行和列的6种方法

发表评论