如何在Excel单元格引用中使用日期变量替换固定路径中的日期

方案1：INDIRECT函数（操作简单，需源工作簿处于打开状态） #

• 先找一个空白辅助单元格（例如存放于Sheet1!$Z$1，可隐藏该单元格避免误改），输入公式生成前一天的标准日期文本：
  =TEXT(TODAY()-1,"yyyy-mm-dd")
• 把原来20个单元格的固定引用公式替换为动态拼接公式：
  =INDIRECT("'J:\analysis\"&Sheet1!$Z$1&" 24 version[workbookname.xlsb]worksheetname'!$A$1")
  只需要修改末尾的!$A$1为对应单元格坐标即可，不需要修改路径部分

注意：INDIRECT函数属于易失性函数，且引用未打开的外部工作簿时会返回#REF!错误，仅适合能保证源文件每日打开的场景使用

方案2：VBA自定义函数（支持读取未打开的源文件，稳定性更高） #

1. 按Alt+F11打开VBA编辑器，右键当前工作簿名称→插入→模块，粘贴如下代码：

Function GetYesterdayData(cellRef As String) As Variant
    Dim yesterdayStr As String
    Dim fullPath As String
    yesterdayStr = Format(Date - 1, "yyyy-mm-dd")
    ' 可根据实际需求修改下方的路径、工作簿名、工作表名
    fullPath = "'J:\analysis\" & yesterdayStr & " 24 version\[workbookname.xlsb]worksheetname'!" & cellRef
    GetYesterdayData = Application.ExecuteExcel4Macro(fullPath)
End Function

2. 保存文件为.xlsm启用宏的格式，之后所有需要引用的单元格直接输入公式即可，比如引用A1就写=GetYesterdayData("$A$1")

提示：首次使用需要在Excel信任中心启用宏，后续无需修改公式，每天打开文件会自动读取前一天的文件数据

方案3：Power Query批量获取（适合需要引用大量单元格的场景） #

• 点击「数据」选项卡→获取数据→自文件→自工作簿，先随便选一天的文件加载对应的工作表
• 进入Power Query编辑器，打开「高级编辑器」，把路径里的固定日期替换为Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-1),"yyyy-MM-dd")动态生成的日期
• 关闭编辑器加载数据到Excel表格，需要更新时点击「全部刷新」即可自动拉取前一天的文件数据，所有单元格直接引用加载出来的表对应位置就行。

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