Excel如何动态引用可变外部文件名实现跨工作簿数据调取

核心问题说明 #

你之前的写法错误是因为Excel不支持在引用路径里直接嵌套单元格变量，必须使用INDIRECT函数拼接完整的引用字符串，且INDIRECT函数要求被引用的外部工作簿必须处于打开状态才能正常返回数值，关闭状态下会返回#REF!错误。

操作步骤 #

1. 先把可变的参数单独存放在固定单元格中，方便后续修改，示例存储位置：

• C2单元格：存放文件夹路径，值为 Z:\Jeremy\new test\ （注意路径末尾要加反斜杠）
• E2单元格：存放目标文件名，值为 Fiche_Audit_-_V2__20210831.xlsx
• F2单元格：存放目标工作表名，值为 Worksheet

2. 使用拼接字符串的方式生成合法的引用，正确公式如下（适配你使用的法语版Excel，参数分隔符为分号）：

=INDEX(INDIRECT("'"&C2&"["&E2&"]"&F2&"'!$3:$3");0;EQUIV("Tarif souscrit ";INDIRECT("'"&C2&"["&E2&"]"&F2&"'!$1:$1");0))

进阶优化（适配路径变动场景） #

如果文件夹路径也会随使用人变化，可以将两个Excel文件放在同一个文件夹下，用公式自动获取当前文件所在路径，无需手动写死路径：

• 将C2单元格的内容替换为公式：=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)
  该公式会自动读取1号文件所在的文件夹路径，只要2号文件和1号文件存放在同一目录下，无论整个文件夹移动到任何位置，都不需要手动修改路径参数。

关闭状态读取方案 #

如果你需要在2号文件关闭的场景下也能正常读取数据，无法通过普通公式实现，需要使用VBA自定义函数实现对关闭工作簿的读取。

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