如何实现可动态调整求和项数量的Excel公式下拉填充？

根据以下表格数据，需要基于B1的起始年份和A2-A7的结束年份，计算B2-B7的投资组合总规模：

需求规则：

• 每年投入C10的金额
• 复利计算基于C列对应年份的TR-Price变化
• 边界情况：起始年晚于/等于结束年时，返回N/A或0
• 向下填充公式时需自动调整求和项数量

解决方案：原生Excel公式实现（无需VBA） #

在B2单元格输入以下公式，直接向下填充即可自动适配所有行的计算需求：

=IF(OR(A2<$B$1,A2=$B$1),NA(),$C$10*SUMPRODUCT($C2/INDEX($C:$C,MATCH($B$1,$A:$A,0)):INDEX($C:$C,ROW()-1)))

公式拆解 #

1. 边界判断
   IF(OR(A2<$B$1,A2=$B$1),NA(),...)：当A列的结束年份早于或等于B1的起始年份时，返回NA()，符合需求中的边界处理逻辑。

2. 动态范围生成

   • MATCH($B$1,$A:$A,0)：定位起始年份在A列对应的行号（示例中为3）。
   • INDEX($C:$C,MATCH(...)):INDEX($C:$C,ROW()-1)：自动生成从起始年份对应的C列值，到当前行上一行的C列值的连续范围。比如B4单元格（行4）对应范围C3:C3，B5单元格（行5）对应范围C3:C4，随结束年份自动扩展。

3. 复利求和计算

   • SUMPRODUCT($C2/...)：遍历动态范围内的每个C列值，计算当前行C值与每个值的商，再将所有商求和得到复利总系数。
   • 乘以$C$10（年度投资额）后，得到最终投资组合规模。

示例验证 #

• B2：A2=1950 < B1=1951 → 返回NA()，正确。
• B3：A3=1951 = B1=1951 → 返回NA()，正确。
• B4：计算$1*(151/152)=0.9934，正确。
• B5：计算$1*(157/152 + 157/151)=2.0726，正确。

替代公式（OFFSET版本） #

如果偏好使用OFFSET函数，可替换为以下公式，逻辑与INDEX版本完全一致：

=IF(OR(A2<$B$1,A2=$B$1),NA(),$C$10*SUMPRODUCT($C2/OFFSET($C$1,MATCH($B$1,$A:$A,0)-1,0,ROW()-MATCH($B$1,$A:$A,0),1)))

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