如何在Excel中通过命名动态数组公式生成累计求和（运行总计）

现有已定义的命名公式 #

• twelve：=SEQUENCE(12)，生成1到12的整数数组，用于生成月份列表等场景（如=TEXT(DATE(2023,twelve,1),"mmm-yy")）。
• FTE：='CSM'!$B$3/12*(--(twelve=twelve))，将CSM工作表B3单元格的年薪拆分为月度薪资，输入后会溢出显示12个月份的结果。

问题说明 #

需要创建一个命名动态数组公式，能够溢出显示FTE的月度累计薪资（运行总计），且在工作簿任意单元格使用时无需修改公式，结果完全一致。

此前尝试的方法存在局限性：

• 关联单元格行号的公式（如=SUM((--(twelve<=ROW()-1))*FTE)）需要根据放置位置调整行号偏移量，无法通用。
• 工作表中常用的MMULT累计求和公式（如=MMULT(ROW(A1#)>=TRANSPOSE(ROW(A1#)),A1#)）移植到命名公式时会返回#VALUE!错误，原因是ROW(FTE)无法获取数组的行号序列，但ROWS(FTE)可正确返回12。

示例数据 #

当B3为$75,000时，=FTE的溢出结果为：

$6,369.86
$5,753.42
$6,369.86
$6,164.38
$6,369.86
$6,164.38
$6,369.86
$6,369.86
$6,164.38
$6,369.86
$6,164.38
$6,369.86

需要生成的累计薪资结果为：

$6,369.86
$12,123.29
$18,493.15
$24,657.53
$31,027.40
$37,191.78
$43,561.64
$49,931.51
$56,095.89
$62,465.75
$68,630.14
$75,000.00

注：暂未启用Lambda等较新函数，可使用SEQUENCE()或MMULT()等函数。

解决方案 #

定义命名公式FTE_Cumulative，公式内容如下：

=MMULT(--(SEQUENCE(ROWS(FTE))>=TRANSPOSE(SEQUENCE(ROWS(FTE)))),FTE)

公式说明 #

1. SEQUENCE(ROWS(FTE))：生成与FTE数组行数一致的整数序列（此处为1到12），替代依赖单元格位置的ROW函数，确保命名公式不随放置位置变化。
2. --(SEQUENCE(ROWS(FTE))>=TRANSPOSE(SEQUENCE(ROWS(FTE))))：生成12×12的布尔矩阵，矩阵中第i行第j列元素为1（当i≥j时），否则为0，用于标记累计求和的范围。
3. MMULT(...)：将布尔矩阵与FTE数组进行矩阵乘法运算，得到每行的累计求和结果，最终溢出显示12个月份的累计薪资。

验证：在任意单元格输入=FTE_Cumulative，均可得到与示例一致的累计薪资结果，无需调整公式。

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