未引用物理数组时Excel公式LAMBDA报错问题求助

需要在单个单元格计算存储月数，要求此前所有月份的累计值等于一次性移除率，具体操作及遇到的问题如下：

• 生成12个月度费率数组：{.5,.5,.5,.6,.6,.6,.6,.6,.6,.9,.9,.9}
• 使用公式计算累计求和序列：LET(array,$D$9:$O$9,MAP(array,LAMBDA(x,SUM(INDEX(array,1,1):x))))
• 通过MATCH(移除率, 返回数组,1)匹配对应月份索引

公式运行情况 #

拆分后的公式可正常运行：

LET(monArray,SEQUENCE(1,12),IFS(monArray<=3,0.5,monArray<=9,0.6,monArray<=12,0.9))
MATCH(D4,LET(array,$D$9:$O$9,MAP(array,LAMBDA(x,SUM(INDEX(array,1,1):x)))),1)

但合并后的公式无法运行：

=LET(ratearray,LET(monArray,SEQUENCE(1,12),IFS(monArray<=3,0.5,monArray<=9,0.6,monArray<=12,0.9)),MATCH(D4,LET(array,ratearray,MAP(array,LAMBDA(x,SUM(INDEX(array,1,1):x)))),1))

问题排查 #

当LAMBDA函数未引用实际物理单元格区域，而是引用别名或公式生成的数组时会报错。例如用VSTACK($D$9:$O$9)替代直接引用单元格区域也会出错：
可运行公式：

MATCH(6,MAP($D$9:$O$9,LAMBDA(x,SUM(INDEX($D$9:$O$9,1):x))),1)

不可运行公式：

MATCH(6,MAP(VSTACK($D$9:$O$9),LAMBDA(x,SUM(INDEX(VSTACK($D$9:$O$9),1):x))),1)

需求：寻求非VBA解决方案，可修复现有公式的变通方案，或重新编写公式实现需求，不接受VBA方案。

解决方案 #

方案1：用SCAN函数替代MAP+SUM组合 #

SCAN可直接生成累计求和序列，无需依赖单元格区域引用，完美适配公式生成的虚拟数组：

=LET(
    ratearray, LET(monArray,SEQUENCE(1,12),IFS(monArray<=3,0.5,monArray<=9,0.6,monArray<=12,0.9)),
    cumulatives, SCAN(0, ratearray, LAMBDA(a,b,a+b)),
    MATCH(D4, cumulatives, 1)
)

原理：SCAN初始值设为0，每次迭代将当前累计值a加上当前费率b，直接生成累计数组，避免了MAP中需要引用数组起始位置的问题。

方案2：修复原MAP公式的引用逻辑 #

如果坚持使用MAP函数，可通过索引遍历数组元素来计算累计和：

=LET(
    ratearray, LET(monArray,SEQUENCE(1,12),IFS(monArray<=3,0.5,monArray<=9,0.6,monArray<=12,0.9)),
    cumulatives, MAP(SEQUENCE(COLUMNS(ratearray)), LAMBDA(i, SUM(INDEX(ratearray,1,1):INDEX(ratearray,1,i)))),
    MATCH(D4, cumulatives, 1)
)

原理：用SEQUENCE生成数组的索引序列，通过INDEX定位对应位置的元素，再计算从第一个元素到当前元素的和，无需依赖对元素本身的引用。

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