如何在Excel内合并不同工作簿中的多个工作表？

适用场景 #

跨工作簿合并表头一致、行数可变的工作表，将数据验证列转为文本，实现自动更新、避免手动调整范围（支持Excel 365/2021+，老版本有替代方案）

核心动态数组方案（推荐，支持自动扩展） #

利用Excel动态数组函数实现自动堆叠数据、过滤空行，同时转换文本格式：

1. 提取单个工作表有效数据（转文本）

提取非空数据行并统一转为文本格式：

=FILTER(TEXT('文件全路径[工作簿名.xlsx]工作表名'!A:Z, "@"), '文件全路径[工作簿名.xlsx]工作表名'!A:A <> "")

• TEXT(..., "@")：将所有单元格转为文本，解决数据验证列的格式兼容问题
• FILTER：自动过滤空行，仅保留有数据的行

2. 批量合并多个工作表

用VSTACK垂直堆叠多个工作表的数据，仅保留一组表头：

=VSTACK(
    '路径1[工作簿1.xlsx]表1'!A1:Z1,  // 统一表头
    FILTER(TEXT('路径1[工作簿1.xlsx]表1'!A2:Z, "@"), '路径1[工作簿1.xlsx]表1'!A2:A <> ""),
    FILTER(TEXT('路径2[工作簿2.xlsx]表2'!A2:Z, "@"), '路径2[工作簿2.xlsx]表2'!A2:A <> ""),
    FILTER(TEXT('路径3[工作簿3.xlsx]表3'!A2:Z, "@"), '路径3[工作簿3.xlsx]表3'!A2:A <> "")
)

• 源表新增行时，动态数组会自动扩展合并表的行数，无需手动修改公式范围
• 需保证所有源表列数完全一致，否则VSTACK会返回错误

3. 简化多工作表公式（减少重复输入）

合并大量工作表时，用LET定义重复路径变量，提升公式可读性与可维护性：

=LET(
    src1, "路径1[工作簿1.xlsx]表1",
    src2, "路径2[工作簿2.xlsx]表2",
    src3, "路径3[工作簿3.xlsx]表3",
    header, INDIRECT(src1 & "!A1:Z1"),
    data1, FILTER(TEXT(INDIRECT(src1 & "!A2:Z"), "@"), INDIRECT(src1 & "!A2:A") <> ""),
    data2, FILTER(TEXT(INDIRECT(src2 & "!A2:Z"), "@"), INDIRECT(src2 & "!A2:A") <> ""),
    data3, FILTER(TEXT(INDIRECT(src3 & "!A2:Z"), "@"), INDIRECT(src3 & "!A2:A") <> ""),
    VSTACK(header, data1, data2, data3)
)

老版本Excel（无动态数组）替代方案 #

如果使用Excel 2019及以下版本，可通过INDEX+COUNTA实现半自动化合并：

1. 计算单个源表的有效数据行数（不含表头）：

   =COUNTA('路径[工作簿.xlsx]表名'!A:A)-1
   

2. 提取对应行数据并转文本（下拉公式至足够行数）：

   =TEXT(INDEX('路径[工作簿.xlsx]表名'!A:A, ROW(A1)+1), "@")
   

3. 对其他工作表重复上述操作，手动拼接数据区域（需定期更新行数范围）

注意事项 #

• 跨工作簿引用时，源工作簿需处于打开状态（否则INDIRECT无法正常解析；若需关闭源工作簿，可改用XLOOKUP结合动态数组，但公式复杂度更高）
• 若源表存在空行，FILTER会自动跳过，无需手动清理

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