Excel - 结合 SUMPRODUCT

Question

我有一张 Excel 表格，用于更新玩家评分。


播放器
配售
初始化
1
2
3
4
金融评级


一个
1
2.0
1.000
0.018
0.016
0.014
2.007

D
2
-2.0
54.598
0.982
0.856
0.775
-1.758

C
3
-0.1
8.166
0.147
0.128
0.116
-0.213

乙
4
0.1
6.686
0.120
0.105
0.095
-0.036


单元格 D2 到 G5 中的公式是

=EXP(-$C2)/SUMPRODUCT(--($B$2:$B$5<=D$1)*(EXP(-$C$2:$C$5)))

H列(FinRating)中的公式是

=((SUMPRODUCT(($D$1:$G$1>=B2)*$D2:$G2)-1)*0.15)+C2

我想将这些公式应用于多个工作表(事件)并增加玩家的数量(这将需要添加更多列)，因此将它们组合在一个公式中是理想的。
理想情况下，公式将进入列 FinRating我可以调整它引用的范围，然后计算 FinRating。决赛 table 看起来像这样


播放器
配售
初始化
金融评级


一个
1
2.0
2.007

D
2
-2.0
-1.758

C
3
-0.1
-0.213

乙
4
0.1
-0.036


我花了一些时间尝试结合这些公式，但没有运气，我的经验是使用 SUMPRODUCT有点轻。
关于如何实现这一目标的任何想法？

Answer 1

这是一个使用 3 个辅助列(可以隐藏)的解决方案。请注意，您不需要为更多玩家添加更多帮助列 --- 3 列适用于所有情况。
在 D2 (第一个帮助栏):

=EXP(-C2)

并填写。
在 E2 (第二个帮助栏):

=1/SUMIF($B$2:$B$5,"<="&B2,$D$2:$D$5)

并填写。
在 F2 (第三个帮助栏):

=SUMIF($B$2:$B$5,">="&B2,$E$2:$E$5)

并填写。
最后在 G2 (最终评分):

=(D2*F2-1)*0.15+C2

并填写。
我建议将数据放在表格中并使用表格公式。这样做的好处是，当最后一行发生更改时，您不需要编辑公式(在您的示例中，最后一行都标有 $5 )。
公式几乎相同，只是列名取代了 A1 样式的引用。
在辅助列 1 中，我将其命名为 Calc1 :

=EXP(-[@IniRating])

在辅助列 2 中，我将其命名为 Calc2 :

=1/SUMIF([Placing],"<="&[@Placing],[Calc1])

在辅助列 3 中，我将其命名为 Calc3 :

=SUMIF([Placing],">="&[@Placing],[Calc2])

在 FinRating柱子:

=([@Calc1]*[@Calc3]-1)*0.15+[@IniRating]