我想在 Excel 中创建一个公式来汇总学生作业的成绩。以下是相关信息:
总共有 11 项作业,成绩分为 11 列(GQ 列)。
当提交作业时,学生可能会得到 0、7、8.5 或 10 分之一。当未提交作业时,单元格留空(单元格中有一个“-”)。
只应对提交的前 9 项作业进行求和。因此,例如,如果 GO 列中的单元格非空(即学生获得了成绩),则应忽略 P 列和 Q 列中单元格的值。
在这 9 项作业中,我需要去掉最低的成绩(即,我需要从 9 项作业的成绩总和中减去这 9 项作业中最低的作业的值)。
例如:
“假设一名学生提交了 10 份作业,作业 1 和 2 的成绩为“7”,作业 3-7 的成绩为“10”,跳过了作业 8(单元格中为“-”),作业 9-11 的成绩为“7”。我要查找的公式将返回结果“71”。
编辑:
以下是我目前所掌握的信息:
=IF(COUNT(G2:Q2)=9,SUM(G2:Q2)-MIN(G2:Q2),IF(COUNT(G2:Q2)<9, SUM(G2:Q2)))
问题是,这无法处理提交了超过 9 份作业的学生。对于这些学生,我需要忽略范围中前 9 名以外的所有成绩,而不是将其相加。这才是真正让我感到困惑的地方。
答案1
这是我所想到的(使用与您的区域设置兼容的设置):
=IF(COUNT(G1:Q1) < 9; NA();
IF(COUNT(G1:Q1) = 9; SUM(G1:Q1) - MIN(G1:Q1);
IF(COUNT(G1:P1) = 9; SUM(G1:P1) - MIN(G1:P1);
SUM(G1:O1) - MIN(G1:O1)
)))
=IF(COUNT(G1:Q1) < 9, NA(),
IF(COUNT(G1:Q1) = 9, SUM(G1:Q1) - MIN(G1:Q1),
IF(COUNT(G1:P1) = 9, SUM(G1:P1) - MIN(G1:P1),
SUM(G1:O1) - MIN(G1:O1)
)))
如果学生提交的作业少于 9 项,公式将调用该NA()
函数。之后,公式会从右侧缩小范围,直到其中只有 9 个值(因此范围始终以 开头G1:
)。
我希望这个公式能够如您所期望的那样发挥作用。