Excel图表图例值的动态工作表引用问题

首先咱们理清核心问题：你创建了工作表级命名区域来动态获取指定日期范围的数据，但复制工作表后，图表系列值仍硬编码指向原工作表的命名区域；尝试用INDIRECT引用当前工作表的命名区域却报#REF!，但直接输入完整引用却能正常生效。

为什么INDIRECT引用工作表级命名区域会报错？ #

工作表级命名区域的完整识别格式是工作簿名!工作表名.命名区域名，但INDIRECT函数更擅长解析单元格引用，对带作用域的命名区域组合格式支持不好。直接输入='Project 2 Charts'!ProjectTemplateNetProfitRanged能生效，是因为Excel直接识别了工作表作用域，但INDIRECT无法正确解析这种嵌套格式。

非VBA解决方案（无需代码） #

方案1：重构工作表级命名区域，去掉硬编码的工作表名 #

你当前的命名区域公式里硬编码了'Project 1 Charts'!$A$2，其实完全可以简化——因为命名区域的作用域是当前工作表，直接引用$A$2即可，Excel会自动关联到当前工作表的A2单元格。

修改后的ProjectTemplateNetProfitRanged命名区域公式：

=INDEX(INDIRECT("'"&$A$2&"'!I15#"),MATCH($C$2, INDIRECT("'"&$A$2&"'!I1#"))):INDEX(INDIRECT("'"&$A$2&"'!I15#"),MATCH($E$2, INDIRECT("'"&$A$2&"'!I1#")))

复制工作表后，新表的命名区域会自动引用自身的A2、C2、E2单元格，无需手动修改。

接下来解决图表引用问题：
创建一个工作簿级命名区域（作用域选整个工作簿），比如命名为CurrentSheetNetProfit，公式写：

=INDIRECT(ADDRESS(1,1,,TRUE, RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))) & "!ProjectTemplateNetProfitRanged")

这个公式通过CELL("filename")获取当前激活的工作表名，再用ADDRESS和INDIRECT组合，动态引用当前工作表的ProjectTemplateNetProfitRanged命名区域。

最后把图表的系列值改成=工作簿名!CurrentSheetNetProfit（当前工作簿内操作可省略工作簿名），这样切换到任意Charts工作表时，图表都会自动加载当前表的命名区域数据。

方案2：用动态数组直接构建图表数据源 #

如果你的Excel版本支持动态数组（365/2021+），可以在Charts工作表的空白区域（比如G1）写公式生成动态数据范围：

=LET(
    SourceSheet, $A$2,
    Dates, INDIRECT("'"&SourceSheet&"'!I1#"),
    NetProfit, INDIRECT("'"&SourceSheet&"'!I15#"),
    StartRow, MATCH($C$2, Dates),
    EndRow, MATCH($E$2, Dates),
    INDEX(NetProfit, StartRow):INDEX(NetProfit, EndRow)
)

让图表直接引用这个动态数组区域即可。复制工作表后，公式会自动关联新表的A2/C2/E2，图表引用也会同步复制到新表的对应区域，无需手动调整。

VBA解决方案（彻底自动化） #

如果非VBA方法满足不了需求，VBA可以实现复制工作表后自动更新所有图表引用，完全无需手动干预。

方法1：复制工作表时自动更新 #

写一个宏来批量复制工作表并修正引用：

Sub CopyChartsSheet()
    Dim originalSheet As Worksheet
    Dim newSheet As Worksheet
    Dim chartObj As ChartObject
    Dim seriesObj As Series
    Dim oldSheetName As String
    Dim newSheetName As String
    
    ' 设置原工作表和新工作表名称
    Set originalSheet = ThisWorkbook.Worksheets("Project 1 Charts")
    newSheetName = "Project 2 Charts"
    
    ' 复制工作表
    originalSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Set newSheet = ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count)
    newSheet.Name = newSheetName
    oldSheetName = originalSheet.Name
    
    ' 遍历新工作表的所有图表，更新系列引用
    For Each chartObj In newSheet.ChartObjects
        For Each seriesObj In chartObj.Chart.SeriesCollection
            ' 替换原工作表名为新工作表名
            seriesObj.Formula = Replace(seriesObj.Formula, oldSheetName, newSheetName)
        Next seriesObj
    Next chartObj
End Sub

运行这个宏就能一键完成工作表复制+图表引用修正。

方法2：工作表激活时自动更新 #

如果你经常手动复制工作表，可以在Charts工作表的代码模块里添加Worksheet_Activate事件，每次激活工作表时自动修正引用：

Private Sub Worksheet_Activate()
    Dim chartObj As ChartObject
    Dim seriesObj As Series
    Dim currentSheetName As String
    
    currentSheetName = Me.Name
    
    For Each chartObj In Me.ChartObjects
        For Each seriesObj In chartObj.Chart.SeriesCollection
            ' 替换任何其他Charts工作表的名称为当前工作表名
            seriesObj.Formula = Replace(seriesObj.Formula, "Project 1 Charts", currentSheetName)
            seriesObj.Formula = Replace(seriesObj.Formula, "Project 2 Charts", currentSheetName)
            ' 可继续添加更多旧工作表名的替换，或用正则表达式匹配所有类似命名
        Next seriesObj
    Next chartObj
End Sub

把这段代码复制到每个Charts工作表的代码模块（或用类模块批量处理），每次打开或切换到该工作表时，图表引用会自动修正为当前工作表的命名区域。

总结 #

• 不想碰代码的话，优先用方案1重构命名区域+工作簿级动态命名区域，简单有效；
• 追求极致自动化的话，VBA方案能彻底解决手动修改的麻烦，推荐用复制工作表的宏批量操作。

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