重置周期动态数组公式问题：月度到期余额预测计算异常

问题核心 #

• 计算规则：月度影响 = 月度余额 × n/12 × 1%，其中n从1开始递增至对应起始月的最大允许值（第1个起始月n到12，第2个到11，以此类推，因总预测期为12个月）
• 当前异常：拖动公式后X14单元格乘数错误（应为1/12，实际显示2/12）
• 需求目标：用SUMPRODUCT批量计算影响值，将AI列的月度预测余额汇总至AI25

一、修正乘数计算逻辑 #

要实现每个起始行的乘数从1/12开始递增，且后续起始行的最大乘数自动递减，可使用列偏移量+行组判断的公式：

=IF(COLUMN()-COLUMN($X$13)+1 <= 12 - (ROW()-ROW($X$13)), (COLUMN()-COLUMN($X$13)+1)/12, "")

公式说明： #

1. COLUMN()-COLUMN($X$13)+1：计算当前单元格相对于起始列X的偏移量，得到当前预测月的序号（从1开始）
2. 12 - (ROW()-ROW($X$13))：计算当前起始月允许的最大乘数序号（第1行起始月为12，第2行11，以此类推）
3. 用IF判断：如果偏移量≤最大序号，计算乘数；否则返回空值（避免超出预测期的无效计算）

将此公式输入X13后，横向拖动到AD列，再纵向拖动到行24，即可实现正确的乘数序列（X14的乘数自动为1/12）

二、SUMPRODUCT批量计算月度影响总和（AI列） #

若AI列是每个起始月的所有预测月影响之和，可直接用SUMPRODUCT替代逐列求和，公式如下（输入AI13后向下拖动）：

=SUMPRODUCT($W13*(ROW(INDIRECT("1:"&12-ROW()+ROW($X$13)))/12)*1%)

公式说明： #

1. 12-ROW()+ROW($X$13)：计算当前起始月的有效预测月数（第1行12，第2行11...）
2. ROW(INDIRECT("1:有效月数"))：生成1到有效月数的序列
3. 整体计算：余额 × 序列均值 × 1%，等价于所有预测月影响值的总和

三、AI列汇总至AI25 #

直接使用SUM函数汇总AI13到AI24的所有值：

=SUM(AI13:AI24)

内容的提问来源于stack exchange，提问作者Matthew Learn