从关闭的Excel工作簿提取特定数据的简化方案咨询

DevOps 小满

2026-6-1

更简洁易维护的跨关闭工作簿数据提取方案

针对你当前用冗长的AGGREGATE+INDEX公式提取数据的痛点，以下是三种更高效的解决方案，按推荐优先级排序：

1. Power Query（最优方案：零公式、易维护）

Power Query是Excel专门处理批量数据整合的工具，完全替代繁琐的数组公式，步骤如下：

第一步：整理文件清单（可选，也可直接扫文件夹）
在空白工作表（比如Sheet2）中列出所有员工工作簿的关键信息：

员工姓名	完整文件路径	工作表名
Ian	S:\POD\POB-SEC3\Releaser Spreadsheets\Team 1\2026 Technician Release Sheet Ian.xlsx	2601
Matthew	S:\POD\POB-SEC3\Releaser Spreadsheets\Team 1\2026 Technician Release Sheet Matthew.xlsx	2601
...	...	...

若文件按团队分文件夹存放，可直接跳过手动清单，用Power Query批量扫描根目录。

第二步：批量导入并提取指定数据
1. 点击「数据」选项卡 → 「获取数据」→ 「自文件」→ 「自文件夹」
2. 选择所有员工工作簿的根目录（如S:\POD\POB-SEC3\Releaser Spreadsheets），勾选「包含子文件夹」
3. 在弹出的预览窗口点击「编辑」，进入Power Query编辑器
4. 添加自定义列，提取指定行（对应你原公式的$A$1行号）的C列数据：
  自定义列公式：
```
= Table.Row(Excel.Workbook([Content]){[Item="2601",Kind="Sheet"]}[Data], Range("A1").Value - 1)[Column3]
```
  （注：Range("A1").Value对应汇总表中指定行号的单元格，若行号固定可直接写数字，比如5）
5. 清理无关列（如文件名、路径），点击「关闭并上载」，将结果加载到汇总表
后续维护：新增/删除员工文件后，只需点击汇总表中的「刷新」按钮，数据自动同步，无需修改任何公式。

若暂时不想用Power Query，可通过定义名称简化现有公式：

第一步：创建路径名称列表
在空白单元格区域（比如Sheet2的A列）逐个输入每个员工工作簿的完整引用路径，例如：
```
'S:\POD\POB-SEC3\Releaser Spreadsheets\Team 1\[2026 Technician Release Sheet Ian.xlsx]2601'!$C:$C
```
选中所有路径单元格，点击「公式」选项卡 → 「定义名称」，命名为FilePaths。
第二步：用SUMPRODUCT替代AGGREGATE
替换原冗长公式为：
```
=SUMPRODUCT(INDIRECT(INDEX(FilePaths,ROW(INDIRECT("1:"&COUNTA(FilePaths))))&"!"&$A$1))
```
公式说明：
- COUNTA(FilePaths)自动统计员工文件数量，新增路径后无需修改公式范围
- INDIRECT调用定义好的路径，INDEX逐个提取每个员工的路径，最终用SUMPRODUCT求和（和原AGGREGATE(9,6,...)的求和逻辑一致）

如果你的Excel版本支持动态数组，可结合结构化表格实现自动更新：

第一步：创建结构化路径表
选中路径清单区域，按Ctrl+T创建Excel表，命名为EmployeeFiles，添加「文件路径」列，填入所有员工工作簿的完整引用路径。
第二步：用BYROW+INDEX提取数据
公式如下：
```
=SUM(BYROW(EmployeeFiles[文件路径],LAMBDA(path,INDEX(INDIRECT(path),$A$1))))
```
新增员工时，只需在结构化表中添加一行路径，公式会自动纳入新数据，无需调整范围。

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