Excel多动态数组汇总至年月表：新增债券无需重复公式方案

问题现状 #

• 债券表中，单条债券的付款计划用公式生成：=TRANSPOSE(EDATE(C4,SEQUENCE(B4,1,6,6)))
• 年月表的年份序列公式：=TRANSPOSE(SEQUENCE(YEAR(MAX(E3:Z4))-YEAR(MIN(E3:Z4))+1,1,YEAR(MIN(E3:Z4))))
• 年月表单元格需要逐行添加类似=IF(OR((MONTH($E$3#)=$B12)*(YEAR($E$3#)=C$11)),$D$3,0)+IF(OR((MONTH($E$4#)=$B12)*(YEAR($E$4#)=C$11)),$D$4,0)的公式，新增债券行时必须手动追加，易触发公式长度上限。

纯函数式解决方案 #

以下方案基于Excel 365/2021的动态数组功能，无需手动修改公式，新增债券行自动生效：

1. 动态生成所有债券的付款日期与对应金额

用动态数组统一生成所有债券的付款日期，并匹配对应金额（每条债券的金额重复对应其付款次数）：

• 动态日期数组（可直接写在辅助单元格或名称管理器）：

  =TOCOL(BYROW(FILTER(B:D,B:B<>""),LAMBDA(row,TRANSPOSE(EDATE(INDEX(row,2),SEQUENCE(INDEX(row,1),1,6,6))))),1)
  

  说明：FILTER(B:D,B:B<>"")自动筛选所有非空债券行；BYROW逐行生成单条债券的付款计划；TOCOL把所有横向的付款日期转成一列，方便后续计算。

• 动态金额数组：

  =TOCOL(INDEX(FILTER(B:D,B:B<>""),,4),1,COUNTIF(INDEX(FILTER(B:D,B:B<>""),,2),">0"))
  

  说明：提取所有债券的金额列，按每条债券的付款次数重复对应金额，确保和日期数组的行数一一匹配。

2. 动态生成年月表的年份序列

替换原年份序列公式，自动识别所有付款日期的起止年份：

=TRANSPOSE(SEQUENCE(YEAR(MAX(动态日期数组))-YEAR(MIN(动态日期数组))+1,1,YEAR(MIN(动态日期数组))))

直接引用第一步的动态日期数组，新增债券后自动更新起止年份。

3. 年月表核心计算（自动匹配所有债券）

在年月表的左上角单元格（比如C12）输入以下数组公式，回车后自动填充整个结果区域：

=BYROW(B12:B23,LAMBDA(month_val,
    BYCOL(C11:Z11,LAMBDA(year_val,
        SUM(IF((MONTH(动态日期数组)=month_val)*(YEAR(动态日期数组)=year_val),动态金额数组,0))
    ))
))

说明：BYROW遍历所有月份，BYCOL遍历所有年份，对每个年月组合，自动计算所有债券中对应日期的金额总和，新增债券行时无需修改此公式。

额外优化 #

如果不想用辅助单元格，可把动态日期和金额数组直接嵌套进核心公式，避免额外单元格占用：

=LET(
    bonds,FILTER(B:D,B:B<>""),
    dates,TOCOL(BYROW(bonds,LAMBDA(r,TRANSPOSE(EDATE(INDEX(r,2),SEQUENCE(INDEX(r,1),1,6,6))))),1),
    amounts,TOCOL(INDEX(bonds,,4),1,COUNTIF(INDEX(bonds,,2),">0")),
    BYROW(B12:B23,LAMBDA(m,
        BYCOL(C11:Z11,LAMBDA(y,
            SUM(IF((MONTH(dates)=m)*(YEAR(dates)=y),amounts,0))
        ))
    ))
)

LET函数把中间变量封装起来，公式更简洁易读，新增债券行时自动识别并计算。

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