Excel中堆叠不同长度列时公式报#CALC!错误，如何解决？

问题描述 #

• 工作簿包含多个格式一致的工作表，目标是堆叠指定列的非空内容
• A1单元格存储工作表列表：data;Blad7
• 单独读取各工作表A1单元格的公式可正常运行：

  =LET(
    Sheets; TRANSPOSE(TEXTSPLIT(A1; ";"));
    Test; MAP(Sheets; LAMBDA(sheet; INDIRECT("'"&sheet&"'!A1")));
    Test
  )
  

• 单独筛选单个工作表D列非空内容的公式可正常溢出返回结果：

  =FILTER(INDIRECT("'"&"data"'!D:D"); INDIRECT("'"&"data"'!D:D")<>"")
  =FILTER(INDIRECT("'"&"Blad7"'!D:D"); INDIRECT("'"&"Blad7"'!D:D")<>"")
  

• 结合MAP筛选并尝试输出结果时出现#CALC!错误：

  =LET(
    Sheets; TRANSPOSE(TEXTSPLIT(A1; ";"));
    Test; MAP(Sheets; LAMBDA(sheet; FILTER(INDIRECT("'"&sheet&"'!D:D"); INDIRECT("'"&sheet&"'!D:D")<>"")));
    Test
  )
  

• 尝试用BYROW替代MAP、外层嵌套VSTACK的公式同样失效：

  =LET(
    Sheets; TRANSPOSE(TEXTSPLIT(A1; ";"));
    Test; VSTACK(MAP(Sheets; LAMBDA(sheet; FILTER(INDIRECT("'"&sheet&"'!D:D"); INDIRECT("'"&sheet&"'!D:D")<>""))));
    Test
  )
  

错误原因 #

1. MAP函数的返回限制：MAP会生成与输入数组同维度的结果，这里输入是2行的工作表名称数组，MAP会输出2个元素，每个元素是长度不同的单列数组（18行、52行），形成嵌套数组结构。Excel无法直接解析并输出这种嵌套数组，因此抛出#CALC!错误。
2. VSTACK无法处理嵌套参数：外层添加VSTACK时，VSTACK接收的是一个包含两个数组的嵌套数组，而非多个独立的数组参数，VSTACK无法识别这种嵌套结构，因此无法完成堆叠操作。

解决方法 #

使用REDUCE函数逐个处理工作表，逐步累计堆叠结果，避免嵌套数组问题：

=LET(
  Sheets; TRANSPOSE(TEXTSPLIT(A1; ";"));
  REDUCE("", Sheets; LAMBDA(acc, sheet, 
    VSTACK(acc, FILTER(INDIRECT("'"&sheet&"'!D:D"), INDIRECT("'"&sheet&"'!D:D")<>""))
  ))
)

• 原理：REDUCE以空字符串为初始累计值（acc），遍历每个工作表名称，将当前工作表筛选出的非空D列内容，通过VSTACK堆叠到累计结果acc中，最终输出完整的单列堆叠结果。

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