Excel合并SUMPRODUCT相关公式实现玩家评级单公式计算咨询

合并后最终公式 #

提前将原中间列的分段阈值存放到独立区域$K$1:$N$1（可放到同表空白列或单独参数表，方便多赛事复用），玩家全量数据范围提前预留到1000行适配新增玩家：排名列范围$B$2:$B$1000，初始评级列范围$C$2:$C$1000。
把以下公式输入到第一个玩家的FinRating列（即D2单元格），下拉即可批量计算：

=((SUMPRODUCT(($K$1:$N$1>=B2)*EXP(-C2)/SUMPRODUCT(--($B$2:$B$1000<=$K$1:$N$1)*EXP(-$C$2:$C$1000)))-1)*0.15)+C2

适配调整说明 #

• 多赛事场景：直接把$K$1:$N$1替换为对应赛事的阈值范围，$B$2:$B$1000/$C$2:$C$1000替换为对应赛事的玩家数据范围即可
• 新增玩家场景：提前把数据范围的行号拉到足够大（比如$B$2:$B$2000），新增玩家时无需修改公式；也可以把玩家数据转为Excel结构化表，直接引用表名[排名]、表名[IniRating]，范围会自动扩展

兼容说明 #

• Excel 365/2021及以上版本直接输入下拉即可生效
• 旧版Excel输入公式后需要按Ctrl+Shift+Enter触发数组计算

公式逻辑说明 #

原辅助列的本质是对每个分段阈值计算两个值的比值：分子是当前玩家初始评级的指数变换值EXP(-C2)，分母是所有排名≤当前阈值的玩家的指数变换值之和。合并公式直接把这个比值计算放到外层SUMPRODUCT的数组逻辑里，对所有满足「阈值≥当前玩家排名」的分段计算比值再求和，完全替代了中间辅助列的计算过程。

内容的提问来源于stack exchange，提问作者Matt Drake