覆盖总标记的类别加权

Question

我有一个教师等级手册，它使用类别加权(以及每个类别中的评估权重)。我计算最终成绩的公式见E栏：

https://docs.google.com/spreadsheets/d/1TMsuJWtc4C7tebZGVVyeA4qbmLCnGZRi55ZRJsI-jGE/edit?usp=sharing

我想做的是给老师一个选择，让他们减少对某一学生进行评估的总分。

示例：

• 评价总分为10分，学生分为4 (40%)。
• 老师决定给这个学生打总分=5分。
• 老师将输入：4 5，其中4是学生的分数，5是评价的新总分。新的百分比是80%

我已经编写了计算新百分比的公式，没有问题，但是我想不出如何得到这个学生的最终成绩。

在上面链接的电子表格中，学生1和学生2在E栏中应有相同的最终成绩。

如何更改E列(从E8开始)中的公式，以包括像4 5这样的情况

Answer 1

那太有趣了！使用@TomSharpe的建议，我想出了一个似乎有效的方法：

=IF(ISBLANK($C8),"",IFERROR((

Settings!$E$3*(IF(COUNTIF($N$5:$HD$5,Settings!$D$3)=0,0,SUMPRODUCT($O8:$HE8,$N$5:$HD$5=Settings!$D$3,$N$4:$HD$4)*100/SUMIFS($N$4:$HD$4,$N$5:$HD$5,Settings!$D$3,$O8:$HE8,">=0",$N$3:$HD$3,">=0")))

+
Settings!$E$4*(IF(COUNTIF($N$5:$HD$5,Settings!$D$4)=0,0,SUMPRODUCT($O8:$HE8,$N$5:$HD$5=Settings!$D$4,$N$4:$HD$4)*100/SUMIFS($N$4:$HD$4,$N$5:$HD$5,Settings!$D$4,$O8:$HE8,">=0",$N$3:$HD$3,">=0")))

+
Settings!$E$5*(IF(COUNTIF($N$5:$HD$5,Settings!$D$5)=0,0,SUMPRODUCT($O8:$HE8,$N$5:$HD$5=Settings!$D$5,$N$4:$HD$4)*100/SUMIFS($N$4:$HD$4,$N$5:$HD$5,Settings!$D$5,$O8:$HE8,">=0",$N$3:$HD$3,">=0")))

+
Settings!$E$6*(IF(COUNTIF($N$5:$HD$5,Settings!$D$6)=0,0,SUMPRODUCT($O8:$HE8,$N$5:$HD$5=Settings!$D$6,$N$4:$HD$4)*100/SUMIFS($N$4:$HD$4,$N$5:$HD$5,Settings!$D$6,$O8:$HE8,">=0",$N$3:$HD$3,">=0")))

+
Settings!$E$7*(IF(COUNTIF($N$5:$HD$5,Settings!$D$7)=0,0,SUMPRODUCT($O8:$HE8,$N$5:$HD$5=Settings!$D$7,$N$4:$HD$4)*100/SUMIFS($N$4:$HD$4,$N$5:$HD$5,Settings!$D$7,$O8:$HE8,">=0",$N$3:$HD$3,">=0")))

+
Settings!$E$8*(IF(COUNTIF($N$5:$HD$5,Settings!$D$8)=0,0,SUMPRODUCT($O8:$HE8,$N$5:$HD$5=Settings!$D$8,$N$4:$HD$4)*100/SUMIFS($N$4:$HD$4,$N$5:$HD$5,Settings!$D$8,$O8:$HE8,">=0",$N$3:$HD$3,">=0")))

+
Settings!$E$9*(IF(COUNTIF($N$5:$HD$5,Settings!$D$9)=0,0,SUMPRODUCT($O8:$HE8,$N$5:$HD$5=Settings!$D$9,$N$4:$HD$4)*100/SUMIFS($N$4:$HD$4,$N$5:$HD$5,Settings!$D$9,$O8:$HE8,">=0",$N$3:$HD$3,">=0")))

+
Settings!$E$10*(IF(COUNTIF($N$5:$HD$5,Settings!$D$10)=0,0,SUMPRODUCT($O8:$HE8,$N$5:$HD$5=Settings!$D$10,$N$4:$HD$4)*100/SUMIFS($N$4:$HD$4,$N$5:$HD$5,Settings!$D$10,$O8:$HE8,">=0",$N$3:$HD$3,">=0")))

+
Settings!$E$11*(IF(COUNTIF($N$5:$HD$5,Settings!$D$11)=0,0,SUMPRODUCT($O8:$HE8,$N$5:$HD$5=Settings!$D$11,$N$4:$HD$4)*100/SUMIFS($N$4:$HD$4,$N$5:$HD$5,Settings!$D$11,$O8:$HE8,">=0",$N$3:$HD$3,">=0")))

+
Settings!$E$12*(IF(COUNTIF($N$5:$HD$5,Settings!$D$12)=0,0,SUMPRODUCT($O8:$HE8,$N$5:$HD$5=Settings!$D$12,$N$4:$HD$4)*100/SUMIFS($N$4:$HD$4,$N$5:$HD$5,Settings!$D$12,$O8:$HE8,">=0",$N$3:$HD$3,">=0")))

)/(IF(COUNTIFS($N$5:$HE$5,Settings!$D$3,N8:HE8,"<>")>=1,Settings!$E$3,0)+IF(COUNTIFS($N$5:$HE$5,Settings!$D$4,N8:HE8,"<>")>=1,Settings!$E$4,0)+IF(COUNTIFS($N$5:$HE$5,Settings!$D$5,N8:HE8,"<>")>=1,Settings!$E$5,0)+IF(COUNTIFS($N$5:$HE$5,Settings!$D$6,N8:HE8,"<>")>=1,Settings!$E$6,0)+IF(COUNTIFS($N$5:$HE$5,Settings!$D$7,N8:HE8,"<>")>=1,Settings!$E$7,0)+IF(COUNTIFS($N$5:$HE$5,Settings!$D$8,N8:HE8,"<>")>=1,Settings!$E$8,0)+IF(COUNTIFS($N$5:$HE$5,Settings!$D$9,N8:HE8,"<>")>=1,Settings!$E$9,0)+IF(COUNTIFS($N$5:$HE$5,Settings!$D$10,N8:HE8,"<>")>=1,Settings!$E$10,0)+IF(COUNTIFS($N$5:$HE$5,Settings!$D$11,N8:HE8,"<>")>=1,Settings!$E$11,0)+IF(COUNTIFS($N$5:$HE$5,Settings!$D$12,N8:HE8,"<>"
)>=1,Settings!$E$12,0)),"No Grade"))

Answer 2

我认为最简单的解决方案是在E列中重写公式，使用百分比列中的数字来计算分数，而不是使用标记列中复杂的字符串。

您可以在E8中使用的示例公式：

=IFERROR(SUMPRODUCT(FILTER(N8:Y8,ISFORMULA(N8:Y8))*FILTER(N$4:Y$4,ISNUMBER(N$4:Y$4))) *100 / SUM(N$4:Y$4),"")