使用LET与MAKEARRAY向Lambda传递范围失败的问题排查

问题背景 #

需要为二维数组生成每行独立重新开始的累计求和结果。

原始数据 #

期望结果 #

已知可行公式 #

直接硬编码单元格范围的MAKEARRAY公式可正常运行：

=MAKEARRAY(
    2,
    4,
    LAMBDA(r, c,
        SUM(
            INDEX(sheet1!A1:D2, r, 1) : INDEX(sheet1!A1:D2, r, c)
        )
    )
)

出现错误的场景 #

1. LET封装的通用公式 #

将目标范围用LET定义后，运行报错：

=LET(
    range, Sheet1!A1:D2,
    MAKEARRAY(
        rows(range),
        Columns(range),
        LAMBDA(r, c,
            SUM(INDEX(range, r, 1) : INDEX(range, r, c))
        )
    )
)

2. 自定义Lambda命名函数 #

将逻辑封装为命名函数TestLambda，调用=TestLambda(A1:D2)时失败：

=LAMBDA(range,
    MAKEARRAY(
        ROWS(range),
        COLUMNS(range),
        LAMBDA(r, c,
            SUM(INDEX(range, r, 1) : INDEX(range, r, c))
        )
    )
)

验证情况 #

• 用LET传递范围给SCAN的测试公式可正常运行，说明LET传递范围本身无问题：

  =LET(
      range, Sheet1!A1:D2,
      SCAN(0, range, LAMBDA(a, c, a + c + INDEX(range, 1, 1)))
  )
  

• 相同逻辑在谷歌表格中直接运行Lambda表达式可正常执行；在Excel中把range定义为静态命名区域时，原公式也能正常运行。

问题原因与解决方案 #

原因分析 #

这是Excel中MAKEARRAY的上下文引用限制导致的：当在MAKEARRAY的内部LAMBDA中使用通过外层LET或自定义Lambda传递的动态范围参数时，Excel无法正确解析INDEX(range, r, 1):INDEX(range, r, c)这种动态构建的连续区域引用的上下文。而硬编码范围或静态命名区域属于Excel能直接识别的静态上下文，因此可以正常运行。

修复后的可行公式 #

改用INDEX结合SEQUENCE提取每行前c个元素后求和，规避动态区域引用的问题：

基于LET的通用公式

=LET(
    range, Sheet1!A1:D2,
    row_cnt, ROWS(range),
    col_cnt, COLUMNS(range),
    MAKEARRAY(
        row_cnt,
        col_cnt,
        LAMBDA(r, c,
            SUM(INDEX(range, r, SEQUENCE(c)))
        )
    )
)

自定义Lambda函数

=LAMBDA(range,
    LET(
        row_cnt, ROWS(range),
        col_cnt, COLUMNS(range),
        MAKEARRAY(
            row_cnt,
            col_cnt,
            LAMBDA(r, c, SUM(INDEX(range, r, SEQUENCE(c))))
        )
    )
)

命名为TestLambda后，调用=TestLambda(A1:D2)即可正常生成逐行累计求和结果。

原理说明 #

SEQUENCE(c)生成从1到c的连续整数序列，INDEX(range, r, SEQUENCE(c))会直接提取第r行的前c个元素组成数组，再通过SUM求和。这种方式不需要构建连续区域引用，而是直接操作目标元素数组，完美规避了Excel在MAKEARRAY内部Lambda中的上下文解析问题。

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