Excel无VBA贷款偿还计算：简化数组公式需求

问题核心 #

需在无VBA的Excel中计算多笔不同放款季度、可变放款金额的贷款总还款额，所有贷款遵循统一的季度还款比例计划（如放款后第20季度偿还X%）。原方案存在明显痛点：

• 逐笔贷款建偏移行：贷款数量大时表格冗余，维护成本极高
• 手动编写多项目数组公式：需手动输入50+项易出错，还需预留空白行，扩展性差

核心解决思路 #

通过数组反转+SUMPRODUCT批量计算+结果反转的逻辑，将分散的偏移需求转化为批量对齐计算，无需逐笔设置，最终整合为单一公式。具体逻辑：

1. 反转放款金额和还款计划数组，让时间轴反向对齐，消除不同放款时间的偏移差异
2. 用SUMPRODUCT计算反向对齐后的乘积和，得到反向的还款额序列
3. 再次反转结果，还原为正向时间轴的每季度总还款额

单一公式实现（Excel 365/2021及以上版本） #

假设数据结构 #

• 放款数据：$B$2:$B$100=各笔贷款放款金额，$C$2:$C$100=对应放款季度（数字格式，如1代表第1季度）
• 还款计划：$E$2:$E$51=放款后还款季度偏移（从1到50，对应放款后第1到第50季度），$F$2:$F$51=对应季度的还款比例（如5%或0.05）
• 计算表：$H$2:$H$100=需计算的季度序列（从1到最大可能季度，即MAX($C$2:$C$100)+MAX($E$2:$E$51)-1）

公式（以计算第1季度还款额的I2单元格为例） #

=REVERSE(SUMPRODUCT(REVERSE($B$2:$B$100)*REVERSE(IF(($C$2:$C$100 + TRANSPOSE($E$2:$E$51) - 1) = REVERSE($H$2:$H$100), $F$2:$F$51, 0))))

输入后按Enter即可自动溢出到所有计算季度（Excel 365动态数组特性）。

公式拆解 #

1. REVERSE($B$2:$B$100)：反转放款金额数组，让最晚放款的贷款排在序列最前
2. REVERSE($F$2:$F$51)：反转还款比例计划，让放款后最晚的还款比例排在最前
3. ($C$2:$C$100 + TRANSPOSE($E$2:$E$51) - 1)：计算每笔贷款各还款比例对应的实际还款季度（放款季度+偏移季度-1）
4. REVERSE($H$2:$H$100)：反转计算季度序列，让最晚计算季度排在最前，实现反向时间轴对齐
5. IF(..., $F$2:$F$51, 0)：匹配实际还款季度与反转后的计算季度，匹配成功则取对应还款比例，否则为0
6. SUMPRODUCT(...)：批量计算反转后每个季度的总还款额（放款金额×对应还款比例的乘积和）
7. REVERSE(...)：将反向计算结果反转，还原为正向时间轴的每季度总还款额

旧版Excel兼容方案（无REVERSE函数） #

用INDEX模拟数组反转，将上述公式中的REVERSE(数组)替换为：

INDEX(数组, COUNTA(数组)+1-ROW(INDIRECT("1:"&COUNTA(数组))), 1)

例如，反转放款金额数组的写法为：

INDEX($B$2:$B$100, COUNTA($B$2:$B$100)+1-ROW(INDIRECT("1:"&COUNTA($B$2:$B$100))), 1)

注意事项 #

• 放款金额和放款季度列需保持行数一致，空白行不影响计算（SUMPRODUCT会自动忽略非数值）
• 还款计划的偏移季度需连续（从1到N），比例支持百分比或小数格式
• 计算季度序列需覆盖所有可能的还款季度，避免遗漏

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