Excel公式用单元格值作外部工作簿文件名，文件关闭失效如何解决？

问题背景 #

原固定引用公式：

='C:\Users\Damien\Documents\Personal\Coffee\Workcards\[UGAG003.xlsx]Checklist'!$B$4

需将UGAG003替换为当前工作表B列单元格的值，且外部工作簿关闭时仍能读取数据（INDIRECT仅支持打开状态的文件，无法满足需求）。

解决方案 #

方法1：定义名称 + EVALUATE（无需VBA，支持关闭文件） #

1. 点击公式选项卡 → 定义名称，设置：
   • 名称：GetExternalValue
   • 引用位置：

     =EVALUATE("'C:\Users\Damien\Documents\Personal\Coffee\Workcards\[" & Sheet1!$B3 & ".xlsx]Checklist'!$B$4")
     

     注意：将Sheet1替换为你当前使用的工作表名称
2. 在需要显示数据的单元格（如C3）输入：

   =GetExternalValue
   

3. 下拉填充整列，即可实现每行自动匹配B列对应的外部工作簿数据，即使目标文件关闭也能正常读取。

方法2：Power Query批量导入（适合批量处理，稳定性高） #

1. 点击数据选项卡 → 获取数据 → 从文件 → 从文件夹
2. 选择目标文件夹C:\Users\Damien\Documents\Personal\Coffee\Workcards，点击确定
3. 在弹出的对话框中点击编辑，进入Power Query编辑器
4. 添加自定义列，输入公式：

   =Excel.Workbook([Content]){[Item="Checklist",Kind="Sheet"]}[Data]{3}[Column2]
   

   解释：{3}对应第4行（索引从0开始），[Column2]对应B列（索引从0开始），即目标单元格B4
5. 添加辅助列提取文件名前缀（去除.xlsx）：

   =Text.BeforeDelimiter([Name],".")
   

6. 关闭并上载数据到当前工作表，之后用VLOOKUP匹配B列的值获取对应数据：

   =VLOOKUP($B3, 上载的表区域, 自定义列所在列号, FALSE)
   

   右键点击表格可刷新数据，即使外部文件关闭也能保留数据。

方法3：VBA自定义函数（灵活自动化） #

1. 按Alt+F11打开VBA编辑器，插入模块，粘贴代码：

   Function GetClosedWorkbookValue(fileName As String, sheetName As String, cellAddr As String) As Variant
       Dim filePath As String
       filePath = "C:\Users\Damien\Documents\Personal\Coffee\Workcards\" & fileName & ".xlsx"
       GetClosedWorkbookValue = ExecuteExcel4Macro("'" & filePath & "'!" & sheetName & "!" & cellAddr)
   End Function
   

2. 在单元格中输入公式，下拉填充：

   =GetClosedWorkbookValue(B3,"Checklist","$B$4")
   

   注意：文件需保存为.xlsm格式，启用宏后生效

注意事项 #

• 确保外部文件路径准确，文件名无特殊字符（如空格、中文需格外注意）
• 方法1中定义名称时，需保证$B3的相对引用正确，下拉时能匹配对应行的B列值
• Power Query方法需手动刷新数据以获取外部文件的最新内容

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