如何在Excel中按指定权重递增规则基于历史行计算Effective Value？

核心规则梳理 #

• 初始有效值固定为3，始终全额计入结果
• 每新增一个数值后，该数值的权重从0.2开始，每日递增0.2，直到权重达到1（即5天后全额计入）
• 当日有效值 = 初始值3 + 所有历史新增数值 × 各自当前权重
• 有效值上限为10，达到后不再变化

公式实现 #

假设数据布局：

• A列：天数（A1为表头，A2开始为1、2、3...）
• B列：数值（B1为表头，新增数值填入对应行，无新增则留空）
• C列：Effective Value（C1为表头）

步骤1：初始值设置

在C2单元格输入：

=3

步骤2：动态计算公式（适用于Excel 365/2021+，支持动态数组）

在C3单元格输入以下公式，然后下拉填充至所有行：

=MIN(3 + SUMPRODUCT(FILTER($B$2:B3,$B$2:B3<>"") * MIN(0.2*(A3 - FILTER($A$2:A3,$B$2:B3<>"") + 1),1)),10)

步骤3：旧版Excel兼容公式（需按Ctrl+Shift+Enter作为数组公式输入）

在C3单元格输入以下公式，按Ctrl+Shift+Enter确认后下拉填充：

=MIN(3 + SUMPRODUCT(($B$2:B3<>"")*$B$2:B3*IF(A3-$A$2:A3+1>=0,MIN(0.2*(A3-$A$2:A3+1),1),0)),10)

公式验证 #

对照示例数据，公式计算结果完全匹配：

内容的提问来源于stack exchange，提问作者Head in excel