如何通过Excel 3D Reference将多工作表文本按行列整理至主表

问题分析 #

你之前用=CONCAT('Worksheet 01:Worksheet 03'!B1:B3)得到的是所有文本连在一起的结果，原因是CONCAT会直接拼接3D引用区域内所有单元格的内容，不会保留原有的行列对应关系。

解决方案 #

要实现每行对应原区域的行、每列对应一个工作表，同时支持首尾工作表间增删工作表的需求，可使用带三维参数的INDEX函数：

1. 在主工作表的目标起始单元格（比如A1）输入公式：

   =INDEX('Worksheet 01:Worksheet 03'!B1:B3,ROW(A1),1,COLUMN(A1))
   

2. 将公式向右填充到所有需要对应工作表的列，再向下填充到3行（对应原区域的B1-B3）。

公式说明

• 'Worksheet 01:Worksheet 03'!B1:B3：指定要引用的首尾工作表及目标区域，中间新增/删除工作表时，该3D引用会自动包含所有中间表
• ROW(A1)：控制提取原区域的行号（向下填充时自动变为ROW(A2)、ROW(A3)，对应B2、B3）
• 1：因为原区域是B1:B3（单列区域），所以取区域内的第1列
• COLUMN(A1)：控制提取第几个工作表的内容（向右填充时自动变为COLUMN(B1)、COLUMN(C1)，对应第2、第3个工作表）

优化处理（可选） #

如果部分工作表的B1:B3单元格为空，公式会返回0，可以用IFERROR替换为空文本：

=IFERROR(INDEX('Worksheet 01:Worksheet 03'!B1:B3,ROW(A1),1,COLUMN(A1)),"")

注意事项 #

• 保持首尾工作表（Worksheet 01和Worksheet 03）的名称不变，中间工作表可任意增删
• 若原目标区域范围调整（比如改为B1:B5），需同步修改公式中的B1:B3部分

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