如何在数组转矩阵时使用INDIRECT函数或替代方案？

问题核心 #

需要将多个工作表中的数组合并为最大列数(所有数组) × 数组数量的矩阵：

• 手动用HSTACK枚举所有输入可得到正确结果，但无法动态适配新增/删除工作表的情况
• 尝试用转置数组作为INDIRECT参数时出现#VALUE!错误，原因是INDIRECT不支持数组化的引用输入，仅能处理单个单元格或区域引用

解决方案（Excel 365/2021 动态数组版本） #

方案1：无需宏，手动指定工作表列表 #

如果工作表数量固定或可手动维护列表，使用以下公式：

=LET(
    // 手动输入要合并的工作表名称数组
    工作表列表, {"Sheet1","Sheet2","Sheet3"},
    // 定义获取单表数据的函数
    取表数据, LAMBDA(s, INDIRECT("'"&s&"'!A1").CurrentRegion),
    // 计算所有表的最大列数（作为结果的行数）
    最大列数, MAX(BYROW(工作表列表, LAMBDA(s, COLUMNS(取表数据(s))))),
    // 定义将单表数据转置并补空到指定行数的函数
    处理单表, LAMBDA(s, LET(
        原转置, TRANSPOSE(取表数据(s)),
        IF(SEQUENCE(最大列数) <= ROWS(原转置), 原转置, "")
    )),
    // 动态合并所有处理后的列
    HSTACK(INDEX(处理单表(工作表列表),,SEQUENCE(ROWS(工作表列表))))
)

方案2：自动获取工作表列表（需启用宏） #

如果需要自动识别所有目标工作表（排除结果所在表，示例中为Sheet4），使用包含宏函数GET.WORKBOOK的公式：

=LET(
    // 自动获取所有工作表名称并排除结果表
    全表名称, TEXTAFTER(FILTER(GET.WORKBOOK(1), NOT(TEXTAFTER(GET.WORKBOOK(1),"]")="Sheet4")),"]"),
    取表数据, LAMBDA(s, INDIRECT("'"&s&"'!A1").CurrentRegion),
    最大列数, MAX(BYROW(全表名称, LAMBDA(s, COLUMNS(取表数据(s))))),
    处理单表, LAMBDA(s, LET(
        原转置, TRANSPOSE(取表数据(s)),
        IF(SEQUENCE(最大列数) <= ROWS(原转置), 原转置, "")
    )),
    HSTACK(INDEX(处理单表(全表名称),,SEQUENCE(ROWS(全表名称))))
)

关键说明 #

• INDIRECT错误原因：该函数不支持数组参数，无法一次性处理多个工作表的引用数组，必须通过LAMBDA逐个处理每个工作表引用
• 动态适配性：方案1可手动修改工作表列表，方案2会自动识别新增的工作表（需确保目标工作表名称符合过滤规则）
• 数据范围：公式默认取每个工作表中A1起始的连续数据区域（CurrentRegion），如果数据起始位置不同，可修改INDIRECT中的引用范围

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