☰

Excel：使用 XLOOKUP 计算 GPA

tag-icon • tag-icon microsoft-excel
Excel：使用 XLOOKUP 计算 GPA

我正在制作一个 Excel 电子表格来计算平均绩点 (GPA)。A 列是课程名称；B 列是相应的学分数；C 列是字母等级。

Course I     4.00      A
Course II    4.00      A
Course III   4.00      B
Course IV    4.00      B

我将等级制度放在单独的工作表上，其中 A 列中是字母等级，B 列中是其对应的等级点。

A     4.00
A–    3.67
B+    3.33
B     3.00
B–    2.67
C+    2.33
C     2.00
D     1.00
F     0.00
S     —
AU    —
I     —
W     —
NGR   —

在最简单的情况下，我把 GPA 计算为学分与绩点乘积之和与学分总和的比率。在上面的例子中，该比率为 (4.00*4.00+4.00*4.00+4.00*3.00+4.00*3.00)/(4.00+4.00+4.00+4.00)。在 Excel 中，我可以创建一个包含加权绩点的 D 列：=B1*XLOOKUP(C1,Grades,GradePoints)，其中 Grades 和 GradePoints 分别是工作表 2 中的 A1:A9 和 B1:B9。D 列中的每个单元格都从 C 列中获取成绩，查找相应的绩点，然后乘以学分数。然后，我可以将 D 列相加 ( =SUM(D1:D4)) 并将其除以学分数 ( =SUM(B1:B4))，这样我们就得到了 GPA。

但…

当出现 S（满意）、W（退学）、I（不完整）或其他没有绩点的称号时，我会遇到问题。让我们用以下示例替换第一个例子：

Course I     4.00     A
Course II    0.50     S
Course III   4.00     A
Course IV    4.00     B
Course V     4.00     B

当我计算绩点时，我想排除课程 II。当然，我想使用SUMIF或SUMIFS，但我不知道求和的标准是什么仅有的在指定范围 Grades 中具有相应成绩的学分。如果我有=ISNUMBER(XMATCH(C1,Grades))，我会得到TRUE或FALSE适当，但我不知道如何在中使用它SUMIF(S)。我也尝试过使用，但没有成功。对于 D 列中的成绩点，如果成绩不在指定范围 Grades 中，INDEX/MATCH我可以返回零，例如，由于 S 不在 Grades 中，因此将返回零。最后，我实际上也不想有明确的 D 列或隐藏单元格。我只想显示 GPA。任何帮助都将不胜感激！XLOOKUP=B2*XLOOKUP(C2,Grades,GradePoints,0)

答案1

如果我理解正确的话，那么：

如果你有，XLOOKUP那么你可能FILTER也有这个功能。在这种情况下，你可以使用以下命令：

请注意，我使用了表格和结构化引用，但如果你愿意，你可以更改为常规寻址

GPA:  =SUMPRODUCT(FILTER(Grades[Credit],COUNTIF(gradeSystem[Grade],Grades[GP])),
         XLOOKUP(FILTER(Grades[GP],COUNTIF(gradeSystem[Grade],Grades[GP])),gradeSystem[Grade],gradeSystem[GP]))/
         SUM(FILTER(Grades[Credit],COUNTIF(gradeSystem[Grade],Grades[GP])))

请注意，对于没有成绩点的字母等级，我将它们排除在表格之外GradeSystem。如果他们必须包含在表中，则只需将整个列范围引用更改为适当的部分列。

在此处输入图片描述

答案2

Excel默认情况下，从平均值中删除非数字条目，因此如果您有十个条目，全部是数字，则它会将 AVERAGE 计算为 SUM(x1:x10)/10 。

然而，如果 x10 处的条目是W（“已提取”）而不是数字，则 AVERAGE 默认为 SUM(x1:x9)/9 ，而不是包括 NAN（非数字）条目。将记录与数字和字母混合保存，它应该会满足您的要求。

平均值计算示例

上面的例子实际上是在 LibreOffice 中完成的计算， 但Excel应该表现相同。

如果愿意，您还可以使用查找将字母更改为数字。

答案3

下面显示的方法解决了该问题：

：警告：

由于 OP 没有提供足够的数据，因此我使用了一些样本数据来解决这个问题。

所使用的公式是 Excel 的替代公式XLOOKUP，并且适用于任何版本的 Excel。

在此处输入图片描述

怎么运行的：

  • 单元格中的公式C80：

    =SUM(C$66:C$79)

  • 单元格中的公式D66：

    =IF(OR(ISBLANK(C66),ISBLANK(B66)),0,IF(ISERROR(MATCH(B66,Sheet2!$F$66:$F$78,0)),0,C66*INDEX(Sheet2!$G$66:$G$78,MATCH(B66,Sheet2!$F$66:$F$78,0))))

将其填满。

  • 单元格中的公式D80：

    =SUM(D$66:D$79)

  • 输入C82这个=C80并且在C83是=D80。

  • 单元格中的最终公式C84：

    =IF(C$82=0," - ",C$83/C$82)

哦您也可以使用这个公式。

=IF(OR(C$80=0,D$80=0)," - ",D$80/C$80)

根据需要调整公式中的单元格引用。

答案4

将其用作数组公式：

=AVERAGE(IFERROR(AVERAGEIFS(I:I,H:H,C1:C5),""))

根据版本的不同，退出编辑模式时可能需要使用 Ctrl-Shift-Enter 而不是 Enter 来确认。

在此处输入图片描述

相关内容