(Google 表格/Excel)SUMPRODUCT 但忽略空单元格

(Google 表格/Excel)SUMPRODUCT 但忽略空单元格

我正在为我的女朋友制作一个 Google Sheets 电子表格,让她能够根据她对学生在不同项目、考试和参与度中所给予的成绩自动计算学生的成绩。

以家庭作业为例,我们可能希望为不同的作业赋予不同的权重。假设这些是我们的作业及其各自的分值(然后将其转换为百分比):

  • 家庭作业#1:50
  • 家庭作业#2:50
  • 作业#3:50
  • 作业#4:250

为此,我成功使用了 SUMPRODUCT,它将每个学生成绩的倍数和该作业的权重(以百分比表示)相加,结果是每个学生的加权平均值。

所以对于 HW #1,如果学生完成了 90% 的作业,那么这个值将乘以 0.125。

作为参考,每个学生单元格的公式如下所示:

=IF(ISBLANK(participation!D14), "---", (sumproduct(participation!D14:Z14, participation!$D$11:$Z$11)))

其中D:14:Z14参与度是不同作业成绩的集合。D11:Z11是每个作业自动计算百分比的集合。

我被困的地方:如果我想免除某个学生的作业怎么办?假​​设鲍勃因为生病没做作业 #4,现在,只要我输入这项作业的权重 (250),只要我没有输入作业 #4 的成绩,这个公式就会使鲍勃的平均成绩下降。如果数组中的成绩值为空,我该如何跳过乘法?(无论如何,权重值可能会被填充)

注意:我是 Excel 新手,主要通过在线研究和在线复制片段和半片段来整理此内容。感谢您的耐心。

公式引用的相关单元格的屏幕截图: 在此处输入图片描述

答案1

对于您的具体情况,通用工作表中单元格 C10 中的公式应为:

=IF(SUM(participation!D14:Z14)=0,"",SUMPRODUCT(participation!D14:Z14,participation!D11:Z11)/(SUMIF(participation!D14:Z14,"<>",participation!D11:Z11)-SUMIF(participation!D14:Z14,"=E",participation!D11:Z11)))

我弄乱了你的文件以便更好地理解。你唯一感兴趣的部分是 generalsheet 单元格 C10,它现在应该可以满足你的要求了!

它基本上是您之前示例的公式,只是根据您的需要进行了调整。

答案2

即使有您的截图,我也不能 100% 确定我是否了解您的需求。

我相信您正在寻找的是:

你有几个学生,有作业,还有作业分数,你的女朋友学生会获得一定比例的分数。所以如果他们完美地完成了作业,他们将获得 100% 的分数,如果他们根本没有完成作业,他们将获得 0% 的分数。

所以我做了以下事情:

在此处输入图片描述

SUMPRODUCT 和 AVERAGE 都会忽略空字段。这意味着平均百分比(第 12 行)不会下降。但是,如果您在 D10 中输入 0%,则它会下降。

请注意:D2:D10 格式为百分比!

这是我的示例的结果。

在此处输入图片描述

我希望这有帮助。

编辑 我不太明白你需要 sumproduct 做什么。如果你关心分数而不仅仅是百分比,如果他生病了你会给他多少分?300 分似乎不对,因为他什么也没做……

我只会按照百分比计算,并减去每个学生的分数。然后使用平均公式可以得到平均百分比。

https://support.office.com/en-nz/article/AVERAGE-function-047bac88-d466-426c-a32b-8f33eb960cf6?ui=en-US&rs=en-NZ&ad=NZ

相关内容