EP010 不同工作簿下的多个工作表的追加查询合并

在上一节中，我们提到了如何汇总同一个工作簿内的多个工作表。但相信很多同学都会遇到一个更普遍的场景：数据源本身就是分散在不同Excel工作簿文件中的，比如一月销售.xlsx、二月销售.xlsx… 我们需要在一个全新的汇总工作簿文件中，将它们合并起来。

下面，我们就来攻克这个难题。思路是构建一个强大的自动化报表，它不仅能汇总不同工作簿的数据，更能解决两个极其棘手的“痛点”：

1. 动态路径问题：如何让你的查询在整个项目文件夹被移动或分享给他人后，依然能“一键刷新”？
2. 动态列问题：当源数据增加了新的列，如何让汇总表自动把它包含进来？（参见EP009第③部分）

---

第一部分：基础方法——快速合并，但隐患重重

我们先用最直接的方法来感受一下。假设我们有一个汇总分析.xlsx文件，和三个数据源文件（工作簿1.xlsx、 工作簿2.xlsx、 工作簿3.xlsx）存放在同一个文件夹中。

操作步骤：

1. 创建独立查询：在汇总分析.xlsx中，打开Power Query编辑器。通过 “新建源” -> “文件” -> “Excel工作簿”，分别为工作簿1、工作簿2和工作簿3创建三个独立的查询。
2. 追加为新查询：点击 “主页” -> “追加查询” -> “将查询追加为新查询”。在弹出的对话框中，选择“三个或更多表”，并将刚刚创建的三个查询全部添加进去。
3. 加载数据：点击“关闭并上载”，将最终的汇总查询和三个中间查询都加载到Excel中。

此时，一个初步的汇总表就完成了。我们测试一下，在工作簿1.xlsx中增加一行数据，甚至增加一个“备注”列，回到汇总分析.xlsx点击“全部刷新”，你会发现所有更改都能被正确更新。

注意：这里的“新增列”之所以能成功刷新，是因为我们采用的是“独立查询后再追加”的模式。Power Query在刷新每个独立查询时，会自动获取其最新的所有列，所以最终追加时，列信息是动态的。这与上节课Excel.Workbook()展开列的机制有所不同。

痛点演示：一碰就碎的“绝对路径”

看起来很完美，对吧？

现在，我们来模拟一个真实的工作场景：将整个项目文件夹从“桌面”移动到“C盘”。

（移动文件夹…）

现在，我们重新打开汇总分析.xlsx，再次点击“全部刷新”…

砰！报错了！

Power Query会弹出错误，抱怨说“找不到位于‘桌面’的那个文件”。

这就是基础方法最致命的弱点：它在查询中记录了每个数据源文件的绝对路径。一旦文件位置发生任何变化，整个自动化流程就会立刻瘫痪。

---

第二部分：终极解决方案——构建“永不迷路”的动态路径查询

要解决这个问题，我们的核心思路是：不再告诉Power Query一个写死的地址，而是赋予它一种能力，让它自己去计算出数据源文件当前在哪里。

步骤一：在Excel中创建“路径配置中心”

1. 在汇总分析.xlsx文件中，新建一个工作表，命名为 配置。这个工作表将成为我们所有动态参数的“控制面板”。
2. 我们需要为每一个数据源文件（工作簿1.xlsx, 工作簿2.xlsx, 工作簿3.xlsx）都生成一个动态路径。在配置工作表的A列，我们分别填入以下公式： 示例（A1, A2, A3单元格分别填入）： =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)&"工作簿1.xlsx" =LEFT(CELL("filename",A2),FIND("[",CELL("filename",A2))-1)&"工作簿2.xlsx" =LEFT(CELL("filename",A3),FIND("[",CELL("filename",A3))-1)&"工作簿3.xlsx"

• 公式解读：这个公式由 CELL(“filename”), LEFT, FIND 等函数组合而成。它的作用是先获取当前汇总分析.xlsx文件所在的文件夹路径，然后在其后面拼接上我们已知的数据源文件名（如工作簿1.xlsx），从而动态地构建出数据源的完整路径。

步骤二：为动态路径“命名”

为了让Power Query能轻松地找到并引用这些路径，我们需要为它们“定义名称”。

1. 选中包含“工作簿1”路径的单元格（例如A1）。
2. 点击顶部菜单 “公式” -> “定义名称”。
3. 输入名称，例如 工作簿1 ，然后确定。
4. 对其他几个路径单元格重复此操作，分别命名为工作簿2、工作簿3。

步骤三：改造Power Query查询——注入“GPS导航”

现在，我们回到Power Query编辑器，对之前创建的三个独立查询进行“升级改造”。

1. 选中工作簿1的查询，在右侧“应用的步骤”中，点击第一步“源”。
2. 看顶部的公式栏，您会看到一个包含写死路径的M代码。
3. 执行关键替换：将公式中写死的类似路径文本（"C:\文件夹\..." 这部分），替换为下面这段能够读取已定义名称的M函数代码：
   • 代码示例： = Excel.CurrentWorkbook(){[Name="工作簿1"]}[Content]{0}[Column1] 用以上这个动态路径获取函数，获取“工作簿1”的实时路径，并且替换原来的被写死的路径。
4. 修改后的完整“源”步骤代码应如下所示： = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="工作簿1"]}[Content]{0}[Column1]), null, true)
5. 对工作簿2和工作簿3的查询重复此操作，确保将M代码中的Name="工作簿1"相应地修改为工作簿2和工作簿3。

修复完这三个查询的“源”之后，您会发现，那个最终的追加查询也自动恢复正常了！

---

第三部分：最终验证与总结

现在，关闭并上载您的查询。我们再来做一次“终极测试”：

• 将整个项目文件夹再次移动到任意一个新的位置。
• 打开汇总分析.xlsx，修改任意一个源文件的数据（增行、增列、改值）。
• 回到汇总分析.xlsx，点击“全部刷新”。

这个时候，一切都完美刷新了！

重要注意事项与建议

• 相对位置不变：此方法的前提是，您的汇总分析.xlsx和所有工作簿.xlsx数据源文件，必须始终保持在同一个文件夹内，作为一个整体进行移动和分享。
• 文件名依赖：如果您重命名了某个数据源文件（例如，工作簿1.xlsx改成了第一季度.xlsx），您必须回到配置工作表中，手动更新对应的动态路径获取公式，否则刷新会失败。
• 给用户的建议：
  • 如果您的文件位置是固定的，完全可以使用第一部分的基础方法，因为它最简单直接。
  • 如果您需要频繁移动、分享文件，或者构建一个需要长期维护的报告，那么强烈建议您花时间学习并使用第二部分的动态路径方法，它将为您节省大量后期维护的时间和精力。