Excel 365无VBA实现可变长度递归动态数组计算

演示示例解决方案 #

针对你给出的累积乘积递归场景（B1=10，Bn=B(n-1)*A(n-1)），可以直接使用SCAN+VSTACK组合实现动态数组，无需下拉公式且自动适配A列数组长度变化：

=VSTACK(10, SCAN(10, A1#, LAMBDA(acc, curr, acc*curr)))

• SCAN函数负责累积计算：以10为初始值，遍历A1#的每个元素，每次用前一次的累积值acc乘当前元素curr，生成B2到末尾的结果。
• VSTACK将初始值10与SCAN的结果堆叠，得到完整的递归序列。

实际复杂公式的实现 #

针对你提供的递归公式：

Q20=EXP((L19-L20)/$Q$12)*(Q19-$Q$19)+(1-EXP((L19-L20)/$Q$12))*$Q$8*($P$16)+$Q$19

其中L19#是可变长度的溢出数组，需生成与其长度匹配的Q序列，可按以下步骤实现：

核心思路 #

利用SCAN处理递归依赖，结合TAKE/DROP提取L19#的相邻元素差值，最终用VSTACK拼接初始值与递归结果：

=VSTACK($Q$19, SCAN($Q$19, TAKE(L19#, ROWS(L19#)-1) - DROP(L19#,1), LAMBDA(prev_q, delta_L, EXP(delta_L/$Q$12)*(prev_q-$Q$19)+(1-EXP(delta_L/$Q$12))*$Q$8*$P$16+$Q$19)))

公式拆解 #

1. 相邻差值计算：TAKE(L19#, ROWS(L19#)-1) - DROP(L19#,1)生成L19-L20、L20-L21...的差值数组，长度比L19#少1。
2. 递归累积计算：SCAN以$Q$19为初始值，遍历差值数组，通过LAMBDA函数代入你的递归公式，每次用前一个Q值（prev_q）计算当前Q值。
3. 完整序列生成：VSTACK将初始值$Q$19与SCAN的递归结果堆叠，生成与L19#长度完全匹配的溢出数组。

注意事项 #

• 需使用支持动态数组的Excel版本（365/2021及以后）。
• 保持公式中固定参数的绝对引用（$Q$12、$Q$8等），避免数组移位错误。
• 当L19#的长度因FILTER条件变化时，Q列的溢出数组会自动同步扩展/收缩，无需手动调整。

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